test/sqlapi/Csql/DMLStmt/alldmlonalldatatype.c

   1 /*
   2   Test BASIC DML statement with no parameters (Insert, Update, Delete, Select for all datatypes).
   3   CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP);
   4   Insert 5 records into t1.
   5   INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');
   6   INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');
   7   INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03','2003-03-03 03:03:03');
   8   INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.44,4444.44,'2004-04-04','04:04:04','2004-04-04 04:04:04');
   9   INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.55,5555.55,'2005-05-05','05:05:05','2005-05-05 05:05:05');
  10   UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6='LAKSHYA_BANGALORE', f7=f7+100, f8=f8+10000,f9='2009-11-20', f10='12:01:01', f11='2009-11-20 12:01:01' WHERE f1<=3;
  11   DELETE FROM t1 WHERE f1=12 OR f1=4;
  12   SELECT * FROM t1;
  13 */
  14 #include"common.h"
  15
  16 int main()
  17 {
  18     DbRetVal rv = OK;
  19     AbsSqlConnection *con = createConnection();
  20     rv = con->connect("root","manager");
  21     if(rv !=OK) {
  22         delete con;
  23         return 1;
  24     }
  25     printf("Connection opened\n");
  26     AbsSqlStatement *stmt = createStatement();
  27     stmt->setConnection(con);
  28 //Creating Table
  29     char statement[400];
  30     printf("CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP);\n");
  31     strcpy(statement,"CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP);");
  32     int rows=0;
  33     rv = stmt->prepare(statement);
  34     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  35     rv = stmt->execute(rows);
  36     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  37     stmt->free();
  38 // Show all tables
  39     strcpy(statement,"GETALLTABLES;");
  40     rows=0;
  41     rv = stmt->prepare(statement);
  42     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  43     stmt->execute(rows);
  44     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  45     while(stmt->next() !=NULL) {
  46         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  47     }
  48     stmt->free();
  49
  50 //Inserting Records
  51     con->beginTrans();
  52     rows=0;
  53     printf("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');\n");
  54     stmt->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
  55     stmt->execute(rows);
  56     printf("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');\n");
  57     stmt->prepare("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
  58     stmt->execute(rows);
  59     printf("INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03','2003-03-03 03:03:03');\n");
  60     stmt->prepare("INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03','2003-03-03 03:03:03');");
  61     stmt->execute(rows);
  62     printf("INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.44,4444.44,'2004-04-04','04:04:04','2004-04-04 04:04:04');\n");
  63     stmt->prepare("INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.44,4444.44,'2004-04-04','04:04:04','2004-04-04 04:04:04');");
  64     stmt->execute(rows);
  65     printf("INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.55,5555.55,'2005-05-05','05:05:05','2005-05-05 05:05:05');\n");
  66     stmt->prepare("INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.55,5555.55,'2005-05-05','05:05:05','2005-05-05 05:05:05');");
  67     stmt->execute(rows);
  68     con->commit();
  69     stmt->free();
  70
  71     char f1var;
  72     short int f2var;
  73     int f3var;
  74     long long f4var;
  75     char f5var[20];
  76     char f6var[32];
  77     float f7var;
  78     double f8var;
  79     Date f9var;
  80     Time f10var;
  81     TimeStamp f11var;
  82
  83 //Fetching records after insert
  84     strcpy(statement,"SELECT * FROM t1;");
  85     rv = stmt->prepare(statement);
  86     if(rv!=OK) { delete stmt; delete con; return 6; }
  87     stmt->bindField(1,&f1var);
  88     stmt->bindField(2,&f2var);
  89     stmt->bindField(3,&f3var);
  90     stmt->bindField(4,&f4var);
  91     stmt->bindField(5,f5var);
  92     stmt->bindField(6,f6var);
  93     stmt->bindField(7,&f7var);
  94     stmt->bindField(8,&f8var);
  95     stmt->bindField(9,&f9var);
  96     stmt->bindField(10,&f10var);
  97     stmt->bindField(11,&f11var);
  98     int count=0;
  99     rv = con->beginTrans();
 100     if(rv!=OK)return 7;
 101     stmt->execute(rows);
 102     while(stmt->fetch() !=NULL) {
 103         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 104         else printf("f1(tinyint)=%d | ", f1var);
 105         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 106         else printf("f2(smallint)=%d | ", f2var);
 107         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 108         else printf("f3(int)=%d | ", f3var);
 109         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 110         else printf("f4(bigint)=%lld | ", f4var);
 111         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 112         else printf("f5(char)=%s | ", f5var);
 113         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 114         else printf("f6(varchar)=%s | ", f6var);
 115         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 116         else printf("f7(float)=%f | ", f7var);
 117         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 118         else printf("f8(double)=%lf | ", f8var);
 119         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 120         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 121         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 122         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 123         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 124         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 125         printf("\n");
 126         count++;
 127     }
 128     stmt->free();
 129     rv = con->commit();
 130     printf("%d rows selected\n",count);
 131
 132
 133 //Updating records.
 134     con->beginTrans();
 135     printf("UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6='LAKSHYA_BANGALORE', f7=f7+100, f8=f8+10000,f9='2009-11-20', f10='12:01:01', f11='2009-11-20 12:01:01' WHERE f1<=3;\n");
 136     strcpy(statement,"UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6='LAKSHYA_BANGALORE', f7=f7+100, f8=f8+10000,f9='2009-11-20', f10='12:01:01', f11='2009-11-20 12:01:01' WHERE f1<=3;");
 137     rv = stmt->prepare(statement);
 138     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
 139     rv = stmt->execute(rows);
 140     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
 141     con->commit();
 142     stmt->free();
 143
 144 //Fetching records after insert
 145     strcpy(statement,"SELECT * FROM t1;");
 146     rv = stmt->prepare(statement);
 147     if(rv!=OK) { delete stmt; delete con; return 6; }
 148     stmt->bindField(1,&f1var);
 149     stmt->bindField(2,&f2var);
 150     stmt->bindField(3,&f3var);
 151     stmt->bindField(4,&f4var);
 152     stmt->bindField(5,f5var);
 153     stmt->bindField(6,f6var);
 154     stmt->bindField(7,&f7var);
 155     stmt->bindField(8,&f8var);
 156     stmt->bindField(9,&f9var);
 157     stmt->bindField(10,&f10var);
 158     stmt->bindField(11,&f11var);
 159     count=0;
 160     rv = con->beginTrans();
 161     if(rv!=OK)return 7;
 162     stmt->execute(rows);
 163     while(stmt->fetch() !=NULL) {
 164         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 165         else printf("f1(tinyint)=%d | ", f1var);
 166         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 167         else printf("f2(smallint)=%d | ", f2var);
 168         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 169         else printf("f3(int)=%d | ", f3var);
 170         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 171         else printf("f4(bigint)=%lld | ", f4var);
 172         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 173         else printf("f5(char)=%s | ", f5var);
 174         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 175         else printf("f6(varchar)=%s | ", f6var);
 176         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 177         else printf("f7(float)=%f | ", f7var);
 178         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 179         else printf("f8(double)=%lf | ", f8var);
 180         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 181         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 182         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 183         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 184         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 185         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 186         printf("\n");
 187         count++;
 188     }
 189     stmt->free();
 190     rv = con->commit();
 191     printf("%d rows selected\n",count);
 192
 193 //Deleting Records.
 194     con->beginTrans();
 195     printf("DELETE FROM t1 WHERE f1=12 OR f1=4;\n");
 196     strcpy(statement,"DELETE FROM t1 WHERE f1=12 OR f1=4;");
 197     rv = stmt->prepare(statement);
 198     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
 199     rv = stmt->execute(rows);
 200     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
 201     con->commit();
 202     stmt->free();
 203
 204 //Fetching records after insert
 205     strcpy(statement,"SELECT * FROM t1;");
 206     rv = stmt->prepare(statement);
 207     if(rv!=OK) { delete stmt; delete con; return 6; }
 208     stmt->bindField(1,&f1var);
 209     stmt->bindField(2,&f2var);
 210     stmt->bindField(3,&f3var);
 211     stmt->bindField(4,&f4var);
 212     stmt->bindField(5,f5var);
 213     stmt->bindField(6,f6var);
 214     stmt->bindField(7,&f7var);
 215     stmt->bindField(8,&f8var);
 216     stmt->bindField(9,&f9var);
 217     stmt->bindField(10,&f10var);
 218     stmt->bindField(11,&f11var);
 219     count=0;
 220     rv = con->beginTrans();
 221     if(rv!=OK)return 7;
 222     stmt->execute(rows);
 223     while(stmt->fetch() !=NULL) {
 224         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 225         else printf("f1(tinyint)=%d | ", f1var);
 226         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 227         else printf("f2(smallint)=%d | ", f2var);
 228         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 229         else printf("f3(int)=%d | ", f3var);
 230         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 231         else printf("f4(bigint)=%lld | ", f4var);
 232         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 233         else printf("f5(char)=%s | ", f5var);
 234         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 235         else printf("f6(varchar)=%s | ", f6var);
 236         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 237         else printf("f7(float)=%f | ", f7var);
 238         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 239         else printf("f8(double)=%lf | ", f8var);
 240         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 241         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 242         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 243         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 244         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 245         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 246         printf("\n");
 247         count++;
 248     }
 249     stmt->free();
 250     rv = con->commit();
 251     printf("%d rows selected\n",count);
 252
 253 //Droping table
 254     strcpy(statement,"DROP TABLE t1;");
 255     rv = stmt->prepare(statement);
 256     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
 257     rv = stmt->execute(rows);
 258     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
 259     printf("Table dropped\n");
 260     stmt->free();
 261     con->disconnect();
 262     printf("Connection Closed\n");
 263
 264     delete stmt; delete con;
 265     return 0;
 266 }
 267