test/sqlapi/Csql/DMLStmt/notnullonalldatatype.c

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