test/sqlapi/Csql/DMLStmt/rollbackafterdml.c

   1 /*
   2   Rollback after dml
   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   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');
   5   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');
   6   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;
   7   rollback;
   8   DELETE FROM t1 WHERE f1=12 OR f1=4;
   9   rollback;
  10   select * from t1; should display the records which are put in the table only through insert operation
  11 */
  12 #include"common.h"
  13
  14 int main()
  15 {
  16     DbRetVal rv = OK;
  17     AbsSqlConnection *con = createConnection();
  18     rv = con->connect("root","manager");
  19     if(rv !=OK) {
  20         delete con;
  21         return 1;
  22     }
  23     printf("Connection opened\n");
  24     AbsSqlStatement *stmt = createStatement();
  25     stmt->setConnection(con);
  26 //Creating Table
  27     char statement[400];
  28     printf("CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);\n");
  29     strcpy(statement,"CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 FLOAT,f7 DOUBLE,f8 DATE,f9 TIME,f10 TIMESTAMP);");
  30     int rows=0;
  31     rv = stmt->prepare(statement);
  32     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
  33     rv = stmt->execute(rows);
  34     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
  35     stmt->free();
  36
  37 // Show all tables
  38     strcpy(statement,"GETALLTABLES;");
  39     rows=0;
  40     rv = stmt->prepare(statement);
  41     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 6; }
  42     stmt->execute(rows);
  43     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 7; }
  44     while(stmt->next() !=NULL) {
  45         printf("Table Name is %s\n",stmt->getFieldValuePtr(2));  //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
  46     }
  47     stmt->free();
  48
  49 //Inserting Records
  50     con->beginTrans();
  51     rows=0;
  52     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");
  53     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');");
  54     stmt->execute(rows);
  55     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");
  56     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');");
  57     stmt->execute(rows);
  58     con->commit();
  59     stmt->free();
  60
  61     char f1var;
  62     short int f2var;
  63     int f3var;
  64     long long f4var;
  65     char f5var[20];
  66     float f6var;
  67     double f7var;
  68     Date f8var;
  69     Time f9var;
  70     TimeStamp f10var;
  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 8; }
  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     int count=0;
  87     rv = con->beginTrans();
  88     if(rv!=OK)return 9;
  89     stmt->execute(rows);
  90     while(stmt->fetch() !=NULL) {
  91         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
  92         else printf("f1(tinyint)=%d | ", f1var);
  93         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
  94         else printf("f2(smallint)=%d | ", f2var);
  95         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
  96         else printf("f3(int)=%d | ", f3var);
  97         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
  98         else printf("f4(bigint)=%lld | ", f4var);
  99         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 100         else printf("f5(char)=%s | ", f5var);
 101         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 102         else printf("f6(float)=%f | ", f6var);
 103         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 104         else printf("f7(double)=%lf | ", f7var);
 105         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 106         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 107         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 108         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 109         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 110         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 111         printf("\n");
 112         count++;
 113     }
 114     stmt->free();
 115     rv = con->commit();
 116     printf("%d rows selected\n",count);
 117
 118
 119 //Updating records.
 120     con->beginTrans();
 121     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");
 122     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;");
 123     rv = stmt->prepare(statement);
 124     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
 125     rv = stmt->execute(rows);
 126     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 11; }
 127     printf("Roll back Transaction\n");
 128     con->rollback();
 129     stmt->free();
 130
 131 //Fetching records after update
 132     strcpy(statement,"SELECT * FROM t1;");
 133     rv = stmt->prepare(statement);
 134     if(rv!=OK) { delete stmt; delete con; return 12; }
 135     stmt->bindField(1,&f1var);
 136     stmt->bindField(2,&f2var);
 137     stmt->bindField(3,&f3var);
 138     stmt->bindField(4,&f4var);
 139     stmt->bindField(5,f5var);
 140     stmt->bindField(6,&f6var);
 141     stmt->bindField(7,&f7var);
 142     stmt->bindField(8,&f8var);
 143     stmt->bindField(9,&f9var);
 144     stmt->bindField(10,&f10var);
 145     count=0;
 146     rv = con->beginTrans();
 147     if(rv!=OK)return 13;
 148     stmt->execute(rows);
 149     while(stmt->fetch() !=NULL) {
 150         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 151         else printf("f1(tinyint)=%d | ", f1var);
 152         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 153         else printf("f2(smallint)=%d | ", f2var);
 154         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 155         else printf("f3(int)=%d | ", f3var);
 156         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 157         else printf("f4(bigint)=%lld | ", f4var);
 158         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 159         else printf("f5(char)=%s | ", f5var);
 160         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 161         else printf("f6(float)=%f | ", f6var);
 162         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 163         else printf("f7(double)=%lf | ", f7var);
 164         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 165         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 166         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 167         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 168         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 169         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 170         printf("\n");
 171         count++;
 172     }
 173     stmt->free();
 174     rv = con->commit();
 175     printf("%d rows selected\n",count);
 176
 177
 178 //Deleting Records.
 179     con->beginTrans();
 180     printf("DELETE FROM t1 WHERE f1=12 OR f1=4;\n");
 181     strcpy(statement,"DELETE FROM t1 WHERE f1=12 OR f1=4;");
 182     rv = stmt->prepare(statement);
 183     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 14; }
 184     rv = stmt->execute(rows);
 185     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 15; }
 186     printf("Roll back Transaction\n");
 187     con->rollback();
 188     stmt->free();
 189
 190 //Fetching records after delete
 191     strcpy(statement,"SELECT * FROM t1;");
 192     rv = stmt->prepare(statement);
 193     if(rv!=OK) { delete stmt; delete con; return 16; }
 194     stmt->bindField(1,&f1var);
 195     stmt->bindField(2,&f2var);
 196     stmt->bindField(3,&f3var);
 197     stmt->bindField(4,&f4var);
 198     stmt->bindField(5,f5var);
 199     stmt->bindField(6,&f6var);
 200     stmt->bindField(7,&f7var);
 201     stmt->bindField(8,&f8var);
 202     stmt->bindField(9,&f9var);
 203     stmt->bindField(10,&f10var);
 204     count=0;
 205     rv = con->beginTrans();
 206     if(rv!=OK)return 17;
 207     stmt->execute(rows);
 208     while(stmt->fetch() !=NULL) {
 209         if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
 210         else printf("f1(tinyint)=%d | ", f1var);
 211         if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
 212         else printf("f2(smallint)=%d | ", f2var);
 213         if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
 214         else printf("f3(int)=%d | ", f3var);
 215         if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
 216         else printf("f4(bigint)=%lld | ", f4var);
 217         if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
 218         else printf("f5(char)=%s | ", f5var);
 219         if(stmt->isFldNull(6)) printf("f6(float)=NULL | ");
 220         else printf("f6(float)=%f | ", f6var);
 221         if(stmt->isFldNull(7)) printf("f7(double)=NULL | ");
 222         else printf("f7(double)=%lf | ", f7var);
 223         if(stmt->isFldNull(8)) printf("f8(date)=NULL | ");
 224         else printf("f8(date)=%02d-%02d-%02d | ", f8var.year(),f8var.month(),f8var.dayOfMonth());
 225         if(stmt->isFldNull(9)) printf("f9(time)=NULL | ");
 226         else printf("f9(time)=%02d:%02d:%02d | ", f9var.hours(),f9var.minutes(),f9var.seconds());
 227         if(stmt->isFldNull(10)) printf("f10(timestamp)=NULL | ");
 228         else printf("f10(timestamp)=%d-%d-%d %d:%d:%d | ", f10var.year(),f10var.month(),f10var.dayOfMonth(),f10var.hours(),f10var.minutes(),f10var.seconds());
 229         printf("\n");
 230         count++;
 231     }
 232     stmt->free();
 233     rv = con->commit();
 234     printf("%d rows selected\n",count);
 235
 236 //Droping table
 237     strcpy(statement,"DROP TABLE t1;");
 238     rv = stmt->prepare(statement);
 239     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 18; }
 240     rv = stmt->execute(rows);
 241     if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 19; }
 242     printf("Table t1 dropped\n");
 243     stmt->free();
 244     con->disconnect();
 245     printf("Connection Closed\n");
 246
 247     delete stmt; delete con;
 248     return 0;
 249 }
 250