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
checkSuccess(const char * value,const char * success)12 void checkSuccess(const char *value, const char *success) {
13
14 if (!success) {
15 if (!value) {
16 stdoutput.printf("success ");
17 return;
18 } else {
19 stdoutput.printf("%s!=%s\n",value,success);
20 stdoutput.printf("failure ");
21 delete cur;
22 delete con;
23 process::exit(1);
24 }
25 }
26
27 if (!charstring::compare(value,success)) {
28 stdoutput.printf("success ");
29 } else {
30 stdoutput.printf("%s!=%s\n",value,success);
31 stdoutput.printf("failure ");
32 delete cur;
33 delete con;
34 process::exit(1);
35 }
36 }
37
checkSuccess(const char * value,const char * success,size_t length)38 void checkSuccess(const char *value, const char *success, size_t length) {
39
40 if (!success) {
41 if (!value) {
42 stdoutput.printf("success ");
43 return;
44 } else {
45 stdoutput.printf("%s!=%s\n",value,success);
46 stdoutput.printf("failure ");
47 delete cur;
48 delete con;
49 process::exit(1);
50 }
51 }
52
53 if (!charstring::compare(value,success,length)) {
54 stdoutput.printf("success ");
55 } else {
56 stdoutput.printf("%s!=%s\n",value,success);
57 stdoutput.printf("failure ");
58 delete cur;
59 delete con;
60 process::exit(1);
61 }
62 }
63
checkSuccess(int value,int success)64 void checkSuccess(int value, int success) {
65
66 if (value==success) {
67 stdoutput.printf("success ");
68 } else {
69 stdoutput.printf("%d!=%d\n",value,success);
70 stdoutput.printf("failure ");
71 delete cur;
72 delete con;
73 process::exit(1);
74 }
75 }
76
checkSuccess(double value,double success)77 void checkSuccess(double value, double success) {
78
79 if (value==success) {
80 stdoutput.printf("success ");
81 } else {
82 stdoutput.printf("%f!=%f\n",value,success);
83 stdoutput.printf("failure ");
84 delete cur;
85 delete con;
86 process::exit(1);
87 }
88 }
89
main(int argc,char ** argv)90 int main(int argc, char **argv) {
91
92 const char *bindvars[6]={"1","2","3","4","5",NULL};
93 const char *bindvals[5]={"4","testchar4","testvarchar4","01-JAN-04","testlong4"};
94 const char *subvars[4]={"var1","var2","var3",NULL};
95 const char *subvalstrings[3]={"hi","hello","bye"};
96 int64_t subvallongs[3]={1,2,3};
97 double subvaldoubles[3]={10.55,10.556,10.5556};
98 uint32_t precs[3]={4,5,6};
99 uint32_t scales[3]={2,3,4};
100 int64_t numvar;
101 const char *stringvar;
102 double floatvar;
103 const char * const *cols;
104 const char * const *fields;
105 uint16_t port;
106 const char *socket;
107 uint16_t id;
108 const char *filename;
109 const char *arraybindvars[6]={"var1","var2","var3","var4","var5",NULL};
110 const char *arraybindvals[5]={"7","testchar7","testvarchar7","01-JAN-07","testlong7"};
111 uint32_t *fieldlens;
112
113 // instantiation
114 con=new sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
115 "test","test",0,1);
116 cur=new sqlrcursor(con);
117
118 // get database type
119 stdoutput.printf("IDENTIFY: \n");
120 checkSuccess(con->identify(),"oracle");
121 stdoutput.printf("\n");
122
123 // ping
124 stdoutput.printf("PING: \n");
125 checkSuccess(con->ping(),1);
126 stdoutput.printf("\n");
127
128 // drop existing table
129 cur->sendQuery("drop table testtable");
130
131 stdoutput.printf("CREATE TEMPTABLE: \n");
132 checkSuccess(cur->sendQuery("create table testtable (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long)"),1);
133 stdoutput.printf("\n");
134
135 stdoutput.printf("INSERT: \n");
136 checkSuccess(cur->sendQuery("insert into testtable values (1,'testchar1','testvarchar1','01-JAN-01','testlong1')"),1);
137 checkSuccess(cur->countBindVariables(),0);
138 stdoutput.printf("\n");
139
140 stdoutput.printf("AFFECTED ROWS: \n");
141 checkSuccess(cur->affectedRows(),1);
142 stdoutput.printf("\n");
143
144 stdoutput.printf("BIND BY POSITION: \n");
145 cur->prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4,:var5)");
146 checkSuccess(cur->countBindVariables(),5);
147 cur->inputBind("1",2);
148 cur->inputBind("2","testchar2");
149 cur->inputBind("3","testvarchar2");
150 cur->inputBind("4",2,1,1,0,0,0,0,NULL,false);
151 cur->inputBind("5","testlong2");
152 checkSuccess(cur->executeQuery(),1);
153 cur->clearBinds();
154 cur->inputBind("1",3);
155 cur->inputBind("2","testchar3");
156 cur->inputBind("3","testvarchar3");
157 cur->inputBind("4",3,1,1,0,0,0,0,NULL,false);
158 cur->inputBind("5","testlong3");
159 checkSuccess(cur->executeQuery(),1);
160 stdoutput.printf("\n");
161
162 stdoutput.printf("ARRAY OF BINDS BY POSITION: \n");
163 cur->clearBinds();
164 cur->inputBinds(bindvars,bindvals);
165 checkSuccess(cur->executeQuery(),1);
166 stdoutput.printf("\n");
167
168 stdoutput.printf("BIND BY NAME: \n");
169 cur->prepareQuery("insert into testtable values (:var1,:var2,:var3,:var4,:var5)");
170 cur->inputBind("var1",5);
171 cur->inputBind("var2","testchar5");
172 cur->inputBind("var3","testvarchar5");
173 cur->inputBind("var4",5,1,1,0,0,0,0,NULL,false);
174 cur->inputBind("var5","testlong5");
175 checkSuccess(cur->executeQuery(),1);
176 cur->clearBinds();
177 cur->inputBind("var1",6);
178 cur->inputBind("var2","testchar6");
179 cur->inputBind("var3","testvarchar6");
180 cur->inputBind("var4",6,1,1,0,0,0,0,NULL,false);
181 cur->inputBind("var5","testlong6");
182 checkSuccess(cur->executeQuery(),1);
183 stdoutput.printf("\n");
184
185 stdoutput.printf("ARRAY OF BINDS BY NAME: \n");
186 cur->clearBinds();
187 cur->inputBinds(arraybindvars,arraybindvals);
188 checkSuccess(cur->executeQuery(),1);
189 stdoutput.printf("\n");
190
191 stdoutput.printf("BIND BY NAME WITH VALIDATION: \n");
192 cur->clearBinds();
193 cur->inputBind("var1",8);
194 cur->inputBind("var2","testchar8");
195 cur->inputBind("var3","testvarchar8");
196 cur->inputBind("var4",8,1,1,0,0,0,0,NULL,false);
197 cur->inputBind("var5","testlong8");
198 cur->inputBind("var9","junkvalue");
199 cur->validateBinds();
200 checkSuccess(cur->executeQuery(),1);
201 stdoutput.printf("\n");
202
203 stdoutput.printf("OUTPUT BIND BY NAME: \n");
204 cur->prepareQuery("begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; :datevar:='03-FEB-01'; end;");
205 cur->defineOutputBindInteger("numvar");
206 cur->defineOutputBindString("stringvar",10);
207 cur->defineOutputBindDouble("floatvar");
208 cur->defineOutputBindDate("datevar");
209 checkSuccess(cur->executeQuery(),1);
210 numvar=cur->getOutputBindInteger("numvar");
211 stringvar=cur->getOutputBindString("stringvar");
212 floatvar=cur->getOutputBindDouble("floatvar");
213 int16_t year=0;
214 int16_t month=0;
215 int16_t day=0;
216 int16_t hour=0;
217 int16_t minute=0;
218 int16_t second=0;
219 int32_t microsecond=0;
220 const char *tz=NULL;
221 bool isnegative=false;
222 cur->getOutputBindDate("datevar",&year,&month,&day,
223 &hour,&minute,&second,µsecond,&tz,
224 &isnegative);
225 checkSuccess(numvar,1);
226 checkSuccess(stringvar,"hello");
227 checkSuccess(floatvar,2.5);
228 checkSuccess(year,2001);
229 checkSuccess(month,2);
230 checkSuccess(day,3);
231 checkSuccess(hour,0);
232 checkSuccess(minute,0);
233 checkSuccess(second,0);
234 checkSuccess(microsecond,0);
235 checkSuccess(tz,"");
236 stdoutput.printf("\n");
237
238 stdoutput.printf("OUTPUT BIND BY POSITION: \n");
239 cur->clearBinds();
240 cur->defineOutputBindInteger("1");
241 cur->defineOutputBindString("2",10);
242 cur->defineOutputBindDouble("3");
243 cur->defineOutputBindDate("4");
244 checkSuccess(cur->executeQuery(),1);
245 numvar=cur->getOutputBindInteger("1");
246 stringvar=cur->getOutputBindString("2");
247 floatvar=cur->getOutputBindDouble("3");
248 cur->getOutputBindDate("4",&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 NAME WITH VALIDATION: \n");
265 cur->clearBinds();
266 cur->defineOutputBindInteger("numvar");
267 cur->defineOutputBindString("stringvar",10);
268 cur->defineOutputBindDouble("floatvar");
269 cur->defineOutputBindDate("datevar");
270 cur->defineOutputBindString("dummyvar",10);
271 cur->validateBinds();
272 checkSuccess(cur->executeQuery(),1);
273 numvar=cur->getOutputBindInteger("numvar");
274 stringvar=cur->getOutputBindString("stringvar");
275 floatvar=cur->getOutputBindDouble("floatvar");
276 cur->getOutputBindDate("datevar",&year,&month,&day,
277 &hour,&minute,&second,µsecond,&tz,
278 &isnegative);
279 checkSuccess(numvar,1);
280 checkSuccess(stringvar,"hello");
281 checkSuccess(floatvar,2.5);
282 checkSuccess(year,2001);
283 checkSuccess(month,2);
284 checkSuccess(day,3);
285 checkSuccess(hour,0);
286 checkSuccess(minute,0);
287 checkSuccess(second,0);
288 checkSuccess(microsecond,0);
289 checkSuccess(tz,"");
290 stdoutput.printf("\n");
291
292 stdoutput.printf("SELECT: \n");
293 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
294 stdoutput.printf("\n");
295
296 stdoutput.printf("COLUMN COUNT: \n");
297 checkSuccess(cur->colCount(),5);
298 stdoutput.printf("\n");
299
300 stdoutput.printf("COLUMN NAMES: \n");
301 checkSuccess(cur->getColumnName(0),"TESTNUMBER");
302 checkSuccess(cur->getColumnName(1),"TESTCHAR");
303 checkSuccess(cur->getColumnName(2),"TESTVARCHAR");
304 checkSuccess(cur->getColumnName(3),"TESTDATE");
305 checkSuccess(cur->getColumnName(4),"TESTLONG");
306 cols=cur->getColumnNames();
307 checkSuccess(cols[0],"TESTNUMBER");
308 checkSuccess(cols[1],"TESTCHAR");
309 checkSuccess(cols[2],"TESTVARCHAR");
310 checkSuccess(cols[3],"TESTDATE");
311 checkSuccess(cols[4],"TESTLONG");
312 stdoutput.printf("\n");
313
314 stdoutput.printf("COLUMN TYPES: \n");
315 checkSuccess(cur->getColumnType((uint32_t)0),"NUMBER");
316 checkSuccess(cur->getColumnType("testnumber"),"NUMBER");
317 checkSuccess(cur->getColumnType(1),"CHAR");
318 checkSuccess(cur->getColumnType("testchar"),"CHAR");
319 checkSuccess(cur->getColumnType(2),"VARCHAR2");
320 checkSuccess(cur->getColumnType("testvarchar"),"VARCHAR2");
321 checkSuccess(cur->getColumnType(3),"DATE");
322 checkSuccess(cur->getColumnType("testdate"),"DATE");
323 checkSuccess(cur->getColumnType(4),"LONG");
324 checkSuccess(cur->getColumnType("testlong"),"LONG");
325 stdoutput.printf("\n");
326
327 stdoutput.printf("COLUMN LENGTH: \n");
328 checkSuccess(cur->getColumnLength((uint32_t)0),22);
329 checkSuccess(cur->getColumnLength("testnumber"),22);
330 checkSuccess(cur->getColumnLength(1),40);
331 checkSuccess(cur->getColumnLength("testchar"),40);
332 checkSuccess(cur->getColumnLength(2),40);
333 checkSuccess(cur->getColumnLength("testvarchar"),40);
334 checkSuccess(cur->getColumnLength(3),7);
335 checkSuccess(cur->getColumnLength("testdate"),7);
336 checkSuccess(cur->getColumnLength(4),0);
337 checkSuccess(cur->getColumnLength("testlong"),0);
338 stdoutput.printf("\n");
339
340 stdoutput.printf("LONGEST COLUMN: \n");
341 checkSuccess(cur->getLongest((uint32_t)0),1);
342 checkSuccess(cur->getLongest("testnumber"),1);
343 checkSuccess(cur->getLongest(1),40);
344 checkSuccess(cur->getLongest("testchar"),40);
345 checkSuccess(cur->getLongest(2),12);
346 checkSuccess(cur->getLongest("testvarchar"),12);
347 checkSuccess(cur->getLongest(3),9);
348 checkSuccess(cur->getLongest("testdate"),9);
349 checkSuccess(cur->getLongest(4),9);
350 checkSuccess(cur->getLongest("testlong"),9);
351 stdoutput.printf("\n");
352
353 stdoutput.printf("ROW COUNT: \n");
354 checkSuccess(cur->rowCount(),8);
355 stdoutput.printf("\n");
356
357 stdoutput.printf("TOTAL ROWS: \n");
358 checkSuccess(cur->totalRows(),0);
359 stdoutput.printf("\n");
360
361 stdoutput.printf("FIRST ROW INDEX: \n");
362 checkSuccess(cur->firstRowIndex(),0);
363 stdoutput.printf("\n");
364
365 stdoutput.printf("END OF RESULT SET: \n");
366 checkSuccess(cur->endOfResultSet(),1);
367 stdoutput.printf("\n");
368
369 stdoutput.printf("FIELDS BY INDEX: \n");
370 checkSuccess(cur->getField(0,(uint32_t)0),"1");
371 checkSuccess(cur->getField(0,1),"testchar1 ");
372 checkSuccess(cur->getField(0,2),"testvarchar1");
373 checkSuccess(cur->getField(0,3),"01-JAN-01");
374 checkSuccess(cur->getField(0,4),"testlong1");
375 stdoutput.printf("\n");
376 checkSuccess(cur->getField(7,(uint32_t)0),"8");
377 checkSuccess(cur->getField(7,1),"testchar8 ");
378 checkSuccess(cur->getField(7,2),"testvarchar8");
379 checkSuccess(cur->getField(7,3),"01-JAN-08");
380 checkSuccess(cur->getField(7,4),"testlong8");
381 stdoutput.printf("\n");
382
383 stdoutput.printf("FIELD LENGTHS BY INDEX: \n");
384 checkSuccess(cur->getFieldLength(0,(uint32_t)0),1);
385 checkSuccess(cur->getFieldLength(0,1),40);
386 checkSuccess(cur->getFieldLength(0,2),12);
387 checkSuccess(cur->getFieldLength(0,3),9);
388 checkSuccess(cur->getFieldLength(0,4),9);
389 stdoutput.printf("\n");
390 checkSuccess(cur->getFieldLength(7,(uint32_t)0),1);
391 checkSuccess(cur->getFieldLength(7,1),40);
392 checkSuccess(cur->getFieldLength(7,2),12);
393 checkSuccess(cur->getFieldLength(7,3),9);
394 checkSuccess(cur->getFieldLength(7,4),9);
395 stdoutput.printf("\n");
396
397 stdoutput.printf("FIELDS BY NAME: \n");
398 checkSuccess(cur->getField(0,"TESTNUMBER"),"1");
399 checkSuccess(cur->getField(0,"TESTCHAR"),"testchar1 ");
400 checkSuccess(cur->getField(0,"TESTVARCHAR"),"testvarchar1");
401 checkSuccess(cur->getField(0,"TESTDATE"),"01-JAN-01");
402 checkSuccess(cur->getField(0,"TESTLONG"),"testlong1");
403 stdoutput.printf("\n");
404 checkSuccess(cur->getField(7,"TESTNUMBER"),"8");
405 checkSuccess(cur->getField(7,"TESTCHAR"),"testchar8 ");
406 checkSuccess(cur->getField(7,"TESTVARCHAR"),"testvarchar8");
407 checkSuccess(cur->getField(7,"TESTDATE"),"01-JAN-08");
408 checkSuccess(cur->getField(7,"TESTLONG"),"testlong8");
409 stdoutput.printf("\n");
410
411 stdoutput.printf("FIELD LENGTHS BY NAME: \n");
412 checkSuccess(cur->getFieldLength(0,"TESTNUMBER"),1);
413 checkSuccess(cur->getFieldLength(0,"TESTCHAR"),40);
414 checkSuccess(cur->getFieldLength(0,"TESTVARCHAR"),12);
415 checkSuccess(cur->getFieldLength(0,"TESTDATE"),9);
416 checkSuccess(cur->getFieldLength(0,"TESTLONG"),9);
417 stdoutput.printf("\n");
418 checkSuccess(cur->getFieldLength(7,"TESTNUMBER"),1);
419 checkSuccess(cur->getFieldLength(7,"TESTCHAR"),40);
420 checkSuccess(cur->getFieldLength(7,"TESTVARCHAR"),12);
421 checkSuccess(cur->getFieldLength(7,"TESTDATE"),9);
422 checkSuccess(cur->getFieldLength(7,"TESTLONG"),9);
423 stdoutput.printf("\n");
424
425 stdoutput.printf("FIELDS BY ARRAY: \n");
426 fields=cur->getRow(0);
427 checkSuccess(fields[0],"1");
428 checkSuccess(fields[1],"testchar1 ");
429 checkSuccess(fields[2],"testvarchar1");
430 checkSuccess(fields[3],"01-JAN-01");
431 checkSuccess(fields[4],"testlong1");
432 stdoutput.printf("\n");
433
434 stdoutput.printf("FIELD LENGTHS BY ARRAY: \n");
435 fieldlens=cur->getRowLengths(0);
436 checkSuccess(fieldlens[0],1);
437 checkSuccess(fieldlens[1],40);
438 checkSuccess(fieldlens[2],12);
439 checkSuccess(fieldlens[3],9);
440 checkSuccess(fieldlens[4],9);
441 stdoutput.printf("\n");
442
443 stdoutput.printf("INDIVIDUAL SUBSTITUTIONS: \n");
444 cur->prepareQuery("select $(var1),'$(var2)',$(var3) from dual");
445 cur->substitution("var1",1);
446 cur->substitution("var2","hello");
447 cur->substitution("var3",10.5556,6,4);
448 checkSuccess(cur->executeQuery(),1);
449 stdoutput.printf("\n");
450
451 stdoutput.printf("FIELDS: \n");
452 checkSuccess(cur->getField(0,(uint32_t)0),"1");
453 checkSuccess(cur->getField(0,1),"hello");
454 checkSuccess(cur->getField(0,2),"10.5556");
455 stdoutput.printf("\n");
456
457 stdoutput.printf("OUTPUT BIND: \n");
458 cur->prepareQuery("begin :var1:='hello'; end;");
459 cur->defineOutputBindString("var1",10);
460 checkSuccess(cur->executeQuery(),1);
461 checkSuccess(cur->getOutputBindString("var1"),"hello");
462 stdoutput.printf("\n");
463
464 stdoutput.printf("ARRAY SUBSTITUTIONS: \n");
465 cur->prepareQuery("select $(var1),$(var2),$(var3) from dual");
466 cur->substitutions(subvars,subvallongs);
467 checkSuccess(cur->executeQuery(),1);
468 stdoutput.printf("\n");
469
470 stdoutput.printf("FIELDS: \n");
471 checkSuccess(cur->getField(0,(uint32_t)0),"1");
472 checkSuccess(cur->getField(0,1),"2");
473 checkSuccess(cur->getField(0,2),"3");
474 stdoutput.printf("\n");
475
476 stdoutput.printf("ARRAY SUBSTITUTIONS: \n");
477 cur->prepareQuery("select '$(var1)','$(var2)','$(var3)' from dual");
478 cur->substitutions(subvars,subvalstrings);
479 checkSuccess(cur->executeQuery(),1);
480 stdoutput.printf("\n");
481
482 stdoutput.printf("FIELDS: \n");
483 checkSuccess(cur->getField(0,(uint32_t)0),"hi");
484 checkSuccess(cur->getField(0,1),"hello");
485 checkSuccess(cur->getField(0,2),"bye");
486 stdoutput.printf("\n");
487
488 stdoutput.printf("ARRAY SUBSTITUTIONS: \n");
489 cur->prepareQuery("select $(var1),$(var2),$(var3) from dual");
490 cur->substitutions(subvars,subvaldoubles,precs,scales);
491 checkSuccess(cur->executeQuery(),1);
492 stdoutput.printf("\n");
493
494 stdoutput.printf("FIELDS: \n");
495 checkSuccess(cur->getField(0,(uint32_t)0),"10.55");
496 checkSuccess(cur->getField(0,1),"10.556");
497 checkSuccess(cur->getField(0,2),"10.5556");
498 stdoutput.printf("\n");
499
500 stdoutput.printf("NULLS as Nulls: \n");
501 cur->getNullsAsNulls();
502 checkSuccess(cur->sendQuery("select NULL,1,NULL from dual"),1);
503 checkSuccess(cur->getField(0,(uint32_t)0),NULL);
504 checkSuccess(cur->getField(0,1),"1");
505 checkSuccess(cur->getField(0,2),NULL);
506 cur->getNullsAsEmptyStrings();
507 checkSuccess(cur->sendQuery("select NULL,1,NULL from dual"),1);
508 checkSuccess(cur->getField(0,(uint32_t)0),"");
509 checkSuccess(cur->getField(0,1),"1");
510 checkSuccess(cur->getField(0,2),"");
511 cur->getNullsAsNulls();
512 stdoutput.printf("\n");
513
514 stdoutput.printf("RESULT SET BUFFER SIZE: \n");
515 checkSuccess(cur->getResultSetBufferSize(),0);
516 cur->setResultSetBufferSize(2);
517 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
518 checkSuccess(cur->getResultSetBufferSize(),2);
519 stdoutput.printf("\n");
520 checkSuccess(cur->firstRowIndex(),0);
521 checkSuccess(cur->endOfResultSet(),0);
522 checkSuccess(cur->rowCount(),2);
523 checkSuccess(cur->getField(0,(uint32_t)0),"1");
524 checkSuccess(cur->getField(1,(uint32_t)0),"2");
525 checkSuccess(cur->getField(2,(uint32_t)0),"3");
526 checkSuccess(cur->firstRowIndex(),2);
527 checkSuccess(cur->endOfResultSet(),0);
528 checkSuccess(cur->rowCount(),4);
529 checkSuccess(cur->getField(6,(uint32_t)0),"7");
530 checkSuccess(cur->getField(7,(uint32_t)0),"8");
531 stdoutput.printf("\n");
532 checkSuccess(cur->firstRowIndex(),6);
533 checkSuccess(cur->endOfResultSet(),0);
534 checkSuccess(cur->rowCount(),8);
535 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
536 stdoutput.printf("\n");
537 checkSuccess(cur->firstRowIndex(),8);
538 checkSuccess(cur->endOfResultSet(),1);
539 checkSuccess(cur->rowCount(),8);
540 stdoutput.printf("\n");
541
542 stdoutput.printf("DONT GET COLUMN INFO: \n");
543 cur->dontGetColumnInfo();
544 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
545 checkSuccess(cur->getColumnName(0),NULL);
546 checkSuccess(cur->getColumnLength((uint32_t)0),0);
547 checkSuccess(cur->getColumnType((uint32_t)0),NULL);
548 cur->getColumnInfo();
549 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
550 checkSuccess(cur->getColumnName(0),"TESTNUMBER");
551 checkSuccess(cur->getColumnLength((uint32_t)0),22);
552 checkSuccess(cur->getColumnType((uint32_t)0),"NUMBER");
553 stdoutput.printf("\n");
554
555 stdoutput.printf("SUSPENDED SESSION: \n");
556 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
557 cur->suspendResultSet();
558 checkSuccess(con->suspendSession(),1);
559 port=con->getConnectionPort();
560 socket=charstring::duplicate(con->getConnectionSocket());
561 checkSuccess(con->resumeSession(port,socket),1);
562 stdoutput.printf("\n");
563 checkSuccess(cur->getField(0,(uint32_t)0),"1");
564 checkSuccess(cur->getField(1,(uint32_t)0),"2");
565 checkSuccess(cur->getField(2,(uint32_t)0),"3");
566 checkSuccess(cur->getField(3,(uint32_t)0),"4");
567 checkSuccess(cur->getField(4,(uint32_t)0),"5");
568 checkSuccess(cur->getField(5,(uint32_t)0),"6");
569 checkSuccess(cur->getField(6,(uint32_t)0),"7");
570 checkSuccess(cur->getField(7,(uint32_t)0),"8");
571 stdoutput.printf("\n");
572 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
573 cur->suspendResultSet();
574 checkSuccess(con->suspendSession(),1);
575 port=con->getConnectionPort();
576 socket=charstring::duplicate(con->getConnectionSocket());
577 checkSuccess(con->resumeSession(port,socket),1);
578 stdoutput.printf("\n");
579 checkSuccess(cur->getField(0,(uint32_t)0),"1");
580 checkSuccess(cur->getField(1,(uint32_t)0),"2");
581 checkSuccess(cur->getField(2,(uint32_t)0),"3");
582 checkSuccess(cur->getField(3,(uint32_t)0),"4");
583 checkSuccess(cur->getField(4,(uint32_t)0),"5");
584 checkSuccess(cur->getField(5,(uint32_t)0),"6");
585 checkSuccess(cur->getField(6,(uint32_t)0),"7");
586 checkSuccess(cur->getField(7,(uint32_t)0),"8");
587 stdoutput.printf("\n");
588 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
589 cur->suspendResultSet();
590 checkSuccess(con->suspendSession(),1);
591 port=con->getConnectionPort();
592 socket=charstring::duplicate(con->getConnectionSocket());
593 checkSuccess(con->resumeSession(port,socket),1);
594 stdoutput.printf("\n");
595 checkSuccess(cur->getField(0,(uint32_t)0),"1");
596 checkSuccess(cur->getField(1,(uint32_t)0),"2");
597 checkSuccess(cur->getField(2,(uint32_t)0),"3");
598 checkSuccess(cur->getField(3,(uint32_t)0),"4");
599 checkSuccess(cur->getField(4,(uint32_t)0),"5");
600 checkSuccess(cur->getField(5,(uint32_t)0),"6");
601 checkSuccess(cur->getField(6,(uint32_t)0),"7");
602 checkSuccess(cur->getField(7,(uint32_t)0),"8");
603 stdoutput.printf("\n");
604
605 stdoutput.printf("SUSPENDED RESULT SET: \n");
606 cur->setResultSetBufferSize(2);
607 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
608 checkSuccess(cur->getField(2,(uint32_t)0),"3");
609 id=cur->getResultSetId();
610 cur->suspendResultSet();
611 checkSuccess(con->suspendSession(),1);
612 port=con->getConnectionPort();
613 socket=charstring::duplicate(con->getConnectionSocket());
614 checkSuccess(con->resumeSession(port,socket),1);
615 checkSuccess(cur->resumeResultSet(id),1);
616 stdoutput.printf("\n");
617 checkSuccess(cur->firstRowIndex(),4);
618 checkSuccess(cur->endOfResultSet(),0);
619 checkSuccess(cur->rowCount(),6);
620 checkSuccess(cur->getField(7,(uint32_t)0),"8");
621 stdoutput.printf("\n");
622 checkSuccess(cur->firstRowIndex(),6);
623 checkSuccess(cur->endOfResultSet(),0);
624 checkSuccess(cur->rowCount(),8);
625 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
626 stdoutput.printf("\n");
627 checkSuccess(cur->firstRowIndex(),8);
628 checkSuccess(cur->endOfResultSet(),1);
629 checkSuccess(cur->rowCount(),8);
630 cur->setResultSetBufferSize(0);
631 stdoutput.printf("\n");
632
633 stdoutput.printf("CACHED RESULT SET: \n");
634 cur->cacheToFile("cachefile1");
635 cur->setCacheTtl(200);
636 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
637 filename=charstring::duplicate(cur->getCacheFileName());
638 checkSuccess(filename,"cachefile1");
639 cur->cacheOff();
640 checkSuccess(cur->openCachedResultSet(filename),1);
641 checkSuccess(cur->getField(7,(uint32_t)0),"8");
642 delete[] filename;
643 stdoutput.printf("\n");
644
645 stdoutput.printf("COLUMN COUNT FOR CACHED RESULT SET: \n");
646 checkSuccess(cur->colCount(),5);
647 stdoutput.printf("\n");
648
649 stdoutput.printf("COLUMN NAMES FOR CACHED RESULT SET: \n");
650 checkSuccess(cur->getColumnName(0),"TESTNUMBER");
651 checkSuccess(cur->getColumnName(1),"TESTCHAR");
652 checkSuccess(cur->getColumnName(2),"TESTVARCHAR");
653 checkSuccess(cur->getColumnName(3),"TESTDATE");
654 checkSuccess(cur->getColumnName(4),"TESTLONG");
655 cols=cur->getColumnNames();
656 checkSuccess(cols[0],"TESTNUMBER");
657 checkSuccess(cols[1],"TESTCHAR");
658 checkSuccess(cols[2],"TESTVARCHAR");
659 checkSuccess(cols[3],"TESTDATE");
660 checkSuccess(cols[4],"TESTLONG");
661 stdoutput.printf("\n");
662
663 stdoutput.printf("CACHED RESULT SET WITH RESULT SET BUFFER SIZE: \n");
664 cur->setResultSetBufferSize(2);
665 cur->cacheToFile("cachefile1");
666 cur->setCacheTtl(200);
667 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
668 filename=charstring::duplicate(cur->getCacheFileName());
669 checkSuccess(filename,"cachefile1");
670 cur->cacheOff();
671 checkSuccess(cur->openCachedResultSet(filename),1);
672 checkSuccess(cur->getField(7,(uint32_t)0),"8");
673 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
674 cur->setResultSetBufferSize(0);
675 delete[] filename;
676 stdoutput.printf("\n");
677
678 stdoutput.printf("FROM ONE CACHE FILE TO ANOTHER: \n");
679 cur->cacheToFile("cachefile2");
680 checkSuccess(cur->openCachedResultSet("cachefile1"),1);
681 cur->cacheOff();
682 checkSuccess(cur->openCachedResultSet("cachefile2"),1);
683 checkSuccess(cur->getField(7,(uint32_t)0),"8");
684 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
685 stdoutput.printf("\n");
686
687 stdoutput.printf("FROM ONE CACHE FILE TO ANOTHER WITH RESULT SET BUFFER SIZE: \n");
688 cur->setResultSetBufferSize(2);
689 cur->cacheToFile("cachefile2");
690 checkSuccess(cur->openCachedResultSet("cachefile1"),1);
691 cur->cacheOff();
692 checkSuccess(cur->openCachedResultSet("cachefile2"),1);
693 checkSuccess(cur->getField(7,(uint32_t)0),"8");
694 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
695 cur->setResultSetBufferSize(0);
696 stdoutput.printf("\n");
697
698 stdoutput.printf("CACHED RESULT SET WITH SUSPEND AND RESULT SET BUFFER SIZE: \n");
699 cur->setResultSetBufferSize(2);
700 cur->cacheToFile("cachefile1");
701 cur->setCacheTtl(200);
702 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
703 checkSuccess(cur->getField(2,(uint32_t)0),"3");
704 filename=charstring::duplicate(cur->getCacheFileName());
705 checkSuccess(filename,"cachefile1");
706 id=cur->getResultSetId();
707 cur->suspendResultSet();
708 checkSuccess(con->suspendSession(),1);
709 port=con->getConnectionPort();
710 socket=charstring::duplicate(con->getConnectionSocket());
711 stdoutput.printf("\n");
712 checkSuccess(con->resumeSession(port,socket),1);
713 checkSuccess(cur->resumeCachedResultSet(id,filename),1);
714 stdoutput.printf("\n");
715 checkSuccess(cur->firstRowIndex(),4);
716 checkSuccess(cur->endOfResultSet(),0);
717 checkSuccess(cur->rowCount(),6);
718 checkSuccess(cur->getField(7,(uint32_t)0),"8");
719 stdoutput.printf("\n");
720 checkSuccess(cur->firstRowIndex(),6);
721 checkSuccess(cur->endOfResultSet(),0);
722 checkSuccess(cur->rowCount(),8);
723 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
724 stdoutput.printf("\n");
725 checkSuccess(cur->firstRowIndex(),8);
726 checkSuccess(cur->endOfResultSet(),1);
727 checkSuccess(cur->rowCount(),8);
728 cur->cacheOff();
729 stdoutput.printf("\n");
730 checkSuccess(cur->openCachedResultSet(filename),1);
731 checkSuccess(cur->getField(7,(uint32_t)0),"8");
732 checkSuccess(cur->getField(8,(uint32_t)0),NULL);
733 cur->setResultSetBufferSize(0);
734 delete[] filename;
735 stdoutput.printf("\n");
736
737 stdoutput.printf("FINISHED SUSPENDED SESSION: \n");
738 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),1);
739 checkSuccess(cur->getField(4,(uint32_t)0),"5");
740 checkSuccess(cur->getField(5,(uint32_t)0),"6");
741 checkSuccess(cur->getField(6,(uint32_t)0),"7");
742 checkSuccess(cur->getField(7,(uint32_t)0),"8");
743 id=cur->getResultSetId();
744 cur->suspendResultSet();
745 checkSuccess(con->suspendSession(),1);
746 port=con->getConnectionPort();
747 socket=charstring::duplicate(con->getConnectionSocket());
748 checkSuccess(con->resumeSession(port,socket),1);
749 checkSuccess(cur->resumeResultSet(id),1);
750 checkSuccess(cur->getField(4,(uint32_t)0),NULL);
751 checkSuccess(cur->getField(5,(uint32_t)0),NULL);
752 checkSuccess(cur->getField(6,(uint32_t)0),NULL);
753 checkSuccess(cur->getField(7,(uint32_t)0),NULL);
754 stdoutput.printf("\n");
755
756 stdoutput.printf("LONG OUTPUT BIND\n");
757 cur->sendQuery("drop table testtable2");
758 cur->sendQuery("create table testtable2 (testval varchar2(2000))");
759 char testval[2001];
760 testval[2000]='\0';
761 cur->prepareQuery("insert into testtable2 values (:testval)");
762 for (int i=0; i<2000; i++) {
763 testval[i]='C';
764 }
765 cur->inputBind("testval",testval);
766 checkSuccess(cur->executeQuery(),1);
767 cur->sendQuery("select testval from testtable2");
768 checkSuccess(testval,cur->getField(0,"TESTVAL"));
769 char query[2000+25];
770 charstring::printf(query,sizeof(query),
771 "begin :bindval:='%s'; end;",testval);
772 cur->prepareQuery(query);
773 cur->defineOutputBindString("bindval",2000);
774 checkSuccess(cur->executeQuery(),1);
775 checkSuccess(cur->getOutputBindLength("bindval"),2000);
776 checkSuccess(cur->getOutputBindString("bindval"),testval);
777 cur->sendQuery("drop table testtable2");
778 stdoutput.printf("\n");
779
780 stdoutput.printf("NEGATIVE INPUT BIND\n");
781 cur->sendQuery("drop table testtable2");
782 cur->sendQuery("create table testtable2 (testval number)");
783 cur->prepareQuery("insert into testtable2 values (:testval)");
784 cur->inputBind("testval",-1);
785 checkSuccess(cur->executeQuery(),1);
786 cur->sendQuery("select testval from testtable2");
787 checkSuccess(cur->getField(0,"TESTVAL"),"-1");
788 cur->sendQuery("drop table testtable2");
789 stdoutput.printf("\n");
790
791
792
793 stdoutput.printf("BIND VALIDATION: \n");
794 cur->sendQuery("drop table testtable1");
795 cur->sendQuery("create table testtable1 (col1 varchar2(20), col2 varchar2(20), col3 varchar2(20))");
796 cur->prepareQuery("insert into testtable1 values ($(var1),$(var2),$(var3))");
797 cur->inputBind("var1",1);
798 cur->inputBind("var2",2);
799 cur->inputBind("var3",3);
800 cur->substitution("var1",":var1");
801 checkSuccess(cur->validBind("var1"),1);
802 checkSuccess(cur->validBind("var2"),0);
803 checkSuccess(cur->validBind("var3"),0);
804 checkSuccess(cur->validBind("var4"),0);
805 stdoutput.printf("\n");
806 cur->substitution("var2",":var2");
807 checkSuccess(cur->validBind("var1"),1);
808 checkSuccess(cur->validBind("var2"),1);
809 checkSuccess(cur->validBind("var3"),0);
810 checkSuccess(cur->validBind("var4"),0);
811 stdoutput.printf("\n");
812 cur->substitution("var3",":var3");
813 checkSuccess(cur->validBind("var1"),1);
814 checkSuccess(cur->validBind("var2"),1);
815 checkSuccess(cur->validBind("var3"),1);
816 checkSuccess(cur->validBind("var4"),0);
817 checkSuccess(cur->executeQuery(),1);
818 cur->sendQuery("drop table testtable1");
819 stdoutput.printf("\n");
820
821 // drop existing table
822 cur->sendQuery("drop table testtable");
823
824
825 // stored procedures
826 stdoutput.printf("STORED PROCEDURE: \n");
827 // return no value
828 cur->sendQuery("drop function testproc");
829 cur->sendQuery("drop procedure testproc");
830 checkSuccess(cur->sendQuery("create or replace procedure testproc(in1 in number, in2 in number, in3 in varchar2) is begin return; end;"),1);
831 cur->prepareQuery("begin testproc(:in1,:in2,:in3); end;");
832 cur->inputBind("in1",1);
833 cur->inputBind("in2",1.1,2,1);
834 cur->inputBind("in3","hello");
835 checkSuccess(cur->executeQuery(),1);
836 // return single value
837 cur->sendQuery("drop function testproc");
838 cur->sendQuery("drop procedure testproc");
839 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);
840 cur->prepareQuery("select testproc(:in1,:in2,:in3) from dual");
841 cur->inputBind("in1",1);
842 cur->inputBind("in2",1.1,2,1);
843 cur->inputBind("in3","hello");
844 checkSuccess(cur->executeQuery(),1);
845 checkSuccess(cur->getField(0,(uint32_t)0),"1");
846 cur->prepareQuery("begin :out1:=testproc(:in1,:in2,:in3); end;");
847 cur->inputBind("in1",1);
848 cur->inputBind("in2",1.1,2,1);
849 cur->inputBind("in3","hello");
850 cur->defineOutputBindInteger("out1");
851 checkSuccess(cur->executeQuery(),1);
852 checkSuccess(cur->getOutputBindInteger("out1"),1);
853 // return multiple values
854 cur->sendQuery("drop function testproc");
855 cur->sendQuery("drop procedure testproc");
856 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);
857 cur->prepareQuery("begin testproc(:in1,:in2,:in3,:out1,:out2,:out3); end;");
858 cur->inputBind("in1",1);
859 cur->inputBind("in2",1.1,2,1);
860 cur->inputBind("in3","hello");
861 cur->defineOutputBindInteger("out1");
862 cur->defineOutputBindDouble("out2");
863 cur->defineOutputBindString("out3",20);
864 checkSuccess(cur->executeQuery(),1);
865 checkSuccess(cur->getOutputBindInteger("out1"),1);
866 checkSuccess(cur->getOutputBindDouble("out2"),1.1);
867 checkSuccess(cur->getOutputBindString("out3"),"hello");
868 cur->sendQuery("drop function testproc");
869 cur->sendQuery("drop procedure testproc");
870 stdoutput.printf("\n");
871
872
873 // in/out variables
874 /*stdoutput.printf("IN/OUT VARIABLES: \n");
875 cur->sendQuery("drop procedure testproc");
876 checkSuccess(cur->sendQuery("create or replace procedure testproc(inout in out number) is begin inout:=inout+1; return; end;"),1);
877 cur->prepareQuery("begin testproc(:inout); end;");
878 cur->inputBind("inout",1);
879 cur->defineOutputBindInteger("inout");
880 checkSuccess(cur->executeQuery(),1);
881 checkSuccess(cur->getOutputBindInteger("inout"),2);
882 cur->sendQuery("drop procedure testproc");
883 stdoutput.printf("\n");*/
884
885
886
887 // rebinding
888 stdoutput.printf("REBINDING: \n");
889 cur->sendQuery("drop procedure testproc");
890 checkSuccess(cur->sendQuery("create or replace procedure testproc(in1 in number, out1 out number) is begin out1:=in1; return; end;"),1);
891 cur->prepareQuery("begin testproc(:in,:out); end;");
892 cur->inputBind("in",1);
893 cur->defineOutputBindInteger("out");
894 checkSuccess(cur->executeQuery(),1);
895 checkSuccess(cur->getOutputBindInteger("out"),1);
896 cur->inputBind("in",2);
897 checkSuccess(cur->executeQuery(),1);
898 checkSuccess(cur->getOutputBindInteger("out"),2);
899 cur->inputBind("in",3);
900 checkSuccess(cur->executeQuery(),1);
901 checkSuccess(cur->getOutputBindInteger("out"),3);
902 cur->sendQuery("drop procedure testproc");
903 stdoutput.printf("\n");
904
905
906 // invalid queries...
907 stdoutput.printf("INVALID QUERIES: \n");
908 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
909 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
910 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
911 checkSuccess(cur->sendQuery("select * from testtable order by testnumber"),0);
912 stdoutput.printf("\n");
913 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
914 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
915 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
916 checkSuccess(cur->sendQuery("insert into testtable values (1,2,3,4)"),0);
917 stdoutput.printf("\n");
918 checkSuccess(cur->sendQuery("create table testtable"),0);
919 checkSuccess(cur->sendQuery("create table testtable"),0);
920 checkSuccess(cur->sendQuery("create table testtable"),0);
921 checkSuccess(cur->sendQuery("create table testtable"),0);
922 stdoutput.printf("\n");
923
924
925 delete cur;
926 delete con;
927
928 return 0;
929 }
930