adding test scripts
[csql.git] / test / sqlapi / Csql / DMLStmt / dmloncopiedtable.c
blob4f5a370947e7b8bee3ea223a78f18e24f952c577
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"
12 int main()
14 DbRetVal rv = OK;
15 AbsSqlConnection *con = createConnection();
16 rv = con->connect("root","manager");
17 if(rv !=OK) {
18 delete con;
19 return 1;
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)
53 stmt->free();
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();
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;
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++;
120 stmt->free();
121 rv = con->commit();
122 printf("%d rows selected\n",count);
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();
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++;
178 stmt->free();
179 rv = con->commit();
180 printf("%d rows selected\n",count);
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();
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++;
236 stmt->free();
237 rv = con->commit();
238 printf("%d rows selected\n",count);
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");
257 delete stmt; delete con;
258 return 0;