#127 Mssql and DATE fields

[Anton Duzenko]

I have a problem with DATE fields in MS SQL
I have a lot of code that works with a database with a fixed structure but different sql server (sqlite, firebird, mysql). Now I need to add MS Sql. My problem is that all other servers return a DATE field as a TDateField, while MS SQL returns it as TStringField
As a result, I am getting a conversion error exception whenever I try to access a DATE field

Can we do something about this??

[marsupilami79]

Hello Anton,

again the culprit is TZMsSqlDatabaseMetadata.UncachedGetColumns in ZDbcDbLibMetadata.pas. This time it is the line that determines the data type:
SQLType := ConvertODBCToSqlType(GetShortByName('DATA_TYPE'), ConSettings.CPType);
The column DATA_TYPE returns the ODBC data type, which my MSSQL 2008R2 always sets to -9, which, regarding [1] means SQL_WVARCHAR.
A possible solution might be to have UncachedGetColumns first check the TYPE_NAME column and only use the ODBC data type as a fallback. This way it would be possible to extend the current behaviour whenever something strange happens.
Unfortunately I don't have the time to test this currently, so if we decide to go that route you would have to check if this solves your problem or not.
For a documentation on sp_columns see [2].

With best regards,

Jan

[1] http://www.easysoft.com/developer/languages/c/examples/ListDataTypes.html
[2] https://msdn.microsoft.com/en-us/library/ms176077.aspx

[Anton Duzenko]

Hi Jan,

I will try to do that later this week

[Anton Duzenko]

Hi Jan,

Sorry for the delay

Your suggestion worked like a charm. I did not apply it for other types though so as not to break anything. My code looks like this:

        if SQLType = stUnknown then
          Result.UpdateNullByName('DATA_TYPE')
        else if (SQLType=stString) and (GetStringByName('TYPE_NAME') = 'date') then
          Result.UpdateShortByName('DATA_TYPE', Ord(stDate))
        else
          Result.UpdateShortByName('DATA_TYPE', Ord(SQLType));

[EgonHugeist]

Patch done R3720 /testing-7.2 (svn)
please test and don't forget to close the ticket!

Cheers, Michael