1 /* select tuple with WHERE clause having param for some fields
2 Create a table having f1 date, f2 tnt and f3 string
4 Execute SELECT * FROM T1 WHERE f1='Date Value' AND f2=? AND f3=?;
13 AbsSqlConnection
*con
= createConnection();
14 rv
= con
->connect("root","manager");
16 AbsSqlStatement
*stmt
= createStatement();
17 stmt
->setConnection(con
);
19 strcpy(statement
,"CREATE TABLE T1(F1 DATE,F2 INT,F3 CHAR(10));");
21 rv
= stmt
->prepare(statement
);
22 if(rv
!=OK
) { delete stmt
; delete con
; return 2; }
23 rv
= stmt
->execute(rows
);
24 if(rv
!=OK
) { delete stmt
; delete con
; return 3; }
26 printf("Table created\n");
28 stmt
->prepare("INSERT INTO T1 values('2008-10-21',0,'Nihar0');");
30 stmt
->prepare("INSERT INTO T1 values('2009-11-10',1,'Nihar1');");
32 stmt
->prepare("INSERT INTO T1 values('2008-10-24',2,'Nihar2');");
34 stmt
->prepare("INSERT INTO T1 values('2009-11-10',3,'Nihar3');");
36 stmt
->prepare("INSERT INTO T1 values('2008-10-21',4,'Nihar4');");
38 stmt
->prepare("INSERT INTO T1 values('2009-11-10',5,'Nihar5');");
42 //**********************************************************
45 f1var
.set(2001,01,01);
47 char f3var
[32]="CSQL";
48 printf("SELECT * FROM T1;\n");
49 strcpy(statement
,"SELECT * FROM T1;");
50 stmt
->prepare(statement
);
51 stmt
->bindField(1,&f1var
);
52 stmt
->bindField(2,&f2var
);
53 stmt
->bindField(3,f3var
);
57 while(stmt
->fetch() !=NULL
) {
58 printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var
.year(), f1var
.month(), f1var
.dayOfMonth(), f2var
,f3var
);
64 printf("%d Rows fetched\n",count
);
67 printf("SELECT * FROM T1 WHERE F1='2009-11-10';\n");
68 strcpy(statement
,"SELECT * FROM T1 WHERE F1='2009-11-10';");
69 stmt
->prepare(statement
);
70 stmt
->bindField(1,&f1var
);
71 stmt
->bindField(2,&f2var
);
72 stmt
->bindField(3,f3var
);
76 while(stmt
->fetch() !=NULL
) {
77 printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var
.year(), f1var
.month(), f1var
.dayOfMonth(), f2var
,f3var
);
83 printf("%d Rows fetched\n",count
);
86 printf("SELECT * FROM T1 where F2 >= ? and F3 <= ?;\n");
87 strcpy(statement
,"SELECT * FROM T1 where F2 >= ? and F3 <= ?;");
88 stmt
->prepare(statement
);
90 strcpy(f3var
,"Nihar3");
91 stmt
->setIntParam(1,f2var
);
92 stmt
->setStringParam(2,f3var
);
93 stmt
->bindField(1,&f1var
);
94 stmt
->bindField(2,&f2var
);
95 stmt
->bindField(3,f3var
);
99 while(stmt
->fetch() !=NULL
) {
100 printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var
.year(), f1var
.month(), f1var
.dayOfMonth(), f2var
,f3var
);
106 printf("%d Rows fetched\n",count
);
109 printf("SELECT * FROM T1 where F1='2009-11-10' and F2 >= ? and F3 <= ?;\n");
110 strcpy(statement
,"SELECT * FROM T1 where F1='2009-11-10' and F2 >= ? and F3 <= ?;");
111 stmt
->prepare(statement
);
113 strcpy(f3var
,"Nihar3");
114 stmt
->setIntParam(1,f2var
);
115 stmt
->setStringParam(2,f3var
);
116 stmt
->bindField(1,&f1var
);
117 stmt
->bindField(2,&f2var
);
118 stmt
->bindField(3,f3var
);
122 while(stmt
->fetch() !=NULL
) {
123 printf("F1=%02d-%02d-%02d | F2=%d | F3=%s",f1var
.year(), f1var
.month(), f1var
.dayOfMonth(), f2var
,f3var
);
129 printf("%d Rows fetched\n",count
);
132 strcpy(statement
,"DROP TABLE T1;");
133 rv
= stmt
->prepare(statement
);
134 if(rv
!=OK
) { delete stmt
; delete con
; return 4; }
135 rv
= stmt
->execute(rows
);
136 if(rv
!=OK
) { delete stmt
; delete con
; return 5; }
137 printf("Table dropped\n");
138 stmt
->free(); delete stmt
; delete con
;