1 /* create table T1 with 10 fields.
2 * insert some records into it.
3 * update T1 with some Null values in the parameter.
4 noOfParamFields() should return 8.
12 AbsSqlConnection
*con
= createConnection();
13 rv
= con
->connect("root","manager");
16 AbsSqlStatement
*stmt
= createStatement();
17 stmt
->setConnection(con
);
20 if(strcmp(getenv("DSN"),"oracle")==0)
21 strcpy(statement
,"CREATE TABLE t1(f1 number(9),f2 number(4),f3 CHAR(30),f4 FLOAT,f5 FLOAT,f6 DATE,f8 TIMESTAMP,f9 number(9),f10 number(18));");
22 else if(strcmp(getenv("DSN"),"psql")==0)
23 strcpy(statement
,"CREATE TABLE t1(f1 INT,f2 SMALLINT,f3 CHAR(30),f4 FLOAT4,f5 FLOAT4,f6 DATE,f7 TIME,f8 TIMESTAMP,f9 INT,f10 BIGINT);");
24 else if(strcmp(getenv("DSN"),"sybase")==0)
25 strcpy(statement
,"CREATE TABLE t1(f1 INT NULL,f2 SMALLINT NULL,f3 CHAR(30) NULL,f4 REAL NULL,f5 REAL NULL,f6 DATE NULL,f7 TIME NULL,f8 DATETIME NULL,f9 INT NULL,f10 BIGINT NULL);");
26 else if(strcmp(getenv("DSN"),"db2")==0)
27 strcpy(statement
,"CREATE TABLE t1(f1 INT ,f2 SMALLINT ,f3 CHAR(30) ,f4 REAL ,f5 REAL ,f6 DATE ,f7 TIME ,f8 TIMESTAMP ,f9 INT ,f10 BIGINT )");
29 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);");
31 rv
= stmt
->prepare(statement
);
32 if(rv
!=OK
) { delete stmt
; delete con
; return 2; }
33 rv
= stmt
->execute(rows
);
34 if(rv
!=OK
) { delete stmt
; delete con
; return 3; }
37 printf("Table created\n");
39 if(strcmp(getenv("DSN"),"oracle")==0){
40 strcpy(statement
,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?);");
42 else if(strcmp(getenv("DSN"),"db2")==0)
43 strcpy(statement
,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?)");
45 strcpy(statement
,"INSERT INTO t1 VALUES(?,?,?,?,?,?,?,?,?,?);");
50 char f3var
[32]="jitendra";
54 f6var
.set(2008,01,21);
58 f8var
.setDate(2008,01,21);
59 f8var
.setTime(12,29,30);
61 long long f10var
= 12000;
62 char f11var
[8]="23fe";
63 rv
= stmt
->prepare(statement
);
64 if(rv
!=OK
) { delete stmt
; delete con
; return 4; }
65 if(strcmp(getenv("DSN"),"psql")==0){
70 for(int i
=0;i
<10;i
++) {
71 rv
= con
->beginTrans();
75 stmt
->setIntParam(1,f1var
);
76 stmt
->setShortParam(2,f2var
);
77 stmt
->setStringParam(3,f3var
);
78 stmt
->setFloatParam(4,f4var
);
79 stmt
->setFloatParam(5,f5var
);
80 stmt
->setDateParam(6,f6var
);
81 if(strcmp(getenv("DSN"),"oracle")==0){
82 stmt
->setTimeStampParam(7,f8var
);
83 stmt
->setIntParam(8,f9var
);
84 stmt
->setLongLongParam(9,f10var
);
86 stmt
->setTimeParam(7,f7var
);
87 stmt
->setTimeStampParam(8,f8var
);
88 stmt
->setIntParam(9,f9var
);
89 stmt
->setLongLongParam(10,f10var
);
91 // stmt->setBinaryParam(11,f11var,8);
92 rv
= stmt
->execute(rows
);
98 printf("%d Rows inserted \n",count
);
100 //**********************************************************
102 if(strcmp(getenv("DSN"),"sybase")==0)
103 strcpy(statement
,"UPDATE t1 SET f10=?,f8=?,f6=?,f5=?,f4=?,f3=? WHERE f2=?;");
104 else if(strcmp(getenv("DSN"),"db2")==0)
105 strcpy(statement
,"UPDATE t1 SET f10=?,f8=?,f6=?,f5=?,f4=?,f3=? WHERE f2=?");
107 strcpy(statement
,"UPDATE t1 SET F10=?,F8=?,F6=?,F5=?,F4=?,F3=? WHERE F2=?;");
108 rv
= stmt
->prepare(statement
);
109 if(strcmp(getenv("DSN"),"psql")==0){
114 nop
= stmt
->noOfParamFields();
115 printf("noOfParamFields return=%d\n",nop
);
117 long long f10var1
=15000;
119 f8var1
.setDate(2007,3,30);
120 f8var1
.setTime(10,25,55);
122 f6var1
.set(2007,3,30);
125 char f3var1
[10]="lakshya";
126 char f11var1
[8]="ab12";
129 for(int i
=0;i
<5;i
++) {
130 rv
= con
->beginTrans();
133 stmt
->setShortParam(7,f2var1
);
134 stmt
->setLongLongParam(1,f10var1
);
135 stmt
->setTimeStampParam(2,f8var1
);
137 stmt
->setFloatParam(4,f5var1
);
138 stmt
->setFloatParam(5,f4var1
);
140 //stmt->setBinaryParam(7,f11var1, 8);
141 rv
= stmt
->execute(rows
);
148 printf("%d Rows updated\n",count
);
149 //**********************************************************
151 strcpy(statement
,"SELECT * FROM t1;");
152 if(strcmp(getenv("DSN"),"db2")==0)
153 strcpy(statement
,"SELECT * FROM t1");
154 rv
= stmt
->prepare(statement
);
155 if(rv
!=OK
) { delete stmt
; delete con
; return 6; }
156 stmt
->bindField(1,&f1var
);
157 stmt
->bindField(2,&f2var
);
158 stmt
->bindField(3,f3var
);
159 stmt
->bindField(4,&f4var
);
160 stmt
->bindField(5,&f5var
);
161 stmt
->bindField(6,&f6var
);
162 if(strcmp(getenv("DSN"),"oracle")==0){
163 stmt
->bindField(7,&f8var
);
164 stmt
->bindField(8,&f9var
);
165 stmt
->bindField(9,&f10var
);
168 stmt
->bindField(7,&f7var
);
169 stmt
->bindField(8,&f8var
);
170 stmt
->bindField(9,&f9var
);
171 stmt
->bindField(10,&f10var
);
173 //stmt->bindField(11,f11var);
175 rv
= con
->beginTrans();
178 while(stmt
->fetch() !=NULL
) {
179 if(stmt
->isFldNull(1)) printf("F1=NULL | ");
180 else printf("F1=%d | ", f1var
);
181 if(stmt
->isFldNull(2)) printf("F2=NULL | ");
182 else printf("F2=%hd | ", f2var
);
183 if(stmt
->isFldNull(3)) printf("F3=NULL | ");
184 else printf("F3=%s | ", f3var
);
185 if(stmt
->isFldNull(4)) printf("F4=NULL | ");
186 else printf("F4=%f | ", f4var
);
187 if(stmt
->isFldNull(5)) printf("F5=NULL | ");
188 else printf("F5=%f | ", f5var
);
189 if(stmt
->isFldNull(6)) printf("F6=NULL | ");
190 else printf("F6=%02d-%02d-%02d | ",f6var
.year(), f6var
.month(), f6var
.dayOfMonth());
191 if(strcmp(getenv("DSN"),"oracle")!=0){
192 if(stmt
->isFldNull(7)) printf("F7=NULL | ");
193 else printf("F7=%02d:%02d:%02d | ", f7var
.hours(),f7var
.minutes(),f7var
.seconds());
195 if(stmt
->isFldNull(8)) printf("F8=NULL | ");
196 else printf("F8=%d-%d-%d %d:%d:%d | ", f8var
.year(),f8var
.month(),f8var
.dayOfMonth(),f8var
.hours(),f8var
.minutes(),f8var
.seconds());
197 if(stmt
->isFldNull(9)) printf("F9=NULL | ");
198 else printf("F9=%d | ", f9var
);
199 if(stmt
->isFldNull(10)) printf("F10=NULL | ");
200 else printf("F10=%lld | ", f10var
);
206 if(rv
!=OK
) { delete stmt
; delete con
; return 8; }
207 printf("Rows fetched=%d\n",count
);
209 strcpy(statement
,"DROP TABLE t1;");
210 if(strcmp(getenv("DSN"),"db2")==0)
211 strcpy(statement
,"DROP TABLE t1");
212 rv
= stmt
->prepare(statement
);
213 if(rv
!=OK
) { delete stmt
; delete con
; return 9; }
214 rv
= stmt
->execute(rows
);
215 if(rv
!=OK
) { delete stmt
; delete con
; return 10; }
217 printf("Table dropped\n");
218 stmt
->free(); delete stmt
; delete con
;