1
/***************************************************************************
2
my_param.c - description
5
copyright : (C) MySQL AB 1995-2002, www.mysql.com
6
author : venu ( venu@mysql.com )
7
***************************************************************************/
9
/***************************************************************************
11
* This program is free software; you can redistribute it and/or modify *
12
* it under the terms of the GNU General Public License as published by *
13
* the Free Software Foundation; either version 2 of the License, or *
14
* (at your option) any later version. *
16
***************************************************************************/
18
/***************************************************************************
20
* This is a basic sample to demonstrate how to insert or delete or *
21
* update data in the table using parameters *
23
***************************************************************************/
25
#include "my_utility.h" /* MyODBC 3.51 sample utility header */
27
/********************************************************
29
*********************************************************/
30
void my_init_table(SQLHDBC hdbc, SQLHSTMT hstmt)
34
printf("\nmy_init_table:\n");
36
/* drop table 'my_demo_param' if it already exists */
37
printf(" creating table 'my_demo_param'\n");
39
rc = SQLExecDirect(hstmt,"DROP TABLE if exists my_demo_param",SQL_NTS);
42
/* commit the transaction */
43
rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
46
/* create the table 'my_demo_param' */
47
rc = SQLExecDirect(hstmt,"CREATE TABLE my_demo_param(\
49
auto int primary key auto_increment,\
51
timestamp timestamp(14))",SQL_NTS);
54
/* commit the transaction*/
55
rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
59
/********************************************************
60
* prints the statement resultset *
61
*********************************************************/
62
int my_print_resultset(SQLHSTMT hstmt)
65
SQLUINTEGER nRowCount=0, pcColDef;
66
SQLCHAR szColName[MAX_NAME_LEN];
67
SQLCHAR szData[MAX_COLUMNS][MAX_ROW_DATA_LEN]={0};
68
SQLSMALLINT nIndex,ncol,pfSqlType, pcbScale, pfNullable;
70
/* get total number of columns from the resultset */
71
rc = SQLNumResultCols(hstmt,&ncol);
74
/* print the column names and do the row bind */
75
for(nIndex = 1; nIndex <= ncol; nIndex++)
77
rc = SQLDescribeCol(hstmt,nIndex,szColName, MAX_NAME_LEN+1, NULL,
78
&pfSqlType,&pcColDef,&pcbScale,&pfNullable);
81
printf(" %s\t",szColName);
83
rc = SQLBindCol(hstmt,nIndex, SQL_C_CHAR, szData[nIndex-1],
84
MAX_ROW_DATA_LEN+1,NULL);
88
printf("\n -------------------------------------------\n");
90
/* now fetch row by row */
92
while(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
95
for(nIndex=0; nIndex< ncol; nIndex++)
96
printf(" %s\t",szData[nIndex]);
101
SQLFreeStmt(hstmt,SQL_UNBIND);
103
printf("\n total rows fetched:%d\n",nRowCount);
105
/* free the statement row bind resources */
106
rc = SQLFreeStmt(hstmt, SQL_UNBIND);
109
/* free the statement cursor */
110
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
116
/********************************************************
117
* insert data using parameters *
118
*********************************************************/
119
void my_param_insert(SQLHDBC hdbc, SQLHSTMT hstmt)
125
printf("\nmy_param_insert:\n");
127
/* prepare the insert statement with parameters */
128
rc = SQLPrepare(hstmt,"INSERT INTO my_demo_param(id,name) VALUES(?,?)",SQL_NTS);
131
/* now supply data to parameter 1 and 2 */
132
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
133
SQL_C_LONG, SQL_INTEGER, 0,0,
137
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
138
SQL_C_CHAR, SQL_CHAR, 0,0,
139
name, sizeof(name), NULL);
142
/* now insert 10 rows of data */
143
for (id = 0; id < 10; id++)
145
sprintf(name,"MySQL%d",id);
147
rc = SQLExecute(hstmt);
151
/* Free statement param resorces */
152
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
155
/* Free statement cursor resorces */
156
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
159
/* commit the transaction */
160
rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
163
/* Now fetch and verify the data */
164
rc = SQLExecDirect(hstmt, "SELECT * FROM my_demo_param",SQL_NTS);
167
assert(10 == my_print_resultset(hstmt));
170
/********************************************************
171
* update data using parameters *
172
*********************************************************/
173
void my_param_update(SQLHDBC hdbc, SQLHSTMT hstmt)
176
SQLINTEGER id=9, nRowCount;
177
SQLCHAR name[]="update";
179
printf("\nmy_param_update:\n");
181
/* prepare the insert statement with parameters */
182
rc = SQLPrepare(hstmt,"UPDATE my_demo_param set name = ? WHERE id = ?",SQL_NTS);
185
/* now supply data to parameter 1 and 2 */
186
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
187
SQL_C_CHAR, SQL_CHAR, 0,0,
188
name, sizeof(name), NULL);
191
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
192
SQL_C_LONG, SQL_INTEGER, 0,0,
196
/* now execute the update statement */
197
rc = SQLExecute(hstmt);
200
/* check the rows affected by the update statement */
201
rc = SQLRowCount(hstmt, &nRowCount);
203
printf("\n total rows updated:%d\n",nRowCount);
204
assert( nRowCount == 1);
206
/* Free statement param resorces */
207
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
210
/* Free statement cursor resorces */
211
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
214
/* commit the transaction */
215
rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
218
/* Now fetch and verify the data */
219
rc = SQLExecDirect(hstmt, "SELECT * FROM my_demo_param",SQL_NTS);
222
assert(10 == my_print_resultset(hstmt));
225
/********************************************************
226
* delete data using parameters *
227
*********************************************************/
228
void my_param_delete(SQLHDBC hdbc, SQLHSTMT hstmt)
231
SQLINTEGER id, nRowCount;
233
printf("\nmy_param_delete:\n");
235
/* supply data to parameter 1 */
236
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
237
SQL_C_LONG, SQL_INTEGER, 0,0,
241
/* execute the DELETE STATEMENT to delete 5th row */
243
rc = SQLExecDirect(hstmt,"DELETE FROM my_demo_param WHERE id = ?",SQL_NTS);
246
/* check the rows affected by the update statement */
247
rc = SQLRowCount(hstmt, &nRowCount);
249
printf(" total rows deleted:%d\n",nRowCount);
250
assert( nRowCount == 1);
252
/* execute the DELETE STATEMENT to delete 8th row */
254
rc = SQLExecDirect(hstmt,"DELETE FROM my_demo_param WHERE id = ?",SQL_NTS);
257
/* check the rows affected by the update statement */
258
rc = SQLRowCount(hstmt, &nRowCount);
260
printf(" total rows deleted:%d\n",nRowCount);
261
assert( nRowCount == 1);
263
/* Free statement param resorces */
264
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
267
/* Free statement cursor resorces */
268
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
271
/* commit the transaction */
272
rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
275
/* Now fetch and verify the data */
276
rc = SQLExecDirect(hstmt, "SELECT * FROM my_demo_param",SQL_NTS);
279
assert(8 == my_print_resultset(hstmt));
282
/********************************************************
284
*********************************************************/
285
int main(int argc, char *argv[])
293
* show the usage string when the user asks for this
295
printf("***********************************************\n");
296
printf("usage: my_param [DSN] [UID] [PWD] \n");
297
printf("***********************************************\n");
300
* if connection string supplied through arguments, overrite
303
for(narg = 1; narg < argc; narg++)
307
else if ( narg == 2 )
309
else if ( narg == 3 )
314
* connect to MySQL server
316
myconnect(&henv,&hdbc,&hstmt);
321
my_init_table(hdbc, hstmt);
324
* insert data using parameters
326
my_param_insert(hdbc, hstmt);
331
my_param_update(hdbc, hstmt);
336
my_param_delete(hdbc, hstmt);
339
* disconnect from the server, by freeing all resources
341
mydisconnect(&henv,&hdbc,&hstmt);
added
removed
Drivers/MySQL/samples/my_param.c
