1 /**************************************************************************
2 * Copyright (C) 2008 by www.databasecache.com *
3 * Contact : praba_tuty@databasecache.com *
5 * THis program is free software; you can redistribute it and/or modify *
6 * it under the terms of the GNU General Public License as Published by *
7 * the Fre Software Foundation;either version 2 of the License, or *
8 * (at your option)any later version. *
10 * This program is distributed in the hope that will be useful, *
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, See the *
13 * GNU General License for more details. *
15 **************************************************************************/
23 void checkrc(int rc
,int line
)
27 printf("Error %d at line %d\n", rc
, line
);
39 SQLSMALLINT outstrlen
;
41 //Allocate an environment handle
42 ret
= SQLAllocHandle(SQL_HANDLE_ENV
,SQL_NULL_HANDLE
,&env
);
43 checkrc(ret
,__LINE__
);
45 //we need ODBC3 support
46 SQLSetEnvAttr(env
,SQL_ATTR_ODBC_VERSION
,(void*)SQL_OV_ODBC3
,0);
48 //Allocate a Connection handle
49 ret
= SQLAllocHandle(SQL_HANDLE_DBC
,env
,&dbc
);
50 checkrc(ret
,__LINE__
);
53 //connect to Data source
54 ret
= SQLConnect (dbc
,
55 (SQLCHAR
*) "test", (SQLSMALLINT
) strlen ("test"),
57 (SQLSMALLINT
) strlen ("root"),
58 (SQLCHAR
*) "manager",
59 (SQLSMALLINT
) strlen (""));
61 //connect using unixODBC Driver Manager
62 ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)
63 "DSN=mycsql;USER=root;PASSWORD=manager;", SQL_NTS,
64 outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
66 if(SQL_SUCCEEDED(ret
))
68 printf("Connected to CSQL\n");
72 printf("error in connection\n");
73 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
74 checkrc(ret
,__LINE__
);
75 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
76 checkrc(ret
,__LINE__
);
80 // Allocation of statement handle for DDL nad DML Operation
81 ret
= SQLAllocHandle(SQL_HANDLE_STMT
,dbc
,&stmt
);
82 checkrc(ret
,__LINE__
);
84 // create table 'T1' with two fields, F1 INTEGER AND F2 CHAR.
85 SQLCHAR table
[200]= "CREATE TABLE T1(F1 INT,F2 CHAR(20))";
87 ret
= SQLPrepare(stmt
,table
,SQL_NTS
);
88 checkrc(ret
,__LINE__
);
90 ret
= SQLExecute(stmt
);
91 checkrc(ret
,__LINE__
);
93 printf("Table T1 created\n");
95 //Insert 10 Tuples into the table 'T1'
97 char name
[20]="THIRU";
98 char names
[10][20]={"Gopal", "Aruna", "Kanchana", "Vijay", "Ganga",
99 "XieLiang", "Rajesh", "Steve", "Veda", "Jitendra" };
102 SQLINTEGER slen
= SQL_NTS
;
104 ret
= SQLPrepare(stmt
,(unsigned char*)"INSERT INTO T1 VALUES(?,?);",SQL_NTS
);
105 checkrc(ret
,__LINE__
);
107 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SLONG
,SQL_INTEGER
,0,0,&id
,0,NULL
);
108 checkrc(ret
,__LINE__
);
110 ret
= SQLBindParameter(stmt
,2,SQL_PARAM_INPUT
,SQL_C_CHAR
,SQL_CHAR
,196,0,(void*)name
,0,&slen
);
111 checkrc(ret
,__LINE__
);
117 strcpy(name
,names
[i
]);
119 ret
= SQLExecute(stmt
);
120 checkrc(ret
,__LINE__
);
122 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
123 checkrc(ret
,__LINE__
);
126 printf("%d Rows inserted\n",count
);
129 //Fetch rows from the table 'T1'
132 ret
= SQLPrepare(stmt
,(unsigned char*)"SELECT * FROM T1;",SQL_NTS
);
133 checkrc(ret
,__LINE__
);
135 ret
= SQLBindCol(stmt
,1,SQL_C_SLONG
,&id1
,0,NULL
);
136 checkrc(ret
,__LINE__
);
138 ret
= SQLBindCol(stmt
,2,SQL_C_CHAR
,name
,sizeof(name
),NULL
);
139 checkrc(ret
,__LINE__
);
142 ret
= SQLExecute(stmt
);
143 checkrc(ret
,__LINE__
);
145 printf("Fetching starts on table T1 :\n");
146 while(SQL_SUCCEEDED(ret
=SQLFetch(stmt
)))
148 printf("F1:%d\tF2:%s\n",id1
,name
);
151 ret
= SQLCloseCursor(stmt
);
152 checkrc(ret
,__LINE__
);
154 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
155 checkrc(ret
,__LINE__
);
157 printf("%d rows fetched\n",count
);
159 //Delete all the rows from the table 'T1'
160 ret
= SQLPrepare(stmt
,(unsigned char*)"DELETE FROM T1 WHERE F1=?;",SQL_NTS
);
161 checkrc(ret
,__LINE__
);
163 ret
= SQLBindParameter(stmt
,1,SQL_PARAM_INPUT
,SQL_C_SLONG
,SQL_INTEGER
,0,0,&id1
,0,NULL
);
164 checkrc(ret
,__LINE__
);
171 ret
= SQLExecute(stmt
);
172 checkrc(ret
,__LINE__
);
176 ret
= SQLTransact(env
,dbc
,SQL_COMMIT
);
177 checkrc(ret
,__LINE__
);
178 printf("%d Rows deleted\n",count
);
181 // drop the table 'T1'
182 SQLCHAR drop
[50]="DROP TABLE T1";
183 ret
= SQLPrepare(stmt
,drop
,SQL_NTS
);
184 checkrc(ret
,__LINE__
);
186 ret
= SQLExecute(stmt
);
187 checkrc(ret
,__LINE__
);
188 printf("Table T1 dropped\n");
190 //Free the statement handle
191 ret
= SQLFreeHandle(SQL_HANDLE_STMT
,stmt
);
192 checkrc(ret
,__LINE__
);
194 //Disconnect from the Data source
195 ret
= SQLDisconnect(dbc
);
196 checkrc(ret
,__LINE__
);
197 printf("Disconnected from CSQL\n");
199 //Free the connection handle
200 ret
= SQLFreeHandle(SQL_HANDLE_DBC
,dbc
);
201 checkrc(ret
,__LINE__
);
203 //Free the environment handle
204 ret
= SQLFreeHandle(SQL_HANDLE_ENV
,env
);
205 checkrc(ret
,__LINE__
);