ODBC support

Richard Broersma Jr | 3 Dec 2007 15:14

Re: possible ODBC bug with '-infinity'

--- On Mon, 12/3/07, Andrei Kovalevski <andyk <at> 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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

headers

Andrei Kovalevski | 3 Dec 2007 15:49

Re: possible ODBC bug with '-infinity'

Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski <andyk <at> 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....

(Continue reading)

headers

Richard Broersma Jr | 3 Dec 2007 16:37

Re: possible ODBC bug with '-infinity'

--- On Mon, 12/3/07, Andrei Kovalevski <andyk <at> 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 application can display.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

headers

Andrei Kovalevski | 3 Jan 2008 17:33

Re: possible ODBC bug with '-infinity'

Hello, Happy New Year!

Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski <andyk <at> 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 application can display.
>   

ODBC supports dates and timestamps as:
    typedef struct tagTIMESTAMP_STRUCT
    {
        SQLSMALLINT    year;
        ..........

(Continue reading)