[DB-SIG] Problem with mxODBC

[Ryan Weisenberger]

I'm CCing this to the db-sig mailing list in case anyone has any extra 
comments.

There seems to be a problem with mxODBC when it tries to get large data 
types.  In the following example, the data type is "image" under SQL Server 
2000.  This is a SQL_LONGVARBINARY with precision of 2147483647.  The 
problem also occurs with "ntext", which is a SQL_WLONGVARCHAR.

The mxODBCursor_GetData fails when the large data type is not the last 
column in the SELECT statement.  So while this works:
select categoryname, picture from categories
this does not:
select picture, categoryname from categories

The second SELECT statement reports:
'[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index'
when it tries to fetch the first record.

The work around seems to be to disable the GetData function for the 
specific datatype.  So in mxODBCursor_AllocateOutputVars after:
case SQL_LONGVARBINARY:
I set vars[i].use_getdata to 0, and the data is returned fine.

Is there a problem with this work around?  I'm including my sample program, 
and the debug output from mxODBC.

Thanks,
Ryan

import mxODBC
db = mxODBC.Connect('sqlserver')
c = db.cursor()
c.execute('select categoryname, picture from categories')
data = c.fetchone()
c.execute('select picture, categoryname from categories')
data = c.fetchone()
db.close()

Log:
--- New Log Session --- Thu Jan 25 10:03:22 2001

Importing the mxDateTime C API...
  module found
  API object found
  API object initialized.
sizeof(mxODBCursor_Variable)=76
mxODBC_New: server='sqlserver', uid='', passwd='', clearAC=1
mxODBC_New: created new connection at 0x7d2f40
mxODBCursor_New: created new cursor '<no name>' at 0x7d9fa0
mxODBCursor_Execute: preparing statement 'select categoryname, picture from 
categories'
mxODBCursor_Execute: number of params in statement: 0
mxODBCursor_Execute: executing command without parameters
mxODBCursor_FreeVars: called for cursor at 0x7d9fa0
mxODBCursor_PrepareOutput: colcount=2 rowcount=-1
mxODBCursor_PrepareOutput: column 0: name='categoryname' type=-9 
precision=15 scale=0 nullable=0
mxODBCursor_PrepareOutput: column 1: name='picture' type=-4 
precision=2147483647 scale=0 nullable=1
mxODBCursor_AllocateOutputVars: binding column 0 - sqltype=-9, ctype=1, 
free=1, maxlen=16
mxODBCursor_AllocateOutputVars:   true len=16
mxODBCursor_AllocateOutputVars: binding column 1 - sqltype=-4, ctype=-2, 
free=1, maxlen=1024
mxODBCursor_Fetch: fetching 2 column(s).
mxODBCursor_Fetch:  column 0...
mxODBCursor_Fetch:  column 1...
mxODBCursor_GetData: received 10746 (max. 1024) bytes, buf size = 1024, 
offset = 0, ctype = -2
mxODBCursor_GetData: received 9722 (max. 1024) bytes, buf size = 2048, 
offset = 1024, ctype = -2
mxODBCursor_GetData: received 8698 (max. 2048) bytes, buf size = 4096, 
offset = 2048, ctype = -2
mxODBCursor_GetData: received 6650 (max. 4096) bytes, buf size = 8192, 
offset = 4096, ctype = -2
mxODBCursor_GetData: received 2554 (max. 8192) bytes, buf size = 16384, 
offset = 8192, ctype = -2
mxODBCursor_Execute: preparing statement 'select picture, categoryname from 
categories'
mxODBCursor_Execute: number of params in statement: 0
mxODBCursor_Execute: executing command without parameters
mxODBCursor_FreeVars: called for cursor at 0x7d9fa0
mxODBCursor_FreeVars:  freeing output variable for column 0
mxODBCursor_FreeVars:  freeing output variable for column 1
mxODBCursor_FreeVars:  freeing output variable array
mxODBCursor_PrepareOutput: colcount=2 rowcount=-1
mxODBCursor_PrepareOutput: column 0: name='picture' type=-4 
precision=2147483647 scale=0 nullable=1
mxODBCursor_PrepareOutput: column 1: name='categoryname' type=-9 
precision=15 scale=0 nullable=0
mxODBCursor_AllocateOutputVars: binding column 0 - sqltype=-4, ctype=-2, 
free=1, maxlen=1024
mxODBCursor_AllocateOutputVars: binding column 1 - sqltype=-9, ctype=1, 
free=1, maxlen=16
mxODBCursor_AllocateOutputVars:   true len=16
mxODBCursor_Fetch: fetching 2 column(s).
mxODBCursor_Fetch:  column 0...
mxODBCursor_GetData: received -858993460 (max. 1024) bytes, buf size = 
1024, offset = 0, ctype = -2
mxODBC_ReportError: sqlstate='S1002' code=0 text='[Microsoft][ODBC SQL 
Server Driver]Invalid Descriptor Index' lineno=2198 rc=-1
mxODBCursor_Free: called for cursor at 0x7d9fa0
mxODBCursor_FreeVars: called for cursor at 0x7d9fa0
mxODBCursor_FreeVars:  freeing output variable for column 0
mxODBCursor_FreeVars:  freeing output variable for column 1
mxODBCursor_FreeVars:  freeing output variable array
mxODBCursor_FreeParameters: called for cursor at 0x7d9fa0
mxODBCursor_Close: called for cursor at 0x7d9fa0
mxODBCursor_Close:   stmt cancelled
mxODBCursor_Close:   stmt freed
mxODBC_Free: called for connection at 0x7d2f40
mxODBC_Close: called for connection at 0x7d2f40, closed=0
mxODBC_Close:  disconnect
mxODBC_Close:  disconnect failed; rolling back the transaction
mxODBC_Rollback: Rolling back changes for connection at 0x7d2f40
mxODBC_Close:  disconnect
mxODBC_Close:  free connection