[IronPython] dbapi parameterization issue (fepy-r7/sqlserver2005)

[Bill Merrill]

Greetings all,

I am using IPCE ipy (not ipy2) to warehouse and analyze large piles  
of data.  I chose it for the dbapi implementation, and it has mostly  
worked as expected.   I chatted with sanxiyn on #ironpython about one  
problem I was having, he offered a work around, and also asked that I  
publish what I'm doing to the list.

I am using fepy-r7's dbapi to connect via odbc to a sql server 2005  
instance.  It mostly works as advertised, but I had one issue with  
using sql parameterization with python long ints.  The module does  
not know how to map a long to the BIGINT column type, and fails with  
an exception.

Here is a table definition and a test program:

'''
CREATE TABLE bignumber (
	bignumber BIGINT
);
'''
import dbapi
import odbc as db

dbapi._load_type(db.assembly,db.typename)
connectstr = 'DSN=dsn;database=db_name;'
sql = '''insert into bignumber (bignumber) values (?)'''
vals = [10123456789] #10 billion and change

dbcon = db.connect(connectstr)
cursor = dbcon.cursor()
cursor.execute(sql, vals)
dbcon.commit()
dbcon.close()

and here is the traceback:

Traceback (most recent call last):
   File z:\scratch\dbapi_test.py, line 16, in Initialize
   File c:\IPCE\Lib\dbapi.py, line 108, in execute
   File , line 0, in ExecuteNonQuery##78
   File System.Data, line unknown, in ExecuteNonQuery
   File System.Data, line unknown, in ExecuteReaderObject
   File System.Data, line unknown, in ExecuteReaderObject
   File System.Data, line unknown, in CalcParameterBufferSize
   File System.Data, line unknown, in PrepareForBind
   File System.Data, line unknown, in ProcessAndGetParameterValue
   File System.Data, line unknown, in FromSystemType
ValueError: No mapping exists from object type IronMath.BigInteger to  
a known managed provider native type.

The work around suggested by sanxiyn, which works, is to import Int64  
and cast the long:

from System import Int64
import dbapi
import odbc as db
dbapi._load_type(db.assembly,db.typename)
connectstr = 'DSN=dsn;database=db_name;'
sql = '''insert into bignumber (bignumber) values (?)'''
vals = [Int64(10123456789)] #10 billion and change
dbcon = db.connect(connectstr)
cursor = dbcon.cursor()
cursor.execute(sql, vals)
dbcon.commit()
dbcon.close()


Thanks to sanxiyn for his help.

-Bill