[unixODBC-support] Different behavior of Linux and Windows PostgreSQL ODBC driver

Luca Devoti Ldevoti at acucorp.com
Fri Nov 24 09:52:13 GMT 2006



Hi all,
I'm testing the Linux ODBC driver for Postgres on Linux RH 7.1.
I have a different behavior when calling SQLGetTypeInfo().
In fact what I get from the Linux driver is that the Char type is a driver defined type while on Windows I get it as a base type.
 
In detail the code that I have for both the Windows and Linux (and that on Windows works great is)
 
        SQLBindCol( hstmt, (UWORD)  1, SQL_C_CHAR,   typename,    (SDWORD) 128,
 &cbLength );
        SQLBindCol( hstmt, (UWORD)  2, SQL_C_SHORT, &sqltype,     (SDWORD)   2,
 &cbLength );
        SQLBindCol( hstmt, (UWORD)  3, SQL_C_LONG,  &Precision,   (SDWORD)   4,
 &cbLength );
        SQLBindCol( hstmt, (UWORD)  6, SQL_C_CHAR,   CreateParms, (SDWORD) 128,
 &cbCPLength );
        SQLBindCol( hstmt, (UWORD) 11, SQL_C_SHORT, &IsMoney,     (SDWORD)   2,
 &cbLength );
        SQLBindCol( hstmt, (UWORD) 12, SQL_C_SHORT, &AutoInc,     (SDWORD)   2,
 &cbAILength );
        SQLBindCol( hstmt, (UWORD) 14, SQL_C_SHORT, &MinScale,    (SDWORD)   2,
 &cbmSLength );
        SQLBindCol( hstmt, (UWORD) 15, SQL_C_SHORT, &MaxScale,    (SDWORD)   2,
 &cbMSLength );
        while ((result = SQLFetch (hstmt)) != SQL_NO_DATA_FOUND) {
                if (result == SQL_ERROR)
                        OdbcSetError (hdbc, hstmt);
                if (result == SQL_SUCCESS ||
                    result == SQL_SUCCESS_WITH_INFO) {
                        switch (sqltype) {
                            case SQL_CHAR:
                                stype = "Char";
                                i = HaveChar;
                                break;
                            case SQL_VARCHAR:
                                stype = "VarChar";
                                i = HaveVarChar;
                                break;
                        [...]
                            case SQL_TIMESTAMP:
                                stype = "TimeStamp";
                                i = HaveTimeStamp;
                                break;
                            default:
                                if (local_f_trace) {
                                        sprintf (scratchstr,
                                                 "Got an unknown type: '%s', '%s', %d", typename,(cbCPLength != SQL_NULL_DATA ? CreateParms :"(nil)"), sqltype);
                                        (*ATraceFunc) (0, 5, scratchstr);
                                }
                                stype = NULL;
                                i = -1;
                                break;
                        }
 
 
The lines printed here are the columns of the result set returned by SQLGetTypeInfo().  The '(nil)' vs. 'max. length' is the Create Parameters column, and tells how to create a column with this type.  The Linux driver says that the string type (char(n)) is a driver-defined type, when it should be a base type.
 
 
Log on Windows
 
OdbcOpenConnection
  SQLConnect, pdsn = 'luca', puid = 'techsup'
 Leaving with a cached connection
 Have BigInt, (int8, (nil), 19, 0, 0, 0, 0)
 Got an unknown type: 'char', '(nil)', -7
 Have Char, (char, max. length, 254, 0, 0, 0, 0)
 Got an unknown type: 'date', '(nil)', 91
 Have Date, (date, (nil), 10, 0, 0, 0, 0)
 Have Decimal, (numeric, precision, scale, 28, 0, 38, 0, 0)
 Have Double, (float8, (nil), 15, 0, 0, 0, 0)
 Have Float, (float8, (nil), 15, 0, 0, 0, 0)
 Have Integer, (int4, (nil), 10, 0, 0, 0, 0)
 Have LongVarBinary, (lo, (nil), -4, 0, 0, 0, 0)
 Got an unknown type: 'text', '(nil)', -1
 Have Numeric, (numeric, precision, scale, 28, 0, 38, 0, 0)
 Have Real, (float4, (nil), 7, 0, 0, 0, 0)
 Have SmallInt, (int2, (nil), 5, 0, 0, 0, 0)
 Got an unknown type: 'time', '(nil)', 92
 Got an unknown type: 'timestamptz', '(nil)', 93
 Got an unknown type: 'time', '(nil)', 10
 Have TimeStamp, (timestamptz, (nil), 19, 0, 6, 0, 0)
 Have TinyInt, (int2, (nil), 5, 0, 0, 0, 0)
 Have VarBinary, (bytea, (nil), 254, 0, 0, 0, 0)
 Have VarChar, (varchar, max. length, 254, 0, 0, 0, 0)
 <SQL
  create table idx1 (idx_1_key char (10) not null, idx_1_alt_key_a char
  (30), idx_1_alt_key_b char (10), idx_1_body char (50))
  EndSQL>
 <SQL
  create unique index Iidx10 on idx1 (idx_1_key)
  EndSQL>
 
Log on Linux
 
OdbcOpenConnection
  SQLConnect, pdsn = 'luca', puid = ''
 Leaving with a cached connection
 Have BigInt, (int8, (nil), 19, 0, 0, 0, 0)
 Got an unknown type: 'char', 'max. length', -7
 Have Char, (char, (nil), 254, 0, 0, 0, 0)
 Have Date, (date, (nil), 10, 0, 0, 0, 0)
 Have Decimal, (numeric, (nil), 1000, 1000, 1000, 0, 0)
 Have Double, (float8, (nil), 15, 0, 0, 0, 0)
 Have Float, (float8, (nil), 15, 0, 0, 0, 0)
 Have Integer, (int4, (nil), 10, 0, 0, 0, 0)
 Have LongVarBinary, (lo, (nil), -4, 0, 0, 0, 0)
 Got an unknown type: 'text', '(nil)', -1
 Have Numeric, (numeric, (nil), 1000, 1000, 1000, 0, 0)
 Have Real, (float4, (nil), 7, 0, 0, 0, 0)
 Have SmallInt, (int2, (nil), 5, 0, 0, 0, 0)
 Got an unknown type: 'time', '(nil)', 10
 Have TimeStamp, (datetime, (nil), 19, 0, 0, 0, 0)
 Have TinyInt, (int2, (nil), 5, 0, 0, 0, 0)
 Have VarBinary, (bytea, (nil), 254, 0, 0, 0, 0)
 Have VarChar, (varchar, max. length, 254, 0, 0, 0, 0)
 <SQL
  create table idx1 (idx_1_key char not null, idx_1_alt_key_a char,
  idx_1_alt_key_b char, idx_1_body char)
  EndSQL>
 <SQL
  create unique index Iidx10 on idx1 (idx_1_key)
  EndSQL>
 A1
 
 
The versions in the game here are:
postgresql-8.1.3     
psqlodbc-08.01.0200      
unixODBC-2.2.12
 
My application is linked to use those unixODBC libraries
 
./unixODBC-2.2.12/Drivers/Postgre7.1/.libs/libodbcpsql.so.2.0.0
./unixODBC-2.2.12/Drivers/Postgre7.1/.libs/libodbcpsql.so.2
./unixODBC-2.2.12/Drivers/Postgre7.1/.libs/libodbcpsql.so
 
 
On Windows I tried ;
Postgresql 8.1.2
PostgreSQL driver 8.1.2
 
A big thank to all who want to respond me.
 
Luca Devoti
System Engineer, Western Region  |  Acucorp Europe s.r.l.
Via F. Coppalati, 8  |  29100 Piacenza (PC), Italy  |  www.acucorp.com/it
T +39 0523.071230  |    F +39 0523.623155




More information about the unixODBC-support mailing list