Skip site navigation (1) Skip section navigation (2)

Further information RE: Possible bug with psqlodbc driver

From: "Brad Wilson" <brad(dot)wilson(at)mnetcorporation(dot)com>
To: "Brad Wilson" <brad(dot)wilson(at)mnetcorporation(dot)com>,<pgsql-interfaces(at)postgresql(dot)org>
Subject: Further information RE: Possible bug with psqlodbc driver
Date: 2003-10-31 02:12:53
Message-ID: 10E7ECBF04FBE344BE0C12C02D9CAFC61C8E59@mnet01.mnet.local (view raw or flat)
Thread:
Lists: 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 'SYSTEM TABLE' 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(at)postgresql(dot)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 previous versions)
	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 'non citrus'
		END
	FROM 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 2
	END	
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(dot)wilson(at)gallery4(dot)org 
http://www.gallery4.org

pgsql-interfaces by date

Next:From: Steffen WittenburgDate: 2003-10-31 09:40:09
Subject: Unwanted "recoding"
Previous:From: Brad WilsonDate: 2003-10-31 02:03:50
Subject: Possible bug with psqlodbc driver

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group