test/sqlapi/Csql/DMLStmt/updrangeonalldatatype.c

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