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

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
createmark
Posts: 6
Joined: Wed 01 Jun 2011 07:54

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

Post by createmark » Tue 16 Sep 2014 11:30

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

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

Post by AlexP » Fri 19 Sep 2014 12:29

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
Posts: 6
Joined: Wed 01 Jun 2011 07:54

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

Post by createmark » Fri 19 Sep 2014 16:54

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

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

Post by azyk » Tue 23 Sep 2014 09:15

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
Posts: 6
Joined: Wed 01 Jun 2011 07:54

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

Post by createmark » Wed 24 Sep 2014 11:46

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

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

Post by azyk » Wed 01 Oct 2014 11:24

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
Posts: 6
Joined: Wed 01 Jun 2011 07:54

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

Post by createmark » Wed 01 Oct 2014 11:44

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

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

Post by azyk » Fri 03 Oct 2014 08:40

This fix will be included to the next SDAC build.

createmark
Posts: 6
Joined: Wed 01 Jun 2011 07:54

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

Post by createmark » Mon 06 Oct 2014 19:22

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

createmark
Posts: 6
Joined: Wed 01 Jun 2011 07:54

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

Post by createmark » Tue 02 Dec 2014 10:13

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

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

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

Post by azyk » Wed 10 Dec 2014 09:14

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.

Post Reply