apologies if this is already in the bug tracker (I had a look but didn't
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
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.
In response to
pgsql-odbc by date
|Next:||From: Hiroshi Inoue||Date: 2010-11-03 08:36:39|
|Subject: Re: Various errors in psqlodbc: SQLGetTypeInfo, SQLTables,
bigint, and double vs float|
|Previous:||From: Hiroshi Inoue||Date: 2010-11-02 23:19:27|
|Subject: Re: Errors using psqlodbc|