1 //Use Connection String "DSN=mycsql;MODE=csql;SERVER=127.0.0.1;PORT=5678;" for Connect Data Source
2 //create table T1 with 10 fields.insert some record.
3 // update some fields with where clause(f2=?)
5 // Author : Jitendra Lenka
14 inline void checkrc(int rc
,int line
)
18 printf("ERROR %d at line %d\n",rc
,line
);
24 int InsertTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
29 char f3
[20]="jitendra";
36 SQLINTEGER slen
= SQL_NTS
;
48 SQL_TIMESTAMP_STRUCT timestamp
;
49 timestamp
.year
= 2008;
55 timestamp
.second
= 10;
56 timestamp
.fraction
= 764576;
58 ret
= SQLPrepare(stmt
,(unsigned char*)"INSERT INTO T1 VALUES(?,?,?,?,?,?,?,?,?,?)",SQL_NTS
);
59 checkrc(ret
,__LINE__
);
61 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SLONG
,SQL_INTEGER
,0,0,&f1
,0,NULL
);
62 checkrc(ret
,__LINE__
);
64 ret
= SQLBindParameter(stmt
,2,SQL_PARAM_INPUT
,SQL_C_SSHORT
,SQL_SMALLINT
,0,0,&f2
,0,NULL
);
65 checkrc(ret
,__LINE__
);
67 ret
= SQLBindParameter(stmt
,3,SQL_PARAM_INPUT
,SQL_C_CHAR
,SQL_CHAR
,196,0,(void*)f3
,0,&slen
);
68 checkrc(ret
,__LINE__
);
70 ret
= SQLBindParameter(stmt
,4,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f4
,0,NULL
);
71 checkrc(ret
,__LINE__
);
73 ret
= SQLBindParameter(stmt
,5,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f5
,0,NULL
);
74 checkrc(ret
,__LINE__
);
76 ret
= SQLBindParameter(stmt
,6,SQL_PARAM_INPUT
,SQL_C_TYPE_DATE
,SQL_TYPE_DATE
,196,0,&date
,sizeof(date
),&slen
);
77 checkrc(ret
,__LINE__
);
79 ret
= SQLBindParameter(stmt
,7,SQL_PARAM_INPUT
,SQL_C_TYPE_TIME
,SQL_TYPE_TIME
,196,0,&time
,sizeof(time
),&slen
);
80 checkrc(ret
,__LINE__
);
82 ret
= SQLBindParameter(stmt
,8,SQL_PARAM_INPUT
,SQL_C_TYPE_TIMESTAMP
,SQL_TYPE_TIMESTAMP
,196,0,×tamp
,sizeof(timestamp
),&slen
);
83 checkrc(ret
,__LINE__
);
85 ret
= SQLBindParameter(stmt
,9,SQL_PARAM_INPUT
,SQL_C_TINYINT
,SQL_TINYINT
,0,0,&f9
,0,NULL
);
86 checkrc(ret
,__LINE__
);
88 ret
= SQLBindParameter(stmt
,10,SQL_PARAM_INPUT
,SQL_C_SBIGINT
,SQL_BIGINT
,0,0,&f10
,0,NULL
);
89 checkrc(ret
,__LINE__
);
96 ret
= SQLExecute(stmt
);
97 checkrc(ret
,__LINE__
);
99 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
100 checkrc(ret
,__LINE__
);
105 printf("%d Rows Inserted\n",count
);
109 //*******************************************************************
110 int UpdateTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
119 SQL_DATE_STRUCT f6Date
;
120 SQL_TIMESTAMP_STRUCT f8Timestamp
;
125 ret
= SQLPrepare(stmt
,(unsigned char*)"UPDATE T1 SET F10=? ,F8=?,F6=?,F5=?,F4=?,F3=? WHERE F2=? ",SQL_NTS
);
126 checkrc(ret
,__LINE__
);
129 SQLNumParams(stmt
,&nop
);
130 printf("Number of parameters=%d\n",nop
);
132 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SBIGINT
,SQL_BIGINT
,0,0,&f10temp
,0,NULL
);
133 checkrc(ret
,__LINE__
);
135 ret
= SQLBindParameter(stmt
,2,SQL_PARAM_INPUT
,SQL_C_TYPE_TIMESTAMP
,SQL_TYPE_TIMESTAMP
,196,0,&f8Timestamp
,sizeof(f8Timestamp
),&slen
);
136 checkrc(ret
,__LINE__
);
138 ret
= SQLBindParameter(stmt
,3,SQL_PARAM_INPUT
,SQL_C_TYPE_DATE
,SQL_TYPE_DATE
,196,0,&f6Date
,sizeof(f6Date
),&slen
);
139 checkrc(ret
,__LINE__
);
141 ret
= SQLBindParameter(stmt
,4,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_REAL
,0,0,&f5temp
,0,NULL
);
142 checkrc(ret
,__LINE__
);
144 ret
= SQLBindParameter(stmt
,5,SQL_PARAM_INPUT
,SQL_C_FLOAT
,SQL_FLOAT
,0,0,&f4temp
,0,NULL
);
145 checkrc(ret
,__LINE__
);
147 ret
= SQLBindParameter(stmt
,6,SQL_PARAM_INPUT
,SQL_C_CHAR
,SQL_CHAR
,196,0,(void*)f3temp
,0,&slen
);
148 checkrc(ret
,__LINE__
);
150 ret
= SQLBindParameter(stmt
,7,SQL_PARAM_INPUT
,SQL_C_SSHORT
,SQL_INTEGER
,0,0,&f2temp
,0,NULL
);
151 checkrc(ret
,__LINE__
);
152 //**************************************************************************************************
154 f2temp
=20;int count1
=0;
158 strcpy(f3temp
,"Lakshya");
166 f8Timestamp
.year
=2009;
167 f8Timestamp
.month
=12;
170 f8Timestamp
.minute
=35;
171 f8Timestamp
.second
=55;
172 f8Timestamp
.fraction
=1234;
175 ret
= SQLExecute(stmt
);
176 checkrc(ret
,__LINE__
);
178 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
179 checkrc(ret
,__LINE__
);
182 printf("%d Rows updated\n",count1
);
185 int FetchTest(SQLHANDLE env
,SQLHANDLE dbc
,SQLHANDLE stmt
)
188 long long int f10
=12000;
190 SQL_TIMESTAMP_STRUCT timestamp
;
191 timestamp
.year
= 2007;
196 timestamp
.minute
= 15;
197 timestamp
.second
= 30;
199 SQL_DATE_STRUCT date
;
206 char f3
[10]="LAKSHYA";
208 ret
= SQLPrepare(stmt
,(unsigned char*)"SELECT F10,F8,F6,F5,F4,F3 FROM T1",SQL_NTS
);
209 checkrc(ret
,__LINE__
);
211 ret
= SQLBindCol(stmt
,1,SQL_C_SBIGINT
,&f10
,0,NULL
);
212 checkrc(ret
,__LINE__
);
214 ret
= SQLBindCol(stmt
,2,SQL_C_TYPE_TIMESTAMP
,×tamp
,sizeof(timestamp
),NULL
);
215 checkrc(ret
,__LINE__
);
217 ret
= SQLBindCol(stmt
,3,SQL_C_TYPE_DATE
,&date
,sizeof(date
),NULL
);
218 checkrc(ret
,__LINE__
);
220 ret
= SQLBindCol(stmt
,4,SQL_C_FLOAT
,&f5
,0,NULL
);
221 checkrc(ret
,__LINE__
);
223 ret
= SQLBindCol(stmt
,5,SQL_C_FLOAT
,&f4
,0,NULL
);
224 checkrc(ret
,__LINE__
);
226 ret
= SQLBindCol(stmt
,6,SQL_C_CHAR
,f3
,sizeof(f3
),NULL
);
227 checkrc(ret
,__LINE__
);
230 ret
= SQLExecute(stmt
);
231 checkrc(ret
,__LINE__
);
232 printf("Fetching starts on table 't1'\n");
233 while(SQL_SUCCEEDED(ret
=SQLFetch(stmt
)))
236 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
);
240 ret
= SQLCloseCursor(stmt
);
241 checkrc(ret
,__LINE__
);
243 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
244 checkrc(ret
,__LINE__
);
246 printf("Total row fetched=%d\n",count
);
259 SQLCHAR outstr
[1024];
260 SQLSMALLINT outstrlen
;
262 ret
= SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
,&env
);
263 checkrc(ret
,__LINE__
);
265 SQLSetEnvAttr(env
,SQL_ATTR_ODBC_VERSION
,(void *)SQL_OV_ODBC3
,0);
267 ret
= SQLAllocHandle(SQL_HANDLE_DBC
,env
,&dbc
);
268 checkrc(ret
,__LINE__
);
270 ret
= SQLConnect (dbc
,
271 (SQLCHAR
*) "DSN=mycsql;MODE=csql;SERVER=127.0.0.1;PORT=5678;", (SQLSMALLINT
) strlen ("DSN=mycsql;MODE=csql;SERVER=127.0.0.1;PORT=5678;"),
273 (SQLSMALLINT
) strlen ("root"),
274 (SQLCHAR
*) "manager",
275 (SQLSMALLINT
) strlen (""));
277 checkrc(ret
,__LINE__
);
279 if(SQL_SUCCEEDED(ret
))
281 printf("\nConnected to the datastring\n");
287 printf("error in connection\n");
289 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
290 checkrc(ret
,__LINE__
);
292 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
293 checkrc(ret
,__LINE__
);
297 ret
= SQLAllocHandle(SQL_HANDLE_STMT
,dbc
,&stmt
);
298 checkrc(ret
,__LINE__
);
301 "CREATE TABLE T1(F1 INT,F2 SMALLINT,F3 CHAR(30),F4 FLOAT,F5 FLOAT,F6 DATE,F7 TIME,F8 TIMESTAMP,F9 TINYINT,F10 BIGINT)";
303 ret
= SQLPrepare(stmt
,table
,SQL_NTS
);
304 checkrc(ret
,__LINE__
);
306 ret
= SQLExecute(stmt
);
307 checkrc(ret
,__LINE__
);
309 printf("Table 'T1' created\n");
310 //**********************************
311 InsertTest(env
,dbc
,stmt
);
312 UpdateTest(env
,dbc
,stmt
);
313 FetchTest(env
,dbc
,stmt
);
314 //*********************************
315 SQLCHAR drop
[30]="DROP TABLE T1";
317 ret
= SQLPrepare(stmt
,drop
,SQL_NTS
);
318 checkrc(ret
,__LINE__
);
320 ret
= SQLExecute(stmt
);
321 checkrc(ret
,__LINE__
);
323 if(ret
!=SQL_SUCCESS
&& ret
!=SQL_SUCCESS_WITH_INFO
)
324 printf("Statement failed\n");
326 printf("Table 'T1' Dropped successfully\n");
330 ret
= SQLFreeHandle(SQL_HANDLE_STMT
,stmt
);
331 checkrc(ret
,__LINE__
);
333 ret
= SQLDisconnect(dbc
);
334 checkrc(ret
,__LINE__
);
336 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
337 checkrc(ret
,__LINE__
);
339 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
340 checkrc(ret
,__LINE__
);