adding test scripts
[csql.git] / test / sqlapi / Select / selecttest13.c
blob78c67f2419f2eede79bb2696003e4f829f94740f
1 /* create table T1 with 10 fields.
2 * insert some records into it with null values
3 *
4 *
5 * select * from T1;
7 */
9 #include"common.h"
11 int main()
13 DbRetVal rv = OK;
14 AbsSqlConnection *con = createConnection();
15 rv = con->connect("root","manager");
16 if(rv!=OK)return 1;
17 AbsSqlStatement *stmt = createStatement();
18 stmt->setConnection(con);
19 char statement[200];
20 strcpy(statement,"CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 INT,F10 BIGINT, F11 BINARY(4));");
21 int rows = 0;
22 rv = stmt->prepare(statement);
23 if(rv!=OK) { delete stmt; delete con; return 2; }
24 rv = stmt->execute(rows);
25 if(rv!=OK) { delete stmt; delete con; return 3; }
26 stmt->free();
27 printf("Table T1 created\n");
28 // insert into table
29 strcpy(statement,"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?,?);");
30 int f1var = 0;
31 short int f2var = 1;
32 char f3var[32]="jitendra";
33 float f4var = 5.5;
34 float f5var = 10.50;
35 Date f6var;
36 f6var.set(2007,01,21);
37 Time f7var;
38 f7var.set(12,29,30);
39 TimeStamp f8var;
40 f8var.setDate(2007,01,21);
41 f8var.setTime(12,29,30);
42 int f9var = 20;
43 long long f10var = 12000;
44 char f11var[12]="23fe";
45 rv = stmt->prepare(statement);
46 if(rv!=OK) { delete stmt; delete con; return 4; }
47 int count=0;
48 for(int i=0;i<10;i++) {
49 rv = con->beginTrans();
50 if(rv!=OK) break;
51 f1var=i;
52 stmt->setIntParam(1,f1var);
53 stmt->setShortParam(2,f2var);
54 stmt->setStringParam(3,f3var);
55 stmt->setFloatParam(4,f4var);
56 stmt->setNull(5);
57 stmt->setDateParam(6,f6var);
58 stmt->setNull(7);
59 stmt->setTimeStampParam(8,f8var);
60 stmt->setIntParam(9,f9var);
61 stmt->setLongLongParam(10,f10var);
62 stmt->setBinaryParam(11,f11var, 12);
63 rv = stmt->execute(rows);
64 if(rv!=OK)break;
65 rv = con->commit();
66 if(rv!=OK)break;
67 count++;
69 stmt->free();
70 //*********************************************************
71 // Insert with value list records from the T1
72 strcpy(statement,"INSERT INTO T1(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10) VALUES(?,?,?,?,?,?,?,?,?,?);");
73 rv = stmt->prepare(statement);
74 if(rv!=OK) { delete stmt; delete con; return 4; }
75 for(int i=0;i<10;i++) {
76 rv = con->beginTrans();
77 if(rv!=OK) break;
78 f1var=i;
79 stmt->setNull(1);
80 stmt->setShortParam(2,f2var);
81 stmt->setStringParam(3,f3var);
82 stmt->setFloatParam(4,f4var);
83 stmt->setNull(5);
84 stmt->setDateParam(6,f6var);
85 stmt->setNull(7);
86 stmt->setTimeStampParam(8,f8var);
87 stmt->setIntParam(9,f9var);
88 stmt->setLongLongParam(10,f10var);
89 rv = stmt->execute(rows);
90 if(rv!=OK)break;
91 rv = con->commit();
92 if(rv!=OK)break;
93 count++;
95 printf("Total rows inserted %d\n",count);
96 stmt->free();
98 //**********************************************************
99 // SELECT * FROM T1;
100 strcpy(statement,"SELECT * FROM T1;");
101 rv = stmt->prepare(statement);
102 if(rv!=OK) { delete stmt; delete con; return 5; }
103 stmt->bindField(1,&f1var);
104 stmt->bindField(2,&f2var);
105 stmt->bindField(3,f3var);
106 stmt->bindField(4,&f4var);
107 stmt->bindField(5,&f5var);
108 stmt->bindField(6,&f6var);
109 stmt->bindField(7,&f7var);
110 stmt->bindField(8,&f8var);
111 stmt->bindField(9,&f9var);
112 stmt->bindField(10,&f10var);
113 stmt->bindField(11,f11var);
114 count=0;
115 rv = con->beginTrans();
116 if(rv!=OK)return 6;
117 stmt->execute(rows);
118 while(stmt->fetch() !=NULL) {
119 if(stmt->isFldNull(1)) printf("F1=NULL | ");
120 else printf("F1=%d | ", f1var);
121 if(stmt->isFldNull(2)) printf("F2=NULL | ");
122 else printf("F2=%hd | ", f2var);
123 if(stmt->isFldNull(3)) printf("F3=NULL | ");
124 else printf("F3=%s | ", f3var);
125 if(stmt->isFldNull(4)) printf("F4=NULL | ");
126 else printf("F4=%f | ", f4var);
127 if(stmt->isFldNull(5)) printf("F5=NULL | ");
128 else printf("F5=%f | ", f5var);
129 if(stmt->isFldNull(6)) printf("F6=NULL | ");
130 else printf("F6=%02d-%02d-%02d | ",f6var.year(), f6var.month(), f6var.dayOfMonth());
131 if(stmt->isFldNull(7)) printf("F7=NULL | ");
132 else printf("F7=%02d:%02d:%02d | ", f7var.hours(),f7var.minutes(),f7var.seconds());
133 if(stmt->isFldNull(8)) printf("F8=NULL | ");
134 else printf("F8=%d-%d-%d %d:%d:%d | ", f8var.year(),f8var.month(),f8var.dayOfMonth(),f8var.hours(),f8var.minutes(),f8var.seconds());
135 if(stmt->isFldNull(9)) printf("F9=NULL | ");
136 else printf("F9=%d | ", f9var);
137 if(stmt->isFldNull(10)) printf("F10=NULL | ");
138 else printf("F10=%lld | ", f10var);
139 if(stmt->isFldNull(11)) printf("F11=NULL | ");
140 else { AllDataType::printVal(f11var,typeBinary,4);
142 printf("\n");
143 count++;
145 stmt->close();
146 rv = con->commit();
147 if(rv !=OK) { delete stmt; delete con; return 7; }
148 printf("Total row fetched=%d\n",count);
149 stmt->free();
150 //=================================
151 // Select with projected values
152 strcpy(statement,"SELECT F1,F2,F3,F4,F5 FROM T1;");
153 rv = stmt->prepare(statement);
154 if(rv!=OK) { delete stmt; delete con; return 5; }
155 stmt->bindField(1,&f1var);
156 stmt->bindField(2,&f2var);
157 stmt->bindField(3,f3var);
158 stmt->bindField(4,&f4var);
159 stmt->bindField(5,&f5var);
160 count=0;
161 rv = con->beginTrans();
162 if(rv!=OK)return 6;
163 stmt->execute(rows);
164 while(stmt->fetch() !=NULL) {
165 if(stmt->isFldNull("F1")) printf("F1=NULL | ");
166 else printf("F1=%d | ", f1var);
167 if(stmt->isFldNull("F2")) printf("F2=NULL | ");
168 else printf("F2=%hd | ", f2var);
169 if(stmt->isFldNull("F3")) printf("F3=NULL | ");
170 else printf("F3=%s | ", f3var);
171 if(stmt->isFldNull("F4")) printf("F4=NULL | ");
172 else printf("F4=%f | ", f4var);
173 if(stmt->isFldNull("F5")) printf("F5=NULL | ");
174 else printf("F5=%f | ", f5var);
175 printf("\n");
176 count++;
178 stmt->close();
179 rv = con->commit();
180 if(rv !=OK) { delete stmt; delete con; return 7; }
181 printf("Total row fetched=%d\n",count);
182 stmt->free();
183 //==========================
186 strcpy(statement,"DROP TABLE T1;");
187 rv = stmt->prepare(statement);
188 if(rv!=OK) { delete stmt; delete con; return 8; }
189 rv = stmt->execute(rows);
190 if(rv!=OK) { delete stmt; delete con; return 9; }
191 printf("Table dropped\n");
192 stmt->free(); delete stmt; delete con;
193 return 0;