1// Copyright (c) 1999-2018 David Muse 2// See the file COPYING for more information. 3 4var sqlrelay=require("sqlrelay"); 5 6 7function checkSuccess(value,success) { 8 if (value==success) { 9 process.stdout.write("success "); 10 } else { 11 console.log(value+"!="+success+" "); 12 console.log("failure "); 13 process.exit(1); 14 } 15} 16 17var subvars=["var1","var2","var3"]; 18var subvalstrings=["hi","hello","bye"]; 19var subvallongs=[1,2,3]; 20var subvaldoubles=[10.55,10.556,10.5556]; 21var precs=[4,5,6]; 22var scales=[2,3,4]; 23var cols; 24var fields; 25var port; 26var socket; 27var id; 28var filename; 29var fieldlens; 30 31 32// instantiation 33var con=new sqlrelay.SQLRConnection("sqlrelay",9000,"/tmp/test.socket","test","test",0,1); 34var cur=new sqlrelay.SQLRCursor(con); 35 36// get database type 37console.log("IDENTIFY: "); 38checkSuccess(con.identify(),"mysql"); 39console.log("\n"); 40 41// get the db version 42var dbversion=con.dbVersion(); 43var majorversion=parseInt(dbversion.substring(0,1)); 44 45// ping 46console.log("PING: "); 47checkSuccess(con.ping(),1); 48console.log("\n"); 49 50// drop existing table 51cur.sendQuery("drop table testtable"); 52 53// create a new table 54console.log("CREATE TEMPTABLE: "); 55checkSuccess(cur.sendQuery("create table testdb.testtable (testtinyint tinyint, testsmallint smallint, testmediumint mediumint, testint int, testbigint bigint, testfloat float, testreal real, testdecimal decimal(2,1), testdate date, testtime time, testdatetime datetime, testyear year, testchar char(40), testtext text, testvarchar varchar(40), testtinytext tinytext, testmediumtext mediumtext, testlongtext longtext, testtimestamp timestamp)"),1); 56console.log("\n"); 57 58console.log("INSERT: "); 59checkSuccess(cur.sendQuery("insert into testdb.testtable values (1,1,1,1,1,1.1,1.1,1.1,'2001-01-01','01:00:00','2001-01-01 01:00:00','2001','char1','text1','varchar1','tinytext1','mediumtext1','longtext1',null)"),1); 60checkSuccess(cur.sendQuery("insert into testdb.testtable values (2,2,2,2,2,2.1,2.1,2.1,'2002-01-01','02:00:00','2002-01-01 02:00:00','2002','char2','text2','varchar2','tinytext2','mediumtext2','longtext2',null)"),1); 61checkSuccess(cur.sendQuery("insert into testdb.testtable values (3,3,3,3,3,3.1,3.1,3.1,'2003-01-01','03:00:00','2003-01-01 03:00:00','2003','char3','text3','varchar3','tinytext3','mediumtext3','longtext3',null)"),1); 62checkSuccess(cur.sendQuery("insert into testdb.testtable values (4,4,4,4,4,4.1,4.1,4.1,'2004-01-01','04:00:00','2004-01-01 04:00:00','2004','char4','text4','varchar4','tinytext4','mediumtext4','longtext4',null)"),1); 63console.log("\n"); 64 65console.log("AFFECTED ROWS: "); 66checkSuccess(cur.affectedRows(),1); 67console.log("\n"); 68 69console.log("BIND BY POSITION: "); 70cur.prepareQuery("insert into testdb.testtable values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,null)"); 71checkSuccess(cur.countBindVariables(),18); 72cur.inputBind("1",5); 73cur.inputBind("2",5); 74cur.inputBind("3",5); 75cur.inputBind("4",5); 76cur.inputBind("5",5); 77cur.inputBind("6",5.1,2,1); 78cur.inputBind("7",5.1,2,1); 79cur.inputBind("8",5.1,2,1); 80cur.inputBind("9","2005-01-01"); 81cur.inputBind("10","05:00:00"); 82cur.inputBind("11","2005-01-01 05:00:00"); 83cur.inputBind("12","2005"); 84cur.inputBind("13","char5"); 85cur.inputBind("14","text5"); 86cur.inputBind("15","varchar5"); 87cur.inputBind("16","tinytext5"); 88cur.inputBind("17","mediumtext5"); 89cur.inputBind("18","longtext5"); 90checkSuccess(cur.executeQuery(),1); 91cur.clearBinds(); 92cur.inputBind("1",6); 93cur.inputBind("2",6); 94cur.inputBind("3",6); 95cur.inputBind("4",6); 96cur.inputBind("5",6); 97cur.inputBind("6",6.1,2,1); 98cur.inputBind("7",6.1,2,1); 99cur.inputBind("8",6.1,2,1); 100cur.inputBind("9","2006-01-01"); 101cur.inputBind("10","06:00:00"); 102cur.inputBind("11","2006-01-01 06:00:00"); 103cur.inputBind("12","2006"); 104cur.inputBind("13","char6"); 105cur.inputBind("14","text6"); 106cur.inputBind("15","varchar6"); 107cur.inputBind("16","tinytext6"); 108cur.inputBind("17","mediumtext6"); 109cur.inputBind("18","longtext6"); 110checkSuccess(cur.executeQuery(),1); 111cur.clearBinds(); 112cur.inputBind("1",7); 113cur.inputBind("2",7); 114cur.inputBind("3",7); 115cur.inputBind("4",7); 116cur.inputBind("5",7); 117cur.inputBind("6",7.1,2,1); 118cur.inputBind("7",7.1,2,1); 119cur.inputBind("8",7.1,2,1); 120cur.inputBind("9","2007-01-01"); 121cur.inputBind("10","07:00:00"); 122cur.inputBind("11","2007-01-01 07:00:00"); 123cur.inputBind("12","2007"); 124cur.inputBind("13","char7"); 125cur.inputBind("14","text7"); 126cur.inputBind("15","varchar7"); 127cur.inputBind("16","tinytext7"); 128cur.inputBind("17","mediumtext7"); 129cur.inputBind("18","longtext7"); 130checkSuccess(cur.executeQuery(),1); 131console.log("\n"); 132 133console.log("BIND BY POSITION WITH VALIDATION: "); 134cur.clearBinds(); 135cur.inputBind("1",8); 136cur.inputBind("2",8); 137cur.inputBind("3",8); 138cur.inputBind("4",8); 139cur.inputBind("5",8); 140cur.inputBind("6",8.1,2,1); 141cur.inputBind("7",8.1,2,1); 142cur.inputBind("8",8.1,2,1); 143cur.inputBind("9","2008-01-01"); 144cur.inputBind("10","08:00:00"); 145cur.inputBind("11","2008-01-01 08:00:00"); 146cur.inputBind("12","2008"); 147cur.inputBind("13","char8"); 148cur.inputBind("14","text8"); 149cur.inputBind("15","varchar8"); 150cur.inputBind("16","tinytext8"); 151cur.inputBind("17","mediumtext8"); 152cur.inputBind("18","longtext8"); 153cur.validateBinds(); 154checkSuccess(cur.executeQuery(),1); 155console.log("\n"); 156 157console.log("SELECT: "); 158checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 159console.log("\n"); 160 161console.log("COLUMN COUNT: "); 162checkSuccess(cur.colCount(),19); 163console.log("\n"); 164 165console.log("COLUMN NAMES: "); 166checkSuccess(cur.getColumnName(0),"testtinyint"); 167checkSuccess(cur.getColumnName(1),"testsmallint"); 168checkSuccess(cur.getColumnName(2),"testmediumint"); 169checkSuccess(cur.getColumnName(3),"testint"); 170checkSuccess(cur.getColumnName(4),"testbigint"); 171checkSuccess(cur.getColumnName(5),"testfloat"); 172checkSuccess(cur.getColumnName(6),"testreal"); 173checkSuccess(cur.getColumnName(7),"testdecimal"); 174checkSuccess(cur.getColumnName(8),"testdate"); 175checkSuccess(cur.getColumnName(9),"testtime"); 176checkSuccess(cur.getColumnName(10),"testdatetime"); 177checkSuccess(cur.getColumnName(11),"testyear"); 178checkSuccess(cur.getColumnName(12),"testchar"); 179checkSuccess(cur.getColumnName(13),"testtext"); 180checkSuccess(cur.getColumnName(14),"testvarchar"); 181checkSuccess(cur.getColumnName(15),"testtinytext"); 182checkSuccess(cur.getColumnName(16),"testmediumtext"); 183checkSuccess(cur.getColumnName(17),"testlongtext"); 184checkSuccess(cur.getColumnName(18),"testtimestamp"); 185cols=cur.getColumnNames(); 186checkSuccess(cols[0],"testtinyint"); 187checkSuccess(cols[1],"testsmallint"); 188checkSuccess(cols[2],"testmediumint"); 189checkSuccess(cols[3],"testint"); 190checkSuccess(cols[4],"testbigint"); 191checkSuccess(cols[5],"testfloat"); 192checkSuccess(cols[6],"testreal"); 193checkSuccess(cols[7],"testdecimal"); 194checkSuccess(cols[8],"testdate"); 195checkSuccess(cols[9],"testtime"); 196checkSuccess(cols[10],"testdatetime"); 197checkSuccess(cols[11],"testyear"); 198checkSuccess(cols[12],"testchar"); 199checkSuccess(cols[13],"testtext"); 200checkSuccess(cols[14],"testvarchar"); 201checkSuccess(cols[15],"testtinytext"); 202checkSuccess(cols[16],"testmediumtext"); 203checkSuccess(cols[17],"testlongtext"); 204checkSuccess(cols[18],"testtimestamp"); 205console.log("\n"); 206 207console.log("COLUMN TYPES: "); 208checkSuccess(cur.getColumnType(0),"TINYINT"); 209checkSuccess(cur.getColumnType(1),"SMALLINT"); 210checkSuccess(cur.getColumnType(2),"MEDIUMINT"); 211checkSuccess(cur.getColumnType(3),"INT"); 212checkSuccess(cur.getColumnType(4),"BIGINT"); 213checkSuccess(cur.getColumnType(5),"FLOAT"); 214checkSuccess(cur.getColumnType(6),"REAL"); 215checkSuccess(cur.getColumnType(7),"DECIMAL"); 216checkSuccess(cur.getColumnType(8),"DATE"); 217checkSuccess(cur.getColumnType(9),"TIME"); 218checkSuccess(cur.getColumnType(10),"DATETIME"); 219checkSuccess(cur.getColumnType(11),"YEAR"); 220if (majorversion==3) { 221 checkSuccess(cur.getColumnType(12),"VARSTRING"); 222} else { 223 checkSuccess(cur.getColumnType(12),"STRING"); 224} 225checkSuccess(cur.getColumnType(13),"BLOB"); 226checkSuccess(cur.getColumnType(14),"VARSTRING"); 227checkSuccess(cur.getColumnType(15),"TINYBLOB"); 228checkSuccess(cur.getColumnType(16),"MEDIUMBLOB"); 229checkSuccess(cur.getColumnType(17),"LONGBLOB"); 230checkSuccess(cur.getColumnType(18),"TIMESTAMP"); 231checkSuccess(cur.getColumnType("testtinyint"),"TINYINT"); 232checkSuccess(cur.getColumnType("testsmallint"),"SMALLINT"); 233checkSuccess(cur.getColumnType("testmediumint"),"MEDIUMINT"); 234checkSuccess(cur.getColumnType("testint"),"INT"); 235checkSuccess(cur.getColumnType("testbigint"),"BIGINT"); 236checkSuccess(cur.getColumnType("testfloat"),"FLOAT"); 237checkSuccess(cur.getColumnType("testreal"),"REAL"); 238checkSuccess(cur.getColumnType("testdecimal"),"DECIMAL"); 239checkSuccess(cur.getColumnType("testdate"),"DATE"); 240checkSuccess(cur.getColumnType("testtime"),"TIME"); 241checkSuccess(cur.getColumnType("testdatetime"),"DATETIME"); 242checkSuccess(cur.getColumnType("testyear"),"YEAR"); 243if (majorversion==3) { 244 checkSuccess(cur.getColumnType("testchar"),"VARSTRING"); 245} else { 246 checkSuccess(cur.getColumnType("testchar"),"STRING"); 247} 248checkSuccess(cur.getColumnType("testtext"),"BLOB"); 249checkSuccess(cur.getColumnType("testvarchar"),"VARSTRING"); 250checkSuccess(cur.getColumnType("testtinytext"),"TINYBLOB"); 251checkSuccess(cur.getColumnType("testmediumtext"),"MEDIUMBLOB"); 252checkSuccess(cur.getColumnType("testlongtext"),"LONGBLOB"); 253checkSuccess(cur.getColumnType("testtimestamp"),"TIMESTAMP"); 254console.log("\n"); 255 256console.log("COLUMN LENGTH: "); 257checkSuccess(cur.getColumnLength(0),1); 258checkSuccess(cur.getColumnLength(1),2); 259checkSuccess(cur.getColumnLength(2),3); 260checkSuccess(cur.getColumnLength(3),4); 261checkSuccess(cur.getColumnLength(4),8); 262checkSuccess(cur.getColumnLength(5),4); 263checkSuccess(cur.getColumnLength(6),8); 264checkSuccess(cur.getColumnLength(7),6); 265checkSuccess(cur.getColumnLength(8),3); 266checkSuccess(cur.getColumnLength(9),3); 267checkSuccess(cur.getColumnLength(10),8); 268checkSuccess(cur.getColumnLength(11),1); 269//checkSuccess(cur.getColumnLength(12),40); 270checkSuccess(cur.getColumnLength(13),65535); 271//checkSuccess(cur.getColumnLength(14),41); 272checkSuccess(cur.getColumnLength(15),255); 273checkSuccess(cur.getColumnLength(16),16777215); 274checkSuccess(cur.getColumnLength(17),2147483647); 275checkSuccess(cur.getColumnLength(18),4); 276checkSuccess(cur.getColumnLength("testtinyint"),1); 277checkSuccess(cur.getColumnLength("testsmallint"),2); 278checkSuccess(cur.getColumnLength("testmediumint"),3); 279checkSuccess(cur.getColumnLength("testint"),4); 280checkSuccess(cur.getColumnLength("testbigint"),8); 281checkSuccess(cur.getColumnLength("testfloat"),4); 282checkSuccess(cur.getColumnLength("testreal"),8); 283checkSuccess(cur.getColumnLength("testdecimal"),6); 284checkSuccess(cur.getColumnLength("testdate"),3); 285checkSuccess(cur.getColumnLength("testtime"),3); 286checkSuccess(cur.getColumnLength("testdatetime"),8); 287checkSuccess(cur.getColumnLength("testyear"),1); 288//checkSuccess(cur.getColumnLength("testchar"),40); 289checkSuccess(cur.getColumnLength("testtext"),65535); 290//checkSuccess(cur.getColumnLength("testvarchar"),41); 291checkSuccess(cur.getColumnLength("testtinytext"),255); 292checkSuccess(cur.getColumnLength("testmediumtext"),16777215); 293checkSuccess(cur.getColumnLength("testlongtext"),2147483647); 294checkSuccess(cur.getColumnLength("testtimestamp"),4); 295console.log("\n"); 296 297console.log("LONGEST COLUMN: "); 298checkSuccess(cur.getLongest(0),1); 299checkSuccess(cur.getLongest(1),1); 300checkSuccess(cur.getLongest(2),1); 301checkSuccess(cur.getLongest(3),1); 302checkSuccess(cur.getLongest(4),1); 303//checkSuccess(cur.getLongest(5),3); 304checkSuccess(cur.getLongest(6),3); 305checkSuccess(cur.getLongest(7),3); 306checkSuccess(cur.getLongest(8),10); 307checkSuccess(cur.getLongest(9),8); 308checkSuccess(cur.getLongest(10),19); 309checkSuccess(cur.getLongest(11),4); 310checkSuccess(cur.getLongest(12),5); 311checkSuccess(cur.getLongest(13),5); 312checkSuccess(cur.getLongest(14),8); 313checkSuccess(cur.getLongest(15),9); 314checkSuccess(cur.getLongest(16),11); 315checkSuccess(cur.getLongest(17),9); 316if (majorversion==3) { 317 checkSuccess(cur.getLongest(18),14); 318} else { 319 checkSuccess(cur.getLongest(18),19); 320} 321checkSuccess(cur.getLongest("testtinyint"),1); 322checkSuccess(cur.getLongest("testsmallint"),1); 323checkSuccess(cur.getLongest("testmediumint"),1); 324checkSuccess(cur.getLongest("testint"),1); 325checkSuccess(cur.getLongest("testbigint"),1); 326//checkSuccess(cur.getLongest("testfloat"),3); 327checkSuccess(cur.getLongest("testreal"),3); 328checkSuccess(cur.getLongest("testdecimal"),3); 329checkSuccess(cur.getLongest("testdate"),10); 330checkSuccess(cur.getLongest("testtime"),8); 331checkSuccess(cur.getLongest("testdatetime"),19); 332checkSuccess(cur.getLongest("testyear"),4); 333checkSuccess(cur.getLongest("testchar"),5); 334checkSuccess(cur.getLongest("testtext"),5); 335checkSuccess(cur.getLongest("testvarchar"),8); 336checkSuccess(cur.getLongest("testtinytext"),9); 337checkSuccess(cur.getLongest("testmediumtext"),11); 338checkSuccess(cur.getLongest("testlongtext"),9); 339if (majorversion==3) { 340 checkSuccess(cur.getLongest("testtimestamp"),14); 341} else { 342 checkSuccess(cur.getLongest("testtimestamp"),19); 343} 344console.log("\n"); 345 346console.log("ROW COUNT: "); 347checkSuccess(cur.rowCount(),8); 348console.log("\n"); 349 350console.log("TOTAL ROWS: "); 351// older versions of mysql know this 352//checkSuccess(cur.totalRows(),0); 353console.log("\n"); 354 355console.log("FIRST ROW INDEX: "); 356checkSuccess(cur.firstRowIndex(),0); 357console.log("\n"); 358 359console.log("END OF RESULT SET: "); 360checkSuccess(cur.endOfResultSet(),1); 361console.log("\n"); 362 363console.log("FIELDS BY INDEX: "); 364checkSuccess(cur.getField(0,0),"1"); 365checkSuccess(cur.getField(0,1),"1"); 366checkSuccess(cur.getField(0,2),"1"); 367checkSuccess(cur.getField(0,3),"1"); 368checkSuccess(cur.getField(0,4),"1"); 369//checkSuccess(cur.getField(0,5),"1.1"); 370checkSuccess(cur.getField(0,6),"1.1"); 371checkSuccess(cur.getField(0,7),"1.1"); 372checkSuccess(cur.getField(0,8),"2001-01-01"); 373checkSuccess(cur.getField(0,9),"01:00:00"); 374checkSuccess(cur.getField(0,10),"2001-01-01 01:00:00"); 375checkSuccess(cur.getField(0,11),"2001"); 376checkSuccess(cur.getField(0,12),"char1"); 377checkSuccess(cur.getField(0,13),"text1"); 378checkSuccess(cur.getField(0,14),"varchar1"); 379checkSuccess(cur.getField(0,15),"tinytext1"); 380checkSuccess(cur.getField(0,16),"mediumtext1"); 381checkSuccess(cur.getField(0,17),"longtext1"); 382console.log(); 383checkSuccess(cur.getField(7,0),"8"); 384checkSuccess(cur.getField(7,1),"8"); 385checkSuccess(cur.getField(7,2),"8"); 386checkSuccess(cur.getField(7,3),"8"); 387checkSuccess(cur.getField(7,4),"8"); 388//checkSuccess(cur.getField(7,5),"8.1"); 389checkSuccess(cur.getField(7,6),"8.1"); 390checkSuccess(cur.getField(7,7),"8.1"); 391checkSuccess(cur.getField(7,8),"2008-01-01"); 392checkSuccess(cur.getField(7,9),"08:00:00"); 393checkSuccess(cur.getField(7,10),"2008-01-01 08:00:00"); 394checkSuccess(cur.getField(7,11),"2008"); 395checkSuccess(cur.getField(7,12),"char8"); 396checkSuccess(cur.getField(7,13),"text8"); 397checkSuccess(cur.getField(7,14),"varchar8"); 398checkSuccess(cur.getField(7,15),"tinytext8"); 399checkSuccess(cur.getField(7,16),"mediumtext8"); 400checkSuccess(cur.getField(7,17),"longtext8"); 401console.log(); 402 403console.log("FIELD LENGTHS BY INDEX: "); 404checkSuccess(cur.getFieldLength(0,0),1); 405checkSuccess(cur.getFieldLength(0,1),1); 406checkSuccess(cur.getFieldLength(0,2),1); 407checkSuccess(cur.getFieldLength(0,3),1); 408checkSuccess(cur.getFieldLength(0,4),1); 409//checkSuccess(cur.getFieldLength(0,5),3); 410checkSuccess(cur.getFieldLength(0,6),3); 411checkSuccess(cur.getFieldLength(0,7),3); 412checkSuccess(cur.getFieldLength(0,8),10); 413checkSuccess(cur.getFieldLength(0,9),8); 414checkSuccess(cur.getFieldLength(0,10),19); 415checkSuccess(cur.getFieldLength(0,11),4); 416checkSuccess(cur.getFieldLength(0,12),5); 417checkSuccess(cur.getFieldLength(0,13),5); 418checkSuccess(cur.getFieldLength(0,14),8); 419checkSuccess(cur.getFieldLength(0,15),9); 420checkSuccess(cur.getFieldLength(0,16),11); 421checkSuccess(cur.getFieldLength(0,17),9); 422console.log(); 423checkSuccess(cur.getFieldLength(7,0),1); 424checkSuccess(cur.getFieldLength(7,1),1); 425checkSuccess(cur.getFieldLength(7,2),1); 426checkSuccess(cur.getFieldLength(7,3),1); 427checkSuccess(cur.getFieldLength(7,4),1); 428//checkSuccess(cur.getFieldLength(7,5),3); 429checkSuccess(cur.getFieldLength(7,6),3); 430checkSuccess(cur.getFieldLength(7,7),3); 431checkSuccess(cur.getFieldLength(7,8),10); 432checkSuccess(cur.getFieldLength(7,9),8); 433checkSuccess(cur.getFieldLength(7,10),19); 434checkSuccess(cur.getFieldLength(7,11),4); 435checkSuccess(cur.getFieldLength(7,12),5); 436checkSuccess(cur.getFieldLength(7,13),5); 437checkSuccess(cur.getFieldLength(7,14),8); 438checkSuccess(cur.getFieldLength(7,15),9); 439checkSuccess(cur.getFieldLength(7,16),11); 440checkSuccess(cur.getFieldLength(7,17),9); 441console.log(); 442 443console.log("FIELDS BY NAME: "); 444checkSuccess(cur.getField(0,"testtinyint"),"1"); 445checkSuccess(cur.getField(0,"testsmallint"),"1"); 446checkSuccess(cur.getField(0,"testmediumint"),"1"); 447checkSuccess(cur.getField(0,"testint"),"1"); 448checkSuccess(cur.getField(0,"testbigint"),"1"); 449//checkSuccess(cur.getField(0,"testfloat"),"1.1"); 450checkSuccess(cur.getField(0,"testreal"),"1.1"); 451checkSuccess(cur.getField(0,"testdecimal"),"1.1"); 452checkSuccess(cur.getField(0,"testdate"),"2001-01-01"); 453checkSuccess(cur.getField(0,"testtime"),"01:00:00"); 454checkSuccess(cur.getField(0,"testdatetime"),"2001-01-01 01:00:00"); 455checkSuccess(cur.getField(0,"testyear"),"2001"); 456checkSuccess(cur.getField(0,"testchar"),"char1"); 457checkSuccess(cur.getField(0,"testtext"),"text1"); 458checkSuccess(cur.getField(0,"testvarchar"),"varchar1"); 459checkSuccess(cur.getField(0,"testtinytext"),"tinytext1"); 460checkSuccess(cur.getField(0,"testmediumtext"),"mediumtext1"); 461checkSuccess(cur.getField(0,"testlongtext"),"longtext1"); 462console.log(); 463checkSuccess(cur.getField(7,"testtinyint"),"8"); 464checkSuccess(cur.getField(7,"testsmallint"),"8"); 465checkSuccess(cur.getField(7,"testmediumint"),"8"); 466checkSuccess(cur.getField(7,"testint"),"8"); 467checkSuccess(cur.getField(7,"testbigint"),"8"); 468//checkSuccess(cur.getField(7,"testfloat"),"8.1"); 469checkSuccess(cur.getField(7,"testreal"),"8.1"); 470checkSuccess(cur.getField(7,"testdecimal"),"8.1"); 471checkSuccess(cur.getField(7,"testdate"),"2008-01-01"); 472checkSuccess(cur.getField(7,"testtime"),"08:00:00"); 473checkSuccess(cur.getField(7,"testdatetime"),"2008-01-01 08:00:00"); 474checkSuccess(cur.getField(7,"testyear"),"2008"); 475checkSuccess(cur.getField(7,"testchar"),"char8"); 476checkSuccess(cur.getField(7,"testtext"),"text8"); 477checkSuccess(cur.getField(7,"testvarchar"),"varchar8"); 478checkSuccess(cur.getField(7,"testtinytext"),"tinytext8"); 479checkSuccess(cur.getField(7,"testmediumtext"),"mediumtext8"); 480checkSuccess(cur.getField(7,"testlongtext"),"longtext8"); 481console.log(); 482 483console.log("FIELD LENGTHS BY NAME: "); 484checkSuccess(cur.getFieldLength(0,"testtinyint"),1); 485checkSuccess(cur.getFieldLength(0,"testsmallint"),1); 486checkSuccess(cur.getFieldLength(0,"testmediumint"),1); 487checkSuccess(cur.getFieldLength(0,"testint"),1); 488checkSuccess(cur.getFieldLength(0,"testbigint"),1); 489//checkSuccess(cur.getFieldLength(0,"testfloat"),3); 490checkSuccess(cur.getFieldLength(0,"testreal"),3); 491checkSuccess(cur.getFieldLength(0,"testdecimal"),3); 492checkSuccess(cur.getFieldLength(0,"testdate"),10); 493checkSuccess(cur.getFieldLength(0,"testtime"),8); 494checkSuccess(cur.getFieldLength(0,"testdatetime"),19); 495checkSuccess(cur.getFieldLength(0,"testyear"),4); 496checkSuccess(cur.getFieldLength(0,"testchar"),5); 497checkSuccess(cur.getFieldLength(0,"testtext"),5); 498checkSuccess(cur.getFieldLength(0,"testvarchar"),8); 499checkSuccess(cur.getFieldLength(0,"testtinytext"),9); 500checkSuccess(cur.getFieldLength(0,"testmediumtext"),11); 501checkSuccess(cur.getFieldLength(0,"testlongtext"),9); 502console.log(); 503checkSuccess(cur.getFieldLength(7,"testtinyint"),1); 504checkSuccess(cur.getFieldLength(7,"testsmallint"),1); 505checkSuccess(cur.getFieldLength(7,"testmediumint"),1); 506checkSuccess(cur.getFieldLength(7,"testint"),1); 507checkSuccess(cur.getFieldLength(7,"testbigint"),1); 508//checkSuccess(cur.getFieldLength(7,"testfloat"),3); 509checkSuccess(cur.getFieldLength(7,"testreal"),3); 510checkSuccess(cur.getFieldLength(7,"testdecimal"),3); 511checkSuccess(cur.getFieldLength(7,"testdate"),10); 512checkSuccess(cur.getFieldLength(7,"testtime"),8); 513checkSuccess(cur.getFieldLength(7,"testdatetime"),19); 514checkSuccess(cur.getFieldLength(7,"testyear"),4); 515checkSuccess(cur.getFieldLength(7,"testchar"),5); 516checkSuccess(cur.getFieldLength(7,"testtext"),5); 517checkSuccess(cur.getFieldLength(7,"testvarchar"),8); 518checkSuccess(cur.getFieldLength(7,"testtinytext"),9); 519checkSuccess(cur.getFieldLength(7,"testmediumtext"),11); 520checkSuccess(cur.getFieldLength(7,"testlongtext"),9); 521console.log(); 522 523console.log("FIELDS BY ARRAY: "); 524fields=cur.getRow(0); 525checkSuccess(fields[0],"1"); 526checkSuccess(fields[1],"1"); 527checkSuccess(fields[2],"1"); 528checkSuccess(fields[3],"1"); 529checkSuccess(fields[4],"1"); 530//checkSuccess(fields[5],"1.1"); 531checkSuccess(fields[6],"1.1"); 532checkSuccess(fields[7],"1.1"); 533checkSuccess(fields[8],"2001-01-01"); 534checkSuccess(fields[9],"01:00:00"); 535checkSuccess(fields[10],"2001-01-01 01:00:00"); 536checkSuccess(fields[11],"2001"); 537checkSuccess(fields[12],"char1"); 538checkSuccess(fields[13],"text1"); 539checkSuccess(fields[14],"varchar1"); 540checkSuccess(fields[15],"tinytext1"); 541checkSuccess(fields[16],"mediumtext1"); 542checkSuccess(fields[17],"longtext1"); 543console.log("\n"); 544 545console.log("FIELD LENGTHS BY ARRAY: "); 546fieldlens=cur.getRowLengths(0); 547checkSuccess(fieldlens[0],1); 548checkSuccess(fieldlens[1],1); 549checkSuccess(fieldlens[2],1); 550checkSuccess(fieldlens[3],1); 551checkSuccess(fieldlens[4],1); 552//checkSuccess(fieldlens[5],3); 553checkSuccess(fieldlens[6],3); 554checkSuccess(fieldlens[7],3); 555checkSuccess(fieldlens[8],10); 556checkSuccess(fieldlens[9],8); 557checkSuccess(fieldlens[10],19); 558checkSuccess(fieldlens[11],4); 559checkSuccess(fieldlens[12],5); 560checkSuccess(fieldlens[13],5); 561checkSuccess(fieldlens[14],8); 562checkSuccess(fieldlens[15],9); 563checkSuccess(fieldlens[16],11); 564checkSuccess(fieldlens[17],9); 565console.log("\n"); 566 567console.log("INDIVIDUAL SUBSTITUTIONS: "); 568cur.prepareQuery("select $(var1),'$(var2)',$(var3)"); 569cur.substitution("var1",1); 570cur.substitution("var2","hello"); 571cur.substitution("var3",10.5556,6,4); 572checkSuccess(cur.executeQuery(),1); 573console.log("\n"); 574 575console.log("FIELDS: "); 576checkSuccess(cur.getField(0,0),"1"); 577checkSuccess(cur.getField(0,1),"hello"); 578checkSuccess(cur.getField(0,2),"10.5556"); 579console.log("\n"); 580 581console.log("ARRAY SUBSTITUTIONS: "); 582cur.prepareQuery("select $(var1),$(var2),$(var3)"); 583cur.substitutions(subvars,subvallongs); 584checkSuccess(cur.executeQuery(),1); 585console.log("\n"); 586 587console.log("FIELDS: "); 588checkSuccess(cur.getField(0,0),"1"); 589checkSuccess(cur.getField(0,1),"2"); 590checkSuccess(cur.getField(0,2),"3"); 591console.log("\n"); 592 593console.log("ARRAY SUBSTITUTIONS: "); 594cur.prepareQuery("select '$(var1)','$(var2)','$(var3)'"); 595cur.substitutions(subvars,subvalstrings); 596checkSuccess(cur.executeQuery(),1); 597console.log("\n"); 598 599console.log("FIELDS: "); 600checkSuccess(cur.getField(0,0),"hi"); 601checkSuccess(cur.getField(0,1),"hello"); 602checkSuccess(cur.getField(0,2),"bye"); 603console.log("\n"); 604 605console.log("ARRAY SUBSTITUTIONS: "); 606cur.prepareQuery("select $(var1),$(var2),$(var3)"); 607cur.substitutions(subvars,subvaldoubles,precs,scales); 608checkSuccess(cur.executeQuery(),1); 609console.log("\n"); 610 611console.log("FIELDS: "); 612checkSuccess(cur.getField(0,0),"10.55"); 613checkSuccess(cur.getField(0,1),"10.556"); 614checkSuccess(cur.getField(0,2),"10.5556"); 615console.log("\n"); 616 617console.log("nullS as Nulls: "); 618cur.getNullsAsNulls(); 619checkSuccess(cur.sendQuery("select null,1,null"),1); 620checkSuccess(cur.getField(0,0),null); 621checkSuccess(cur.getField(0,1),"1"); 622checkSuccess(cur.getField(0,2),null); 623cur.getNullsAsEmptyStrings(); 624checkSuccess(cur.sendQuery("select null,1,null"),1); 625checkSuccess(cur.getField(0,0),""); 626checkSuccess(cur.getField(0,1),"1"); 627checkSuccess(cur.getField(0,2),""); 628cur.getNullsAsNulls(); 629console.log("\n"); 630 631console.log("RESULT SET BUFFER SIZE: "); 632checkSuccess(cur.getResultSetBufferSize(),0); 633cur.setResultSetBufferSize(2); 634checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 635checkSuccess(cur.getResultSetBufferSize(),2); 636console.log(); 637checkSuccess(cur.firstRowIndex(),0); 638checkSuccess(cur.endOfResultSet(),0); 639checkSuccess(cur.rowCount(),2); 640checkSuccess(cur.getField(0,0),"1"); 641checkSuccess(cur.getField(1,0),"2"); 642checkSuccess(cur.getField(2,0),"3"); 643console.log(); 644checkSuccess(cur.firstRowIndex(),2); 645checkSuccess(cur.endOfResultSet(),0); 646checkSuccess(cur.rowCount(),4); 647checkSuccess(cur.getField(6,0),"7"); 648checkSuccess(cur.getField(7,0),"8"); 649console.log(); 650checkSuccess(cur.firstRowIndex(),6); 651checkSuccess(cur.endOfResultSet(),0); 652checkSuccess(cur.rowCount(),8); 653checkSuccess(cur.getField(8,0),null); 654console.log(); 655checkSuccess(cur.firstRowIndex(),8); 656checkSuccess(cur.endOfResultSet(),1); 657checkSuccess(cur.rowCount(),8); 658console.log("\n"); 659 660console.log("DONT GET COLUMN INFO: "); 661cur.dontGetColumnInfo(); 662checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 663checkSuccess(cur.getColumnName(0),null); 664checkSuccess(cur.getColumnLength(0),0); 665checkSuccess(cur.getColumnType(0),null); 666console.log(); 667cur.getColumnInfo(); 668checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 669checkSuccess(cur.getColumnName(0),"testtinyint"); 670checkSuccess(cur.getColumnLength(0),1); 671checkSuccess(cur.getColumnType(0),"TINYINT"); 672console.log("\n"); 673 674console.log("SUSPENDED SESSION: "); 675checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 676cur.suspendResultSet(); 677checkSuccess(con.suspendSession(),1); 678port=con.getConnectionPort(); 679socket=con.getConnectionSocket(); 680checkSuccess(con.resumeSession(port,socket),1); 681console.log(); 682checkSuccess(cur.getField(0,0),"1"); 683checkSuccess(cur.getField(1,0),"2"); 684checkSuccess(cur.getField(2,0),"3"); 685checkSuccess(cur.getField(3,0),"4"); 686checkSuccess(cur.getField(4,0),"5"); 687checkSuccess(cur.getField(5,0),"6"); 688checkSuccess(cur.getField(6,0),"7"); 689checkSuccess(cur.getField(7,0),"8"); 690console.log(); 691checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 692cur.suspendResultSet(); 693checkSuccess(con.suspendSession(),1); 694port=con.getConnectionPort(); 695socket=con.getConnectionSocket(); 696checkSuccess(con.resumeSession(port,socket),1); 697console.log(); 698checkSuccess(cur.getField(0,0),"1"); 699checkSuccess(cur.getField(1,0),"2"); 700checkSuccess(cur.getField(2,0),"3"); 701checkSuccess(cur.getField(3,0),"4"); 702checkSuccess(cur.getField(4,0),"5"); 703checkSuccess(cur.getField(5,0),"6"); 704checkSuccess(cur.getField(6,0),"7"); 705checkSuccess(cur.getField(7,0),"8"); 706console.log(); 707checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 708cur.suspendResultSet(); 709checkSuccess(con.suspendSession(),1); 710port=con.getConnectionPort(); 711socket=con.getConnectionSocket(); 712checkSuccess(con.resumeSession(port,socket),1); 713console.log(); 714checkSuccess(cur.getField(0,0),"1"); 715checkSuccess(cur.getField(1,0),"2"); 716checkSuccess(cur.getField(2,0),"3"); 717checkSuccess(cur.getField(3,0),"4"); 718checkSuccess(cur.getField(4,0),"5"); 719checkSuccess(cur.getField(5,0),"6"); 720checkSuccess(cur.getField(6,0),"7"); 721checkSuccess(cur.getField(7,0),"8"); 722console.log("\n"); 723 724console.log("SUSPENDED RESULT SET: "); 725cur.setResultSetBufferSize(2); 726checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 727checkSuccess(cur.getField(2,0),"3"); 728id=cur.getResultSetId(); 729cur.suspendResultSet(); 730checkSuccess(con.suspendSession(),1); 731port=con.getConnectionPort(); 732socket=con.getConnectionSocket(); 733checkSuccess(con.resumeSession(port,socket),1); 734checkSuccess(cur.resumeResultSet(id),1); 735console.log(); 736checkSuccess(cur.firstRowIndex(),4); 737checkSuccess(cur.endOfResultSet(),0); 738checkSuccess(cur.rowCount(),6); 739checkSuccess(cur.getField(7,0),"8"); 740console.log(); 741checkSuccess(cur.firstRowIndex(),6); 742checkSuccess(cur.endOfResultSet(),0); 743checkSuccess(cur.rowCount(),8); 744checkSuccess(cur.getField(8,0),null); 745console.log(); 746checkSuccess(cur.firstRowIndex(),8); 747checkSuccess(cur.endOfResultSet(),1); 748checkSuccess(cur.rowCount(),8); 749cur.setResultSetBufferSize(0); 750console.log("\n"); 751 752console.log("CACHED RESULT SET: "); 753cur.cacheToFile("cachefile1"); 754cur.setCacheTtl(200); 755checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 756filename=cur.getCacheFileName(); 757checkSuccess(filename,"cachefile1"); 758cur.cacheOff(); 759checkSuccess(cur.openCachedResultSet(filename),1); 760checkSuccess(cur.getField(7,0),"8"); 761console.log("\n"); 762 763console.log("COLUMN COUNT FOR CACHED RESULT SET: "); 764checkSuccess(cur.colCount(),19); 765console.log("\n"); 766 767console.log("COLUMN NAMES FOR CACHED RESULT SET: "); 768checkSuccess(cur.getColumnName(0),"testtinyint"); 769checkSuccess(cur.getColumnName(1),"testsmallint"); 770checkSuccess(cur.getColumnName(2),"testmediumint"); 771checkSuccess(cur.getColumnName(3),"testint"); 772checkSuccess(cur.getColumnName(4),"testbigint"); 773checkSuccess(cur.getColumnName(5),"testfloat"); 774checkSuccess(cur.getColumnName(6),"testreal"); 775checkSuccess(cur.getColumnName(7),"testdecimal"); 776checkSuccess(cur.getColumnName(8),"testdate"); 777checkSuccess(cur.getColumnName(9),"testtime"); 778checkSuccess(cur.getColumnName(10),"testdatetime"); 779checkSuccess(cur.getColumnName(11),"testyear"); 780checkSuccess(cur.getColumnName(12),"testchar"); 781checkSuccess(cur.getColumnName(13),"testtext"); 782checkSuccess(cur.getColumnName(14),"testvarchar"); 783checkSuccess(cur.getColumnName(15),"testtinytext"); 784checkSuccess(cur.getColumnName(16),"testmediumtext"); 785checkSuccess(cur.getColumnName(17),"testlongtext"); 786cols=cur.getColumnNames(); 787checkSuccess(cols[0],"testtinyint"); 788checkSuccess(cols[1],"testsmallint"); 789checkSuccess(cols[2],"testmediumint"); 790checkSuccess(cols[3],"testint"); 791checkSuccess(cols[4],"testbigint"); 792checkSuccess(cols[5],"testfloat"); 793checkSuccess(cols[6],"testreal"); 794checkSuccess(cols[7],"testdecimal"); 795checkSuccess(cols[8],"testdate"); 796checkSuccess(cols[9],"testtime"); 797checkSuccess(cols[10],"testdatetime"); 798checkSuccess(cols[11],"testyear"); 799checkSuccess(cols[12],"testchar"); 800checkSuccess(cols[13],"testtext"); 801checkSuccess(cols[14],"testvarchar"); 802checkSuccess(cols[15],"testtinytext"); 803checkSuccess(cols[16],"testmediumtext"); 804checkSuccess(cols[17],"testlongtext"); 805console.log("\n"); 806 807console.log("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: "); 808cur.setResultSetBufferSize(2); 809cur.cacheToFile("cachefile1"); 810cur.setCacheTtl(200); 811checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 812filename=cur.getCacheFileName(); 813checkSuccess(filename,"cachefile1"); 814cur.cacheOff(); 815checkSuccess(cur.openCachedResultSet(filename),1); 816checkSuccess(cur.getField(7,0),"8"); 817checkSuccess(cur.getField(8,0),null); 818cur.setResultSetBufferSize(0); 819console.log("\n"); 820 821console.log("FROM ONE CACHE FILE TO ANOTHER: "); 822cur.cacheToFile("cachefile2"); 823checkSuccess(cur.openCachedResultSet("cachefile1"),1); 824cur.cacheOff(); 825checkSuccess(cur.openCachedResultSet("cachefile2"),1); 826checkSuccess(cur.getField(7,0),"8"); 827checkSuccess(cur.getField(8,0),null); 828console.log("\n"); 829 830console.log("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: "); 831cur.setResultSetBufferSize(2); 832cur.cacheToFile("cachefile2"); 833checkSuccess(cur.openCachedResultSet("cachefile1"),1); 834cur.cacheOff(); 835checkSuccess(cur.openCachedResultSet("cachefile2"),1); 836checkSuccess(cur.getField(7,0),"8"); 837checkSuccess(cur.getField(8,0),null); 838cur.setResultSetBufferSize(0); 839console.log("\n"); 840 841console.log("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: "); 842cur.setResultSetBufferSize(2); 843cur.cacheToFile("cachefile1"); 844cur.setCacheTtl(200); 845checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),1); 846checkSuccess(cur.getField(2,0),"3"); 847filename=cur.getCacheFileName(); 848checkSuccess(filename,"cachefile1"); 849id=cur.getResultSetId(); 850cur.suspendResultSet(); 851checkSuccess(con.suspendSession(),1); 852port=con.getConnectionPort(); 853socket=con.getConnectionSocket(); 854console.log(); 855checkSuccess(con.resumeSession(port,socket),1); 856checkSuccess(cur.resumeCachedResultSet(id,filename),1); 857console.log(); 858checkSuccess(cur.firstRowIndex(),4); 859checkSuccess(cur.endOfResultSet(),0); 860checkSuccess(cur.rowCount(),6); 861checkSuccess(cur.getField(7,0),"8"); 862console.log(); 863checkSuccess(cur.firstRowIndex(),6); 864checkSuccess(cur.endOfResultSet(),0); 865checkSuccess(cur.rowCount(),8); 866checkSuccess(cur.getField(8,0),null); 867console.log(); 868checkSuccess(cur.firstRowIndex(),8); 869checkSuccess(cur.endOfResultSet(),1); 870checkSuccess(cur.rowCount(),8); 871cur.cacheOff(); 872console.log(); 873checkSuccess(cur.openCachedResultSet(filename),1); 874checkSuccess(cur.getField(7,0),"8"); 875checkSuccess(cur.getField(8,0),null); 876cur.setResultSetBufferSize(0); 877console.log("\n"); 878 879console.log("COMMIT AND ROLLBACK: "); 880// Note: Mysql's default isolation level is repeatable-read, 881// not read-committed like most other db's. Both sessions must 882// commit to see the changes that each other has made. 883var secondcon=new sqlrelay.SQLRConnection("sqlrelay",9000,"/tmp/test.socket","test","test",0,1); 884var secondcur=new sqlrelay.SQLRCursor(secondcon); 885checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1); 886if (majorversion>3) { 887 checkSuccess(secondcur.getField(0,0),"0"); 888} else { 889 checkSuccess(secondcur.getField(0,0),"8"); 890} 891checkSuccess(con.commit(),1); 892checkSuccess(secondcon.commit(),1); 893checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1); 894checkSuccess(secondcur.getField(0,0),"8"); 895checkSuccess(con.autoCommitOn(),1); 896checkSuccess(cur.sendQuery("insert into testdb.testtable values (10,10,10,10,10,10.1,10.1,1.1,'2010-01-01','10:00:00','2010-01-01 10:00:00','2010','char10','text10','varchar10','tinytext10','mediumtext10','longtext10',null)"),1); 897checkSuccess(secondcon.commit(),1); 898checkSuccess(secondcur.sendQuery("select count(*) from testtable"),1); 899checkSuccess(secondcur.getField(0,0),"9"); 900checkSuccess(con.autoCommitOff(),1); 901secondcon.commit(); 902console.log("\n"); 903 904console.log("FINISHED SUSPENDED SESSION: "); 905checkSuccess(cur.sendQuery("select * from testtable order by testint"),1); 906checkSuccess(cur.getField(4,0),"5"); 907checkSuccess(cur.getField(5,0),"6"); 908checkSuccess(cur.getField(6,0),"7"); 909checkSuccess(cur.getField(7,0),"8"); 910id=cur.getResultSetId(); 911cur.suspendResultSet(); 912checkSuccess(con.suspendSession(),1); 913port=con.getConnectionPort(); 914socket=con.getConnectionSocket(); 915checkSuccess(con.resumeSession(port,socket),1); 916checkSuccess(cur.resumeResultSet(id),1); 917checkSuccess(cur.getField(4,0),null); 918checkSuccess(cur.getField(5,0),null); 919checkSuccess(cur.getField(6,0),null); 920checkSuccess(cur.getField(7,0),null); 921console.log("\n"); 922 923// drop existing table 924cur.sendQuery("drop table testtable"); 925 926// invalid queries... 927console.log("INVALID QUERIES: "); 928checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),0); 929checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),0); 930checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),0); 931checkSuccess(cur.sendQuery("select * from testtable order by testtinyint"),0); 932console.log(); 933checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 934checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 935checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 936checkSuccess(cur.sendQuery("insert into testtable values (1,2,3,4)"),0); 937console.log(); 938checkSuccess(cur.sendQuery("create table testtable"),0); 939checkSuccess(cur.sendQuery("create table testtable"),0); 940checkSuccess(cur.sendQuery("create table testtable"),0); 941checkSuccess(cur.sendQuery("create table testtable"),0); 942console.log("\n"); 943 944process.exit(0); 945