A problem with mapping in type ftTime using prSQL provider and English (US) regional settings in MS Windows

[createmark]

Hi!

We've encountered such problem, we have a field with 'time' type on our server (MS SQL Server 2012). PrSQL provider is set in the options of TMSConnection component on the client. Thus, in the dataset server type 'time' is recognized as TWideString. We have manually changed the type to TTime and created mapping rule for this exact field (DataSet.DataTypeMap.AddFieldNameRule('SomeFieldName', ftTime), its type is recognized as TTime.

In case time format in MS Windows is set to 24 hours and not AM/PM, everything is fine. But when we use American settings (i.e. AM/PM time format), then during the data update we get this error: Value is too long: 22:00:00.

I digged in the code a bit and found that TCRRecordSet.PutFieldData procedure invokes the data accuracy check function that compares the length of '10:00:00 PM' to 8 (a TTime type size). Thus the data is considered inaccurate because 10:00:00 PM is longer and therefore update isn't completed. An interesting thing is that an incorrect value is got before the error message shows (GetDataAsVariant function), this value retrieves time in 24 hours format, i.e. 22:00:00.

Please advise how to correct this so that we can use AM/PM time format. Maybe you could provide a fix for this error?

Thank you.

[AlexP]

Hello,

It seems that the SQL Native Client provider is not used by your application.
The point is that standard OLEDB provider processes datetime fields only as string fields.
But to map the DATE and TIME SQL Server types to TDateField and TTimeField, you should use this provider.
Try to set the TMSConnection.Options.Provider property to the prNativeClient value.

[createmark]

Thank you for your answer.

But I need to use exactly prSQL cause SQL Native Client (MS SQL Server 2012) can be installed only on Vista or above. So to support WinXP I use prSQL.

As I indicated in my first message the problem is in components that does not check for AM/PM format and it brings to this error.

[azyk]

We have tried again to reproduce the issue described in your first post on SDAC 6.11.22. We set American regional settings on Windows (including AM/PM time format). We set the standard OLE DB provider in connection settings, and applied mapping rules to the dataset:

Code: Select all

  MSConnection.Options.Provider := prSQL;
  MSQuery.DataTypeMap.AddFieldNameRule('TimeField', ftTime);

The described error wasn't reproduced. Please update your SDAC to the version 6.11.22 and let us know if the error is still reproduced on this version.

[createmark]

Unfortunately, on 6.11.22 the problem still occurs.

I did a project on which you can observe this problem:

www.createmark.com/exchange/test.zip

Just to show the problem www.createmark.com/exchange/time_problem.png

Also, for the latest version of the component, I found bug.
If MSQuery ParamCheck is false such SQL:

{CALL System.uspExecuteXSQL (:AXSQL)}

occurs to error message “List index out of bounds (0).” on open/close SQL dialog or open form.

Just to show the problem www.createmark.com/exchange/bug.png

[azyk]

We have reproduced the problem with your sample. According to the specification -
http://technet.microsoft.com/en-us/libr ... .110).aspx , this behavior is due to that SQL Server expects for string data in the 'hh:mm:ss' format for the time data type, and in your case data is transferred as 'h:mm:ss tt'.

The 'List index out of bounds (0).' error, that occurred on opening the SQL property of the TMSQuery component in design time when the TMSQuery.ParamCheck property is set to False, was fixed in SDAC 6.11.23. This version is available for download at our website: http://www.devart.com/sdac/download.html . Please download and update your SDAC verison.

[createmark]

Thank you for your update.

And could you please detect AM/PM format in time (when it mapped from TWideString to TTime) and convert to the 'hh:mm:ss' format on posting to database? I spent a lot of time to find a place to fix the problem. I think it have to be fixed because in American format of time it always raises error.

[azyk]

This fix will be included to the next SDAC build.

[createmark]

Thank you very much, it's very good news.

[createmark]

Two months later, this problem still persists in 7.0.1. Could you please fix this problem.

[azyk]

We have already fixed this problem. This fix will be included in the next SDAC build. If you want to get this fix before the next SDAC version is released, please provide your license number to andreyz*devart*com and we will send you a night build.