[DB-SIG] Re: Problem with mxODBC

[M.-A. Lemburg]

Ryan Weisenberger wrote:
> 
> 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.

Are you sure ? The data will most likely be truncated...
 
> Is there a problem with this work around?  I'm including my sample program,
> and the debug output from mxODBC.

This is a known problem. It is not really related to mxODBC,
but to the capabilities of the ODBC driver. Some drivers
do not allow mixing the use of SQLGetData() and direct binding
of a result column. I have only seen this happen with MS Access.

The correct work-around for this is to fetch all columns after
the first one which needs SQLGetData (in order to fetch large
data chunks) using the same technique.

The next mxODBC version will have do this automatically provided
that the ODBC driver reveals this problem via the SQLGetInfo() call.

In any case, blob type columns should always be placed at the
end of the result set, since this doesn't trigger the problem
and is more efficient.
 
> 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

-- 
Marc-Andre Lemburg
______________________________________________________________________
Company:                                        http://www.egenix.com/
Consulting:                                    http://www.lemburg.com/
Python Pages:                           http://www.lemburg.com/python/