1 // Copyright (c) 1999-2018 David Muse
2 // See the file COPYING for more information.
3
4 #include <sqlrelay/sqlrclient.h>
5 #include <rudiments/charstring.h>
6 #include <rudiments/process.h>
7 #include <rudiments/stdio.h>
8
9 sqlrconnection *con;
10 sqlrcursor *cur;
11 sqlrconnection *secondcon;
12 sqlrcursor *secondcur;
13
checkSuccess(const char * value,const char * success)14 void checkSuccess(const char *value, const char *success) {
15
16 if (!success) {
17 if (!value) {
18 stdoutput.printf("success ");
19 return;
20 } else {
21 stdoutput.printf("%s!=%s\n",value,success);
22 stdoutput.printf("failure ");
23 delete cur;
24 delete con;
25 process::exit(1);
26 }
27 }
28
29 if (!charstring::compare(value,success)) {
30 stdoutput.printf("success ");
31 } else {
32 stdoutput.printf("%s!=%s\n",value,success);
33 stdoutput.printf("failure ");
34 delete cur;
35 delete con;
36 process::exit(1);
37 }
38 }
39
checkSuccess(const char * value,const char * success,size_t length)40 void checkSuccess(const char *value, const char *success, size_t length) {
41
42 if (!success) {
43 if (!value) {
44 stdoutput.printf("success ");
45 return;
46 } else {
47 stdoutput.printf("%s!=%s\n",value,success);
48 stdoutput.printf("failure ");
49 delete cur;
50 delete con;
51 process::exit(1);
52 }
53 }
54
55 if (!charstring::compare(value,success,length)) {
56 stdoutput.printf("success ");
57 } else {
58 stdoutput.printf("%s!=%s\n",value,success);
59 stdoutput.printf("failure ");
60 delete cur;
61 delete con;
62 process::exit(1);
63 }
64 }
65
checkSuccess(int value,int success)66 void checkSuccess(int value, int success) {
67
68 if (value==success) {
69 stdoutput.printf("success ");
70 } else {
71 stdoutput.printf("%d!=%d\n",value,success);
72 stdoutput.printf("failure ");
73 delete cur;
74 delete con;
75 process::exit(1);
76 }
77 }
78
checkSuccess(double value,double success)79 void checkSuccess(double value, double success) {
80
81 if (value==success) {
82 stdoutput.printf("success ");
83 } else {
84 stdoutput.printf("%f!=%f\n",value,success);
85 stdoutput.printf("failure ");
86 delete cur;
87 delete con;
88 process::exit(1);
89 }
90 }
91
main(int argc,char ** argv)92 int main(int argc, char **argv) {
93
94 const char *bindvars[6]={"1","2","3","4","5",NULL};
95 const char *bindvals[5]={"4","testchar4","testvarchar4","01-JAN-2004","testlong4"};
96 const char *subvars[4]={"var1","var2","var3",NULL};
97 const char *subvalstrings[3]={"hi","hello","bye"};
98 int64_t subvallongs[3]={1,2,3};
99 double subvaldoubles[3]={10.55,10.556,10.5556};
100 uint32_t precs[3]={4,5,6};
101 uint32_t scales[3]={2,3,4};
102 int64_t numvar;
103 const char *clobvar;
104 uint32_t clobvarlength;
105 const char *blobvar;
106 uint32_t blobvarlength;
107 const char *stringvar;
108 double floatvar;
109 const char * const *cols;
110 const char * const *fields;
111 uint16_t port;
112 const char *socket;
113 uint16_t id;
114 const char *filename;
115 const char *arraybindvars[6]={"var1","var2","var3","var4","var5",NULL};
116 const char *arraybindvals[5]={"7","testchar7","testvarchar7","01-JAN-2007","testlong7"};
117 uint32_t *fieldlens;
118
119 const char *service=NULL;
120 #ifdef _WIN32
121 service="sqlrelay/fedora24x64.firstworks.com@AD.FIRSTWORKS.COM";
122 #endif
123
124 // instantiation
125 con=new sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
126 NULL,NULL,0,1);
127 cur=new sqlrcursor(con);
128 con->enableKerberos(service,NULL,NULL);
129
130 // get database type
131 stdoutput.printf("IDENTIFY: \n");
132 checkSuccess(con->identify(),"oracle");
133 stdoutput.printf("\n");
134
135 // ping
136 stdoutput.printf("PING: \n");
137 checkSuccess(con->ping(),1);
138 stdoutput.printf("\n");
139
140 // drop existing table
141 cur->sendQuery("drop table testtable");
142
143 stdoutput.printf("CREATE TEMPTABLE: \n");
144 checkSuccess(cur->sendQuery("create table testtable (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long, testclob clob, testblob blob)"),1);
145 stdoutput.printf("\n");
146
147 stdoutput.printf("INSERT: \n");
148 checkSuccess(cur->sendQuery("insert into testtable values (1,'testchar1','testvarchar1','01-JAN-2001','testlong1','testclob1',empty_blob())"),1);
149 checkSuccess(cur->countBindVariables(),0);
150 stdoutput.printf("\n");
151
152 stdoutput.printf("AFFECTED ROWS: \n");
153 checkSuccess(cur->affectedRows(),1);
154 stdoutput.printf("\n");
155
156 stdoutput.printf("BIND BY POSITION: \n");
157 cur->prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)");
158 checkSuccess(cur->countBindVariables(),7);
159 cur->inputBind("1",2);
160 cur->inputBind("2","testchar2");
161 cur->inputBind("3","testvarchar2");
162 cur->inputBind("4",2002,1,1,0,0,0,0,NULL,false);
163 cur->inputBind("5","testlong2");
164 cur->inputBindClob("6","testclob2",9);
165 cur->inputBindBlob("7","testblob2",9);
166 checkSuccess(cur->executeQuery(),1);
167 cur->clearBinds();
168 cur->inputBind("1",3);
169 cur->inputBind("2","testchar3");
170 cur->inputBind("3","testvarchar3");
171 cur->inputBind("4",2003,1,1,0,0,0,0,NULL,false);
172 cur->inputBind("5","testlong3");
173 cur->inputBindClob("6","testclob3",9);
174 cur->inputBindBlob("7","testblob3",9);
175 checkSuccess(cur->executeQuery(),1);
176 stdoutput.printf("\n");
177
178 stdoutput.printf("ARRAY OF BINDS BY POSITION: \n");
179 cur->clearBinds();
180 cur->inputBinds(bindvars,bindvals);
181 cur->inputBindClob("6","testclob4",9);
182 cur->inputBindBlob("7","testblob4",9);
183 checkSuccess(cur->executeQuery(),1);
184 stdoutput.printf("\n");
185
186 stdoutput.printf("BIND BY NAME: \n");
187 cur->prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4,:var5,:var6,:var7)");
188 cur->inputBind("var1",5);
189 cur->inputBind("var2","testchar5");
190 cur->inputBind("var3","testvarchar5");
191 cur->inputBind("var4",2005,1,1,0,0,0,0,NULL,false);
192 cur->inputBind("var5","testlong5");
193 cur->inputBindClob("var6","testclob5",9);
194 cur->inputBindBlob("var7","testblob5",9);
195 checkSuccess(cur->executeQuery(),1);
196 cur->clearBinds();
197 cur->inputBind("var1",6);
198 cur->inputBind("var2","testchar6");
199 cur->inputBind("var3","testvarchar6");
200 cur->inputBind("var4",2006,1,1,0,0,0,0,NULL,false);
201 cur->inputBind("var5","testlong6");
202 cur->inputBindClob("var6","testclob6",9);
203 cur->inputBindBlob("var7","testblob6",9);
204 checkSuccess(cur->executeQuery(),1);
205 stdoutput.printf("\n");
206
207 stdoutput.printf("ARRAY OF BINDS BY NAME: \n");
208 cur->clearBinds();
209 cur->inputBinds(arraybindvars,arraybindvals);
210 cur->inputBindClob("var6","testclob7",9);
211 cur->inputBindBlob("var7","testblob7",9);
212 checkSuccess(cur->executeQuery(),1);
213 stdoutput.printf("\n");
214
215 stdoutput.printf("BIND BY NAME WITH VALIDATION: \n");
216 cur->clearBinds();
217 cur->inputBind("var1",8);
218 cur->inputBind("var2","testchar8");
219 cur->inputBind("var3","testvarchar8");
220 cur->inputBind("var4",2008,1,1,0,0,0,0,NULL,false);
221 cur->inputBind("var5","testlong8");
222 cur->inputBindClob("var6","testclob8",9);
223 cur->inputBindBlob("var7","testblob8",9);
224 cur->inputBind("var9","junkvalue");
225 cur->validateBinds();
226 checkSuccess(cur->executeQuery(),1);
227 stdoutput.printf("\n");
228
229 stdoutput.printf("OUTPUT BIND BY NAME: \n");
230 cur->prepareQuery("begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; :datevar:='03-FEB-2001'; end;");
231 cur->defineOutputBindInteger("numvar");
232 cur->defineOutputBindString("stringvar",10);
233 cur->defineOutputBindDouble("floatvar");
234 cur->defineOutputBindDate("datevar");
235 checkSuccess(cur->executeQuery(),1);
236 numvar=cur->getOutputBindInteger("numvar");
237 stringvar=cur->getOutputBindString("stringvar");
238 floatvar=cur->getOutputBindDouble("floatvar");
239 int16_t year=0;
240 int16_t month=0;
241 int16_t day=0;
242 int16_t hour=0;
243 int16_t minute=0;
244 int16_t second=0;
245 int32_t microsecond=0;
246 const char *tz=NULL;
247 bool isnegative=false;
248 cur->getOutputBindDate("datevar",&year,&month,&day,
249 &hour,&minute,&second,µsecond,&tz,
250 &isnegative);
251 checkSuccess(numvar,1);
252 checkSuccess(stringvar,"hello");
253 checkSuccess(floatvar,2.5);
254 checkSuccess(year,2001);
255 checkSuccess(month,2);
256 checkSuccess(day,3);
257 checkSuccess(hour,0);
258 checkSuccess(minute,0);
259 checkSuccess(second,0);
260 checkSuccess(microsecond,0);
261 checkSuccess(tz,"");
262 stdoutput.printf("\n");
263
264 stdoutput.printf("OUTPUT BIND BY POSITION: \n");
265 cur->clearBinds();
266 cur->defineOutputBindInteger("1");
267 cur->defineOutputBindString("2",10);
268 cur->defineOutputBindDouble("3");
269 cur->defineOutputBindDate("4");
270 checkSuccess(cur->executeQuery(),1);
271 numvar=cur->getOutputBindInteger("1");
272 stringvar=cur->getOutputBindString("2");
273 floatvar=cur->getOutputBindDouble("3");
274 cur->getOutputBindDate("4",&year,&month,&day,
275 &hour,&minute,&second,µsecond,&tz,
276 &isnegative);
277 checkSuccess(numvar,1);
278 checkSuccess(stringvar,"hello");
279 checkSuccess(floatvar,2.5);
280 checkSuccess(year,2001);
281 checkSuccess(month,2);
282 checkSuccess(day,3);
283 checkSuccess(hour,0);
284 checkSuccess(minute,0);
285 checkSuccess(second,0);
286 checkSuccess(microsecond,0);
287 checkSuccess(tz,"");
288 stdoutput.printf("\n");
289
290 stdoutput.printf("OUTPUT BIND BY NAME WITH VALIDATION: \n");
291 cur->clearBinds();
292 cur->defineOutputBindInteger("numvar");
293 cur->defineOutputBindString("stringvar",10);
294 cur->defineOutputBindDouble("floatvar");
295 cur->defineOutputBindDate("datevar");
296 cur->defineOutputBindString("dummyvar",10);
297 cur->validateBinds();
298 checkSuccess(cur->executeQuery(),1);
299 numvar=cur->getOutputBindInteger("numvar");
300 stringvar=cur->getOutputBindString("stringvar");
301 floatvar=cur->getOutputBindDouble("floatvar");
302 cur->getOutputBindDate("datevar",&year,&month,&day,
303 &hour,&minute,&second,µsecond,&tz,
304 &isnegative);
305 checkSuccess(numvar,1);
306 checkSuccess(stringvar,"hello");
307 checkSuccess(floatvar,2.5);
308 checkSuccess(year,2001);
309 checkSuccess(month,2);
310 checkSuccess(day,3);
311 checkSuccess(hour,0);
312 checkSuccess(minute,0);
313 checkSuccess(second,0);
314 checkSuccess(microsecond,0);
315 checkSuccess(tz,"");
316 stdoutput.printf("\n");
317
318 stdoutput.printf("SELECT: \n");
319 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
320 stdoutput.printf("\n");
321
322 stdoutput.printf("COLUMN COUNT: \n");
323 checkSuccess(cur->colCount(),7);
324 stdoutput.printf("\n");
325
326 stdoutput.printf("COLUMN NAMES: \n");
327 checkSuccess(cur->getColumnName(0),"TESTNUMBER");
328 checkSuccess(cur->getColumnName(1),"TESTCHAR");
329 checkSuccess(cur->getColumnName(2),"TESTVARCHAR");
330 checkSuccess(cur->getColumnName(3),"TESTDATE");
331 checkSuccess(cur->getColumnName(4),"TESTLONG");
332 checkSuccess(cur->getColumnName(5),"TESTCLOB");
333 checkSuccess(cur->getColumnName(6),"TESTBLOB");
334 cols=cur->getColumnNames();
335 checkSuccess(cols[0],"TESTNUMBER");
336 checkSuccess(cols[1],"TESTCHAR");
337 checkSuccess(cols[2],"TESTVARCHAR");
338 checkSuccess(cols[3],"TESTDATE");
339 checkSuccess(cols[4],"TESTLONG");
340 checkSuccess(cols[5],"TESTCLOB");
341 checkSuccess(cols[6],"TESTBLOB");
342 stdoutput.printf("\n");
343
344 stdoutput.printf("COLUMN TYPES: \n");
345 checkSuccess(cur->getColumnType((uint32_t)0),"NUMBER");
346 checkSuccess(cur->getColumnType("TESTNUMBER"),"NUMBER");
347 checkSuccess(cur->getColumnType(1),"CHAR");
348 checkSuccess(cur->getColumnType("TESTCHAR"),"CHAR");
349 checkSuccess(cur->getColumnType(2),"VARCHAR2");
350 checkSuccess(cur->getColumnType("TESTVARCHAR"),"VARCHAR2");
351 checkSuccess(cur->getColumnType(3),"DATE");
352 checkSuccess(cur->getColumnType("TESTDATE"),"DATE");
353 checkSuccess(cur->getColumnType(4),"LONG");
354 checkSuccess(cur->getColumnType("TESTLONG"),"LONG");
355 checkSuccess(cur->getColumnType(5),"CLOB");
356 checkSuccess(cur->getColumnType("TESTCLOB"),"CLOB");
357 checkSuccess(cur->getColumnType(6),"BLOB");
358 checkSuccess(cur->getColumnType("TESTBLOB"),"BLOB");
359 stdoutput.printf("\n");
360
361 stdoutput.printf("COLUMN LENGTH: \n");
362 checkSuccess(cur->getColumnLength((uint32_t)0),22);
363 checkSuccess(cur->getColumnLength("TESTNUMBER"),22);
364 checkSuccess(cur->getColumnLength(1),40);
365 checkSuccess(cur->getColumnLength("TESTCHAR"),40);
366 checkSuccess(cur->getColumnLength(2),40);
367 checkSuccess(cur->getColumnLength("TESTVARCHAR"),40);
368 checkSuccess(cur->getColumnLength(3),7);
369 checkSuccess(cur->getColumnLength("TESTDATE"),7);
370 checkSuccess(cur->getColumnLength(4),0);
371 checkSuccess(cur->getColumnLength("TESTLONG"),0);
372 checkSuccess(cur->getColumnLength(5),0);
373 checkSuccess(cur->getColumnLength("TESTCLOB"),0);
374 checkSuccess(cur->getColumnLength(6),0);
375 checkSuccess(cur->getColumnLength("TESTBLOB"),0);
376 stdoutput.printf("\n");
377
378 stdoutput.printf("LONGEST COLUMN: \n");
379 checkSuccess(cur->getLongest((uint32_t)0),1);
380 checkSuccess(cur->getLongest("TESTNUMBER"),1);
381 checkSuccess(cur->getLongest(1),40);
382 checkSuccess(cur->getLongest("TESTCHAR"),40);
383 checkSuccess(cur->getLongest(2),12);
384 checkSuccess(cur->getLongest("TESTVARCHAR"),12);
385 checkSuccess(cur->getLongest(3),9);
386 checkSuccess(cur->getLongest("TESTDATE"),9);
387 checkSuccess(cur->getLongest(4),9);
388 checkSuccess(cur->getLongest("TESTLONG"),9);
389 checkSuccess(cur->getLongest(5),9);
390 checkSuccess(cur->getLongest("TESTCLOB"),9);
391 checkSuccess(cur->getLongest(6),9);
392 checkSuccess(cur->getLongest("TESTBLOB"),9);
393 stdoutput.printf("\n");
394
395 stdoutput.printf("ROW COUNT: \n");
396 checkSuccess(cur->rowCount(),8);
397 stdoutput.printf("\n");
398
399 stdoutput.printf("TOTAL ROWS: \n");
400 checkSuccess(cur->totalRows(),0);
401 stdoutput.printf("\n");
402
403 stdoutput.printf("FIRST ROW INDEX: \n");
404 checkSuccess(cur->firstRowIndex(),0);
405 stdoutput.printf("\n");
406
407 stdoutput.printf("END OF RESULT SET: \n");
408 checkSuccess(cur->endOfResultSet(),1);
409 stdoutput.printf("\n");
410
411 stdoutput.printf("FIELDS BY INDEX: \n");
412 checkSuccess(cur->getField(0,(uint32_t)0),"1");
413 checkSuccess(cur->getField(0,1),"testchar1 ");
414 checkSuccess(cur->getField(0,2),"testvarchar1");
415 checkSuccess(cur->getField(0,3),"01-JAN-01");
416 checkSuccess(cur->getField(0,4),"testlong1");
417 checkSuccess(cur->getField(0,5),"testclob1");
418 checkSuccess(cur->getField(0,6),"");
419 stdoutput.printf("\n");
420 checkSuccess(cur->getField(7,(uint32_t)0),"8");
421 checkSuccess(cur->getField(7,1),"testchar8 ");
422 checkSuccess(cur->getField(7,2),"testvarchar8");
423 checkSuccess(cur->getField(7,3),"01-JAN-08");
424 checkSuccess(cur->getField(7,4),"testlong8");
425 checkSuccess(cur->getField(7,5),"testclob8");
426 checkSuccess(cur->getField(7,6),"testblob8");
427 stdoutput.printf("\n");
428
429 stdoutput.printf("FIELD LENGTHS BY INDEX: \n");
430 checkSuccess(cur->getFieldLength(0,(uint32_t)0),1);
431 checkSuccess(cur->getFieldLength(0,1),40);
432 checkSuccess(cur->getFieldLength(0,2),12);
433 checkSuccess(cur->getFieldLength(0,3),9);
434 checkSuccess(cur->getFieldLength(0,4),9);
435 checkSuccess(cur->getFieldLength(0,5),9);
436 checkSuccess(cur->getFieldLength(0,6),0);
437 stdoutput.printf("\n");
438 checkSuccess(cur->getFieldLength(7,(uint32_t)0),1);
439 checkSuccess(cur->getFieldLength(7,1),40);
440 checkSuccess(cur->getFieldLength(7,2),12);
441 checkSuccess(cur->getFieldLength(7,3),9);
442 checkSuccess(cur->getFieldLength(7,4),9);
443 checkSuccess(cur->getFieldLength(7,5),9);
444 checkSuccess(cur->getFieldLength(7,6),9);
445 stdoutput.printf("\n");
446
447 stdoutput.printf("FIELDS BY NAME: \n");
448 checkSuccess(cur->getField(0,"TESTNUMBER"),"1");
449 checkSuccess(cur->getField(0,"TESTCHAR"),"testchar1 ");
450 checkSuccess(cur->getField(0,"TESTVARCHAR"),"testvarchar1");
451 checkSuccess(cur->getField(0,"TESTDATE"),"01-JAN-01");
452 checkSuccess(cur->getField(0,"TESTLONG"),"testlong1");
453 checkSuccess(cur->getField(0,"TESTCLOB"),"testclob1");
454 checkSuccess(cur->getField(0,"TESTBLOB"),"");
455 stdoutput.printf("\n");
456 checkSuccess(cur->getField(7,"TESTNUMBER"),"8");
457 checkSuccess(cur->getField(7,"TESTCHAR"),"testchar8 ");
458 checkSuccess(cur->getField(7,"TESTVARCHAR"),"testvarchar8");
459 checkSuccess(cur->getField(7,"TESTDATE"),"01-JAN-08");
460 checkSuccess(cur->getField(7,"TESTLONG"),"testlong8");
461 checkSuccess(cur->getField(7,"TESTCLOB"),"testclob8");
462 checkSuccess(cur->getField(7,"TESTBLOB"),"testblob8");
463 stdoutput.printf("\n");
464
465 stdoutput.printf("FIELD LENGTHS BY NAME: \n");
466 checkSuccess(cur->getFieldLength(0,"TESTNUMBER"),1);
467 checkSuccess(cur->getFieldLength(0,"TESTCHAR"),40);
468 checkSuccess(cur->getFieldLength(0,"TESTVARCHAR"),12);
469 checkSuccess(cur->getFieldLength(0,"TESTDATE"),9);
470 checkSuccess(cur->getFieldLength(0,"TESTLONG"),9);
471 checkSuccess(cur->getFieldLength(0,"TESTCLOB"),9);
472 checkSuccess(cur->getFieldLength(0,"TESTBLOB"),0);
473 stdoutput.printf("\n");
474 checkSuccess(cur->getFieldLength(7,"TESTNUMBER"),1);
475 checkSuccess(cur->getFieldLength(7,"TESTCHAR"),40);
476 checkSuccess(cur->getFieldLength(7,"TESTVARCHAR"),12);
477 checkSuccess(cur->getFieldLength(7,"TESTDATE"),9);
478 checkSuccess(cur->getFieldLength(7,"TESTLONG"),9);
479 checkSuccess(cur->getFieldLength(7,"TESTCLOB"),9);
480 checkSuccess(cur->getFieldLength(7,"TESTBLOB"),9);
481 stdoutput.printf("\n");
482
483 stdoutput.printf("FIELDS BY ARRAY: \n");
484 fields=cur->getRow(0);
485 checkSuccess(fields[0],"1");
486 checkSuccess(fields[1],"testchar1 ");
487 checkSuccess(fields[2],"testvarchar1");
488 checkSuccess(fields[3],"01-JAN-01");
489 checkSuccess(fields[4],"testlong1");
490 checkSuccess(fields[5],"testclob1");
491 checkSuccess(fields[6],"");
492 stdoutput.printf("\n");
493
494 stdoutput.printf("FIELD LENGTHS BY ARRAY: \n");
495 fieldlens=cur->getRowLengths(0);
496 checkSuccess(fieldlens[0],1);
497 checkSuccess(fieldlens[1],40);
498 checkSuccess(fieldlens[2],12);
499 checkSuccess(fieldlens[3],9);
500 checkSuccess(fieldlens[4],9);
501 checkSuccess(fieldlens[5],9);
502 checkSuccess(fieldlens[6],0);
503 stdoutput.printf("\n");
504
505 stdoutput.printf("INDIVIDUAL SUBSTITUTIONS: \n");
506 cur->prepareQuery("select $(var1),'$(var2)',$(var3) from dual");
507 cur->substitution("var1",1);
508 cur->substitution("var2","hello");
509 cur->substitution("var3",10.5556,6,4);
510 checkSuccess(cur->executeQuery(),1);
511 stdoutput.printf("\n");
512
513 stdoutput.printf("FIELDS: \n");
514 checkSuccess(cur->getField(0,(uint32_t)0),"1");
515 checkSuccess(cur->getField(0,1),"hello");
516 checkSuccess(cur->getField(0,2),"10.5556");
517 stdoutput.printf("\n");
518
519 stdoutput.printf("OUTPUT BIND: \n");
520 cur->prepareQuery("begin :var1:='hello'; end;");
521 cur->defineOutputBindString("var1",10);
522 checkSuccess(cur->executeQuery(),1);
523 checkSuccess(cur->getOutputBindString("var1"),"hello");
524 stdoutput.printf("\n");
525
526 stdoutput.printf("ARRAY SUBSTITUTIONS: \n");
527 cur->prepareQuery("select $(var1),$(var2),$(var3) from dual");
528 cur->substitutions(subvars,subvallongs);
529 checkSuccess(cur->executeQuery(),1);
530 stdoutput.printf("\n");
531
532 stdoutput.printf("FIELDS: \n");
533 checkSuccess(cur->getField(0,(uint32_t)0),"1");
534 checkSuccess(cur->getField(0,1),"2");
535 checkSuccess(cur->getField(0,2),"3");
536 stdoutput.printf("\n");
537
538 stdoutput.printf("ARRAY SUBSTITUTIONS: \n");
539 cur->prepareQuery("select '$(var1)','$(var2)','$(var3)' from dual");
540 cur->substitutions(subvars,subvalstrings);
541 checkSuccess(cur->executeQuery(),1);
542 stdoutput.printf("\n");
543
544 stdoutput.printf("FIELDS: \n");
545 checkSuccess(cur->getField(0,(uint32_t)0),"hi");
546 checkSuccess(cur->getField(0,1),"hello");
547 checkSuccess(cur->getField(0,2),"bye");
548 stdoutput.printf("\n");
549
550 stdoutput.printf("ARRAY SUBSTITUTIONS: \n");
551 cur->prepareQuery("select $(var1),$(var2),$(var3) from dual");
552 cur->substitutions(subvars,subvaldoubles,precs,scales);
553 checkSuccess(cur->executeQuery(),1);
554 stdoutput.printf("\n");
555
556 stdoutput.printf("FIELDS: \n");
557 checkSuccess(cur->getField(0,(uint32_t)0),"10.55");
558 checkSuccess(cur->getField(0,1),"10.556");
559 checkSuccess(cur->getField(0,2),"10.5556");
560 stdoutput.printf("\n");
561
562 stdoutput.printf("NULLS as Nulls: \n");
563 cur->getNullsAsNulls();
564 checkSuccess(cur->sendQuery("select NULL,1,NULL from dual"),1);
565 checkSuccess(cur->getField(0,(uint32_t)0),NULL);
566 checkSuccess(cur->getField(0,1),"1");
567 checkSuccess(cur->getField(0,2),NULL);
568 cur->getNullsAsEmptyStrings();
569 checkSuccess(cur->sendQuery("select NULL,1,NULL from dual"),1);
570 checkSuccess(cur->getField(0,(uint32_t)0),"");
571 checkSuccess(cur->getField(0,1),"1");
572 checkSuccess(cur->getField(0,2),"");
573 cur->getNullsAsNulls();
574 stdoutput.printf("\n");
575
576 stdoutput.printf("RESULT SET BUFFER SIZE: \n");
577 checkSuccess(cur->getResultSetBufferSize(),0);
578 cur->setResultSetBufferSize(2);
579 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
580 checkSuccess(cur->getResultSetBufferSize(),2);
581 stdoutput.printf("\n");
582 checkSuccess(cur->firstRowIndex(),0);
583 checkSuccess(cur->endOfResultSet(),0);
584 checkSuccess(cur->rowCount(),2);
585 checkSuccess(cur->getField(0,(uint32_t)0),"1");
586 checkSuccess(cur->getField(1,(uint32_t)0),"2");
587 checkSuccess(cur->getField(2,(uint32_t)0),"3");
588 checkSuccess(cur->firstRowIndex(),2);
589 checkSuccess(cur->endOfResultSet(),0);
590 checkSuccess(cur->rowCount(),4);
591 checkSuccess(cur->getField(6,(uint32_t)0),"7");
592 checkSuccess(cur->getField(7,(uint32_t)0),"8");
593 stdoutput.printf("\n");
594 checkSuccess(cur->firstRowIndex(),6);
595 checkSuccess(cur->endOfResultSet(),0);
596 checkSuccess(cur->rowCount(),8);
597 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
598 stdoutput.printf("\n");
599 checkSuccess(cur->firstRowIndex(),8);
600 checkSuccess(cur->endOfResultSet(),1);
601 checkSuccess(cur->rowCount(),8);
602 stdoutput.printf("\n");
603
604 stdoutput.printf("DONT GET COLUMN INFO: \n");
605 cur->dontGetColumnInfo();
606 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
607 checkSuccess(cur->getColumnName(0),NULL);
608 checkSuccess(cur->getColumnLength((uint32_t)0),0);
609 checkSuccess(cur->getColumnType((uint32_t)0),NULL);
610 cur->getColumnInfo();
611 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
612 checkSuccess(cur->getColumnName(0),"TESTNUMBER");
613 checkSuccess(cur->getColumnLength((uint32_t)0),22);
614 checkSuccess(cur->getColumnType((uint32_t)0),"NUMBER");
615 stdoutput.printf("\n");
616
617 stdoutput.printf("SUSPENDED SESSION: \n");
618 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
619 cur->suspendResultSet();
620 checkSuccess(con->suspendSession(),1);
621 port=con->getConnectionPort();
622 socket=charstring::duplicate(con->getConnectionSocket());
623 checkSuccess(con->resumeSession(port,socket),1);
624 delete[] socket;
625 stdoutput.printf("\n");
626 checkSuccess(cur->getField(0,(uint32_t)0),"1");
627 checkSuccess(cur->getField(1,(uint32_t)0),"2");
628 checkSuccess(cur->getField(2,(uint32_t)0),"3");
629 checkSuccess(cur->getField(3,(uint32_t)0),"4");
630 checkSuccess(cur->getField(4,(uint32_t)0),"5");
631 checkSuccess(cur->getField(5,(uint32_t)0),"6");
632 checkSuccess(cur->getField(6,(uint32_t)0),"7");
633 checkSuccess(cur->getField(7,(uint32_t)0),"8");
634 stdoutput.printf("\n");
635 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
636 cur->suspendResultSet();
637 checkSuccess(con->suspendSession(),1);
638 port=con->getConnectionPort();
639 socket=charstring::duplicate(con->getConnectionSocket());
640 checkSuccess(con->resumeSession(port,socket),1);
641 delete[] socket;
642 stdoutput.printf("\n");
643 checkSuccess(cur->getField(0,(uint32_t)0),"1");
644 checkSuccess(cur->getField(1,(uint32_t)0),"2");
645 checkSuccess(cur->getField(2,(uint32_t)0),"3");
646 checkSuccess(cur->getField(3,(uint32_t)0),"4");
647 checkSuccess(cur->getField(4,(uint32_t)0),"5");
648 checkSuccess(cur->getField(5,(uint32_t)0),"6");
649 checkSuccess(cur->getField(6,(uint32_t)0),"7");
650 checkSuccess(cur->getField(7,(uint32_t)0),"8");
651 stdoutput.printf("\n");
652 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
653 cur->suspendResultSet();
654 checkSuccess(con->suspendSession(),1);
655 port=con->getConnectionPort();
656 socket=charstring::duplicate(con->getConnectionSocket());
657 checkSuccess(con->resumeSession(port,socket),1);
658 delete[] socket;
659 stdoutput.printf("\n");
660 checkSuccess(cur->getField(0,(uint32_t)0),"1");
661 checkSuccess(cur->getField(1,(uint32_t)0),"2");
662 checkSuccess(cur->getField(2,(uint32_t)0),"3");
663 checkSuccess(cur->getField(3,(uint32_t)0),"4");
664 checkSuccess(cur->getField(4,(uint32_t)0),"5");
665 checkSuccess(cur->getField(5,(uint32_t)0),"6");
666 checkSuccess(cur->getField(6,(uint32_t)0),"7");
667 checkSuccess(cur->getField(7,(uint32_t)0),"8");
668 stdoutput.printf("\n");
669
670 stdoutput.printf("SUSPENDED RESULT SET: \n");
671 cur->setResultSetBufferSize(2);
672 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
673 checkSuccess(cur->getField(2,(uint32_t)0),"3");
674 id=cur->getResultSetId();
675 cur->suspendResultSet();
676 checkSuccess(con->suspendSession(),1);
677 port=con->getConnectionPort();
678 socket=charstring::duplicate(con->getConnectionSocket());
679 checkSuccess(con->resumeSession(port,socket),1);
680 checkSuccess(cur->resumeResultSet(id),1);
681 delete[] socket;
682 stdoutput.printf("\n");
683 checkSuccess(cur->firstRowIndex(),4);
684 checkSuccess(cur->endOfResultSet(),0);
685 checkSuccess(cur->rowCount(),6);
686 checkSuccess(cur->getField(7,(uint32_t)0),"8");
687 stdoutput.printf("\n");
688 checkSuccess(cur->firstRowIndex(),6);
689 checkSuccess(cur->endOfResultSet(),0);
690 checkSuccess(cur->rowCount(),8);
691 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
692 stdoutput.printf("\n");
693 checkSuccess(cur->firstRowIndex(),8);
694 checkSuccess(cur->endOfResultSet(),1);
695 checkSuccess(cur->rowCount(),8);
696 cur->setResultSetBufferSize(0);
697 stdoutput.printf("\n");
698
699 stdoutput.printf("CACHED RESULT SET: \n");
700 cur->cacheToFile("cachefile1");
701 cur->setCacheTtl(200);
702 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
703 filename=charstring::duplicate(cur->getCacheFileName());
704 checkSuccess(filename,"cachefile1");
705 cur->cacheOff();
706 checkSuccess(cur->openCachedResultSet(filename),1);
707 checkSuccess(cur->getField(7,(uint32_t)0),"8");
708 delete[] filename;
709 stdoutput.printf("\n");
710
711 stdoutput.printf("COLUMN COUNT FOR CACHED RESULT SET: \n");
712 checkSuccess(cur->colCount(),7);
713 stdoutput.printf("\n");
714
715 stdoutput.printf("COLUMN NAMES FOR CACHED RESULT SET: \n");
716 checkSuccess(cur->getColumnName(0),"TESTNUMBER");
717 checkSuccess(cur->getColumnName(1),"TESTCHAR");
718 checkSuccess(cur->getColumnName(2),"TESTVARCHAR");
719 checkSuccess(cur->getColumnName(3),"TESTDATE");
720 checkSuccess(cur->getColumnName(4),"TESTLONG");
721 checkSuccess(cur->getColumnName(5),"TESTCLOB");
722 checkSuccess(cur->getColumnName(6),"TESTBLOB");
723 cols=cur->getColumnNames();
724 checkSuccess(cols[0],"TESTNUMBER");
725 checkSuccess(cols[1],"TESTCHAR");
726 checkSuccess(cols[2],"TESTVARCHAR");
727 checkSuccess(cols[3],"TESTDATE");
728 checkSuccess(cols[4],"TESTLONG");
729 checkSuccess(cols[5],"TESTCLOB");
730 checkSuccess(cols[6],"TESTBLOB");
731 stdoutput.printf("\n");
732
733 stdoutput.printf("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: \n");
734 cur->setResultSetBufferSize(2);
735 cur->cacheToFile("cachefile1");
736 cur->setCacheTtl(200);
737 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
738 filename=charstring::duplicate(cur->getCacheFileName());
739 checkSuccess(filename,"cachefile1");
740 cur->cacheOff();
741 checkSuccess(cur->openCachedResultSet(filename),1);
742 checkSuccess(cur->getField(7,(uint32_t)0),"8");
743 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
744 cur->setResultSetBufferSize(0);
745 delete[] filename;
746 stdoutput.printf("\n");
747
748 stdoutput.printf("FROM ONE CACHE FILE TO ANOTHER: \n");
749 cur->cacheToFile("cachefile2");
750 checkSuccess(cur->openCachedResultSet("cachefile1"),1);
751 cur->cacheOff();
752 checkSuccess(cur->openCachedResultSet("cachefile2"),1);
753 checkSuccess(cur->getField(7,(uint32_t)0),"8");
754 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
755 stdoutput.printf("\n");
756
757 stdoutput.printf("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: \n");
758 cur->setResultSetBufferSize(2);
759 cur->cacheToFile("cachefile2");
760 checkSuccess(cur->openCachedResultSet("cachefile1"),1);
761 cur->cacheOff();
762 checkSuccess(cur->openCachedResultSet("cachefile2"),1);
763 checkSuccess(cur->getField(7,(uint32_t)0),"8");
764 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
765 cur->setResultSetBufferSize(0);
766 stdoutput.printf("\n");
767
768 stdoutput.printf("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: \n");
769 cur->setResultSetBufferSize(2);
770 cur->cacheToFile("cachefile1");
771 cur->setCacheTtl(200);
772 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
773 checkSuccess(cur->getField(2,(uint32_t)0),"3");
774 filename=charstring::duplicate(cur->getCacheFileName());
775 checkSuccess(filename,"cachefile1");
776 id=cur->getResultSetId();
777 cur->suspendResultSet();
778 checkSuccess(con->suspendSession(),1);
779 port=con->getConnectionPort();
780 socket=charstring::duplicate(con->getConnectionSocket());
781 stdoutput.printf("\n");
782 checkSuccess(con->resumeSession(port,socket),1);
783 checkSuccess(cur->resumeCachedResultSet(id,filename),1);
784 delete[] socket;
785 stdoutput.printf("\n");
786 checkSuccess(cur->firstRowIndex(),4);
787 checkSuccess(cur->endOfResultSet(),0);
788 checkSuccess(cur->rowCount(),6);
789 checkSuccess(cur->getField(7,(uint32_t)0),"8");
790 stdoutput.printf("\n");
791 checkSuccess(cur->firstRowIndex(),6);
792 checkSuccess(cur->endOfResultSet(),0);
793 checkSuccess(cur->rowCount(),8);
794 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
795 stdoutput.printf("\n");
796 checkSuccess(cur->firstRowIndex(),8);
797 checkSuccess(cur->endOfResultSet(),1);
798 checkSuccess(cur->rowCount(),8);
799 cur->cacheOff();
800 stdoutput.printf("\n");
801 checkSuccess(cur->openCachedResultSet(filename),1);
802 checkSuccess(cur->getField(7,(uint32_t)0),"8");
803 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
804 cur->setResultSetBufferSize(0);
805 delete[] filename;
806 stdoutput.printf("\n");
807
808 stdoutput.printf("COMMIT AND ROLLBACK: \n");
809 secondcon=new sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
810 NULL,NULL,0,1);
811 secondcur=new sqlrcursor(secondcon);
812 secondcon->enableKerberos(service,NULL,NULL);
813 checkSuccess(secondcur->sendQuery("select count(*) from testtable"),1);
814 checkSuccess(secondcur->getField(0,(uint32_t)0),"0");
815 checkSuccess(con->commit(),1);
816 checkSuccess(secondcur->sendQuery("select count(*) from testtable"),1);
817 checkSuccess(secondcur->getField(0,(uint32_t)0),"8");
818 checkSuccess(con->autoCommitOn(),1);
819 checkSuccess(cur->sendQuery("insert into testtable values (10,'testchar10','testvarchar10','01-JAN-2010','testlong10','testclob10',NULL)"),1);
820 checkSuccess(secondcur->sendQuery("select count(*) from testtable"),1);
821 checkSuccess(secondcur->getField(0,(uint32_t)0),"9");
822 checkSuccess(con->autoCommitOff(),1);
823 delete secondcur;
824 delete secondcon;
825 stdoutput.printf("\n");
826
827 stdoutput.printf("FINISHED SUSPENDED SESSION: \n");
828 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
829 checkSuccess(cur->getField(4,(uint32_t)0),"5");
830 checkSuccess(cur->getField(5,(uint32_t)0),"6");
831 checkSuccess(cur->getField(6,(uint32_t)0),"7");
832 checkSuccess(cur->getField(7,(uint32_t)0),"8");
833 id=cur->getResultSetId();
834 cur->suspendResultSet();
835 checkSuccess(con->suspendSession(),1);
836 port=con->getConnectionPort();
837 socket=charstring::duplicate(con->getConnectionSocket());
838 checkSuccess(con->resumeSession(port,socket),1);
839 delete[] socket;
840 checkSuccess(cur->resumeResultSet(id),1);
841 checkSuccess(cur->getField(4,(uint32_t)0),NULL);
842 checkSuccess(cur->getField(5,(uint32_t)0),NULL);
843 checkSuccess(cur->getField(6,(uint32_t)0),NULL);
844 checkSuccess(cur->getField(7,(uint32_t)0),NULL);
845 stdoutput.printf("\n");
846
847 stdoutput.printf("CLOB AND BLOB OUTPUT BIND: \n");
848 cur->sendQuery("drop table testtable1");
849 checkSuccess(cur->sendQuery("create table testtable1 (testclob clob, testblob blob)"),1);
850 cur->prepareQuery("insert into testtable1 values ('hello',:var1)");
851 cur->inputBindBlob("var1","hello",5);
852 checkSuccess(cur->executeQuery(),1);
853 cur->prepareQuery("begin select testclob into :clobvar from testtable1; select testblob into :blobvar from testtable1; end;");
854 cur->defineOutputBindClob("clobvar");
855 cur->defineOutputBindBlob("blobvar");
856 checkSuccess(cur->executeQuery(),1);
857 clobvar=cur->getOutputBindClob("clobvar");
858 clobvarlength=cur->getOutputBindLength("clobvar");
859 blobvar=cur->getOutputBindBlob("blobvar");
860 blobvarlength=cur->getOutputBindLength("blobvar");
861 checkSuccess(clobvar,"hello",5);
862 checkSuccess(clobvarlength,5);
863 checkSuccess(blobvar,"hello",5);
864 checkSuccess(blobvarlength,5);
865 cur->sendQuery("drop table testtable1");
866 stdoutput.printf("\n");
867
868 stdoutput.printf("NULL AND EMPTY CLOBS AND BLOBS: \n");
869 cur->getNullsAsNulls();
870 cur->sendQuery("create table testtable1 (testclob1 clob, testclob2 clob, testblob1 blob, testblob2 blob)");
871 cur->prepareQuery("insert into testtable1 values (:var1,:var2,:var3,:var4)");
872 cur->inputBindClob("var1","",0);
873 cur->inputBindClob("var2",NULL,0);
874 cur->inputBindBlob("var3","",0);
875 cur->inputBindBlob("var4",NULL,0);
876 checkSuccess(cur->executeQuery(),1);
877 cur->sendQuery("select * from testtable1");
878 checkSuccess(cur->getField(0,(uint32_t)0),NULL);
879 checkSuccess(cur->getField(0,1),NULL);
880 checkSuccess(cur->getField(0,2),NULL);
881 checkSuccess(cur->getField(0,3),NULL);
882 cur->sendQuery("drop table testtable1");
883 stdoutput.printf("\n");
884
885 stdoutput.printf("CURSOR BINDS: \n");
886 cur->clearBinds();
887 checkSuccess(cur->sendQuery("create or replace package types is type cursorType is ref cursor; end;"),1);
888 checkSuccess(cur->sendQuery("create or replace function sp_testtable(value in number) return types.cursortype is l_cursor types.cursorType; begin open l_cursor for select * from testtable where testnumber>value; return l_cursor; end;"),1);
889 cur->prepareQuery("begin :curs1:=sp_testtable(5); :curs2:=sp_testtable(0); end;");
890 cur->defineOutputBindCursor("curs1");
891 cur->defineOutputBindCursor("curs2");
892 checkSuccess(cur->executeQuery(),1);
893 sqlrcursor *bindcur1=cur->getOutputBindCursor("curs1");
894 checkSuccess(bindcur1->fetchFromBindCursor(),1);
895 checkSuccess(bindcur1->getField(0,(uint32_t)0),"6");
896 checkSuccess(bindcur1->getField(1,(uint32_t)0),"7");
897 checkSuccess(bindcur1->getField(2,(uint32_t)0),"8");
898 delete bindcur1;
899 sqlrcursor *bindcur2=cur->getOutputBindCursor("curs2");
900 checkSuccess(bindcur2->fetchFromBindCursor(),1);
901 checkSuccess(bindcur2->getField(0,(uint32_t)0),"1");
902 checkSuccess(bindcur2->getField(1,(uint32_t)0),"2");
903 checkSuccess(bindcur2->getField(2,(uint32_t)0),"3");
904 delete bindcur2;
905 checkSuccess(cur->sendQuery("drop package types"),1);
906 stdoutput.printf("\n");
907
908 stdoutput.printf("LONG CLOB: \n");
909 cur->sendQuery("drop table testtable2");
910 cur->sendQuery("create table testtable2 (testclob clob)");
911 cur->prepareQuery("insert into testtable2 values (:clobval)");
912 char clobval[8*1024+1];
913 for (int i=0; i<8*1024; i++) {
914 clobval[i]='C';
915 }
916 clobval[8*1024]='\0';
917 cur->inputBindClob("clobval",clobval,8*1024);
918 checkSuccess(cur->executeQuery(),1);
919 cur->sendQuery("select testclob from testtable2");
920 checkSuccess(clobval,cur->getField(0,"testclob"));
921 cur->prepareQuery("begin select testclob into :clobbindval from testtable2; end;");
922 cur->defineOutputBindClob("clobbindval");
923 checkSuccess(cur->executeQuery(),1);
924 const char *clobbindvar=cur->getOutputBindClob("clobbindval");
925 checkSuccess(cur->getOutputBindLength("clobbindval"),8*1024);
926 checkSuccess(clobval,clobbindvar);
927 cur->sendQuery("drop table testtable2");
928 stdoutput.printf("\n");
929
930
931 stdoutput.printf("LONG OUTPUT BIND\n");
932 cur->sendQuery("drop table testtable2");
933 cur->sendQuery("create table testtable2 (testval varchar2(4000))");
934 char testval[4001];
935 testval[4000]='\0';
936 cur->prepareQuery("insert into testtable2 values (:testval)");
937 for (int i=0; i<4000; i++) {
938 testval[i]='C';
939 }
940 cur->inputBind("testval",testval);
941 checkSuccess(cur->executeQuery(),1);
942 cur->sendQuery("select testval from testtable2");
943 checkSuccess(testval,cur->getField(0,"testval"));
944 char query[4000+25];
945 charstring::printf(query,sizeof(query),
946 "begin :bindval:='%s'; end;",testval);
947 cur->prepareQuery(query);
948 cur->defineOutputBindString("bindval",4000);
949 checkSuccess(cur->executeQuery(),1);
950 checkSuccess(cur->getOutputBindLength("bindval"),4000);
951 checkSuccess(cur->getOutputBindString("bindval"),testval);
952 cur->sendQuery("drop table testtable2");
953 stdoutput.printf("\n");
954
955 stdoutput.printf("NEGATIVE INPUT BIND\n");
956 cur->sendQuery("drop table testtable2");
957 cur->sendQuery("create table testtable2 (testval number)");
958 cur->prepareQuery("insert into testtable2 values (:testval)");
959 cur->inputBind("testval",-1);
960 checkSuccess(cur->executeQuery(),1);
961 cur->sendQuery("select testval from testtable2");
962 checkSuccess(cur->getField(0,"TESTVAL"),"-1");
963 cur->sendQuery("drop table testtable2");
964 stdoutput.printf("\n");
965
966
967
968 stdoutput.printf("BIND VALIDATION: \n");
969 cur->sendQuery("drop table testtable1");
970 cur->sendQuery("create table testtable1 (col1 varchar2(20), col2 varchar2(20), col3 varchar2(20))");
971 cur->prepareQuery("insert into testtable1 values ($(var1),$(var2),$(var3))");
972 cur->inputBind("var1",1);
973 cur->inputBind("var2",2);
974 cur->inputBind("var3",3);
975 cur->substitution("var1",":var1");
976 checkSuccess(cur->validBind("var1"),1);
977 checkSuccess(cur->validBind("var2"),0);
978 checkSuccess(cur->validBind("var3"),0);
979 checkSuccess(cur->validBind("var4"),0);
980 stdoutput.printf("\n");
981 cur->substitution("var2",":var2");
982 checkSuccess(cur->validBind("var1"),1);
983 checkSuccess(cur->validBind("var2"),1);
984 checkSuccess(cur->validBind("var3"),0);
985 checkSuccess(cur->validBind("var4"),0);
986 stdoutput.printf("\n");
987 cur->substitution("var3",":var3");
988 checkSuccess(cur->validBind("var1"),1);
989 checkSuccess(cur->validBind("var2"),1);
990 checkSuccess(cur->validBind("var3"),1);
991 checkSuccess(cur->validBind("var4"),0);
992 checkSuccess(cur->executeQuery(),1);
993 cur->sendQuery("drop table testtable1");
994 stdoutput.printf("\n");
995
996 // drop existing table
997 cur->sendQuery("drop table testtable");
998
999
1000 // temporary tables
1001 stdoutput.printf("TEMPORARY TABLES: \n");
1002 cur->sendQuery("drop table temptabledelete\n");
1003 cur->sendQuery("create global temporary table temptabledelete (col1 number) on commit delete rows");
1004 checkSuccess(cur->sendQuery("insert into temptabledelete values (1)"),1);
1005 checkSuccess(cur->sendQuery("select count(*) from temptabledelete"),1);
1006 checkSuccess(cur->getField(0,(uint32_t)0),"1");
1007 checkSuccess(con->commit(),1);
1008 checkSuccess(cur->sendQuery("select count(*) from temptabledelete"),1);
1009 checkSuccess(cur->getField(0,(uint32_t)0),"0");
1010 cur->sendQuery("drop table temptabledelete\n");
1011 stdoutput.printf("\n");
1012 cur->sendQuery("truncate table temptablepreserve\n");
1013 cur->sendQuery("drop table temptablepreserve\n");
1014 cur->sendQuery("create global temporary table temptablepreserve (col1 number) on commit preserve rows");
1015 checkSuccess(cur->sendQuery("insert into temptablepreserve values (1)"),1);
1016 checkSuccess(cur->sendQuery("select count(*) from temptablepreserve"),1);
1017 checkSuccess(cur->getField(0,(uint32_t)0),"1");
1018 checkSuccess(con->commit(),1);
1019 checkSuccess(cur->sendQuery("select count(*) from temptablepreserve"),1);
1020 checkSuccess(cur->getField(0,(uint32_t)0),"1");
1021 con->endSession();
1022 stdoutput.printf("\n");
1023 checkSuccess(cur->sendQuery("select count(*) from temptablepreserve"),1);
1024 checkSuccess(cur->getField(0,(uint32_t)0),"0");
1025 checkSuccess(cur->sendQuery("truncate table temptablepreserve\n"),1);
1026 checkSuccess(cur->sendQuery("drop table temptablepreserve\n"),1);
1027 checkSuccess(cur->sendQuery("select count(*) from temptablepreserve"),0);
1028 stdoutput.printf("\n");
1029
1030
1031 // stored procedures
1032 stdoutput.printf("STORED PROCEDURE: \n");
1033 // return no value
1034 cur->sendQuery("drop function testproc");
1035 cur->sendQuery("drop procedure testproc");
1036 checkSuccess(cur->sendQuery("create or replace procedure testproc(in1 in number, in2 in number, in3 in varchar2) is begin return; end;"),1);
1037 cur->prepareQuery("begin testproc(:in1,:in2,:in3); end;");
1038 cur->inputBind("in1",1);
1039 cur->inputBind("in2",1.1,2,1);
1040 cur->inputBind("in3","hello");
1041 checkSuccess(cur->executeQuery(),1);
1042 // return single value
1043 cur->sendQuery("drop function testproc");
1044 cur->sendQuery("drop procedure testproc");
1045 checkSuccess(cur->sendQuery("create or replace function testproc(in1 in number, in2 in number, in3 in varchar2) return number is begin return in1; end;"),1);
1046 cur->prepareQuery("select testproc(:in1,:in2,:in3) from dual");
1047 cur->inputBind("in1",1);
1048 cur->inputBind("in2",1.1,2,1);
1049 cur->inputBind("in3","hello");
1050 checkSuccess(cur->executeQuery(),1);
1051 checkSuccess(cur->getField(0,(uint32_t)0),"1");
1052 cur->prepareQuery("begin :out1:=testproc(:in1,:in2,:in3); end;");
1053 cur->inputBind("in1",1);
1054 cur->inputBind("in2",1.1,2,1);
1055 cur->inputBind("in3","hello");
1056 cur->defineOutputBindInteger("out1");
1057 checkSuccess(cur->executeQuery(),1);
1058 checkSuccess(cur->getOutputBindInteger("out1"),1);
1059 // return multiple values
1060 cur->sendQuery("drop function testproc");
1061 cur->sendQuery("drop procedure testproc");
1062 checkSuccess(cur->sendQuery("create or replace procedure testproc(in1 in number, in2 in number, in3 in varchar2, out1 out number, out2 out number, out3 out varchar2) is begin out1:=in1; out2:=in2; out3:=in3; end;"),1);
1063 cur->prepareQuery("begin testproc(:in1,:in2,:in3,:out1,:out2,:out3); end;");
1064 cur->inputBind("in1",1);
1065 cur->inputBind("in2",1.1,2,1);
1066 cur->inputBind("in3","hello");
1067 cur->defineOutputBindInteger("out1");
1068 cur->defineOutputBindDouble("out2");
1069 cur->defineOutputBindString("out3",20);
1070 checkSuccess(cur->executeQuery(),1);
1071 checkSuccess(cur->getOutputBindInteger("out1"),1);
1072 checkSuccess(cur->getOutputBindDouble("out2"),1.1);
1073 checkSuccess(cur->getOutputBindString("out3"),"hello");
1074 cur->sendQuery("drop function testproc");
1075 cur->sendQuery("drop procedure testproc");
1076 stdoutput.printf("\n");
1077
1078
1079 // in/out variables
1080 /*stdoutput.printf("IN/OUT VARIABLES: \n");
1081 cur->sendQuery("drop procedure testproc");
1082 checkSuccess(cur->sendQuery("create or replace procedure testproc(inout in out number) is begin inout:=inout+1; return; end;"),1);
1083 cur->prepareQuery("begin testproc(:inout); end;");
1084 cur->inputBind("inout",1);
1085 cur->defineOutputBindInteger("inout");
1086 checkSuccess(cur->executeQuery(),1);
1087 checkSuccess(cur->getOutputBindInteger("inout"),2);
1088 cur->sendQuery("drop procedure testproc");
1089 stdoutput.printf("\n");*/
1090
1091
1092
1093 // rebinding
1094 stdoutput.printf("REBINDING: \n");
1095 cur->sendQuery("drop procedure testproc");
1096 checkSuccess(cur->sendQuery("create or replace procedure testproc(in1 in number, out1 out number) is begin out1:=in1; return; end;"),1);
1097 cur->prepareQuery("begin testproc(:in,:out); end;");
1098 cur->inputBind("in",1);
1099 cur->defineOutputBindInteger("out");
1100 checkSuccess(cur->executeQuery(),1);
1101 checkSuccess(cur->getOutputBindInteger("out"),1);
1102 cur->inputBind("in",2);
1103 checkSuccess(cur->executeQuery(),1);
1104 checkSuccess(cur->getOutputBindInteger("out"),2);
1105 cur->inputBind("in",3);
1106 checkSuccess(cur->executeQuery(),1);
1107 checkSuccess(cur->getOutputBindInteger("out"),3);
1108 cur->sendQuery("drop procedure testproc");
1109 stdoutput.printf("\n");
1110
1111
1112 // invalid queries...
1113 stdoutput.printf("INVALID QUERIES: \n");
1114 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
1115 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
1116 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
1117 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
1118 stdoutput.printf("\n");
1119 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
1120 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
1121 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
1122 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
1123 stdoutput.printf("\n");
1124 checkSuccess(cur->sendQuery("create table testtable"),0);
1125 checkSuccess(cur->sendQuery("create table testtable"),0);
1126 checkSuccess(cur->sendQuery("create table testtable"),0);
1127 checkSuccess(cur->sendQuery("create table testtable"),0);
1128 stdoutput.printf("\n");
1129
1130
1131 delete cur;
1132 delete con;
1133
1134 return 0;
1135 }
1136