test/sqlapi/Csql/DMLStmt/defaultwithalldatatype.c

   1 /*
   2   Default value testing with all datatypes.(TINYINT,SMALLINT,INT,BIGINT,CHAR,VARCHAR,FLOAT,DOUBLE,DATE,TIME,TIMESTAMP))
   3   CREATE TABLE t1(f1 TINYINT DEFAULT 1,f2 SMALLINT DEFAULT 11,f3 INT DEFAULT 111,f4 BIGINT DEFAULT 1111,f5 CHAR(20) DEFAULT 'CSQL1',f6 VARCHAR(30) DEFAULT 'Lakshya1',f7 FLOAT DEFAULT 11.11,f8 DOUBLE DEFAULT 1111.11,f9 DATE DEFAULT '2001/01/01',f10 TIME DEFAULT '01:01:01',f11 TIMESTAMP DEFAULT '2001/01/01 01:01:01');
   4   Insert a record say (insert into t1 (f1) values(1); It should insert default values in all other fields.
   5   Update all the inserted default field values to null. it should update.
   6   Insert null values in all the fields , it should accept null values.
   7  It should pass.
   8 */
   9
  10 #include"common.h"
  11
  12 int main()
  13 {
  14     DbRetVal rv = OK;
  15     AbsSqlConnection *con = createConnection();
  16     rv = con->connect("root","manager");
  17     if(rv !=OK) {
  18         delete con;
  19         return 1;
  20     }
  21     printf("Connection opened\n");
  22     AbsSqlStatement *stmt = createStatement();
  23     stmt->setConnection(con);
  24 //Creating Table
  25     char statement[400];
  26     printf("CREATE TABLE t1(f1 TINYINT DEFAULT 1,f2 SMALLINT DEFAULT 11,f3 INT DEFAULT 111,f4 BIGINT DEFAULT 1111,f5 CHAR(20) DEFAULT 'CSQL1',f6 VARCHAR(30) DEFAULT 'Lakshya1',f7 FLOAT DEFAULT 11.11,f8 DOUBLE DEFAULT 1111.11,f9 DATE DEFAULT '2001-01-01',f10 TIME DEFAULT '01:01:01',f11 TIMESTAMP DEFAULT '2001-01-01 01:01:01');\n");
  27     strcpy(statement,"CREATE TABLE t1(f1 TINYINT DEFAULT 1,f2 SMALLINT DEFAULT 11,f3 INT DEFAULT 111,f4 BIGINT DEFAULT 1111,f5 CHAR(20) DEFAULT 'CSQL1',f6 VARCHAR(30) DEFAULT 'Lakshya1',f7 FLOAT DEFAULT 11.11,f8 DOUBLE DEFAULT 1111.11,f9 DATE DEFAULT '2001-01-01',f10 TIME DEFAULT '01:01:01',f11 TIMESTAMP DEFAULT '2001-01-01 01:01:01');");
  28     int rows=0;
  29     rv = stmt->prepare(statement);
  30     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  31     rv = stmt->execute(rows);
  32     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  33     stmt->free();
  34 // Show all tables
  35     strcpy(statement,"GETALLTABLES;");
  36     rows=0;
  37     rv = stmt->prepare(statement);
  38     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  39     stmt->execute(rows);
  40     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  41     while(stmt->next() !=NULL) {
  42         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  43     }
  44     stmt->free();
  45
  46 //Inserting Records
  47     con->beginTrans();
  48     rows=0;
  49     printf("INSERT INTO t1(f1) VALUES(1);\n");
  50     stmt->prepare("INSERT INTO t1(f1) VALUES(1);");
  51     stmt->execute(rows);
  52     printf("INSERT INTO t1 VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);\n");
  53     stmt->prepare("INSERT INTO t1 VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);");
  54     stmt->execute(rows);
  55     printf("INSERT INTO t1 VALUES(NULL,33,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);\n");
  56     stmt->prepare("INSERT INTO t1 VALUES(NULL,33,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);");
  57     stmt->execute(rows);
  58     con->commit();
  59     stmt->free();
  60     char f1var;
  61     short int f2var;
  62     int f3var;
  63     long long f4var;
  64     char f5var[20];
  65     char f6var[32];
  66     float f7var;
  67     double f8var;
  68     Date f9var;
  69     Time f10var;
  70     TimeStamp f11var;
  71
  72 //Fetching records after insert
  73     strcpy(statement,"SELECT * FROM t1;");
  74     rv = stmt->prepare(statement);
  75     if(rv!=OK) { delete stmt; delete con; return 6; }
  76     stmt->bindField(1,&f1var);
  77     stmt->bindField(2,&f2var);
  78     stmt->bindField(3,&f3var);
  79     stmt->bindField(4,&f4var);
  80     stmt->bindField(5,f5var);
  81     stmt->bindField(6,f6var);
  82     stmt->bindField(7,&f7var);
  83     stmt->bindField(8,&f8var);
  84     stmt->bindField(9,&f9var);
  85     stmt->bindField(10,&f10var);
  86     stmt->bindField(11,&f11var);
  87     int count=0;
  88     rv = con->beginTrans();
  89     if(rv!=OK)return 7;
  90     stmt->execute(rows);
  91     while(stmt->fetch() !=NULL) {
  92         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
  93         else printf("f1(tinyint)=%d | ", f1var);
  94         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
  95         else printf("f2(smallint)=%d | ", f2var);
  96         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
  97         else printf("f3(int)=%d | ", f3var);
  98         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
  99         else printf("f4(bigint)=%lld | ", f4var);
 100         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 101         else printf("f5(char)=%s | ", f5var);
 102         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 103         else printf("f6(varchar)=%s | ", f6var);
 104         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 105         else printf("f7(float)=%f | ", f7var);
 106         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 107         else printf("f8(double)=%lf | ", f8var);
 108         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 109         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 110         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 111         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 112         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 113         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 114         printf("\n");
 115         count++;
 116     }
 117     stmt->free();
 118     rv = con->commit();
 119     printf("%d rows selected\n",count);
 120 //Updating Default fields to NULL
 121     con->beginTrans();
 122     strcpy(statement,"UPDATE t1 SET f1=NULL,f2=NULL,f3=NULL,f4=NULL,f5=NULL,f6=NULL,f7=NULL,f8=NULL,f9=NULL,f10=NULL,f11=NULL where f1=1;");
 123     rv = stmt->prepare(statement);
 124     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
 125     printf("Prepared\n");
 126     rv = stmt->execute(rows);
 127     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
 128     printf("Executed\n");
 129     con->commit();
 130     stmt->free();
 131
 132 //Fetching records after Update
 133     strcpy(statement,"SELECT * FROM t1;");
 134     rv = stmt->prepare(statement);
 135     if(rv!=OK) { delete stmt; delete con; return 10; }
 136     stmt->bindField(1,&f1var);
 137     stmt->bindField(2,&f2var);
 138     stmt->bindField(3,&f3var);
 139     stmt->bindField(4,&f4var);
 140     stmt->bindField(5,f5var);
 141     stmt->bindField(6,f6var);
 142     stmt->bindField(7,&f7var);
 143     stmt->bindField(8,&f8var);
 144     stmt->bindField(9,&f9var);
 145     stmt->bindField(10,&f10var);
 146     stmt->bindField(11,&f11var);
 147     count=0;
 148     rv = con->beginTrans();
 149     if(rv!=OK)return 11;
 150     stmt->execute(rows);
 151     while(stmt->fetch() !=NULL) {
 152         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 153         else printf("f1(tinyint)=%d | ", f1var);
 154         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 155         else printf("f2(smallint)=%d | ", f2var);
 156         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 157         else printf("f3(int)=%d | ", f3var);
 158         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 159         else printf("f4(bigint)=%lld | ", f4var);
 160         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 161         else printf("f5(char)=%s | ", f5var);
 162         if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
 163         else printf("f6(varchar)=%s | ", f6var);
 164         if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
 165         else printf("f7(float)=%f | ", f7var);
 166         if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
 167         else printf("f8(double)=%lf | ", f8var);
 168         if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
 169         else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
 170         if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
 171         else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
 172         if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
 173         else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
 174         printf("\n");
 175         count++;
 176     }
 177     stmt->free();
 178     rv = con->commit();
 179     printf("%d rows selected\n",count);
 180 //Droping table
 181     strcpy(statement,"DROP TABLE t1;");
 182     rv = stmt->prepare(statement);
 183     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
 184     rv = stmt->execute(rows);
 185     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
 186     printf("Table dropped\n");
 187     stmt->free();
 188     con->disconnect();
 189     printf("Connection Closed\n");
 190
 191     delete stmt; delete con;
 192     return 0;
 193 }
 194