time problem with postgres ODBC driver (fwd)

Repost...sorry if it goes to the list twice.

We are seeing a problem with the ODBC driver.

Using this table:

create table tst
( col1 date,
col2 time);

and inserting values (rows) into the date and time columns.

When using the PostreSQL ODBC driver version 07.01.0007 against both a 7.2
and 7.1.3 database server, a select * from tst returns the date value for
col1 correctly, but returns the current date for col2 (which obviously is
wrong).

Interestingly, we have a system that has (had) a driver we had downloaded
from GreatBridge ODBC and it was working fine until we installed the PostgreSQL
driver onto it.

Is this a known problem? Any ideas?

Thanks,
--
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere

Laurette Cisneros <laurette(at)nextbus(dot)com> writes:
> create table tst
> ( col1 date,
> col2 time);
> When using the PostreSQL ODBC driver version 07.01.0007 against both a 7.2
> and 7.1.3 database server, a select * from tst returns the date value for
> col1 correctly, but returns the current date for col2 (which obviously is
> wrong).

It sounds like ODBC (or the client application) is misinterpreting the
datatype of col2 as being a "date" or "date/time" type not "time of
day". There is a translation between PG internal types and the ODBC
standard's notion of types, so one possible explanation is that there's
something getting lost in translation.

> Interestingly, we have a system that has (had) a driver we had
> downloaded from GreatBridge ODBC and it was working fine until we
> installed the PostgreSQL driver onto it.

This, however, mystifies me. AFAIK GB's ODBC driver wasn't different
from the community's.

regards, tom lane

On Tue, 12 Feb 2002, Tom Lane wrote:

> Laurette Cisneros <laurette(at)nextbus(dot)com> writes:
> > create table tst
> > ( col1 date,
> > col2 time);
> > When using the PostreSQL ODBC driver version 07.01.0007 against both a 7.2
> > and 7.1.3 database server, a select * from tst returns the date value for
> > col1 correctly, but returns the current date for col2 (which obviously is
> > wrong).
>
> It sounds like ODBC (or the client application) is misinterpreting the
> datatype of col2 as being a "date" or "date/time" type not "time of
> day". There is a translation between PG internal types and the ODBC
> standard's notion of types, so one possible explanation is that there's
> something getting lost in translation.

What's even more interesting is that casting doesn't help -
col2::time still returns the current date.

>
> > Interestingly, we have a system that has (had) a driver we had
> > downloaded from GreatBridge ODBC and it was working fine until we
> > installed the PostgreSQL driver onto it.

Us too. The only difference is that that ODBC driver was an older version
than the new one that was downloaded and started this problem (this problem
occurs on another system for which it was the only driver installed).

(The client program is ArcView).

>
> This, however, mystifies me. AFAIK GB's ODBC driver wasn't different
> from the community's.
>
> regards, tom lane
>

Thanks.

--
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere

Laurette Cisneros <laurette(at)nextbus(dot)com> writes:
> On Tue, 12 Feb 2002, Tom Lane wrote:
>> It sounds like ODBC (or the client application) is misinterpreting the
>> datatype of col2 as being a "date" or "date/time" type not "time of
>> day". There is a translation between PG internal types and the ODBC
>> standard's notion of types, so one possible explanation is that there's
>> something getting lost in translation.

> What's even more interesting is that casting doesn't help -
> col2::time still returns the current date.

No, of course it wouldn't --- the column coming out of the backend is
PG's "time" type either way. I'm sure that the unwanted conversion to
a date or datetime value (with, evidently, implicit fill-in of today's
date) is happening on the client side.

I don't know if our ODBC code should be blamed or if it's a
client-application bug. A quick look at the ODBC sources makes it
appear that the ODBC code reports ODBC type code "SQL_TIME" for a
PG "time" column, which seems a reasonable mapping to me, but I'm
no ODBC expert.

> Us too. The only difference is that that ODBC driver was an older version
> than the new one that was downloaded and started this problem

Hmm, so perhaps the problem could have been triggered by a recent "fix".
I looked at 7.1 and 7.2 ODBC sources and they seemed about the same in
this respect. Do you know what version the GB driver was, exactly?

regards, tom lane

The version of the old ODBC driver is: 1.0.0.0

L.
On Tue, 12 Feb 2002, Tom Lane wrote:

> Laurette Cisneros <laurette(at)nextbus(dot)com> writes:
> > On Tue, 12 Feb 2002, Tom Lane wrote:
> >> It sounds like ODBC (or the client application) is misinterpreting the
> >> datatype of col2 as being a "date" or "date/time" type not "time of
> >> day". There is a translation between PG internal types and the ODBC
> >> standard's notion of types, so one possible explanation is that there's
> >> something getting lost in translation.
>
> > What's even more interesting is that casting doesn't help -
> > col2::time still returns the current date.
>
> No, of course it wouldn't --- the column coming out of the backend is
> PG's "time" type either way. I'm sure that the unwanted conversion to
> a date or datetime value (with, evidently, implicit fill-in of today's
> date) is happening on the client side.
>
> I don't know if our ODBC code should be blamed or if it's a
> client-application bug. A quick look at the ODBC sources makes it
> appear that the ODBC code reports ODBC type code "SQL_TIME" for a
> PG "time" column, which seems a reasonable mapping to me, but I'm
> no ODBC expert.
>
> > Us too. The only difference is that that ODBC driver was an older version
> > than the new one that was downloaded and started this problem
>
> Hmm, so perhaps the problem could have been triggered by a recent "fix".
> I looked at 7.1 and 7.2 ODBC sources and they seemed about the same in
> this respect. Do you know what version the GB driver was, exactly?
>
> regards, tom lane
>

--
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere

Laurette Cisneros <laurette(at)nextbus(dot)com> writes:
> The version of the old ODBC driver is: 1.0.0.0

Surely not ... unless GreatBridge decided to start their own numbering,
which seems unlikely. I'd expect a number like 6.40.nnnn or 7.01.nnnn.

regards, tom lane

Here's what it displays in the "Data Sources (ODBC)" listing for
Drivers:

pgsqlodbcplus 7.01.00.00.00 Great Bridge

The date on the dll is 4/21/2001

L.

On Tue, 12 Feb 2002, Tom Lane wrote:

> Laurette Cisneros <laurette(at)nextbus(dot)com> writes:
> > The version of the old ODBC driver is: 1.0.0.0
>
> Surely not ... unless GreatBridge decided to start their own numbering,
> which seems unlikely. I'd expect a number like 6.40.nnnn or 7.01.nnnn.
>
> regards, tom lane
>

--
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere

Laurette Cisneros wrote:
>
> Repost...sorry if it goes to the list twice.
>
> We are seeing a problem with the ODBC driver.
>
> Using this table:
>
> create table tst
> ( col1 date,
> col2 time);
>
> and inserting values (rows) into the date and time columns.
>
> When using the PostreSQL ODBC driver version 07.01.0007 against both a 7.2
> and 7.1.3 database server, a select * from tst returns the date value for
> col1 correctly, but returns the current date for col2 (which obviously is
> wrong).

If you are using 7.01.0007 you can turn on the Mylog
option using ODBC DataSource Administrator.
Please send me the Mylog output for debug.

regards,
Hiroshi Inoue

Ok, here's a lot more information on the problem.

Regardless of trying to remember which driver we used before and if it
worked then or not, we are seeing this problem now. So, we did a lot more
research on it and found got some interesting results.

We are almost certain that the driver we were using before (when we are
almost positive we did not see this problem) was the psqlodbcplus driver
released 04/01/2001. In "Data Sources (ODBC)" / "Drivers" menu, it shows
7.01.00.00 as the version.

Then we loaded on the latest driver which "Data Sources (ODBC)" / "Drivers"
menu shows as version 7.01.00.07, and we started seeing this problem.

So, we re-loaded the old driver (which we no longer had on our system and had
to find and are pretty sure it was the one from 04/01/2001). That is the
driver we are using now.

But wait -

The client we are using, ESRI's ArcView, actually provides 2 ways of
extracting the data from the database.

1) The first way, is using it's menu option "SQLConnect". When we use this
option, the data comes back correct! Yay. But, of course, this is *not*
what we need to do our work. Darn. But it works.

2) The second way, is to use their menu option or "Add Database Table". With
this option, the time data comes back as today's date. This is what we
need to do our work.

So, we turned on the "CommLog (C:\psqlodbc.log)" option for the driver.

I have attached the output from #1 and #2 to this mail:
1) psqlodbc-756.log is the ouput for try #1 (the one that returns the time
correctly).
2) psqlodbc-536.log is the output for try #2 (the one that returns today's
date instead of time).

I can see some differences in the two outputs:
A) The "nDriverCompletion value is different. For #1 it is 1, for #2 it
is 3. What is this value? Is it meaningful / pertinent?

B) The "sqltype" value is different. For #1 it is "40277776", for #2 it
is "47027984". The "sqltype" value is the same for each and every column
but it is different between #1 and #2. Meaningful / pertinent?

C) The driver connection between the two seems to happen differently?

Any ideas?

Thanks for all your help,

Laurette

On Wed, 13 Feb 2002, Hiroshi Inoue wrote:

> Laurette Cisneros wrote:
> >
> > Repost...sorry if it goes to the list twice.
> >
> > We are seeing a problem with the ODBC driver.
> >
> > Using this table:
> >
> > create table tst
> > ( col1 date,
> > col2 time);
> >
> > and inserting values (rows) into the date and time columns.
> >
> > When using the PostreSQL ODBC driver version 07.01.0007 against both a 7.2
> > and 7.1.3 database server, a select * from tst returns the date value for
> > col1 correctly, but returns the current date for col2 (which obviously is
> > wrong).
>
> If you are using 7.01.0007 you can turn on the Mylog
> option using ODBC DataSource Administrator.
> Please send me the Mylog output for debug.
>
> regards,
> Hiroshi Inoue
>

--
Laurette Cisneros
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Passenger Information Everywhere