test/sqlapi/Csql/DMLStmt/null_alldatatype.c

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