def setUp(self): self.con = PySQLRClient.sqlrconnection( SQLRELAY_HOST, SQLRELAY_PORT, '', SQLRELAY_USER, SQLRELAY_PASS, 0, 1) if SQLRELAY_DEBUG: self.con.debugOn() self.cur = PySQLRClient.sqlrcursor(self.con)
def main(): sqlrcon=PySQLRClient.sqlrconnection("examplehost",9000, \ "/tmp/example.socket", \ "exampleuser", \ "examplepassword",0,1) sqlrcur = PySQLRClient.sqlrcursor(sqlrcon) sqlrcur.sendQuery("select * from exampletable") for row in range(0, sqlrcur.rowCount()): for col in range(0, sqlrcur.colCount()): print sqlrcur.getField(row, col) + ",", print
def _execute(buffer_size): info = sorted(instances, key=lambda x: x[0])[0] try: con = PySQLRClient.sqlrconnection( info[1]['host'], info[1]['port'], '', info[1]['user'], info[1]['pass'], 0, 1) cur = PySQLRClient.sqlrcursor(con) cur.setResultSetBufferSize(buffer_size) except PySQLRDB.DatabaseError, e: logger.error(e) raise
def sqlrelay_client_cursor(debug=False): ''' Connect sqlrelay client ''' info = sorted(INSTANCES, key=lambda x: x[0])[0] try: con = PySQLRClient.sqlrconnection( info[1]['host'], info[1]['port'], '', info[1]['user'], info[1]['pass'], 0, 1) cur = PySQLRClient.sqlrcursor(con) if debug: con.debugOn() except Exception, e: raise
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(),"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(), "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") print() # 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 (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 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 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 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 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 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),"CHAR") 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"),"CHAR") 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(),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),"1") checkSuccess(cur.getField(0,2),"1") checkSuccess(cur.getField(0,3),"1") checkSuccess(cur.getField(0,4),"1") checkSuccess(cur.getField(0,5),"1.1") checkSuccess(cur.getField(0,6),"1.1") checkSuccess(cur.getField(0,7),"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),"8.1") checkSuccess(cur.getField(7,6),"8.1") checkSuccess(cur.getField(7,7),"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"),"1.1") checkSuccess(cur.getField(0,"testreal"),"1.1") checkSuccess(cur.getField(0,"testdecimal"),"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"),"8.1") checkSuccess(cur.getField(7,"testreal"),"8.1") checkSuccess(cur.getField(7,"testdecimal"),"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],"1.1") checkSuccess(fields[6],"1.1") checkSuccess(fields[7],"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"],"1.1") checkSuccess(fields["testreal"],"1.1") checkSuccess(fields["testdecimal"],"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"],"8.1") checkSuccess(fields["testreal"],"8.1") checkSuccess(fields["testdecimal"],"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),"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),"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: ") 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 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],"1.1") checkSuccess(rows[0][6],"1.1") checkSuccess(rows[0][7],"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],"2.1") checkSuccess(rows[1][6],"2.1") checkSuccess(rows[1][7],"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],"3.1") checkSuccess(rows[2][6],"3.1") checkSuccess(rows[2][7],"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],"4.1") checkSuccess(rows[3][6],"4.1") checkSuccess(rows[3][7],"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],"5.1") checkSuccess(rows[4][6],"5.1") checkSuccess(rows[4][7],"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],"6.1") checkSuccess(rows[5][6],"6.1") checkSuccess(rows[5][7],"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()
from SQLRelay import PySQLRClient con = PySQLRClient.sqlrconnection("sqlrserver", 9000, "", "user", "password", 0, 1) cur = PySQLRClient.sqlrcursor(con) cur.prepareQuery("begin :curs:=sp_mytable end;") cur.defineOutputBindCursor("curs") cur.executeQuery() bindcur = cur.getOutputBindCursor("curs") bindcur.fetchFromBindCursor() # print fields from table for i in range(0, bindcur.rowCount() - 1): for j in range(0, bindcur.colCount() - 1): print bindcur.getField(i, j), ", " 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()
from SQLRelay import PySQLRClient con=PySQLRClient.sqlrconnection('sqlrserver',9000,'/tmp/example.socket','user','password',0,1) cursor1=PySQLRClient.sqlrcursor(con) cursor2=PySQLRClient.sqlrcursor(con) cursor1.setResultSetBufferSize(10) cursor1.sendQuery('select * from my_huge_table') index=0 while (!cursor1.endOfResultSet()): cursor2.prepareQuery('insert into my_other_table values (:1,:2,:3)') cursor2.inputBind('1',cursor1.getField(index,1)) cursor2.inputBind('2',cursor1.getField(index,2)) cursor2.inputBind('3',cursor1.getField(index,3)) cursor2.executeQuery()