FreeTDS Development Group

[ZIGLIO Frediano <Frediano.Ziglio AT vodafoneomnitel.it>]

> > From: Bill Thompson [mailto:thompbil AT exchange.uk.ml.com]
> > Sent: July 31, 2002 11:31 AM
> > 
> > I would like to know the string format that is returned from 
> > the ODBC api,
> > when a datetime column is bound to a character variable (SQL_C_CHAR)
> 
> Bill, 
> 
> Docs on the aforementioned "gadget":
> 
> SQLDriverConnect (ODBC)
> 
> The Microsoft® SQL Server(tm) ODBC driver and the ODBC driver manager
> recognizes the following SQLDriverConnect connection string keywords.
> 
> [snip]
> 
> Regional
>       When yes, the SQL Server ODBC driver uses client settings when
> converting currency, date, and time data to character data. 
> The conversion
> is one way only; the driver does not recognize non-ODBC 
> standard formats for
> date strings or currency values within; for example, a 
> parameter used in an
> INSERT or UPDATE statement. When no, the driver uses ODBC 
> standard strings
> to represent currency, date, and time data that is converted 
> to string data.
> 
> 
> ---
> 
> SQLConfigDataSource (ODBC)
> The Microsoft® SQL Server(tm) ODBC driver supports the following SQL
> Server-specific keyword/value pairs for data source 
> configuration attribute
> strings.
> 
> Keyword Values Description 
> [snip]
> Regional yes
>       Respect client workstation settings for region when 
> converting date,
> time, and currency values to character strings. This setting 
> should only be
> specified for applications that only display data, not for 
> applications that
> process data. 
> 
> ---
> 
> SQLSetStmtAttr (ODBC)
> 
> [snip]
> The SQL Server ODBC driver also supports the following driver-specific
> statement attributes.
> [snip]
> 
> SQL_SOPT_SS_REGIONALIZE
>       The driver uses this attribute to determine data 
> conversion at the
> statement level. The attribute causes the driver to respect the client
> locale setting when converting date, time, and currency 
> values to character
> strings. The conversion is from SQL Server native data types 
> to character
> strings only.
> 
> ValuePtr value Description 
> SQL_RE_OFF Default. 
>       The driver does not convert date, time, and currency data to
> character string data using the client locale setting. 
> 
> SQL_RE_ON 
>       The driver uses the client locale setting when converting date,
> time, and currency data to character string data. 
> 
> 
> Regional conversion settings apply to currency, numeric, 
> date, and time data
> types. The conversion setting is only applicable to: 
> 
> * Output conversions when currency, numeric, date, or time values are
> converted to character strings. 
> * Bulk copy in operations containing character columns when 
> BCP6xFILEFMT is
> also set on. 
> 
> --------------------------------------------------------------
> --------------
> ----
> Note When the statement option SQL_SOPT_SS_REGIONALIZE is on, 
> the driver
> uses the locale registry settings for the current user. The 
> driver does not
> honor the current thread's locale if the application sets it by, for
> example, calling SetThreadLocale.
> 
> Altering the regional behavior of a data source can cause application
> failure. An application that parses date strings and expects 
> date strings to
> appear as defined by ODBC, could be adversely affected by 
> altering this
> value.
> --------------------------------------------------------------
> --------------
> ----
> 
> +++
> 
> All to confirm, as Mr. Harvey said, that the driver *can* convert to a
> locale-derived format, but doesn't have to, and won't always in an
> unsurprising way.  Hope you are as, ahem, amused by this as I am.  
> 
> I want to mention that MS's default format is ISO-specified.  
> I think that's
> another good reason to follow their lead.  
> 
> Regards, 
> 
> --jkl
> 
I test on my machine on all information work as descripted.
Note that that is a recent stuff (VC6++ SP5 do not have such constant for
setting regional settings on macro, I haved to import declaration from sql7
headers...).
Default behaviour is to convert in iso format (yyyy-mm-dd HH:mm:ss.mmm)
The fast thing to do (for 0.6 release) is convert always in iso format and
put a comment for future conversion (we do not support many statement
options...). Also functions for converting data/time/currency is highly
recommended (so we can localize future changes). It seem that dblib have
similar conversion...

freddy77

=================================
"STRICTLY PERSONAL AND CONFIDENTIAL

This message may contain confidential and proprietary material for the sole
use of the intended recipient. Any review or distribution by others is
strictly prohibited. If you are not the intended recipient please contact
the sender and delete all copies.
The contents of this message that do not relate to the official business of
our company shall be understood as neither given nor endorsed by it."

=================================