?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Removing large file for data load. Modified README to generate mysql and psql input...
[csql.git] / test / performance / wisc / odbcDB.c
blob0121029f03544f380f746f682a918a9ca65b436f
1 #include <stdio.h>
2 #include <stdlib.h>
3 #include <unistd.h>
4 #include <strings.h>
5 #include <string.h>
6
7 #include "sql.h"
8 #include "sqlext.h"
9
10 #include "support.h"
11 #include "odbcDB.h"
12
13 static SQLHENV henv;
14 static SQLHDBC hdbc;
15 static SQLHSTMT hstmt;
16
17 #define InsertTmplt \
18 "insert into %s values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
19
20 #define MAXHSTMT 100
21 #define MAXCOLS 100
22
23 extern int verboseOut;
24 extern int readOnly;
25
26 static int longerRun = 1;
27 #define RPTS 2
28
29 int odbcDB::connect (char *dsn)
30 {
31 int rc;
32 char errstr[256];
33
34 rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
35 if (rc)
36 printf ("Unable to allocate an environment handle.\n");
37
38 SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
39 rc = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
40 if (rc)
41 printf ("Unable to allocate connection handle.\n");
42
43
44
45 SWORD xstat;
46 unsigned char outbuf[256];
47
48 rc = SQLConnect (hdbc,
49 (SQLCHAR *) "test", (SQLSMALLINT) strlen ("test"),
50 (SQLCHAR *) "root",
51 (SQLSMALLINT) strlen ("root"),
52 (SQLCHAR *) "manager",
53 (SQLSMALLINT) strlen (""));
54
55 // connect to mysql
56 /*SQLCHAR* outstr;
57 SQLSMALLINT outstrlen;
58 //SQLSMALLINT SQL_DRIVER_NOPROMPT;
59 SQLSMALLINT slen = SQL_NTS;
60
61 SQLDriverConnect(hdbc,NULL,(SQLCHAR*)"DSN=myodbc3;",SQL_NTS,outstr,sizeof(outstr),&outstrlen,SQL_DRIVER_NOPROMPT);*/
62
63 if(!SQL_SUCCEEDED(rc)){
64 printf("Unable to connect to driver\n");
65 return rc;
66 }
67
68 rc = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
69 if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
70 printf ("Unable to set connection option autocommit %d.\n", rc);
71 /* allocate statement handle for use with table creation and population */
72
73 rc = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
74 if (rc)
75 printf ("Unable to allocate statement handle.\n");
76
77 return (rc);
78 }
79
80
81 int
82 odbcDB::populateTable(char* tblName, int numTups, int valRange)
83 {
84
85 char buf[1024];
86 int prime;
87 int generator;
88 int seed;
89 int randInt;
90 static SQLINTEGER intVal1, intVal2, intVal3, intVal4, intVal5, intVal6,
91 intVal7, intVal8, intVal9, intVal10, intVal11, intVal12,
92 intVal13;
93 static SQLINTEGER cbintVal=4;
94 static char s4Val[64];
95 static char charVal1[64], charVal2[64];
96 int i;
97
98 SQLRETURN rc = SQL_SUCCESS;
99 static SQLINTEGER cValLen=0,cValLen2=0,cValLen4=0;
100 int fBind = 1;
101
102 if (verboseOut)
103 printf ("Populating table %s with %d rows...\n", tblName, numTups);
104
105 /* create a prepared statement */
106 sprintf(buf, InsertTmplt, tblName);
107
108 rc = SQLPrepare(hstmt,(SQLCHAR*)buf, SQL_NTS );
109 if (rc) {
110 printf ("Unable to prepare statement.\n");
111 return (rc);
112 }
113
114 /* set generator and prime values based upon value range */
115
116 if ( valRange<=1000 ) {
117 generator = 279;
118 prime = 1009;
119 }
120 else if ( valRange<=10000 ) {
121 generator = 2969;
122 prime = 10007;
123 }
124 else if ( valRange<=100000 ) {
125 generator = 21395;
126 prime = 100003;
127 }
128 else if ( valRange<=1000000 ) {
129 generator = 2107;
130 prime = 1000003;
131 }
132 else if ( valRange<=10000000 ) {
133 generator = 211;
134 prime = 10000019;
135 }
136 else if ( valRange<=100000000 ) {
137 generator = 21;
138 prime = 100000007;
139 }
140 else {
141 printf ("Too many rows in table.\n");
142 }
143
144 seed = generator;
145
146 /* bind the input parameters */
147
148 sprintf(charVal1, "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
149 charVal1[51] = '\0';
150 sprintf(charVal2, "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
151 charVal2[51] = '\0';
152
153 if (fBind)
154 {
155 if (verboseOut)
156 printf ("Binding parameters for table load...\n");
157
158 fBind = 0;
159 rc = 0;
160
161 rc += SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,\
162 SQL_INTEGER, 10, 0, &intVal1,4 , &cbintVal) ;
163 rc += SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,\
164 SQL_INTEGER, 10, 0, &intVal2,4 , &cbintVal) ;
165 rc += SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_SLONG,\
166 SQL_INTEGER, 10, 0, &intVal3,4 , &cbintVal) ;
167 rc += SQLBindParameter( hstmt, 4, SQL_PARAM_INPUT, SQL_C_SLONG,\
168 SQL_INTEGER, 10, 0, &intVal4,4 , &cbintVal) ;
169 rc += SQLBindParameter( hstmt, 5, SQL_PARAM_INPUT, SQL_C_SLONG,\
170 SQL_INTEGER, 10, 0, &intVal5,4 , &cbintVal) ;
171 rc += SQLBindParameter( hstmt, 6, SQL_PARAM_INPUT, SQL_C_SLONG,\
172 SQL_INTEGER, 10, 0, &intVal6,4 , &cbintVal) ;
173 rc += SQLBindParameter( hstmt, 7, SQL_PARAM_INPUT, SQL_C_SLONG,\
174 SQL_INTEGER, 10, 0, &intVal7,4 , &cbintVal) ;
175 rc += SQLBindParameter( hstmt, 8, SQL_PARAM_INPUT, SQL_C_SLONG,\
176 SQL_INTEGER, 10, 0, &intVal8,4 , &cbintVal) ;
177 rc += SQLBindParameter( hstmt, 9, SQL_PARAM_INPUT, SQL_C_SLONG,\
178 SQL_INTEGER, 10, 0, &intVal9,4 , &cbintVal) ;
179 rc += SQLBindParameter( hstmt, 10, SQL_PARAM_INPUT, SQL_C_SLONG,\
180 SQL_INTEGER, 10, 0, &intVal10,4 , &cbintVal) ;
181 rc += SQLBindParameter( hstmt, 11, SQL_PARAM_INPUT, SQL_C_SLONG,\
182 SQL_INTEGER, 10, 0, &intVal11,4 , &cbintVal) ;
183 rc += SQLBindParameter( hstmt, 12, SQL_PARAM_INPUT, SQL_C_SLONG,\
184 SQL_INTEGER, 10, 0, &intVal12,4 , &cbintVal) ;
185 rc += SQLBindParameter( hstmt, 13, SQL_PARAM_INPUT, SQL_C_SLONG,\
186 SQL_INTEGER, 38, 0, &intVal13,4 , &cbintVal) ;
187
188 cValLen = SQL_NTS;
189 rc += SQLBindParameter( hstmt, 14, SQL_PARAM_INPUT, SQL_C_CHAR,\
190 SQL_CHAR, 52,0, charVal1, 52, &cValLen) ;
191 cValLen2 = SQL_NTS;
192 rc += SQLBindParameter( hstmt, 15, SQL_PARAM_INPUT, SQL_C_CHAR,\
193 SQL_CHAR, 52,0, charVal2, 52, &cValLen2) ;
194 cValLen4 = SQL_NTS;
195 rc += SQLBindParameter( hstmt, 16, SQL_PARAM_INPUT, SQL_C_CHAR,\
196 SQL_CHAR, 52,0, s4Val, 52, &cValLen4) ;
197
198 if (rc)
199 printf ("Bind Parameters failed.\n");
200
201 if (verboseOut)
202 printf ("Completed\n");
203
204 } /* End BindColumns */
205
206 /* insert the numTups rows */
207
208 for (i=0; i<numTups; i++) {
209 seed = randNum(seed, valRange, generator, prime);
210 randInt = (int) seed - 1;
211
212 /* unique1: range is 0 - valRange-1; order is random */
213 intVal1 = randInt;
214 /* unique2: range is 0 - valRange-1; order is sequential */
215 intVal2 = i;
216 /* two: range is 0 - 1; order is random */
217 intVal3 = randInt % 2;
218 /* four: range is 0 - 3; order is random */
219 intVal4 = randInt % 4;
220 /* ten: range is 0 - 9; order is random */
221 intVal5 = randInt % 10;
222 /* twenty: range is 0 - 19; order is random */
223 intVal6 = randInt % 20;
224 /* onepercent: range is 0 - 99; order is random */
225 intVal7 = randInt % 100;
226 /* tenpercent: range is 0 - 9; order is random */
227 intVal8 = randInt % 10;
228 /* twentypercent: range is 0 - 4; order is random */
229 intVal9 = randInt % 5;
230 /* fiftypercent: range is 0 - 1; order is random */
231 intVal10 = randInt % 2;
232 /* unique3: range is 0 - valRange-1; order is random */
233 intVal11 = randInt;
234 /* evenonepercent: range is 0 - 198; order is random */
235 intVal12 = (randInt%100) * 2;
236 /* oddonepercent: range is 0 - 199; order is random */
237 intVal13 = (randInt%100) * 2 + 1;
238 /* stringu1: order is random */
239 convert(randInt, charVal1);
240 cValLen = (int) strlen(charVal1);
241 /* stringu2: order is sequential */
242 convert(i, charVal2);
243 cValLen2 = (int) strlen(charVal2);
244 /* string4: order is cyclic */
245
246 switch ( i%4 ) {
247 case 0:
248 strcpy(s4Val,"AAAAxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
249 break;
250 case 1:
251 strcpy(s4Val,"HHHHxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
252 break;
253 case 2:
254 strcpy(s4Val,"OOOOxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
255 break;
256 case 3:
257 strcpy(s4Val,"VVVVxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
258 break;
259 }
260 cValLen4 = (int) strlen(s4Val);
261
262 /* ODBC Execute prepared statement */
263
264 rc = SQLExecute(hstmt);
265
266 if (rc)
267 {
268 #if 0
269 SQLCHAR sqlstate[6], msg[300];
270 SQLINTEGER native;
271 SQLSMALLINT i, len;
272 i = 1;
273 while ( SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, sqlstate, &native, msg, 290, &len)
274 != SQL_NO_DATA )
275 {
276 printf("%s\n", msg);
277 i++;
278 }
279 #endif
280 printf ("SQLExecute failed. (%d)\n", i);
281 exit(1);
282 }
283
284 #if 0
285 rc = SQLTransact(henv,hdbc,SQL_COMMIT);
286 if (rc)
287 printf ("Commit failed\n");
288 #endif
289 } /* end for */
290
291 /* commit the transaction */
292
293 rc = SQLTransact(henv,hdbc,SQL_COMMIT);
294 if (rc)
295 printf ("Commit failed\n");
296
297 /* close the statement (but do not drop) and return */
298
299 rc = SQLFreeStmt(hstmt, SQL_DROP);
300 if (rc)
301 printf ("Unable to SQL_DROP statement handle.\n");
302
303 rc = SQLAllocStmt(hdbc, &hstmt);
304 if (rc)
305 printf ("Unable to Alloc statement handle.\n");
306
307 return(rc);
308 }
309
310 static int
311 fetchSelect(int queryNum, class wiscTimer *wT, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmtI, int verify,
312 int resCard, int uniq)
313 {
314 int i;
315 SQLCHAR colname[32];
316 SQLSMALLINT coltype;
317 SQLSMALLINT bindtype;
318 SQLINTEGER bindsize;
319 SQLSMALLINT colnamelen;
320 SQLSMALLINT nullable;
321 SQLUINTEGER collen[MAXCOLS];
322 SQLSMALLINT scale;
323 SQLINTEGER outlen[MAXCOLS];
324 SQLCHAR* data[MAXCOLS];
325 SQLSMALLINT nresultcols = 0, dispcols = 0;
326 SQLRETURN rc = SQL_SUCCESS;
327 int print = 0;
328 int cnt = 0;
329 // SQLRETURN rc1 = SQL_SUCCESS;
330
331 for ( i = 0; i < MAXCOLS; i++ )
332 data[i] = NULL;
333
334 /* Instantiate the result set */
335
336 rc = SQLNumResultCols(hstmtI, &nresultcols);
337 if (rc)
338 printf ("SQLNumresult columns failed.\n");
339
340 /* bind each column */
341
342 for (i = 0; i < nresultcols; i++) {
343
344 rc = SQLDescribeCol(hstmtI,
345 (SQLSMALLINT)(i+1),
346 colname,
347 (SQLSMALLINT) sizeof(colname),
348 &colnamelen,
349 &coltype,
350 &collen[i],
351 &scale,
352 &nullable);
353
354 if (rc)
355 printf ("Unable to describe the column %d.\n", i+1);
356
357 /* Select a binding type based on the column type */
358
359 switch (coltype) {
360 case SQL_VARCHAR:
361 case SQL_CHAR:
362 bindtype = SQL_C_CHAR;
363 bindsize = collen[i] + 1;
364 break;
365
366 case SQL_INTEGER:
367 bindtype = SQL_C_SLONG;
368 bindsize = sizeof (int);
369 break;
370
371 case SQL_SMALLINT:
372 bindtype = SQL_C_SHORT;
373 bindsize = sizeof (short);
374 break;
375
376 case SQL_TINYINT:
377 bindtype = SQL_C_TINYINT;
378 bindsize = sizeof (char);
379 break;
380
381 case SQL_REAL:
382 bindtype = SQL_C_FLOAT;
383 bindsize = sizeof (float);
384 break;
385
386 case SQL_FLOAT:
387 bindtype = SQL_C_DOUBLE;
388 bindsize = sizeof (double);
389 break;
390
391 #if 0
392 // The schema used for wisconsin benchmark queries does not
393 // involve this datatype.
394 case SQL_NUMERIC:
395 bindtype = SQL_C_NUMERIC;
396 bindsize = collen[i] + 1;
397 break;
398 #endif
399
400 default:
401 printf ("Unknown col type %d\n", coltype);
402 bindtype = SQL_C_CHAR;
403 bindsize = 80;
404 break;
405 }
406
407 data[i] = (unsigned char *) ::malloc(bindsize);
408
409 rc = SQLBindCol( hstmtI,
410 (SQLUSMALLINT)(i+1),
411 bindtype,
412 (SQLPOINTER)(data[i]),
413 bindsize,
414 &outlen[i]);
415
416 if (rc)
417 printf ("Unable to bind the column %d.\n", i+1);
418
419 //if (print) printf ("%*.*s ",collen[i], collen[i], colname);
420 } /* end for each column */
421
422 dispcols = nresultcols;
423
424 /* commit the transaction */
425
426 /* execute the query */
427 wT->startTimer();
428
429 rc = SQLExecute(hstmtI);
430
431 if (rc != SQL_SUCCESS)
432 {
433 printf ("Execute failed. (query=%d hstmt=%p rc=%d)\n",
434 queryNum, hstmtI, rc);
435 return rc;
436 }
437
438 /* if specified, verify that the number of actual result
439 * rows are the expected number of result rows
440 */
441
442 cnt = 0;
443 for (;;) {
444 rc = SQLFetch(hstmtI);
445
446 if (rc)
447 break;
448
449 cnt++;
450
451 if (print) {
452 for (i = 0; i < dispcols; i++) {
453 if (outlen[i] == SQL_NULL_DATA) {
454 strcpy((char *)data[i], "NULL");
455 }
456 printf ("%*.*s ", collen[i], collen[i], data[i]);
457 }
458 printf ("\n");
459 }
460 }
461
462 //printf ("rc = %d\n", rc);
463 //printf("PRABA::Total tuples fetched %d\n", cnt);
464
465 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
466 if (rc)
467 printf ("Unable to commit transaction");
468
469 wT->stopTimer();
470 rc = SQLCloseCursor(hstmtI);
471 if (rc != SQL_SUCCESS)
472 {
473 printf ("Close Cursor failed. (query=%d hstmt=%p rc=%d)\n",
474 queryNum, hstmtI, rc);
475 return rc;
476 }
477
478 if (verify) {
479 if (uniq && cnt != 1) {
480 printf ("Error: Query %d: uniq specified and cnt = %d\n", queryNum, cnt);
481 }
482 if ( cnt != resCard) {
483 printf ("Error: Query %d: expected %d results, got %d\n", queryNum, resCard, cnt);
484 }
485 }
486
487 /* free locally allocated memory */
488
489 for ( i = 0 ; i < MAXCOLS; i++ ) {
490 if ( data[i] )
491 ::free(data[i]);
492 }
493
494 return(rc);
495 }
496
497 int
498 odbcDB::selectionQuery (int queryNum, class wiscTimer *wT, char *form1, char *form2, int tblCard,
499 int resCard, int uniq, int verify, int repeatCnt,
500 int numParams)
501 {
502 int range = tblCard - resCard;
503 int val;
504 char buf[MAXHSTMT][192];
505 int i,j,times;
506 int totalNumQueries;
507 SQLHSTMT hstmtA[MAXHSTMT];
508 SQLRETURN rc = SQL_SUCCESS;
509
510 /* do not perform the operation if the query is not specified by the
511 * user */
512
513 if (!isQuery(queryNum)) {
514 return(SQL_SUCCESS);
515 }
516
517 bzero((char *)buf,sizeof(buf));
518
519 /* print the query number */
520
521 if (verboseOut)
522 printf ("Query %d:\n", queryNum);
523
524 /* limit the repeat count */
525
526 if ( repeatCnt > 256 ) {
527 rc = 1;
528 printf ("selectionQuery: repeatCnt too high");
529 return rc;
530 }
531
532 /* create array[repeatCnt*2] of statement handles */
533
534 totalNumQueries = repeatCnt * 2;
535 times = longerRun * RPTS;
536
537 printf ("Query %d - total %d times %d\n", queryNum, totalNumQueries, times);
538
539 for (i = 0; i < totalNumQueries; i++) {
540 rc = SQLAllocStmt(hdbc, &hstmtA[i]);
541 if (rc)
542 printf ("Unable to allocate statement handle.\n");
543 }
544
545 /*
546 * Build and compile queries that select desired number of
547 * rows (alternating between the two forms). A total of
548 * ten queries are constructed, with column ranges selected
549 * at random.
550 */
551
552 for (i = 0; i < repeatCnt; i++) {
553
554 val = (int) (drand48() * range);
555
556 switch (numParams) {
557 case 0:
558 sprintf(buf[2*i], form1);
559 break;
560 case 1:
561 sprintf(buf[2*i], form1, val);
562 break;
563 case 2:
564 sprintf(buf[2*i], form1, val, val+resCard-1);
565 break;
566 default:
567 printf ("selectionQuery: unexpected numParams value\n");
568 return(-1);
569 }
570
571 /* ODBC prepared statement 2 different statements prepared, each
572 * executed repeatCnt number of times with the parameters changed
573 * for each iteration */
574
575 rc= SQLPrepare(hstmtA[2*i],(SQLCHAR *) buf[2*i], SQL_NTS );
576 if (rc)
577 printf ("Unable to prepare statement.\n");
578 printf("hstmtA[%d]=%p, buf[%d]=%s\n", 2*i, hstmtA[2*i], 2*i, buf[2*i]);
579
580 val = (int) (drand48() * range);
581
582 switch ( numParams ) {
583 case 0:
584 sprintf(buf[2*i+1], form2);
585 break;
586 case 1:
587 sprintf(buf[2*i+1], form2, val);
588 break;
589 case 2:
590 sprintf(buf[2*i+1], form2, val, val+resCard-1);
591 break;
592 default:
593 printf ("selectionQuery: unexpected numParams value\n");
594 return -1;
595 }
596
597 /* ODBC prepared statement #2 */
598
599 rc = SQLPrepare(hstmtA[2*i+1],(SQLCHAR *) buf[2*i+1], SQL_NTS );
600 if (rc) {
601 printf ("Unable to prepare statement.\n");
602 return -1;
603 }
604 }
605
606 /* commit the transaction */
607
608 /* execute and time queries. */
609
610 /* ODBC version of fetchSelect call, handle freed in fetchSelect */
611
612 for (i = 0; i < totalNumQueries; i++) {
613
614 /* execute each query many a time */
615
616 for (j = 0; j < times; j++) {
617
618 rc = fetchSelect(queryNum, wT, henv, hdbc, hstmtA[i], verify, resCard, uniq);
619
620 if (rc) {
621 printf ("fetchSelect failed.\n");
622 return(rc);
623 }
624 }
625 }
626
627 return(rc);
628 }
629
630 int
631 odbcDB::runUpdate(int queryNum, class wiscTimer *wT, char* col,
632 int numBigTblTups, char *Big1TblName, char *Big2TblName)
633 {
634 char* upd = "update %s set %s = %d where %s = %d";
635 char buf[512];
636 int val1 = numBigTblTups + 1;
637 int val2 = numBigTblTups/2;
638 int i;
639 SQLHSTMT hstmtA[MAXHSTMT];
640 SQLRETURN rc = SQL_SUCCESS;
641
642 if (!isQuery(queryNum))
643 return 0;
644
645 /* print the query number */
646
647 if (verboseOut)
648 printf ("Query %d:\n", queryNum);
649
650 /* prepare the update statements */
651
652 for (i = 0; i < ((10*RPTS)/4); i++, val1++, val2++) {
653 rc = 0;
654
655 /* allocate and prepare statement #1 */
656
657 sprintf(buf, upd, Big1TblName, col, val1, col, val2);
658
659 rc += SQLAllocStmt(hdbc, &hstmtA[i*4]);
660 rc += SQLPrepare(hstmtA[i*4],(SQLCHAR *)buf, SQL_NTS );
661
662 /* allocate and prepare statement #2 */
663
664 sprintf(buf, upd, Big2TblName, col, val1, col, val2);
665
666 rc += SQLAllocStmt(hdbc, &hstmtA[i*4+1]);
667 rc += SQLPrepare(hstmtA[i*4+1],(SQLCHAR *)buf, SQL_NTS );
668
669 /* allocate and prepare statement #3 */
670
671 sprintf(buf, upd, Big1TblName, col, val2, col, val1);
672
673 rc += SQLAllocStmt(hdbc, &hstmtA[i*4+2]);
674 rc += SQLPrepare(hstmtA[i*4+2],(SQLCHAR *)buf, SQL_NTS );
675
676 /* allocate and prepare statement #4 */
677
678 sprintf(buf, upd, Big2TblName, col, val2, col, val1);
679
680 rc += SQLAllocStmt(hdbc, &hstmtA[i*4+3]);
681 rc += SQLPrepare(hstmtA[i*4+3],(SQLCHAR *)buf, SQL_NTS );
682
683 if (rc)
684 printf ("Prepare failed\n");
685 }
686
687 /* commit the transaction */
688
689 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
690 if (rc)
691 printf ("Unable to commit transaction");
692
693 /* Execute and time updates. */
694
695 for (i = 0; i < 10*RPTS; i++) {
696
697 wT->startTimer();
698
699 rc = SQLExecute(hstmtA[i]);
700 if (rc)
701 printf ("Execute failed (handle[%d]=%p)\n", i, hstmtA[i]);
702
703 rc = SQLTransact(henv,hdbc,SQL_COMMIT);
704
705 wT->stopTimer();
706
707 if (rc)
708 printf ("commit failed\n");
709 }
710
711
712 /* Delete compiled commands. */
713
714 for (i=0; i<10*RPTS; i++) {
715 rc = SQLFreeStmt(hstmtA[i], SQL_DROP);
716 if (rc)
717 printf ("Free Stmt failed\n");
718 }
719
720 /* commit the transaction and return */
721
722 rc = SQLTransact(henv,hdbc,SQL_COMMIT);
723 if (rc)
724 printf ("Unable to commit transaction.\n");
725
726 return(rc);
727 }
728
729 int
730 odbcDB::runDelete(int queryNum, class wiscTimer *wT, int numBigTblTups,
731 char *Big1TblName, char *Big2TblName)
732 {
733 char* ins = "delete from %s where unique1=%d";
734 char buf[512];
735 int val = numBigTblTups + 1;
736 int i;
737 int recordQuery = 1;
738 SQLHSTMT hstmtA[MAXHSTMT];
739 SQLRETURN rc = SQL_SUCCESS;
740
741 /* perform this operation whether specified by user or not, in case
742 * it affects subsequent queries; however, do not record it if not
743 * specified by user */
744
745 if (!isQuery(queryNum))
746 return 0;
747
748 /* print the query number */
749
750 if (verboseOut)
751 printf ("Query %d:\n", queryNum);
752
753 /* prepare delete statements */
754
755 for (i = 0; i < 5*RPTS; i++, val++) {
756
757 rc = 0;
758
759 sprintf(buf, ins, Big1TblName, val);
760
761 rc += SQLAllocStmt(hdbc, &hstmtA[i*2]);
762
763 rc += SQLPrepare(hstmtA[i*2],(SQLCHAR *)buf, SQL_NTS );
764
765 sprintf(buf, ins, Big2TblName, val);
766
767 rc += SQLAllocStmt(hdbc, &hstmtA[i*2+1]);
768
769 rc += SQLPrepare(hstmtA[i*2+1],(SQLCHAR *)buf, SQL_NTS );
770
771 if (rc)
772 printf ("Allocation and Prepare failed\n");
773
774 } /* end for */
775
776 /* commit the transaction */
777
778 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
779
780 if (rc)
781 printf ("Commit failed\n");
782
783 /* execute and time deletes. */
784
785 for (i = 0; i < 10*RPTS; i++) {
786
787 val += i % 2; // incr every other value.
788 wT->startTimer();
789
790 rc = SQLExecute(hstmtA[i]);
791 if (rc)
792 printf ("Execute failed\n");
793
794 /* commit the transaction */
795
796 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
797
798 wT->stopTimer();
799
800 if (rc)
801 printf ("Unable to commit transaction\n");
802
803 } /* end for */
804
805 for (i = 0; i < 10*RPTS; i++) {
806
807 rc = SQLFreeStmt(hstmtA[i], SQL_DROP);
808
809 if (rc)
810 printf ("Free Stmt failed.\n");
811 }
812
813
814 /* commit the transaction and return */
815
816 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
817 if (rc)
818 printf ("Unable to commit transaction");
819
820 return(rc);
821
822 }
823
824 int
825 odbcDB::runInsert(int queryNum, class wiscTimer *wT, int numBigTblTups,
826 char *Big1TblName, char *Big2TblName)
827 {
828 char* ins = "insert into %s values (%d, %d, 0, 2, 0, 10, 50,\
829 688, 1950, 4950, 9950, 1, 100,\
830 'MxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxC',\
831 'GxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxA',\
832 'OxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxO')";
833 char buf[512];
834 int val = numBigTblTups + 1;
835 int i;
836 int recordQuery = 1;
837 SQLHSTMT hstmtA[MAXHSTMT];
838 SQLRETURN rc = SQL_SUCCESS;
839
840 /* perform this operation whether specified by user or not, in case
841 * it affects subsequent queries; however, do not record it if not
842 * specified by user */
843
844 if (!isQuery(queryNum))
845 return 0;
846
847 /* print the query number */
848
849 if (verboseOut)
850 printf ("Query %d:\n", queryNum);
851
852 /* Alloc statement handles, compile insert statements and bind
853 * parameters */
854
855 for (i = 0; i < 5*RPTS; i++, val++) {
856 rc = 0;
857
858 sprintf(buf, ins, Big1TblName, val, val);
859
860 rc += SQLAllocStmt(hdbc, &hstmtA[i*2]);
861
862 rc += SQLPrepare(hstmtA[i*2],(SQLCHAR *)buf, SQL_NTS );
863
864 sprintf(buf, ins, Big2TblName, val, val);
865
866 rc += SQLAllocStmt(hdbc, &hstmtA[i*2+1]);
867
868 rc += SQLPrepare(hstmtA[i*2+1],(SQLCHAR *)buf, SQL_NTS );
869
870 if (rc)
871 printf ("Alloc and prepare failed\n");
872
873 } /* end for */
874
875 /* commit the transaction */
876
877 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
878 if (rc)
879 printf ("Commit failed\n");
880
881 /* execute the insert statement 10*RPT times */
882
883 for (i = 0; i < 10*RPTS; i++) {
884
885 wT->startTimer();
886
887 rc = SQLExecute(hstmtA[i]);
888 if (rc)
889 printf ("Execute now\n");
890
891 rc = SQLTransact(henv,hdbc,SQL_COMMIT);
892
893 wT->stopTimer();
894
895 if (rc)
896 printf ("Commit failed\n");
897
898 }
899
900 for (i=0; i<10*RPTS; i++) {
901
902 rc = SQLFreeStmt(hstmtA[i], SQL_DROP);
903 if (rc)
904 printf ("Free Stmt failed\n");
905 }
906
907 rc = SQLTransact(henv, hdbc, SQL_COMMIT);
908 if (rc)
909 printf ("Unable to commit transaction\n");
910
911 return(rc);
912
913 }
914
915
916 int odbcDB::disconnect()
917 {
918 int rc;
919
920 if (verboseOut)
921 printf ("Disconnecting from the data source...\n");
922
923 rc = SQLTransact(henv,hdbc,SQL_COMMIT);
924 if (rc)
925 printf ("Unable to commit transaction\n");
926
927 rc = SQLDisconnect(hdbc);
928 if (rc)
929 printf ("Unable to disconnect\n");
930
931 rc = SQLFreeConnect(hdbc);
932 if (rc)
933 printf ("Unable to free connection handle");
934
935 rc = SQLFreeEnv(henv);
936 if (rc)
937 printf ("Unable to free environment handle");
938
939 return rc;
940 }
941
942 static int immedExecute (char* cmd)
943 {
944 SQLRETURN rc;
945 SQLHSTMT lhstmt;
946 rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
947 if (rc != SQL_SUCCESS)
948 printf ("immedExecute: SQLAllocHandle failed with retCode=%d %s:%d\n", rc, __FILE__, __LINE__);
949
950 /* Execute the command */
951 rc = SQLPrepare(hstmt, (SQLCHAR *) cmd, SQL_NTS);
952 if (rc)
953 printf ("Prepare of (%s) failed\n", cmd);
954 rc = SQLExecute(hstmt);
955 if (rc)
956 printf ("Execute of (%s) failed\n", cmd);
957
958 /* Commit the transaction */
959
960 //rc = SQLTransact(henv, hdbc, SQL_COMMIT);
961 if (rc)
962 printf ("Unable to commit transaction");
963
964 /* Close associated cusssor and drop pending results */
965 printf("SQL Statement executed %s\n", (char*)cmd);
966 //rc = SQLFreeStmt(hstmt, SQL_CLOSE);
967 if (rc)
968 printf ("Unable to SQL_CLOSE statement handle.\n");
969
970 return rc;
971 }
972
973 int
974 odbcDB::emptyTable (char *tblName)
975 {
976 char buf[128];
977
978 sprintf (buf, "delete from %s", tblName);
979
980 return immedExecute (buf);
981 }
982
983 int
984 odbcDB::createTable (char *tblName)
985 {
986 char buf[1024];
987
988 sprintf (buf, "create table %s "
989 "(unique1 integer not null, unique2 integer not null, "
990 "two integer not null, four integer not null, "
991 "ten integer not null, twenty integer not null, "
992 "onepercent integer not null, tenpercent integer not null, "
993 "twentypercent integer not null, fiftypercent integer not null, "
994 "unique3 integer not null, evenonepercent integer not null, "
995 "oddonepercent integer not null, stringu1 char(52) not null, "
996 "stringu2 char(52) not null, string4 char(52) not null)",
997 tblName);
998
999 return immedExecute (buf);
1000 }
1001
1002 int
1003 odbcDB::dropTable (char *tblName)
1004 {
1005 char buf[128];
1006
1007 sprintf (buf, "drop table %s", tblName);
1008
1009 return immedExecute (buf);
1010 }
1011
1012 int
1013 odbcDB::createIndex (char *idx, char *tblName, char *fld)
1014 {
1015 char buf[128];
1016 if (strcmp(fld, "unique2") == 0) {
1017 sprintf (buf, "create index %s on %s (%s)",
1018 idx, tblName, fld);
1019 }else {
1020 sprintf (buf, "create index %s on %s (%s) tree",
1021 idx, tblName, fld);
1022
1023 }
1024
1025 return immedExecute (buf);
1026 }
1027
1028 int
1029 odbcDB::dropIndex (char *idx, char *tblName)
1030 {
1031 char buf[128];
1032
1033 sprintf (buf, "drop index %s", idx);
1034
1035 return immedExecute (buf);
1036 }
Main memory database SQL engine (cvs import)