test/sqlapi/Select/aggseltest14.c

   1 /*  create table T1 with 10 fields.
   2  *  insert some records into it with null values
   3  *
   4  *
   5  *  select * from T1;
   6
   7  */
   8
   9 #include"common.h"
  10
  11 int main()
  12 {
  13     DbRetVal rv = OK;
  14     AbsSqlConnection *con =  createConnection();
  15     rv = con->connect("root","manager");
  16     if(rv!=OK)return 1;
  17     AbsSqlStatement *stmt = createStatement();
  18     stmt->setConnection(con);
  19     char statement[200];
  20     strcpy(statement,"CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 INT,F10 BIGINT, F11 BINARY(4));");
  21     int rows = 0;
  22     rv = stmt->prepare(statement);
  23     if(rv!=OK) { delete stmt; delete con; return 2; }
  24     rv = stmt->execute(rows);
  25     if(rv!=OK) { delete stmt; delete con; return 3; }
  26     stmt->free();
  27     printf("Table T1 created\n");
  28     // insert into table
  29     strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
  30     int f1var = 0;
  31     short int f2var = 1;
  32     char f3var[32]="jitendra";
  33     float f4var = 5.5;
  34     float f5var = 10.50;
  35     Date f6var;
  36     f6var.set(2007,01,21);
  37     Time f7var;
  38     f7var.set(12,29,30);
  39     TimeStamp f8var;
  40     f8var.setDate(2007,01,21);
  41     f8var.setTime(12,29,30);
  42     int f9var = 20;
  43     long long f10var = 12000;
  44     char f11var[12]="23fe";
  45     rv = stmt->prepare(statement);
  46     if(rv!=OK) { delete stmt; delete con; return 4; }
  47     int count=0;
  48     for(int i=0;i<10;i++) {
  49             rv  = con->beginTrans();
  50             if(rv!=OK) break;
  51             f1var=i;
  52         stmt->setIntParam(1,f1var);
  53         stmt->setShortParam(2,f2var);
  54         stmt->setStringParam(3,f3var);
  55         stmt->setFloatParam(4,f4var);
  56         stmt->setNull(5);
  57         stmt->setDateParam(6,f6var);
  58         stmt->setNull(7);
  59         stmt->setTimeStampParam(8,f8var);
  60         stmt->setIntParam(9,f9var);
  61         stmt->setLongLongParam(10,f10var);
  62         stmt->setBinaryParam(11,f11var, 12);
  63         rv = stmt->execute(rows);
  64         if(rv!=OK)break;
  65         rv = con->commit();
  66         if(rv!=OK)break;
  67         count++;
  68     }
  69     stmt->free();
  70     //*********************************************************
  71     //  Insert with value list records from the T1
  72     strcpy(statement,"INSERT INTO T1(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) VALUES(?,?,?,?,?,?,?,?,?,?);");
  73     rv = stmt->prepare(statement);
  74     if(rv!=OK) { delete stmt; delete con; return 4; }
  75     for(int i=0;i<10;i++) {
  76             rv  = con->beginTrans();
  77             if(rv!=OK) break;
  78             f1var=i;
  79         stmt->setNull(1);
  80         stmt->setShortParam(2,f2var);
  81         stmt->setStringParam(3,f3var);
  82         stmt->setFloatParam(4,f4var);
  83         stmt->setNull(5);
  84         stmt->setDateParam(6,f6var);
  85         stmt->setNull(7);
  86         stmt->setTimeStampParam(8,f8var);
  87         stmt->setIntParam(9,f9var);
  88         stmt->setLongLongParam(10,f10var);
  89         rv = stmt->execute(rows);
  90         if(rv!=OK)break;
  91         rv = con->commit();
  92         if(rv!=OK)break;
  93         count++;
  94     }
  95     printf("Total rows inserted %d\n",count);
  96     stmt->free();
  97     int cnt = 0;
  98     int max = 0;
  99     int min = 0;
 100     double avg = 0;
 101     long long sum = 0;
 102     //**********************************************************
 103     // SELECT * FROM T1;
 104     strcpy(statement,"SELECT count(F1), MAX(F1), MIN(F2), avg(F9), sum(F10) FROM T1;");
 105     rv = stmt->prepare(statement);
 106     if(rv!=OK) { delete stmt; delete con; return 5; }
 107     stmt->bindField(1,&cnt);
 108     stmt->bindField(2,&max);
 109     stmt->bindField(3,&min);
 110     stmt->bindField(4,&avg);
 111     stmt->bindField(5,&sum);
 112     count=0;
 113     rv = con->beginTrans();
 114     if(rv!=OK)return 6;
 115     stmt->execute(rows);
 116     while(stmt->fetch() !=NULL) {
 117         printf("select count(*) from T1 = %d\n", cnt);
 118         printf("select max(F1) from T1 = %d\n", max);
 119         printf("select min(F2) from T1 = %d\n", min);
 120         printf("select avg(F9) from T1 = %lf\n", avg);
 121         printf("select sum(F10) from T1 = %d\n", sum);
 122         count++;
 123     }
 124     stmt->close();
 125     rv = con->commit();
 126     if(rv !=OK) { delete stmt; delete con; return 7; }
 127     printf("Total row fetched=%d\n",count);
 128     stmt->free();
 129    strcpy(statement,"SELECT F1,F2,F3,F4,F5 FROM T1;");
 130     rv = stmt->prepare(statement);
 131     if(rv!=OK) { delete stmt; delete con; return 5; }
 132     stmt->bindField(1,&f1var);
 133     stmt->bindField(2,&f2var);
 134     stmt->bindField(3,f3var);
 135     stmt->bindField(4,&f4var);
 136     stmt->bindField(5,&f5var);
 137     count=0;
 138     rv = con->beginTrans();
 139     if(rv!=OK)return 6;
 140     stmt->execute(rows);
 141     while(stmt->fetch() !=NULL) {
 142         if(stmt->isFldNull("F1")) printf("F1=NULL | ");
 143         else printf("F1=%d | ", f1var);
 144         if(stmt->isFldNull("F2")) printf("F2=NULL | ");
 145         else printf("F2=%hd | ", f2var);
 146         if(stmt->isFldNull("F3")) printf("F3=NULL | ");
 147         else printf("F3=%s | ", f3var);
 148         if(stmt->isFldNull("F4")) printf("F4=NULL | ");
 149         else printf("F4=%f | ", f4var);
 150         if(stmt->isFldNull("F5")) printf("F5=NULL | ");
 151         else printf("F5=%f | ", f5var);
 152         printf("\n");
 153         count++;
 154     }
 155     stmt->close();
 156     rv = con->commit();
 157     if(rv !=OK) { delete stmt; delete con; return 7; }
 158     printf("Total row fetched=%d\n",count);
 159     stmt->free();
 160 //==========================
 161
 162     strcpy(statement,"DROP TABLE T1;");
 163     rv = stmt->prepare(statement);
 164     if(rv!=OK) { delete stmt; delete con; return 8; }
 165     rv = stmt->execute(rows);
 166     if(rv!=OK) { delete stmt; delete con; return 9; }
 167     printf("Table dropped\n");
 168     stmt->free(); delete stmt; delete con;
 169     return 0;
 170 }