test/sqlapi/Csql/DDLStmt/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(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);\n");
  47     rv = stmt->prepare("INSERT INTO t1 VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);");
  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(NULL,33,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);\n");
  65     rv = stmt->prepare("INSERT INTO t1 VALUES(NULL,33,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     con->commit();
  83     stmt->free();
  84     char f1var;
  85     short int f2var;
  86     int f3var;
  87     long long f4var;
  88     char f5var[20];
  89     char f6var[30];
  90     float f7var;
  91     double f8var;
  92     Date f9var;
  93     Time f10var;
  94     TimeStamp f11var;
  95
  96 //Fetching records after insert
  97     strcpy(statement,"SELECT * FROM t1;");
  98     rv = stmt->prepare(statement);
  99     if(rv!=OK) { delete stmt; delete con; return 10; }
 100     stmt->bindField(1,&f1var);
 101     stmt->bindField(2,&f2var);
 102     stmt->bindField(3,&f3var);
 103     stmt->bindField(4,&f4var);
 104     stmt->bindField(5,f5var);
 105     stmt->bindField(6,f6var);
 106     stmt->bindField(7,&f7var);
 107     stmt->bindField(8,&f8var);
 108     stmt->bindField(9,&f9var);
 109     stmt->bindField(10,&f10var);
 110     stmt->bindField(11,&f11var);
 111     int count=0;
 112     rv = con->beginTrans();
 113     if(rv!=OK)return 7;
 114     stmt->execute(rows);
 115     while(stmt->fetch() !=NULL) {
 116         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 117         else printf("f1(tinyint)=%d | ", f1var);
 118         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 119         else printf("f2(smallint)=%d | ", f2var);
 120         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 121         else printf("f3(int)=%d | ", f3var);
 122         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 123         else printf("f4(bigint)=%lld | ", f4var);
 124         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 125         else printf("f5(char)=%s | ", f5var);
 126         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 127         else printf("f6(varchar)=%s | ", f6var);
 128         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 129         else printf("f7(float)=%f | ", f7var);
 130         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 131         else printf("f8(double)=%lf | ", f8var);
 132         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 133         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 134         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 135         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 136         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 137         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 138         printf("\n");
 139         count++;
 140     }
 141     stmt->free();
 142     rv = con->commit();
 143     printf("%d rows selected\n",count);
 144 //Droping table
 145     strcpy(statement,"DROP TABLE t1;");
 146     rv = stmt->prepare(statement);
 147     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 11; }
 148     rv = stmt->execute(rows);
 149     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
 150     printf("Table dropped\n");
 151     stmt->free();
 152     con->disconnect();
 153     printf("Connection Closed\n");
 154
 155     delete stmt; delete con;
 156     return 0;
 157 }
 158