adding test scripts
[csql.git] / test / sqlapi / Csql / DMLStmt / rollbackafterdml.c
blobff8656361cf30f4638ffb1501917171d383423b0
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
12 #include"common.h"
14 int main()
16 DbRetVal rv = OK;
17 AbsSqlConnection *con = createConnection();
18 rv = con->connect("root","manager");
19 if(rv !=OK) {
20 delete con;
21 return 1;
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();
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)
47 stmt->free();
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();
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;
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++;
114 stmt->free();
115 rv = con->commit();
116 printf("%d rows selected\n",count);
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();
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++;
173 stmt->free();
174 rv = con->commit();
175 printf("%d rows selected\n",count);
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();
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++;
232 stmt->free();
233 rv = con->commit();
234 printf("%d rows selected\n",count);
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");
247 delete stmt; delete con;
248 return 0;