From: | Adrien de Croy <adrien(at)qbik(dot)com> |
---|---|
To: | Hiroshi Inoue <inoue(at)tpf(dot)co(dot)jp> |
Cc: | "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Various errors in psqlodbc: SQLGetTypeInfo, SQLTables, bigint, and double vs float |
Date: | 2010-11-04 05:39:47 |
Message-ID: | 4CD24723.5020203@qbik.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
Hi again
apologies, I did some more research. Looks like SQL_FLOAT and
SQL_DOUBLE are used for c double types, and SQL_REAL for c float type.
very confusing, but that's not unusual for ODBC.
So I've also managed to solve my bigint issue, by falling back to a
signed __int64 instead of unsigned. I guess there's no plan to
implement unsigned numeric types?
Adrien
On 3/11/2010 9:36 p.m., Hiroshi Inoue wrote:
> Hi,
>
> (2010/11/03 12:22), Adrien de Croy wrote:
>>
>> 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
>
> Because int8 type in PostgreSQL is signed, the precision is 19.
>
>> 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?)
>
> SQL_FLOAT means double precision type. What means signle precision type
> is SQL_REAL.
>
>> 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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Nelson Andre | 2010-11-04 09:09:47 | Re: Errors using psqlodbc |
Previous Message | Ryan Pfeiffer | 2010-11-03 13:35:00 | Re: i have problem with psqlodbc_09_00_0200.zip |