1 /* 2 * @(#)$Id: sample.c 3648 2010-06-08 22:44:25Z unsaved $ 3 * 4 * HyperSQL Database Engine 5 * 6 * Copyright (c) 2009-2010, The HSQL Development Group 7 */ 8 9 10 #include <stdio.h> 11 #ifdef _WINDOWS 12 #include <windows.h> 13 #endif 14 #include <sqlext.h> 15 // sqlext.h pulls in all other ODBC header files that we need 16 #include <string.h> 17 #include <stdlib.h> 18 19 extern int detectOdbcFailure(SQLRETURN rv, SQLHENV c, char* failMsg); 20 extern int print_ret(char* msg, int retval); 21 extern int print2_ret(char* msg, char* msg2, int retval); 22 23 /** 24 * This test HyperSQL client uses the ODBC DSN "tstdsn" to connect up to a 25 * HyperSQL server. Just configure your own DSN to use the HyperSQL ODBC 26 * driver, specifying the HyperSQL server host name, database name, user, 27 * password, etc. 28 * 29 * Sample C program accessing HyperSQL. 30 * 31 * ODBC C API ref at 32 * http://msdn.microsoft.com/en-us/library/ms714562(VS.85).aspx . 33 * Summary of functions at 34 * http://msdn.microsoft.com/en-us/library/ms712628(VS.85).aspx 35 * 36 * To build on UNIX with unixODBC:<PRE><CODE> 37 * gcc -lodbc -o sample sample.c 38 * </CODE></PRE> 39 * 40 * To build in Windows with MSVC++ (Express variant is free):<PRE><CODE> directed_acyclic(nodes: usize) -> Self41 * cl /nologo /D _WINDOWS /D ODBCVER=0x0351 /c sample.c 42 * link odbc32.lib /nologo /machine:x86 sample.obj /out:sample.exe 43 * </CODE></PRE> 44 * 45 * @author Blaine Simpson (blaine dot simpson at admc dot com) 46 */ 47 int main(int argc, char** argv) { 48 SQLRETURN odbcret; 49 SQLHENV sqlhenv; 50 SQLHENV conn; 51 SQLHSTMT stmt; 52 char *cp; 53 long in_idval; 54 const int cstrmax = 100; 55 char *in_vcval = malloc(cstrmax); 56 long out_idval; 57 char *out_vcval = malloc(cstrmax); 58 char *out_etimeval = malloc(cstrmax); 59 SQLLEN ntsval = SQL_NTS; 60 int detect; 61 62 // I. CONNECT 63 odbcret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlhenv); 64 if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO) 65 return print_ret("Failed to allocate an ODBC environment handle", 1); 66 67 odbcret = 68 SQLSetEnvAttr(sqlhenv, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3, 0); 69 if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO) 70 return print_ret("Failed to set ODBC version 3.0", 2); 71 72 odbcret = SQLAllocHandle(SQL_HANDLE_DBC, sqlhenv, &conn); 73 if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO) 74 return print_ret("Failed to allocate an ODBC connection handle", 3); 75 76 odbcret = SQLSetConnectAttr( 77 conn, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0); 78 if (odbcret != SQL_SUCCESS && odbcret != SQL_SUCCESS_WITH_INFO) 79 return print_ret("Failed to allocate an ODBC connection handle", 3); 80 // May also want to set timeout values in the same way 81 82 // Can override the DSN-defined user name and/or password here: 83 detect = detectOdbcFailure( 84 SQLConnect(conn, (SQLCHAR*) "tstdsn", SQL_NTS, (SQLCHAR*) NULL, 0, 85 (SQLCHAR*) NULL, 0), 86 conn, "Connection failure"); 87 if (detect) return detect; 88 89 90 // II. PREPARE OBJECTS FOR USE 91 detect = detectOdbcFailure( 92 SQLAllocHandle(SQL_HANDLE_STMT, conn, &stmt), conn, 93 "Failed to allocate an ODBC statement handle"); 94 if (detect) return detect; 95 96 // Just using this char pointer because some non-ANSI compilers won't let 97 // us declare a char array/pointer here. 98 cp = "DROP TABLE tsttbl IF EXISTS"; 99 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 100 "DROP statement failed"); 101 if (detect) return detect; 102 103 // Some recent change to the HyperSQL server or to unixODBC 104 // has made this commit necessary, at least on UNIX. Some other 105 // transaction control command would probably be more 106 // appropriate here. 107 detect = detectOdbcFailure(SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT), 108 conn, "COMMIT failed"); 109 if (detect) return detect; 110 111 cp = "CREATE TABLE tsttbl(\n\ 112 id BIGINT generated BY DEFAULT AS IDENTITY,\n\ 113 vc VARCHAR(20),\n\ 114 entrytime TIMESTAMP DEFAULT current_timestamp NOT NULL\n\ 115 )"; 116 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 117 "CREATE TABLE statement failed"); 118 if (detect) return detect; 119 120 detect = detectOdbcFailure(SQLCloseCursor(stmt), conn, 121 "Failed to close Cursor for re-use"); 122 if (detect) return detect; 123 124 125 // III. INSERT DATA 126 // Non-parameter INSERT 127 cp = "INSERT INTO tsttbl (id, vc) values (1, 'one')"; 128 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 129 "1st Insertion failed"); 130 if (detect) return detect; 131 132 #ifdef _WINDOWS 133 // TODO: PROBLEM with Parameterized INPUT in Windows (works fine on UNIX). 134 // For some reason, even if we are do a Prepare/Execute (and our 135 // driver is set to always use server-side Preparation), the client side 136 // is doing the substitution... and doing a bad Lob of it too. 137 // Therefore, we do all INSERTs statically for Windows here: 138 cp = "INSERT INTO tsttbl (id, vc) values (2, 'two')"; 139 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 140 "2nd Insertion failed"); 141 if (detect) return detect; 142 cp = "INSERT INTO tsttbl (id, vc) values (3, 'three')"; 143 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 144 "3rd Insertion failed"); 145 if (detect) return detect; 146 cp = "INSERT INTO tsttbl (id, vc) values (4, 'four')"; 147 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 148 "4th Insertion failed"); 149 if (detect) return detect; 150 cp = "INSERT INTO tsttbl (id, vc) values (5, 'five')"; 151 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 152 "5th Insertion failed"); 153 if (detect) return detect; 154 #else 155 // Parameterized INSERT 156 cp = "INSERT INTO tsttbl (id, vc) values (?, ?)"; 157 detect = detectOdbcFailure(SQLPrepare(stmt, (SQLCHAR*) cp, SQL_NTS), conn, 158 "Preparation of Insertion stmt failed"); 159 if (detect) return detect; 160 detect = detectOdbcFailure( 161 SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT, 162 0, 0, &in_idval, 0, NULL), conn, 163 "Bind of 'id' input failed"); 164 if (detect) return detect; 165 detect = detectOdbcFailure( 166 SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 167 20, 0, in_vcval, cstrmax, &ntsval), conn, 168 "Bind of 'vc' input failed"); 169 if (detect) return detect; 170 171 in_idval = 2; 172 strcpy(in_vcval, "two"); 173 detect = detectOdbcFailure(SQLExecute(stmt), conn, 174 "Insertion of 2nd row failed"); 175 if (detect) return detect; 176 in_idval = 3; 177 strcpy(in_vcval, "three"); 178 detect = detectOdbcFailure(SQLExecute(stmt), conn, 179 "Insertion of 3rd row failed"); 180 if (detect) return detect; 181 in_idval = 4; 182 strcpy(in_vcval, "four"); 183 detect = detectOdbcFailure(SQLExecute(stmt), conn, 184 "Insertion of 4th row failed"); 185 if (detect) return detect; 186 in_idval = 5; 187 strcpy(in_vcval, "five"); 188 detect = detectOdbcFailure(SQLExecute(stmt), conn, 189 "Insertion of 5th row failed"); 190 if (detect) return detect; 191 #endif 192 193 detect = detectOdbcFailure(SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT), 194 conn, "COMMIT failed"); 195 if (detect) return detect; 196 197 detect = detectOdbcFailure(SQLCloseCursor(stmt), conn, 198 "Failed to close Cursor for re-use"); 199 if (detect) return detect; 200 201 202 // IV. QUERIES 203 // Non-Parameter QUERY 204 cp = "SELECT * FROM tsttbl WHERE id < 3"; 205 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 206 "Non-parameter query failed"); 207 // Would return SQL_NO_DATA if no rows inserted. 208 // Don't need to bind until before fetches are performed. 209 if (detect) return detect; 210 detect = detectOdbcFailure( 211 SQLBindCol(stmt, 1, SQL_C_SLONG, &out_idval, 0, NULL), conn, 212 "Bind of 'id' output failed"); 213 if (detect) return detect; 214 detect = detectOdbcFailure( 215 SQLBindCol(stmt, 2, SQL_C_CHAR, out_vcval, cstrmax, &ntsval), 216 conn, "Bind of 'vc' output failed"); 217 if (detect) return detect; 218 detect = detectOdbcFailure( 219 SQLBindCol(stmt, 3, SQL_C_CHAR, out_etimeval, cstrmax, &ntsval), 220 conn, "Bind of 'entrytime' output failed"); 221 if (detect) return detect; 222 223 while ((odbcret = SQLFetch(stmt)) != SQL_NO_DATA) { 224 if (detectOdbcFailure(odbcret, conn, "Fetch failed")) return detect; 225 printf("%dl|%s|%s\n", out_idval, out_vcval, out_etimeval); 226 } 227 228 detect = detectOdbcFailure(SQLCloseCursor(stmt), conn, 229 "Failed to close Cursor for re-use"); 230 if (detect) return detect; 231 232 #if _WINDOWS 233 // Input parameters not working on Windows. See comment above. 234 cp = "SELECT * FROM tsttbl WHERE id > 3"; 235 detect = detectOdbcFailure(SQLExecDirect(stmt, cp, SQL_NTS), conn, 236 "Non-parameter query failed"); 237 // Would return SQL_NO_DATA if no rows inserted. 238 // Don't need to bind until before fetches are performed. 239 if (detect) return detect; 240 detect = detectOdbcFailure( 241 SQLBindCol(stmt, 1, SQL_C_SLONG, &out_idval, 0, NULL), conn, 242 "Bind of 'id' output failed"); 243 if (detect) return detect; 244 detect = detectOdbcFailure( 245 SQLBindCol(stmt, 2, SQL_C_CHAR, out_vcval, cstrmax, &ntsval), 246 conn, "Bind of 'vc' output failed"); 247 if (detect) return detect; 248 detect = detectOdbcFailure( 249 SQLBindCol(stmt, 3, SQL_C_CHAR, out_etimeval, cstrmax, &ntsval), 250 conn, "Bind of 'entrytime' output failed"); 251 if (detect) return detect; 252 253 while ((odbcret = SQLFetch(stmt)) != SQL_NO_DATA) { 254 if (detectOdbcFailure(odbcret, conn, "Fetch failed")) return detect; 255 printf("%dl|%s|%s\n", out_idval, out_vcval, out_etimeval); 256 } 257 #else 258 259 // Parameterized QUERY 260 cp = "SELECT * FROM tsttbl WHERE id > ?"; 261 detect = detectOdbcFailure(SQLPrepare(stmt, (SQLCHAR*) cp, SQL_NTS), conn, 262 "Preparation of Query stmt failed"); 263 if (detect) return detect; 264 in_idval = 3; 265 detect = detectOdbcFailure( 266 SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT, 267 0, 0, &in_idval, 0, NULL), conn, 268 "Bind of 'id' input failed"); 269 if (detect) return detect; 270 detect = detectOdbcFailure(SQLExecute(stmt), conn, 271 "Parameterized query failed"); 272 // Would return SQL_NO_DATA if no rows selected 273 // Don't need to bind until before fetches are performed. 274 if (detect) return detect; 275 detect = detectOdbcFailure( 276 SQLBindCol(stmt, 1, SQL_C_SLONG, &out_idval, 0, NULL), conn, 277 "Bind of 'id' output failed"); 278 if (detect) return detect; 279 detect = detectOdbcFailure( 280 SQLBindCol(stmt, 2, SQL_C_CHAR, out_vcval, cstrmax, &ntsval), 281 conn, "Bind of 'vc' output failed"); 282 if (detect) return detect; 283 detect = detectOdbcFailure( 284 SQLBindCol(stmt, 3, SQL_C_CHAR, out_etimeval, cstrmax, &ntsval), 285 conn, "Bind of 'entrytime' output failed"); 286 if (detect) return detect; 287 #endif 288 289 while ((odbcret = SQLFetch(stmt)) != SQL_NO_DATA) { 290 if (detectOdbcFailure(odbcret, conn, "Fetch failed")) return detect; 291 printf("%dl|%s|%s\n", out_idval, out_vcval, out_etimeval); 292 } 293 294 detect = detectOdbcFailure(SQLCloseCursor(stmt), conn, 295 "Failed to close Cursor"); 296 if (detect) return detect; 297 298 SQLDisconnect(conn); 299 SQLFreeHandle(SQL_HANDLE_DBC, conn); 300 SQLFreeHandle(SQL_HANDLE_ENV, sqlhenv); 301 //return print_ret("Success", 0); 302 return 0; 303 } 304 305 /** 306 * Displays error message and prepare for program exit. 307 */ 308 int barf(SQLHENV c, char* failMsg) { 309 char sqlhmsg[200], sqlhstat[10]; 310 SQLSMALLINT junksmall; 311 SQLINTEGER errint; 312 313 SQLGetDiagRec(SQL_HANDLE_DBC, c, 1, sqlhstat, &errint, 314 sqlhmsg, 100, &junksmall); 315 return print2_ret(failMsg, sqlhmsg, 1); 316 } 317 318 /** 319 * Displays error message and prepare for program exit if the given 320 * rv indicates ODBC failure. 321 */ 322 int detectOdbcFailure(SQLRETURN rv, SQLHENV c, char* failMsg) { 323 if (rv == SQL_SUCCESS || rv == SQL_SUCCESS_WITH_INFO) return 0; 324 return barf(c, failMsg); 325 } 326 327 /** 328 * 2-param wrapper for print2_ret() function. 329 */ 330 int print_ret(char* msg, int retval) { 331 return print2_ret(msg, (char*) NULL, retval); 332 } 333 334 /** 335 * Displays message to stderr and returns given value. 336 * 337 * Function name here is a hack, because I don't remember how to overload C 338 * functions (in a portable way). 339 */ 340 int print2_ret(char* msg, char* msg2, int retval) { 341 fputs(msg, stderr); 342 fputc('\n', stderr); 343 if (msg2 != NULL) { 344 fputs(msg2, stderr); 345 fputc('\n', stderr); 346 } 347 return retval; 348 } 349