Re: ODBC driver and Dates - Mailing list pgsql-interfaces

Byron Nikolaidis wrote:
>
> Hello,
>
> Yes it is true that there should be something done about supporting other date
> styles.  In making the driver be able to handle all the odbc conversions of
> date, time, and timestamp, assumptions were made about what format they were in.
>
> It would be relatively easy to modify this to allow for other styles.

I don't think that modifying the driver is the right way to go - there
is extemsive date-fiddling functionality already present in Windows,
and duplicating it at wire-protocol level would just confuse things.

I think that the right way would be to force the dates to be in a singe
format on wire.I assume that you use the ASCII protocol, in binary they
probably are, but in a platform-dependent way

Therefore my suggestion to set the date format to 'ISO' - i think it
is easiest to parse being nicely ordered YYYY-MM-DD hh:mm:ss.s+ts

If you have already working US then of course there is no need to change
it, just have a startup command make the backend aware that the dates
should be sent in 'US' format.

There is _no_ need to start parsing the dates in different formats,
it would probably just introduce some subtle bugs.

> Should the driver query the database (i.e., "show datestyle") to see what format
> it should use, -OR-, should it be an option for the datasource, where you select
> what style to use, and the driver sets the style when it makes a connection
> ("set datestyle")?

As it will be invisible to the user anyway, I recomment that you just do
a
"set datestyle to 'US'" (assuming you have debugged it for US dates :)

> HERE IS A POSSIBLY USEFUL BUT UNDOCUMENTED FEATURE OF THE NEW ODBC DRIVER:
>
> By the way, there is a feature in this driver that allows you to send simple
> commands to the driver at connection time.  Its registry key is 'ConnSettings'
> and it can be on a driver level or datasource level, or both.
>
> Here is an example, if you had a datasource called 'co1', then in the registry
> under the HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\co1 you would create a String
> Value called 'ConnSettings' which can contain a list of commands, separated by
> semicolans, to send to Postgres on a successful connection.  Example, "set geqo
> to 'off'; set datestyle to 'ISO' "
>
> You can also set this on a driver level so that for any datasource these
> commands would be set.  That key is
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL
> and it is also called 'ConnSettings'.
>
> It will first send the driver 'ConnSettings' (if any) followed by the datasource
> 'ConnSettings' (if any).
>
> This feature has its limitations.   For example, it can only handle commands;
> not queries that would generate tuples.

Well, that is a useful feature indeed, and I think that this will solve
the whole problem without changing the driver code. But the install
script
should set this to "set Datestyle to 'US';" for all of us poor
non-americans
(and it would'nt hurt those who use the US dates neither).

> P.S., Please send these notes to the pgsql-interfaces@postgreSQL.org list so
> that everyone can read them, including me.

Sorry, I answered to a message in the 'old' postodbc list and forgot to
cc.

Hannu