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
);
108 //*******************************************************************
109 int UpdateTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
118 SQL_DATE_STRUCT f6Date
;
119 SQL_TIMESTAMP_STRUCT f8Timestamp
;
124 ret
= SQLPrepare(stmt
,(unsigned char*)"UPDATE T1 SET F10=? ,F8=?,F6=?,F5=?,F4=?,F3=? WHERE F2=? ",SQL_NTS
);
125 checkrc(ret
,__LINE__
);
128 SQLNumParams(stmt
,&nop
);
129 printf("Number of parameters=%d\n",nop
);
131 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SBIGINT
,SQL_BIGINT
,0,0,&f10temp
,0,NULL
);
132 checkrc(ret
,__LINE__
);
134 ret
= SQLBindParameter(stmt
,2,SQL_PARAM_INPUT
,SQL_C_TYPE_TIMESTAMP
,SQL_TYPE_TIMESTAMP
,196,0,&f8Timestamp
,sizeof(f8Timestamp
),&slen
);
135 checkrc(ret
,__LINE__
);
137 ret
= SQLBindParameter(stmt
,3,SQL_PARAM_INPUT
,SQL_C_TYPE_DATE
,SQL_TYPE_DATE
,196,0,&f6Date
,sizeof(f6Date
),&slen
);
138 checkrc(ret
,__LINE__
);
140 ret
= SQLBindParameter(stmt
,4,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_REAL
,0,0,&f5temp
,0,NULL
);
141 checkrc(ret
,__LINE__
);
143 ret
= SQLBindParameter(stmt
,5,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f4temp
,0,NULL
);
144 checkrc(ret
,__LINE__
);
146 ret
= SQLBindParameter(stmt
,6,SQL_PARAM_INPUT
,SQL_C_CHAR
,SQL_CHAR
,196,0,(void*)f3temp
,0,&slen
);
147 checkrc(ret
,__LINE__
);
149 ret
= SQLBindParameter(stmt
,7,SQL_PARAM_INPUT
,SQL_C_SSHORT
,SQL_INTEGER
,0,0,&f2temp
,0,NULL
);
150 checkrc(ret
,__LINE__
);
151 //**************************************************************************************************
153 f2temp
=20;int count1
=0;
157 strcpy(f3temp
,"Lakshya");
165 f8Timestamp
.year
=2009;
166 f8Timestamp
.month
=12;
169 f8Timestamp
.minute
=35;
170 f8Timestamp
.second
=55;
171 f8Timestamp
.fraction
=1234;
174 ret
= SQLExecute(stmt
);
175 checkrc(ret
,__LINE__
);
177 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
178 checkrc(ret
,__LINE__
);
181 printf("%d Rows updated\n",count1
);
184 int FetchTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
187 long long int f10
=12000;
189 SQL_TIMESTAMP_STRUCT timestamp
;
190 timestamp
.year
= 2007;
195 timestamp
.minute
= 15;
196 timestamp
.second
= 30;
198 SQL_DATE_STRUCT date
;
205 char f3
[10]="LAKSHYA";
207 ret
= SQLPrepare(stmt
,(unsigned char*)"SELECT F10,F8,F6,F5,F4,F3 FROM T1",SQL_NTS
);
208 checkrc(ret
,__LINE__
);
210 ret
= SQLBindCol(stmt
,1,SQL_C_SBIGINT
,&f10
,0,NULL
);
211 checkrc(ret
,__LINE__
);
213 ret
= SQLBindCol(stmt
,2,SQL_C_TYPE_TIMESTAMP
,×tamp
,sizeof(timestamp
),NULL
);
214 checkrc(ret
,__LINE__
);
216 ret
= SQLBindCol(stmt
,3,SQL_C_TYPE_DATE
,&date
,sizeof(date
),NULL
);
217 checkrc(ret
,__LINE__
);
219 ret
= SQLBindCol(stmt
,4,SQL_C_FLOAT
,&f5
,0,NULL
);
220 checkrc(ret
,__LINE__
);
222 ret
= SQLBindCol(stmt
,5,SQL_C_FLOAT
,&f4
,0,NULL
);
223 checkrc(ret
,__LINE__
);
225 ret
= SQLBindCol(stmt
,6,SQL_C_CHAR
,f3
,sizeof(f3
),NULL
);
226 checkrc(ret
,__LINE__
);
229 ret
= SQLExecute(stmt
);
230 checkrc(ret
,__LINE__
);
231 printf("Fetching starts on table 't1'\n");
232 while(SQL_SUCCEEDED(ret
=SQLFetch(stmt
)))
235 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
);
239 ret
= SQLCloseCursor(stmt
);
240 checkrc(ret
,__LINE__
);
242 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
243 checkrc(ret
,__LINE__
);
245 printf("Total row fetched=%d\n",count
);
258 SQLCHAR outstr
[1024];
259 SQLSMALLINT outstrlen
;
261 ret
= SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
,&env
);
262 checkrc(ret
,__LINE__
);
264 SQLSetEnvAttr(env
,SQL_ATTR_ODBC_VERSION
,(void *)SQL_OV_ODBC3
,0);
266 ret
= SQLAllocHandle(SQL_HANDLE_DBC
,env
,&dbc
);
267 checkrc(ret
,__LINE__
);
269 ret
= SQLConnect (dbc
,
270 (SQLCHAR
*) "test", (SQLSMALLINT
) strlen ("test"),
272 (SQLSMALLINT
) strlen ("root"),
273 (SQLCHAR
*) "manager",
274 (SQLSMALLINT
) strlen (""));
276 checkrc(ret
,__LINE__
);
278 if(SQL_SUCCEEDED(ret
))
280 printf("\nConnected to the datastring\n");
286 printf("error in connection\n");
288 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
289 checkrc(ret
,__LINE__
);
291 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
292 checkrc(ret
,__LINE__
);
296 ret
= SQLAllocHandle(SQL_HANDLE_STMT
,dbc
,&stmt
);
297 checkrc(ret
,__LINE__
);
300 "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
302 ret
= SQLPrepare(stmt
,table
,SQL_NTS
);
303 checkrc(ret
,__LINE__
);
305 ret
= SQLExecute(stmt
);
306 checkrc(ret
,__LINE__
);
308 printf("Table 'T1' created\n");
309 //**********************************
310 InsertTest(env
,dbc
,stmt
);
311 UpdateTest(env
,dbc
,stmt
);
312 FetchTest(env
,dbc
,stmt
);
313 //*********************************
314 SQLCHAR drop
[30]="DROP TABLE T1";
316 ret
= SQLPrepare(stmt
,drop
,SQL_NTS
);
317 checkrc(ret
,__LINE__
);
319 ret
= SQLExecute(stmt
);
320 checkrc(ret
,__LINE__
);
322 if(ret
!=SQL_SUCCESS
&& ret
!=SQL_SUCCESS_WITH_INFO
)
323 printf("Statement failed\n");
325 printf("Table 'T1' Dropped successfully\n");
329 ret
= SQLFreeHandle(SQL_HANDLE_STMT
,stmt
);
330 checkrc(ret
,__LINE__
);
332 ret
= SQLDisconnect(dbc
);
333 checkrc(ret
,__LINE__
);
335 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
336 checkrc(ret
,__LINE__
);
338 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
339 checkrc(ret
,__LINE__
);