1 // create table T1 with 10 fields.insert some record.
2 // update some fields with where clause(f2=?)
4 // Author : Jitendra Lenka
13 inline void checkrc(int rc
,int line
)
17 printf("ERROR %d at line %d\n",rc
,line
);
23 int InsertTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
28 char f3
[20]="jitendra";
35 SQLINTEGER slen
= SQL_NTS
;
47 SQL_TIMESTAMP_STRUCT timestamp
;
48 timestamp
.year
= 2008;
54 timestamp
.second
= 10;
55 timestamp
.fraction
= 764576;
57 ret
= SQLPrepare(stmt
,(unsigned char*)"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?)",SQL_NTS
);
58 checkrc(ret
,__LINE__
);
60 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SLONG
,SQL_INTEGER
,0,0,&f1
,0,NULL
);
61 checkrc(ret
,__LINE__
);
63 ret
= SQLBindParameter(stmt
,2,SQL_PARAM_INPUT
,SQL_C_SSHORT
,SQL_SMALLINT
,0,0,&f2
,0,NULL
);
64 checkrc(ret
,__LINE__
);
66 ret
= SQLBindParameter(stmt
,3,SQL_PARAM_INPUT
,SQL_C_CHAR
,SQL_CHAR
,196,0,(void*)f3
,0,&slen
);
67 checkrc(ret
,__LINE__
);
69 ret
= SQLBindParameter(stmt
,4,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f4
,0,NULL
);
70 checkrc(ret
,__LINE__
);
72 ret
= SQLBindParameter(stmt
,5,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f5
,0,NULL
);
73 checkrc(ret
,__LINE__
);
75 ret
= SQLBindParameter(stmt
,6,SQL_PARAM_INPUT
,SQL_C_TYPE_DATE
,SQL_TYPE_DATE
,196,0,&date
,sizeof(date
),&slen
);
76 checkrc(ret
,__LINE__
);
78 ret
= SQLBindParameter(stmt
,7,SQL_PARAM_INPUT
,SQL_C_TYPE_TIME
,SQL_TYPE_TIME
,196,0,&time
,sizeof(time
),&slen
);
79 checkrc(ret
,__LINE__
);
81 ret
= SQLBindParameter(stmt
,8,SQL_PARAM_INPUT
,SQL_C_TYPE_TIMESTAMP
,SQL_TYPE_TIMESTAMP
,196,0,×tamp
,sizeof(timestamp
),&slen
);
82 checkrc(ret
,__LINE__
);
84 ret
= SQLBindParameter(stmt
,9,SQL_PARAM_INPUT
,SQL_C_TINYINT
,SQL_TINYINT
,0,0,&f9
,0,NULL
);
85 checkrc(ret
,__LINE__
);
87 ret
= SQLBindParameter(stmt
,10,SQL_PARAM_INPUT
,SQL_C_SBIGINT
,SQL_BIGINT
,0,0,&f10
,0,NULL
);
88 checkrc(ret
,__LINE__
);
95 ret
= SQLExecute(stmt
);
96 checkrc(ret
,__LINE__
);
98 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
99 checkrc(ret
,__LINE__
);
104 printf("%d Rows Inserted\n",count
);
107 //*******************************************************************
108 int UpdateTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
117 SQL_DATE_STRUCT f6Date
;
118 SQL_TIMESTAMP_STRUCT f8Timestamp
;
123 ret
= SQLPrepare(stmt
,(unsigned char*)"UPDATE T1 SET F10=? ,F8=?,F6=?,F5=?,F4=?,F3=? WHERE F2=? ",SQL_NTS
);
124 checkrc(ret
,__LINE__
);
127 SQLNumParams(stmt
,&nop
);
128 printf("Number of parameters=%d\n",nop
);
130 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SBIGINT
,SQL_BIGINT
,0,0,&f10temp
,0,NULL
);
131 checkrc(ret
,__LINE__
);
133 ret
= SQLBindParameter(stmt
,2,SQL_PARAM_INPUT
,SQL_C_TYPE_TIMESTAMP
,SQL_TYPE_TIMESTAMP
,196,0,&f8Timestamp
,sizeof(f8Timestamp
),&slen
);
134 checkrc(ret
,__LINE__
);
136 ret
= SQLBindParameter(stmt
,3,SQL_PARAM_INPUT
,SQL_C_TYPE_DATE
,SQL_TYPE_DATE
,196,0,&f6Date
,sizeof(f6Date
),&slen
);
137 checkrc(ret
,__LINE__
);
139 ret
= SQLBindParameter(stmt
,4,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_REAL
,0,0,&f5temp
,0,NULL
);
140 checkrc(ret
,__LINE__
);
142 ret
= SQLBindParameter(stmt
,5,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f4temp
,0,NULL
);
143 checkrc(ret
,__LINE__
);
145 ret
= SQLBindParameter(stmt
,6,SQL_PARAM_INPUT
,SQL_C_CHAR
,SQL_CHAR
,196,0,(void*)f3temp
,0,&slen
);
146 checkrc(ret
,__LINE__
);
148 ret
= SQLBindParameter(stmt
,7,SQL_PARAM_INPUT
,SQL_C_SSHORT
,SQL_INTEGER
,0,0,&f2temp
,0,NULL
);
149 checkrc(ret
,__LINE__
);
150 //**************************************************************************************************
152 f2temp
=20;int count1
=0;
156 strcpy(f3temp
,"Lakshya");
164 f8Timestamp
.year
=2009;
165 f8Timestamp
.month
=12;
168 f8Timestamp
.minute
=35;
169 f8Timestamp
.second
=55;
170 f8Timestamp
.fraction
=1234;
173 ret
= SQLExecute(stmt
);
174 checkrc(ret
,__LINE__
);
176 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
177 checkrc(ret
,__LINE__
);
180 printf("%d Rows updated\n",count1
);
183 int FetchTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
186 long long int f10
=12000;
188 SQL_TIMESTAMP_STRUCT timestamp
;
189 timestamp
.year
= 2007;
194 timestamp
.minute
= 15;
195 timestamp
.second
= 30;
197 SQL_DATE_STRUCT date
;
204 char f3
[10]="LAKSHYA";
206 ret
= SQLPrepare(stmt
,(unsigned char*)"SELECT F10,F8,F6,F5,F4,F3 FROM T1",SQL_NTS
);
207 checkrc(ret
,__LINE__
);
209 ret
= SQLBindCol(stmt
,1,SQL_C_SBIGINT
,&f10
,0,NULL
);
210 checkrc(ret
,__LINE__
);
212 ret
= SQLBindCol(stmt
,2,SQL_C_TYPE_TIMESTAMP
,×tamp
,sizeof(timestamp
),NULL
);
213 checkrc(ret
,__LINE__
);
215 ret
= SQLBindCol(stmt
,3,SQL_C_TYPE_DATE
,&date
,sizeof(date
),NULL
);
216 checkrc(ret
,__LINE__
);
218 ret
= SQLBindCol(stmt
,4,SQL_C_FLOAT
,&f5
,0,NULL
);
219 checkrc(ret
,__LINE__
);
221 ret
= SQLBindCol(stmt
,5,SQL_C_FLOAT
,&f4
,0,NULL
);
222 checkrc(ret
,__LINE__
);
224 ret
= SQLBindCol(stmt
,6,SQL_C_CHAR
,f3
,sizeof(f3
),NULL
);
225 checkrc(ret
,__LINE__
);
228 ret
= SQLExecute(stmt
);
229 checkrc(ret
,__LINE__
);
230 printf("Fetching starts on table 't1'\n");
231 while(SQL_SUCCEEDED(ret
=SQLFetch(stmt
)))
234 printf("F10=%lld\tTIMESTAMP=%d-%d-%d %d:%d:%d\tDATE=%d-%d-%d\tF5=%f\tF4=%f\tF3=%s\n",f10
,timestamp
.year
,timestamp
.month
,timestamp
.day
,timestamp
.hour
,timestamp
.minute
,timestamp
.second
,date
.year
,date
.month
,date
.day
,f5
,f4
,f3
);
238 ret
= SQLCloseCursor(stmt
);
239 checkrc(ret
,__LINE__
);
241 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
242 checkrc(ret
,__LINE__
);
244 printf("Total row fetched=%d\n",count
);
256 SQLCHAR outstr
[1024];
257 SQLSMALLINT outstrlen
;
259 ret
= SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
,&env
);
260 checkrc(ret
,__LINE__
);
262 SQLSetEnvAttr(env
,SQL_ATTR_ODBC_VERSION
,(void *)SQL_OV_ODBC3
,0);
264 ret
= SQLAllocHandle(SQL_HANDLE_DBC
,env
,&dbc
);
265 checkrc(ret
,__LINE__
);
267 ret
= SQLConnect (dbc
,
268 (SQLCHAR
*) "test", (SQLSMALLINT
) strlen ("test"),
270 (SQLSMALLINT
) strlen ("root"),
271 (SQLCHAR
*) "manager",
272 (SQLSMALLINT
) strlen (""));
274 checkrc(ret
,__LINE__
);
276 if(SQL_SUCCEEDED(ret
))
278 printf("\nConnected to the datastring\n");
284 printf("error in connection\n");
286 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
287 checkrc(ret
,__LINE__
);
289 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
290 checkrc(ret
,__LINE__
);
294 ret
= SQLAllocHandle(SQL_HANDLE_STMT
,dbc
,&stmt
);
295 checkrc(ret
,__LINE__
);
298 "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
300 ret
= SQLPrepare(stmt
,table
,SQL_NTS
);
301 checkrc(ret
,__LINE__
);
303 ret
= SQLExecute(stmt
);
304 checkrc(ret
,__LINE__
);
306 printf("Table 'T1' created\n");
307 //**********************************
308 InsertTest(env
,dbc
,stmt
);
309 UpdateTest(env
,dbc
,stmt
);
310 FetchTest(env
,dbc
,stmt
);
311 //*********************************
312 SQLCHAR drop
[30]="DROP TABLE T1";
314 ret
= SQLPrepare(stmt
,drop
,SQL_NTS
);
315 checkrc(ret
,__LINE__
);
317 ret
= SQLExecute(stmt
);
318 checkrc(ret
,__LINE__
);
320 if(ret
!=SQL_SUCCESS
&& ret
!=SQL_SUCCESS_WITH_INFO
)
321 printf("Statement failed\n");
323 printf("Table 'T1' Dropped successfully\n");
327 ret
= SQLFreeHandle(SQL_HANDLE_STMT
,stmt
);
328 checkrc(ret
,__LINE__
);
330 ret
= SQLDisconnect(dbc
);
331 checkrc(ret
,__LINE__
);
333 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
334 checkrc(ret
,__LINE__
);
336 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
337 checkrc(ret
,__LINE__
);