def main():
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con=PySQLRClient.sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
"test","test")
cur=PySQLRClient.sqlrcursor(con)
# get database type
print("IDENTIFY: ")
checkSuccess(con.identify(),"firebird")
print()
# ping
print("PING: ")
checkSuccess(con.ping(),1)
print()
# clear table
cur.sendQuery("delete from testtable")
con.commit()
print("INSERT: ")
checkSuccess(cur.sendQuery("insert into testtable values (1,1,1.1,1.1,1.1,1.1,'01-JAN-2001','01:00:00','testchar1','testvarchar1',NULL,NULL)"),1)
print()
print("BIND BY POSITION: ")
cur.prepareQuery("insert into testtable values (?,?,?,?,?,?,?,?,?,?,?,NULL)")
checkSuccess(cur.countBindVariables(),11)
cur.inputBind("1",2)
cur.inputBind("2",2)
cur.inputBind("3",2.2,2,1)
cur.inputBind("4",2.2,2,1)
cur.inputBind("5",2.2,2,1)
cur.inputBind("6",2.2,2,1)
cur.inputBind("7","01-JAN-2002")
cur.inputBind("8","02:00:00")
cur.inputBind("9","testchar2")
cur.inputBind("10","testvarchar2")
cur.inputBind("11",None)
checkSuccess(cur.executeQuery(),1)
cur.clearBinds()
cur.inputBind("1",3)
cur.inputBind("2",3)
cur.inputBind("3",3.3,2,1)
cur.inputBind("4",3.3,2,1)
cur.inputBind("5",3.3,2,1)
cur.inputBind("6",3.3,2,1)
cur.inputBind("7","01-JAN-2003")
cur.inputBind("8","03:00:00")
cur.inputBind("9","testchar3")
cur.inputBind("10","testvarchar3")
cur.inputBind("11",None)
checkSuccess(cur.executeQuery(),1)
print()
print("ARRAY OF BINDS BY POSITION: ")
cur.clearBinds()
cur.inputBinds(["1","2","3","4","5","6",
"7","8","9","10","11"],
[4,4,4.4,4.4,4.4,4.4,"01-JAN-2004","04:00:00",
"testchar4","testvarchar4",None],
[0,0,2,2,2,2,0,0,0,0,0],
[0,0,1,1,1,1,0,0,0,0,0])
checkSuccess(cur.executeQuery(),1)
print()
print("INSERT: ")
checkSuccess(cur.sendQuery("insert into testtable values (5,5,5.5,5.5,5.5,5.5,'01-JAN-2005','05:00:00','testchar5','testvarchar5',NULL,NULL)"),1)
checkSuccess(cur.sendQuery("insert into testtable values (6,6,6.6,6.6,6.6,6.6,'01-JAN-2006','06:00:00','testchar6','testvarchar6',NULL,NULL)"),1)
checkSuccess(cur.sendQuery("insert into testtable values (7,7,7.7,7.7,7.7,7.7,'01-JAN-2007','07:00:00','testchar7','testvarchar7',NULL,NULL)"),1)
checkSuccess(cur.sendQuery("insert into testtable values (8,8,8.8,8.8,8.8,8.8,'01-JAN-2008','08:00:00','testchar8','testvarchar8',NULL,NULL)"),1)
print()
print("AFFECTED ROWS: ")
checkSuccess(cur.affectedRows(),0)
print()
print("STORED PROCEDURE: ")
cur.prepareQuery("select * from testproc(?,?,?,NULL)")
cur.inputBind("1",1)
cur.inputBind("2",1.1,2,1)
cur.inputBind("3","hello")
checkSuccess(cur.executeQuery(),1)
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),Decimal(Decimal("1.1000")))
checkSuccess(cur.getField(0,2),"hello")
cur.prepareQuery("execute procedure testproc ?, ?, ?, NULL")
cur.inputBind("1",1)
cur.inputBind("2",1.1,2,1)
cur.inputBind("3","hello")
cur.defineOutputBindInteger("1")
cur.defineOutputBindDouble("2")
cur.defineOutputBindString("3",20)
cur.defineOutputBindBlob("4")
checkSuccess(cur.executeQuery(),1)
checkSuccess(cur.getOutputBindInteger("1"),1)
#checkSuccess(cur.getOutputBindDouble("2"),1.1)
checkSuccess(cur.getOutputBindString("3"),"hello ")
print()
print("SELECT: ")
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
print()
print("COLUMN COUNT: ")
checkSuccess(cur.colCount(),12)
print()
print("COLUMN NAMES: ")
checkSuccess(cur.getColumnName(0),"TESTINTEGER")
checkSuccess(cur.getColumnName(1),"TESTSMALLINT")
checkSuccess(cur.getColumnName(2),"TESTDECIMAL")
checkSuccess(cur.getColumnName(3),"TESTNUMERIC")
checkSuccess(cur.getColumnName(4),"TESTFLOAT")
checkSuccess(cur.getColumnName(5),"TESTDOUBLE")
checkSuccess(cur.getColumnName(6),"TESTDATE")
checkSuccess(cur.getColumnName(7),"TESTTIME")
checkSuccess(cur.getColumnName(8),"TESTCHAR")
checkSuccess(cur.getColumnName(9),"TESTVARCHAR")
checkSuccess(cur.getColumnName(10),"TESTTIMESTAMP")
cols=cur.getColumnNames()
checkSuccess(cols[0],"TESTINTEGER")
checkSuccess(cols[1],"TESTSMALLINT")
checkSuccess(cols[2],"TESTDECIMAL")
checkSuccess(cols[3],"TESTNUMERIC")
checkSuccess(cols[4],"TESTFLOAT")
checkSuccess(cols[5],"TESTDOUBLE")
checkSuccess(cols[6],"TESTDATE")
checkSuccess(cols[7],"TESTTIME")
checkSuccess(cols[8],"TESTCHAR")
checkSuccess(cols[9],"TESTVARCHAR")
checkSuccess(cols[10],"TESTTIMESTAMP")
print()
print("COLUMN TYPES: ")
checkSuccess(cur.getColumnType(0),"INTEGER")
checkSuccess(cur.getColumnType('TESTINTEGER'),"INTEGER")
checkSuccess(cur.getColumnType(1),"SMALLINT")
checkSuccess(cur.getColumnType('TESTSMALLINT'),"SMALLINT")
checkSuccess(cur.getColumnType(2),"DECIMAL")
checkSuccess(cur.getColumnType('TESTDECIMAL'),"DECIMAL")
checkSuccess(cur.getColumnType(3),"NUMERIC")
checkSuccess(cur.getColumnType('TESTNUMERIC'),"NUMERIC")
checkSuccess(cur.getColumnType(4),"FLOAT")
checkSuccess(cur.getColumnType('TESTFLOAT'),"FLOAT")
checkSuccess(cur.getColumnType(5),"DOUBLE PRECISION")
checkSuccess(cur.getColumnType('TESTDOUBLE'),"DOUBLE PRECISION")
checkSuccess(cur.getColumnType(6),"DATE")
checkSuccess(cur.getColumnType('TESTDATE'),"DATE")
checkSuccess(cur.getColumnType(7),"TIME")
checkSuccess(cur.getColumnType('TESTTIME'),"TIME")
checkSuccess(cur.getColumnType(8),"CHAR")
checkSuccess(cur.getColumnType('TESTCHAR'),"CHAR")
checkSuccess(cur.getColumnType(9),"VARCHAR")
checkSuccess(cur.getColumnType('TESTVARCHAR'),"VARCHAR")
checkSuccess(cur.getColumnType(10),"TIMESTAMP")
checkSuccess(cur.getColumnType('TESTTIMESTAMP'),"TIMESTAMP")
print()
print("COLUMN LENGTH: ")
checkSuccess(cur.getColumnLength(0),4)
checkSuccess(cur.getColumnLength('TESTINTEGER'),4)
checkSuccess(cur.getColumnLength(1),2)
checkSuccess(cur.getColumnLength('TESTSMALLINT'),2)
checkSuccess(cur.getColumnLength(2),8)
checkSuccess(cur.getColumnLength('TESTDECIMAL'),8)
checkSuccess(cur.getColumnLength(3),8)
checkSuccess(cur.getColumnLength('TESTNUMERIC'),8)
checkSuccess(cur.getColumnLength(4),4)
checkSuccess(cur.getColumnLength('TESTFLOAT'),4)
checkSuccess(cur.getColumnLength(5),8)
checkSuccess(cur.getColumnLength('TESTDOUBLE'),8)
checkSuccess(cur.getColumnLength(6),4)
checkSuccess(cur.getColumnLength('TESTDATE'),4)
checkSuccess(cur.getColumnLength(7),4)
checkSuccess(cur.getColumnLength('TESTTIME'),4)
checkSuccess(cur.getColumnLength(8),50)
checkSuccess(cur.getColumnLength('TESTCHAR'),50)
checkSuccess(cur.getColumnLength(9),50)
checkSuccess(cur.getColumnLength('TESTVARCHAR'),50)
checkSuccess(cur.getColumnLength(10),8)
checkSuccess(cur.getColumnLength('TESTTIMESTAMP'),8)
print()
print("LONGEST COLUMN: ")
checkSuccess(cur.getLongest(0),1)
checkSuccess(cur.getLongest('TESTINTEGER'),1)
checkSuccess(cur.getLongest(1),1)
checkSuccess(cur.getLongest('TESTSMALLINT'),1)
checkSuccess(cur.getLongest(2),4)
checkSuccess(cur.getLongest('TESTDECIMAL'),4)
checkSuccess(cur.getLongest(3),4)
checkSuccess(cur.getLongest('TESTNUMERIC'),4)
checkSuccess(cur.getLongest(4),6)
checkSuccess(cur.getLongest('TESTFLOAT'),6)
checkSuccess(cur.getLongest(5),6)
checkSuccess(cur.getLongest('TESTDOUBLE'),6)
checkSuccess(cur.getLongest(6),10)
checkSuccess(cur.getLongest('TESTDATE'),10)
checkSuccess(cur.getLongest(7),8)
checkSuccess(cur.getLongest('TESTTIME'),8)
checkSuccess(cur.getLongest(8),50)
checkSuccess(cur.getLongest('TESTCHAR'),50)
checkSuccess(cur.getLongest(9),12)
checkSuccess(cur.getLongest('TESTVARCHAR'),12)
checkSuccess(cur.getLongest(10),0)
checkSuccess(cur.getLongest('TESTTIMESTAMP'),0)
print()
print("ROW COUNT: ")
checkSuccess(cur.rowCount(),8)
print()
print("TOTAL ROWS: ")
checkSuccess(cur.totalRows(),0)
print()
print("FIRST ROW INDEX: ")
checkSuccess(cur.firstRowIndex(),0)
print()
print("END OF RESULT SET: ")
checkSuccess(cur.endOfResultSet(),1)
print()
print("FIELDS BY INDEX: ")
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),1)
checkSuccess(cur.getField(0,2),Decimal(Decimal("1.10")))
checkSuccess(cur.getField(0,3),Decimal(Decimal("1.10")))
checkSuccess(cur.getField(0,4),Decimal(Decimal("1.1000")))
checkSuccess(cur.getField(0,5),Decimal(Decimal("1.1000")))
checkSuccess(cur.getField(0,6),"2001:01:01")
checkSuccess(cur.getField(0,7),"01:00:00")
checkSuccess(cur.getField(0,8),"testchar1 ")
checkSuccess(cur.getField(0,9),"testvarchar1")
print()
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(7,1),8)
checkSuccess(cur.getField(7,2),Decimal("8.80"))
checkSuccess(cur.getField(7,3),Decimal("8.80"))
checkSuccess(cur.getField(7,4),Decimal("8.8000"))
checkSuccess(cur.getField(7,5),Decimal("8.8000"))
checkSuccess(cur.getField(7,6),"2008:01:01")
checkSuccess(cur.getField(7,7),"08:00:00")
checkSuccess(cur.getField(7,8),"testchar8 ")
checkSuccess(cur.getField(7,9),"testvarchar8")
print()
print("FIELD LENGTHS BY INDEX: ")
checkSuccess(cur.getFieldLength(0,0),1)
checkSuccess(cur.getFieldLength(0,1),1)
checkSuccess(cur.getFieldLength(0,2),4)
checkSuccess(cur.getFieldLength(0,3),4)
checkSuccess(cur.getFieldLength(0,4),6)
checkSuccess(cur.getFieldLength(0,5),6)
checkSuccess(cur.getFieldLength(0,6),10)
checkSuccess(cur.getFieldLength(0,7),8)
checkSuccess(cur.getFieldLength(0,8),50)
checkSuccess(cur.getFieldLength(0,9),12)
print()
checkSuccess(cur.getFieldLength(7,0),1)
checkSuccess(cur.getFieldLength(7,1),1)
checkSuccess(cur.getFieldLength(7,2),4)
checkSuccess(cur.getFieldLength(7,3),4)
checkSuccess(cur.getFieldLength(7,4),6)
checkSuccess(cur.getFieldLength(7,5),6)
checkSuccess(cur.getFieldLength(7,6),10)
checkSuccess(cur.getFieldLength(7,7),8)
checkSuccess(cur.getFieldLength(7,8),50)
checkSuccess(cur.getFieldLength(7,9),12)
print()
print("FIELDS BY NAME: ")
checkSuccess(cur.getField(0,"TESTINTEGER"),1)
checkSuccess(cur.getField(0,"TESTSMALLINT"),1)
checkSuccess(cur.getField(0,"TESTDECIMAL"),Decimal("1.10"))
checkSuccess(cur.getField(0,"TESTNUMERIC"),Decimal("1.10"))
checkSuccess(cur.getField(0,"TESTFLOAT"),Decimal("1.1000"))
checkSuccess(cur.getField(0,"TESTDOUBLE"),Decimal("1.1000"))
checkSuccess(cur.getField(0,"TESTDATE"),"2001:01:01")
checkSuccess(cur.getField(0,"TESTTIME"),"01:00:00")
checkSuccess(cur.getField(0,"TESTCHAR"),"testchar1 ")
checkSuccess(cur.getField(0,"TESTVARCHAR"),"testvarchar1")
print()
checkSuccess(cur.getField(7,"TESTINTEGER"),8)
checkSuccess(cur.getField(7,"TESTSMALLINT"),8)
checkSuccess(cur.getField(7,"TESTDECIMAL"),Decimal("8.80"))
checkSuccess(cur.getField(7,"TESTNUMERIC"),Decimal("8.80"))
checkSuccess(cur.getField(7,"TESTFLOAT"),Decimal("8.8000"))
checkSuccess(cur.getField(7,"TESTDOUBLE"),Decimal("8.8000"))
checkSuccess(cur.getField(7,"TESTDATE"),"2008:01:01")
checkSuccess(cur.getField(7,"TESTTIME"),"08:00:00")
checkSuccess(cur.getField(7,"TESTCHAR"),"testchar8 ")
checkSuccess(cur.getField(7,"TESTVARCHAR"),"testvarchar8")
print()
print("FIELD LENGTHS BY NAME: ")
checkSuccess(cur.getFieldLength(0,"TESTINTEGER"),1)
checkSuccess(cur.getFieldLength(0,"TESTSMALLINT"),1)
checkSuccess(cur.getFieldLength(0,"TESTDECIMAL"),4)
checkSuccess(cur.getFieldLength(0,"TESTNUMERIC"),4)
checkSuccess(cur.getFieldLength(0,"TESTFLOAT"),6)
checkSuccess(cur.getFieldLength(0,"TESTDOUBLE"),6)
checkSuccess(cur.getFieldLength(0,"TESTDATE"),10)
checkSuccess(cur.getFieldLength(0,"TESTTIME"),8)
checkSuccess(cur.getFieldLength(0,"TESTCHAR"),50)
checkSuccess(cur.getFieldLength(0,"TESTVARCHAR"),12)
print()
checkSuccess(cur.getFieldLength(7,"TESTINTEGER"),1)
checkSuccess(cur.getFieldLength(7,"TESTSMALLINT"),1)
checkSuccess(cur.getFieldLength(7,"TESTDECIMAL"),4)
checkSuccess(cur.getFieldLength(7,"TESTNUMERIC"),4)
checkSuccess(cur.getFieldLength(7,"TESTFLOAT"),6)
checkSuccess(cur.getFieldLength(7,"TESTDOUBLE"),6)
checkSuccess(cur.getFieldLength(7,"TESTDATE"),10)
checkSuccess(cur.getFieldLength(7,"TESTTIME"),8)
checkSuccess(cur.getFieldLength(7,"TESTCHAR"),50)
checkSuccess(cur.getFieldLength(7,"TESTVARCHAR"),12)
print()
print("FIELDS BY ARRAY: ")
fields=cur.getRow(0)
checkSuccess(fields[0],1)
checkSuccess(fields[1],1)
checkSuccess(fields[2],Decimal("1.1"))
checkSuccess(fields[3],Decimal("1.1"))
checkSuccess(fields[4],Decimal("1.1"))
checkSuccess(fields[5],Decimal("1.1"))
checkSuccess(fields[6],"2001:01:01")
checkSuccess(fields[7],"01:00:00")
checkSuccess(fields[8],"testchar1 ")
checkSuccess(fields[9],"testvarchar1")
print()
print("FIELD LENGTHS BY ARRAY: ")
fieldlens=cur.getRowLengths(0)
checkSuccess(fieldlens[0],1)
checkSuccess(fieldlens[1],1)
checkSuccess(fieldlens[2],4)
checkSuccess(fieldlens[3],4)
checkSuccess(fieldlens[4],6)
checkSuccess(fieldlens[5],6)
checkSuccess(fieldlens[6],10)
checkSuccess(fieldlens[7],8)
checkSuccess(fieldlens[8],50)
checkSuccess(fieldlens[9],12)
print()
print("FIELDS BY DICTIONARY: ")
fields=cur.getRowDictionary(0)
checkSuccess(fields["TESTINTEGER"],1)
checkSuccess(fields["TESTSMALLINT"],1)
checkSuccess(fields["TESTDECIMAL"],Decimal("1.1"))
checkSuccess(fields["TESTNUMERIC"],Decimal("1.1"))
checkSuccess(fields["TESTFLOAT"],Decimal("1.1"))
checkSuccess(fields["TESTDOUBLE"],Decimal("1.1"))
checkSuccess(fields["TESTDATE"],"2001:01:01")
checkSuccess(fields["TESTTIME"],"01:00:00")
checkSuccess(fields["TESTCHAR"],"testchar1 ")
checkSuccess(fields["TESTVARCHAR"],"testvarchar1")
print()
fields=cur.getRowDictionary(7)
checkSuccess(fields["TESTINTEGER"],8)
checkSuccess(fields["TESTSMALLINT"],8)
checkSuccess(fields["TESTDECIMAL"],Decimal("8.8"))
checkSuccess(fields["TESTNUMERIC"],Decimal("8.8"))
checkSuccess(fields["TESTFLOAT"],Decimal("8.8"))
checkSuccess(fields["TESTDOUBLE"],Decimal("8.8"))
checkSuccess(fields["TESTDATE"],"2008:01:01")
checkSuccess(fields["TESTTIME"],"08:00:00")
checkSuccess(fields["TESTCHAR"],"testchar8 ")
checkSuccess(fields["TESTVARCHAR"],"testvarchar8")
print()
print("FIELD LENGTHS BY DICTIONARY: ")
fieldlengths=cur.getRowLengthsDictionary(0)
checkSuccess(fieldlengths["TESTINTEGER"],1)
checkSuccess(fieldlengths["TESTSMALLINT"],1)
checkSuccess(fieldlengths["TESTDECIMAL"],4)
checkSuccess(fieldlengths["TESTNUMERIC"],4)
checkSuccess(fieldlengths["TESTFLOAT"],6)
checkSuccess(fieldlengths["TESTDOUBLE"],6)
checkSuccess(fieldlengths["TESTDATE"],10)
checkSuccess(fieldlengths["TESTTIME"],8)
checkSuccess(fieldlengths["TESTCHAR"],50)
checkSuccess(fieldlengths["TESTVARCHAR"],12)
print()
fieldlengths=cur.getRowLengthsDictionary(7)
checkSuccess(fieldlengths["TESTINTEGER"],1)
checkSuccess(fieldlengths["TESTSMALLINT"],1)
checkSuccess(fieldlengths["TESTDECIMAL"],4)
checkSuccess(fieldlengths["TESTNUMERIC"],4)
checkSuccess(fieldlengths["TESTFLOAT"],6)
checkSuccess(fieldlengths["TESTDOUBLE"],6)
checkSuccess(fieldlengths["TESTDATE"],10)
checkSuccess(fieldlengths["TESTTIME"],8)
checkSuccess(fieldlengths["TESTCHAR"],50)
checkSuccess(fieldlengths["TESTVARCHAR"],12)
print()
print("INDIVIDUAL SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)','$(var3)' from rdb$database")
cur.substitution("var1",1)
cur.substitution("var2","hello")
cur.substitution("var3",10.5556,6,4)
checkSuccess(cur.executeQuery(),1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),"hello")
checkSuccess(cur.getField(0,2),"10.5556")
print()
print("ARRAY SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)','$(var3)' from rdb$database")
cur.substitutions(["var1","var2","var3"],
[1,"hello",10.5556],[0,0,6],[0,0,4])
checkSuccess(cur.executeQuery(),1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),"hello")
checkSuccess(cur.getField(0,2),"10.5556")
print()
print("NULLS as Nones: ")
cur.getNullsAsNone()
checkSuccess(cur.sendQuery("select 1,NULL,NULL from rdb$database"),1)
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),None)
checkSuccess(cur.getField(0,2),None)
cur.getNullsAsEmptyStrings()
checkSuccess(cur.sendQuery("select 1,NULL,NULL from rdb$database"),1)
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),"")
checkSuccess(cur.getField(0,2),"")
cur.getNullsAsNone()
print()
print("RESULT SET BUFFER SIZE: ")
checkSuccess(cur.getResultSetBufferSize(),0)
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
checkSuccess(cur.getResultSetBufferSize(),2)
print()
checkSuccess(cur.firstRowIndex(),0)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),2)
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
print()
checkSuccess(cur.firstRowIndex(),2)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),4)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.firstRowIndex(),6)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),8)
checkSuccess(cur.getField(8,0),None)
print()
checkSuccess(cur.firstRowIndex(),8)
checkSuccess(cur.endOfResultSet(),1)
checkSuccess(cur.rowCount(),8)
print()
print("DONT GET COLUMN INFO: ")
cur.dontGetColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
checkSuccess(cur.getColumnName(0),None)
checkSuccess(cur.getColumnLength(0),0)
checkSuccess(cur.getColumnType(0),None)
cur.getColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
checkSuccess(cur.getColumnName(0),"TESTINTEGER")
checkSuccess(cur.getColumnLength(0),4)
checkSuccess(cur.getColumnType(0),"INTEGER")
print()
print("SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
print()
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
checkSuccess(cur.getField(3,0),4)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
print()
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
checkSuccess(cur.getField(3,0),4)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
print()
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
checkSuccess(cur.getField(3,0),4)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
print("SUSPENDED RESULT SET: ")
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
checkSuccess(cur.getField(2,0),3)
id=cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
checkSuccess(cur.resumeResultSet(id),1)
print()
checkSuccess(cur.firstRowIndex(),4)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),6)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.firstRowIndex(),6)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),8)
checkSuccess(cur.getField(8,0),None)
print()
checkSuccess(cur.firstRowIndex(),8)
checkSuccess(cur.endOfResultSet(),1)
checkSuccess(cur.rowCount(),8)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET: ")
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
filename=cur.getCacheFileName()
checkSuccess(filename,"cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename),1)
checkSuccess(cur.getField(7,0),8)
print()
print("COLUMN COUNT FOR CACHED RESULT SET: ")
checkSuccess(cur.colCount(),12)
print()
print("COLUMN NAMES FOR CACHED RESULT SET: ")
checkSuccess(cur.getColumnName(0),"TESTINTEGER")
checkSuccess(cur.getColumnName(1),"TESTSMALLINT")
checkSuccess(cur.getColumnName(2),"TESTDECIMAL")
checkSuccess(cur.getColumnName(3),"TESTNUMERIC")
checkSuccess(cur.getColumnName(4),"TESTFLOAT")
checkSuccess(cur.getColumnName(5),"TESTDOUBLE")
checkSuccess(cur.getColumnName(6),"TESTDATE")
checkSuccess(cur.getColumnName(7),"TESTTIME")
checkSuccess(cur.getColumnName(8),"TESTCHAR")
checkSuccess(cur.getColumnName(9),"TESTVARCHAR")
checkSuccess(cur.getColumnName(10),"TESTTIMESTAMP")
cols=cur.getColumnNames()
checkSuccess(cols[0],"TESTINTEGER")
checkSuccess(cols[1],"TESTSMALLINT")
checkSuccess(cols[2],"TESTDECIMAL")
checkSuccess(cols[3],"TESTNUMERIC")
checkSuccess(cols[4],"TESTFLOAT")
checkSuccess(cols[5],"TESTDOUBLE")
checkSuccess(cols[6],"TESTDATE")
checkSuccess(cols[7],"TESTTIME")
checkSuccess(cols[8],"TESTCHAR")
checkSuccess(cols[9],"TESTVARCHAR")
checkSuccess(cols[10],"TESTTIMESTAMP")
print()
print("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
filename=cur.getCacheFileName()
checkSuccess(filename,"cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
cur.setResultSetBufferSize(0)
print()
print("FROM ONE CACHE FILE TO ANOTHER: ")
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"),1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
print()
print("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"),1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
checkSuccess(cur.getField(2,0),3)
filename=cur.getCacheFileName()
checkSuccess(filename,"cachefile1")
id=cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
print()
checkSuccess(con.resumeSession(port,socket),1)
checkSuccess(cur.resumeCachedResultSet(id,filename),1)
print()
checkSuccess(cur.firstRowIndex(),4)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),6)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.firstRowIndex(),6)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),8)
checkSuccess(cur.getField(8,0),None)
print()
checkSuccess(cur.firstRowIndex(),8)
checkSuccess(cur.endOfResultSet(),1)
checkSuccess(cur.rowCount(),8)
cur.cacheOff()
print()
checkSuccess(cur.openCachedResultSet(filename),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
cur.setResultSetBufferSize(0)
print()
#print("COMMIT AND ROLLBACK: ")
secondcon=PySQLRClient.sqlrconnection("sqlrelay",9000,
"/tmp/test.socket",
"test","test")
secondcur=PySQLRClient.sqlrcursor(secondcon)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1)
checkSuccess(secondcur.getField(0,0),0)
checkSuccess(con.commit(),1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1)
checkSuccess(secondcur.getField(0,0),8)
checkSuccess(con.autoCommitOn(),1)
checkSuccess(cur.sendQuery("insert into testtable values (10,10,10.1,10.1,10.1,10.1,'01-JAN-2010','10:00:00','testchar10','testvarchar10',NULL,NULL)"),1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1)
checkSuccess(secondcur.getField(0,0),9)
checkSuccess(con.autoCommitOff(),1)
print()
print("ROW RANGE:")
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
print()
rows=cur.getRowRange(0,5)
checkSuccess(rows[0][0],1)
checkSuccess(rows[0][1],1)
checkSuccess(rows[0][2],Decimal("1.1"))
checkSuccess(rows[0][3],Decimal("1.1"))
checkSuccess(rows[0][4],Decimal("1.1"))
checkSuccess(rows[0][5],Decimal("1.1"))
checkSuccess(rows[0][6],"2001:01:01")
checkSuccess(rows[0][7],"01:00:00")
checkSuccess(rows[0][8],"testchar1 ")
checkSuccess(rows[0][9],"testvarchar1")
print()
checkSuccess(rows[1][0],2)
checkSuccess(rows[1][1],2)
checkSuccess(rows[1][2],Decimal("2.2"))
checkSuccess(rows[1][3],Decimal("2.2"))
checkSuccess(rows[1][4],Decimal("2.2"))
checkSuccess(rows[1][5],Decimal("2.2"))
checkSuccess(rows[1][6],"2002:01:01")
checkSuccess(rows[1][7],"02:00:00")
checkSuccess(rows[1][8],"testchar2 ")
checkSuccess(rows[1][9],"testvarchar2")
print()
checkSuccess(rows[2][0],3)
checkSuccess(rows[2][1],3)
checkSuccess(rows[2][2],Decimal("3.3"))
checkSuccess(rows[2][3],Decimal("3.3"))
checkSuccess(rows[2][4],Decimal("3.3"))
checkSuccess(rows[2][5],Decimal("3.3"))
checkSuccess(rows[2][6],"2003:01:01")
checkSuccess(rows[2][7],"03:00:00")
checkSuccess(rows[2][8],"testchar3 ")
checkSuccess(rows[2][9],"testvarchar3")
print()
checkSuccess(rows[3][0],4)
checkSuccess(rows[3][1],4)
checkSuccess(rows[3][2],Decimal("4.4"))
checkSuccess(rows[3][3],Decimal("4.4"))
checkSuccess(rows[3][4],Decimal("4.4"))
checkSuccess(rows[3][5],Decimal("4.4"))
checkSuccess(rows[3][6],"2004:01:01")
checkSuccess(rows[3][7],"04:00:00")
checkSuccess(rows[3][8],"testchar4 ")
checkSuccess(rows[3][9],"testvarchar4")
print()
checkSuccess(rows[4][0],5)
checkSuccess(rows[4][1],5)
checkSuccess(rows[4][2],Decimal("5.5"))
checkSuccess(rows[4][3],Decimal("5.5"))
checkSuccess(rows[4][4],Decimal("5.5"))
checkSuccess(rows[4][5],Decimal("5.5"))
checkSuccess(rows[4][6],"2005:01:01")
checkSuccess(rows[4][7],"05:00:00")
checkSuccess(rows[4][8],"testchar5 ")
checkSuccess(rows[4][9],"testvarchar5")
print()
checkSuccess(rows[5][0],6)
checkSuccess(rows[5][1],6)
checkSuccess(rows[5][2],Decimal("6.6"))
checkSuccess(rows[5][3],Decimal("6.6"))
checkSuccess(rows[5][4],Decimal("6.6"))
checkSuccess(rows[5][5],Decimal("6.6"))
checkSuccess(rows[5][6],"2006:01:01")
checkSuccess(rows[5][7],"06:00:00")
checkSuccess(rows[5][8],"testchar6 ")
checkSuccess(rows[5][9],"testvarchar6")
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testinteger"),1)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
id=cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
checkSuccess(cur.resumeResultSet(id),1)
checkSuccess(cur.getField(4,0),None)
checkSuccess(cur.getField(5,0),None)
checkSuccess(cur.getField(6,0),None)
checkSuccess(cur.getField(7,0),None)
print()
# drop existing table
con.commit()
cur.sendQuery("delete from testtable")
con.commit()
print()
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable1 order by testinteger"),0)
checkSuccess(cur.sendQuery("select * from testtable1 order by testinteger"),0)
checkSuccess(cur.sendQuery("select * from testtable1 order by testinteger"),0)
checkSuccess(cur.sendQuery("select * from testtable1 order by testinteger"),0)
print()
checkSuccess(cur.sendQuery("insert into testtable1 values (1,2,3,4)"),0)
checkSuccess(cur.sendQuery("insert into testtable1 values (1,2,3,4)"),0)
checkSuccess(cur.sendQuery("insert into testtable1 values (1,2,3,4)"),0)
checkSuccess(cur.sendQuery("insert into testtable1 values (1,2,3,4)"),0)
print()
checkSuccess(cur.sendQuery("create table testtable"),0)
checkSuccess(cur.sendQuery("create table testtable"),0)
checkSuccess(cur.sendQuery("create table testtable"),0)
checkSuccess(cur.sendQuery("create table testtable"),0)
print()
def main():
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con = PySQLRClient.sqlrconnection("sqlrelay", 9000, "/tmp/test.socket",
"test", "test")
cur = PySQLRClient.sqlrcursor(con)
# get database type
print("IDENTIFY: ")
checkSuccess(con.identify(), "freetds")
print()
# ping
print("PING: ")
checkSuccess(con.ping(), 1)
print()
# drop existing table
cur.sendQuery("drop table testtable")
print("CREATE TEMPTABLE: ")
checkSuccess(
cur.sendQuery(
"create table testtable (testint int, testsmallint smallint, testtinyint tinyint, testreal real, testfloat float, testdecimal decimal(4,1), testnumeric numeric(4,1), testmoney money, testsmallmoney smallmoney, testdatetime datetime, testsmalldatetime smalldatetime, testchar char(40), testvarchar varchar(40), testbit bit)"
), 1)
print()
print("BEGIN TRANSACTION: ")
checkSuccess(cur.sendQuery("begin tran"), 1)
print()
print("INSERT: ")
checkSuccess(
cur.sendQuery(
"insert into testtable values (1,1,1,1.1,1.1,1.1,1.1,1.00,1.00,'01-Jan-2001 01:00:00','01-Jan-2001 01:00:00','testchar1','testvarchar1',1)"
), 1)
print()
print("AFFECTED ROWS: ")
checkSuccess(cur.affectedRows(), 1)
print()
print("BIND BY POSITION: ")
cur.prepareQuery(
"insert into testtable values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
checkSuccess(cur.countBindVariables(), 14)
cur.inputBind("1", 2)
cur.inputBind("2", 2)
cur.inputBind("3", 2)
cur.inputBind("4", 2.2, 2, 1)
cur.inputBind("5", 2.2, 2, 1)
cur.inputBind("6", 2.2, 2, 1)
cur.inputBind("7", 2.2, 2, 1)
cur.inputBind("8", 2.00, 3, 2)
cur.inputBind("9", 2.00, 3, 2)
cur.inputBind("10", "01-Jan-2002 02:00:00")
cur.inputBind("11", "01-Jan-2002 02:00:00")
cur.inputBind("12", "testchar2")
cur.inputBind("13", "testvarchar2")
cur.inputBind("14", 1)
checkSuccess(cur.executeQuery(), 1)
cur.clearBinds()
cur.inputBind("1", 3)
cur.inputBind("2", 3)
cur.inputBind("3", 3)
cur.inputBind("4", 3.3, 2, 1)
cur.inputBind("5", 3.3, 2, 1)
cur.inputBind("6", 3.3, 2, 1)
cur.inputBind("7", 3.3, 2, 1)
cur.inputBind("8", 3.00, 3, 2)
cur.inputBind("9", 3.00, 3, 2)
cur.inputBind("10", "01-Jan-2003 03:00:00")
cur.inputBind("11", "01-Jan-2003 03:00:00")
cur.inputBind("12", "testchar3")
cur.inputBind("13", "testvarchar3")
cur.inputBind("14", 1)
checkSuccess(cur.executeQuery(), 1)
print()
print("ARRAY OF BINDS BY POSITION: ")
cur.clearBinds()
cur.inputBinds([
"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13",
"14"
], [
4, 4, 4, 4.4, 4.4, 4.4, 4.4, 4.00, 4.00, "01-Jan-2004 04:00:00",
"01-Jan-2004 04:00:00", "testchar4", "testvarchar4", 1
], [0, 0, 0, 2, 2, 2, 2, 3, 3, 0, 0, 0, 0, 0],
[0, 0, 0, 1, 1, 1, 1, 2, 2, 0, 0, 0, 0, 0])
checkSuccess(cur.executeQuery(), 1)
print()
print("BIND BY NAME: ")
cur.clearBinds()
cur.prepareQuery(
"insert into testtable values (@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9,@var10,@var11,@var12,@var13,@var14)"
)
cur.inputBind("var1", 5)
cur.inputBind("var2", 5)
cur.inputBind("var3", 5)
cur.inputBind("var4", 5.5, 2, 1)
cur.inputBind("var5", 5.5, 2, 1)
cur.inputBind("var6", 5.5, 2, 1)
cur.inputBind("var7", 5.5, 2, 1)
cur.inputBind("var8", 5.00, 3, 2)
cur.inputBind("var9", 5.00, 3, 2)
cur.inputBind("var10", "01-Jan-2005 05:00:00")
cur.inputBind("var11", "01-Jan-2005 05:00:00")
cur.inputBind("var12", "testchar5")
cur.inputBind("var13", "testvarchar5")
cur.inputBind("var14", 1)
checkSuccess(cur.executeQuery(), 1)
cur.clearBinds()
cur.inputBind("var1", 6)
cur.inputBind("var2", 6)
cur.inputBind("var3", 6)
cur.inputBind("var4", 6.6, 2, 1)
cur.inputBind("var5", 6.6, 2, 1)
cur.inputBind("var6", 6.6, 2, 1)
cur.inputBind("var7", 6.6, 2, 1)
cur.inputBind("var8", 6.00, 3, 2)
cur.inputBind("var9", 6.00, 3, 2)
cur.inputBind("var10", "01-Jan-2006 06:00:00")
cur.inputBind("var11", "01-Jan-2006 06:00:00")
cur.inputBind("var12", "testchar6")
cur.inputBind("var13", "testvarchar6")
cur.inputBind("var14", 1)
checkSuccess(cur.executeQuery(), 1)
print()
print("ARRAY OF BINDS BY NAME: ")
cur.clearBinds()
cur.inputBinds([
"var1", "var2", "var3", "var4", "var5", "var6", "var7", "var8", "var9",
"var10", "var11", "var12", "var13", "var14"
], [
7, 7, 7, 7.7, 7.7, 7.7, 7.7, 7.00, 7.00, "01-Jan-2007 07:00:00",
"01-Jan-2007 07:00:00", "testchar7", "testvarchar7", 1
], [0, 0, 0, 2, 2, 2, 2, 3, 3, 0, 0, 0, 0, 0],
[0, 0, 0, 1, 1, 1, 1, 2, 2, 0, 0, 0, 0, 0])
checkSuccess(cur.executeQuery(), 1)
print()
print("BIND BY NAME WITH VALIDATION: ")
cur.clearBinds()
cur.inputBind("var1", 8)
cur.inputBind("var2", 8)
cur.inputBind("var3", 8)
cur.inputBind("var4", 8.8, 2, 1)
cur.inputBind("var5", 8.8, 2, 1)
cur.inputBind("var6", 8.8, 2, 1)
cur.inputBind("var7", 8.8, 2, 1)
cur.inputBind("var8", 8.00, 3, 2)
cur.inputBind("var9", 8.00, 3, 2)
cur.inputBind("var10", "01-Jan-2008 08:00:00")
cur.inputBind("var11", "01-Jan-2008 08:00:00")
cur.inputBind("var12", "testchar8")
cur.inputBind("var13", "testvarchar8")
cur.inputBind("var14", 1)
cur.inputBind("var15", "junkvalue")
cur.validateBinds()
checkSuccess(cur.executeQuery(), 1)
print()
print("SELECT: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
print()
print("COLUMN COUNT: ")
checkSuccess(cur.colCount(), 14)
print()
print("COLUMN NAMES: ")
checkSuccess(cur.getColumnName(0), "testint")
checkSuccess(cur.getColumnName(1), "testsmallint")
checkSuccess(cur.getColumnName(2), "testtinyint")
checkSuccess(cur.getColumnName(3), "testreal")
checkSuccess(cur.getColumnName(4), "testfloat")
checkSuccess(cur.getColumnName(5), "testdecimal")
checkSuccess(cur.getColumnName(6), "testnumeric")
checkSuccess(cur.getColumnName(7), "testmoney")
checkSuccess(cur.getColumnName(8), "testsmallmoney")
checkSuccess(cur.getColumnName(9), "testdatetime")
checkSuccess(cur.getColumnName(10), "testsmalldatetime")
checkSuccess(cur.getColumnName(11), "testchar")
checkSuccess(cur.getColumnName(12), "testvarchar")
checkSuccess(cur.getColumnName(13), "testbit")
cols = cur.getColumnNames()
checkSuccess(cols[0], "testint")
checkSuccess(cols[1], "testsmallint")
checkSuccess(cols[2], "testtinyint")
checkSuccess(cols[3], "testreal")
checkSuccess(cols[4], "testfloat")
checkSuccess(cols[5], "testdecimal")
checkSuccess(cols[6], "testnumeric")
checkSuccess(cols[7], "testmoney")
checkSuccess(cols[8], "testsmallmoney")
checkSuccess(cols[9], "testdatetime")
checkSuccess(cols[10], "testsmalldatetime")
checkSuccess(cols[11], "testchar")
checkSuccess(cols[12], "testvarchar")
checkSuccess(cols[13], "testbit")
print()
print("COLUMN TYPES: ")
checkSuccess(cur.getColumnType(0), "INT")
checkSuccess(cur.getColumnType('testint'), "INT")
checkSuccess(cur.getColumnType(1), "SMALLINT")
checkSuccess(cur.getColumnType('testsmallint'), "SMALLINT")
checkSuccess(cur.getColumnType(2), "TINYINT")
checkSuccess(cur.getColumnType('testtinyint'), "TINYINT")
checkSuccess(cur.getColumnType(3), "REAL")
checkSuccess(cur.getColumnType('testreal'), "REAL")
checkSuccess(cur.getColumnType(4), "FLOAT")
checkSuccess(cur.getColumnType('testfloat'), "FLOAT")
checkSuccess(cur.getColumnType(5), "DECIMAL")
checkSuccess(cur.getColumnType('testdecimal'), "DECIMAL")
checkSuccess(cur.getColumnType(6), "NUMERIC")
checkSuccess(cur.getColumnType('testnumeric'), "NUMERIC")
checkSuccess(cur.getColumnType(7), "MONEY")
checkSuccess(cur.getColumnType('testmoney'), "MONEY")
checkSuccess(cur.getColumnType(8), "SMALLMONEY")
checkSuccess(cur.getColumnType('testsmallmoney'), "SMALLMONEY")
checkSuccess(cur.getColumnType(9), "DATETIME")
checkSuccess(cur.getColumnType('testdatetime'), "DATETIME")
checkSuccess(cur.getColumnType(10), "SMALLDATETIME")
checkSuccess(cur.getColumnType('testsmalldatetime'), "SMALLDATETIME")
checkSuccess(cur.getColumnType(11), "CHAR")
checkSuccess(cur.getColumnType('testchar'), "CHAR")
checkSuccess(cur.getColumnType(12), "CHAR")
checkSuccess(cur.getColumnType('testvarchar'), "CHAR")
checkSuccess(cur.getColumnType(13), "BIT")
checkSuccess(cur.getColumnType('testbit'), "BIT")
print()
print("COLUMN LENGTH: ")
checkSuccess(cur.getColumnLength(0), 4)
checkSuccess(cur.getColumnLength('testint'), 4)
checkSuccess(cur.getColumnLength(1), 2)
checkSuccess(cur.getColumnLength('testsmallint'), 2)
checkSuccess(cur.getColumnLength(2), 1)
checkSuccess(cur.getColumnLength('testtinyint'), 1)
checkSuccess(cur.getColumnLength(3), 4)
checkSuccess(cur.getColumnLength('testreal'), 4)
checkSuccess(cur.getColumnLength(4), 8)
checkSuccess(cur.getColumnLength('testfloat'), 8)
# these seem to fluctuate with every freetds release
#checkSuccess(cur.getColumnLength(5),3)
#checkSuccess(cur.getColumnLength('testdecimal'),3)
#checkSuccess(cur.getColumnLength(6),3)
#checkSuccess(cur.getColumnLength('testnumeric'),3)
checkSuccess(cur.getColumnLength(7), 8)
checkSuccess(cur.getColumnLength('testmoney'), 8)
checkSuccess(cur.getColumnLength(8), 4)
checkSuccess(cur.getColumnLength('testsmallmoney'), 4)
checkSuccess(cur.getColumnLength(9), 8)
checkSuccess(cur.getColumnLength('testdatetime'), 8)
checkSuccess(cur.getColumnLength(10), 4)
checkSuccess(cur.getColumnLength('testsmalldatetime'), 4)
# these seem to fluctuate too
#checkSuccess(cur.getColumnLength(11),40)
#checkSuccess(cur.getColumnLength('testchar'),40)
#checkSuccess(cur.getColumnLength(12),40)
#checkSuccess(cur.getColumnLength('testvarchar'),40)
checkSuccess(cur.getColumnLength(13), 1)
checkSuccess(cur.getColumnLength('testbit'), 1)
print()
print("LONGEST COLUMN: ")
checkSuccess(cur.getLongest(0), 1)
checkSuccess(cur.getLongest('testint'), 1)
checkSuccess(cur.getLongest(1), 1)
checkSuccess(cur.getLongest('testsmallint'), 1)
checkSuccess(cur.getLongest(2), 1)
checkSuccess(cur.getLongest('testtinyint'), 1)
#checkSuccess(cur.getLongest(3),3)
#checkSuccess(cur.getLongest('testreal'),3)
#checkSuccess(cur.getLongest(4),17)
#checkSuccess(cur.getLongest('testfloat'),17)
checkSuccess(cur.getLongest(5), 3)
checkSuccess(cur.getLongest('testdecimal'), 3)
checkSuccess(cur.getLongest(6), 3)
checkSuccess(cur.getLongest('testnumeric'), 3)
#checkSuccess(cur.getLongest(7),4)
#checkSuccess(cur.getLongest('testmoney'),4)
#checkSuccess(cur.getLongest(8),4)
#checkSuccess(cur.getLongest('testsmallmoney'),4)
#checkSuccess(cur.getLongest(9),26)
#checkSuccess(cur.getLongest('testdatetime'),26)
#checkSuccess(cur.getLongest(10),26)
#checkSuccess(cur.getLongest('testsmalldatetime'),26)
checkSuccess(cur.getLongest(11), 40)
checkSuccess(cur.getLongest('testchar'), 40)
checkSuccess(cur.getLongest(12), 12)
checkSuccess(cur.getLongest('testvarchar'), 12)
checkSuccess(cur.getLongest(13), 1)
checkSuccess(cur.getLongest('testbit'), 1)
print()
print("ROW COUNT: ")
checkSuccess(cur.rowCount(), 8)
print()
print("TOTAL ROWS: ")
checkSuccess(cur.totalRows(), 0)
print()
print("FIRST ROW INDEX: ")
checkSuccess(cur.firstRowIndex(), 0)
print()
print("END OF RESULT SET: ")
checkSuccess(cur.endOfResultSet(), 1)
print()
print("FIELDS BY INDEX: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), 1)
#checkSuccess(cur.getField(0,3),Decimal("1.1"))
#checkSuccess(cur.getField(0,4),Decimal("1.1"))
checkSuccess(cur.getField(0, 5), Decimal("1.1"))
checkSuccess(cur.getField(0, 6), Decimal("1.1"))
#checkSuccess(cur.getField(0,7),Decimal("1.00"))
#checkSuccess(cur.getField(0,8),Decimal("1.00"))
#checkSuccess(cur.getField(0,9),"Jan 1 2001 01:00:00:000AM")
#checkSuccess(cur.getField(0,10),"Jan 1 2001 01:00:00:000AM")
checkSuccess(cur.getField(0, 11),
"testchar1 ")
checkSuccess(cur.getField(0, 12), "testvarchar1")
checkSuccess(cur.getField(0, 13), 1)
print()
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(7, 1), 8)
checkSuccess(cur.getField(7, 2), 8)
#checkSuccess(cur.getField(7,3),Decimal("8.8"))
#checkSuccess(cur.getField(7,4),Decimal("8.8"))
checkSuccess(cur.getField(7, 5), Decimal("8.8"))
checkSuccess(cur.getField(7, 6), Decimal("8.8"))
#checkSuccess(cur.getField(7,7),Decimal("8.00"))
#checkSuccess(cur.getField(7,8),Decimal("8.00"))
#checkSuccess(cur.getField(7,9),"Jan 1 2008 08:00:00:000AM")
#checkSuccess(cur.getField(7,10),"Jan 1 2008 08:00:00:000AM")
checkSuccess(cur.getField(7, 11),
"testchar8 ")
checkSuccess(cur.getField(7, 12), "testvarchar8")
checkSuccess(cur.getField(7, 13), 1)
print()
print("FIELD LENGTHS BY INDEX: ")
checkSuccess(cur.getFieldLength(0, 0), 1)
checkSuccess(cur.getFieldLength(0, 1), 1)
checkSuccess(cur.getFieldLength(0, 2), 1)
#checkSuccess(cur.getFieldLength(0,3),3)
#checkSuccess(cur.getFieldLength(0,4),3)
checkSuccess(cur.getFieldLength(0, 5), 3)
checkSuccess(cur.getFieldLength(0, 6), 3)
#checkSuccess(cur.getFieldLength(0,7),4)
#checkSuccess(cur.getFieldLength(0,8),4)
#checkSuccess(cur.getFieldLength(0,9),26)
#checkSuccess(cur.getFieldLength(0,10),26)
checkSuccess(cur.getFieldLength(0, 11), 40)
checkSuccess(cur.getFieldLength(0, 12), 12)
checkSuccess(cur.getFieldLength(0, 13), 1)
print()
checkSuccess(cur.getFieldLength(7, 0), 1)
checkSuccess(cur.getFieldLength(7, 1), 1)
checkSuccess(cur.getFieldLength(7, 2), 1)
#checkSuccess(cur.getFieldLength(7,3),3)
#checkSuccess(cur.getFieldLength(7,4),17)
checkSuccess(cur.getFieldLength(7, 5), 3)
checkSuccess(cur.getFieldLength(7, 6), 3)
#checkSuccess(cur.getFieldLength(7,7),4)
#checkSuccess(cur.getFieldLength(7,8),4)
#checkSuccess(cur.getFieldLength(7,9),26)
#checkSuccess(cur.getFieldLength(7,10),26)
checkSuccess(cur.getFieldLength(7, 11), 40)
checkSuccess(cur.getFieldLength(7, 12), 12)
checkSuccess(cur.getFieldLength(7, 13), 1)
print()
print("FIELDS BY NAME: ")
checkSuccess(cur.getField(0, "testint"), 1)
checkSuccess(cur.getField(0, "testsmallint"), 1)
checkSuccess(cur.getField(0, "testtinyint"), 1)
#checkSuccess(cur.getField(0,"testreal"),Decimal("1.1"))
#checkSuccess(cur.getField(0,"testfloat"),Decimal("1.1"))
checkSuccess(cur.getField(0, "testdecimal"), Decimal("1.1"))
checkSuccess(cur.getField(0, "testnumeric"), Decimal("1.1"))
#checkSuccess(cur.getField(0,"testmoney"),Decimal("1.00"))
#checkSuccess(cur.getField(0,"testsmallmoney"),Decimal("1.00"))
#checkSuccess(cur.getField(0,"testdatetime"),"Jan 1 2001 01:00:00:000AM")
#checkSuccess(cur.getField(0,"testsmalldatetime"),"Jan 1 2001 01:00:00:000AM")
checkSuccess(cur.getField(0, "testchar"),
"testchar1 ")
checkSuccess(cur.getField(0, "testvarchar"), "testvarchar1")
checkSuccess(cur.getField(0, "testbit"), 1)
print()
checkSuccess(cur.getField(7, "testint"), 8)
checkSuccess(cur.getField(7, "testsmallint"), 8)
checkSuccess(cur.getField(7, "testtinyint"), 8)
#checkSuccess(cur.getField(7,"testreal"),Decimal("8.8"))
#checkSuccess(cur.getField(7,"testfloat"),Decimal("8.8"))
checkSuccess(cur.getField(7, "testdecimal"), Decimal("8.8"))
checkSuccess(cur.getField(7, "testnumeric"), Decimal("8.8"))
#checkSuccess(cur.getField(7,"testmoney"),Decimal("8.00"))
#checkSuccess(cur.getField(7,"testsmallmoney"),Decimal("8.00"))
#checkSuccess(cur.getField(7,"testdatetime"),"Jan 1 2008 08:00:00:000AM")
#checkSuccess(cur.getField(7,"testsmalldatetime"),"Jan 1 2008 08:00:00:000AM")
checkSuccess(cur.getField(7, "testchar"),
"testchar8 ")
checkSuccess(cur.getField(7, "testvarchar"), "testvarchar8")
checkSuccess(cur.getField(7, "testbit"), 1)
print()
print("FIELD LENGTHS BY NAME: ")
checkSuccess(cur.getFieldLength(0, "testint"), 1)
checkSuccess(cur.getFieldLength(0, "testsmallint"), 1)
checkSuccess(cur.getFieldLength(0, "testtinyint"), 1)
#checkSuccess(cur.getFieldLength(0,"testreal"),3)
#checkSuccess(cur.getFieldLength(0,"testfloat"),3)
checkSuccess(cur.getFieldLength(0, "testdecimal"), 3)
checkSuccess(cur.getFieldLength(0, "testnumeric"), 3)
#checkSuccess(cur.getFieldLength(0,"testmoney"),4)
#checkSuccess(cur.getFieldLength(0,"testsmallmoney"),4)
#checkSuccess(cur.getFieldLength(0,"testdatetime"),26)
#checkSuccess(cur.getFieldLength(0,"testsmalldatetime"),26)
checkSuccess(cur.getFieldLength(0, "testchar"), 40)
checkSuccess(cur.getFieldLength(0, "testvarchar"), 12)
checkSuccess(cur.getFieldLength(0, "testbit"), 1)
print()
checkSuccess(cur.getFieldLength(7, "testint"), 1)
checkSuccess(cur.getFieldLength(7, "testsmallint"), 1)
checkSuccess(cur.getFieldLength(7, "testtinyint"), 1)
#checkSuccess(cur.getFieldLength(7,"testreal"),3)
#checkSuccess(cur.getFieldLength(7,"testfloat"),17)
checkSuccess(cur.getFieldLength(7, "testdecimal"), 3)
checkSuccess(cur.getFieldLength(7, "testnumeric"), 3)
#checkSuccess(cur.getFieldLength(7,"testmoney"),4)
#checkSuccess(cur.getFieldLength(7,"testsmallmoney"),4)
#checkSuccess(cur.getFieldLength(7,"testdatetime"),26)
#checkSuccess(cur.getFieldLength(7,"testsmalldatetime"),26)
checkSuccess(cur.getFieldLength(7, "testchar"), 40)
checkSuccess(cur.getFieldLength(7, "testvarchar"), 12)
checkSuccess(cur.getFieldLength(7, "testbit"), 1)
print()
print("FIELDS BY ARRAY: ")
fields = cur.getRow(0)
checkSuccess(fields[0], 1)
checkSuccess(fields[1], 1)
checkSuccess(fields[2], 1)
#checkSuccess(fields[3],Decimal("1.1"))
#checkSuccess(fields[4],Decimal("1.1"))
checkSuccess(fields[5], Decimal("1.1"))
checkSuccess(fields[6], Decimal("1.1"))
#checkSuccess(fields[7],Decimal("1.0"))
#checkSuccess(fields[8],Decimal("1.0"))
#checkSuccess(fields[9],"Jan 1 2001 01:00:00:000AM")
#checkSuccess(fields[10],"Jan 1 2001 01:00:00:000AM")
checkSuccess(fields[11], "testchar1 ")
checkSuccess(fields[12], "testvarchar1")
checkSuccess(fields[13], 1)
print()
print("FIELD LENGTHS BY ARRAY: ")
fieldlens = cur.getRowLengths(0)
checkSuccess(fieldlens[0], 1)
checkSuccess(fieldlens[1], 1)
checkSuccess(fieldlens[2], 1)
#checkSuccess(fieldlens[3],3)
#checkSuccess(fieldlens[4],3)
checkSuccess(fieldlens[5], 3)
checkSuccess(fieldlens[6], 3)
#checkSuccess(fieldlens[7],4)
#checkSuccess(fieldlens[8],4)
#checkSuccess(fieldlens[9],26)
#checkSuccess(fieldlens[10],26)
checkSuccess(fieldlens[11], 40)
checkSuccess(fieldlens[12], 12)
checkSuccess(fieldlens[13], 1)
print()
print("FIELDS BY DICTIONARY: ")
fields = cur.getRowDictionary(0)
checkSuccess(fields["testint"], 1)
checkSuccess(fields["testsmallint"], 1)
checkSuccess(fields["testtinyint"], 1)
#checkSuccess(fields["testreal"],Decimal("1.1"))
#checkSuccess(fields["testfloat"],Decimal("1.1"))
checkSuccess(fields["testdecimal"], Decimal("1.1"))
checkSuccess(fields["testnumeric"], Decimal("1.1"))
#checkSuccess(fields["testmoney"],Decimal("1.0"))
#checkSuccess(fields["testsmallmoney"],Decimal("1.0"))
#checkSuccess(fields["testdatetime"],"Jan 1 2001 01:00:00:000AM")
#checkSuccess(fields["testsmalldatetime"],"Jan 1 2001 01:00:00:000AM")
checkSuccess(fields["testchar"],
"testchar1 ")
checkSuccess(fields["testvarchar"], "testvarchar1")
checkSuccess(fields["testbit"], 1)
print()
fields = cur.getRowDictionary(7)
checkSuccess(fields["testint"], 8)
checkSuccess(fields["testsmallint"], 8)
checkSuccess(fields["testtinyint"], 8)
#checkSuccess(fields["testreal"],Decimal("8.8"))
#checkSuccess(fields["testfloat"],Decimal("8.8"))
#checkSuccess(fields["testdecimal"],Decimal("8.8"))
#checkSuccess(fields["testnumeric"],Decimal("8.8"))
#checkSuccess(fields["testmoney"],Decimal("8.0"))
#checkSuccess(fields["testsmallmoney"],Decimal("8.0"))
#checkSuccess(fields["testdatetime"],"Jan 1 2008 08:00:00:000AM")
#checkSuccess(fields["testsmalldatetime"],"Jan 1 2008 08:00:00:000AM")
checkSuccess(fields["testchar"],
"testchar8 ")
checkSuccess(fields["testvarchar"], "testvarchar8")
checkSuccess(fields["testbit"], 1)
print()
print("FIELD LENGTHS BY DICTIONARY: ")
fieldlengths = cur.getRowLengthsDictionary(0)
checkSuccess(fieldlengths["testint"], 1)
checkSuccess(fieldlengths["testsmallint"], 1)
checkSuccess(fieldlengths["testtinyint"], 1)
#checkSuccess(fieldlengths["testreal"],3)
#checkSuccess(fieldlengths["testfloat"],3)
checkSuccess(fieldlengths["testdecimal"], 3)
checkSuccess(fieldlengths["testnumeric"], 3)
#checkSuccess(fieldlengths["testmoney"],4)
#checkSuccess(fieldlengths["testsmallmoney"],4)
#checkSuccess(fieldlengths["testdatetime"],26)
#checkSuccess(fieldlengths["testsmalldatetime"],26)
checkSuccess(fieldlengths["testchar"], 40)
checkSuccess(fieldlengths["testvarchar"], 12)
checkSuccess(fieldlengths["testbit"], 1)
print()
fieldlengths = cur.getRowLengthsDictionary(7)
checkSuccess(fieldlengths["testsmallint"], 1)
checkSuccess(fieldlengths["testtinyint"], 1)
#checkSuccess(fieldlengths["testreal"],3)
#checkSuccess(fieldlengths["testfloat"],17)
checkSuccess(fieldlengths["testdecimal"], 3)
checkSuccess(fieldlengths["testnumeric"], 3)
#checkSuccess(fieldlengths["testmoney"],4)
#checkSuccess(fieldlengths["testsmallmoney"],4)
#checkSuccess(fieldlengths["testdatetime"],26)
#checkSuccess(fieldlengths["testsmalldatetime"],26)
checkSuccess(fieldlengths["testchar"], 40)
checkSuccess(fieldlengths["testvarchar"], 12)
checkSuccess(fieldlengths["testbit"], 1)
print()
print("INDIVIDUAL SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3)")
cur.substitution("var1", 1)
cur.substitution("var2", "hello")
cur.substitution("var3", 10.5556, 6, 4)
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
checkSuccess(cur.getField(0, 2), Decimal("10.5556"))
print()
print("ARRAY SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3)")
cur.substitutions(["var1", "var2", "var3"], [1, "hello", 10.5556],
[0, 0, 6], [0, 0, 4])
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
checkSuccess(cur.getField(0, 2), Decimal("10.5556"))
print()
print("NULLS as Nones: ")
cur.getNullsAsNone()
checkSuccess(cur.sendQuery("select NULL,1,NULL"), 1)
checkSuccess(cur.getField(0, 0), None)
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), None)
cur.getNullsAsEmptyStrings()
checkSuccess(cur.sendQuery("select NULL,1,NULL"), 1)
# not a good test, sap makes all of these columns int type
checkSuccess(cur.getField(0, 0), 0)
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), 0)
cur.getNullsAsNone()
print()
print("RESULT SET BUFFER SIZE: ")
checkSuccess(cur.getResultSetBufferSize(), 0)
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getResultSetBufferSize(), 2)
print()
checkSuccess(cur.firstRowIndex(), 0)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 2)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
print()
checkSuccess(cur.firstRowIndex(), 2)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 4)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
print()
print("DONT GET COLUMN INFO: ")
cur.dontGetColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getColumnName(0), None)
checkSuccess(cur.getColumnLength(0), 0)
checkSuccess(cur.getColumnType(0), None)
cur.getColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getColumnName(0), "testint")
checkSuccess(cur.getColumnLength(0), 4)
checkSuccess(cur.getColumnType(0), "INT")
print()
print("SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
print("SUSPENDED RESULT SET: ")
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(2, 0), 3)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET: ")
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
print()
print("COLUMN COUNT FOR CACHED RESULT SET: ")
checkSuccess(cur.colCount(), 14)
print()
print("COLUMN NAMES FOR CACHED RESULT SET: ")
checkSuccess(cur.getColumnName(0), "testint")
checkSuccess(cur.getColumnName(1), "testsmallint")
checkSuccess(cur.getColumnName(2), "testtinyint")
checkSuccess(cur.getColumnName(3), "testreal")
checkSuccess(cur.getColumnName(4), "testfloat")
checkSuccess(cur.getColumnName(5), "testdecimal")
checkSuccess(cur.getColumnName(6), "testnumeric")
checkSuccess(cur.getColumnName(7), "testmoney")
checkSuccess(cur.getColumnName(8), "testsmallmoney")
checkSuccess(cur.getColumnName(9), "testdatetime")
checkSuccess(cur.getColumnName(10), "testsmalldatetime")
checkSuccess(cur.getColumnName(11), "testchar")
checkSuccess(cur.getColumnName(12), "testvarchar")
checkSuccess(cur.getColumnName(13), "testbit")
cols = cur.getColumnNames()
checkSuccess(cols[0], "testint")
checkSuccess(cols[1], "testsmallint")
checkSuccess(cols[2], "testtinyint")
checkSuccess(cols[3], "testreal")
checkSuccess(cols[4], "testfloat")
checkSuccess(cols[5], "testdecimal")
checkSuccess(cols[6], "testnumeric")
checkSuccess(cols[7], "testmoney")
checkSuccess(cols[8], "testsmallmoney")
checkSuccess(cols[9], "testdatetime")
checkSuccess(cols[10], "testsmalldatetime")
checkSuccess(cols[11], "testchar")
checkSuccess(cols[12], "testvarchar")
checkSuccess(cols[13], "testbit")
print()
print("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("FROM ONE CACHE FILE TO ANOTHER: ")
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
print()
print("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(2, 0), 3)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
print()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeCachedResultSet(id, filename), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.cacheOff()
print()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("ROW RANGE:")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
print()
rows = cur.getRowRange(0, 5)
checkSuccess(rows[0][0], 1)
checkSuccess(rows[0][1], 1)
checkSuccess(rows[0][2], 1)
#checkSuccess(rows[0][3],Decimal("1.1"))
#checkSuccess(rows[0][4],Decimal("1.1"))
checkSuccess(rows[0][5], Decimal("1.1"))
checkSuccess(rows[0][6], Decimal("1.1"))
checkSuccess(rows[0][7], Decimal("1.00"))
checkSuccess(rows[0][8], Decimal("1.00"))
#checkSuccess(rows[0][9],"Jan 1 2001 01:00:00:000AM")
#checkSuccess(rows[0][10],"Jan 1 2001 01:00:00:000AM")
checkSuccess(rows[0][11], "testchar1 ")
checkSuccess(rows[0][12], "testvarchar1")
checkSuccess(rows[0][13], 1)
print()
checkSuccess(rows[1][0], 2)
checkSuccess(rows[1][1], 2)
checkSuccess(rows[1][2], 2)
#checkSuccess(rows[1][3],Decimal("2.2"))
#checkSuccess(rows[1][4],Decimal("2.2"))
#checkSuccess(rows[1][5],Decimal("2.2"))
#checkSuccess(rows[1][6],Decimal("2.2"))
checkSuccess(rows[1][7], Decimal("2.00"))
checkSuccess(rows[1][8], Decimal("2.00"))
#checkSuccess(rows[1][9],"Jan 1 2002 02:00:00:000AM")
#checkSuccess(rows[1][10],"Jan 1 2002 02:00:00:000AM")
checkSuccess(rows[1][11], "testchar2 ")
checkSuccess(rows[1][12], "testvarchar2")
checkSuccess(rows[1][13], 1)
print()
checkSuccess(rows[2][0], 3)
checkSuccess(rows[2][1], 3)
checkSuccess(rows[2][2], 3)
#checkSuccess(rows[2][3],Decimal("3.3"))
#checkSuccess(rows[2][4],Decimal("3.3"))
#checkSuccess(rows[2][5],Decimal("3.3"))
#checkSuccess(rows[2][6],Decimal("3.3"))
checkSuccess(rows[2][7], Decimal("3.00"))
checkSuccess(rows[2][8], Decimal("3.00"))
#checkSuccess(rows[2][9],"Jan 1 2003 03:00:00:000AM")
#checkSuccess(rows[2][10],"Jan 1 2003 03:00:00:000AM")
checkSuccess(rows[2][11], "testchar3 ")
checkSuccess(rows[2][12], "testvarchar3")
checkSuccess(rows[2][13], 1)
print()
checkSuccess(rows[3][0], 4)
checkSuccess(rows[3][1], 4)
checkSuccess(rows[3][2], 4)
#checkSuccess(rows[3][3],Decimal("4.4"))
#checkSuccess(rows[3][4],Decimal("4.4"))
#checkSuccess(rows[3][5],Decimal("4.4"))
#checkSuccess(rows[3][6],Decimal("4.4"))
checkSuccess(rows[3][7], Decimal("4.00"))
checkSuccess(rows[3][8], Decimal("4.00"))
#checkSuccess(rows[3][9],"Jan 1 2004 04:00:00:000AM")
#checkSuccess(rows[3][10],"Jan 1 2004 04:00:00:000AM")
checkSuccess(rows[3][11], "testchar4 ")
checkSuccess(rows[3][12], "testvarchar4")
checkSuccess(rows[3][13], 1)
print()
checkSuccess(rows[4][0], 5)
checkSuccess(rows[4][1], 5)
checkSuccess(rows[4][2], 5)
#checkSuccess(rows[4][3],Decimal("5.5"))
#checkSuccess(rows[4][4],Decimal("5.5"))
#checkSuccess(rows[4][5],Decimal("5.5"))
#checkSuccess(rows[4][6],Decimal("5.5"))
checkSuccess(rows[4][7], Decimal("5.00"))
checkSuccess(rows[4][8], Decimal("5.00"))
#checkSuccess(rows[4][9],"Jan 1 2005 05:00:00:000AM")
#checkSuccess(rows[4][10],"Jan 1 2005 05:00:00:000AM")
checkSuccess(rows[4][11], "testchar5 ")
checkSuccess(rows[4][12], "testvarchar5")
checkSuccess(rows[4][13], 1)
print()
checkSuccess(rows[5][0], 6)
checkSuccess(rows[5][1], 6)
checkSuccess(rows[5][2], 6)
#checkSuccess(rows[5][3],Decimal("6.6"))
#checkSuccess(rows[5][4],Decimal("6.6"))
#checkSuccess(rows[5][5],Decimal("6.6"))
#checkSuccess(rows[5][6],Decimal("6.6"))
checkSuccess(rows[5][7], Decimal("6.00"))
checkSuccess(rows[5][8], Decimal("6.00"))
#checkSuccess(rows[5][9],"Jan 1 2006 06:00:00:000AM")
#checkSuccess(rows[5][10],"Jan 1 2006 06:00:00:000AM")
checkSuccess(rows[5][11], "testchar6 ")
checkSuccess(rows[5][12], "testvarchar6")
checkSuccess(rows[5][13], 1)
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
checkSuccess(cur.getField(4, 0), None)
checkSuccess(cur.getField(5, 0), None)
checkSuccess(cur.getField(6, 0), None)
checkSuccess(cur.getField(7, 0), None)
print()
# drop existing table
cur.sendQuery("commit tran")
cur.sendQuery("drop table testtable")
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 0)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 0)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 0)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 0)
print()
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
print()
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
print()
def main():
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con = PySQLRClient.sqlrconnection("sqlrelay", 9000, "/tmp/test.socket",
"test", "test")
cur = PySQLRClient.sqlrcursor(con)
# get database type
print("IDENTIFY: ")
checkSuccess(con.identify(), "sqlite")
print()
# ping
print("PING: ")
checkSuccess(con.ping(), 1)
print()
# drop existing table
cur.sendQuery("begin transaction")
cur.sendQuery("drop table testtable")
con.commit()
# create a new table
print("CREATE TEMPTABLE: ")
cur.sendQuery("begin transaction")
checkSuccess(
cur.sendQuery(
"create table testtable (testint int, testfloat float, testchar char(40), testvarchar varchar(40))"
), 1)
con.commit()
print()
print("INSERT: ")
cur.sendQuery("begin transaction")
checkSuccess(
cur.sendQuery(
"insert into testtable values (1,1.1,'testchar1','testvarchar1')"),
1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (2,2.2,'testchar2','testvarchar2')"),
1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (3,3.3,'testchar3','testvarchar3')"),
1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (4,4.4,'testchar4','testvarchar4')"),
1)
print()
print("AFFECTED ROWS: ")
checkSuccess(cur.affectedRows(), 0)
print()
print("BIND BY NAME: ")
cur.prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4)")
checkSuccess(cur.countBindVariables(), 4)
cur.inputBind("var1", 5)
cur.inputBind("var2", Decimal("5.5"), 4, 1)
cur.inputBind("var3", "testchar5")
cur.inputBind("var4", "testvarchar5")
checkSuccess(cur.executeQuery(), 1)
cur.clearBinds()
cur.inputBind("var1", 6)
cur.inputBind("var2", Decimal("6.6"), 4, 1)
cur.inputBind("var3", "testchar6")
cur.inputBind("var4", "testvarchar6")
checkSuccess(cur.executeQuery(), 1)
print()
print("ARRAY OF BINDS BY NAME: ")
cur.clearBinds()
cur.inputBinds(["var1", "var2", "var3", "var4"],
[7, 7.7, "testchar7", "testvarchar7"], [0, 4, 0, 0],
[0, 1, 0, 0])
checkSuccess(cur.executeQuery(), 1)
print()
print("BIND BY NAME WITH VALIDATION: ")
cur.clearBinds()
cur.inputBind("var1", 8)
cur.inputBind("var2", 8.8, 4, 1)
cur.inputBind("var3", "testchar8")
cur.inputBind("var4", "testvarchar8")
cur.validateBinds()
checkSuccess(cur.executeQuery(), 1)
print()
print("SELECT: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
print()
print("COLUMN COUNT: ")
checkSuccess(cur.colCount(), 4)
print()
print("COLUMN NAMES: ")
checkSuccess(cur.getColumnName(0), "testint")
checkSuccess(cur.getColumnName(1), "testfloat")
checkSuccess(cur.getColumnName(2), "testchar")
checkSuccess(cur.getColumnName(3), "testvarchar")
cols = cur.getColumnNames()
checkSuccess(cols[0], "testint")
checkSuccess(cols[1], "testfloat")
checkSuccess(cols[2], "testchar")
checkSuccess(cols[3], "testvarchar")
print()
print("COLUMN TYPES: ")
checkSuccess(cur.getColumnType(0), "INTEGER")
checkSuccess(cur.getColumnType('testint'), "INTEGER")
checkSuccess(cur.getColumnType(1), "FLOAT")
checkSuccess(cur.getColumnType('testfloat'), "FLOAT")
checkSuccess(cur.getColumnType(2), "STRING")
checkSuccess(cur.getColumnType('testchar'), "STRING")
checkSuccess(cur.getColumnType(3), "STRING")
checkSuccess(cur.getColumnType('testvarchar'), "STRING")
print()
print("COLUMN LENGTH: ")
checkSuccess(cur.getColumnLength(0), 0)
checkSuccess(cur.getColumnLength('testint'), 0)
checkSuccess(cur.getColumnLength(1), 0)
checkSuccess(cur.getColumnLength('testfloat'), 0)
checkSuccess(cur.getColumnLength(2), 0)
checkSuccess(cur.getColumnLength('testchar'), 0)
checkSuccess(cur.getColumnLength(3), 0)
checkSuccess(cur.getColumnLength('testvarchar'), 0)
print()
print("LONGEST COLUMN: ")
checkSuccess(cur.getLongest(0), 1)
checkSuccess(cur.getLongest('testint'), 1)
checkSuccess(cur.getLongest(1), 3)
checkSuccess(cur.getLongest('testfloat'), 3)
checkSuccess(cur.getLongest(2), 9)
checkSuccess(cur.getLongest('testchar'), 9)
checkSuccess(cur.getLongest(3), 12)
checkSuccess(cur.getLongest('testvarchar'), 12)
print()
print("ROW COUNT: ")
checkSuccess(cur.rowCount(), 8)
print()
print("TOTAL ROWS: ")
checkSuccess(cur.totalRows(), 0)
print()
print("FIRST ROW INDEX: ")
checkSuccess(cur.firstRowIndex(), 0)
print()
print("END OF RESULT SET: ")
checkSuccess(cur.endOfResultSet(), 1)
print()
print("FIELDS BY INDEX: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), Decimal("1.1"))
checkSuccess(cur.getField(0, 2), "testchar1")
checkSuccess(cur.getField(0, 3), "testvarchar1")
print()
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(7, 1), Decimal("8.8"))
checkSuccess(cur.getField(7, 2), "testchar8")
checkSuccess(cur.getField(7, 3), "testvarchar8")
print()
print("FIELD LENGTHS BY INDEX: ")
checkSuccess(cur.getFieldLength(0, 0), 1)
checkSuccess(cur.getFieldLength(0, 1), 3)
checkSuccess(cur.getFieldLength(0, 2), 9)
checkSuccess(cur.getFieldLength(0, 3), 12)
print()
checkSuccess(cur.getFieldLength(7, 0), 1)
checkSuccess(cur.getFieldLength(7, 1), 3)
checkSuccess(cur.getFieldLength(7, 2), 9)
checkSuccess(cur.getFieldLength(7, 3), 12)
print()
print("FIELDS BY NAME: ")
checkSuccess(cur.getField(0, "testint"), 1)
checkSuccess(cur.getField(0, "testfloat"), Decimal("1.1"))
checkSuccess(cur.getField(0, "testchar"), "testchar1")
checkSuccess(cur.getField(0, "testvarchar"), "testvarchar1")
print()
checkSuccess(cur.getField(7, "testint"), 8)
checkSuccess(cur.getField(7, "testfloat"), Decimal("8.8"))
checkSuccess(cur.getField(7, "testchar"), "testchar8")
checkSuccess(cur.getField(7, "testvarchar"), "testvarchar8")
print()
print("FIELD LENGTHS BY NAME: ")
checkSuccess(cur.getFieldLength(0, "testint"), 1)
checkSuccess(cur.getFieldLength(0, "testfloat"), 3)
checkSuccess(cur.getFieldLength(0, "testchar"), 9)
checkSuccess(cur.getFieldLength(0, "testvarchar"), 12)
print()
checkSuccess(cur.getFieldLength(7, "testint"), 1)
checkSuccess(cur.getFieldLength(7, "testfloat"), 3)
checkSuccess(cur.getFieldLength(7, "testchar"), 9)
checkSuccess(cur.getFieldLength(7, "testvarchar"), 12)
print()
print("FIELDS BY ARRAY: ")
fields = cur.getRow(0)
checkSuccess(fields[0], 1)
checkSuccess(fields[1], Decimal("1.1"))
checkSuccess(fields[2], "testchar1")
checkSuccess(fields[3], "testvarchar1")
print()
print("FIELD LENGTHS BY ARRAY: ")
fieldlens = cur.getRowLengths(0)
checkSuccess(fieldlens[0], 1)
checkSuccess(fieldlens[1], 3)
checkSuccess(fieldlens[2], 9)
checkSuccess(fieldlens[3], 12)
print()
print("FIELDS BY DICTIONARY: ")
fields = cur.getRowDictionary(0)
checkSuccess(fields["testint"], 1)
checkSuccess(fields["testfloat"], Decimal("1.1"))
checkSuccess(fields["testchar"], "testchar1")
checkSuccess(fields["testvarchar"], "testvarchar1")
print()
fields = cur.getRowDictionary(7)
checkSuccess(fields["testint"], 8)
checkSuccess(fields["testfloat"], Decimal("8.8"))
checkSuccess(fields["testchar"], "testchar8")
checkSuccess(fields["testvarchar"], "testvarchar8")
print()
print("FIELD LENGTHS BY DICTIONARY: ")
fieldlengths = cur.getRowLengthsDictionary(0)
checkSuccess(fieldlengths["testint"], 1)
checkSuccess(fieldlengths["testfloat"], 3)
checkSuccess(fieldlengths["testchar"], 9)
checkSuccess(fieldlengths["testvarchar"], 12)
print()
fieldlengths = cur.getRowLengthsDictionary(7)
checkSuccess(fieldlengths["testint"], 1)
checkSuccess(fieldlengths["testfloat"], 3)
checkSuccess(fieldlengths["testchar"], 9)
checkSuccess(fieldlengths["testvarchar"], 12)
print()
print("INDIVIDUAL SUBSTITUTIONS: ")
cur.sendQuery("drop table testtable1")
checkSuccess(
cur.sendQuery(
"create table testtable1 (col1 int, col2 char, col3 float)"), 1)
cur.prepareQuery(
"insert into testtable1 values ($(var1),'$(var2)',$(var3))")
cur.substitution("var1", 1)
cur.substitution("var2", "hello")
cur.substitution("var3", 10.5556, 6, 4)
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.sendQuery("select * from testtable1"), 1)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
checkSuccess(cur.getField(0, 2), Decimal("10.5556"))
checkSuccess(cur.sendQuery("delete from testtable1"), 1)
print()
print("ARRAY SUBSTITUTIONS: ")
cur.prepareQuery(
"insert into testtable1 values ($(var1),'$(var2)',$(var3))")
cur.substitutions(["var1", "var2", "var3"], [1, "hello", 10.5556],
[0, 0, 6], [0, 0, 4])
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.sendQuery("select * from testtable1"), 1)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
checkSuccess(cur.getField(0, 2), Decimal("10.5556"))
checkSuccess(cur.sendQuery("delete from testtable1"), 1)
print()
print("NULLS as Nones: ")
cur.getNullsAsNone()
checkSuccess(cur.sendQuery("insert into testtable1 values (1,NULL,NULL)"),
1)
checkSuccess(cur.sendQuery("select * from testtable1"), 1)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), None)
checkSuccess(cur.getField(0, 2), None)
cur.getNullsAsEmptyStrings()
checkSuccess(cur.sendQuery("select * from testtable1"), 1)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "")
checkSuccess(cur.getField(0, 2), "")
cur.getNullsAsNone()
print()
print("RESULT SET BUFFER SIZE: ")
checkSuccess(cur.getResultSetBufferSize(), 0)
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getResultSetBufferSize(), 2)
print()
checkSuccess(cur.firstRowIndex(), 0)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 2)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
print()
checkSuccess(cur.firstRowIndex(), 2)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 4)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
print()
print("DONT GET COLUMN INFO: ")
cur.dontGetColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getColumnName(0), None)
checkSuccess(cur.getColumnLength(0), 0)
checkSuccess(cur.getColumnType(0), None)
cur.getColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getColumnName(0), "testint")
checkSuccess(cur.getColumnLength(0), 0)
checkSuccess(cur.getColumnType(0), "INTEGER")
print()
print("SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
print("SUSPENDED RESULT SET: ")
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(2, 0), 3)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET: ")
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
print()
print("COLUMN COUNT FOR CACHED RESULT SET: ")
checkSuccess(cur.colCount(), 4)
print()
print("COLUMN NAMES FOR CACHED RESULT SET: ")
checkSuccess(cur.getColumnName(0), "testint")
checkSuccess(cur.getColumnName(1), "testfloat")
checkSuccess(cur.getColumnName(2), "testchar")
checkSuccess(cur.getColumnName(3), "testvarchar")
cols = cur.getColumnNames()
checkSuccess(cols[0], "testint")
checkSuccess(cols[1], "testfloat")
checkSuccess(cols[2], "testchar")
checkSuccess(cols[3], "testvarchar")
print()
print("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("FROM ONE CACHE FILE TO ANOTHER: ")
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
print()
print("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(2, 0), 3)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
print()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeCachedResultSet(id, filename), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.cacheOff()
print()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("ROW RANGE:")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
print()
rows = cur.getRowRange(0, 5)
checkSuccess(rows[0][0], 1)
checkSuccess(rows[0][1], Decimal("1.1"))
checkSuccess(rows[0][2], "testchar1")
checkSuccess(rows[0][3], "testvarchar1")
print()
checkSuccess(rows[1][0], 2)
checkSuccess(rows[1][1], Decimal("2.2"))
checkSuccess(rows[1][2], "testchar2")
checkSuccess(rows[1][3], "testvarchar2")
print()
checkSuccess(rows[2][0], 3)
checkSuccess(rows[2][1], Decimal("3.3"))
checkSuccess(rows[2][2], "testchar3")
checkSuccess(rows[2][3], "testvarchar3")
print()
checkSuccess(rows[3][0], 4)
checkSuccess(rows[3][1], Decimal("4.4"))
checkSuccess(rows[3][2], "testchar4")
checkSuccess(rows[3][3], "testvarchar4")
print()
checkSuccess(rows[4][0], 5)
checkSuccess(rows[4][1], Decimal("5.5"))
checkSuccess(rows[4][2], "testchar5")
checkSuccess(rows[4][3], "testvarchar5")
print()
checkSuccess(rows[5][0], 6)
checkSuccess(rows[5][1], Decimal("6.6"))
checkSuccess(rows[5][2], "testchar6")
checkSuccess(rows[5][3], "testvarchar6")
print()
print("COMMIT AND ROLLBACK: ")
secondcon = PySQLRClient.sqlrconnection("sqlrelay", 9000,
"/tmp/test.socket", "test", "test")
secondcur = PySQLRClient.sqlrcursor(secondcon)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 0)
checkSuccess(con.commit(), 1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 8)
checkSuccess(
cur.sendQuery(
"insert into testtable values (10,10.1,'testchar10','testvarchar10')"
), 1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 9)
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
checkSuccess(cur.getField(4, 0), None)
checkSuccess(cur.getField(5, 0), None)
checkSuccess(cur.getField(6, 0), None)
checkSuccess(cur.getField(7, 0), None)
print()
# drop existing table
cur.sendQuery("drop table testtable")
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable"), 0)
checkSuccess(cur.sendQuery("select * from testtable"), 0)
checkSuccess(cur.sendQuery("select * from testtable"), 0)
checkSuccess(cur.sendQuery("select * from testtable"), 0)
print()
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
print()
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
print()
def main():
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con = PySQLRClient.sqlrconnection("sqlrelay", 9000, "/tmp/test.socket",
"test", "test")
cur = PySQLRClient.sqlrcursor(con)
# get database type
print("IDENTIFY: ")
checkSuccess(con.identify(), "mysql")
# ping
print("PING: ")
checkSuccess(con.ping(), 1)
print()
# drop existing table
cur.sendQuery("drop table testtable")
# create a new table
print("CREATE TEMPTABLE: ")
checkSuccess(
cur.sendQuery(
"create table testdb.testtable (testtinyint tinyint, testsmallint smallint, testmediumint mediumint, testint int, testbigint bigint, testfloat float, testreal real, testdecimal decimal(2,1), testdate date, testtime time, testdatetime datetime, testyear year, testchar char(40), testtext text, testvarchar varchar(40), testtinytext tinytext, testmediumtext mediumtext, testlongtext longtext, testtimestamp timestamp)"
), 1)
print()
print("INSERT: ")
checkSuccess(
cur.sendQuery(
"insert into testdb.testtable values (1,1,1,1,1,1.1,1.1,1.1,'2001-01-01','01:00:00','2001-01-01 01:00:00','2001','char1','text1','varchar1','tinytext1','mediumtext1','longtext1',NULL)"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testdb.testtable values (2,2,2,2,2,2.1,2.1,2.1,'2002-01-01','02:00:00','2002-01-01 02:00:00','2002','char2','text2','varchar2','tinytext2','mediumtext2','longtext2',NULL)"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testdb.testtable values (3,3,3,3,3,3.1,3.1,3.1,'2003-01-01','03:00:00','2003-01-01 03:00:00','2003','char3','text3','varchar3','tinytext3','mediumtext3','longtext3',NULL)"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testdb.testtable values (4,4,4,4,4,4.1,4.1,4.1,'2004-01-01','04:00:00','2004-01-01 04:00:00','2004','char4','text4','varchar4','tinytext4','mediumtext4','longtext4',NULL)"
), 1)
print()
print("AFFECTED ROWS: ")
checkSuccess(cur.affectedRows(), 1)
print()
print("BIND BY POSITION: ")
cur.prepareQuery(
"insert into testdb.testtable values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)"
)
checkSuccess(cur.countBindVariables(), 18)
cur.inputBind("1", 5)
cur.inputBind("2", 5)
cur.inputBind("3", 5)
cur.inputBind("4", 5)
cur.inputBind("5", 5)
cur.inputBind("6", 5.1, 2, 1)
cur.inputBind("7", 5.1, 2, 1)
cur.inputBind("8", 5.1, 2, 1)
cur.inputBind("9", "2005-01-01")
cur.inputBind("10", "05:00:00")
cur.inputBind("11", "2005-01-01 05:00:00")
cur.inputBind("12", "2005")
cur.inputBind("13", "char5")
cur.inputBind("14", "text5")
cur.inputBind("15", "varchar5")
cur.inputBind("16", "tinytext5")
cur.inputBind("17", "mediumtext5")
cur.inputBind("18", "longtext5")
checkSuccess(cur.executeQuery(), 1)
cur.clearBinds()
cur.inputBind("1", 6)
cur.inputBind("2", 6)
cur.inputBind("3", 6)
cur.inputBind("4", 6)
cur.inputBind("5", 6)
cur.inputBind("6", 6.1, 2, 1)
cur.inputBind("7", 6.1, 2, 1)
cur.inputBind("8", 6.1, 2, 1)
cur.inputBind("9", '2006-01-01')
cur.inputBind("10", '06:00:00')
cur.inputBind("11", '2006-01-01 06:00:00')
cur.inputBind("12", '2006')
cur.inputBind("13", 'char6')
cur.inputBind("14", 'text6')
cur.inputBind("15", 'varchar6')
cur.inputBind("16", 'tinytext6')
cur.inputBind("17", 'mediumtext6')
cur.inputBind("18", 'longtext6')
checkSuccess(cur.executeQuery(), 1)
print()
print("ARRAY OF BINDS BY POSITION: ")
cur.clearBinds()
cur.inputBinds([
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
], [
7, 7, 7, 7, 7, 7.1, 7.1, 7.1, '2007-01-01', '07:00:00',
'2007-01-01 07:00:00', '2007', 'char7', 'text7', 'varchar7',
'tinytext7', 'mediumtext7', 'longtext7'
], [0, 0, 0, 0, 0, 2, 2, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],
[0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0])
checkSuccess(cur.executeQuery(), 1)
print()
print("BIND BY POSITION WITH VALIDATION: ")
cur.clearBinds()
cur.inputBind("1", 8)
cur.inputBind("2", 8)
cur.inputBind("3", 8)
cur.inputBind("4", 8)
cur.inputBind("5", 8)
cur.inputBind("6", 8.1, 2, 1)
cur.inputBind("7", 8.1, 2, 1)
cur.inputBind("8", 8.1, 2, 1)
cur.inputBind("9", '2008-01-01')
cur.inputBind("10", '08:00:00')
cur.inputBind("11", '2008-01-01 08:00:00')
cur.inputBind("12", '2008')
cur.inputBind("13", 'char8')
cur.inputBind("14", 'text8')
cur.inputBind("15", 'varchar8')
cur.inputBind("16", 'tinytext8')
cur.inputBind("17", 'mediumtext8')
cur.inputBind("18", 'longtext8')
cur.validateBinds()
checkSuccess(cur.executeQuery(), 1)
print()
print("SELECT: ")
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
print()
print("COLUMN COUNT: ")
checkSuccess(cur.colCount(), 19)
print()
print("COLUMN NAMES: ")
checkSuccess(cur.getColumnName(0), "testtinyint")
checkSuccess(cur.getColumnName(1), "testsmallint")
checkSuccess(cur.getColumnName(2), "testmediumint")
checkSuccess(cur.getColumnName(3), "testint")
checkSuccess(cur.getColumnName(4), "testbigint")
checkSuccess(cur.getColumnName(5), "testfloat")
checkSuccess(cur.getColumnName(6), "testreal")
checkSuccess(cur.getColumnName(7), "testdecimal")
checkSuccess(cur.getColumnName(8), "testdate")
checkSuccess(cur.getColumnName(9), "testtime")
checkSuccess(cur.getColumnName(10), "testdatetime")
checkSuccess(cur.getColumnName(11), "testyear")
checkSuccess(cur.getColumnName(12), "testchar")
checkSuccess(cur.getColumnName(13), "testtext")
checkSuccess(cur.getColumnName(14), "testvarchar")
checkSuccess(cur.getColumnName(15), "testtinytext")
checkSuccess(cur.getColumnName(16), "testmediumtext")
checkSuccess(cur.getColumnName(17), "testlongtext")
checkSuccess(cur.getColumnName(18), "testtimestamp")
cols = cur.getColumnNames()
checkSuccess(cols[0], "testtinyint")
checkSuccess(cols[1], "testsmallint")
checkSuccess(cols[2], "testmediumint")
checkSuccess(cols[3], "testint")
checkSuccess(cols[4], "testbigint")
checkSuccess(cols[5], "testfloat")
checkSuccess(cols[6], "testreal")
checkSuccess(cols[7], "testdecimal")
checkSuccess(cols[8], "testdate")
checkSuccess(cols[9], "testtime")
checkSuccess(cols[10], "testdatetime")
checkSuccess(cols[11], "testyear")
checkSuccess(cols[12], "testchar")
checkSuccess(cols[13], "testtext")
checkSuccess(cols[14], "testvarchar")
checkSuccess(cols[15], "testtinytext")
checkSuccess(cols[16], "testmediumtext")
checkSuccess(cols[17], "testlongtext")
checkSuccess(cols[18], "testtimestamp")
print()
print("COLUMN TYPES: ")
checkSuccess(cur.getColumnType(0), "TINYINT")
checkSuccess(cur.getColumnType(1), "SMALLINT")
checkSuccess(cur.getColumnType(2), "MEDIUMINT")
checkSuccess(cur.getColumnType(3), "INT")
checkSuccess(cur.getColumnType(4), "BIGINT")
checkSuccess(cur.getColumnType(5), "FLOAT")
checkSuccess(cur.getColumnType(6), "REAL")
checkSuccess(cur.getColumnType(7), "DECIMAL")
checkSuccess(cur.getColumnType(8), "DATE")
checkSuccess(cur.getColumnType(9), "TIME")
checkSuccess(cur.getColumnType(10), "DATETIME")
checkSuccess(cur.getColumnType(11), "YEAR")
checkSuccess(cur.getColumnType(12), "STRING")
checkSuccess(cur.getColumnType(13), "BLOB")
checkSuccess(cur.getColumnType(14), "VARSTRING")
checkSuccess(cur.getColumnType(15), "TINYBLOB")
checkSuccess(cur.getColumnType(16), "MEDIUMBLOB")
checkSuccess(cur.getColumnType(17), "LONGBLOB")
checkSuccess(cur.getColumnType(18), "TIMESTAMP")
checkSuccess(cur.getColumnType("testtinyint"), "TINYINT")
checkSuccess(cur.getColumnType("testsmallint"), "SMALLINT")
checkSuccess(cur.getColumnType("testmediumint"), "MEDIUMINT")
checkSuccess(cur.getColumnType("testint"), "INT")
checkSuccess(cur.getColumnType("testbigint"), "BIGINT")
checkSuccess(cur.getColumnType("testfloat"), "FLOAT")
checkSuccess(cur.getColumnType("testreal"), "REAL")
checkSuccess(cur.getColumnType("testdecimal"), "DECIMAL")
checkSuccess(cur.getColumnType("testdate"), "DATE")
checkSuccess(cur.getColumnType("testtime"), "TIME")
checkSuccess(cur.getColumnType("testdatetime"), "DATETIME")
checkSuccess(cur.getColumnType("testyear"), "YEAR")
checkSuccess(cur.getColumnType("testchar"), "STRING")
checkSuccess(cur.getColumnType("testtext"), "BLOB")
checkSuccess(cur.getColumnType("testvarchar"), "VARSTRING")
checkSuccess(cur.getColumnType("testtinytext"), "TINYBLOB")
checkSuccess(cur.getColumnType("testmediumtext"), "MEDIUMBLOB")
checkSuccess(cur.getColumnType("testlongtext"), "LONGBLOB")
checkSuccess(cur.getColumnType("testtimestamp"), "TIMESTAMP")
print()
print("COLUMN LENGTH: ")
checkSuccess(cur.getColumnLength(0), 1)
checkSuccess(cur.getColumnLength(1), 2)
checkSuccess(cur.getColumnLength(2), 3)
checkSuccess(cur.getColumnLength(3), 4)
checkSuccess(cur.getColumnLength(4), 8)
checkSuccess(cur.getColumnLength(5), 4)
checkSuccess(cur.getColumnLength(6), 8)
checkSuccess(cur.getColumnLength(7), 6)
checkSuccess(cur.getColumnLength(8), 3)
checkSuccess(cur.getColumnLength(9), 3)
checkSuccess(cur.getColumnLength(10), 8)
checkSuccess(cur.getColumnLength(11), 1)
#checkSuccess(cur.getColumnLength(12),40)
checkSuccess(cur.getColumnLength(13), 65535)
#checkSuccess(cur.getColumnLength(14),41)
checkSuccess(cur.getColumnLength(15), 255)
checkSuccess(cur.getColumnLength(16), 16777215)
checkSuccess(cur.getColumnLength(17), 2147483647)
checkSuccess(cur.getColumnLength(18), 4)
checkSuccess(cur.getColumnLength("testtinyint"), 1)
checkSuccess(cur.getColumnLength("testsmallint"), 2)
checkSuccess(cur.getColumnLength("testmediumint"), 3)
checkSuccess(cur.getColumnLength("testint"), 4)
checkSuccess(cur.getColumnLength("testbigint"), 8)
checkSuccess(cur.getColumnLength("testfloat"), 4)
checkSuccess(cur.getColumnLength("testreal"), 8)
checkSuccess(cur.getColumnLength("testdecimal"), 6)
checkSuccess(cur.getColumnLength("testdate"), 3)
checkSuccess(cur.getColumnLength("testtime"), 3)
checkSuccess(cur.getColumnLength("testdatetime"), 8)
checkSuccess(cur.getColumnLength("testyear"), 1)
#checkSuccess(cur.getColumnLength("testchar"),40)
checkSuccess(cur.getColumnLength("testtext"), 65535)
#checkSuccess(cur.getColumnLength("testvarchar"),41)
checkSuccess(cur.getColumnLength("testtinytext"), 255)
checkSuccess(cur.getColumnLength("testmediumtext"), 16777215)
checkSuccess(cur.getColumnLength("testlongtext"), 2147483647)
checkSuccess(cur.getColumnLength("testtimestamp"), 4)
print()
print("LONGEST COLUMN: ")
checkSuccess(cur.getLongest(0), 1)
checkSuccess(cur.getLongest(1), 1)
checkSuccess(cur.getLongest(2), 1)
checkSuccess(cur.getLongest(3), 1)
checkSuccess(cur.getLongest(4), 1)
#checkSuccess(cur.getLongest(5),3)
checkSuccess(cur.getLongest(6), 3)
checkSuccess(cur.getLongest(7), 3)
checkSuccess(cur.getLongest(8), 10)
checkSuccess(cur.getLongest(9), 8)
checkSuccess(cur.getLongest(10), 19)
checkSuccess(cur.getLongest(11), 4)
checkSuccess(cur.getLongest(12), 5)
checkSuccess(cur.getLongest(13), 5)
checkSuccess(cur.getLongest(14), 8)
checkSuccess(cur.getLongest(15), 9)
checkSuccess(cur.getLongest(16), 11)
checkSuccess(cur.getLongest(17), 9)
checkSuccess(cur.getLongest(18), 19)
checkSuccess(cur.getLongest("testtinyint"), 1)
checkSuccess(cur.getLongest("testsmallint"), 1)
checkSuccess(cur.getLongest("testmediumint"), 1)
checkSuccess(cur.getLongest("testint"), 1)
checkSuccess(cur.getLongest("testbigint"), 1)
#checkSuccess(cur.getLongest("testfloat"),3)
checkSuccess(cur.getLongest("testreal"), 3)
checkSuccess(cur.getLongest("testdecimal"), 3)
checkSuccess(cur.getLongest("testdate"), 10)
checkSuccess(cur.getLongest("testtime"), 8)
checkSuccess(cur.getLongest("testdatetime"), 19)
checkSuccess(cur.getLongest("testyear"), 4)
checkSuccess(cur.getLongest("testchar"), 5)
checkSuccess(cur.getLongest("testtext"), 5)
checkSuccess(cur.getLongest("testvarchar"), 8)
checkSuccess(cur.getLongest("testtinytext"), 9)
checkSuccess(cur.getLongest("testmediumtext"), 11)
checkSuccess(cur.getLongest("testlongtext"), 9)
checkSuccess(cur.getLongest("testtimestamp"), 19)
print()
print("ROW COUNT: ")
checkSuccess(cur.rowCount(), 8)
print()
print("TOTAL ROWS: ")
checkSuccess(cur.totalRows(), 0)
print()
print("FIRST ROW INDEX: ")
checkSuccess(cur.firstRowIndex(), 0)
print()
print("END OF RESULT SET: ")
checkSuccess(cur.endOfResultSet(), 1)
print()
print("FIELDS BY INDEX: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), 1)
checkSuccess(cur.getField(0, 3), 1)
checkSuccess(cur.getField(0, 4), 1)
#checkSuccess(cur.getField(0,5),Decimal("1.1"))
#checkSuccess(cur.getField(0,6),Decimal("1.1"))
checkSuccess(cur.getField(0, 7), Decimal("1.1"))
checkSuccess(cur.getField(0, 8), "2001-01-01")
checkSuccess(cur.getField(0, 9), "01:00:00")
checkSuccess(cur.getField(0, 10), "2001-01-01 01:00:00")
checkSuccess(cur.getField(0, 11), 2001)
checkSuccess(cur.getField(0, 12), "char1")
checkSuccess(cur.getField(0, 13), "text1")
checkSuccess(cur.getField(0, 14), "varchar1")
checkSuccess(cur.getField(0, 15), "tinytext1")
checkSuccess(cur.getField(0, 16), "mediumtext1")
checkSuccess(cur.getField(0, 17), "longtext1")
print()
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(7, 1), 8)
checkSuccess(cur.getField(7, 2), 8)
checkSuccess(cur.getField(7, 3), 8)
checkSuccess(cur.getField(7, 4), 8)
#checkSuccess(cur.getField(7,5),Decimal("8.1"))
#checkSuccess(cur.getField(7,6),Decimal("8.1"))
checkSuccess(cur.getField(7, 7), Decimal("8.1"))
checkSuccess(cur.getField(7, 8), "2008-01-01")
checkSuccess(cur.getField(7, 9), "08:00:00")
checkSuccess(cur.getField(7, 10), "2008-01-01 08:00:00")
checkSuccess(cur.getField(7, 11), 2008)
checkSuccess(cur.getField(7, 12), "char8")
checkSuccess(cur.getField(7, 13), "text8")
checkSuccess(cur.getField(7, 14), "varchar8")
checkSuccess(cur.getField(7, 15), "tinytext8")
checkSuccess(cur.getField(7, 16), "mediumtext8")
checkSuccess(cur.getField(7, 17), "longtext8")
print()
print("FIELD LENGTHS BY INDEX: ")
checkSuccess(cur.getFieldLength(0, 0), 1)
checkSuccess(cur.getFieldLength(0, 1), 1)
checkSuccess(cur.getFieldLength(0, 2), 1)
checkSuccess(cur.getFieldLength(0, 3), 1)
checkSuccess(cur.getFieldLength(0, 4), 1)
#checkSuccess(cur.getFieldLength(0,5),3)
checkSuccess(cur.getFieldLength(0, 6), 3)
checkSuccess(cur.getFieldLength(0, 7), 3)
checkSuccess(cur.getFieldLength(0, 8), 10)
checkSuccess(cur.getFieldLength(0, 9), 8)
checkSuccess(cur.getFieldLength(0, 10), 19)
checkSuccess(cur.getFieldLength(0, 11), 4)
checkSuccess(cur.getFieldLength(0, 12), 5)
checkSuccess(cur.getFieldLength(0, 13), 5)
checkSuccess(cur.getFieldLength(0, 14), 8)
checkSuccess(cur.getFieldLength(0, 15), 9)
checkSuccess(cur.getFieldLength(0, 16), 11)
checkSuccess(cur.getFieldLength(0, 17), 9)
print()
checkSuccess(cur.getFieldLength(7, 0), 1)
checkSuccess(cur.getFieldLength(7, 1), 1)
checkSuccess(cur.getFieldLength(7, 2), 1)
checkSuccess(cur.getFieldLength(7, 3), 1)
checkSuccess(cur.getFieldLength(7, 4), 1)
#checkSuccess(cur.getFieldLength(7,5),3)
checkSuccess(cur.getFieldLength(7, 6), 3)
checkSuccess(cur.getFieldLength(7, 7), 3)
checkSuccess(cur.getFieldLength(7, 8), 10)
checkSuccess(cur.getFieldLength(7, 9), 8)
checkSuccess(cur.getFieldLength(7, 10), 19)
checkSuccess(cur.getFieldLength(7, 11), 4)
checkSuccess(cur.getFieldLength(7, 12), 5)
checkSuccess(cur.getFieldLength(7, 13), 5)
checkSuccess(cur.getFieldLength(7, 14), 8)
checkSuccess(cur.getFieldLength(7, 15), 9)
checkSuccess(cur.getFieldLength(7, 16), 11)
checkSuccess(cur.getFieldLength(7, 17), 9)
print()
print("FIELDS BY NAME: ")
checkSuccess(cur.getField(0, "testtinyint"), 1)
checkSuccess(cur.getField(0, "testsmallint"), 1)
checkSuccess(cur.getField(0, "testmediumint"), 1)
checkSuccess(cur.getField(0, "testint"), 1)
checkSuccess(cur.getField(0, "testbigint"), 1)
#checkSuccess(cur.getField(0,"testfloat"),Decimal("1.1"))
#checkSuccess(cur.getField(0,"testreal"),Decimal("1.1"))
checkSuccess(cur.getField(0, "testdecimal"), Decimal("1.1"))
checkSuccess(cur.getField(0, "testdate"), "2001-01-01")
checkSuccess(cur.getField(0, "testtime"), "01:00:00")
checkSuccess(cur.getField(0, "testdatetime"), "2001-01-01 01:00:00")
checkSuccess(cur.getField(0, "testyear"), 2001)
checkSuccess(cur.getField(0, "testchar"), "char1")
checkSuccess(cur.getField(0, "testtext"), "text1")
checkSuccess(cur.getField(0, "testvarchar"), "varchar1")
checkSuccess(cur.getField(0, "testtinytext"), "tinytext1")
checkSuccess(cur.getField(0, "testmediumtext"), "mediumtext1")
checkSuccess(cur.getField(0, "testlongtext"), "longtext1")
print()
checkSuccess(cur.getField(7, "testtinyint"), 8)
checkSuccess(cur.getField(7, "testsmallint"), 8)
checkSuccess(cur.getField(7, "testmediumint"), 8)
checkSuccess(cur.getField(7, "testint"), 8)
checkSuccess(cur.getField(7, "testbigint"), 8)
#checkSuccess(cur.getField(7,"testfloat"),Decimal("8.1"))
#checkSuccess(cur.getField(7,"testreal"),Decimal("8.1"))
checkSuccess(cur.getField(7, "testdecimal"), Decimal("8.1"))
checkSuccess(cur.getField(7, "testdate"), "2008-01-01")
checkSuccess(cur.getField(7, "testtime"), "08:00:00")
checkSuccess(cur.getField(7, "testdatetime"), "2008-01-01 08:00:00")
checkSuccess(cur.getField(7, "testyear"), 2008)
checkSuccess(cur.getField(7, "testchar"), "char8")
checkSuccess(cur.getField(7, "testtext"), "text8")
checkSuccess(cur.getField(7, "testvarchar"), "varchar8")
checkSuccess(cur.getField(7, "testtinytext"), "tinytext8")
checkSuccess(cur.getField(7, "testmediumtext"), "mediumtext8")
checkSuccess(cur.getField(7, "testlongtext"), "longtext8")
print()
print("FIELD LENGTHS BY NAME: ")
checkSuccess(cur.getFieldLength(0, "testtinyint"), 1)
checkSuccess(cur.getFieldLength(0, "testsmallint"), 1)
checkSuccess(cur.getFieldLength(0, "testmediumint"), 1)
checkSuccess(cur.getFieldLength(0, "testint"), 1)
checkSuccess(cur.getFieldLength(0, "testbigint"), 1)
#checkSuccess(cur.getFieldLength(0,"testfloat"),3)
checkSuccess(cur.getFieldLength(0, "testreal"), 3)
checkSuccess(cur.getFieldLength(0, "testdecimal"), 3)
checkSuccess(cur.getFieldLength(0, "testdate"), 10)
checkSuccess(cur.getFieldLength(0, "testtime"), 8)
checkSuccess(cur.getFieldLength(0, "testdatetime"), 19)
checkSuccess(cur.getFieldLength(0, "testyear"), 4)
checkSuccess(cur.getFieldLength(0, "testchar"), 5)
checkSuccess(cur.getFieldLength(0, "testtext"), 5)
checkSuccess(cur.getFieldLength(0, "testvarchar"), 8)
checkSuccess(cur.getFieldLength(0, "testtinytext"), 9)
checkSuccess(cur.getFieldLength(0, "testmediumtext"), 11)
checkSuccess(cur.getFieldLength(0, "testlongtext"), 9)
print()
checkSuccess(cur.getFieldLength(7, "testtinyint"), 1)
checkSuccess(cur.getFieldLength(7, "testsmallint"), 1)
checkSuccess(cur.getFieldLength(7, "testmediumint"), 1)
checkSuccess(cur.getFieldLength(7, "testint"), 1)
checkSuccess(cur.getFieldLength(7, "testbigint"), 1)
#checkSuccess(cur.getFieldLength(7,"testfloat"),3)
checkSuccess(cur.getFieldLength(7, "testreal"), 3)
checkSuccess(cur.getFieldLength(7, "testdecimal"), 3)
checkSuccess(cur.getFieldLength(7, "testdate"), 10)
checkSuccess(cur.getFieldLength(7, "testtime"), 8)
checkSuccess(cur.getFieldLength(7, "testdatetime"), 19)
checkSuccess(cur.getFieldLength(7, "testyear"), 4)
checkSuccess(cur.getFieldLength(7, "testchar"), 5)
checkSuccess(cur.getFieldLength(7, "testtext"), 5)
checkSuccess(cur.getFieldLength(7, "testvarchar"), 8)
checkSuccess(cur.getFieldLength(7, "testtinytext"), 9)
checkSuccess(cur.getFieldLength(7, "testmediumtext"), 11)
checkSuccess(cur.getFieldLength(7, "testlongtext"), 9)
print()
print("FIELDS BY ARRAY: ")
fields = cur.getRow(0)
checkSuccess(fields[0], 1)
checkSuccess(fields[1], 1)
checkSuccess(fields[2], 1)
checkSuccess(fields[3], 1)
checkSuccess(fields[4], 1)
#checkSuccess(fields[5],Decimal("1.1"))
#checkSuccess(fields[6],Decimal("1.1"))
checkSuccess(fields[7], Decimal("1.1"))
checkSuccess(fields[8], "2001-01-01")
checkSuccess(fields[9], "01:00:00")
checkSuccess(fields[10], "2001-01-01 01:00:00")
checkSuccess(fields[11], 2001)
checkSuccess(fields[12], "char1")
checkSuccess(fields[13], "text1")
checkSuccess(fields[14], "varchar1")
checkSuccess(fields[15], "tinytext1")
checkSuccess(fields[16], "mediumtext1")
checkSuccess(fields[17], "longtext1")
print()
print("FIELD LENGTHS BY ARRAY: ")
fieldlens = cur.getRowLengths(0)
checkSuccess(fieldlens[0], 1)
checkSuccess(fieldlens[1], 1)
checkSuccess(fieldlens[2], 1)
checkSuccess(fieldlens[3], 1)
checkSuccess(fieldlens[4], 1)
#checkSuccess(fieldlens[5],3)
checkSuccess(fieldlens[6], 3)
checkSuccess(fieldlens[7], 3)
checkSuccess(fieldlens[8], 10)
checkSuccess(fieldlens[9], 8)
checkSuccess(fieldlens[10], 19)
checkSuccess(fieldlens[11], 4)
checkSuccess(fieldlens[12], 5)
checkSuccess(fieldlens[13], 5)
checkSuccess(fieldlens[14], 8)
checkSuccess(fieldlens[15], 9)
checkSuccess(fieldlens[16], 11)
checkSuccess(fieldlens[17], 9)
print()
print("FIELDS BY DICTIONARY: ")
fields = cur.getRowDictionary(0)
checkSuccess(fields["testtinyint"], 1)
checkSuccess(fields["testsmallint"], 1)
checkSuccess(fields["testmediumint"], 1)
checkSuccess(fields["testint"], 1)
checkSuccess(fields["testbigint"], 1)
#checkSuccess(fields["testfloat"],Decimal("1.1"))
#checkSuccess(fields["testreal"],Decimal("1.1"))
checkSuccess(fields["testdecimal"], Decimal("1.1"))
checkSuccess(fields["testdate"], "2001-01-01")
checkSuccess(fields["testtime"], "01:00:00")
checkSuccess(fields["testdatetime"], "2001-01-01 01:00:00")
checkSuccess(fields["testyear"], 2001)
checkSuccess(fields["testchar"], "char1")
checkSuccess(fields["testtext"], "text1")
checkSuccess(fields["testvarchar"], "varchar1")
checkSuccess(fields["testtinytext"], "tinytext1")
checkSuccess(fields["testmediumtext"], "mediumtext1")
checkSuccess(fields["testlongtext"], "longtext1")
print()
fields = cur.getRowDictionary(7)
checkSuccess(fields["testtinyint"], 8)
checkSuccess(fields["testsmallint"], 8)
checkSuccess(fields["testmediumint"], 8)
checkSuccess(fields["testint"], 8)
checkSuccess(fields["testbigint"], 8)
#checkSuccess(fields["testfloat"],Decimal("8.1"))
#checkSuccess(fields["testreal"],Decimal("8.1"))
checkSuccess(fields["testdecimal"], Decimal("8.1"))
checkSuccess(fields["testdate"], "2008-01-01")
checkSuccess(fields["testtime"], "08:00:00")
checkSuccess(fields["testdatetime"], "2008-01-01 08:00:00")
checkSuccess(fields["testyear"], 2008)
checkSuccess(fields["testchar"], "char8")
checkSuccess(fields["testtext"], "text8")
checkSuccess(fields["testvarchar"], "varchar8")
checkSuccess(fields["testtinytext"], "tinytext8")
checkSuccess(fields["testmediumtext"], "mediumtext8")
checkSuccess(fields["testlongtext"], "longtext8")
print()
print("FIELD LENGTHS BY DICTIONARY: ")
fieldlengths = cur.getRowLengthsDictionary(0)
checkSuccess(fieldlengths["testtinyint"], 1)
checkSuccess(fieldlengths["testsmallint"], 1)
checkSuccess(fieldlengths["testmediumint"], 1)
checkSuccess(fieldlengths["testint"], 1)
checkSuccess(fieldlengths["testbigint"], 1)
#checkSuccess(fieldlengths["testfloat"],3)
checkSuccess(fieldlengths["testreal"], 3)
checkSuccess(fieldlengths["testdecimal"], 3)
checkSuccess(fieldlengths["testdate"], 10)
checkSuccess(fieldlengths["testtime"], 8)
checkSuccess(fieldlengths["testdatetime"], 19)
checkSuccess(fieldlengths["testyear"], 4)
checkSuccess(fieldlengths["testchar"], 5)
checkSuccess(fieldlengths["testtext"], 5)
checkSuccess(fieldlengths["testvarchar"], 8)
checkSuccess(fieldlengths["testtinytext"], 9)
checkSuccess(fieldlengths["testmediumtext"], 11)
checkSuccess(fieldlengths["testlongtext"], 9)
print()
fieldlengths = cur.getRowLengthsDictionary(7)
checkSuccess(fieldlengths["testtinyint"], 1)
checkSuccess(fieldlengths["testsmallint"], 1)
checkSuccess(fieldlengths["testmediumint"], 1)
checkSuccess(fieldlengths["testint"], 1)
checkSuccess(fieldlengths["testbigint"], 1)
#checkSuccess(fieldlengths["testfloat"],3)
checkSuccess(fieldlengths["testreal"], 3)
checkSuccess(fieldlengths["testdecimal"], 3)
checkSuccess(fieldlengths["testdate"], 10)
checkSuccess(fieldlengths["testtime"], 8)
checkSuccess(fieldlengths["testdatetime"], 19)
checkSuccess(fieldlengths["testyear"], 4)
checkSuccess(fieldlengths["testchar"], 5)
checkSuccess(fieldlengths["testtext"], 5)
checkSuccess(fieldlengths["testvarchar"], 8)
checkSuccess(fieldlengths["testtinytext"], 9)
checkSuccess(fieldlengths["testmediumtext"], 11)
checkSuccess(fieldlengths["testlongtext"], 9)
print()
print("INDIVIDUAL SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3)")
cur.substitution("var1", 1)
cur.substitution("var2", "hello")
cur.substitution("var3", 10.5556, 6, 4)
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
checkSuccess(cur.getField(0, 2), Decimal("10.5556"))
print()
print("ARRAY SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3)")
cur.substitutions(["var1", "var2", "var3"], [1, "hello", 10.5556],
[0, 0, 6], [0, 0, 4])
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
checkSuccess(cur.getField(0, 2), Decimal("10.5556"))
print()
print("NULLS as Nones: ")
cur.getNullsAsNone()
checkSuccess(cur.sendQuery("select NULL,1,NULL"), 1)
checkSuccess(cur.getField(0, 0), None)
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), None)
cur.getNullsAsEmptyStrings()
checkSuccess(cur.sendQuery("select NULL,1,NULL"), 1)
checkSuccess(cur.getField(0, 0), "")
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), "")
cur.getNullsAsNone()
print()
print("RESULT SET BUFFER SIZE: ")
checkSuccess(cur.getResultSetBufferSize(), 0)
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
checkSuccess(cur.getResultSetBufferSize(), 2)
print()
checkSuccess(cur.firstRowIndex(), 0)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 2)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
print()
checkSuccess(cur.firstRowIndex(), 2)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 4)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
print()
print("DONT GET COLUMN INFO: ")
cur.dontGetColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
checkSuccess(cur.getColumnName(0), None)
checkSuccess(cur.getColumnLength(0), 0)
checkSuccess(cur.getColumnType(0), None)
print()
cur.getColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
checkSuccess(cur.getColumnName(0), "testtinyint")
checkSuccess(cur.getColumnLength(0), 1)
checkSuccess(cur.getColumnType(0), "TINYINT")
print()
print("SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
print("SUSPENDED RESULT SET: ")
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
checkSuccess(cur.getField(2, 0), 3)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET: ")
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
print()
print("COLUMN COUNT FOR CACHED RESULT SET: ")
checkSuccess(cur.colCount(), 19)
print()
print("COLUMN NAMES FOR CACHED RESULT SET: ")
checkSuccess(cur.getColumnName(0), "testtinyint")
checkSuccess(cur.getColumnName(1), "testsmallint")
checkSuccess(cur.getColumnName(2), "testmediumint")
checkSuccess(cur.getColumnName(3), "testint")
checkSuccess(cur.getColumnName(4), "testbigint")
checkSuccess(cur.getColumnName(5), "testfloat")
checkSuccess(cur.getColumnName(6), "testreal")
checkSuccess(cur.getColumnName(7), "testdecimal")
checkSuccess(cur.getColumnName(8), "testdate")
checkSuccess(cur.getColumnName(9), "testtime")
checkSuccess(cur.getColumnName(10), "testdatetime")
checkSuccess(cur.getColumnName(11), "testyear")
checkSuccess(cur.getColumnName(12), "testchar")
checkSuccess(cur.getColumnName(13), "testtext")
checkSuccess(cur.getColumnName(14), "testvarchar")
checkSuccess(cur.getColumnName(15), "testtinytext")
checkSuccess(cur.getColumnName(16), "testmediumtext")
checkSuccess(cur.getColumnName(17), "testlongtext")
cols = cur.getColumnNames()
checkSuccess(cols[0], "testtinyint")
checkSuccess(cols[1], "testsmallint")
checkSuccess(cols[2], "testmediumint")
checkSuccess(cols[3], "testint")
checkSuccess(cols[4], "testbigint")
checkSuccess(cols[5], "testfloat")
checkSuccess(cols[6], "testreal")
checkSuccess(cols[7], "testdecimal")
checkSuccess(cols[8], "testdate")
checkSuccess(cols[9], "testtime")
checkSuccess(cols[10], "testdatetime")
checkSuccess(cols[11], "testyear")
checkSuccess(cols[12], "testchar")
checkSuccess(cols[13], "testtext")
checkSuccess(cols[14], "testvarchar")
checkSuccess(cols[15], "testtinytext")
checkSuccess(cols[16], "testmediumtext")
checkSuccess(cols[17], "testlongtext")
print()
print("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("FROM ONE CACHE FILE TO ANOTHER: ")
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
print()
print("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
checkSuccess(cur.getField(2, 0), 3)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
print()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeCachedResultSet(id, filename), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.cacheOff()
print()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("COMMIT AND ROLLBACK: ")
# Note: Mysql's default isolation level is repeatable-read,
# not read-committed like most other db's. Both sessions must
# commit to see the changes that each other has made.
secondcon = PySQLRClient.sqlrconnection("sqlrelay", 9000,
"/tmp/test.socket", "test", "test")
secondcur = PySQLRClient.sqlrcursor(secondcon)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 0)
checkSuccess(con.commit(), 1)
checkSuccess(secondcon.commit(), 1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 8)
checkSuccess(con.autoCommitOn(), 1)
checkSuccess(
cur.sendQuery(
"insert into testdb.testtable values (10,10,10,10,10,10.1,10.1,10.1,'2010-01-01','10:00:00','2010-01-01 10:00:00','2010','char10','text10','varchar10','tinytext10','mediumtext10','longtext10',NULL)"
), 1)
checkSuccess(secondcon.commit(), 1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 9)
checkSuccess(con.autoCommitOff(), 1)
secondcon.commit()
print()
print("ROW RANGE:")
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
1)
print()
rows = cur.getRowRange(0, 5)
checkSuccess(rows[0][0], 1)
checkSuccess(rows[0][1], 1)
checkSuccess(rows[0][2], 1)
checkSuccess(rows[0][3], 1)
checkSuccess(rows[0][4], 1)
#checkSuccess(rows[0][5],Decimal("1.1"))
#checkSuccess(rows[0][6],Decimal("1.1"))
checkSuccess(rows[0][7], Decimal("1.1"))
checkSuccess(rows[0][8], "2001-01-01")
checkSuccess(rows[0][9], "01:00:00")
checkSuccess(rows[0][10], "2001-01-01 01:00:00")
checkSuccess(rows[0][11], 2001)
checkSuccess(rows[0][12], "char1")
checkSuccess(rows[0][13], "text1")
checkSuccess(rows[0][14], "varchar1")
checkSuccess(rows[0][15], "tinytext1")
checkSuccess(rows[0][16], "mediumtext1")
checkSuccess(rows[0][17], "longtext1")
print()
checkSuccess(rows[1][0], 2)
checkSuccess(rows[1][1], 2)
checkSuccess(rows[1][2], 2)
checkSuccess(rows[1][3], 2)
checkSuccess(rows[1][4], 2)
#checkSuccess(rows[1][5],Decimal("2.1"))
#checkSuccess(rows[1][6],Decimal("2.1"))
checkSuccess(rows[1][7], Decimal("2.1"))
checkSuccess(rows[1][8], "2002-01-01")
checkSuccess(rows[1][9], "02:00:00")
checkSuccess(rows[1][10], "2002-01-01 02:00:00")
checkSuccess(rows[1][11], 2002)
checkSuccess(rows[1][12], "char2")
checkSuccess(rows[1][13], "text2")
checkSuccess(rows[1][14], "varchar2")
checkSuccess(rows[1][15], "tinytext2")
checkSuccess(rows[1][16], "mediumtext2")
checkSuccess(rows[1][17], "longtext2")
print()
checkSuccess(rows[2][0], 3)
checkSuccess(rows[2][1], 3)
checkSuccess(rows[2][2], 3)
checkSuccess(rows[2][3], 3)
checkSuccess(rows[2][4], 3)
#checkSuccess(rows[2][5],Decimal("3.1"))
#checkSuccess(rows[2][6],Decimal("3.1"))
checkSuccess(rows[2][7], Decimal("3.1"))
checkSuccess(rows[2][8], "2003-01-01")
checkSuccess(rows[2][9], "03:00:00")
checkSuccess(rows[2][10], "2003-01-01 03:00:00")
checkSuccess(rows[2][11], 2003)
checkSuccess(rows[2][12], "char3")
checkSuccess(rows[2][13], "text3")
checkSuccess(rows[2][14], "varchar3")
checkSuccess(rows[2][15], "tinytext3")
checkSuccess(rows[2][16], "mediumtext3")
checkSuccess(rows[2][17], "longtext3")
print()
checkSuccess(rows[3][0], 4)
checkSuccess(rows[3][1], 4)
checkSuccess(rows[3][2], 4)
checkSuccess(rows[3][3], 4)
checkSuccess(rows[3][4], 4)
#checkSuccess(rows[3][5],Decimal("4.1"))
#checkSuccess(rows[3][6],Decimal("4.1"))
checkSuccess(rows[3][7], Decimal("4.1"))
checkSuccess(rows[3][8], "2004-01-01")
checkSuccess(rows[3][9], "04:00:00")
checkSuccess(rows[3][10], "2004-01-01 04:00:00")
checkSuccess(rows[3][11], 2004)
checkSuccess(rows[3][12], "char4")
checkSuccess(rows[3][13], "text4")
checkSuccess(rows[3][14], "varchar4")
checkSuccess(rows[3][15], "tinytext4")
checkSuccess(rows[3][16], "mediumtext4")
checkSuccess(rows[3][17], "longtext4")
print()
checkSuccess(rows[4][0], 5)
checkSuccess(rows[4][1], 5)
checkSuccess(rows[4][2], 5)
checkSuccess(rows[4][3], 5)
checkSuccess(rows[4][4], 5)
#checkSuccess(rows[4][5],Decimal("5.1"))
#checkSuccess(rows[4][6],Decimal("5.1"))
checkSuccess(rows[4][7], Decimal("5.1"))
checkSuccess(rows[4][8], "2005-01-01")
checkSuccess(rows[4][9], "05:00:00")
checkSuccess(rows[4][10], "2005-01-01 05:00:00")
checkSuccess(rows[4][11], 2005)
checkSuccess(rows[4][12], "char5")
checkSuccess(rows[4][13], "text5")
checkSuccess(rows[4][14], "varchar5")
checkSuccess(rows[4][15], "tinytext5")
checkSuccess(rows[4][16], "mediumtext5")
checkSuccess(rows[4][17], "longtext5")
print()
checkSuccess(rows[5][0], 6)
checkSuccess(rows[5][1], 6)
checkSuccess(rows[5][2], 6)
checkSuccess(rows[5][3], 6)
checkSuccess(rows[5][4], 6)
#checkSuccess(rows[5][5],Decimal("6.1"))
#checkSuccess(rows[5][6],Decimal("6.1"))
checkSuccess(rows[5][7], Decimal("6.1"))
checkSuccess(rows[5][8], "2006-01-01")
checkSuccess(rows[5][9], "06:00:00")
checkSuccess(rows[5][10], "2006-01-01 06:00:00")
checkSuccess(rows[5][11], 2006)
checkSuccess(rows[5][12], "char6")
checkSuccess(rows[5][13], "text6")
checkSuccess(rows[5][14], "varchar6")
checkSuccess(rows[5][15], "tinytext6")
checkSuccess(rows[5][16], "mediumtext6")
checkSuccess(rows[5][17], "longtext6")
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
checkSuccess(cur.getField(4, 0), None)
checkSuccess(cur.getField(5, 0), None)
checkSuccess(cur.getField(6, 0), None)
checkSuccess(cur.getField(7, 0), None)
print()
# drop existing table
cur.sendQuery("drop table testtable")
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
0)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
0)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
0)
checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),
0)
print()
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
print()
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
print()
def main():
getcontext().prec=1
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con=PySQLRClient.sqlrconnection("sqlrelay",9000,
"/tmp/test.socket",
"test","test")
cur=PySQLRClient.sqlrcursor(con)
print("IDENTIFY: ")
checkSuccess(con.identify(),"postgresql")
print()
# ping
print("PING: ")
checkSuccess(con.ping(),1)
print()
# drop existing table
cur.sendQuery("drop table testtable")
print("CREATE TEMPTABLE: ")
checkSuccess(cur.sendQuery("create table testtable (testint int, testfloat float, testreal real, testsmallint smallint, testchar char(40), testvarchar varchar(40), testdate date, testtime time, testtimestamp timestamp)"),1)
print()
print("BEGIN TRANSCTION: ")
checkSuccess(cur.sendQuery("begin"),1)
print()
print("INSERT: ")
checkSuccess(cur.sendQuery("insert into testtable values (1,1.1,1.1,1,'testchar1','testvarchar1','01/01/2001','01:00:00',NULL)"),1)
checkSuccess(cur.sendQuery("insert into testtable values (2,2.2,2.2,2,'testchar2','testvarchar2','01/01/2002','02:00:00',NULL)"),1)
checkSuccess(cur.sendQuery("insert into testtable values (3,3.3,3.3,3,'testchar3','testvarchar3','01/01/2003','03:00:00',NULL)"),1)
checkSuccess(cur.sendQuery("insert into testtable values (4,4.4,4.4,4,'testchar4','testvarchar4','01/01/2004','04:00:00',NULL)"),1)
print()
print("AFFECTED ROWS: ")
checkSuccess(cur.affectedRows(),1)
print()
print("BIND BY POSITION: ")
cur.prepareQuery("insert into testtable values ($1,$2,$3,$4,$5,$6,$7,$8)")
checkSuccess(cur.countBindVariables(),8)
cur.inputBind("1",5)
cur.inputBind("2",5.5,4,2)
cur.inputBind("3",5.5,4,2)
cur.inputBind("4",5)
cur.inputBind("5","testchar5")
cur.inputBind("6","testvarchar5")
cur.inputBind("7","01/01/2005")
cur.inputBind("8","05:00:00")
checkSuccess(cur.executeQuery(),1)
cur.clearBinds()
cur.inputBind("1",6)
cur.inputBind("2",6.6,4,2)
cur.inputBind("3",6.6,4,2)
cur.inputBind("4",6)
cur.inputBind("5","testchar6")
cur.inputBind("6","testvarchar6")
cur.inputBind("7","01/01/2006")
cur.inputBind("8","06:00:00")
checkSuccess(cur.executeQuery(),1)
print()
print("ARRAY OF BINDS BY POSITION: ")
cur.clearBinds()
cur.inputBinds(["1","2","3","4","5","6","7","8"],
[7,7.7,7.7,7,"testchar7","testvarchar7",
"01/01/2007","07:00:00"],
[0,4,4,0,0,0,0,0],
[0,2,2,0,0,0,0,0])
checkSuccess(cur.executeQuery(),1)
print()
print("BIND BY POSITION WITH VALIDATION: ")
cur.clearBinds()
cur.inputBind("1",8)
cur.inputBind("2",8.8,4,2)
cur.inputBind("3",8.8,4,2)
cur.inputBind("4",8)
cur.inputBind("5","testchar8")
cur.inputBind("6","testvarchar8")
cur.inputBind("7","01/01/2008")
cur.inputBind("8","08:00:00")
cur.validateBinds()
checkSuccess(cur.executeQuery(),1)
print()
print("SELECT: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
print()
print("COLUMN COUNT: ")
checkSuccess(cur.colCount(),9)
print()
print("COLUMN NAMES: ")
checkSuccess(cur.getColumnName(0),"testint")
checkSuccess(cur.getColumnName(1),"testfloat")
checkSuccess(cur.getColumnName(2),"testreal")
checkSuccess(cur.getColumnName(3),"testsmallint")
checkSuccess(cur.getColumnName(4),"testchar")
checkSuccess(cur.getColumnName(5),"testvarchar")
checkSuccess(cur.getColumnName(6),"testdate")
checkSuccess(cur.getColumnName(7),"testtime")
checkSuccess(cur.getColumnName(8),"testtimestamp")
cols=cur.getColumnNames()
checkSuccess(cols[0],"testint")
checkSuccess(cols[1],"testfloat")
checkSuccess(cols[2],"testreal")
checkSuccess(cols[3],"testsmallint")
checkSuccess(cols[4],"testchar")
checkSuccess(cols[5],"testvarchar")
checkSuccess(cols[6],"testdate")
checkSuccess(cols[7],"testtime")
checkSuccess(cols[8],"testtimestamp")
print()
print("COLUMN TYPES: ")
checkSuccess(cur.getColumnType(0),"int4")
checkSuccess(cur.getColumnType('testint'),"int4")
checkSuccess(cur.getColumnType(1),"float8")
checkSuccess(cur.getColumnType('testfloat'),"float8")
checkSuccess(cur.getColumnType(2),"float4")
checkSuccess(cur.getColumnType('testreal'),"float4")
checkSuccess(cur.getColumnType(3),"int2")
checkSuccess(cur.getColumnType('testsmallint'),"int2")
checkSuccess(cur.getColumnType(4),"bpchar")
checkSuccess(cur.getColumnType('testchar'),"bpchar")
checkSuccess(cur.getColumnType(5),"varchar")
checkSuccess(cur.getColumnType('testvarchar'),"varchar")
checkSuccess(cur.getColumnType(6),"date")
checkSuccess(cur.getColumnType('testdate'),"date")
checkSuccess(cur.getColumnType(7),"time")
checkSuccess(cur.getColumnType('testtime'),"time")
checkSuccess(cur.getColumnType(8),"timestamp")
checkSuccess(cur.getColumnType('testtimestamp'),"timestamp")
print()
print("COLUMN LENGTH: ")
checkSuccess(cur.getColumnLength(0),4)
checkSuccess(cur.getColumnLength('testint'),4)
checkSuccess(cur.getColumnLength(1),8)
checkSuccess(cur.getColumnLength('testfloat'),8)
checkSuccess(cur.getColumnLength(2),4)
checkSuccess(cur.getColumnLength('testreal'),4)
checkSuccess(cur.getColumnLength(3),2)
checkSuccess(cur.getColumnLength('testsmallint'),2)
checkSuccess(cur.getColumnLength(4),44)
checkSuccess(cur.getColumnLength('testchar'),44)
checkSuccess(cur.getColumnLength(5),44)
checkSuccess(cur.getColumnLength('testvarchar'),44)
checkSuccess(cur.getColumnLength(6),4)
checkSuccess(cur.getColumnLength('testdate'),4)
checkSuccess(cur.getColumnLength(7),8)
checkSuccess(cur.getColumnLength('testtime'),8)
checkSuccess(cur.getColumnLength(8),8)
checkSuccess(cur.getColumnLength('testtimestamp'),8)
print()
print("LONGEST COLUMN: ")
checkSuccess(cur.getLongest(0),1)
checkSuccess(cur.getLongest('testint'),1)
checkSuccess(cur.getLongest(1),3)
checkSuccess(cur.getLongest('testfloat'),3)
checkSuccess(cur.getLongest(2),3)
checkSuccess(cur.getLongest('testreal'),3)
checkSuccess(cur.getLongest(3),1)
checkSuccess(cur.getLongest('testsmallint'),1)
checkSuccess(cur.getLongest(4),40)
checkSuccess(cur.getLongest('testchar'),40)
checkSuccess(cur.getLongest(5),12)
checkSuccess(cur.getLongest('testvarchar'),12)
checkSuccess(cur.getLongest(6),10)
checkSuccess(cur.getLongest('testdate'),10)
checkSuccess(cur.getLongest(7),8)
checkSuccess(cur.getLongest('testtime'),8)
print()
print("ROW COUNT: ")
checkSuccess(cur.rowCount(),8)
print()
#print("TOTAL ROWS: ")
#checkSuccess(cur.totalRows(),8)
#print()
print("FIRST ROW INDEX: ")
checkSuccess(cur.firstRowIndex(),0)
print()
print("END OF RESULT SET: ")
checkSuccess(cur.endOfResultSet(),1)
print()
print("FIELDS BY INDEX: ")
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),Decimal("1.1"))
checkSuccess(cur.getField(0,2),Decimal("1.1"))
checkSuccess(cur.getField(0,3),1)
checkSuccess(cur.getField(0,4),"testchar1 ")
checkSuccess(cur.getField(0,5),"testvarchar1")
checkSuccess(cur.getField(0,6),"2001-01-01")
checkSuccess(cur.getField(0,7),"01:00:00")
print()
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(7,1),Decimal("8.8"))
checkSuccess(cur.getField(7,2),Decimal("8.8"))
checkSuccess(cur.getField(7,3),8)
checkSuccess(cur.getField(7,4),"testchar8 ")
checkSuccess(cur.getField(7,5),"testvarchar8")
checkSuccess(cur.getField(7,6),"2008-01-01")
checkSuccess(cur.getField(7,7),"08:00:00")
print()
print("FIELD LENGTHS BY INDEX: ")
checkSuccess(cur.getFieldLength(0,0),1)
checkSuccess(cur.getFieldLength(0,1),3)
checkSuccess(cur.getFieldLength(0,2),3)
checkSuccess(cur.getFieldLength(0,3),1)
checkSuccess(cur.getFieldLength(0,4),40)
checkSuccess(cur.getFieldLength(0,5),12)
checkSuccess(cur.getFieldLength(0,6),10)
checkSuccess(cur.getFieldLength(0,7),8)
print()
checkSuccess(cur.getFieldLength(7,0),1)
checkSuccess(cur.getFieldLength(7,1),3)
checkSuccess(cur.getFieldLength(7,2),3)
checkSuccess(cur.getFieldLength(7,3),1)
checkSuccess(cur.getFieldLength(7,4),40)
checkSuccess(cur.getFieldLength(7,5),12)
checkSuccess(cur.getFieldLength(7,6),10)
checkSuccess(cur.getFieldLength(7,7),8)
print()
print("FIELDS BY NAME: ")
checkSuccess(cur.getField(0,"testint"),1)
checkSuccess(cur.getField(0,"testfloat"),Decimal("1.1"))
checkSuccess(cur.getField(0,"testreal"),Decimal("1.1"))
checkSuccess(cur.getField(0,"testsmallint"),1)
checkSuccess(cur.getField(0,"testchar"),"testchar1 ")
checkSuccess(cur.getField(0,"testvarchar"),"testvarchar1")
checkSuccess(cur.getField(0,"testdate"),"2001-01-01")
checkSuccess(cur.getField(0,"testtime"),"01:00:00")
print()
checkSuccess(cur.getField(7,"testint"),8)
checkSuccess(cur.getField(7,"testfloat"),Decimal("8.8"))
checkSuccess(cur.getField(7,"testreal"),Decimal("8.8"))
checkSuccess(cur.getField(7,"testsmallint"),8)
checkSuccess(cur.getField(7,"testchar"),"testchar8 ")
checkSuccess(cur.getField(7,"testvarchar"),"testvarchar8")
checkSuccess(cur.getField(7,"testdate"),"2008-01-01")
checkSuccess(cur.getField(7,"testtime"),"08:00:00")
print()
print("FIELD LENGTHS BY NAME: ")
checkSuccess(cur.getFieldLength(0,"testint"),1)
checkSuccess(cur.getFieldLength(0,"testfloat"),3)
checkSuccess(cur.getFieldLength(0,"testreal"),3)
checkSuccess(cur.getFieldLength(0,"testsmallint"),1)
checkSuccess(cur.getFieldLength(0,"testchar"),40)
checkSuccess(cur.getFieldLength(0,"testvarchar"),12)
checkSuccess(cur.getFieldLength(0,"testdate"),10)
checkSuccess(cur.getFieldLength(0,"testtime"),8)
print()
checkSuccess(cur.getFieldLength(7,"testint"),1)
checkSuccess(cur.getFieldLength(7,"testfloat"),3)
checkSuccess(cur.getFieldLength(7,"testreal"),3)
checkSuccess(cur.getFieldLength(7,"testsmallint"),1)
checkSuccess(cur.getFieldLength(7,"testchar"),40)
checkSuccess(cur.getFieldLength(7,"testvarchar"),12)
checkSuccess(cur.getFieldLength(7,"testdate"),10)
checkSuccess(cur.getFieldLength(7,"testtime"),8)
print()
print("FIELDS BY ARRAY: ")
fields=cur.getRow(0)
checkSuccess(fields[0],1)
checkSuccess(fields[1],Decimal("1.1"))
checkSuccess(fields[2],Decimal("1.1"))
checkSuccess(fields[3],1)
checkSuccess(fields[4],"testchar1 ")
checkSuccess(fields[5],"testvarchar1")
checkSuccess(fields[6],"2001-01-01")
checkSuccess(fields[7],"01:00:00")
print()
print("FIELD LENGTHS BY ARRAY: ")
fieldlens=cur.getRowLengths(0)
checkSuccess(fieldlens[0],1)
checkSuccess(fieldlens[1],3)
checkSuccess(fieldlens[2],3)
checkSuccess(fieldlens[3],1)
checkSuccess(fieldlens[4],40)
checkSuccess(fieldlens[5],12)
checkSuccess(fieldlens[6],10)
checkSuccess(fieldlens[7],8)
print()
print("FIELDS BY DICTIONARY: ")
fields=cur.getRowDictionary(0)
checkSuccess(fields["testint"],1)
checkSuccess(fields["testfloat"],Decimal("1.1"))
checkSuccess(fields["testreal"],Decimal("1.1"))
checkSuccess(fields["testsmallint"],1)
checkSuccess(fields["testchar"],"testchar1 ")
checkSuccess(fields["testvarchar"],"testvarchar1")
checkSuccess(fields["testdate"],"2001-01-01")
checkSuccess(fields["testtime"],"01:00:00")
print()
fields=cur.getRowDictionary(7)
checkSuccess(fields["testint"],8)
checkSuccess(fields["testfloat"],Decimal("8.8"))
checkSuccess(fields["testreal"],Decimal("8.8"))
checkSuccess(fields["testsmallint"],8)
checkSuccess(fields["testchar"],"testchar8 ")
checkSuccess(fields["testvarchar"],"testvarchar8")
checkSuccess(fields["testdate"],"2008-01-01")
checkSuccess(fields["testtime"],"08:00:00")
print()
print("FIELD LENGTHS BY DICTIONARY: ")
fieldlengths=cur.getRowLengthsDictionary(0)
checkSuccess(fieldlengths["testint"],1)
checkSuccess(fieldlengths["testfloat"],3)
checkSuccess(fieldlengths["testreal"],3)
checkSuccess(fieldlengths["testsmallint"],1)
checkSuccess(fieldlengths["testchar"],40)
checkSuccess(fieldlengths["testvarchar"],12)
checkSuccess(fieldlengths["testdate"],10)
checkSuccess(fieldlengths["testtime"],8)
print()
fieldlengths=cur.getRowLengthsDictionary(7)
checkSuccess(fieldlengths["testint"],1)
checkSuccess(fieldlengths["testfloat"],3)
checkSuccess(fieldlengths["testreal"],3)
checkSuccess(fieldlengths["testsmallint"],1)
checkSuccess(fieldlengths["testchar"],40)
checkSuccess(fieldlengths["testvarchar"],12)
checkSuccess(fieldlengths["testdate"],10)
checkSuccess(fieldlengths["testtime"],8)
print()
print("INDIVIDUAL SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3)")
cur.substitution("var1",1)
cur.substitution("var2","hello")
cur.substitution("var3",10.5556,6,4)
checkSuccess(cur.executeQuery(),1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),"hello")
checkSuccess(cur.getField(0,2),Decimal("10.5556"))
print()
print("ARRAY SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3)")
cur.substitutions(["var1","var2","var3"],
[1,"hello",10.5556],[0,0,6],[0,0,4])
checkSuccess(cur.executeQuery(),1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(0,1),"hello")
checkSuccess(cur.getField(0,2),Decimal("10.5556"))
print()
print("NULLS as Nones: ")
cur.getNullsAsNone()
checkSuccess(cur.sendQuery("select NULL,1,NULL"),1)
checkSuccess(cur.getField(0,0),None)
checkSuccess(cur.getField(0,1),1)
checkSuccess(cur.getField(0,2),None)
cur.getNullsAsEmptyStrings()
checkSuccess(cur.sendQuery("select NULL,1,NULL"),1)
checkSuccess(cur.getField(0,0),"")
checkSuccess(cur.getField(0,1),1)
checkSuccess(cur.getField(0,2),"")
cur.getNullsAsNone()
print()
print("RESULT SET BUFFER SIZE: ")
checkSuccess(cur.getResultSetBufferSize(),0)
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
checkSuccess(cur.getResultSetBufferSize(),2)
print()
checkSuccess(cur.firstRowIndex(),0)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),2)
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
print()
checkSuccess(cur.firstRowIndex(),2)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),4)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.firstRowIndex(),6)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),8)
checkSuccess(cur.getField(8,0),None)
print()
checkSuccess(cur.firstRowIndex(),8)
checkSuccess(cur.endOfResultSet(),1)
checkSuccess(cur.rowCount(),8)
print()
print("DONT GET COLUMN INFO: ")
cur.dontGetColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
checkSuccess(cur.getColumnName(0),None)
checkSuccess(cur.getColumnLength(0),0)
checkSuccess(cur.getColumnType(0),None)
cur.getColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
checkSuccess(cur.getColumnName(0),"testint")
checkSuccess(cur.getColumnLength(0),4)
checkSuccess(cur.getColumnType(0),"int4")
print()
print("SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
print()
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
checkSuccess(cur.getField(3,0),4)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
print()
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
checkSuccess(cur.getField(3,0),4)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
print()
checkSuccess(cur.getField(0,0),1)
checkSuccess(cur.getField(1,0),2)
checkSuccess(cur.getField(2,0),3)
checkSuccess(cur.getField(3,0),4)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
print()
print("SUSPENDED RESULT SET: ")
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
checkSuccess(cur.getField(2,0),3)
id=cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
checkSuccess(cur.resumeResultSet(id),1)
print()
checkSuccess(cur.firstRowIndex(),4)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),6)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.firstRowIndex(),6)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),8)
checkSuccess(cur.getField(8,0),None)
print()
checkSuccess(cur.firstRowIndex(),8)
checkSuccess(cur.endOfResultSet(),1)
checkSuccess(cur.rowCount(),8)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET: ")
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
filename=cur.getCacheFileName()
checkSuccess(filename,"cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename),1)
checkSuccess(cur.getField(7,0),8)
print()
print("COLUMN COUNT FOR CACHED RESULT SET: ")
checkSuccess(cur.colCount(),9)
print()
print("COLUMN NAMES FOR CACHED RESULT SET: ")
checkSuccess(cur.getColumnName(0),"testint")
checkSuccess(cur.getColumnName(1),"testfloat")
checkSuccess(cur.getColumnName(2),"testreal")
checkSuccess(cur.getColumnName(3),"testsmallint")
checkSuccess(cur.getColumnName(4),"testchar")
checkSuccess(cur.getColumnName(5),"testvarchar")
checkSuccess(cur.getColumnName(6),"testdate")
checkSuccess(cur.getColumnName(7),"testtime")
checkSuccess(cur.getColumnName(8),"testtimestamp")
cols=cur.getColumnNames()
checkSuccess(cols[0],"testint")
checkSuccess(cols[1],"testfloat")
checkSuccess(cols[2],"testreal")
checkSuccess(cols[3],"testsmallint")
checkSuccess(cols[4],"testchar")
checkSuccess(cols[5],"testvarchar")
checkSuccess(cols[6],"testdate")
checkSuccess(cols[7],"testtime")
checkSuccess(cols[8],"testtimestamp")
print()
print("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
filename=cur.getCacheFileName()
checkSuccess(filename,"cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
cur.setResultSetBufferSize(0)
print()
print("FROM ONE CACHE FILE TO ANOTHER: ")
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"),1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
print()
print("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"),1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
checkSuccess(cur.getField(2,0),3)
filename=cur.getCacheFileName()
checkSuccess(filename,"cachefile1")
id=cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
print()
checkSuccess(con.resumeSession(port,socket),1)
checkSuccess(cur.resumeCachedResultSet(id,filename),1)
print()
checkSuccess(cur.firstRowIndex(),4)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),6)
checkSuccess(cur.getField(7,0),8)
print()
checkSuccess(cur.firstRowIndex(),6)
checkSuccess(cur.endOfResultSet(),0)
checkSuccess(cur.rowCount(),8)
checkSuccess(cur.getField(8,0),None)
print()
checkSuccess(cur.firstRowIndex(),8)
checkSuccess(cur.endOfResultSet(),1)
checkSuccess(cur.rowCount(),8)
cur.cacheOff()
print()
checkSuccess(cur.openCachedResultSet(filename),1)
checkSuccess(cur.getField(7,0),8)
checkSuccess(cur.getField(8,0),None)
cur.setResultSetBufferSize(0)
print()
print("COMMIT AND ROLLBACK: ")
secondcon=PySQLRClient.sqlrconnection("sqlrelay",9000,
"/tmp/test.socket",
"test","test")
secondcur=PySQLRClient.sqlrcursor(secondcon)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1)
checkSuccess(secondcur.getField(0,0),0)
checkSuccess(con.commit(),1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1)
checkSuccess(secondcur.getField(0,0),8)
#checkSuccess(con.autoCommitOn(),1)
checkSuccess(cur.sendQuery("insert into testtable values (10,10.1,10.1,10,'testchar10','testvarchar10','01/01/2010','10:00:00',NULL)"),1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1)
checkSuccess(secondcur.getField(0,0),9)
#checkSuccess(con.autoCommitOff(),1)
print()
print("ROW RANGE:")
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
print()
rows=cur.getRowRange(0,5)
checkSuccess(rows[0][0],1)
checkSuccess(rows[0][1],Decimal("1.1"))
checkSuccess(rows[0][2],Decimal("1.1"))
checkSuccess(rows[0][3],1)
checkSuccess(rows[0][4],"testchar1 ")
checkSuccess(rows[0][5],"testvarchar1")
checkSuccess(rows[0][6],"2001-01-01")
checkSuccess(rows[0][7],"01:00:00")
print()
checkSuccess(rows[1][0],2)
checkSuccess(rows[1][1],Decimal("2.2"))
checkSuccess(rows[1][2],Decimal("2.2"))
checkSuccess(rows[1][3],2)
checkSuccess(rows[1][4],"testchar2 ")
checkSuccess(rows[1][5],"testvarchar2")
checkSuccess(rows[1][6],"2002-01-01")
checkSuccess(rows[1][7],"02:00:00")
print()
checkSuccess(rows[2][0],3)
checkSuccess(rows[2][1],Decimal("3.3"))
checkSuccess(rows[2][2],Decimal("3.3"))
checkSuccess(rows[2][3],3)
checkSuccess(rows[2][4],"testchar3 ")
checkSuccess(rows[2][5],"testvarchar3")
checkSuccess(rows[2][6],"2003-01-01")
checkSuccess(rows[2][7],"03:00:00")
print()
checkSuccess(rows[3][0],4)
checkSuccess(rows[3][1],Decimal("4.4"))
checkSuccess(rows[3][2],Decimal("4.4"))
checkSuccess(rows[3][3],4)
checkSuccess(rows[3][4],"testchar4 ")
checkSuccess(rows[3][5],"testvarchar4")
checkSuccess(rows[3][6],"2004-01-01")
checkSuccess(rows[3][7],"04:00:00")
print()
checkSuccess(rows[4][0],5)
checkSuccess(rows[4][1],Decimal("5.5"))
checkSuccess(rows[4][2],Decimal("5.5"))
checkSuccess(rows[4][3],5)
checkSuccess(rows[4][4],"testchar5 ")
checkSuccess(rows[4][5],"testvarchar5")
checkSuccess(rows[4][6],"2005-01-01")
checkSuccess(rows[4][7],"05:00:00")
print()
checkSuccess(rows[5][0],6)
checkSuccess(rows[5][1],Decimal("6.6"))
checkSuccess(rows[5][2],Decimal("6.6"))
checkSuccess(rows[5][3],6)
checkSuccess(rows[5][4],"testchar6 ")
checkSuccess(rows[5][5],"testvarchar6")
checkSuccess(rows[5][6],"2006-01-01")
checkSuccess(rows[5][7],"06:00:00")
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"),1)
checkSuccess(cur.getField(4,0),5)
checkSuccess(cur.getField(5,0),6)
checkSuccess(cur.getField(6,0),7)
checkSuccess(cur.getField(7,0),8)
id=cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(),1)
port=con.getConnectionPort()
socket=con.getConnectionSocket()
checkSuccess(con.resumeSession(port,socket),1)
checkSuccess(cur.resumeResultSet(id),1)
checkSuccess(cur.getField(4,0),None)
checkSuccess(cur.getField(5,0),None)
checkSuccess(cur.getField(6,0),None)
checkSuccess(cur.getField(7,0),None)
print()
# drop existing table
cur.sendQuery("drop table testtable")
# stored procedures
print("STORED PROCEDURES: ")
cur.sendQuery("drop function testfunc(int)")
checkSuccess(cur.sendQuery("create function testfunc(int) returns int as ' begin return $1; end;' language plpgsql"),1)
cur.prepareQuery("select * from testfunc($1)")
cur.inputBind("1",5)
checkSuccess(cur.executeQuery(),1)
checkSuccess(cur.getField(0,0),5)
cur.sendQuery("drop function testfunc(int)")
cur.sendQuery("drop function testfunc(int,char(20))")
checkSuccess(cur.sendQuery("create function testfunc(int, char(20)) returns record as ' declare output record; begin select $1,$2 into output; return output; end;' language plpgsql"),1)
cur.prepareQuery("select * from testfunc($1,$2) as (col1 int, col2 bpchar)")
cur.inputBind("1",5)
cur.inputBind("2","hello")
checkSuccess(cur.executeQuery(),1)
checkSuccess(cur.getField(0,0),5)
checkSuccess(cur.getField(0,1),"hello")
cur.sendQuery("drop function testfunc(int,char(20))")
print()
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"),0)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),0)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),0)
checkSuccess(cur.sendQuery("select * from testtable order by testint"),0)
print()
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0)
print()
checkSuccess(cur.sendQuery("create table testtable"),0)
checkSuccess(cur.sendQuery("create table testtable"),0)
checkSuccess(cur.sendQuery("create table testtable"),0)
checkSuccess(cur.sendQuery("create table testtable"),0)
print()
def main():
cert = "/usr/local/firstworks/etc/sqlrelay.conf.d/client.pem"
ca = "/usr/local/firstworks/etc/sqlrelay.conf.d/ca.pem"
if platform.system() == "Windows":
cert = "C:\\Program Files\\Firstworks\\etc\\sqlrelay.conf.d\\client.pfx"
ca = "C:\\Program Files\\Firstworks\\etc\\sqlrelay.conf.d\\ca.pfx"
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con = PySQLRClient.sqlrconnection("sqlrelay", 9000, "/tmp/test.socket", "",
"")
cur = PySQLRClient.sqlrcursor(con)
con.enableTls("", cert, "", "", "ca", ca, 0)
# get database type
print("IDENTIFY: ")
checkSuccess(con.identify(), "oracle")
print()
# ping
print("PING: ")
checkSuccess(con.ping(), 1)
print()
# drop existing table
cur.sendQuery("drop table testtable")
print("CREATE TEMPTABLE: ")
checkSuccess(
cur.sendQuery(
"create table testtable (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long, testclob clob, testblob blob)"
), 1)
print()
print("INSERT: ")
checkSuccess(
cur.sendQuery(
"insert into testtable values (1,'testchar1','testvarchar1','01-JAN-2001','testlong1','testclob1',empty_blob())"
), 1)
print()
print("AFFECTED ROWS: ")
checkSuccess(cur.affectedRows(), 1)
print()
print("BIND BY POSITION: ")
cur.prepareQuery(
"insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)"
)
checkSuccess(cur.countBindVariables(), 7)
cur.inputBind("1", 2)
cur.inputBind("2", "testchar2")
cur.inputBind("3", "testvarchar2")
cur.inputBind("4", "01-JAN-2002")
cur.inputBind("5", "testlong2")
cur.inputBindClob("6", "testclob2", 9)
cur.inputBindBlob("7", "testblob2", 9)
checkSuccess(cur.executeQuery(), 1)
cur.clearBinds()
cur.inputBind("1", 3)
cur.inputBind("2", "testchar3")
cur.inputBind("3", "testvarchar3")
cur.inputBind("4", "01-JAN-2003")
cur.inputBind("5", "testlong3")
cur.inputBindClob("6", "testclob3", 9)
cur.inputBindBlob("7", "testblob3", 9)
checkSuccess(cur.executeQuery(), 1)
print()
print("ARRAY OF BINDS BY POSITION: ")
cur.clearBinds()
cur.inputBinds(
["1", "2", "3", "4", "5"],
[4, "testchar4", "testvarchar4", "01-JAN-2004", "testlong4"])
cur.inputBindClob("6", "testclob7", 9)
cur.inputBindBlob("7", "testblob7", 9)
checkSuccess(cur.executeQuery(), 1)
print()
print("BIND BY NAME: ")
cur.prepareQuery(
"insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)"
)
cur.inputBind("var1", 5)
cur.inputBind("var2", "testchar5")
cur.inputBind("var3", "testvarchar5")
cur.inputBind("var4", "01-JAN-2005")
cur.inputBind("var5", "testlong5")
cur.inputBindClob("var6", "testclob5", 9)
cur.inputBindBlob("var7", "testblob5", 9)
checkSuccess(cur.executeQuery(), 1)
cur.clearBinds()
cur.inputBind("var1", 6)
cur.inputBind("var2", "testchar6")
cur.inputBind("var3", "testvarchar6")
cur.inputBind("var4", "01-JAN-2006")
cur.inputBind("var5", "testlong6")
cur.inputBindClob("var6", "testclob6", 9)
cur.inputBindBlob("var7", "testblob6", 9)
checkSuccess(cur.executeQuery(), 1)
print()
print("ARRAY OF BINDS BY NAME: ")
cur.clearBinds()
cur.inputBinds(
["var1", "var2", "var3", "var4", "var5"],
[7, "testchar7", "testvarchar7", "01-JAN-2007", "testlong7"])
cur.inputBindClob("var6", "testclob7", 9)
cur.inputBindBlob("var7", "testblob7", 9)
checkSuccess(cur.executeQuery(), 1)
print()
print("BIND BY NAME WITH VALIDATION: ")
cur.clearBinds()
cur.inputBind("var1", 8)
cur.inputBind("var2", "testchar8")
cur.inputBind("var3", "testvarchar8")
cur.inputBind("var4", "01-JAN-2008")
cur.inputBind("var5", "testlong8")
cur.inputBindClob("var6", "testclob8", 9)
cur.inputBindBlob("var7", "testblob8", 9)
cur.inputBind("var9", "junkvalue")
cur.validateBinds()
checkSuccess(cur.executeQuery(), 1)
print()
print("OUTPUT BIND BY NAME: ")
cur.prepareQuery(
"begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; end;")
cur.defineOutputBindInteger("numvar")
cur.defineOutputBindString("stringvar", 10)
cur.defineOutputBindDouble("floatvar")
checkSuccess(cur.executeQuery(), 1)
numvar = cur.getOutputBindInteger("numvar")
stringvar = cur.getOutputBindString("stringvar")
floatvar = cur.getOutputBindDouble("floatvar")
checkSuccess(numvar, 1)
checkSuccess(stringvar, 'hello')
checkSuccess(floatvar, 2.5)
print()
print("OUTPUT BIND BY NAME: ")
cur.clearBinds()
cur.defineOutputBindInteger("1")
cur.defineOutputBindString("2", 10)
cur.defineOutputBindDouble("3")
checkSuccess(cur.executeQuery(), 1)
numvar = cur.getOutputBindInteger("1")
stringvar = cur.getOutputBindString("2")
floatvar = cur.getOutputBindDouble("3")
checkSuccess(numvar, 1)
checkSuccess(stringvar, 'hello')
checkSuccess(floatvar, 2.5)
print()
print("OUTPUT BIND BY NAME WITH VALIDATION: ")
cur.clearBinds()
cur.defineOutputBindInteger("numvar")
cur.defineOutputBindString("stringvar", 10)
cur.defineOutputBindDouble("floatvar")
cur.defineOutputBindString("dummyvar", 10)
cur.validateBinds()
checkSuccess(cur.executeQuery(), 1)
numvar = cur.getOutputBindInteger("numvar")
stringvar = cur.getOutputBindString("stringvar")
floatvar = cur.getOutputBindDouble("floatvar")
checkSuccess(numvar, 1)
checkSuccess(stringvar, 'hello')
checkSuccess(floatvar, 2.5)
print()
print("SELECT: ")
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
print()
print("COLUMN COUNT: ")
checkSuccess(cur.colCount(), 7)
print()
print("COLUMN NAMES: ")
checkSuccess(cur.getColumnName(0), "TESTNUMBER")
checkSuccess(cur.getColumnName(1), "TESTCHAR")
checkSuccess(cur.getColumnName(2), "TESTVARCHAR")
checkSuccess(cur.getColumnName(3), "TESTDATE")
checkSuccess(cur.getColumnName(4), "TESTLONG")
checkSuccess(cur.getColumnName(5), "TESTCLOB")
checkSuccess(cur.getColumnName(6), "TESTBLOB")
cols = cur.getColumnNames()
checkSuccess(cols[0], "TESTNUMBER")
checkSuccess(cols[1], "TESTCHAR")
checkSuccess(cols[2], "TESTVARCHAR")
checkSuccess(cols[3], "TESTDATE")
checkSuccess(cols[4], "TESTLONG")
checkSuccess(cols[5], "TESTCLOB")
checkSuccess(cols[6], "TESTBLOB")
print()
print("COLUMN TYPES: ")
checkSuccess(cur.getColumnType(0), "NUMBER")
checkSuccess(cur.getColumnType('TESTNUMBER'), "NUMBER")
checkSuccess(cur.getColumnType(1), "CHAR")
checkSuccess(cur.getColumnType('TESTCHAR'), "CHAR")
checkSuccess(cur.getColumnType(2), "VARCHAR2")
checkSuccess(cur.getColumnType('TESTVARCHAR'), "VARCHAR2")
checkSuccess(cur.getColumnType(3), "DATE")
checkSuccess(cur.getColumnType('TESTDATE'), "DATE")
checkSuccess(cur.getColumnType(4), "LONG")
checkSuccess(cur.getColumnType('TESTLONG'), "LONG")
checkSuccess(cur.getColumnType(5), "CLOB")
checkSuccess(cur.getColumnType('TESTCLOB'), "CLOB")
checkSuccess(cur.getColumnType(6), "BLOB")
checkSuccess(cur.getColumnType('TESTBLOB'), "BLOB")
print()
print("COLUMN LENGTH: ")
checkSuccess(cur.getColumnLength(0), 22)
checkSuccess(cur.getColumnLength('TESTNUMBER'), 22)
checkSuccess(cur.getColumnLength(1), 40)
checkSuccess(cur.getColumnLength('TESTCHAR'), 40)
checkSuccess(cur.getColumnLength(2), 40)
checkSuccess(cur.getColumnLength('TESTVARCHAR'), 40)
checkSuccess(cur.getColumnLength(3), 7)
checkSuccess(cur.getColumnLength('TESTDATE'), 7)
checkSuccess(cur.getColumnLength(4), 0)
checkSuccess(cur.getColumnLength('TESTLONG'), 0)
checkSuccess(cur.getColumnLength(5), 0)
checkSuccess(cur.getColumnLength('TESTCLOB'), 0)
checkSuccess(cur.getColumnLength(6), 0)
checkSuccess(cur.getColumnLength('TESTBLOB'), 0)
print()
print("LONGEST COLUMN: ")
checkSuccess(cur.getLongest(0), 1)
checkSuccess(cur.getLongest('TESTNUMBER'), 1)
checkSuccess(cur.getLongest(1), 40)
checkSuccess(cur.getLongest('TESTCHAR'), 40)
checkSuccess(cur.getLongest(2), 12)
checkSuccess(cur.getLongest('TESTVARCHAR'), 12)
checkSuccess(cur.getLongest(3), 9)
checkSuccess(cur.getLongest('TESTDATE'), 9)
checkSuccess(cur.getLongest(4), 9)
checkSuccess(cur.getLongest('TESTLONG'), 9)
checkSuccess(cur.getLongest(5), 9)
checkSuccess(cur.getLongest('TESTCLOB'), 9)
checkSuccess(cur.getLongest(6), 9)
checkSuccess(cur.getLongest('TESTBLOB'), 9)
print()
print("ROW COUNT: ")
checkSuccess(cur.rowCount(), 8)
print()
print("TOTAL ROWS: ")
checkSuccess(cur.totalRows(), 0)
print()
print("FIRST ROW INDEX: ")
checkSuccess(cur.firstRowIndex(), 0)
print()
print("END OF RESULT SET: ")
checkSuccess(cur.endOfResultSet(), 1)
print()
print("FIELDS BY INDEX: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1),
"testchar1 ")
checkSuccess(cur.getField(0, 2), "testvarchar1")
checkSuccess(cur.getField(0, 3), "01-JAN-01")
checkSuccess(cur.getField(0, 4), "testlong1")
checkSuccess(cur.getField(0, 5), "testclob1")
checkSuccess(cur.getField(0, 6), "")
print()
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(7, 1),
"testchar8 ")
checkSuccess(cur.getField(7, 2), "testvarchar8")
checkSuccess(cur.getField(7, 3), "01-JAN-08")
checkSuccess(cur.getField(7, 4), "testlong8")
checkSuccess(cur.getField(7, 5), "testclob8")
checkSuccess(cur.getField(7, 6), "testblob8")
print()
print("FIELD LENGTHS BY INDEX: ")
checkSuccess(cur.getFieldLength(0, 0), 1)
checkSuccess(cur.getFieldLength(0, 1), 40)
checkSuccess(cur.getFieldLength(0, 2), 12)
checkSuccess(cur.getFieldLength(0, 3), 9)
checkSuccess(cur.getFieldLength(0, 4), 9)
checkSuccess(cur.getFieldLength(0, 5), 9)
checkSuccess(cur.getFieldLength(0, 6), 0)
print()
checkSuccess(cur.getFieldLength(7, 0), 1)
checkSuccess(cur.getFieldLength(7, 1), 40)
checkSuccess(cur.getFieldLength(7, 2), 12)
checkSuccess(cur.getFieldLength(7, 3), 9)
checkSuccess(cur.getFieldLength(7, 4), 9)
checkSuccess(cur.getFieldLength(7, 5), 9)
checkSuccess(cur.getFieldLength(7, 6), 9)
print()
print("FIELDS BY NAME: ")
checkSuccess(cur.getField(0, "TESTNUMBER"), 1)
checkSuccess(cur.getField(0, "TESTCHAR"),
"testchar1 ")
checkSuccess(cur.getField(0, "TESTVARCHAR"), "testvarchar1")
checkSuccess(cur.getField(0, "TESTDATE"), "01-JAN-01")
checkSuccess(cur.getField(0, "TESTLONG"), "testlong1")
checkSuccess(cur.getField(0, "TESTCLOB"), "testclob1")
checkSuccess(cur.getField(0, "TESTBLOB"), "")
print()
checkSuccess(cur.getField(7, "TESTNUMBER"), 8)
checkSuccess(cur.getField(7, "TESTCHAR"),
"testchar8 ")
checkSuccess(cur.getField(7, "TESTVARCHAR"), "testvarchar8")
checkSuccess(cur.getField(7, "TESTDATE"), "01-JAN-08")
checkSuccess(cur.getField(7, "TESTLONG"), "testlong8")
checkSuccess(cur.getField(7, "TESTCLOB"), "testclob8")
checkSuccess(cur.getField(7, "TESTBLOB"), "testblob8")
print()
print("FIELD LENGTHS BY NAME: ")
checkSuccess(cur.getFieldLength(0, "TESTNUMBER"), 1)
checkSuccess(cur.getFieldLength(0, "TESTCHAR"), 40)
checkSuccess(cur.getFieldLength(0, "TESTVARCHAR"), 12)
checkSuccess(cur.getFieldLength(0, "TESTDATE"), 9)
checkSuccess(cur.getFieldLength(0, "TESTLONG"), 9)
checkSuccess(cur.getFieldLength(0, "TESTCLOB"), 9)
checkSuccess(cur.getFieldLength(0, "TESTBLOB"), 0)
print()
checkSuccess(cur.getFieldLength(7, "TESTNUMBER"), 1)
checkSuccess(cur.getFieldLength(7, "TESTCHAR"), 40)
checkSuccess(cur.getFieldLength(7, "TESTVARCHAR"), 12)
checkSuccess(cur.getFieldLength(7, "TESTDATE"), 9)
checkSuccess(cur.getFieldLength(7, "TESTLONG"), 9)
checkSuccess(cur.getFieldLength(7, "TESTCLOB"), 9)
checkSuccess(cur.getFieldLength(7, "TESTBLOB"), 9)
print()
print("FIELDS BY ARRAY: ")
fields = cur.getRow(0)
checkSuccess(fields[0], 1)
checkSuccess(fields[1], "testchar1 ")
checkSuccess(fields[2], "testvarchar1")
checkSuccess(fields[3], "01-JAN-01")
checkSuccess(fields[4], "testlong1")
checkSuccess(fields[5], "testclob1")
checkSuccess(fields[6], "")
print()
print("FIELD LENGTHS BY ARRAY: ")
fieldlens = cur.getRowLengths(0)
checkSuccess(fieldlens[0], 1)
checkSuccess(fieldlens[1], 40)
checkSuccess(fieldlens[2], 12)
checkSuccess(fieldlens[3], 9)
checkSuccess(fieldlens[4], 9)
checkSuccess(fieldlens[5], 9)
checkSuccess(fieldlens[6], None)
print()
print("FIELDS BY DICTIONARY: ")
fields = cur.getRowDictionary(0)
checkSuccess(fields["TESTNUMBER"], 1)
checkSuccess(fields["TESTCHAR"],
"testchar1 ")
checkSuccess(fields["TESTVARCHAR"], "testvarchar1")
checkSuccess(fields["TESTDATE"], "01-JAN-01")
checkSuccess(fields["TESTLONG"], "testlong1")
checkSuccess(fields["TESTCLOB"], "testclob1")
checkSuccess(fields["TESTBLOB"], "")
print()
fields = cur.getRowDictionary(7)
checkSuccess(fields["TESTNUMBER"], 8)
checkSuccess(fields["TESTCHAR"],
"testchar8 ")
checkSuccess(fields["TESTVARCHAR"], "testvarchar8")
checkSuccess(fields["TESTDATE"], "01-JAN-08")
checkSuccess(fields["TESTLONG"], "testlong8")
checkSuccess(fields["TESTCLOB"], "testclob8")
checkSuccess(fields["TESTBLOB"], "testblob8")
print()
print("FIELD LENGTHS BY DICTIONARY: ")
fieldlengths = cur.getRowLengthsDictionary(0)
checkSuccess(fieldlengths["TESTNUMBER"], 1)
checkSuccess(fieldlengths["TESTCHAR"], 40)
checkSuccess(fieldlengths["TESTVARCHAR"], 12)
checkSuccess(fieldlengths["TESTDATE"], 9)
checkSuccess(fieldlengths["TESTLONG"], 9)
checkSuccess(fieldlengths["TESTCLOB"], 9)
checkSuccess(fieldlengths["TESTBLOB"], 0)
print()
fieldlengths = cur.getRowLengthsDictionary(7)
checkSuccess(fieldlengths["TESTNUMBER"], 1)
checkSuccess(fieldlengths["TESTCHAR"], 40)
checkSuccess(fieldlengths["TESTVARCHAR"], 12)
checkSuccess(fieldlengths["TESTDATE"], 9)
checkSuccess(fieldlengths["TESTLONG"], 9)
checkSuccess(fieldlengths["TESTCLOB"], 9)
checkSuccess(fieldlengths["TESTBLOB"], 9)
print()
print("INDIVIDUAL SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3) from dual")
cur.substitution("var1", 1)
cur.substitution("var2", "hello")
cur.substitution("var3", 10.5556, 6, 4)
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
# oracle makes this field an integer
#checkSuccess(cur.getField(0,2),Decimal("10.5556"))
checkSuccess(cur.getField(0, 2), 10)
print()
print("OUTPUT BIND: ")
cur.prepareQuery("begin :var1:='hello'; end;")
cur.defineOutputBindString("var1", 10)
checkSuccess(cur.executeQuery(), 1)
checkSuccess(cur.getOutputBindString("var1"), "hello")
print()
print("ARRAY SUBSTITUTIONS: ")
cur.prepareQuery("select $(var1),'$(var2)',$(var3) from dual")
cur.substitutions(["var1", "var2", "var3"], [1, "hello", 10.5556],
[0, 0, 6], [0, 0, 4])
checkSuccess(cur.executeQuery(), 1)
print()
print("FIELDS: ")
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(0, 1), "hello")
# oracle makes this field an integer
#checkSuccess(cur.getField(0,2),Decimal("10.5556"))
checkSuccess(cur.getField(0, 2), 10)
print()
print("NULLS as Nones: ")
cur.getNullsAsNone()
checkSuccess(cur.sendQuery("select NULL,1,NULL from dual"), 1)
checkSuccess(cur.getField(0, 0), None)
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), None)
cur.getNullsAsEmptyStrings()
checkSuccess(cur.sendQuery("select NULL,1,NULL from dual"), 1)
checkSuccess(cur.getField(0, 0), "")
checkSuccess(cur.getField(0, 1), 1)
checkSuccess(cur.getField(0, 2), "")
cur.getNullsAsNone()
print()
print("RESULT SET BUFFER SIZE: ")
checkSuccess(cur.getResultSetBufferSize(), 0)
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
checkSuccess(cur.getResultSetBufferSize(), 2)
print()
checkSuccess(cur.firstRowIndex(), 0)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 2)
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
print()
checkSuccess(cur.firstRowIndex(), 2)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 4)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
print()
print("DONT GET COLUMN INFO: ")
cur.dontGetColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
checkSuccess(cur.getColumnName(0), None)
checkSuccess(cur.getColumnLength(0), 0)
checkSuccess(cur.getColumnType(0), None)
cur.getColumnInfo()
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
checkSuccess(cur.getColumnName(0), "TESTNUMBER")
checkSuccess(cur.getColumnLength(0), 22)
checkSuccess(cur.getColumnType(0), "NUMBER")
print()
print("SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
print()
checkSuccess(cur.getField(0, 0), 1)
checkSuccess(cur.getField(1, 0), 2)
checkSuccess(cur.getField(2, 0), 3)
checkSuccess(cur.getField(3, 0), 4)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
print()
print("SUSPENDED RESULT SET: ")
cur.setResultSetBufferSize(2)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
checkSuccess(cur.getField(2, 0), 3)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET: ")
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
print()
print("COLUMN COUNT FOR CACHED RESULT SET: ")
checkSuccess(cur.colCount(), 7)
print()
print("COLUMN NAMES FOR CACHED RESULT SET: ")
checkSuccess(cur.getColumnName(0), "TESTNUMBER")
checkSuccess(cur.getColumnName(1), "TESTCHAR")
checkSuccess(cur.getColumnName(2), "TESTVARCHAR")
checkSuccess(cur.getColumnName(3), "TESTDATE")
checkSuccess(cur.getColumnName(4), "TESTLONG")
checkSuccess(cur.getColumnName(5), "TESTCLOB")
checkSuccess(cur.getColumnName(6), "TESTBLOB")
cols = cur.getColumnNames()
checkSuccess(cols[0], "TESTNUMBER")
checkSuccess(cols[1], "TESTCHAR")
checkSuccess(cols[2], "TESTVARCHAR")
checkSuccess(cols[3], "TESTDATE")
checkSuccess(cols[4], "TESTLONG")
checkSuccess(cols[5], "TESTCLOB")
checkSuccess(cols[6], "TESTBLOB")
print()
print("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
cur.cacheOff()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("FROM ONE CACHE FILE TO ANOTHER: ")
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
print()
print("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile2")
checkSuccess(cur.openCachedResultSet("cachefile1"), 1)
cur.cacheOff()
checkSuccess(cur.openCachedResultSet("cachefile2"), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: ")
cur.setResultSetBufferSize(2)
cur.cacheToFile("cachefile1")
cur.setCacheTtl(200)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
checkSuccess(cur.getField(2, 0), 3)
filename = cur.getCacheFileName()
checkSuccess(filename, "cachefile1")
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
print()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeCachedResultSet(id, filename), 1)
print()
checkSuccess(cur.firstRowIndex(), 4)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 6)
checkSuccess(cur.getField(7, 0), 8)
print()
checkSuccess(cur.firstRowIndex(), 6)
checkSuccess(cur.endOfResultSet(), 0)
checkSuccess(cur.rowCount(), 8)
checkSuccess(cur.getField(8, 0), None)
print()
checkSuccess(cur.firstRowIndex(), 8)
checkSuccess(cur.endOfResultSet(), 1)
checkSuccess(cur.rowCount(), 8)
cur.cacheOff()
print()
checkSuccess(cur.openCachedResultSet(filename), 1)
checkSuccess(cur.getField(7, 0), 8)
checkSuccess(cur.getField(8, 0), None)
cur.setResultSetBufferSize(0)
print()
print("COMMIT AND ROLLBACK: ")
secondcon = PySQLRClient.sqlrconnection("sqlrelay", 9000,
"/tmp/test.socket", "", "")
secondcur = PySQLRClient.sqlrcursor(secondcon)
secondcon.enableTls("", cert, "", "", "ca", ca, 0)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 0)
checkSuccess(con.commit(), 1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 8)
checkSuccess(con.autoCommitOn(), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (10,'testchar10','testvarchar10','01-JAN-2010','testlong10','testclob10',empty_blob())"
), 1)
checkSuccess(secondcur.sendQuery("select count(*) from testtable"), 1)
checkSuccess(secondcur.getField(0, 0), 9)
checkSuccess(con.autoCommitOff(), 1)
print()
print("ROW RANGE:")
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
print()
rows = cur.getRowRange(0, 5)
checkSuccess(rows[0][0], 1)
checkSuccess(rows[0][1], "testchar1 ")
checkSuccess(rows[0][2], "testvarchar1")
checkSuccess(rows[0][3], "01-JAN-01")
checkSuccess(rows[0][4], "testlong1")
print()
checkSuccess(rows[1][0], 2)
checkSuccess(rows[1][1], "testchar2 ")
checkSuccess(rows[1][2], "testvarchar2")
checkSuccess(rows[1][3], "01-JAN-02")
checkSuccess(rows[1][4], "testlong2")
print()
checkSuccess(rows[2][0], 3)
checkSuccess(rows[2][1], "testchar3 ")
checkSuccess(rows[2][2], "testvarchar3")
checkSuccess(rows[2][3], "01-JAN-03")
checkSuccess(rows[2][4], "testlong3")
print()
checkSuccess(rows[3][0], 4)
checkSuccess(rows[3][1], "testchar4 ")
checkSuccess(rows[3][2], "testvarchar4")
checkSuccess(rows[3][3], "01-JAN-04")
checkSuccess(rows[3][4], "testlong4")
print()
checkSuccess(rows[4][0], 5)
checkSuccess(rows[4][1], "testchar5 ")
checkSuccess(rows[4][2], "testvarchar5")
checkSuccess(rows[4][3], "01-JAN-05")
checkSuccess(rows[4][4], "testlong5")
print()
checkSuccess(rows[5][0], 6)
checkSuccess(rows[5][1], "testchar6 ")
checkSuccess(rows[5][2], "testvarchar6")
checkSuccess(rows[5][3], "01-JAN-06")
checkSuccess(rows[5][4], "testlong6")
print()
print("CLOB AND BLOB OUTPUT BIND: ")
cur.sendQuery("drop table testtable1")
checkSuccess(
cur.sendQuery(
"create table testtable1 (testclob clob, testblob blob)"), 1)
cur.prepareQuery("insert into testtable1 values ('hello',:var1)")
cur.inputBindBlob("var1", "hello", 5)
checkSuccess(cur.executeQuery(), 1)
cur.prepareQuery(
"begin select testclob into :clobvar from testtable1; select testblob into :blobvar from testtable1; end;"
)
cur.defineOutputBindClob("clobvar")
cur.defineOutputBindBlob("blobvar")
checkSuccess(cur.executeQuery(), 1)
clobvar = cur.getOutputBindClob("clobvar")
clobvarlength = cur.getOutputBindLength("clobvar")
blobvar = cur.getOutputBindBlob("blobvar")
blobvarlength = cur.getOutputBindLength("blobvar")
checkSuccess(clobvar, "hello")
checkSuccess(clobvarlength, 5)
checkSuccess(blobvar, "hello")
checkSuccess(blobvarlength, 5)
cur.sendQuery("drop table testtable1")
print()
print("NULL AND EMPTY CLOBS AND CLOBS: ")
cur.getNullsAsNone()
cur.sendQuery(
"create table testtable1 (testclob1 clob, testclob2 clob, testblob1 blob, testblob2 blob)"
)
cur.prepareQuery("insert into testtable1 values (:var1,:var2,:var3,:var4)")
cur.inputBindClob("var1", "", 0)
cur.inputBindClob("var2", None, 0)
cur.inputBindBlob("var3", "", 0)
cur.inputBindBlob("var4", None, 0)
checkSuccess(cur.executeQuery(), 1)
cur.sendQuery("select * from testtable1")
checkSuccess(cur.getField(0, 0), None)
checkSuccess(cur.getField(0, 1), None)
checkSuccess(cur.getField(0, 2), None)
checkSuccess(cur.getField(0, 3), None)
cur.sendQuery("drop table testtable1")
print()
print("CURSOR BINDS: ")
checkSuccess(
cur.sendQuery(
"create or replace package types as type cursorType is ref cursor; end;"
), 1)
checkSuccess(
cur.sendQuery(
"create or replace function sp_testtable return types.cursortype as l_cursor types.cursorType; begin open l_cursor for select * from testtable; return l_cursor; end;"
), 1)
cur.prepareQuery("begin :curs:=sp_testtable; end;")
cur.defineOutputBindCursor("curs")
checkSuccess(cur.executeQuery(), 1)
bindcur = cur.getOutputBindCursor("curs")
checkSuccess(bindcur.fetchFromBindCursor(), 1)
checkSuccess(bindcur.getField(0, 0), 1)
checkSuccess(bindcur.getField(1, 0), 2)
checkSuccess(bindcur.getField(2, 0), 3)
checkSuccess(bindcur.getField(3, 0), 4)
checkSuccess(bindcur.getField(4, 0), 5)
checkSuccess(bindcur.getField(5, 0), 6)
checkSuccess(bindcur.getField(6, 0), 7)
checkSuccess(bindcur.getField(7, 0), 8)
print()
print("LONG CLOB: ")
cur.sendQuery("drop table testtable2")
cur.sendQuery("create table testtable2 (testclob clob)")
cur.prepareQuery("insert into testtable2 values (:clobval)")
clobval = ""
for i in range(0, 8 * 1024):
clobval = clobval + 'C'
cur.inputBindClob("clobval", clobval, 8 * 1024)
checkSuccess(cur.executeQuery(), 1)
cur.sendQuery("select testclob from testtable2")
checkSuccess(clobval, cur.getField(0, "TESTCLOB"))
cur.prepareQuery(
"begin select testclob into :clobbindval from testtable2; end;")
cur.defineOutputBindClob("clobbindval")
checkSuccess(cur.executeQuery(), 1)
clobbindvar = cur.getOutputBindClob("clobbindval")
checkSuccess(cur.getOutputBindLength("clobbindval"), 8 * 1024)
checkSuccess(clobval, clobbindvar)
cur.sendQuery("drop table testtable2")
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
1)
checkSuccess(cur.getField(4, 0), 5)
checkSuccess(cur.getField(5, 0), 6)
checkSuccess(cur.getField(6, 0), 7)
checkSuccess(cur.getField(7, 0), 8)
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
checkSuccess(cur.getField(4, 0), None)
checkSuccess(cur.getField(5, 0), None)
checkSuccess(cur.getField(6, 0), None)
checkSuccess(cur.getField(7, 0), None)
print()
print("BIND VALIDATION: ")
cur.sendQuery("drop table testtable1")
cur.sendQuery(
"create table testtable1 (col1 varchar2(20), col2 varchar2(20), col3 varchar2(20))"
)
cur.prepareQuery("insert into testtable1 values ($(var1),$(var2),$(var3))")
cur.inputBind("var1", 1)
cur.inputBind("var2", 2)
cur.inputBind("var3", 3)
cur.substitution("var1", ":var1")
checkSuccess(cur.validBind("var1"), 1)
checkSuccess(cur.validBind("var2"), 0)
checkSuccess(cur.validBind("var3"), 0)
checkSuccess(cur.validBind("var4"), 0)
print()
cur.substitution("var2", ":var2")
checkSuccess(cur.validBind("var1"), 1)
checkSuccess(cur.validBind("var2"), 1)
checkSuccess(cur.validBind("var3"), 0)
checkSuccess(cur.validBind("var4"), 0)
print()
cur.substitution("var3", ":var3")
checkSuccess(cur.validBind("var1"), 1)
checkSuccess(cur.validBind("var2"), 1)
checkSuccess(cur.validBind("var3"), 1)
checkSuccess(cur.validBind("var4"), 0)
checkSuccess(cur.executeQuery(), 1)
cur.sendQuery("drop table testtable1")
print()
# drop existing table
cur.sendQuery("drop table testtable")
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
0)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
0)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
0)
checkSuccess(cur.sendQuery("select * from testtable order by testnumber"),
0)
print()
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
print()
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
print()
def main():
PySQLRClient.getNumericFieldsAsNumbers()
# instantiation
con = PySQLRClient.sqlrconnection("sqlrelay", 9000, "/tmp/test.socket",
"test", "test")
cur = PySQLRClient.sqlrcursor(con)
# get database type
print("IDENTIFY: ")
checkSuccess(con.identify(), "odbc")
# ping
print("PING: ")
checkSuccess(con.ping(), 1)
print()
# drop existing table
cur.sendQuery("drop table testtable")
# create a new table
print("CREATE TEMPTABLE: ")
checkSuccess(
cur.sendQuery(
"create table testtable (testint int, testchar char(40), testvarchar varchar(40), testdate date)"
), 1)
print()
print("INSERT: ")
checkSuccess(
cur.sendQuery(
"insert into testtable values (1,'testchar1','testvarchar1','01-JAN-2001')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (2,'testchar2','testvarchar2','02-JAN-2002')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (3,'testchar3','testvarchar3','03-JAN-2003')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (4,'testchar4','testvarchar4','04-JAN-2004')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (5,'testchar5','testvarchar5','05-JAN-2005')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (6,'testchar6','testvarchar6','06-JAN-2006')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (7,'testchar7','testvarchar7','07-JAN-2007')"
), 1)
checkSuccess(
cur.sendQuery(
"insert into testtable values (8,'testchar8','testvarchar8','08-JAN-2008')"
), 1)
print()
print("FINISHED SUSPENDED SESSION: ")
checkSuccess(cur.sendQuery("select * from testtable order by testint"), 1)
checkSuccess(cur.getField(4, 0), "5")
checkSuccess(cur.getField(5, 0), "6")
checkSuccess(cur.getField(6, 0), "7")
checkSuccess(cur.getField(7, 0), "8")
id = cur.getResultSetId()
cur.suspendResultSet()
checkSuccess(con.suspendSession(), 1)
port = con.getConnectionPort()
socket = con.getConnectionSocket()
checkSuccess(con.resumeSession(port, socket), 1)
checkSuccess(cur.resumeResultSet(id), 1)
checkSuccess(cur.getField(4, 0), None)
checkSuccess(cur.getField(5, 0), None)
checkSuccess(cur.getField(6, 0), None)
checkSuccess(cur.getField(7, 0), None)
print()
# drop existing table
cur.sendQuery("drop table testtable")
# invalid queries...
print("INVALID QUERIES: ")
checkSuccess(cur.sendQuery("select * from testtable"), 0)
checkSuccess(cur.sendQuery("select * from testtable"), 0)
checkSuccess(cur.sendQuery("select * from testtable"), 0)
checkSuccess(cur.sendQuery("select * from testtable"), 0)
print()
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"), 0)
print()
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
checkSuccess(cur.sendQuery("create table testtable"), 0)
print()
