Possible bug with psqlodbc driver - Mailing list pgsql-interfaces
From | Brad Wilson |
---|---|
Subject | Possible bug with psqlodbc driver |
Date | |
Msg-id | 10E7ECBF04FBE344BE0C12C02D9CAFC61C8E58@mnet01.mnet.local Whole thread Raw |
List | pgsql-interfaces |
Hello, Firstly, software specs:OS: Windows XP Pro (also occurs on Windows 2000)psqlodbc: 07_03_0200 (also occurs with the 10 previousversions)DB: postgresql 7.3 (also occurs with 7.2, not tested on others) I am having a problem when using the "CASE" function over ODBC within a select statement. For example,SELECT a.apples, a.bananas, CASE a.others WHEN 'orange' THEN 'citrus' ELSE 'noncitrus' ENDFROM fruits a; This will result in about 10 retries (as per the ODBC commlog file), and then the final results will contain valid data in the 'apples' and 'bananas' columns, but no data (empty fields) in the 'case' column. Interestingly, if I modify the CASE function to return integers rather than strings, then it will work. Eg,CASE a.others WHEN 'orange' THEN 1 ELSE 2END Will work and return the expected results. Addendum: Some further testing has shown that statements such as the following will also fail with similar errors:SELECT substring (a.apples from 1 for 3) from fruits a; Below is a snippet of the commlog file showing the final two retries of a similar SELECT statement using CASE: ======================================================================== ==== ------------------------------------------------------------ hdbc=40581264, stmt=40575224, result=40575856 manual_result=0, prepare=1, internal=0 bindings=40575064, bindings_allocated=3 parameters=0, parameters_allocated=0 statement_type=0, statement='select t.tableowner, t.tablename, case substring(t.tablename from 1 for 3) when 'pg_' then 'SYSTEM TABLE'else 'TABLE' end from pg_tables t where 1=1' stmt_with_params='select t.tableowner, t.tablename, case substring(t.tablename from 1 for 3) when 'pg_' then 'SYSTEM TABLE' else 'TABLE' end from pg_tables t where 1=1' data_at_exec=-1, current_exec_param=-1, put_data=0 currTuple=31, current_col=-1,lobj_fd=-1 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1 cursor_name='SQL_CUR026B20F8' ----------------QResult Info ------------------------------- fields=40575184, manual_tuples=0, backend_tuples=40601240, tupleField=40601240, conn=40581264 fetch_count=0, num_total_rows=33, num_fields=3, cursor='(NULL)' message='(NULL)', command='SELECT', notice='(NULL)' status=8, inTuples=0 CONN ERROR: func=PGAPI_GetData, desc='', errnum=0, errmsg='(NULL)' ------------------------------------------------------------ henv=40574744, conn=40581264, status=1, num_stmts=16 sock=40574800, stmts=40574872, lobj_type=-999 ---------------- Socket Info ------------------------------- socket=424, reverse=0, errornumber=0, errormsg='(NULL)' buffer_in=40592264,buffer_out=40596368 buffer_filled_in=1509, buffer_filled_out=0, buffer_read_in=1509 STATEMENT ERROR: func=PGAPI_GetData, desc='', errnum=14, errmsg='Received an unsupported type from Postgres.' ------------------------------------------------------------ hdbc=40581264, stmt=40575224, result=40575856 manual_result=0, prepare=1, internal=0 bindings=40575064, bindings_allocated=3 parameters=0, parameters_allocated=0 statement_type=0, statement='select t.tableowner, t.tablename, case substring(t.tablename from 1 for 3) when 'pg_' then 'SYSTEM TABLE'else 'TABLE' end from pg_tables t where 1=1' stmt_with_params='select t.tableowner, t.tablename, case substring(t.tablename from 1 for 3) when 'pg_' then 'SYSTEM TABLE' else 'TABLE' end from pg_tables t where 1=1' data_at_exec=-1, current_exec_param=-1, put_data=0 currTuple=32, current_col=-1,lobj_fd=-1 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1 cursor_name='SQL_CUR026B20F8' ----------------QResult Info ------------------------------- fields=40575184, manual_tuples=0, backend_tuples=40601240, tupleField=40601240, conn=40581264 fetch_count=0, num_total_rows=33, num_fields=3, cursor='(NULL)' message='(NULL)', command='SELECT', notice='(NULL)' status=8, inTuples=0 CONN ERROR: func=PGAPI_GetData, desc='', errnum=0, errmsg='(NULL)' ------------------------------------------------------------ henv=40574744, conn=40581264, status=1, num_stmts=16 sock=40574800, stmts=40574872, lobj_type=-999 ---------------- Socket Info ------------------------------- socket=424, reverse=0, errornumber=0, errormsg='(NULL)' buffer_in=40592264,buffer_out=40596368 buffer_filled_in=1509, buffer_filled_out=0, buffer_read_in=1509===================================================== ======================= If you require any further information, please don't hesitate to ask. Kind Regards, Brad Wilson Wireless Applications Manager - Gallery 4 m.Net Corporation Level 13, 99 Gawler Place Adelaide SA 5000, Australia Tel: +61 8 8210 2029 Fax: +61 8 8211 9620 Mobile: 0418 848 275 mailto:brad.wilson@gallery4.org http://www.gallery4.org
pgsql-interfaces by date: