test/sqlapi/Csql/DMLStmt/deletealloperatoronalldatatype.c

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