test/adapter/Select/singleconnadapter.c

   1 /*One connection working with multiple statements
   2  *create table T1 with two fields,
   3  *One connection working with multiple statements simultaneously for csql,
   4  *Create conn
   5  *create 3 stmts for inserting into 5 tables and set the same connection
   6  *insert into all 3 tables and commit the transaction
   7  *free all stmts
   8  *select the records from 3 tables in same connection
   9  *free all stmt;
  10  *Author : Nihar Paital
  11  */
  12
  13 #include"common.h"
  14
  15 int main()
  16 {
  17     DbRetVal rv = OK;
  18     AbsSqlConnection *con = createConnection();
  19     rv  = con->connect("root","manager");
  20     if(rv!=OK)return 1;
  21     AbsSqlStatement *stmt1 = createStatement();
  22     AbsSqlStatement *stmt2 = createStatement();
  23     AbsSqlStatement *stmt3 = createStatement();
  24     char statement[200];
  25
  26     stmt1->setConnection(con);
  27     strcpy(statement,"CREATE TABLE T1(F1 INT,F2 CHAR(20));");
  28     int rows=0;
  29     rv = stmt1->prepare(statement);
  30     if(rv!=OK) { delete stmt1; delete con; return 1; }
  31     rv = stmt1->execute(rows);
  32     if(rv!=OK) { delete stmt1; delete con; return 2; }
  33     printf("Table created\n");
  34
  35     stmt2->setConnection(con);
  36     strcpy(statement,"CREATE TABLE T2(F1 INT,F2 CHAR(20));");
  37     rows=0;
  38     rv = stmt2->prepare(statement);
  39     if(rv!=OK) { delete stmt2; delete con; return 3; }
  40     rv = stmt2->execute(rows);
  41     if(rv!=OK) { delete stmt2; delete con; return 4; }
  42     printf("Table created\n");
  43
  44     stmt3->setConnection(con);
  45     strcpy(statement,"CREATE TABLE T3(F1 INT,F2 CHAR(20));");
  46     rows=0;
  47     rv = stmt3->prepare(statement);
  48     if(rv!=OK) { delete stmt3; delete con; return 5; }
  49     rv = stmt3->execute(rows);
  50     if(rv!=OK) { delete stmt3; delete con; return 6; }
  51     printf("Table created\n");
  52     stmt1->free();
  53     stmt2->free();
  54     stmt3->free();
  55
  56 //  insert records into T1
  57     int f1varT1 = 1;
  58     char f2varT1[24] = "lakshya";
  59     printf("Inserting records into table T1\n");
  60     strcpy(statement,"INSERT INTO T1 VALUES(?,?);");
  61     rv = stmt1->prepare(statement);
  62     if(rv!=OK) { delete stmt1; delete con; return 3; }
  63     int count=0;
  64     rv = con->beginTrans();
  65     for(int j=0;j<5;j++) {
  66             f1varT1 = j;
  67             if(rv!=OK)break;
  68             stmt1->setIntParam(1,f1varT1);
  69             stmt1->setStringParam(2,f2varT1);
  70             rv = stmt1->execute(rows);
  71             if(rv!=OK)break;
  72             count++;
  73     }
  74     printf("%d rows inserted into table T1\n",count);
  75
  76 //  insert records into T2
  77     int f1varT2 = 1;
  78     char f2varT2[24] = "lakshya";
  79     printf("Inserting records into table T2\n");
  80     strcpy(statement,"INSERT INTO T2 VALUES(?,?);");
  81     rv = stmt2->prepare(statement);
  82     if(rv!=OK) { delete stmt2; delete con; return 3; }
  83     count=0;
  84     for(int j=5;j<10;j++) {
  85             f1varT2 = j;
  86             if(rv!=OK)break;
  87             stmt2->setIntParam(1,f1varT2);
  88             stmt2->setStringParam(2,f2varT2);
  89             rv = stmt2->execute(rows);
  90             if(rv!=OK)break;
  91             count++;
  92     }
  93     printf("%d rows inserted into table T2\n",count);
  94
  95 //  insert records into T3
  96     int f1varT3 = 1;
  97     char f2varT3[24] = "lakshya";
  98     printf("Inserting records into table T3\n");
  99     strcpy(statement,"INSERT INTO T3 VALUES(?,?);");
 100     rv = stmt3->prepare(statement);
 101     if(rv!=OK) { delete stmt3; delete con; return 3; }
 102     count=0;
 103     for(int j=10;j<15;j++) {
 104             f1varT3 = j;
 105             if(rv!=OK)break;
 106             stmt3->setIntParam(1,f1varT3);
 107             stmt3->setStringParam(2,f2varT3);
 108             rv = stmt3->execute(rows);
 109             if(rv!=OK)break;
 110             count++;
 111     }
 112     printf("%d rows inserted into table T3\n",count);
 113     rv = con->commit();
 114
 115     stmt1->free();
 116     stmt2->free();
 117     stmt3->free();
 118 //*******************************************
 119     printf("SELECT * FROM T1;\n");
 120     strcpy(statement,"SELECT * FROM T1;");
 121     rv = stmt1->prepare(statement);
 122     if(rv !=OK) { delete stmt1; delete con; return 4; }
 123     stmt1->bindField(1,&f1varT1);
 124     stmt1->bindField(2,f2varT1);
 125     void *ret;
 126     count = 0;
 127     rv = con->beginTrans();
 128     if(rv!=OK)return 5;
 129     printf("T1.f1\tT1.f2\n");
 130     stmt1->execute(rows);
 131     while((char*)stmt1->fetch()) {
 132         printf("%d\t%s\n",f1varT1,f2varT1);
 133         if(ret==NULL) {
 134                 break;
 135         }
 136     }
 137
 138     printf("SELECT * FROM T2;\n");
 139     strcpy(statement,"SELECT * FROM T2;");
 140     rv = stmt2->prepare(statement);
 141     if(rv !=OK) { delete stmt2; delete con; return 4; }
 142     stmt2->bindField(1,&f1varT2);
 143     stmt2->bindField(2,f2varT2);
 144     count = 0;
 145     stmt2->execute(rows);
 146     printf("T2.f1\tT2.f2\n");
 147     while((char*)stmt2->fetch()) {
 148         printf("%d\t%s\n",f1varT2,f2varT2);
 149         if(ret==NULL) {
 150                break;
 151         }
 152     }
 153
 154     printf("SELECT * FROM T3;\n");
 155     strcpy(statement,"SELECT * FROM T3;");
 156     rv = stmt3->prepare(statement);
 157     if(rv !=OK) { delete stmt3; delete con; return 4; }
 158     stmt3->bindField(1,&f1varT3);
 159     stmt3->bindField(2,f2varT3);
 160     count = 0;
 161     stmt3->execute(rows);
 162     printf("T3.f1\tT3.f2\n");
 163     while((char*)stmt3->fetch()) {
 164         printf("%d\t%s\n",f1varT3,f2varT3);
 165         if(ret==NULL) {
 166                break;
 167         }
 168     }
 169     con->commit();
 170
 171     strcpy(statement,"DROP TABLE T1;");
 172     rv = stmt1->prepare(statement);
 173     rv = stmt1->execute(rows);
 174     if(rv==OK){printf("Table T1 dropped successfully\n");}
 175
 176     strcpy(statement,"DROP TABLE T2;");
 177     rv = stmt2->prepare(statement);
 178     rv = stmt2->execute(rows);
 179     if(rv==OK){printf("Table T2 dropped successfully\n");}
 180
 181     strcpy(statement,"DROP TABLE T3;");
 182     rv = stmt3->prepare(statement);
 183     rv = stmt3->execute(rows);
 184     if(rv==OK){printf("Table T3 dropped successfully\n");}
 185
 186     stmt1->free(); delete stmt1;
 187     stmt2->free(); delete stmt2;
 188     stmt3->free(); delete stmt3;
 189     con->commit();
 190     con->disconnect();
 191     delete con;
 192     return 0;
 193 }