*** empty log message ***
[csql.git] / test / sqlapi / Csql / DMLStmt / alldmlonalldatatype.c
blob593f4bdce0dfcad7d6b55f01dd6c7ccb21fad96d
1 /*
2 Test BASIC DML statement with no parameters (Insert, Update, Delete, Select for 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.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','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');
7 INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03','2003-03-03 03:03:03');
8 INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.44,4444.44,'2004-04-04','04:04:04','2004-04-04 04:04:04');
9 INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.55,5555.55,'2005-05-05','05:05:05','2005-05-05 05:05:05');
10 UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6='LAKSHYA_BANGALORE', f7=f7+100, f8=f8+10000,f9='2009-11-20', f10='12:01:01', f11='2009-11-20 12:01:01' WHERE f1<=3;
11 DELETE FROM t1 WHERE f1=12 OR f1=4;
12 SELECT * FROM t1;
14 #include"common.h"
16 int main()
18 DbRetVal rv = OK;
19 AbsSqlConnection *con = createConnection();
20 rv = con->connect("root","manager");
21 if(rv !=OK) {
22 delete con;
23 return 1;
25 printf("Connection opened\n");
26 AbsSqlStatement *stmt = createStatement();
27 stmt->setConnection(con);
28 //Creating Table
29 char statement[400];
30 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");
31 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);");
32 int rows=0;
33 rv = stmt->prepare(statement);
34 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
35 rv = stmt->execute(rows);
36 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
37 stmt->free();
38 // Show all tables
39 strcpy(statement,"GETALLTABLES;");
40 rows=0;
41 rv = stmt->prepare(statement);
42 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
43 stmt->execute(rows);
44 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
45 while(stmt->next() !=NULL) {
46 printf("Table Name is %s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
48 stmt->free();
50 //Inserting Records
51 con->beginTrans();
52 rows=0;
53 printf("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');\n");
54 stmt->prepare("INSERT INTO t1 VALUES(1,11,111,1111,'CSQL1','LAKSHYA1',11.11,1111.11,'2001-01-01','01:01:01','2001-01-01 01:01:01');");
55 stmt->execute(rows);
56 printf("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');\n");
57 stmt->prepare("INSERT INTO t1 VALUES(2,22,222,2222,'CSQL2','LAKSHYA2',22.22,2222.22,'2002-02-02','02:02:02','2002-02-02 02:02:02');");
58 stmt->execute(rows);
59 printf("INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03','2003-03-03 03:03:03');\n");
60 stmt->prepare("INSERT INTO t1 VALUES(3,33,333,3333,'CSQL3','LAKSHYA3',33.33,3333.33,'2003-03-03','03:03:03','2003-03-03 03:03:03');");
61 stmt->execute(rows);
62 printf("INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.44,4444.44,'2004-04-04','04:04:04','2004-04-04 04:04:04');\n");
63 stmt->prepare("INSERT INTO t1 VALUES(4,44,444,4444,'CSQL4','LAKSHYA4',44.44,4444.44,'2004-04-04','04:04:04','2004-04-04 04:04:04');");
64 stmt->execute(rows);
65 printf("INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.55,5555.55,'2005-05-05','05:05:05','2005-05-05 05:05:05');\n");
66 stmt->prepare("INSERT INTO t1 VALUES(5,55,555,5555,'CSQL5','LAKSHYA5',55.55,5555.55,'2005-05-05','05:05:05','2005-05-05 05:05:05');");
67 stmt->execute(rows);
68 con->commit();
69 stmt->free();
71 char f1var;
72 short int f2var;
73 int f3var;
74 long long f4var;
75 char f5var[20];
76 char f6var[32];
77 float f7var;
78 double f8var;
79 Date f9var;
80 Time f10var;
81 TimeStamp f11var;
83 //Fetching records after insert
84 strcpy(statement,"SELECT * FROM t1;");
85 rv = stmt->prepare(statement);
86 if(rv!=OK) { delete stmt; delete con; return 6; }
87 stmt->bindField(1,&f1var);
88 stmt->bindField(2,&f2var);
89 stmt->bindField(3,&f3var);
90 stmt->bindField(4,&f4var);
91 stmt->bindField(5,f5var);
92 stmt->bindField(6,f6var);
93 stmt->bindField(7,&f7var);
94 stmt->bindField(8,&f8var);
95 stmt->bindField(9,&f9var);
96 stmt->bindField(10,&f10var);
97 stmt->bindField(11,&f11var);
98 int count=0;
99 rv = con->beginTrans();
100 if(rv!=OK)return 7;
101 stmt->execute(rows);
102 while(stmt->fetch() !=NULL) {
103 if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
104 else printf("f1(tinyint)=%d | ", f1var);
105 if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
106 else printf("f2(smallint)=%d | ", f2var);
107 if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
108 else printf("f3(int)=%d | ", f3var);
109 if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
110 else printf("f4(bigint)=%lld | ", f4var);
111 if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
112 else printf("f5(char)=%s | ", f5var);
113 if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
114 else printf("f6(varchar)=%s | ", f6var);
115 if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
116 else printf("f7(float)=%f | ", f7var);
117 if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
118 else printf("f8(double)=%lf | ", f8var);
119 if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
120 else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
121 if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
122 else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
123 if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
124 else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
125 printf("\n");
126 count++;
128 stmt->free();
129 rv = con->commit();
130 printf("%d rows selected\n",count);
133 //Updating records.
134 con->beginTrans();
135 printf("UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6='LAKSHYA_BANGALORE', f7=f7+100, f8=f8+10000,f9='2009-11-20', f10='12:01:01', f11='2009-11-20 12:01:01' WHERE f1<=3;\n");
136 strcpy(statement,"UPDATE t1 SET f1=f1+10, f2=f2+100, f3=f3+1000, f4=f4+10000, f5='CSQLMMDB', f6='LAKSHYA_BANGALORE', f7=f7+100, f8=f8+10000,f9='2009-11-20', f10='12:01:01', f11='2009-11-20 12:01:01' WHERE f1<=3;");
137 rv = stmt->prepare(statement);
138 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
139 rv = stmt->execute(rows);
140 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
141 con->commit();
142 stmt->free();
144 //Fetching records after insert
145 strcpy(statement,"SELECT * FROM t1;");
146 rv = stmt->prepare(statement);
147 if(rv!=OK) { delete stmt; delete con; return 6; }
148 stmt->bindField(1,&f1var);
149 stmt->bindField(2,&f2var);
150 stmt->bindField(3,&f3var);
151 stmt->bindField(4,&f4var);
152 stmt->bindField(5,f5var);
153 stmt->bindField(6,f6var);
154 stmt->bindField(7,&f7var);
155 stmt->bindField(8,&f8var);
156 stmt->bindField(9,&f9var);
157 stmt->bindField(10,&f10var);
158 stmt->bindField(11,&f11var);
159 count=0;
160 rv = con->beginTrans();
161 if(rv!=OK)return 7;
162 stmt->execute(rows);
163 while(stmt->fetch() !=NULL) {
164 if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
165 else printf("f1(tinyint)=%d | ", f1var);
166 if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
167 else printf("f2(smallint)=%d | ", f2var);
168 if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
169 else printf("f3(int)=%d | ", f3var);
170 if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
171 else printf("f4(bigint)=%lld | ", f4var);
172 if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
173 else printf("f5(char)=%s | ", f5var);
174 if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
175 else printf("f6(varchar)=%s | ", f6var);
176 if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
177 else printf("f7(float)=%f | ", f7var);
178 if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
179 else printf("f8(double)=%lf | ", f8var);
180 if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
181 else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
182 if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
183 else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
184 if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
185 else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
186 printf("\n");
187 count++;
189 stmt->free();
190 rv = con->commit();
191 printf("%d rows selected\n",count);
193 //Deleting Records.
194 con->beginTrans();
195 printf("DELETE FROM t1 WHERE f1=12 OR f1=4;\n");
196 strcpy(statement,"DELETE FROM t1 WHERE f1=12 OR f1=4;");
197 rv = stmt->prepare(statement);
198 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 8; }
199 rv = stmt->execute(rows);
200 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
201 con->commit();
202 stmt->free();
204 //Fetching records after insert
205 strcpy(statement,"SELECT * FROM t1;");
206 rv = stmt->prepare(statement);
207 if(rv!=OK) { delete stmt; delete con; return 6; }
208 stmt->bindField(1,&f1var);
209 stmt->bindField(2,&f2var);
210 stmt->bindField(3,&f3var);
211 stmt->bindField(4,&f4var);
212 stmt->bindField(5,f5var);
213 stmt->bindField(6,f6var);
214 stmt->bindField(7,&f7var);
215 stmt->bindField(8,&f8var);
216 stmt->bindField(9,&f9var);
217 stmt->bindField(10,&f10var);
218 stmt->bindField(11,&f11var);
219 count=0;
220 rv = con->beginTrans();
221 if(rv!=OK)return 7;
222 stmt->execute(rows);
223 while(stmt->fetch() !=NULL) {
224 if(stmt->isFldNull(1)) printf("f1(tinyint)=NULL | ");
225 else printf("f1(tinyint)=%d | ", f1var);
226 if(stmt->isFldNull(2)) printf("f2(smallint)=NULL | ");
227 else printf("f2(smallint)=%d | ", f2var);
228 if(stmt->isFldNull(3)) printf("f3(int)=NULL | ");
229 else printf("f3(int)=%d | ", f3var);
230 if(stmt->isFldNull(4)) printf("f4(bigint)=NULL | ");
231 else printf("f4(bigint)=%lld | ", f4var);
232 if(stmt->isFldNull(5)) printf("f5(char)=NULL | ");
233 else printf("f5(char)=%s | ", f5var);
234 if(stmt->isFldNull(6)) printf("f6(varchar)=NULL | ");
235 else printf("f6(varchar)=%s | ", f6var);
236 if(stmt->isFldNull(7)) printf("f7(float)=NULL | ");
237 else printf("f7(float)=%f | ", f7var);
238 if(stmt->isFldNull(8)) printf("f8(double)=NULL | ");
239 else printf("f8(double)=%lf | ", f8var);
240 if(stmt->isFldNull(9)) printf("f9(date)=NULL | ");
241 else printf("f9(date)=%02d-%02d-%02d | ", f9var.year(),f9var.month(),f9var.dayOfMonth());
242 if(stmt->isFldNull(10)) printf("f10(time)=NULL | ");
243 else printf("f10(time)=%02d:%02d:%02d | ", f10var.hours(),f10var.minutes(),f10var.seconds());
244 if(stmt->isFldNull(11)) printf("f11(timestamp)=NULL | ");
245 else printf("f11(timestamp)=%d-%d-%d %d:%d:%d | ", f11var.year(),f11var.month(),f11var.dayOfMonth(),f11var.hours(),f11var.minutes(),f11var.seconds());
246 printf("\n");
247 count++;
249 stmt->free();
250 rv = con->commit();
251 printf("%d rows selected\n",count);
253 //Droping table
254 strcpy(statement,"DROP TABLE t1;");
255 rv = stmt->prepare(statement);
256 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 12; }
257 rv = stmt->execute(rows);
258 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 13; }
259 printf("Table dropped\n");
260 stmt->free();
261 con->disconnect();
262 printf("Connection Closed\n");
264 delete stmt; delete con;
265 return 0;