test/sqlapi/Csql/DMLStmt/dmloncopiedtable.c

   1 /*
   2  Basic DML on copied table
   3   CREATE TABLE t2(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);
   4   create table t1 as select * from t2;
   5   INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1',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',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');
   7   UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6=f6+100, f7=f7+10000,f8='2009-11-20', f9='12:01:01', f10='2009-11-20 12:01:01' WHERE f1<=3;
   8   DELETE FROM t1 WHERE f1=12 OR f1=4;
   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 t2(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);\n");
  27     strcpy(statement,"CREATE TABLE t2(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);");
  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 //copy table
  35     printf("CREATE TABLE t1 AS SELECT * FROM t2;\n");
  36     strcpy(statement,"CREATE TABLE t1 AS SELECT * FROM t2;");
  37     rows=0;
  38     rv = stmt->prepare(statement);
  39     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
  40     rv = stmt->execute(rows);
  41     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
  42     stmt->free();
  43 // Show all tables
  44     strcpy(statement,"GETALLTABLES;");
  45     rows=0;
  46     rv = stmt->prepare(statement);
  47     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
  48     stmt->execute(rows);
  49     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
  50     while(stmt->next() !=NULL) {
  51         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  52     }
  53     stmt->free();
  54
  55 //Inserting Records
  56     con->beginTrans();
  57     rows=0;
  58     printf("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');\n");
  59     stmt->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
  60     stmt->execute(rows);
  61     printf("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');\n");
  62     stmt->prepare("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
  63     stmt->execute(rows);
  64     con->commit();
  65     stmt->free();
  66
  67     char f1var;
  68     short int f2var;
  69     int f3var;
  70     long long f4var;
  71     char f5var[20];
  72     float f6var;
  73     double f7var;
  74     Date f8var;
  75     Time f9var;
  76     TimeStamp f10var;
  77
  78 //Fetching records after insert
  79     strcpy(statement,"SELECT * FROM t1;");
  80     rv = stmt->prepare(statement);
  81     if(rv!=OK) { delete stmt; delete con; return 8; }
  82     stmt->bindField(1,&f1var);
  83     stmt->bindField(2,&f2var);
  84     stmt->bindField(3,&f3var);
  85     stmt->bindField(4,&f4var);
  86     stmt->bindField(5,f5var);
  87     stmt->bindField(6,&f6var);
  88     stmt->bindField(7,&f7var);
  89     stmt->bindField(8,&f8var);
  90     stmt->bindField(9,&f9var);
  91     stmt->bindField(10,&f10var);
  92     int count=0;
  93     rv = con->beginTrans();
  94     if(rv!=OK)return 9;
  95     stmt->execute(rows);
  96     while(stmt->fetch() !=NULL) {
  97         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
  98         else printf("f1(tinyint)=%d | ", f1var);
  99         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 100         else printf("f2(smallint)=%d | ", f2var);
 101         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 102         else printf("f3(int)=%d | ", f3var);
 103         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 104         else printf("f4(bigint)=%lld | ", f4var);
 105         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 106         else printf("f5(char)=%s | ", f5var);
 107         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 108         else printf("f6(float)=%f | ", f6var);
 109         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 110         else printf("f7(double)=%lf | ", f7var);
 111         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 112         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 113         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 114         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 115         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 116         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 117         printf("\n");
 118         count++;
 119     }
 120     stmt->free();
 121     rv = con->commit();
 122     printf("%d rows selected\n",count);
 123
 124
 125 //Updating records.
 126     con->beginTrans();
 127     printf("UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6=f6+100, f7=f7+10000,f8='2009-11-20', f9='12:01:01', f10='2009-11-20 12:01:01' WHERE f1<=3;\n");
 128     strcpy(statement,"UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6=f6+100, f7=f7+10000,f8='2009-11-20', f9='12:01:01', f10='2009-11-20 12:01:01' WHERE f1<=3;");
 129     rv = stmt->prepare(statement);
 130     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
 131     rv = stmt->execute(rows);
 132     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 11; }
 133     con->commit();
 134     stmt->free();
 135
 136 //Fetching records after update
 137     strcpy(statement,"SELECT * FROM t1;");
 138     rv = stmt->prepare(statement);
 139     if(rv!=OK) { delete stmt; delete con; return 12; }
 140     stmt->bindField(1,&f1var);
 141     stmt->bindField(2,&f2var);
 142     stmt->bindField(3,&f3var);
 143     stmt->bindField(4,&f4var);
 144     stmt->bindField(5,f5var);
 145     stmt->bindField(6,&f6var);
 146     stmt->bindField(7,&f7var);
 147     stmt->bindField(8,&f8var);
 148     stmt->bindField(9,&f9var);
 149     stmt->bindField(10,&f10var);
 150     count=0;
 151     rv = con->beginTrans();
 152     if(rv!=OK)return 13;
 153     stmt->execute(rows);
 154     while(stmt->fetch() !=NULL) {
 155         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 156         else printf("f1(tinyint)=%d | ", f1var);
 157         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 158         else printf("f2(smallint)=%d | ", f2var);
 159         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 160         else printf("f3(int)=%d | ", f3var);
 161         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 162         else printf("f4(bigint)=%lld | ", f4var);
 163         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 164         else printf("f5(char)=%s | ", f5var);
 165         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 166         else printf("f6(float)=%f | ", f6var);
 167         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 168         else printf("f7(double)=%lf | ", f7var);
 169         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 170         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 171         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 172         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 173         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 174         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 175         printf("\n");
 176         count++;
 177     }
 178     stmt->free();
 179     rv = con->commit();
 180     printf("%d rows selected\n",count);
 181
 182
 183 //Deleting Records.
 184     con->beginTrans();
 185     printf("DELETE FROM t1 WHERE f1=12 OR f1=4;\n");
 186     strcpy(statement,"DELETE FROM t1 WHERE f1=12 OR f1=4;");
 187     rv = stmt->prepare(statement);
 188     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
 189     rv = stmt->execute(rows);
 190     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
 191     con->commit();
 192     stmt->free();
 193
 194 //Fetching records after delete
 195     strcpy(statement,"SELECT * FROM t1;");
 196     rv = stmt->prepare(statement);
 197     if(rv!=OK) { delete stmt; delete con; return 16; }
 198     stmt->bindField(1,&f1var);
 199     stmt->bindField(2,&f2var);
 200     stmt->bindField(3,&f3var);
 201     stmt->bindField(4,&f4var);
 202     stmt->bindField(5,f5var);
 203     stmt->bindField(6,&f6var);
 204     stmt->bindField(7,&f7var);
 205     stmt->bindField(8,&f8var);
 206     stmt->bindField(9,&f9var);
 207     stmt->bindField(10,&f10var);
 208     count=0;
 209     rv = con->beginTrans();
 210     if(rv!=OK)return 17;
 211     stmt->execute(rows);
 212     while(stmt->fetch() !=NULL) {
 213         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 214         else printf("f1(tinyint)=%d | ", f1var);
 215         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 216         else printf("f2(smallint)=%d | ", f2var);
 217         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 218         else printf("f3(int)=%d | ", f3var);
 219         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 220         else printf("f4(bigint)=%lld | ", f4var);
 221         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 222         else printf("f5(char)=%s | ", f5var);
 223         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 224         else printf("f6(float)=%f | ", f6var);
 225         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 226         else printf("f7(double)=%lf | ", f7var);
 227         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 228         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 229         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 230         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 231         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 232         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 233         printf("\n");
 234         count++;
 235     }
 236     stmt->free();
 237     rv = con->commit();
 238     printf("%d rows selected\n",count);
 239
 240 //Droping table
 241     strcpy(statement,"DROP TABLE t1;");
 242     rv = stmt->prepare(statement);
 243     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 18; }
 244     rv = stmt->execute(rows);
 245     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 19; }
 246     printf("Table t1 dropped\n");
 247     strcpy(statement,"DROP TABLE t2;");
 248     rv = stmt->prepare(statement);
 249     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 20; }
 250     rv = stmt->execute(rows);
 251     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 21; }
 252     printf("Table t2 dropped\n");
 253     stmt->free();
 254     con->disconnect();
 255     printf("Connection Closed\n");
 256
 257     delete stmt; delete con;
 258     return 0;
 259 }
 260