Thread: possible ODBC bug with '-infinity'
If just ran across this today and thought I should mention it just in case it wasn't already known. ODBC like: .. DEFAULT 'infinity', ... ODBC doesn't like: .. DEFAULT '-infinity', ... Regards, Richard Broersma Jr.
--- On Fri, 11/30/07, Greg Cocks <gcocks@stoller.com> wrote: > Using a MS product? Is this *maybe* related to the same > 'issue' with > NULLs from PostgreSQL to MS products i.e., in the > <postgresql.conf> > file, turning 'transform_null_equals' to > 'on'? > > Likely not, but thought I would put it out there... :-) > .. DEFAULT '-infinity', ... I should probably clarify what I am seeing. I have a table that has a time stamp field. The timestamp fields have two special values in postgres: 'infinity' which implies '+infinity' and '-infinity' when I create a recordset of records that have the value 'infinity' in the timestamp field all is well. The client sidetimestamp displays '9999-12-31 .... '. However, when I try to create a recordset that has field that contains the '-infinity' values I get an error message. Regards, Richard Broesma Jr.
Richard Broersma Jr wrote: > --- On Fri, 11/30/07, Greg Cocks <gcocks@stoller.com> wrote: > >> Using a MS product? Is this *maybe* related to the same >> 'issue' with >> NULLs from PostgreSQL to MS products i.e., in the >> <postgresql.conf> >> file, turning 'transform_null_equals' to >> 'on'? >> >> Likely not, but thought I would put it out there... :-) >> .. DEFAULT '-infinity', ... > > I should probably clarify what I am seeing. > > I have a table that has a time stamp field. The timestamp fields have two special values in postgres: > 'infinity' which implies '+infinity' > and '-infinity' ODBC doesn't have such a concept. > when I create a recordset of records that have the value 'infinity' in the timestamp field all is well. The client sidetimestamp displays '9999-12-31 .... '. Though I have no confidence, I'm doing so. Do you think it is an appropriate handling ? > However, when I try to create a recordset that has field that contains the '-infinity' values I get an error message. How do you think the driver should treat -infinity ? regards, Hiroshi Inoue
--- On Sun, 12/2/07, Hiroshi Inoue <inoue@tpf.co.jp> wrote: > Do you think it is an appropriate handling ? This is a good question. I am not sure what an appropriate handling would be. My first test of '-infinity' (which resultedin a raised error) was with an ado recordset. Since you are questioning what is appropriate I thought that I shouldtry a few different client front ends to see how they treated (+/-)infinity: Client | -infinity | + infinity ADO | <error> |9999-12-31 23:59:59| Access/LT|1899-11-30 00:00:00|9999-12-31 23:59:59| OOB(ODBC)|0001-01-03 00:00:00|9999-12-31 23:59:59| ADO = ado recordset created in VB Access/LT = a linked tabled in MS-Access (probably DAO/ODBC?) OOB(ODBC) = Open Office.org Base ODBC connection to the Database. Having done further tests, it seems that the error must be a bug with ADO since other clients have not a problem with making-infinity equal to the smallest time stamp that their respective data type can display. In my case, I do not reallycare about what time stamp values are displayed by the clients for (+/-) infinity since I use these values as an impliedNULL value for time stamps. Unfortunately, ADO is broken and can't display any value for -infinity. > How do you think the driver should treat -infinity ? It seems that it already handles it correctly. I was mistaken in thinking that the translation of (+/-) infinity was handledby ODBC. :o) Regards, Richard Broersma Jr.
Hello. Richard Broersma Jr wrote: > --- On Sun, 12/2/07, Hiroshi Inoue <inoue@tpf.co.jp> wrote: > > >> Do you think it is an appropriate handling ? >> > > This is a good question. I am not sure what an appropriate handling would be. My first test of '-infinity' (which resultedin a raised error) was with an ado recordset. Since you are questioning what is appropriate I thought that I shouldtry a few different client front ends to see how they treated (+/-)infinity: > > Client | -infinity | + infinity > ADO | <error> |9999-12-31 23:59:59| > Access/LT|1899-11-30 00:00:00|9999-12-31 23:59:59| > OOB(ODBC)|0001-01-03 00:00:00|9999-12-31 23:59:59| > > ADO = ado recordset created in VB > Access/LT = a linked tabled in MS-Access (probably DAO/ODBC?) > OOB(ODBC) = Open Office.org Base ODBC connection to the Database. > > Having done further tests, it seems that the error must be a bug with ADO since other clients have not a problem with making-infinity equal to the smallest time stamp that their respective data type can display. In my case, I do not reallycare about what time stamp values are displayed by the clients for (+/-) infinity since I use these values as an impliedNULL value for time stamps. Unfortunately, ADO is broken and can't display any value for -infinity. > > >> How do you think the driver should treat -infinity ? >> > > It seems that it already handles it correctly. I was mistaken in thinking that the translation of (+/-) infinity was handledby ODBC. :o) > It should be handled by ODBC because of possible type conversions on the ODBC driver layer. Take a look: http://msdn2.microsoft.com/en-us/library/ms709280aspx. Table shows required conversions. Also all Microsoft applications notify ODBC driver by setting additional parameter during initialization. I think ADO requests 'inifinity' value as SQL_C_CHAR and sure it doesn't expect it to be 'infinity'. > Regards, > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
--- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote: > http://msdn2.microsoft.com/en-us/library/ms709280aspx. I tried this link, but is says "content not found".
Hello, Richard Broersma Jr wrote: > --- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote: > > >> http://msdn2.microsoft.com/en-us/library/ms709280aspx. >> > > I tried this link, but is says "content not found". > Oh... Sorry, missed a dot before 'aspx'. Correct link: http://msdn2.microsoft.com/en-us/library/ms709280.aspx > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
--- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote: > Also all Microsoft applications notify ODBC driver > by setting additional parameter during initialization. I > think ADO requests 'inifinity' value as SQL_C_CHAR and sure > it doesn't expect it to be 'infinity'. Actually, postgresql doesn't actually have a (+/-) infinity value. Notice: http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#AEN4999 +infinity = 5874897 AD -infinity = 4713 BC so the problem must be that ADO doesn't know what to do with dates as small as 4713 BCE.
Richard Broersma Jr wrote: > --- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote: > > >> Also all Microsoft applications notify ODBC driver >> by setting additional parameter during initialization. I >> think ADO requests 'inifinity' value as SQL_C_CHAR and sure >> it doesn't expect it to be 'infinity'. >> > > Actually, postgresql doesn't actually have a (+/-) infinity value. Notice: > http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#AEN4999 > > +infinity = 5874897 AD > -infinity = 4713 BC > Are you sure these values are equal? Try this in PgAdmin: create table t (t timestamp); insert into t values ('-infinity'); insert into t values ('infinity'); insert into t values ('9999-12-31 23:59:59'); insert into t values ('5874897-12-31 23:59:59'); insert into t values ('5874897-12-31 23:59:59.999999999'); --insert into t values ('5874898-01-01 00:00:00'); select t::date, t::time, t::timestamp, t::varchar from t; Cool behaviour: PostgreSQL 8.2.x thinks that 'infinity' = '-infinity' = null if it was converted from timestamp to date or time.... By the way - you can't 'insert into t values ('5874898-01-01 00:00:00')', but you can 'insert into t values ('5874897-12-31 23:59:59.999999999')' - with interesting result ;) > so the problem must be that ADO doesn't know what to do with dates as small as 4713 BCE. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
--- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:
> Are you sure these values are equal? Try this in PgAdmin:
>
> create table t (t timestamp);
> insert into t values ('-infinity');
> insert into t values ('infinity');
> insert into t values ('9999-12-31 23:59:59');
> insert into t values ('5874897-12-31 23:59:59');
> insert into t values ('5874897-12-31
> 23:59:59.999999999');
> --insert into t values ('5874898-01-01 00:00:00');
> select t::date, t::time, t::timestamp, t::varchar from t;
Now the real question is, "What does and ODBC client do with these value?".
Also, don't forget to include values for -infinity and other timestamps that are smaller than what the client side
applicationcan display.
Hello, Happy New Year!
Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:
>
>
>> Are you sure these values are equal? Try this in PgAdmin:
>>
>> create table t (t timestamp);
>> insert into t values ('-infinity');
>> insert into t values ('infinity');
>> insert into t values ('9999-12-31 23:59:59');
>> insert into t values ('5874897-12-31 23:59:59');
>> insert into t values ('5874897-12-31
>> 23:59:59.999999999');
>> --insert into t values ('5874898-01-01 00:00:00');
>> select t::date, t::time, t::timestamp, t::varchar from t;
>>
>
> Now the real question is, "What does and ODBC client do with these value?".
>
> Also, don't forget to include values for -infinity and other timestamps that are smaller than what the client side
applicationcan display.
>
ODBC supports dates and timestamps as:
typedef struct tagTIMESTAMP_STRUCT
{
SQLSMALLINT year;
..........
} TIMESTAMP_STRUCT;
So, it can't show dates bigger then 32,767 or smaller then -32,768.
I think - correct driver behavour should be:
1) driver returns NULL value for SQL_DATE_STRUCT and SQL_TIMESTAMP_STRUCT
2) function return value should be SQL_SUCCESS_WITH_INFO;
3) driver also sets Informative Description for this error with actual
value - if possible.
--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/