Further information RE: Possible bug with psqlodbc driver - Mailing list pgsql-interfaces

Further to my last post, I just noticed another error in the commlog
file right up at the start when the statements is first executed:
'Unsupported statement option (Set)'

Below is the start of these such errors:

========================================================================
====
$ tail -f psqlodbc_3292.log -n 0
STATEMENT ERROR: func=PGAPI_SetStmtAttr, desc='', errnum=28,
errmsg='Unsupported statement option (Set)'
------------------------------------------------------------                hdbc=40581264, stmt=40575224, result=0
         manual_result=0, prepare=0, internal=0                bindings=0, bindings_allocated=0
parameters=0,parameters_allocated=0                statement_type=-2, statement='(NULL)'
stmt_with_params='(NULL)'               data_at_exec=-1, current_exec_param=-1, put_data=0                currTuple=-1,
current_col=-1,lobj_fd=-1                maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, 
scroll_concurrency=1                cursor_name=''                ----------------QResult Info
-------------------------------
CONN ERROR: func=PGAPI_SetStmtAttr, 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=1537, buffer_filled_out=0, 
buffer_read_in=1537
conn=40581264, query='select  t.tableowner,  t.tablename,  case substring(t.tablename from 1 for 3)     when 'pg_' then
'SYSTEMTABLE' else 'TABLE       '  end 
from  pg_tables t
where 1=1'   [ fetched 33 rows ]
STATEMENT ERROR: func=PGAPI_GetData, desc='', errnum=14,
errmsg='Received an unsupported type from Postgres.'
------------------------------------------------------------                hdbc=40581264, stmt=40575224,
result=40575064               manual_result=0, prepare=1, internal=0                bindings=40575856,
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=0,
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=1537, buffer_filled_out=0, 
buffer_read_in=1537
STATEMENT ERROR: func=PGAPI_GetData, desc='', errnum=14,
errmsg='Received an unsupported type from Postgres.'
========================================================================
====

The group of entries between "STATEMENT ERROR" (inclusive) and
"STATEMENT ERROR" (exclusive) are then repeated in the log file until
the results are displayed by the client.

Regards,

Brad Wilson - Wireless Applications Manager, Gallery 4
m.Net Corporation , tel: +61 8 8210 2029, mob: 0418 848 275


-----Original Message-----
From: Brad Wilson
Sent: Friday, 31 October 2003 12:34 PM
To: 'pgsql-interfaces@postgresql.org'
Subject: Possible bug with psqlodbc driver


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