Thread: Re: ODBC driver and Dates
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.
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")?
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.
Byron
P.S., Please send these notes to the pgsql-interfaces@postgreSQL.org list so
that everyone can read them, including me.
Hannu Krosing wrote:
> Stephen Davies wrote:
> >
> > > The driver at
> > > > http://www.insightdist.com/download/
> > > > works great. Very well done and much quicker than previous drivers.
> > > >
> > > > I have had a couple of problems though. The problems seem to be with
> >
> > G'day all.
> >
> > I too have had good success with this driver except for one MAJOR problem:
> >
> > it does not seem to handle date fields at all well.
>
> To get a uniform date access across different databases one should
> first issue a query like "set DateStyle to 'ISO'" or some other that the
> driver is developed for
>
> I routinely do it always when accessin PostgreSQL. I think it would be
> nice of the driver to always start the session by settin the DateStyle
> to some known value
>
> Does it not do that already?
>
>
> Hannu
Byron Nikolaidis <byronn@insightdist.com> writes:
> 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")?
I beleave there is no need for such an option. The only thing is
needed that ODBC-driver and Postgres use the same format during
connection. Thus opening connection ODBC-driver should always set
the datestyle which it uses. Asking the datestyle from backend and
than using appropriate conversion is posible but why to bother with
this while the opposite (instructing backend what datestyle to use)
is much simpler to implement and will work equaly well?
Aleksey
--
Aleksey Demakov
avd@avd.garsib.ru
Surely the way to go is to retrieve the date style from Windows. That is, use
the setting in Control Panel Regional Settings.
This can also be used to pick up decimal point and currency settings.
Cheers,
Stephen.
> Byron Nikolaidis <byronn@insightdist.com> writes:
>
> > 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")?
>
> I beleave there is no need for such an option. The only thing is
> needed that ODBC-driver and Postgres use the same format during
> connection. Thus opening connection ODBC-driver should always set
> the datestyle which it uses. Asking the datestyle from backend and
> than using appropriate conversion is posible but why to bother with
> this while the opposite (instructing backend what datestyle to use)
> is much simpler to implement and will work equaly well?
>
> Aleksey
>
> --
> Aleksey Demakov
> avd@avd.garsib.ru
========================================================================
Stephen Davies Consulting scldad@sdc.com.au
Adelaide, South Australia. Voice: 61-8-82728863
Computing & Network solutions. Fax: 61-8-82741015
Stephen Davies <scldad@sdc.com.au> writes:
>
> Surely the way to go is to retrieve the date style from Windows. That is, use
> the setting in Control Panel Regional Settings.
>
> This can also be used to pick up decimal point and currency settings.
Hm... I have to disagree. I see things another way.
All data including dates are transferred in three steps:
1) ODBC driver gets it from Postgres;
2) ODBC driver converts data and passes it to an application;
3) application presents data to user;
It's a duty of applications to represent data accordingly to
Windows settings (using library functions, of course).
The data format on the second step (between ODBC and app)
is defined by the ODBC spec.
The only thing which should be fixed in the driver is defining
the data format on the first step. This step is completely invisible
for end-users and the exact format doesn't matter. It just should be
defined.
> > Byron Nikolaidis <byronn@insightdist.com> writes:
> >
> > > 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")?
> >
> > I beleave there is no need for such an option. The only thing is
> > needed that ODBC-driver and Postgres use the same format during
> > connection. Thus opening connection ODBC-driver should always set
> > the datestyle which it uses. Asking the datestyle from backend and
> > than using appropriate conversion is posible but why to bother with
> > this while the opposite (instructing backend what datestyle to use)
> > is much simpler to implement and will work equaly well?
--
Aleksey Demakov
avd@avd.garsib.ru
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
Hi all, I read in the driver release notes that postgres 6.3.1 password authentication is supported. I'm trying to use this authentication scheme using Access 97 but i get this error: ODBC: connection to p_rojas failed (or something like that, i get the error in spanish) This is my entry in the pg_hba.conf: host all 192.168.38.16 255.255.255.255 password And i can connect via JDBC from the same machine i'm testing ODBC. Have you tested password connections? Antonio Garcia Mari Mallorca (Spain)
> > 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. > Ahah!! A whole new ball game:-)) This indeed sounds very good. The caveat is that "we poor dumb users" should not need/be allowed to fiddle with the registry. Something like an "Advanced Features" button on the DSN definition form that allowed the entry of these commands would be an excellent feature I reckon. It even fits into the ODBC model as these can be seen as "connection attributes" that are being externalised for apps such as MS Query that have no other mechanism for setting such attributes. Cheers, Strephen ======================================================================== Stephen Davies Consulting scldad@sdc.com.au Adelaide, South Australia. Voice: 61-8-82728863 Computing & Network solutions. Fax: 61-8-82741015
Hi Hackers,
On 17 Apr 1998, Aleksey Demakov wrote:
> Byron Nikolaidis <byronn@insightdist.com> writes:
>
> > 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")?
>
> I beleave there is no need for such an option. The only thing is
> needed that ODBC-driver and Postgres use the same format during
> connection. Thus opening connection ODBC-driver should always set
> the datestyle which it uses. Asking the datestyle from backend and
> than using appropriate conversion is posible but why to bother with
> this while the opposite (instructing backend what datestyle to use)
> is much simpler to implement and will work equaly well?
I cannot aggree fully.
In my applications, there are many data imports and exports from the Unix
side and Windows side. They normally should look identically.
Another way of representing data is often the pure ascii way - i. e. how
it is sent from the server (e. g. in the older PostODBC versions). A
simple text viewer may be used the display the data.
I personally would like to controll the transfer and translations as data
source options.
E.g. a radio button array:
Date representation:
Autodetect [X]
European [ ]
ISO Style [ ]
ASCII [ ]
etc...
IMHO the way Byron implemented special featured is great, but these should
be DOCUMENTED features. An input field in the data source setup dialog
could be used to define these "pre-connect statements". And I think there
are some other UNDOCUMENTED features, which would be very usefull (take a
look at the source)!
And last, some type of mapping table could be used to controll the
different *CHAR* mappings between the server and the frontend applications
(but I don't have a detailed idea how to implement this). This could be
the end of the NAME<>CHAR<>BPCHAR<>VARCHAR<>LONGVARCHAR... discussions.
Bye
Gerhard
+-----------------+ +--- gerhardr@tech-edv.co.at ---+
| Technische EDV \ Reithofer / Technical Sofware Developement |
| A-2136 Laa/Thaya \ Gerhard / Tel +43-2522/8726 +-------------+
| Staatsbahnstr. 100 +-------+ Fax +43-2522/87268 |
+----- http://members.aon.at/tech-edv/Info -------+
Gerhard Reithofer wrote:
>
> Hi Hackers,
>
> On 17 Apr 1998, Aleksey Demakov wrote:
>
> > Byron Nikolaidis <byronn@insightdist.com> writes:
> >
> > > 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")?
> >
> > I beleave there is no need for such an option. The only thing is
> > needed that ODBC-driver and Postgres use the same format during
> > connection. Thus opening connection ODBC-driver should always set
> > the datestyle which it uses. Asking the datestyle from backend and
> > than using appropriate conversion is posible but why to bother with
> > this while the opposite (instructing backend what datestyle to use)
> > is much simpler to implement and will work equaly well?
>
> I cannot aggree fully.
>
> In my applications, there are many data imports and exports from the Unix
> side and Windows side. They normally should look identically.
>
> Another way of representing data is often the pure ascii way - i. e. how
> it is sent from the server (e. g. in the older PostODBC versions). A
> simple text viewer may be used the display the data.
It has nothing to do with representation, but how data is transferred
between client and server. The representation is done et a completely
different level. It is due to PostgreSQL lacking a common universal
binary transfer format that these problems have emerged in the first
place - there is no sharp distinction between internal protocol and
external representation.
I think it was not meant to be that way at first, as there are special
placed reserved in pg_type for both type conversion functions :
typinput/typoutput (presumably machine dependent ones) and
typreceive/typsend (presumably network-neutral ones),
somehow they are always the same .
I think that getting a common network-neutral binary format should also
be in the TODO. Bruce?
> I personally would like to controll the transfer and translations as data
> source options.
It is much better to have _one_ encoding on wire. It adds _nothing_ but
complexity if you have several encodings there - the only way to see it
is from some debugging logs anyway.
> IMHO the way Byron implemented special featured is great, but these should
> be DOCUMENTED features.
After Byrons letter they are ;)
> An input field in the data source setup dialog
> could be used to define these "pre-connect statements". And I think there
> are some other UNDOCUMENTED features, which would be very usefull (take a
> look at the source)!
>
> And last, some type of mapping table could be used to controll the
> different *CHAR* mappings between the server and the frontend applications
> (but I don't have a detailed idea how to implement this). This could be
> the end of the NAME<>CHAR<>BPCHAR<>VARCHAR<>LONGVARCHAR... discussions.
That would be a nice feature indeed!
Hannu
On Sat, 18 Apr 1998, Hannu Krosing wrote:
> Gerhard Reithofer wrote:
> >
> > Hi Hackers,
> >
... deleted some older stuff ...
> > I cannot aggree fully.
> >
> > In my applications, there are many data imports and exports from the Unix
> > side and Windows side. They normally should look identically.
> >
> > Another way of representing data is often the pure ascii way - i. e. how
> > it is sent from the server (e. g. in the older PostODBC versions). A
> > simple text viewer may be used the display the data.
>
> It has nothing to do with representation, but how data is transferred
> between client and server. The representation is done et a completely
> different level. It is due to PostgreSQL lacking a common universal
> binary transfer format that these problems have emerged in the first
> place - there is no sharp distinction between internal protocol and
> external representation.
>
> I think it was not meant to be that way at first, as there are special
> placed reserved in pg_type for both type conversion functions :
> typinput/typoutput (presumably machine dependent ones) and
> typreceive/typsend (presumably network-neutral ones),
> somehow they are always the same .
I still cannot agree!
I didn't study V6.3 in detail, but at least until 6.2 all user data has
been transfered in an ASCII byte by byte order (not mentioning blobs).
Translation into a platform dependant representation (like a binary
date...) is always done inside the (ODBC) driver. That's the reason why we
have all the mapping disussions - of the char types, date/time, money ...
The main problem of any ODBC implementation is that you have all freedom
to implement almost any conversion as you like - and many programmers,
incl. MS itself - do not follow common rules, but they EXPECT a specific
translation. This is the reason for many errors which are mostly specific
to ONE application in ONE version on ONE platform.
> I think that getting a common network-neutral binary format should also
> be in the TODO. Bruce?
... an old, old discussion - remembering byte order... ;-)
> > And last, some type of mapping table could be used to controll the
> > different *CHAR* mappings between the server and the frontend applications
> > (but I don't have a detailed idea how to implement this). This could be
> > the end of the NAME<>CHAR<>BPCHAR<>VARCHAR<>LONGVARCHAR... discussions.
>
> That would be a nice feature indeed!
This could be a common approach and should also include date/time/money...
but would make much, much work :-(
I think we have the same goal but are just discussing about the way :-)
Bye
Gerhard
+-----------------+ +--- gerhardr@tech-edv.co.at ---+
| Technische EDV \ Reithofer / Technical Sofware Developement |
| A-2136 Laa/Thaya \ Gerhard / Tel +43-2522/8726 +-------------+
| Staatsbahnstr. 100 +-------+ Fax +43-2522/87268 |
+----- http://members.aon.at/tech-edv/Info -------+
Gerhard Reithofer wrote: > > > > > I think it was not meant to be that way at first, as there are special > > placed reserved in pg_type for both type conversion functions : > > typinput/typoutput (presumably machine dependent ones) and > > typreceive/typsend (presumably network-neutral ones), > > somehow they are always the same . > > I still cannot agree! > > I didn't study V6.3 in detail, but at least until 6.2 all user data has > been transfered in an ASCII byte by byte order (not mentioning blobs). This is because ASCII is for now the only platform independant format ;( There is at least one place where BINARY cursor helps - namely determining the real defined maximum length on varchar fields. > Translation into a platform dependant representation (like a binary > date...) is always done inside the (ODBC) driver. That's the reason why we > have all the mapping disussions - of the char types, date/time, money ... We would need the mappings anyhow, even if it would ne and INT<->INT mapping. > The main problem of any ODBC implementation is that you have all freedom > to implement almost any conversion as you like - and many programmers, > incl. MS itself - do not follow common rules, but they EXPECT a specific > translation. This is the reason for many errors which are mostly specific > to ONE application in ONE version on ONE platform. They not only expect a specific translation but they even have a date datatype ;) What I was advocating, was to always force the driver-level transfers in the same format, not just guess and use what the locale specific format happens to be. > > I think that getting a common network-neutral binary format should also > > be in the TODO. Bruce? > ... an old, old discussion - remembering byte order... ;-) The byte order (which ended up the non-standard way) was about the protocol itself, not the data which should not be fixed but extensible. > > > And last, some type of mapping table could be used to controll the > > > different *CHAR* mappings between the server and the frontend applications > > > (but I don't have a detailed idea how to implement this). This could be > > > the end of the NAME<>CHAR<>BPCHAR<>VARCHAR<>LONGVARCHAR... discussions. > > > > That would be a nice feature indeed! > > This could be a common approach and should also include date/time/money... > but would make much, much work :-( So it is better to _not_ include anything we don't absolutely need at the protocol level. The less clutter we have here the better. I repeat once more - this has nothing to do with what the user sees or what the database stores. It is just what is transferred over wire. ----- Hannu
> It has nothing to do with representation, but how data is transferred > between client and server. The representation is done et a completely > different level. It is due to PostgreSQL lacking a common universal > binary transfer format that these problems have emerged in the first > place - there is no sharp distinction between internal protocol and > external representation. > > I think it was not meant to be that way at first, as there are special > placed reserved in pg_type for both type conversion functions : > typinput/typoutput (presumably machine dependent ones) and > typreceive/typsend (presumably network-neutral ones), > somehow they are always the same . > > I think that getting a common network-neutral binary format should also > be in the TODO. Bruce? This is an interesting idea. It would be good to have more discussion before I put something on the TODO list. Not sure exactly where we are going with this. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Bruce Momjian <maillist@candle.pha.pa.us> writes: > > I think that getting a common network-neutral binary format should also > > be in the TODO. Bruce? > > This is an interesting idea. It would be good to have more discussion > before I put something on the TODO list. Not sure exactly where we are > going with this. Yes. It would be interesting. And I have even more interesting (or stupid) idea on this. I have recently read some introductory materials on CORBA and learned about IIOP which was designed as universal network protocol. Actually I haven't yet go deep into this CORBA stuff and can't judge about it. But from what I know it looks promising. If you have a CORBA interface you no longer have to care about numerous network issues and different language mappings. So I think CORBAification of PostgreSQL would be great. Crazy, yeh? Aleksey -- Aleksey Demakov avd@avd.garsib.ru