'''A Pure Python DB-API 2.0 compliant interface to ThinkSQL.
Copyright © 2000-2012 Greg Gaughan
http://www.thinksql.co.uk
'''
from string import split,replace
import struct
import cStringIO
import warnings
import socket
import datetime
try:
from fixedpoint import FixedPoint
have_fixedpoint = True
except ImportError:
have_fixedpoint = False
apilevel='2.0'
threadsafety=1
paramstyle='qmark'
__copyright__ = "(c)Copyright 2000-2012 Greg Gaughan"
__author__ = "Greg Gaughan"
def connect(dsn=None, user=None, password=None, host=None,
database=None, port=None):
'''Create and open a connection.
connect(dsn=None, user='DEFAULT', password='', host='localhost',
database='', port=9075)
Returns a connection object.
'''
#Defaults
_d = {'host':'localhost','port':9075, 'database':'',
'user':'DEFAULT', 'password':''}
#todo Parse any DSN
#Now use any keywords
if (user is not None): _d['user'] = user
if (password is not None): _d['password'] = password
if (host is not None): _d['host'] = host
try:
params = split(host,':')
_d['host'] = params[0]
_d['port'] = params[1]
except:
pass
if (database is not None): _d['database']=database
if (port is not None):_d['port'] = port
return connection(_d['host'], _d['port'], _d['database'],
_d['user'], _d['password'])
#Connection class -------------------------------------------------------------
class connection:
def __init__(self, host=None, port=None, catalog=None,
user=None, password=None):
self.state=_stateClosed
self.marshalBuffer=marshalBuffer(host, port)
self._Server=''
self._Catalog=catalog
self._Username=user
self._Password=password
self._Autocommit=False #Python has it right, unlike ODBC, JDBC etc!
self.resultErrCode=None
self.resultErrText=''
if self.marshalBuffer.sendHandshake()!=_ok: #send raw handshake
raise HandshakeError, str(_seHandshakeFailed)+' '+_seHandshakeFailedText
#Now the server knows we're using CLI protocol we can use the marshal buffer
self.marshalBuffer.putSQLUSMALLINT(_clientCLIversion) #special version marker for initial protocol handshake
if _clientCLIversion>=93:
self.marshalBuffer.putSQLUSMALLINT(_CLI_PYTHON_DBAPI)
if self.marshalBuffer.send()!=_ok:
raise HandshakeError, str(_seHandshakeFailed)+' '+_seHandshakeFailedText
#Wait for handshake response
if self.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self.marshalBuffer.getFunction()
if functionId!=_SQL_API_handshake:
raise HandshakeError, str(_seHandshakeFailed)+' '+_seHandshakeFailedText
self.serverCLIversion=self.marshalBuffer.getSQLUSMALLINT() #note server's protocol
if self.serverCLIversion>=93:
self.serverTransactionKey=self.marshalBuffer.getSQLPOINTER() #note server's transaction key
#Now SQLconnect
self.marshalBuffer.clearToSend()
self.marshalBuffer.putFunction(_SQL_API_SQLCONNECT)
self.marshalBuffer.putSQLHDBC(0) #(int)(self)
if self._Catalog=='':
self.marshalBuffer.putpUCHAR_SWORD(self._Server)
else:
self.marshalBuffer.putpUCHAR_SWORD(self._Server+'.'+self._Catalog)
self.marshalBuffer.putpUCHAR_SWORD(self._Username)
self.marshalBuffer.putpUCHAR_SWORD(self._Password) #todo encrypt
if self.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for read to return the response
if self.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLCONNECT:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self.marshalBuffer.getSQLINTEGER()
self.resultErrText=self.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
#Ok, we're connected
self.state=_stateOpen
def cursor(self):
'''Create a new cursor on the connection.
'''
if self.state!=_stateOpen:
raise InterfaceError, str(_seConnectionNotOpen)+' '+_seConnectionNotOpenText
return cursor(self)
def close(self):
'''Close the connection.
'''
#Negotiate disconnection
if self.state!=_stateOpen:
raise InterfaceError, str(_seConnectionNotOpen)+' '+_seConnectionNotOpenText
self.marshalBuffer.clearToSend()
self.marshalBuffer.putFunction(_SQL_API_SQLDISCONNECT)
self.marshalBuffer.putSQLHDBC(0) #(int)(self)
if self.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for read to return the response
if self.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLDISCONNECT:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self.marshalBuffer.getSQLINTEGER()
self.resultErrText=self.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
#Try to disconnect from the server
self.marshalBuffer.close() #close this connection
self.state=_stateClosed
def commit(self):
'''Commit the current transaction.
'''
#Send SQLendTran to server
self.marshalBuffer.clearToSend()
self.marshalBuffer.putFunction(_SQL_API_SQLENDTRAN)
self.marshalBuffer.putSQLHDBC(0) #todo pass TranId?
self.marshalBuffer.putSQLSMALLINT(_SQL_COMMIT)
if self.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for read to return the response
if self.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLENDTRAN:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self.marshalBuffer.getSQLINTEGER()
self.resultErrText=self.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
def rollback(self):
'''Rollback the current transaction.
'''
#Send SQLendTran to server
self.marshalBuffer.clearToSend()
self.marshalBuffer.putFunction(_SQL_API_SQLENDTRAN)
self.marshalBuffer.putSQLHDBC(0) #todo pass TranId?
self.marshalBuffer.putSQLSMALLINT(_SQL_ROLLBACK)
if self.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for read to return the response
if self.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLENDTRAN:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self.marshalBuffer.getSQLINTEGER()
self.resultErrText=self.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
def __del__(self):
if self.state==_stateOpen:
self.close()
#Cursor class -----------------------------------------------------------------
class cursor(object):
def __init__(self, conn):
'''Open a new cursor (statement) on the specified connection.
'''
self._Con=conn
self.colCount=None
self.col=[]
self.paramCount=None
self.param=[]
self.serverStatementHandle=-1 #=> not connected
self.resultSet=False
self.prepared=False
self._affectedRowCount=None
self.lastSQL=None
self.lastArraySize=1 #server default
self._description=None
self.resultErrCode=None
self.resultErrText=''
#We notify the server of this new command(=stmt)
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLALLOCHANDLE)
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_HANDLE_STMT)
self._Con.marshalBuffer.putSQLHDBC(0) #todo _Con
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLALLOCHANDLE:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
serverS=self._Con.marshalBuffer.getSQLHSTMT() #server will return 0 if failed
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
self.serverStatementHandle=serverS #we will pass this reference to server in future calls
def close(self):
'''Close the cursor.
'''
if self.serverStatementHandle<=0:
pass
else:
self._resultSetClose() #close the server cursor, if any
#Free the server handle
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLFREEHANDLE)
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_HANDLE_STMT)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle) #pass server statement ref
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLFREEHANDLE:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
self.serverStatementHandle=-1 #not connected
def _resultSetClose(self):
if self.resultSet:
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLCLOSECURSOR)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle) #pass server statement ref
if self.prepared:
self._Con.marshalBuffer.putSQLSMALLINT(0) #keep server plan
else:
self._Con.marshalBuffer.putSQLSMALLINT(1) #remove server plan
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLCLOSECURSOR:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
def _doPrepare(self,sql):
self.lastSQL=sql
#call server prepare
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLPREPARE)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle)
self._Con.marshalBuffer.putpUCHAR_SDWORD(sql)
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLPREPARE:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
#raise later...
resSet=self._Con.marshalBuffer.getSQLUSMALLINT()
#Remember this for future state changes
if resSet==_SQL_TRUE:
self.resultSet=True
else:
self.resultSet=False
if self.resultCode==_SQL_ERROR:
raise ProgrammingError, str(self.resultErrCode)+' '+self.resultErrText
if self.resultSet:
#Now get the cursor column count & definitions
self.col=[]
self.colCount=self._Con.marshalBuffer.getSQLINTEGER()
for i in range(self.colCount):
rn=self._Con.marshalBuffer.getSQLSMALLINT()
self.newcol=_columnSQL()
self.newcol.iFldNum=rn
self.newcol.colName=self._Con.marshalBuffer.getpSQLCHAR_SWORD()[:-1] #remove trailing \0
self.newcol.iFldType=self._Con.marshalBuffer.getSQLSMALLINT()
if self._Con.serverCLIversion>=93:
self.newcol.iUnits1=self._Con.marshalBuffer.getSQLINTEGER()
else:
self.newcol.iUnits1=self._Con.marshalBuffer.getSQLSMALLINT()
self.newcol.iUnits2=self._Con.marshalBuffer.getSQLSMALLINT()
self.newcol.iNullOffset=self._Con.marshalBuffer.getSQLSMALLINT()
self.col.append(self.newcol)
#Now set the description
self._description=[]
for newcol in self.col:
if newcol.iFldType==_SQL_NUMERIC or newcol.iFldType==_SQL_DECIMAL:
dsize=newcol.iUnits1+newcol.iUnits2+1
isize=newcol.iUnits1+newcol.iUnits2
elif newcol.iFldType==_SQL_SMALLINT:
dsize=5
isize=2
elif newcol.iFldType==_SQL_INTEGER:
dsize=10
isize=4
elif newcol.iFldType==_SQL_REAL:
dsize=7
isize=4
elif newcol.iFldType==_SQL_FLOAT or newcol.iFldType==_SQL_DOUBLE:
dsize=15
isize=8
elif newcol.iFldType==_SQL_TYPE_DATE:
dsize=10
isize=4
elif newcol.iFldType==_SQL_TYPE_TIME:
dsize=8
if newcol.iUnits2>0:
dsize=9+newcol.iUnits2
isize=8
elif newcol.iFldType==_SQL_TYPE_TIMESTAMP:
dsize=19
if newcol.iUnits2>0:
dsize=20+newcol.iUnits2
isize=12
else:
dsize=newcol.iUnits1
isize=newcol.iUnits1
self._description.append((newcol.colName, newcol.iFldType,
dsize, isize, newcol.iUnits1, newcol.iUnits2, None))
else:
#no result set
self._description=None
self.colCount=0
#Now get the param count & definitions
self.paramCount=self._Con.marshalBuffer.getSQLINTEGER()
for i in range(self.paramCount):
rn=self._Con.marshalBuffer.getSQLSMALLINT()
self.newparam=_paramSQL()
self.newparam.iParamNum=rn
self.newparam.colName=self._Con.marshalBuffer.getpSQLCHAR_SWORD()
self.newparam.iDataType=self._Con.marshalBuffer.getSQLSMALLINT()
self.newparam.iArgType=_ptInput #default
if self._Con.serverCLIversion>=93:
self.newparam.iUnits1=self._Con.marshalBuffer.getSQLINTEGER()
else:
self.newparam.iUnits1=self._Con.marshalBuffer.getSQLSMALLINT()
self.newparam.iUnits2=self._Con.marshalBuffer.getSQLSMALLINT()
x=self._Con.marshalBuffer.getSQLSMALLINT()
self.param.append(self.newparam)
#Now auto-bind all the columns
for i in range(self.colCount):
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLSETDESCFIELD)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle) #pass server statement ref
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_ATTR_APP_ROW_DESC)
self._Con.marshalBuffer.putSQLSMALLINT(i+1) #=colRef(-1) on the server
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_DESC_DATA_POINTER)
self._Con.marshalBuffer.putSQLPOINTER(1) #= 0=unbound, else bound
self._Con.marshalBuffer.putSQLINTEGER(0)
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLSETDESCFIELD:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
#raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
def _doExecute(self, parameters):
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLEXECUTE)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle) #pass server statement ref
#Write row count
self._Con.marshalBuffer.putSQLUINTEGER(1)
for row in range(1,2):
#Now send the param count & data for this row
if parameters is None:
self._Con.marshalBuffer.putSQLINTEGER(0)
else:
self._Con.marshalBuffer.putSQLINTEGER(len(parameters))
for i in range(len(parameters)):
self._Con.marshalBuffer.putSQLSMALLINT((i+1))
#Put the data
#Put the null flag
tempNull=_SQL_FALSE #default
if parameters[i] is None:
tempNull=_SQL_TRUE
self._Con.marshalBuffer.putSQLSMALLINT(tempNull)
#Note: we only send length+data if not null
if tempNull==_SQL_FALSE:
if isinstance(parameters[i],BinaryString):
self._Con.marshalBuffer.putpDataSDWORD(str(parameters[i])) #omit \0 if BLOB
else :
self._Con.marshalBuffer.putpDataSDWORD(str(parameters[i])+chr(0))
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLEXECUTE:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
#Get the row count - only valid for insert/update/delete
self._affectedRowCount=self._Con.marshalBuffer.getSQLINTEGER()
if self._Con.serverCLIversion>=92:
#Now get any late (post-prepare) resultSet definition, i.e. for stored procedure return cursors
#False here doesn't mean we have no result set, it means we should use the details from SQLprepare
lateResSet=self._Con.marshalBuffer.getSQLUSMALLINT()
#Remember this for future state changes
if lateResSet==_SQL_TRUE:
self.resultSet=True
#else leave resultSet as was
if lateResSet==_SQL_TRUE:
#Now get the cursor column count & definitions
self.col=[]
self.colCount=self._Con.marshalBuffer.getSQLINTEGER()
for i in range(self.colCount):
rn=self._Con.marshalBuffer.getSQLSMALLINT()
self.newcol=_columnSQL()
self.newcol.iFldNum=rn
self.newcol.colName=self._Con.marshalBuffer.getpSQLCHAR_SWORD()[:-1] #remove trailing \0
self.newcol.iFldType=self._Con.marshalBuffer.getSQLSMALLINT()
if self._Con.serverCLIversion>=93:
self.newcol.iUnits1=self._Con.marshalBuffer.getSQLINTEGER()
else:
self.newcol.iUnits1=self._Con.marshalBuffer.getSQLSMALLINT()
self.newcol.iUnits2=self._Con.marshalBuffer.getSQLSMALLINT()
self.newcol.iNullOffset=self._Con.marshalBuffer.getSQLSMALLINT()
self.col.append(self.newcol)
#Now set the description
self._description=[]
for rn,newcol in self.col:
if newcol.iFldType==_SQL_NUMERIC or newcol.iFldType==_SQL_DECIMAL:
dsize=newcol.iUnits1+newcol.iUnits2+1
isize=newcol.iUnits1+newcol.iUnits2
elif newcol.iFldType==_SQL_SMALLINT:
dsize=5
isize=2
elif newcol.iFldType==_SQL_INTEGER:
dsize=10
isize=4
elif newcol.iFldType==_SQL_REAL:
dsize=7
isize=4
elif newcol.iFldType==_SQL_FLOAT or newcol.iFldType==_SQL_DOUBLE:
dsize=15
isize=8
elif newcol.iFldType==_SQL_TYPE_DATE:
dsize=10
isize=4
elif newcol.iFldType==_SQL_TYPE_TIME:
dsize=8
if newcol.iUnits2>0:
dsize=9+newcol.iUnits2
isize=8
elif newcol.iFldType==_SQL_TYPE_TIMESTAMP:
dsize=19
if newcol.iUnits2>0:
dsize=20+newcol.iUnits2
isize=12
else:
dsize=newcol.iUnits1
isize=newcol.iUnits1
self._description.append((newcol.colName, newcol.iFldType,
dsize, isize, newcol.iUnits1, newcol.iUnits2, None))
#else no late result set: leave as was (i.e. don't zeroise self.description)
#else young server cannot handle this
if self.resultCode==_SQL_SUCCESS or self.resultCode==_SQL_SUCCESS_WITH_INFO:
#we SQLendTran now if in autocommit mode & if not select/result-set
if not self.resultSet and self._Con._Autocommit:
self._Con.commit()
elif self.resultCode==_SQL_NEED_DATA:
if self.prepared:
rn=self._Con.marshalBuffer.getSQLSMALLINT() #this is the parameter id that's missing
raise InterfaceError, str(_seMissingParameter)+' '+_seMissingParameterText
def execute(self, operation=None, parameters=None):
'''Execute the specified SQL operation, possibly opening a result set.
'''
self._doPrepare(operation)
self._doExecute(parameters)
return None
def executemany(self, operation=None, seq_of_parameters=None):
'''Repeatedly execute the specified SQL operation, once for each set of
parameters in the sequence.
This is more efficient than repeatedly calling the execute method.
'''
self._doPrepare(operation)
self.prepared=True
for parameters in seq_of_parameters:
if self.resultSet: #close any existing query result set on this cursor before we re-execute
self._resultSetClose()
last=self._doExecute(parameters)
return last
def callproc(self, procname, parameters=None):
'''Call the specified stored procedure with the parameters.
May return a result set accessible using the fetchXXX methods.
'''
return self.execute('CALL '+procname+replace(replace(str(parameters),'[','('),']',')'))
def _setArraySize(self, arraySize=1):
'''Tell the server how many rows to return per fetch.
'''
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLSETDESCFIELD)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle)
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_ATTR_APP_ROW_DESC)
self._Con.marshalBuffer.putSQLSMALLINT(0)
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_DESC_ARRAY_SIZE)
self._Con.marshalBuffer.putSQLUINTEGER(arraySize)
self._Con.marshalBuffer.putSQLINTEGER(0) #bufferLength = n/a here
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLSETDESCFIELD:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
self.resultCode=self._Con.marshalBuffer.getRETCODE()
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
#record the latest value
self.lastArraySize=arraySize
def _fetch(self, arraySize=None):
if self.resultSet:
res=[]
if arraySize!=None and arraySize!=self.lastArraySize:
self._setArraySize(arraySize)
#call server fetchScroll
self._Con.marshalBuffer.clearToSend()
self._Con.marshalBuffer.putFunction(_SQL_API_SQLFETCHSCROLL)
self._Con.marshalBuffer.putSQLHSTMT(self.serverStatementHandle)
self._Con.marshalBuffer.putSQLSMALLINT(_SQL_FETCH_NEXT)
self._Con.marshalBuffer.putSQLINTEGER(0)
if self._Con.marshalBuffer.send()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#Wait for response
if self._Con.marshalBuffer.read()!=_ok:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
functionId=self._Con.marshalBuffer.getFunction()
if functionId!=_SQL_API_SQLFETCHSCROLL:
raise ConnectionError, str(_seConnectionFailed)+' '+_seConnectionFailedText
#resultCode comes later: first we retrieve any result data
#Read row count
rowCount=self._Con.marshalBuffer.getSQLUINTEGER()
for row in range(rowCount):
rowres=[]
#Now get the col count & data for this row
self.colCount=self._Con.marshalBuffer.getSQLINTEGER()
for i in range(self.colCount):
rn=self._Con.marshalBuffer.getSQLSMALLINT()
if rn<=self.colCount:
#Get the null flag
tempNull=self._Con.marshalBuffer.getSQLSMALLINT()
if tempNull==_SQL_TRUE:
rowres.append(None)
else:
#Note: we only get length+data if not null
self.col[i].data=self._Con.marshalBuffer.getpDataSDWORD()
#Convert the raw data to Python data
if self.col[i].iFldType==_SQL_CHAR or self.col[i].iFldType==_SQL_VARCHAR:
rowres.append(self.col[i].data)
elif self.col[i].iFldType==_SQL_NUMERIC or self.col[i].iFldType==_SQL_DECIMAL:
if have_fixedpoint:
fp=str(struct.unpack(' no more data
if arraySize>1:
res.append(tuple(rowres))
else:
res=tuple(rowres)
self.resultCode=self._Con.marshalBuffer.getRETCODE()
if self.resultCode==_SQL_NO_DATA and arraySize==1:
res=None #-> no more data
#if error, then get error details: local-number, default-text
self.errCount=self._Con.marshalBuffer.getSQLINTEGER() #error count
if self.resultCode==_SQL_ERROR:
for err in range(self.errCount):
self.resultErrCode=self._Con.marshalBuffer.getSQLINTEGER()
self.resultErrText=self._Con.marshalBuffer.getpUCHAR_SWORD()
raise DatabaseError, str(self.resultErrCode)+' '+self.resultErrText
return res
else:
raise InterfaceError, str(_seResultSetNotOpen)+' '+_seResultSetNotOpenText
def fetchone(self):
'''Fetch the next row from the cursor's result set.
'''
return self._fetch(1)
def fetchmany(self, size=None):
'''Fetch a number of rows from the cursor's result set.
'''
if size is None:
size=self.lastArraySize
return self._fetch(size)
def fetchall(self):
'''Fetch all the remaining rows from the cursor's result set.
'''
res=[]
while 1:
r=self.fetchone()
if r is None:
break
res.append(r)
return res
def _getaffectedRowCount(self):
return self._affectedRowCount
rowcount=property(_getaffectedRowCount, doc='Number of affected row(s)')
def _getdescription(self):
return self._description
def _getconnection(self):
warnings.warn('DB-API extension cursor.connection used')
return self._Con
description=property(_getdescription, doc='Column description(s)')
def _getarraysize(self):
return self.lastArraySize
arraysize=property(_getarraysize, _setArraySize, doc='Number of rows to fetch with fetchmany()')
def __iter__(self):
return self
def next(self):
x=self.fetchone()
if x is None:
raise StopIteration
return x
def __del__(self):
self.close()
#Column/Parameter classes -----------------------------------------------------
class _columnSQL:
def __init__(self):
self.iFldNum=None
self.iFldType=None
self.iUnits1=None
self.iUnits2=None
self.iNullOffset=None
self.colName=None
self.data=None
class _paramSQL:
def __init__(self):
self.iParamNum=None
self.colName=None
self.iDataType=None
self.iArgType=None
self.iUnits1=None
self.iUnits2=None
self.buffer=None
self.bufferLen=None
self.isNull=None
_clientCLIversion =100 #client parameter passing version
_CLI_ODBC=1
_CLI_JDBC=2
_CLI_DBEXPRESS=3
_CLI_ADO_NET=4
_CLI_PYTHON_DBAPI=5
_DriverName='ThinkSQL'
_DriverVersion='1.03.01'
__version__ = 1, 03, 01
_DriverMajorVersion=1
_DriverMinorVersion=03
_ok=0
_fail=-1
_failString=''
_stateClosed=0
_stateOpen=1
_sizeof_short=2
_sizeof_int=4
_sizeof_long=8
_sizeof_float=4
_sizeof_double=8
_sizeof_byte=8 #in bits
_sizeof_date=4
_sizeof_dateY=2
_sizeof_dateM=1
_sizeof_dateD=1
_sizeof_time=7
_sizeof_timeH=1
_sizeof_timeM=1
_sizeof_timeS=4
_sizeof_timeSc=1
_TIME_MAX_SCALE=6
_MAX_COL_PER_TABLE=300
_MAX_PARAM_PER_QUERY=300
_SQL_FALSE =0
_SQL_TRUE =1
#parameter types
_ptInput = 0
_ptOutput = 1
_EscapeChar='\\'
_SQL_ERROR=-1
_SQL_ERROR2=_SQL_ERROR
_SQL_SUCCESS=0
_SQL_SUCCESS_WITH_INFO=1
_SQL_STILL_EXECUTING=2
_SQL_NEED_DATA=99
_SQL_NO_DATA=100
_SQL_CHAR =1
_SQL_NUMERIC =2
_SQL_DECIMAL =3
_SQL_INTEGER =4
_SQL_SMALLINT =5
_SQL_FLOAT =6
_SQL_REAL =7
_SQL_DOUBLE =8
_SQL_DATETIME =9
_SQL_INTERVAL =10
_SQL_VARCHAR =12
_SQL_TYPE_DATE =91
_SQL_TYPE_TIME =92
_SQL_TYPE_TIMESTAMP =93
_SQL_LONGVARCHAR =-1
#SQL_BINARY =-2
#SQL_VARBINARY =-3
_SQL_LONGVARBINARY =-4
#future use: SQL_BIGINT =-5
_SQL_API_SQLCONNECT =7
_SQL_API_SQLDISCONNECT =9
_SQL_API_SQLEXECUTE =12
_SQL_API_SQLPREPARE =19
_SQL_API_SQLGETDATA =43
_SQL_API_SQLGETINFO =45
_SQL_API_SQLALLOCHANDLE =1001
_SQL_API_SQLCLOSECURSOR =1003
_SQL_API_SQLENDTRAN =1005
_SQL_API_SQLFREEHANDLE =1006
_SQL_API_SQLSETDESCFIELD =1017
_SQL_API_SQLFETCHSCROLL =1021
_SQL_ATTR_APP_ROW_DESC =10010
_SQL_DESC_ARRAY_SIZE =20
_SQL_DESC_DATA_POINTER =1010
_SQL_ROW_SUCCESS =0
_SQL_ROW_NOROW =3
_SQL_API_handshake =9999
_SQL_HANDLE_STMT =3
_SQL_ROLLBACK =1
_SQL_COMMIT =0
_SQL_FETCH_NEXT =1
_SQL_DBMS_NAME =17
_SQL_DBMS_VERSION =18
#Errors:
_seNotImplementedYet=500
_seNotImplementedYetText='Not implemented yet'
_seHandshakeFailed=1500
_seHandshakeFailedText='Handshake failed'
_seConnectionFailed=1502
_seConnectionFailedText='Communication link failure'
_seInvalidColumnIndex=1600
_seInvalidColumnIndexText='Invalid column index'
_seInvalidConversion=1602
_seInvalidConversionText='Invalid data conversion'
_seInvalidParameterIndex=1604
_seInvalidParameterIndexText='Invalid parameter index'
_seConnectionNotOpen=1700
_seConnectionNotOpenText='Connection not open'
_seResultSetNotOpen=1702
_seResultSetNotOpenText='No result set'
_seMissingParameter=1704
_seMissingParameterText='Not enough parameters passed'
_ss08001='08001'
_ss08S01='08S01'
_ss42000='42000'
_ssHY000='HY000'
_ssHY010='HT010'
_ssHYC00='HYC00' #optional feature not implemented yet
_ssNA='NA'
_sqlDate='