1#! /usr/bin/env ruby 2 3# Copyright (c) 1999-2018 David Muse 4# See the file COPYING for more information. 5 6 7 8require 'rbconfig' 9require 'sqlrelay' 10 11def checkSuccess(value,success) 12 if value==success 13 print "success " 14 else 15 print value , " != " , success, " " 16 print "failure " 17 exit(1) 18 end 19end 20 21 22 23 24 25# instantiation 26con=SQLRConnection.new("sqlrelay",9000,"/tmp/test.socket", 27 "test","test",0,1) 28cur=SQLRCursor.new(con) 29 30# get database type 31print "IDENTIFY: \n" 32checkSuccess(con.identify(),"freetds") 33print "\n" 34 35# ping 36print "PING: \n" 37checkSuccess(con.ping(),1) 38print "\n" 39 40 41# drop existing table 42cur.sendQuery("drop table testtable") 43 44 45print "CREATE TEMPTABLE: \n" 46checkSuccess(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) 47print "\n" 48 49print "BEGIN TRANSACTION: \n" 50checkSuccess(cur.sendQuery("begin tran"),1) 51print "\n" 52 53print "INSERT: \n" 54checkSuccess(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) 55print "\n" 56 57print "AFFECTED ROWS: \n" 58checkSuccess(cur.affectedRows(),1) 59print "\n" 60 61print "BIND BY POSITION: \n" 62cur.prepareQuery("insert into testtable values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)") 63checkSuccess(cur.countBindVariables(),14) 64cur.inputBind("1",2) 65cur.inputBind("2",2) 66cur.inputBind("3",2) 67cur.inputBind("4",2.2,2,1) 68cur.inputBind("5",2.2,2,1) 69cur.inputBind("6",2.2,2,1) 70cur.inputBind("7",2.2,2,1) 71cur.inputBind("8",2.00,3,2) 72cur.inputBind("9",2.00,3,2) 73cur.inputBind("10","01-Jan-2002 02:00:00") 74cur.inputBind("11","01-Jan-2002 02:00:00") 75cur.inputBind("12","testchar2") 76cur.inputBind("13","testvarchar2") 77cur.inputBind("14",1) 78checkSuccess(cur.executeQuery(),1) 79cur.clearBinds() 80cur.inputBind("1",3) 81cur.inputBind("2",3) 82cur.inputBind("3",3) 83cur.inputBind("4",3.3,2,1) 84cur.inputBind("5",3.3,2,1) 85cur.inputBind("6",3.3,2,1) 86cur.inputBind("7",3.3,2,1) 87cur.inputBind("8",3.00,3,2) 88cur.inputBind("9",3.00,3,2) 89cur.inputBind("10","01-Jan-2003 03:00:00") 90cur.inputBind("11","01-Jan-2003 03:00:00") 91cur.inputBind("12","testchar3") 92cur.inputBind("13","testvarchar3") 93cur.inputBind("14",1) 94checkSuccess(cur.executeQuery(),1) 95print "\n" 96 97print "ARRAY OF BINDS BY POSITION: \n" 98cur.clearBinds() 99cur.inputBinds(["1","2","3","4","5","6", 100 "7","8","9","10","11","12", 101 "13","14"], 102 [4,4,4,4.4,4.4,4.4,4.4,4.00,4.00, 103 "01-Jan-2004 04:00:00", 104 "01-Jan-2004 04:00:00", 105 "testchar4","testvarchar4",1], 106 [0,0,0,2,2,2,2,3,3,0,0,0,0,0], 107 [0,0,0,1,1,1,1,2,2,0,0,0,0,0]) 108checkSuccess(cur.executeQuery(),1) 109print "\n" 110 111print "BIND BY NAME: \n" 112cur.clearBinds() 113cur.prepareQuery("insert into testtable values (@var1,@var2,@var3,@var4,@var5,@var6,@var7,@var8,@var9,@var10,@var11,@var12,@var13,@var14)") 114cur.inputBind("var1",5) 115cur.inputBind("var2",5) 116cur.inputBind("var3",5) 117cur.inputBind("var4",5.5,2,1) 118cur.inputBind("var5",5.5,2,1) 119cur.inputBind("var6",5.5,2,1) 120cur.inputBind("var7",5.5,2,1) 121cur.inputBind("var8",5.00,3,2) 122cur.inputBind("var9",5.00,3,2) 123cur.inputBind("var10","01-Jan-2005 05:00:00") 124cur.inputBind("var11","01-Jan-2005 05:00:00") 125cur.inputBind("var12","testchar5") 126cur.inputBind("var13","testvarchar5") 127cur.inputBind("var14",1) 128checkSuccess(cur.executeQuery(),1) 129cur.clearBinds() 130cur.inputBind("var1",6) 131cur.inputBind("var2",6) 132cur.inputBind("var3",6) 133cur.inputBind("var4",6.6,2,1) 134cur.inputBind("var5",6.6,2,1) 135cur.inputBind("var6",6.6,2,1) 136cur.inputBind("var7",6.6,2,1) 137cur.inputBind("var8",6.00,3,2) 138cur.inputBind("var9",6.00,3,2) 139cur.inputBind("var10","01-Jan-2006 06:00:00") 140cur.inputBind("var11","01-Jan-2006 06:00:00") 141cur.inputBind("var12","testchar6") 142cur.inputBind("var13","testvarchar6") 143cur.inputBind("var14",1) 144checkSuccess(cur.executeQuery(),1) 145print "\n" 146 147print "ARRAY OF BINDS BY NAME: \n" 148cur.clearBinds() 149cur.inputBinds(["var1","var2","var3","var4","var5","var6", 150 "var7","var8","var9","var10","var11","var12", 151 "var13","var14"], 152 [7,7,7,7.7,7.7,7.7,7.7,7.00,7.00, 153 "01-Jan-2007 07:00:00", 154 "01-Jan-2007 07:00:00", 155 "testchar7","testvarchar7",1], 156 [0,0,0,2,2,2,2,3,3,0,0,0,0,0], 157 [0,0,0,1,1,1,1,2,2,0,0,0,0,0]) 158checkSuccess(cur.executeQuery(),1) 159print "\n" 160 161print "BIND BY NAME WITH VALIDATION: \n" 162cur.clearBinds() 163cur.inputBind("var1",8) 164cur.inputBind("var2",8) 165cur.inputBind("var3",8) 166cur.inputBind("var4",8.8,2,1) 167cur.inputBind("var5",8.8,2,1) 168cur.inputBind("var6",8.8,2,1) 169cur.inputBind("var7",8.8,2,1) 170cur.inputBind("var8",8.00,3,2) 171cur.inputBind("var9",8.00,3,2) 172cur.inputBind("var10","01-Jan-2008 08:00:00") 173cur.inputBind("var11","01-Jan-2008 08:00:00") 174cur.inputBind("var12","testchar8") 175cur.inputBind("var13","testvarchar8") 176cur.inputBind("var14",1) 177cur.inputBind("var15","junkvalue") 178cur.validateBinds() 179checkSuccess(cur.executeQuery(),1) 180print "\n" 181 182print "SELECT: \n" 183checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 184print "\n" 185 186print "COLUMN COUNT: \n" 187checkSuccess(cur.colCount(),14) 188print "\n" 189 190print "COLUMN NAMES: \n" 191checkSuccess(cur.getColumnName(0),"testint") 192checkSuccess(cur.getColumnName(1),"testsmallint") 193checkSuccess(cur.getColumnName(2),"testtinyint") 194checkSuccess(cur.getColumnName(3),"testreal") 195checkSuccess(cur.getColumnName(4),"testfloat") 196checkSuccess(cur.getColumnName(5),"testdecimal") 197checkSuccess(cur.getColumnName(6),"testnumeric") 198checkSuccess(cur.getColumnName(7),"testmoney") 199checkSuccess(cur.getColumnName(8),"testsmallmoney") 200checkSuccess(cur.getColumnName(9),"testdatetime") 201checkSuccess(cur.getColumnName(10),"testsmalldatetime") 202checkSuccess(cur.getColumnName(11),"testchar") 203checkSuccess(cur.getColumnName(12),"testvarchar") 204checkSuccess(cur.getColumnName(13),"testbit") 205cols=cur.getColumnNames() 206checkSuccess(cols[0],"testint") 207checkSuccess(cols[1],"testsmallint") 208checkSuccess(cols[2],"testtinyint") 209checkSuccess(cols[3],"testreal") 210checkSuccess(cols[4],"testfloat") 211checkSuccess(cols[5],"testdecimal") 212checkSuccess(cols[6],"testnumeric") 213checkSuccess(cols[7],"testmoney") 214checkSuccess(cols[8],"testsmallmoney") 215checkSuccess(cols[9],"testdatetime") 216checkSuccess(cols[10],"testsmalldatetime") 217checkSuccess(cols[11],"testchar") 218checkSuccess(cols[12],"testvarchar") 219checkSuccess(cols[13],"testbit") 220print "\n" 221 222print "COLUMN TYPES: \n" 223checkSuccess(cur.getColumnType(0),"INT") 224checkSuccess(cur.getColumnType('testint'),"INT") 225checkSuccess(cur.getColumnType(1),"SMALLINT") 226checkSuccess(cur.getColumnType('testsmallint'),"SMALLINT") 227checkSuccess(cur.getColumnType(2),"TINYINT") 228checkSuccess(cur.getColumnType('testtinyint'),"TINYINT") 229checkSuccess(cur.getColumnType(3),"REAL") 230checkSuccess(cur.getColumnType('testreal'),"REAL") 231checkSuccess(cur.getColumnType(4),"FLOAT") 232checkSuccess(cur.getColumnType('testfloat'),"FLOAT") 233checkSuccess(cur.getColumnType(5),"DECIMAL") 234checkSuccess(cur.getColumnType('testdecimal'),"DECIMAL") 235checkSuccess(cur.getColumnType(6),"NUMERIC") 236checkSuccess(cur.getColumnType('testnumeric'),"NUMERIC") 237checkSuccess(cur.getColumnType(7),"MONEY") 238checkSuccess(cur.getColumnType('testmoney'),"MONEY") 239checkSuccess(cur.getColumnType(8),"SMALLMONEY") 240checkSuccess(cur.getColumnType('testsmallmoney'),"SMALLMONEY") 241checkSuccess(cur.getColumnType(9),"DATETIME") 242checkSuccess(cur.getColumnType('testdatetime'),"DATETIME") 243checkSuccess(cur.getColumnType(10),"SMALLDATETIME") 244checkSuccess(cur.getColumnType('testsmalldatetime'),"SMALLDATETIME") 245checkSuccess(cur.getColumnType(11),"CHAR") 246checkSuccess(cur.getColumnType('testchar'),"CHAR") 247checkSuccess(cur.getColumnType(12),"CHAR") 248checkSuccess(cur.getColumnType('testvarchar'),"CHAR") 249checkSuccess(cur.getColumnType(13),"BIT") 250checkSuccess(cur.getColumnType('testbit'),"BIT") 251print "\n" 252 253print "COLUMN LENGTH: \n" 254checkSuccess(cur.getColumnLength(0),4) 255checkSuccess(cur.getColumnLength('testint'),4) 256checkSuccess(cur.getColumnLength(1),2) 257checkSuccess(cur.getColumnLength('testsmallint'),2) 258checkSuccess(cur.getColumnLength(2),1) 259checkSuccess(cur.getColumnLength('testtinyint'),1) 260checkSuccess(cur.getColumnLength(3),4) 261checkSuccess(cur.getColumnLength('testreal'),4) 262checkSuccess(cur.getColumnLength(4),8) 263checkSuccess(cur.getColumnLength('testfloat'),8) 264# these seem to fluctuate with every freetds release 265#checkSuccess(cur.getColumnLength(5),3) 266#checkSuccess(cur.getColumnLength('testdecimal'),3) 267#checkSuccess(cur.getColumnLength(6),3) 268#checkSuccess(cur.getColumnLength('testnumeric'),3) 269checkSuccess(cur.getColumnLength(7),8) 270checkSuccess(cur.getColumnLength('testmoney'),8) 271checkSuccess(cur.getColumnLength(8),4) 272checkSuccess(cur.getColumnLength('testsmallmoney'),4) 273checkSuccess(cur.getColumnLength(9),8) 274checkSuccess(cur.getColumnLength('testdatetime'),8) 275checkSuccess(cur.getColumnLength(10),4) 276checkSuccess(cur.getColumnLength('testsmalldatetime'),4) 277# these seem to fluctuate too 278#checkSuccess(cur.getColumnLength(11),40) 279#checkSuccess(cur.getColumnLength('testchar'),40) 280#checkSuccess(cur.getColumnLength(12),40) 281#checkSuccess(cur.getColumnLength('testvarchar'),40) 282checkSuccess(cur.getColumnLength(13),1) 283checkSuccess(cur.getColumnLength('testbit'),1) 284print "\n" 285 286print "LONGEST COLUMN: \n" 287checkSuccess(cur.getLongest(0),1) 288checkSuccess(cur.getLongest('testint'),1) 289checkSuccess(cur.getLongest(1),1) 290checkSuccess(cur.getLongest('testsmallint'),1) 291checkSuccess(cur.getLongest(2),1) 292checkSuccess(cur.getLongest('testtinyint'),1) 293#checkSuccess(cur.getLongest(3),3) 294#checkSuccess(cur.getLongest('testreal'),3) 295#checkSuccess(cur.getLongest(4),17) 296#checkSuccess(cur.getLongest('testfloat'),17) 297checkSuccess(cur.getLongest(5),3) 298checkSuccess(cur.getLongest('testdecimal'),3) 299checkSuccess(cur.getLongest(6),3) 300checkSuccess(cur.getLongest('testnumeric'),3) 301#checkSuccess(cur.getLongest(7),4) 302#checkSuccess(cur.getLongest('testmoney'),4) 303#checkSuccess(cur.getLongest(8),4) 304#checkSuccess(cur.getLongest('testsmallmoney'),4) 305#checkSuccess(cur.getLongest(9),26) 306#checkSuccess(cur.getLongest('testdatetime'),26) 307#checkSuccess(cur.getLongest(10),26) 308#checkSuccess(cur.getLongest('testsmalldatetime'),26) 309checkSuccess(cur.getLongest(11),40) 310checkSuccess(cur.getLongest('testchar'),40) 311checkSuccess(cur.getLongest(12),12) 312checkSuccess(cur.getLongest('testvarchar'),12) 313checkSuccess(cur.getLongest(13),1) 314checkSuccess(cur.getLongest('testbit'),1) 315print "\n" 316 317print "ROW COUNT: \n" 318checkSuccess(cur.rowCount(),8) 319print "\n" 320 321print "TOTAL ROWS: \n" 322checkSuccess(cur.totalRows(),0) 323print "\n" 324 325print "FIRST ROW INDEX: \n" 326checkSuccess(cur.firstRowIndex(),0) 327print "\n" 328 329print "END OF RESULT SET: \n" 330checkSuccess(cur.endOfResultSet(),1) 331print "\n" 332 333print "FIELDS BY INDEX: \n" 334checkSuccess(cur.getField(0,0),"1") 335checkSuccess(cur.getField(0,1),"1") 336checkSuccess(cur.getField(0,2),"1") 337#checkSuccess(cur.getField(0,3),"1.1") 338#checkSuccess(cur.getField(0,4),"1.1") 339checkSuccess(cur.getField(0,5),"1.1") 340checkSuccess(cur.getField(0,6),"1.1") 341#checkSuccess(cur.getField(0,7),"1.00") 342#checkSuccess(cur.getField(0,8),"1.00") 343#checkSuccess(cur.getField(0,9),"Jan 1 2001 01:00:00:000AM") 344#checkSuccess(cur.getField(0,10),"Jan 1 2001 01:00:00:000AM") 345checkSuccess(cur.getField(0,11),"testchar1 ") 346checkSuccess(cur.getField(0,12),"testvarchar1") 347checkSuccess(cur.getField(0,13),"1") 348print "\n" 349checkSuccess(cur.getField(7,0),"8") 350checkSuccess(cur.getField(7,1),"8") 351checkSuccess(cur.getField(7,2),"8") 352#checkSuccess(cur.getField(7,3),"8.8") 353#checkSuccess(cur.getField(7,4),"8.8") 354#checkSuccess(cur.getField(7,5),"8.8") 355#checkSuccess(cur.getField(7,6),"8.8") 356#checkSuccess(cur.getField(7,7),"8.00") 357#checkSuccess(cur.getField(7,8),"8.00") 358#checkSuccess(cur.getField(7,9),"Jan 1 2008 08:00:00:000AM") 359#checkSuccess(cur.getField(7,10),"Jan 1 2008 08:00:00:000AM") 360checkSuccess(cur.getField(7,11),"testchar8 ") 361checkSuccess(cur.getField(7,12),"testvarchar8") 362checkSuccess(cur.getField(7,13),"1") 363print "\n" 364 365print "FIELD LENGTHS BY INDEX: \n" 366checkSuccess(cur.getFieldLength(0,0),1) 367checkSuccess(cur.getFieldLength(0,1),1) 368checkSuccess(cur.getFieldLength(0,2),1) 369#checkSuccess(cur.getFieldLength(0,3),3) 370#checkSuccess(cur.getFieldLength(0,4),3) 371checkSuccess(cur.getFieldLength(0,5),3) 372checkSuccess(cur.getFieldLength(0,6),3) 373#checkSuccess(cur.getFieldLength(0,7),4) 374#checkSuccess(cur.getFieldLength(0,8),4) 375#checkSuccess(cur.getFieldLength(0,9),26) 376#checkSuccess(cur.getFieldLength(0,10),26) 377checkSuccess(cur.getFieldLength(0,11),40) 378checkSuccess(cur.getFieldLength(0,12),12) 379checkSuccess(cur.getFieldLength(0,13),1) 380print "\n" 381checkSuccess(cur.getFieldLength(7,0),1) 382checkSuccess(cur.getFieldLength(7,1),1) 383checkSuccess(cur.getFieldLength(7,2),1) 384#checkSuccess(cur.getFieldLength(7,3),3) 385#checkSuccess(cur.getFieldLength(7,4),17) 386checkSuccess(cur.getFieldLength(7,5),3) 387checkSuccess(cur.getFieldLength(7,6),3) 388#checkSuccess(cur.getFieldLength(7,7),4) 389#checkSuccess(cur.getFieldLength(7,8),4) 390#checkSuccess(cur.getFieldLength(7,9),26) 391#checkSuccess(cur.getFieldLength(7,10),26) 392checkSuccess(cur.getFieldLength(7,11),40) 393checkSuccess(cur.getFieldLength(7,12),12) 394checkSuccess(cur.getFieldLength(7,13),1) 395print "\n" 396 397print "FIELDS BY NAME: \n" 398checkSuccess(cur.getField(0,"testint"),"1") 399checkSuccess(cur.getField(0,"testsmallint"),"1") 400checkSuccess(cur.getField(0,"testtinyint"),"1") 401#checkSuccess(cur.getField(0,"testreal"),"1.1") 402#checkSuccess(cur.getField(0,"testfloat"),"1.1") 403checkSuccess(cur.getField(0,"testdecimal"),"1.1") 404checkSuccess(cur.getField(0,"testnumeric"),"1.1") 405#checkSuccess(cur.getField(0,"testmoney"),"1.00") 406#checkSuccess(cur.getField(0,"testsmallmoney"),"1.00") 407#checkSuccess(cur.getField(0,"testdatetime"),"Jan 1 2001 01:00:00:000AM") 408#checkSuccess(cur.getField(0,"testsmalldatetime"),"Jan 1 2001 01:00:00:000AM") 409checkSuccess(cur.getField(0,"testchar"),"testchar1 ") 410checkSuccess(cur.getField(0,"testvarchar"),"testvarchar1") 411checkSuccess(cur.getField(0,"testbit"),"1") 412print "\n" 413checkSuccess(cur.getField(7,"testint"),"8") 414checkSuccess(cur.getField(7,"testsmallint"),"8") 415checkSuccess(cur.getField(7,"testtinyint"),"8") 416#checkSuccess(cur.getField(7,"testreal"),"8.8") 417#checkSuccess(cur.getField(7,"testfloat"),"8.8") 418#checkSuccess(cur.getField(7,"testdecimal"),"8.8") 419#checkSuccess(cur.getField(7,"testnumeric"),"8.8") 420#checkSuccess(cur.getField(7,"testmoney"),"8.00") 421#checkSuccess(cur.getField(7,"testsmallmoney"),"8.00") 422#checkSuccess(cur.getField(7,"testdatetime"),"Jan 1 2008 08:00:00:000AM") 423#checkSuccess(cur.getField(7,"testsmalldatetime"),"Jan 1 2008 08:00:00:000AM") 424checkSuccess(cur.getField(7,"testchar"),"testchar8 ") 425checkSuccess(cur.getField(7,"testvarchar"),"testvarchar8") 426checkSuccess(cur.getField(7,"testbit"),"1") 427print "\n" 428 429print "FIELD LENGTHS BY NAME: \n" 430checkSuccess(cur.getFieldLength(0,"testint"),1) 431checkSuccess(cur.getFieldLength(0,"testsmallint"),1) 432checkSuccess(cur.getFieldLength(0,"testtinyint"),1) 433#checkSuccess(cur.getFieldLength(0,"testreal"),3) 434#checkSuccess(cur.getFieldLength(0,"testfloat"),3) 435checkSuccess(cur.getFieldLength(0,"testdecimal"),3) 436checkSuccess(cur.getFieldLength(0,"testnumeric"),3) 437#checkSuccess(cur.getFieldLength(0,"testmoney"),4) 438#checkSuccess(cur.getFieldLength(0,"testsmallmoney"),4) 439#checkSuccess(cur.getFieldLength(0,"testdatetime"),26) 440#checkSuccess(cur.getFieldLength(0,"testsmalldatetime"),26) 441checkSuccess(cur.getFieldLength(0,"testchar"),40) 442checkSuccess(cur.getFieldLength(0,"testvarchar"),12) 443checkSuccess(cur.getFieldLength(0,"testbit"),1) 444print "\n" 445checkSuccess(cur.getFieldLength(7,"testint"),1) 446checkSuccess(cur.getFieldLength(7,"testsmallint"),1) 447checkSuccess(cur.getFieldLength(7,"testtinyint"),1) 448#checkSuccess(cur.getFieldLength(7,"testreal"),3) 449#checkSuccess(cur.getFieldLength(7,"testfloat"),17) 450checkSuccess(cur.getFieldLength(7,"testdecimal"),3) 451checkSuccess(cur.getFieldLength(7,"testnumeric"),3) 452#checkSuccess(cur.getFieldLength(7,"testmoney"),4) 453#checkSuccess(cur.getFieldLength(7,"testsmallmoney"),4) 454#checkSuccess(cur.getFieldLength(7,"testdatetime"),26) 455#checkSuccess(cur.getFieldLength(7,"testsmalldatetime"),26) 456checkSuccess(cur.getFieldLength(7,"testchar"),40) 457checkSuccess(cur.getFieldLength(7,"testvarchar"),12) 458checkSuccess(cur.getFieldLength(7,"testbit"),1) 459print "\n" 460 461print "FIELDS BY ARRAY: \n" 462fields=cur.getRow(0) 463checkSuccess(fields[0],"1") 464checkSuccess(fields[1],"1") 465checkSuccess(fields[2],"1") 466#checkSuccess(fields[3],"1.1") 467#checkSuccess(fields[4],"1.1") 468checkSuccess(fields[5],"1.1") 469checkSuccess(fields[6],"1.1") 470#checkSuccess(fields[7],"1.00") 471#checkSuccess(fields[8],"1.00") 472#checkSuccess(fields[9],"Jan 1 2001 01:00:00:000AM") 473#checkSuccess(fields[10],"Jan 1 2001 01:00:00:000AM") 474checkSuccess(fields[11],"testchar1 ") 475checkSuccess(fields[12],"testvarchar1") 476checkSuccess(fields[13],"1") 477print "\n" 478 479print "FIELD LENGTHS BY ARRAY: \n" 480fieldlens=cur.getRowLengths(0) 481checkSuccess(fieldlens[0],1) 482checkSuccess(fieldlens[1],1) 483checkSuccess(fieldlens[2],1) 484#checkSuccess(fieldlens[3],3) 485#checkSuccess(fieldlens[4],3) 486checkSuccess(fieldlens[5],3) 487checkSuccess(fieldlens[6],3) 488#checkSuccess(fieldlens[7],4) 489#checkSuccess(fieldlens[8],4) 490#checkSuccess(fieldlens[9],26) 491#checkSuccess(fieldlens[10],26) 492checkSuccess(fieldlens[11],40) 493checkSuccess(fieldlens[12],12) 494checkSuccess(fieldlens[13],1) 495print "\n" 496 497print "FIELDS BY HASH: \n" 498fields=cur.getRowHash(0) 499checkSuccess(fields["testint"],"1") 500checkSuccess(fields["testsmallint"],"1") 501checkSuccess(fields["testtinyint"],"1") 502#checkSuccess(fields["testreal"],"1.1") 503#checkSuccess(fields["testfloat"],"1.1") 504checkSuccess(fields["testdecimal"],"1.1") 505checkSuccess(fields["testnumeric"],"1.1") 506#checkSuccess(fields["testmoney"],"1.00") 507#checkSuccess(fields["testsmallmoney"],"1.00") 508#checkSuccess(fields["testdatetime"],"Jan 1 2001 01:00:00:000AM") 509#checkSuccess(fields["testsmalldatetime"],"Jan 1 2001 01:00:00:000AM") 510checkSuccess(fields["testchar"],"testchar1 ") 511checkSuccess(fields["testvarchar"],"testvarchar1") 512checkSuccess(fields["testbit"],"1") 513print "\n" 514fields=cur.getRowHash(7) 515checkSuccess(fields["testint"],"8") 516checkSuccess(fields["testsmallint"],"8") 517checkSuccess(fields["testtinyint"],"8") 518#checkSuccess(fields["testreal"],"8.8") 519#checkSuccess(fields["testfloat"],"8.8") 520#checkSuccess(fields["testdecimal"],"8.8") 521#checkSuccess(fields["testnumeric"],"8.8") 522#checkSuccess(fields["testmoney"],"8.00") 523#checkSuccess(fields["testsmallmoney"],"8.00") 524#checkSuccess(fields["testdatetime"],"Jan 1 2008 08:00:00:000AM") 525#checkSuccess(fields["testsmalldatetime"],"Jan 1 2008 08:00:00:000AM") 526checkSuccess(fields["testchar"],"testchar8 ") 527checkSuccess(fields["testvarchar"],"testvarchar8") 528checkSuccess(fields["testbit"],"1") 529print "\n" 530 531print "FIELD LENGTHS BY HASH: \n" 532fieldlengths=cur.getRowLengthsHash(0) 533checkSuccess(fieldlengths["testint"],1) 534checkSuccess(fieldlengths["testsmallint"],1) 535checkSuccess(fieldlengths["testtinyint"],1) 536#checkSuccess(fieldlengths["testreal"],3) 537#checkSuccess(fieldlengths["testfloat"],3) 538checkSuccess(fieldlengths["testdecimal"],3) 539checkSuccess(fieldlengths["testnumeric"],3) 540#checkSuccess(fieldlengths["testmoney"],4) 541#checkSuccess(fieldlengths["testsmallmoney"],4) 542#checkSuccess(fieldlengths["testdatetime"],26) 543#checkSuccess(fieldlengths["testsmalldatetime"],26) 544checkSuccess(fieldlengths["testchar"],40) 545checkSuccess(fieldlengths["testvarchar"],12) 546checkSuccess(fieldlengths["testbit"],1) 547print "\n" 548fieldlengths=cur.getRowLengthsHash(7) 549checkSuccess(fieldlengths["testsmallint"],1) 550checkSuccess(fieldlengths["testtinyint"],1) 551#checkSuccess(fieldlengths["testreal"],3) 552#checkSuccess(fieldlengths["testfloat"],17) 553checkSuccess(fieldlengths["testdecimal"],3) 554checkSuccess(fieldlengths["testnumeric"],3) 555#checkSuccess(fieldlengths["testmoney"],4) 556#checkSuccess(fieldlengths["testsmallmoney"],4) 557#checkSuccess(fieldlengths["testdatetime"],26) 558#checkSuccess(fieldlengths["testsmalldatetime"],26) 559checkSuccess(fieldlengths["testchar"],40) 560checkSuccess(fieldlengths["testvarchar"],12) 561checkSuccess(fieldlengths["testbit"],1) 562print "\n" 563 564print "INDIVIDUAL SUBSTITUTIONS: \n" 565cur.prepareQuery("select $(var1),'$(var2)',$(var3)") 566cur.substitution("var1",1) 567cur.substitution("var2","hello") 568cur.substitution("var3",10.5556,6,4) 569checkSuccess(cur.executeQuery(),1) 570print "\n" 571 572print "FIELDS: \n" 573checkSuccess(cur.getField(0,0),"1") 574checkSuccess(cur.getField(0,1),"hello") 575checkSuccess(cur.getField(0,2),"10.5556") 576print "\n" 577 578print "ARRAY SUBSTITUTIONS: \n" 579cur.prepareQuery("select $(var1),'$(var2)',$(var3)") 580cur.substitutions(["var1","var2","var3"], 581 [1,"hello",10.5556],[0,0,6],[0,0,4]) 582checkSuccess(cur.executeQuery(),1) 583print "\n" 584 585print "FIELDS: \n" 586checkSuccess(cur.getField(0,0),"1") 587checkSuccess(cur.getField(0,1),"hello") 588checkSuccess(cur.getField(0,2),"10.5556") 589print "\n" 590 591print "NULLS as nils: \n" 592cur.getNullsAsNils() 593checkSuccess(cur.sendQuery("select NULL,1,NULL"),1) 594checkSuccess(cur.getField(0,0),nil) 595checkSuccess(cur.getField(0,1),"1") 596checkSuccess(cur.getField(0,2),nil) 597cur.getNullsAsEmptyStrings() 598checkSuccess(cur.sendQuery("select NULL,1,NULL"),1) 599checkSuccess(cur.getField(0,0),"") 600checkSuccess(cur.getField(0,1),"1") 601checkSuccess(cur.getField(0,2),"") 602cur.getNullsAsNils() 603print "\n" 604 605print "RESULT SET BUFFER SIZE: \n" 606checkSuccess(cur.getResultSetBufferSize(),0) 607cur.setResultSetBufferSize(2) 608checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 609checkSuccess(cur.getResultSetBufferSize(),2) 610print "\n" 611checkSuccess(cur.firstRowIndex(),0) 612checkSuccess(cur.endOfResultSet(),0) 613checkSuccess(cur.rowCount(),2) 614checkSuccess(cur.getField(0,0),"1") 615checkSuccess(cur.getField(1,0),"2") 616checkSuccess(cur.getField(2,0),"3") 617print "\n" 618checkSuccess(cur.firstRowIndex(),2) 619checkSuccess(cur.endOfResultSet(),0) 620checkSuccess(cur.rowCount(),4) 621checkSuccess(cur.getField(6,0),"7") 622checkSuccess(cur.getField(7,0),"8") 623print "\n" 624checkSuccess(cur.firstRowIndex(),6) 625checkSuccess(cur.endOfResultSet(),0) 626checkSuccess(cur.rowCount(),8) 627checkSuccess(cur.getField(8,0),nil) 628print "\n" 629checkSuccess(cur.firstRowIndex(),8) 630checkSuccess(cur.endOfResultSet(),1) 631checkSuccess(cur.rowCount(),8) 632print "\n" 633 634print "DONT GET COLUMN INFO: \n" 635cur.dontGetColumnInfo() 636checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 637checkSuccess(cur.getColumnName(0),nil) 638checkSuccess(cur.getColumnLength(0),0) 639checkSuccess(cur.getColumnType(0),nil) 640cur.getColumnInfo() 641checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 642checkSuccess(cur.getColumnName(0),"testint") 643checkSuccess(cur.getColumnLength(0),4) 644checkSuccess(cur.getColumnType(0),"INT") 645print "\n" 646 647print "SUSPENDED SESSION: \n" 648checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 649cur.suspendResultSet() 650checkSuccess(con.suspendSession(),1) 651port=con.getConnectionPort() 652socket=con.getConnectionSocket() 653checkSuccess(con.resumeSession(port,socket),1) 654print "\n" 655checkSuccess(cur.getField(0,0),"1") 656checkSuccess(cur.getField(1,0),"2") 657checkSuccess(cur.getField(2,0),"3") 658checkSuccess(cur.getField(3,0),"4") 659checkSuccess(cur.getField(4,0),"5") 660checkSuccess(cur.getField(5,0),"6") 661checkSuccess(cur.getField(6,0),"7") 662checkSuccess(cur.getField(7,0),"8") 663print "\n" 664checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 665cur.suspendResultSet() 666checkSuccess(con.suspendSession(),1) 667port=con.getConnectionPort() 668socket=con.getConnectionSocket() 669checkSuccess(con.resumeSession(port,socket),1) 670checkSuccess(cur.getField(0,0),"1") 671checkSuccess(cur.getField(1,0),"2") 672checkSuccess(cur.getField(2,0),"3") 673checkSuccess(cur.getField(3,0),"4") 674checkSuccess(cur.getField(4,0),"5") 675checkSuccess(cur.getField(5,0),"6") 676checkSuccess(cur.getField(6,0),"7") 677checkSuccess(cur.getField(7,0),"8") 678print "\n" 679checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 680cur.suspendResultSet() 681checkSuccess(con.suspendSession(),1) 682port=con.getConnectionPort() 683socket=con.getConnectionSocket() 684checkSuccess(con.resumeSession(port,socket),1) 685print "\n" 686checkSuccess(cur.getField(0,0),"1") 687checkSuccess(cur.getField(1,0),"2") 688checkSuccess(cur.getField(2,0),"3") 689checkSuccess(cur.getField(3,0),"4") 690checkSuccess(cur.getField(4,0),"5") 691checkSuccess(cur.getField(5,0),"6") 692checkSuccess(cur.getField(6,0),"7") 693checkSuccess(cur.getField(7,0),"8") 694print "\n" 695 696print "SUSPENDED RESULT SET: \n" 697cur.setResultSetBufferSize(2) 698checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 699checkSuccess(cur.getField(2,0),"3") 700id=cur.getResultSetId() 701cur.suspendResultSet() 702checkSuccess(con.suspendSession(),1) 703port=con.getConnectionPort() 704socket=con.getConnectionSocket() 705checkSuccess(con.resumeSession(port,socket),1) 706checkSuccess(cur.resumeResultSet(id),1) 707print "\n" 708checkSuccess(cur.firstRowIndex(),4) 709checkSuccess(cur.endOfResultSet(),0) 710checkSuccess(cur.rowCount(),6) 711checkSuccess(cur.getField(7,0),"8") 712print "\n" 713checkSuccess(cur.firstRowIndex(),6) 714checkSuccess(cur.endOfResultSet(),0) 715checkSuccess(cur.rowCount(),8) 716checkSuccess(cur.getField(8,0),nil) 717print "\n" 718checkSuccess(cur.firstRowIndex(),8) 719checkSuccess(cur.endOfResultSet(),1) 720checkSuccess(cur.rowCount(),8) 721cur.setResultSetBufferSize(0) 722print "\n" 723 724print "CACHED RESULT SET: \n" 725cur.cacheToFile("cachefile1") 726cur.setCacheTtl(200) 727checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 728filename=cur.getCacheFileName() 729checkSuccess(filename,"cachefile1") 730cur.cacheOff() 731checkSuccess(cur.openCachedResultSet(filename),1) 732checkSuccess(cur.getField(7,0),"8") 733print "\n" 734 735print "COLUMN COUNT FOR CACHED RESULT SET: \n" 736checkSuccess(cur.colCount(),14) 737print "\n" 738 739print "COLUMN NAMES FOR CACHED RESULT SET: \n" 740checkSuccess(cur.getColumnName(0),"testint") 741checkSuccess(cur.getColumnName(1),"testsmallint") 742checkSuccess(cur.getColumnName(2),"testtinyint") 743checkSuccess(cur.getColumnName(3),"testreal") 744checkSuccess(cur.getColumnName(4),"testfloat") 745checkSuccess(cur.getColumnName(5),"testdecimal") 746checkSuccess(cur.getColumnName(6),"testnumeric") 747checkSuccess(cur.getColumnName(7),"testmoney") 748checkSuccess(cur.getColumnName(8),"testsmallmoney") 749checkSuccess(cur.getColumnName(9),"testdatetime") 750checkSuccess(cur.getColumnName(10),"testsmalldatetime") 751checkSuccess(cur.getColumnName(11),"testchar") 752checkSuccess(cur.getColumnName(12),"testvarchar") 753checkSuccess(cur.getColumnName(13),"testbit") 754cols=cur.getColumnNames() 755checkSuccess(cols[0],"testint") 756checkSuccess(cols[1],"testsmallint") 757checkSuccess(cols[2],"testtinyint") 758checkSuccess(cols[3],"testreal") 759checkSuccess(cols[4],"testfloat") 760checkSuccess(cols[5],"testdecimal") 761checkSuccess(cols[6],"testnumeric") 762checkSuccess(cols[7],"testmoney") 763checkSuccess(cols[8],"testsmallmoney") 764checkSuccess(cols[9],"testdatetime") 765checkSuccess(cols[10],"testsmalldatetime") 766checkSuccess(cols[11],"testchar") 767checkSuccess(cols[12],"testvarchar") 768checkSuccess(cols[13],"testbit") 769print "\n" 770 771print "CACHED RESULT SET WITH RESULT SET BUFFER SIZE: \n" 772cur.setResultSetBufferSize(2) 773cur.cacheToFile("cachefile1") 774cur.setCacheTtl(200) 775checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 776filename=cur.getCacheFileName() 777checkSuccess(filename,"cachefile1") 778cur.cacheOff() 779checkSuccess(cur.openCachedResultSet(filename),1) 780checkSuccess(cur.getField(7,0),"8") 781checkSuccess(cur.getField(8,0),nil) 782cur.setResultSetBufferSize(0) 783print "\n" 784 785print "FROM ONE CACHE FILE TO ANOTHER: \n" 786cur.cacheToFile("cachefile2") 787checkSuccess(cur.openCachedResultSet("cachefile1"),1) 788cur.cacheOff() 789checkSuccess(cur.openCachedResultSet("cachefile2"),1) 790checkSuccess(cur.getField(7,0),"8") 791checkSuccess(cur.getField(8,0),nil) 792print "\n" 793 794print "FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: \n" 795cur.setResultSetBufferSize(2) 796cur.cacheToFile("cachefile2") 797checkSuccess(cur.openCachedResultSet("cachefile1"),1) 798cur.cacheOff() 799checkSuccess(cur.openCachedResultSet("cachefile2"),1) 800checkSuccess(cur.getField(7,0),"8") 801checkSuccess(cur.getField(8,0),nil) 802cur.setResultSetBufferSize(0) 803print "\n" 804 805print "CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: \n" 806cur.setResultSetBufferSize(2) 807cur.cacheToFile("cachefile1") 808cur.setCacheTtl(200) 809checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 810checkSuccess(cur.getField(2,0),"3") 811filename=cur.getCacheFileName() 812checkSuccess(filename,"cachefile1") 813id=cur.getResultSetId() 814cur.suspendResultSet() 815checkSuccess(con.suspendSession(),1) 816port=con.getConnectionPort() 817socket=con.getConnectionSocket() 818print "\n" 819checkSuccess(con.resumeSession(port,socket),1) 820checkSuccess(cur.resumeCachedResultSet(id,filename),1) 821print "\n" 822checkSuccess(cur.firstRowIndex(),4) 823checkSuccess(cur.endOfResultSet(),0) 824checkSuccess(cur.rowCount(),6) 825checkSuccess(cur.getField(7,0),"8") 826print "\n" 827checkSuccess(cur.firstRowIndex(),6) 828checkSuccess(cur.endOfResultSet(),0) 829checkSuccess(cur.rowCount(),8) 830checkSuccess(cur.getField(8,0),nil) 831print "\n" 832checkSuccess(cur.firstRowIndex(),8) 833checkSuccess(cur.endOfResultSet(),1) 834checkSuccess(cur.rowCount(),8) 835cur.cacheOff() 836print "\n" 837checkSuccess(cur.openCachedResultSet(filename),1) 838checkSuccess(cur.getField(7,0),"8") 839checkSuccess(cur.getField(8,0),nil) 840cur.setResultSetBufferSize(0) 841print "\n" 842 843print "FINISHED SUSPENDED SESSION: \n" 844checkSuccess(cur.sendQuery("select * from testtable order by testint"),1) 845checkSuccess(cur.getField(4,0),"5") 846checkSuccess(cur.getField(5,0),"6") 847checkSuccess(cur.getField(6,0),"7") 848checkSuccess(cur.getField(7,0),"8") 849id=cur.getResultSetId() 850cur.suspendResultSet() 851checkSuccess(con.suspendSession(),1) 852port=con.getConnectionPort() 853socket=con.getConnectionSocket() 854checkSuccess(con.resumeSession(port,socket),1) 855checkSuccess(cur.resumeResultSet(id),1) 856checkSuccess(cur.getField(4,0),nil) 857checkSuccess(cur.getField(5,0),nil) 858checkSuccess(cur.getField(6,0),nil) 859checkSuccess(cur.getField(7,0),nil) 860print "\n" 861 862# drop existing table 863cur.sendQuery("commit tran") 864cur.sendQuery("drop table testtable") 865 866# invalid queries... 867print "INVALID QUERIES: \n" 868checkSuccess(cur.sendQuery("select * from testtable order by testint"),0) 869checkSuccess(cur.sendQuery("select * from testtable order by testint"),0) 870checkSuccess(cur.sendQuery("select * from testtable order by testint"),0) 871checkSuccess(cur.sendQuery("select * from testtable order by testint"),0) 872print "\n" 873checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0) 874checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0) 875checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0) 876checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0) 877print "\n" 878checkSuccess(cur.sendQuery("create table testtable"),0) 879checkSuccess(cur.sendQuery("create table testtable"),0) 880checkSuccess(cur.sendQuery("create table testtable"),0) 881checkSuccess(cur.sendQuery("create table testtable"),0) 882print "\n" 883 884exit(0) 885