adding test scripts
[csql.git] / test / sqlapi / Csql / DMLStmt / aggregate.c
blob78f05e6e32f873bacb637034abfc1753caf1cbfa
1 /*
2 Aggregate test
3 CREATE TABLE t1(f1 INT,f2 BIGINT,f3 DOUBLE,f4 DATE);
4 Insert 5 records into t1.
5 INSERT INTO t1 VALUES(100,100,100,'2009-11-23');
6 INSERT INTO t1 VALUES(200,200,200,'2009-11-25');
7 INSERT INTO t1 VALUES(100,300,300,'2009-11-27');
8 INSERT INTO t1 VALUES(200,400,400,'2009-11-29');
9 INSERT INTO t1 VALUES(300,500,500,'2009-12-01');
10 SELECT * FROM t1;
11 select count(f4) from t1;
12 select f1, sum(f2), avg(f3), max(f4) from t1 group by f1;
13 select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having max(f4)>'2009/11/29';
14 select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having (max(f4) >'2009/11/26' and max(f4) < '2009-11-28') or avg(f3) > 400;
16 #include"common.h"
18 int main()
20 DbRetVal rv = OK;
21 AbsSqlConnection *con = createConnection();
22 rv = con->connect("root","manager");
23 if(rv !=OK) {
24 delete con;
25 return 1;
27 printf("Connection opened\n");
28 AbsSqlStatement *stmt = createStatement();
29 stmt->setConnection(con);
30 //Creating Table
31 char statement[400];
32 printf("CREATE TABLE t1(f1 INT,f2 BIGINT,f3 DOUBLE,f4 DATE);\n");
33 strcpy(statement,"CREATE TABLE t1(f1 INT,f2 BIGINT,f3 DOUBLE,f4 DATE);");
34 int rows=0;
35 rv = stmt->prepare(statement);
36 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 2; }
37 rv = stmt->execute(rows);
38 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 3; }
39 stmt->free();
40 // Show all tables
41 strcpy(statement,"GETALLTABLES;");
42 rows=0;
43 rv = stmt->prepare(statement);
44 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 4; }
45 stmt->execute(rows);
46 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 5; }
47 while(stmt->next() !=NULL) {
48 printf("Table Name is %s\n",stmt->getFieldValuePtr(2)); //stmt->getFieldValuePtr(2) returns the TABLE_NAME (src/sql/SqlStatement.cxx)
50 stmt->free();
52 //Inserting Records
53 con->beginTrans();
54 rows=0;
55 printf("INSERT INTO t1 VALUES(100,100,100,'2009-11-23');\n");
56 stmt->prepare("INSERT INTO t1 VALUES(100,100,100,'2009-11-23');");
57 stmt->execute(rows);
58 printf("INSERT INTO t1 VALUES(200,200,200,'2009-11-25');\n");
59 stmt->prepare("INSERT INTO t1 VALUES(200,200,200,'2009-11-25');");
60 stmt->execute(rows);
61 printf("INSERT INTO t1 VALUES(100,300,300,'2009-11-27');\n");
62 stmt->prepare("INSERT INTO t1 VALUES(100,300,300,'2009-11-27');");
63 stmt->execute(rows);
64 printf("INSERT INTO t1 VALUES(200,400,400,'2009-11-29');\n");
65 stmt->prepare("INSERT INTO t1 VALUES(200,400,400,'2009-11-29');");
66 stmt->execute(rows);
67 printf("INSERT INTO t1 VALUES(300,500,500,'2009-12-01');\n");
68 stmt->prepare("INSERT INTO t1 VALUES(300,500,500,'2009-12-01');");
69 stmt->execute(rows);
70 con->commit();
71 stmt->free();
73 int f1var;
74 long long f2var;
75 double f3var;
76 Date f4var;
78 //Fetching records after insert
79 printf("SELECT * FROM t1;\n");
80 strcpy(statement,"SELECT * FROM t1;");
81 rv = stmt->prepare(statement);
82 if(rv!=OK) { delete stmt; delete con; return 7; }
83 stmt->bindField(1,&f1var);
84 stmt->bindField(2,&f2var);
85 stmt->bindField(3,&f3var);
86 stmt->bindField(4,&f4var);
87 int count=0;
88 rv = con->beginTrans();
89 if(rv!=OK)return 8;
90 stmt->execute(rows);
91 while(stmt->fetch() !=NULL) {
92 if(stmt->isFldNull(1)) printf("f1(int)=NULL | ");
93 else printf("f1(int)=%d | ", f1var);
94 if(stmt->isFldNull(2)) printf("f2(bigint)=NULL | ");
95 else printf("f2(bigint)=%lld | ", f2var);
96 if(stmt->isFldNull(3)) printf("f3(double)=NULL | ");
97 else printf("f3(double)=%lf | ", f3var);
98 if(stmt->isFldNull(4)) printf("f4(date)=NULL | ");
99 else printf("f4(date)=%d-%d-%d | ", f4var.year(),f4var.month(),f4var.dayOfMonth());
100 printf("\n");
101 count++;
103 stmt->free();
104 rv = con->commit();
105 printf("%d rows selected\n",count);
107 //select count(f4) from t1;
108 int cnt=0;
109 strcpy(statement,"SELECT count(f4) FROM t1;");
110 rv = stmt->prepare(statement);
111 if(rv!=OK) { delete stmt; delete con; return 7; }
112 stmt->bindField(1,&cnt);
113 rv = con->beginTrans();
114 if(rv!=OK)return 6;
115 stmt->execute(rows);
116 while(stmt->fetch() !=NULL) {
117 printf("select count(f4) from t1 = %d\n",cnt);
119 stmt->free();
120 rv = con->commit();
122 //select f1, sum(f2), avg(f3), max(f4) from t1 group by f1;
123 long long sum = 0;
124 double avg = 0;
125 Date max;
126 strcpy(statement,"select f1, sum(f2), avg(f3), max(f4) from t1 group by f1;");
127 rv = stmt->prepare(statement);
128 if(rv!=OK) { delete stmt; delete con; return 5; }
129 stmt->bindField(1,&f1var);
130 stmt->bindField(2,&sum);
131 stmt->bindField(3,&avg);
132 stmt->bindField(4,&max);
133 rv = con->beginTrans();
134 if(rv!=OK)return 6;
135 stmt->execute(rows);
136 printf("\nselect f1, sum(f2), avg(f3), max(f4) from t1 group by f1;\n");
137 while(stmt->fetch() !=NULL) {
138 printf("f1=%d | ", f1var);
139 printf("sum(f2)=%lld | ", sum);
140 printf("avg(f3)=%lf | ", avg);
141 printf("max(f4)=%d-%d-%d | ", max.year(),max.month(),max.dayOfMonth());
142 printf("\n");
144 stmt->close();
145 rv = con->commit();
147 //select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having max(f4)>'2009/11/29';
148 strcpy(statement,"select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having max(f4)>'2009/11/29';");
149 rv = stmt->prepare(statement);
150 if(rv!=OK) { delete stmt; delete con; return 5; }
151 stmt->bindField(1,&f1var);
152 stmt->bindField(2,&sum);
153 stmt->bindField(3,&avg);
154 stmt->bindField(4,&max);
155 rv = con->beginTrans();
156 if(rv!=OK)return 6;
157 stmt->execute(rows);
158 printf("\nselect f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having max(f4)>'2009/11/29';\n");
159 while(stmt->fetch() !=NULL) {
160 printf("f1=%d | ", f1var);
161 printf("sum(f2)=%lld | ", sum);
162 printf("avg(f3)=%lf | ", avg);
163 printf("max(f4)=%d-%d-%d | ", max.year(),max.month(),max.dayOfMonth());
164 printf("\n");
166 stmt->close();
167 rv = con->commit();
169 //select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having (max(f4) >'2009/11/26' and max(f4) < '2009-11-28') or avg(f3) > 400;
170 strcpy(statement,"select f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having (max(f4) >'2009/11/26' and max(f4) < '2009-11-28') or avg(f3) > 400;");
171 rv = stmt->prepare(statement);
172 if(rv!=OK) { delete stmt; delete con; return 5; }
173 stmt->bindField(1,&f1var);
174 stmt->bindField(2,&sum);
175 stmt->bindField(3,&avg);
176 stmt->bindField(4,&max);
177 rv = con->beginTrans();
178 if(rv!=OK)return 6;
179 stmt->execute(rows);
180 printf("\nselect f1, sum(f2), avg(f3), max(f4) from t1 group by f1 having (max(f4) >'2009/11/26' and max(f4) < '2009-11-28') or avg(f3) > 400;\n");
181 while(stmt->fetch() !=NULL) {
182 printf("f1=%d | ", f1var);
183 printf("sum(f2)=%lld | ", sum);
184 printf("avg(f3)=%lf | ", avg);
185 printf("max(f4)=%d-%d-%d | ", max.year(),max.month(),max.dayOfMonth());
186 printf("\n");
188 stmt->close();
189 rv = con->commit();
191 //Droping table
192 strcpy(statement,"DROP TABLE t1;");
193 rv = stmt->prepare(statement);
194 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 9; }
195 rv = stmt->execute(rows);
196 if(rv!=OK) { delete stmt; con->disconnect(); delete con; return 10; }
197 printf("Table dropped\n");
198 stmt->free();
199 con->disconnect();
200 printf("Connection Closed\n");
202 delete stmt; delete con;
203 return 0;