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()