2 Test Update conditionally using relational operators on all datatypes.
3 CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP);
4 Insert 5 records into t1.
5 INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.00,1111.00,'2001-01-01','01:01:01','2001-01-01 01:01:01');
6 INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.00,2222.00,'2002-02-02','02:02:02','2002-02-02 02:02:02');
7 INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.00,3333.00,'2003-03-03','03:03:03','2003-03-03 03:03:03');
8 INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.00,4444.00,'2004-04-04','04:04:04','2004-04-04 04:04:04');
9 INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.00,5555.00,'2005-05-05','05:05:05','2005-05-05 05:05:05');
10 INSERT INTO t1 VALUES(6,66,666,6666,'CSQL6','LAKSHYA6',66.00,6666.00,'2006-06-06','06:06:06','2006-06-06 06:06:06');
11 INSERT INTO t1 VALUES(7,77,777,7777,'CSQL7','LAKSHYA7',77.00,7777.00,'2007-07-07','07:07:07','2007-07-07 07:07:07');
12 INSERT INTO t1 VALUES(8,88,888,8888,'CSQL8','LAKSHYA8',88.00,8888.00,'2008-08-08','08:08:08','2008-08-08 08:08:08');
13 INSERT INTO t1 VALUES(9,99,999,9999,'CSQL9','LAKSHYA9',99.00,9999.00,'2009-09-09','09:09:09','2009-09-09 09:09:09');
14 INSERT INTO t1 VALUES(10,100,1000,10000,'CSQL10',NULL,100.00,10000.00,'2010-10-10','10:10:10','2010-10-10 10:10:10');
16 DELETE FROM t1 WHERE f3=111;
17 DELETE FROM t1 WHERE f4<3333;
18 DELETE FROM t1 WHERE f6<='LAKSHYA3';
19 DELETE FROM t1 WHERE f9>'2008-08-08' and f10 <'10:10:10';
21 DELETE FROM t1 WHERE f3 between 100 and 500;
22 DELETE FROM t1 WHERE f6 like '%AKSHYA5';
23 DELETE FROM t1 WHERE f11 in ('2005-05-05 05:05:05','2006-06-06 06:06:06');
25 DELETE FROM t1 WHERE f3 is NOT NULL and f4=7777;
27 DELETE FROM t1 WHERE f6 is NULL or not(f4=8888);
35 AbsSqlConnection
*con
= createConnection();
36 rv
= con
->connect("root","manager");
41 printf("Connection opened\n");
42 AbsSqlStatement
*stmt
= createStatement();
43 stmt
->setConnection(con
);
46 printf("CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP);\n");
47 strcpy(statement
,"CREATE TABLE t1(f1 TINYINT,f2 SMALLINT,f3 INT,f4 BIGINT,f5 CHAR(20),f6 VARCHAR(30),f7 FLOAT,f8 DOUBLE,f9 DATE,f10 TIME,f11 TIMESTAMP);");
49 rv
= stmt
->prepare(statement
);
50 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 2; }
51 rv
= stmt
->execute(rows
);
52 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 3; }
55 strcpy(statement
,"GETALLTABLES;");
57 rv
= stmt
->prepare(statement
);
58 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 4; }
60 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 5; }
61 while(stmt
->next() !=NULL
) {
62 printf("Table Name is %s\n",stmt
->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
69 stmt
->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.00,1111.00,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
72 stmt
->prepare("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.00,2222.00,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
75 stmt
->prepare("INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.00,3333.00,'2003-03-03','03:03:03','2003-03-03 03:03:03');");
78 stmt
->prepare("INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.00,4444.00,'2004-04-04','04:04:04','2004-04-04 04:04:04');");
81 stmt
->prepare("INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.00,5555.00,'2005-05-05','05:05:05','2005-05-05 05:05:05');");
84 stmt
->prepare("INSERT INTO t1 VALUES(6,66,666,6666,'CSQL6','LAKSHYA6',66.00,6666.00,'2006-06-06','06:06:06','2006-06-06 06:06:06');");
87 stmt
->prepare("INSERT INTO t1 VALUES(7,77,777,7777,'CSQL7','LAKSHYA7',77.00,7777.00,'2007-07-07','07:07:07','2007-07-07 07:07:07');");
90 stmt
->prepare("INSERT INTO t1 VALUES(8,88,888,8888,'CSQL8','LAKSHYA8',88.00,8888.00,'2008-08-08','08:08:08','2008-08-08 08:08:08');");
93 stmt
->prepare("INSERT INTO t1 VALUES(9,99,999,9999,'CSQL9','LAKSHYA9',99.00,9999.00,'2009-09-09','09:09:09','2009-09-09 09:09:09');");
96 stmt
->prepare("INSERT INTO t1 VALUES(10,100,1000,10000,'CSQL10',NULL,100.00,10000.00,'2010-10-10','10:10:10','2010-10-10 10:10:10');");
113 //Fetching records after insert
114 printf("SELECT * FROM t1;\n");
115 strcpy(statement
,"SELECT * FROM t1;");
116 rv
= stmt
->prepare(statement
);
117 if(rv
!=OK
) { delete stmt
; delete con
; return 6; }
118 stmt
->bindField(1,&f1var
);
119 stmt
->bindField(2,&f2var
);
120 stmt
->bindField(3,&f3var
);
121 stmt
->bindField(4,&f4var
);
122 stmt
->bindField(5,f5var
);
123 stmt
->bindField(6,f6var
);
124 stmt
->bindField(7,&f7var
);
125 stmt
->bindField(8,&f8var
);
126 stmt
->bindField(9,&f9var
);
127 stmt
->bindField(10,&f10var
);
128 stmt
->bindField(11,&f11var
);
130 rv
= con
->beginTrans();
133 while(stmt
->fetch() !=NULL
) {
134 if(stmt
->isFldNull(1)) printf("f1=NULL | ");
135 else printf("f1=%d | ", f1var
);
136 if(stmt
->isFldNull(2)) printf("f2(=NULL | ");
137 else printf("f2(=%d | ", f2var
);
138 if(stmt
->isFldNull(3)) printf("f3=NULL | ");
139 else printf("f3=%d | ", f3var
);
140 if(stmt
->isFldNull(4)) printf("f4=NULL | ");
141 else printf("f4=%lld | ", f4var
);
142 if(stmt
->isFldNull(5)) printf("f5=NULL | ");
143 else printf("f5=%s | ", f5var
);
144 if(stmt
->isFldNull(6)) printf("f6=NULL | ");
145 else printf("f6=%s | ", f6var
);
146 if(stmt
->isFldNull(7)) printf("f7=NULL | ");
147 else printf("f7=%f | ", f7var
);
148 if(stmt
->isFldNull(8)) printf("f8=NULL | ");
149 else printf("f8=%lf | ", f8var
);
150 if(stmt
->isFldNull(9)) printf("f9=NULL | ");
151 else printf("f9=%02d-%02d-%02d | ", f9var
.year(),f9var
.month(),f9var
.dayOfMonth());
152 if(stmt
->isFldNull(10)) printf("f10=NULL | ");
153 else printf("f10=%02d:%02d:%02d | ", f10var
.hours(),f10var
.minutes(),f10var
.seconds());
154 if(stmt
->isFldNull(11)) printf("f11=NULL | ");
155 else printf("f11=%d-%d-%d %d:%d:%d | ", f11var
.year(),f11var
.month(),f11var
.dayOfMonth(),f11var
.hours(),f11var
.minutes(),f11var
.seconds());
161 printf("%d rows selected\n",count
);
165 printf("DELETE FROM t1 WHERE f3=111;\n");
166 strcpy(statement
,"DELETE FROM t1 WHERE f3=111;");
167 rv
= stmt
->prepare(statement
);
168 rv
= stmt
->execute(rows
);
169 printf("DELETE FROM t1 WHERE f4<3333;\n");
170 strcpy(statement
,"DELETE FROM t1 WHERE f4<3333;");
171 rv
= stmt
->prepare(statement
);
172 rv
= stmt
->execute(rows
);
173 printf("DELETE FROM t1 WHERE f6<='LAKSHYA3';\n");
174 strcpy(statement
,"DELETE FROM t1 WHERE f6<='LAKSHYA3';");
175 rv
= stmt
->prepare(statement
);
176 rv
= stmt
->execute(rows
);
177 printf("DELETE FROM t1 WHERE f9>'2008-08-08' and f10 <'10:10:10';\n");
178 strcpy(statement
,"DELETE FROM t1 WHERE f9>'2008-08-08' and f10 <'10:10:10';");
179 rv
= stmt
->prepare(statement
);
180 rv
= stmt
->execute(rows
);
181 printf("DELETE FROM t1 WHERE f3 between 100 and 500;\n");
182 strcpy(statement
,"DELETE FROM t1 WHERE f3 between 100 and 500;");
183 rv
= stmt
->prepare(statement
);
184 rv
= stmt
->execute(rows
);
185 printf("DELETE FROM t1 WHERE f6 like '%AKSHYA5';\n");
186 strcpy(statement
,"DELETE FROM t1 WHERE f6 like '%AKSHYA5';");
187 rv
= stmt
->prepare(statement
);
188 rv
= stmt
->execute(rows
);
189 printf("DELETE FROM t1 WHERE f11 in ('2005-05-05 05:05:05','2006-06-06 06:06:06');\n");
190 strcpy(statement
,"DELETE FROM t1 WHERE f11 in ('2005-05-05 05:05:05','2006-06-06 06:06:06');");
191 rv
= stmt
->prepare(statement
);
192 rv
= stmt
->execute(rows
);
193 printf("DELETE FROM t1 WHERE f3 is NOT NULL and f4=7777;\n");
194 strcpy(statement
,"DELETE FROM t1 WHERE f3 is NOT NULL and f4=7777;");
195 rv
= stmt
->prepare(statement
);
196 rv
= stmt
->execute(rows
);
197 printf("DELETE FROM t1 WHERE f6 is NULL or not(f4=8888);\n");
198 strcpy(statement
,"DELETE FROM t1 WHERE f6 is NULL or not(f4=8888);");
199 rv
= stmt
->prepare(statement
);
200 rv
= stmt
->execute(rows
);
205 printf("SELECT * FROM t1;\n");
206 strcpy(statement
,"SELECT * FROM t1;");
207 rv
= stmt
->prepare(statement
);
208 if(rv
!=OK
) { delete stmt
; delete con
; return 8; }
209 stmt
->bindField(1,&f1var
);
210 stmt
->bindField(2,&f2var
);
211 stmt
->bindField(3,&f3var
);
212 stmt
->bindField(4,&f4var
);
213 stmt
->bindField(5,f5var
);
214 stmt
->bindField(6,f6var
);
215 stmt
->bindField(7,&f7var
);
216 stmt
->bindField(8,&f8var
);
217 stmt
->bindField(9,&f9var
);
218 stmt
->bindField(10,&f10var
);
219 stmt
->bindField(11,&f11var
);
221 rv
= con
->beginTrans();
224 while(stmt
->fetch() !=NULL
) {
225 if(stmt
->isFldNull(1)) printf("f1=NULL | ");
226 else printf("f1=%d | ", f1var
);
227 if(stmt
->isFldNull(2)) printf("f2(=NULL | ");
228 else printf("f2(=%d | ", f2var
);
229 if(stmt
->isFldNull(3)) printf("f3=NULL | ");
230 else printf("f3=%d | ", f3var
);
231 if(stmt
->isFldNull(4)) printf("f4=NULL | ");
232 else printf("f4=%lld | ", f4var
);
233 if(stmt
->isFldNull(5)) printf("f5=NULL | ");
234 else printf("f5=%s | ", f5var
);
235 if(stmt
->isFldNull(6)) printf("f6=NULL | ");
236 else printf("f6=%s | ", f6var
);
237 if(stmt
->isFldNull(7)) printf("f7=NULL | ");
238 else printf("f7=%f | ", f7var
);
239 if(stmt
->isFldNull(8)) printf("f8=NULL | ");
240 else printf("f8=%lf | ", f8var
);
241 if(stmt
->isFldNull(9)) printf("f9=NULL | ");
242 else printf("f9=%02d-%02d-%02d | ", f9var
.year(),f9var
.month(),f9var
.dayOfMonth());
243 if(stmt
->isFldNull(10)) printf("f10=NULL | ");
244 else printf("f10=%02d:%02d:%02d | ", f10var
.hours(),f10var
.minutes(),f10var
.seconds());
245 if(stmt
->isFldNull(11)) printf("f11=NULL | ");
246 else printf("f11=%d-%d-%d %d:%d:%d | ", f11var
.year(),f11var
.month(),f11var
.dayOfMonth(),f11var
.hours(),f11var
.minutes(),f11var
.seconds());
252 printf("%d rows selected\n",count
);
255 strcpy(statement
,"DROP TABLE t1;");
256 rv
= stmt
->prepare(statement
);
257 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 10; }
258 rv
= stmt
->execute(rows
);
259 if(rv
!=OK
) { delete stmt
; con
->disconnect(); delete con
; return 11; }
260 printf("Table dropped\n");
263 printf("Connection Closed\n");
265 delete stmt
; delete con
;