Skip site navigation (1) Skip section navigation (2)

Various errors in psqlodbc: SQLGetTypeInfo, SQLTables, bigint, and double vs float

From: Adrien de Croy <adrien(at)qbik(dot)com>
To: "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: Various errors in psqlodbc: SQLGetTypeInfo, SQLTables, bigint, and double vs float
Date: 2010-11-03 03:22:44
Message-ID: 4CD0D584.30908@qbik.com (view raw or flat)
Thread:
Lists: pgsql-odbc
Hi all,

apologies if this is already in the bug tracker (I had a look but didn't 
find it).

We recently did some work to get our product working with PostgreSQL 9.0 
and the latest ODBC driver.  Our code is written to (try to) be 
DBMS-agnostic.  So it enumerates reported types, searches for matching 
types etc, and builds CREATE TABLE statements etc from what it finds.

There were a couple of inconsistencies in data returned from the ODBC 
driver.

1. Data returned from SQLGetTypeInfo uses ODBC2 field names.

The columns reported include

"PRECISION" instead of "COLUMN_SIZE"
"MONEY" instead of "FIXED_PREC_SCALE"
"AUTO_INCREMENT" instead of "AUTO_UNIQUE_VALUE"

2. Data returned from SQLTables uses ODBC2 field names

"TABLE_QUALIFIER" instead of "TABLE_CAT"
"TABLE_OWNER" instead of "TABLE_SCHEM"

These 2 issues aren't huge problems, since the ordinal column number 
stays the same.

3. Not all supported types returned by SQLGetTypeInfo

Specifically the types SERIAL and BIGSERIAL are not reported.  This 
means we needed to hard-code a hack so if the driver was PostgreSQL and 
we needed an AUTO_UNIQUE_VALUE counter, we used "SERIAL".  It works, but 
if SQLGetTypeInfo returned these types, it wouldn't need the hack.

4.  Oddness with bigint fields.

bigint reported as precision of 19, instead of 20.  This isn't big 
enough to store an unsigned __int64

5. Oddness with double precision fields.

We had to use double precision fields to store file size information, 
because bigint couldn't do an __int64 (not sure what actual C type this 
really maps to in reality).  However when we get the field data back in 
a query, it is reported as type SQL_FLOAT, even though the DB structure 
in the PostgreSQL manager shows it as double precision.  Obviously you 
don't want to truncate double to float, so is this just in the driver 
(some type switch case not supported?)

Once we worked around all these issues, it seems to be working great.  
I'm a bit concerned about losing precision with double vs float though.

Regards

Adrien

In response to

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2010-11-03 08:36:39
Subject: Re: Various errors in psqlodbc: SQLGetTypeInfo, SQLTables, bigint, and double vs float
Previous:From: Hiroshi InoueDate: 2010-11-02 23:19:27
Subject: Re: Errors using psqlodbc

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group