Обсуждение: 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@nextbus.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@nextbus.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@nextbus.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@nextbus.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@nextbus.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

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 12 February 2002 18:23
> To: Laurette Cisneros
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] time problem with postgres ODBC driver (fwd)
>
> > 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.

Sounds like psqlodbc+
(http://gborg.postgresql.org/project/psqlodbcplus/projdisplay.php) which was
branched off from the main code on 2 March 2001. They released a version
1.0.0 on 1st April.

Regards, Dave.

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@nextbus.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

> -----Original Message-----
> From: Laurette Cisneros [mailto:laurette@nextbus.com]
>
> And to add a bit more to the data gathered for this problem.  I installed
> the latest driver (7.01.00.09) and turned on "MyLog(Debug output)".
>
> I have attached the "MyLog" output for the two problems:
>
> 1)  The mylog_1052.log file is for #1 below (the time gets returned
> correctly).
>
> 2)  The mylog_692.log file is for #2 below (the time gets retured as
> today's date).
>

What column is of type TIME ?
positions.evtime seems to be of type TIMESTAMP.

regards,
Hiroshi Inoue

Same problem with either time or timestamp.  Is these examples evtime is
timestamp but returns today's date.  Sorry for the confusion.

On Fri, 15 Feb 2002, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: Laurette Cisneros [mailto:laurette@nextbus.com]
> >
> > And to add a bit more to the data gathered for this problem.  I installed
> > the latest driver (7.01.00.09) and turned on "MyLog(Debug output)".
> >
> > I have attached the "MyLog" output for the two problems:
> >
> > 1)  The mylog_1052.log file is for #1 below (the time gets returned
> > correctly).
> >
> > 2)  The mylog_692.log file is for #2 below (the time gets retured as
> > today's date).
> >
>
> What column is of type TIME ?
> positions.evtime seems to be of type TIMESTAMP.
>
> regards,
> Hiroshi Inoue
>

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

Hi Hiroshi,

Thank you very much for helping me on this problem...I really do appreciate
it.

This problem is very perplexing to us.

We have the following:

1) Client program - Esri Arcview (version 3.2)
2) MS ODBC (Data Sources) (ODBC Data Sources Administrator, About tab shows
   version 3.520.6019.0 for all components)
3) psqlodbcplus driver (date: 04/01/01) (we tried the trial version
   3.0 but unfortunately it gives us a segmentation violation so we are not
   using it)

I do not completely understand how each of those interact with each other
and I believe that you do...this is why I ask so many questions to maybe
try to isolate where the problem is...in one of those?

In our client application we can connect in one of two ways, using a
menu item a) "Create Database Table" or b) "SQLConnect".  If we turn on ODBC
tracing (via ODBC Data Sources, Tracing tab) for a) we will get a trace log
(like the one I sent to you), for b) we will not get a tracing log - which
tells me that SQLConnect does not go through MS ODBC DSN but rather goes
directly to the postgresql odbc driver...would you agree?

Well, for a) we see this problem for b) we do not.

The problem is that in the client application, what gets displayed for the
time field is the value "20020221" or today's date, not the time value
though clearly via other methods we can see the time value returned.

So, what we are trying to figure out is:
1) is this a problem with the postgresql odbc driver returning a wrong
   "flag" type for the data type, or
2) is MS ODBC DSN translating wrong or,
3)  is it a bug in our client, or
4) Something else?

What I was asking before was, since the trial postgresql odbc 3.0 driver
doesn't work for us and the psqlodbcplus (date: 04/01/01) does
not support ODBC 3.0, I was assuming this meant the MS ODBC DSN was vers.
3.0.  So, I wanted to unintall MS ODBC DSN and install a previous version
that is supported by the psqlodbcplus (date: 04/01/01).  Is this possible?

Also, I re-ran the simple example of a table tst1 that has two columns col1
of type date and col2 of type time.  I have attached the mylog output and
the MS ODBC DSN tracing log to look at.

Thank you again for your help!

On Fri, 22 Feb 2002, Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: Laurette Cisneros [mailto:laurette@nextbus.com]
> >
> > Did this and we get "segmentation violation" when trying to use our client
> > connection with this driver.
>
> Could you send me the mylog output ?
>
> BTW what do you mean by *returns the current date for
> the column of type time* ?
> Using M$ Access I see e.g. 02 02 21 10:21:28 for the field
> of type time. Isn't it the same as your case ?
>
> regards,
> Hiroshi Inoue
>

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