test/sqlapi/Csql/DMLStmt/updlogicalonalldatatype.c

   1 /*
   2   Testing update on all datatypes using logical operators
   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   UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB1', f6='LAKSHYA_BANGALORE1', f7=f7+100, f8=f8+10000,f9='2001-01-11', f10='01:01:11', f11='2001-01-11 01:01:11' WHERE f1=1 and f2=11 and f3=111 and f4=1111;
  11   UPDATE t1 SET f1=f1+20, f2=f2+200, f3=f3+2000, f4=f4+20000, f5='CSQLMMDB2', f6='LAKSHYA_BANGALORE2', f7=f7+200, f8=f8+20000,f9='2002-02-12', f10='02:02:12', f11='2002-02-12 02:02:12' WHERE f3=222 and f5='CSQL2' and f6<='LAKSHYA2';
  12   UPDATE t1 SET f1=f1+30, f2=f2+300, f3=f3+3000, f4=f4+30000, f5='CSQLMMDB3', f6='LAKSHYA_BANGALORE3', f7=f7+300, f8=f8+30000,f9='2003-03-13', f10='03:03:13', f11='2003-03-13 03:03:13' WHERE f3<=333 and f6<='LAKSHYA4' and (f3 = 333 or f3 = 2222);
  13   UPDATE t1 SET f1=f1+40, f2=f2+400, f3=f3+4000, f4=f4+40000, f5='CSQLMMDB4', f6='LAKSHYA_BANGALORE4', f7=f7+400, f8=f8+40000,f9='2004-04-14', f10='04:04:14', f11='2004-04-14 04:04:14' WHERE f3<=3333 and f6<='LAKSHYA6' and not(f3 = 3333 or f3 = 555) and (f6 = 'LAKSHYA4' or f6='LAKSHYA_BANGALORE3' or f9='2004/4/4');
  14   UPDATE t1 SET f1=f1+50, f2=f2+500, f3=f3+5000, f4=f4+50000, f5='CSQLMMDB5', f6='LAKSHYA_BANGALORE5', f7=f7+500, f8=f8+50000,f9='2005-05-15', f10='05:05:15', f11='2005-05-15 05:05:15' WHERE f1=5 and f2=55 and f3=555 and f4=5555 and f5='CSQL5' and f6='LAKSHYA5' and f7=55 and f8=5555 and f9='2005/05/05' and f10='05:05:05' and f11='2005/05/05 05:05:05';
  15   UPDATE t1 SET f1=50, f2=500, f3=5000, f4=50000, f5='CSQLMMDB50', f6='LAKSHYA_BANGALORE50', f7=500, f8=50000,f9='2005-05-15', f10='05:05:15', f11='2005-05-15 05:05:15' WHERE f3=5555 or f4=44444 or f6='LAKSHYA_BANGALORE3' or f8=22222 or f11='2001/01/11 01:01:11';
  16   SELECT * FROM t1;
  17 */
  18 #include"common.h"
  19
  20 int main()
  21 {
  22     DbRetVal rv = OK;
  23     AbsSqlConnection *con = createConnection();
  24     rv = con->connect("root","manager");
  25     if(rv !=OK) {
  26         delete con;
  27         return 1;
  28     }
  29     printf("Connection opened\n");
  30     AbsSqlStatement *stmt = createStatement();
  31     stmt->setConnection(con);
  32 //Creating Table
  33     char statement[400];
  34     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");
  35     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);");
  36     int rows=0;
  37     rv = stmt->prepare(statement);
  38     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  39     rv = stmt->execute(rows);
  40     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  41     stmt->free();
  42 // Show all tables
  43     strcpy(statement,"GETALLTABLES;");
  44     rows=0;
  45     rv = stmt->prepare(statement);
  46     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  47     stmt->execute(rows);
  48     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  49     while(stmt->next() !=NULL) {
  50         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  51     }
  52     stmt->free();
  53
  54 //Inserting Records
  55     con->beginTrans();
  56     rows=0;
  57     printf("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');\n");
  58     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');");
  59     stmt->execute(rows);
  60     printf("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');\n");
  61     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');");
  62     stmt->execute(rows);
  63     printf("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');\n");
  64     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');");
  65     stmt->execute(rows);
  66     printf("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');\n");
  67     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');");
  68     stmt->execute(rows);
  69     printf("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');\n");
  70     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');");
  71     stmt->execute(rows);
  72     con->commit();
  73     stmt->free();
  74
  75     char f1var;
  76     short int f2var;
  77     int f3var;
  78     long long f4var;
  79     char f5var[20];
  80     char f6var[32];
  81     float f7var;
  82     double f8var;
  83     Date f9var;
  84     Time f10var;
  85     TimeStamp f11var;
  86
  87 //Fetching records after insert
  88     strcpy(statement,"SELECT * FROM t1;");
  89     rv = stmt->prepare(statement);
  90     if(rv!=OK) { delete stmt; delete con; return 6; }
  91     stmt->bindField(1,&f1var);
  92     stmt->bindField(2,&f2var);
  93     stmt->bindField(3,&f3var);
  94     stmt->bindField(4,&f4var);
  95     stmt->bindField(5,f5var);
  96     stmt->bindField(6,f6var);
  97     stmt->bindField(7,&f7var);
  98     stmt->bindField(8,&f8var);
  99     stmt->bindField(9,&f9var);
 100     stmt->bindField(10,&f10var);
 101     stmt->bindField(11,&f11var);
 102     int count=0;
 103     rv = con->beginTrans();
 104     if(rv!=OK)return 7;
 105     stmt->execute(rows);
 106     while(stmt->fetch() !=NULL) {
 107         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 108         else printf("f1(tinyint)=%d | ", f1var);
 109         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 110         else printf("f2(smallint)=%d | ", f2var);
 111         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 112         else printf("f3(int)=%d | ", f3var);
 113         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 114         else printf("f4(bigint)=%lld | ", f4var);
 115         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 116         else printf("f5(char)=%s | ", f5var);
 117         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 118         else printf("f6(varchar)=%s | ", f6var);
 119         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 120         else printf("f7(float)=%f | ", f7var);
 121         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 122         else printf("f8(double)=%lf | ", f8var);
 123         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 124         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 125         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 126         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 127         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 128         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 129         printf("\n");
 130         count++;
 131     }
 132     stmt->free();
 133     rv = con->commit();
 134     printf("%d rows selected\n",count);
 135
 136 //Updating records.
 137     con->beginTrans();
 138     printf("UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB1', f6='LAKSHYA_BANGALORE1', f7=f7+100, f8=f8+10000,f9='2001-01-11', f10='01:01:11', f11='2001-01-11 01:01:11' WHERE f1=1 and f2=11 and f3=111 and f4=1111;\n");
 139     strcpy(statement,"UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB1', f6='LAKSHYA_BANGALORE1', f7=f7+100, f8=f8+10000,f9='2001-01-11', f10='01:01:11', f11='2001-01-11 01:01:11' WHERE f1=1 and f2=11 and f3=111 and f4=1111;");
 140     rv = stmt->prepare(statement);
 141     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
 142     rv = stmt->execute(rows);
 143     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
 144     printf("UPDATE t1 SET f1=f1+20, f2=f2+200, f3=f3+2000, f4=f4+20000, f5='CSQLMMDB2', f6='LAKSHYA_BANGALORE2', f7=f7+200, f8=f8+20000,f9='2002-02-12', f10='02:02:12', f11='2002-02-12 02:02:12' WHERE f3=222 and f5='CSQL2' and f6<='LAKSHYA2';\n");
 145     strcpy(statement,"UPDATE t1 SET f1=f1+20, f2=f2+200, f3=f3+2000, f4=f4+20000, f5='CSQLMMDB2', f6='LAKSHYA_BANGALORE2', f7=f7+200, f8=f8+20000,f9='2002-02-12', f10='02:02:12', f11='2002-02-12 02:02:12' WHERE f3=222 and f5='CSQL2' and f6<='LAKSHYA2';");
 146     rv = stmt->prepare(statement);
 147     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
 148     rv = stmt->execute(rows);
 149     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
 150     printf("UPDATE t1 SET f1=f1+30, f2=f2+300, f3=f3+3000, f4=f4+30000, f5='CSQLMMDB3', f6='LAKSHYA_BANGALORE3', f7=f7+300, f8=f8+30000,f9='2003-03-13', f10='03:03:13', f11='2003-03-13 03:03:13' WHERE f3<=333 and f6<='LAKSHYA4' and (f3 = 333 or f3 = 2222);\n");
 151     strcpy(statement,"UPDATE t1 SET f1=f1+30, f2=f2+300, f3=f3+3000, f4=f4+30000, f5='CSQLMMDB3', f6='LAKSHYA_BANGALORE3', f7=f7+300, f8=f8+30000,f9='2003-03-13', f10='03:03:13', f11='2003-03-13 03:03:13' WHERE f3<=333 and f6<='LAKSHYA4' and (f3 = 333 or f3 = 2222);");
 152     rv = stmt->prepare(statement);
 153     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
 154     rv = stmt->execute(rows);
 155     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 11; }
 156     printf("UPDATE t1 SET f1=f1+40, f2=f2+400, f3=f3+4000, f4=f4+40000, f5='CSQLMMDB4', f6='LAKSHYA_BANGALORE4', f7=f7+400, f8=f8+40000,f9='2004-04-14', f10='04:04:14', f11='2004-04-14 04:04:14' WHERE f3<=3333 and f6<='LAKSHYA6' and not(f3 = 3333 or f3 = 555) and (f6 = 'LAKSHYA4' or f6='LAKSHYA_BANGALORE3' or f9='2004/4/4');\n");
 157     strcpy(statement,"UPDATE t1 SET f1=f1+40, f2=f2+400, f3=f3+4000, f4=f4+40000, f5='CSQLMMDB4', f6='LAKSHYA_BANGALORE4', f7=f7+400, f8=f8+40000,f9='2004-04-14', f10='04:04:14', f11='2004-04-14 04:04:14' WHERE f3<=3333 and f6<='LAKSHYA6' and not(f3 = 3333 or f3 = 555) and (f6 = 'LAKSHYA4' or f6='LAKSHYA_BANGALORE3' or f9='2004/4/4');");
 158     rv = stmt->prepare(statement);
 159     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
 160     rv = stmt->execute(rows);
 161     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
 162     printf("UPDATE t1 SET f1=f1+50, f2=f2+500, f3=f3+5000, f4=f4+50000, f5='CSQLMMDB5', f6='LAKSHYA_BANGALORE5', f7=f7+500, f8=f8+50000,f9='2005-05-15', f10='05:05:15', f11='2005-05-15 05:05:15' WHERE f1=5 and f2=55 and f3=555 and f4=5555 and f5='CSQL5' and f6='LAKSHYA5' and f7=55 and f8=5555 and f9='2005/05/05' and f10='05:05:05' and f11='2005/05/05 05:05:05';\n");
 163     strcpy(statement,"UPDATE t1 SET f1=f1+50, f2=f2+500, f3=f3+5000, f4=f4+50000, f5='CSQLMMDB5', f6='LAKSHYA_BANGALORE5', f7=f7+500, f8=f8+50000,f9='2005-05-15', f10='05:05:15', f11='2005-05-15 05:05:15' WHERE f1=5 and f2=55 and f3=555 and f4=5555 and f5='CSQL5' and f6='LAKSHYA5' and f7=55 and f8=5555 and f9='2005/05/05' and f10='05:05:05' and f11='2005/05/05 05:05:05';");
 164     rv = stmt->prepare(statement);
 165     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
 166     rv = stmt->execute(rows);
 167     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
 168     printf("UPDATE t1 SET f1=50, f2=500, f3=5000, f4=50000, f5='CSQLMMDB50', f6='LAKSHYA_BANGALORE50', f7=500, f8=50000,f9='2005-05-15', f10='05:05:15', f11='2005-05-15 05:05:15' WHERE f3=5555 or f4=44444 or f6='LAKSHYA_BANGALORE3' or f8=22222 or f11='2001/01/11 01:01:11';\n");
 169     strcpy(statement,"UPDATE t1 SET f1=50, f2=500, f3=5000, f4=50000, f5='CSQLMMDB50', f6='LAKSHYA_BANGALORE50', f7=500, f8=50000,f9='2005-05-15', f10='05:05:15', f11='2005-05-15 05:05:15' WHERE f3=5555 or f4=44444 or f6='LAKSHYA_BANGALORE3' or f8=22222 or f11='2001/01/11 01:01:11';");
 170     rv = stmt->prepare(statement);
 171     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 16; }
 172     rv = stmt->execute(rows);
 173     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 17; }
 174     con->commit();
 175     stmt->free();
 176
 177 //Fetching records after Update
 178     strcpy(statement,"SELECT * FROM t1;");
 179     rv = stmt->prepare(statement);
 180     if(rv!=OK) { delete stmt; delete con; return 18; }
 181     stmt->bindField(1,&f1var);
 182     stmt->bindField(2,&f2var);
 183     stmt->bindField(3,&f3var);
 184     stmt->bindField(4,&f4var);
 185     stmt->bindField(5,f5var);
 186     stmt->bindField(6,f6var);
 187     stmt->bindField(7,&f7var);
 188     stmt->bindField(8,&f8var);
 189     stmt->bindField(9,&f9var);
 190     stmt->bindField(10,&f10var);
 191     stmt->bindField(11,&f11var);
 192     count=0;
 193     rv = con->beginTrans();
 194     if(rv!=OK)return 7;
 195     stmt->execute(rows);
 196     while(stmt->fetch() !=NULL) {
 197         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 198         else printf("f1(tinyint)=%d | ", f1var);
 199         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 200         else printf("f2(smallint)=%d | ", f2var);
 201         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 202         else printf("f3(int)=%d | ", f3var);
 203         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 204         else printf("f4(bigint)=%lld | ", f4var);
 205         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 206         else printf("f5(char)=%s | ", f5var);
 207         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 208         else printf("f6(varchar)=%s | ", f6var);
 209         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 210         else printf("f7(float)=%f | ", f7var);
 211         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 212         else printf("f8(double)=%lf | ", f8var);
 213         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 214         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 215         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 216         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 217         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 218         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 219         printf("\n");
 220         count++;
 221     }
 222     stmt->free();
 223     rv = con->commit();
 224     printf("%d rows selected\n",count);
 225
 226 //Droping table
 227     strcpy(statement,"DROP TABLE t1;");
 228     rv = stmt->prepare(statement);
 229     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 19; }
 230     rv = stmt->execute(rows);
 231     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
 232     printf("Table dropped\n");
 233     stmt->free();
 234     con->disconnect();
 235     printf("Connection Closed\n");
 236
 237     delete stmt; delete con;
 238     return 0;
 239 }
 240