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