Line data Source code
1 : /*
2 : * Code to test ODBC implementation.
3 : * - David Fraser, Abelon Systems 2003.
4 : */
5 :
6 : /*
7 : * TODO
8 : * remove Northwind dependency
9 : */
10 :
11 : #include "common.h"
12 :
13 : #ifdef DEBUG
14 : # define AB_FUNCT(x) do { printf x; printf("\n"); } while(0)
15 : # define AB_PRINT(x) do { printf x; printf("\n"); } while(0)
16 : #else
17 : # define AB_FUNCT(x)
18 : # define AB_PRINT(x)
19 : #endif
20 : #define AB_ERROR(x) do { printf("ERROR: "); printf x; printf("\n"); } while(0)
21 :
22 : #undef TRUE
23 : #undef FALSE
24 : enum
25 : { FALSE, TRUE };
26 : typedef int DbTestFn(void);
27 :
28 : static int RunTests(void);
29 :
30 : typedef struct
31 : {
32 : DbTestFn *testFn;
33 : const char *description;
34 : } DbTestEntry;
35 :
36 : /*
37 : * Test that makes a parameterized ODBC query using SQLPrepare and SQLExecute
38 : */
39 : static int
40 8 : TestRawODBCPreparedQuery(void)
41 : {
42 : SQLTCHAR *queryString;
43 8 : SQLLEN lenOrInd = 0;
44 8 : SQLSMALLINT supplierId = 4;
45 : int count;
46 :
47 8 : AB_FUNCT(("TestRawODBCPreparedQuery (in)"));
48 :
49 : /* INIT */
50 :
51 8 : odbc_connect();
52 :
53 : /* MAKE QUERY */
54 :
55 8 : odbc_command("CREATE TABLE #Products ("
56 : "ProductID int NOT NULL ,"
57 : "ProductName varchar (40) ,"
58 : "SupplierID int NULL ,"
59 : "CategoryID int NULL ,"
60 : "QuantityPerUnit varchar (20) ,"
61 : "UnitPrice money NULL ,"
62 : "UnitsInStock smallint NULL ,"
63 : "UnitsOnOrder smallint NULL ,"
64 : "ReorderLevel smallint NULL ,"
65 : "Discontinued bit NOT NULL "
66 : ") "
67 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(9,'Mishi Kobe Niku',4,6,'18 - 500 g pkgs.',97.00,29,0,0,1) "
68 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(10,'Ikura',4,8,'12 - 200 ml jars',31.00,31,0,0,0) "
69 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(74,'Longlife Tofu',4,7,'5 kg pkg.',10.00,4,20,5,0) "
70 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(11,'Queso Cabrales',5,4,'1 kg pkg.',21.00,22,30,30,0) "
71 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(12,'Queso Manchego La Pastora',5,4,'10 - 500 g pkgs.',38.00,86,0,0,0)");
72 48 : while (SQLMoreResults(odbc_stmt) == SQL_SUCCESS)
73 32 : continue;
74 :
75 8 : queryString = T("SELECT * FROM #Products WHERE SupplierID = ?");
76 :
77 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &supplierId, 0, &lenOrInd, "S");
78 :
79 8 : CHKPrepare(queryString, SQL_NTS, "S");
80 :
81 8 : CHKExecute("S");
82 :
83 8 : count = 0;
84 :
85 40 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
86 24 : count++;
87 : }
88 8 : AB_PRINT(("Got %d rows", count));
89 :
90 8 : if (count != 3) {
91 : /*
92 : * OK - so 3 is a magic number - it's the number of rows matching
93 : * this query from the MS sample Northwind database and is a constant.
94 : */
95 0 : AB_ERROR(("Expected %d rows - but got %d rows", 3, count));
96 0 : AB_FUNCT(("TestRawODBCPreparedQuery (out): error"));
97 0 : return FALSE;
98 : }
99 :
100 : /* CLOSEDOWN */
101 :
102 8 : odbc_disconnect();
103 :
104 8 : AB_FUNCT(("TestRawODBCPreparedQuery (out): ok"));
105 8 : return TRUE;
106 : }
107 :
108 : /*
109 : * Test that makes a parameterized ODBC query using SQLExecDirect.
110 : */
111 : static int
112 8 : TestRawODBCDirectQuery(void)
113 : {
114 8 : SQLLEN lenOrInd = 0;
115 8 : SQLSMALLINT supplierId = 1;
116 : int count;
117 :
118 8 : AB_FUNCT(("TestRawODBCDirectQuery (in)"));
119 :
120 : /* INIT */
121 :
122 8 : odbc_connect();
123 :
124 : /* MAKE QUERY */
125 :
126 8 : odbc_command("CREATE TABLE #Products ("
127 : "ProductID int NOT NULL ,"
128 : "ProductName varchar (40) ,"
129 : "SupplierID int NULL ,"
130 : "CategoryID int NULL ,"
131 : "QuantityPerUnit varchar (20) ,"
132 : "UnitPrice money NULL ,"
133 : "UnitsInStock smallint NULL ,"
134 : "UnitsOnOrder smallint NULL ,"
135 : "ReorderLevel smallint NULL ,"
136 : "Discontinued bit NOT NULL "
137 : ") "
138 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(1,'Chai',1,1,'10 boxes x 20 bags',18.00,39,0,10,0) "
139 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(2,'Chang',1,1,'24 - 12 oz bottles',19.00,17,40,25,0) "
140 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(3,'Aniseed Syrup',1,2,'12 - 550 ml bottles',10.00,13,70,25,0) "
141 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(4,'Chef Anton''s Cajun Seasoning',2,2,'48 - 6 oz jars',22.00,53,0,0,0) "
142 : "INSERT INTO #Products(ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued) VALUES(5,'Chef Anton''s Gumbo Mix',2,2,'36 boxes',21.35,0,0,0,1) ");
143 48 : while (SQLMoreResults(odbc_stmt) == SQL_SUCCESS)
144 32 : continue;
145 :
146 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &supplierId, 0, &lenOrInd, "S");
147 :
148 8 : CHKExecDirect(T("SELECT * FROM #Products WHERE SupplierID = ?"), SQL_NTS, "S");
149 :
150 8 : count = 0;
151 :
152 40 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
153 24 : count++;
154 : }
155 8 : AB_PRINT(("Got %d rows", count));
156 :
157 8 : if (count != 3) {
158 : /*
159 : * OK - so 3 is a magic number - it's the number of rows matching
160 : * this query from the MS sample Northwind database and is a constant.
161 : */
162 0 : AB_ERROR(("Expected %d rows - but got %d rows", 3, count));
163 0 : AB_FUNCT(("TestRawODBCDirectQuery (out): error"));
164 0 : return FALSE;
165 : }
166 :
167 : /* CLOSEDOWN */
168 :
169 8 : odbc_disconnect();
170 :
171 8 : AB_FUNCT(("TestRawODBCDirectQuery (out): ok"));
172 8 : return TRUE;
173 : }
174 :
175 : /*
176 : * Test that show what works and what doesn't for the poorly
177 : * documented GUID.
178 : */
179 : static int
180 8 : TestRawODBCGuid(void)
181 : {
182 : SQLRETURN status;
183 :
184 : const char *queryString;
185 : SQLLEN lenOrInd;
186 : SQLSMALLINT age;
187 : char guid[40];
188 : SQLCHAR name[20];
189 :
190 : SQLGUID sqlguid;
191 8 : int count = 0;
192 :
193 8 : AB_FUNCT(("TestRawODBCGuid (in)"));
194 :
195 8 : odbc_connect();
196 :
197 8 : if (!odbc_db_is_microsoft()) {
198 2 : odbc_disconnect();
199 2 : return TRUE;
200 : }
201 :
202 6 : AB_PRINT(("Creating #pet table"));
203 :
204 6 : queryString = "CREATE TABLE #pet (name VARCHAR(20), owner VARCHAR(20), "
205 : "species VARCHAR(20), sex CHAR(1), age INTEGER, " "guid UNIQUEIDENTIFIER DEFAULT NEWID() ); ";
206 6 : CHKExecDirect(T(queryString), SQL_NTS, "SNo");
207 :
208 6 : odbc_command_with_result(odbc_stmt, "DROP PROC GetGUIDRows");
209 :
210 6 : AB_PRINT(("Creating stored proc GetGUIDRows"));
211 :
212 6 : queryString = "CREATE PROCEDURE GetGUIDRows (@guidpar uniqueidentifier) AS \
213 : SELECT name, guid FROM #pet WHERE guid = @guidpar";
214 6 : CHKExecDirect(T(queryString), SQL_NTS, "SNo");
215 :
216 6 : AB_PRINT(("Insert row 1"));
217 :
218 6 : queryString = "INSERT INTO #pet( name, owner, species, sex, age ) \
219 : VALUES ( 'Fang', 'Mike', 'dog', 'm', 12 );";
220 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
221 :
222 6 : AB_PRINT(("Insert row 2"));
223 :
224 : /*
225 : * Ok - new row with explicit GUID, but parameterised age.
226 : */
227 6 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
228 : VALUES ( 'Splash', 'Dan', 'fish', 'm', ?, \
229 : '12345678-1234-1234-1234-123456789012' );";
230 :
231 6 : lenOrInd = 0;
232 6 : age = 3;
233 6 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &age, 0, &lenOrInd, "S");
234 :
235 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
236 6 : CHKFreeStmt(SQL_CLOSE, "S");
237 :
238 6 : AB_PRINT(("Insert row 3"));
239 : /*
240 : * Ok - new row with parameterised GUID.
241 : */
242 6 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
243 : VALUES ( 'Woof', 'Tom', 'cat', 'f', 2, ? );";
244 :
245 6 : lenOrInd = SQL_NTS;
246 6 : strcpy(guid, "87654321-4321-4321-4321-123456789abc");
247 :
248 6 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0, 0, guid, 0, &lenOrInd, "S");
249 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
250 :
251 6 : AB_PRINT(("Insert row 4"));
252 : /*
253 : * Ok - new row with parameterised GUID.
254 : */
255 6 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
256 : VALUES ( 'Spike', 'Diane', 'pig', 'f', 4, ? );";
257 :
258 6 : lenOrInd = SQL_NTS;
259 6 : strcpy(guid, "1234abcd-abcd-abcd-abcd-123456789abc");
260 :
261 6 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 36, 0, guid, 0, &lenOrInd, "S");
262 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
263 :
264 6 : AB_PRINT(("Insert row 5"));
265 : /*
266 : * Ok - new row with parameterised GUID.
267 : */
268 6 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
269 : VALUES ( 'Fluffy', 'Sam', 'dragon', 'm', 16, ? );";
270 :
271 6 : sqlguid.Data1 = 0xaabbccdd;
272 6 : sqlguid.Data2 = 0xeeff;
273 6 : sqlguid.Data3 = 0x1122;
274 6 : sqlguid.Data4[0] = 0x11;
275 6 : sqlguid.Data4[1] = 0x22;
276 6 : sqlguid.Data4[2] = 0x33;
277 6 : sqlguid.Data4[3] = 0x44;
278 6 : sqlguid.Data4[4] = 0x55;
279 6 : sqlguid.Data4[5] = 0x66;
280 6 : sqlguid.Data4[6] = 0x77;
281 6 : sqlguid.Data4[7] = 0x88;
282 :
283 6 : lenOrInd = 16;
284 6 : strcpy(guid, "1234abcd-abcd-abcd-abcd-123456789abc");
285 :
286 6 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_GUID, SQL_GUID, 16, 0, &sqlguid, 16, &lenOrInd, "S");
287 6 : status = SQLExecDirect(odbc_stmt, T(queryString), SQL_NTS);
288 6 : if (status != SQL_SUCCESS) {
289 0 : AB_ERROR(("Insert row 5 failed"));
290 0 : AB_ERROR(("Sadly this was expected in *nix ODBC. Carry on."));
291 : }
292 :
293 : /*
294 : * Now retrieve rows - especially GUID column values.
295 : */
296 6 : AB_PRINT(("retrieving name and guid"));
297 6 : queryString = "SELECT name, guid FROM #pet";
298 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
299 42 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
300 30 : count++;
301 30 : CHKGetData(1, SQL_CHAR, name, 20, 0, "S");
302 30 : CHKGetData(2, SQL_CHAR, guid, 37, 0, "S");
303 :
304 30 : AB_PRINT(("name: %-10s guid: %s", name, guid));
305 : }
306 :
307 : /*
308 : * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
309 : * state if we try SELECT again).
310 : */
311 6 : odbc_reset_statement();
312 :
313 :
314 : /*
315 : * Now retrieve rows - especially GUID column values.
316 : */
317 :
318 6 : AB_PRINT(("retrieving name and guid again"));
319 6 : queryString = "SELECT name, guid FROM #pet";
320 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
321 42 : while (CHKFetch("SNo") == SQL_SUCCESS) {
322 30 : count++;
323 30 : CHKGetData(1, SQL_CHAR, name, 20, 0, "S");
324 30 : CHKGetData(2, SQL_GUID, &sqlguid, 16, 0, "S");
325 :
326 30 : AB_PRINT(("%-10s %08X-%04X-%04X-%02X%02X-%02X%02X%02X%02X%02X%02X",
327 : name,
328 : (int) (sqlguid.Data1), sqlguid.Data2,
329 : sqlguid.Data3, sqlguid.Data4[0], sqlguid.Data4[1],
330 : sqlguid.Data4[2], sqlguid.Data4[3], sqlguid.Data4[4],
331 : sqlguid.Data4[5], sqlguid.Data4[6], sqlguid.Data4[7]));
332 : }
333 :
334 : /*
335 : * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
336 : * state if we try SELECT again).
337 : */
338 6 : odbc_reset_statement();
339 :
340 : /*
341 : * Now retrieve rows via stored procedure passing GUID as param.
342 : */
343 6 : AB_PRINT(("retrieving name and guid"));
344 :
345 6 : queryString = "{call GetGUIDRows(?)}";
346 6 : lenOrInd = SQL_NTS;
347 6 : strcpy(guid, "87654321-4321-4321-4321-123456789abc");
348 :
349 6 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0, 0, guid, 0, &lenOrInd, "S");
350 6 : CHKExecDirect(T(queryString), SQL_NTS, "S");
351 18 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
352 6 : count++;
353 6 : CHKGetData(1, SQL_CHAR, name, 20, 0, "S");
354 6 : CHKGetData(2, SQL_CHAR, guid, 37, 0, "S");
355 :
356 6 : AB_PRINT(("%-10s %s", name, guid));
357 : }
358 :
359 : /*
360 : * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
361 : * state after a previous SELECT has occurred).
362 : */
363 6 : odbc_reset_statement();
364 :
365 : /* cleanup */
366 6 : odbc_command_with_result(odbc_stmt, "DROP PROC GetGUIDRows");
367 :
368 : /* CLOSEDOWN */
369 :
370 6 : odbc_disconnect();
371 :
372 6 : AB_FUNCT(("TestRawODBCGuid (out): ok"));
373 6 : return TRUE;
374 : }
375 :
376 : /**
377 : * Array of tests.
378 : */
379 : static DbTestEntry _dbTests[] = {
380 : /* 1 */ {TestRawODBCDirectQuery, "Raw ODBC direct query"},
381 : /* 2 */ {TestRawODBCPreparedQuery, "Raw ODBC prepared query"},
382 : /* 3 */ {TestRawODBCGuid, "Raw ODBC GUID"},
383 : /* end */ {0, 0}
384 : };
385 :
386 : static DbTestEntry *tests = _dbTests;
387 :
388 : /**
389 : * Code to iterate through all tests to run.
390 : *
391 : * \return
392 : * TRUE if all tests pass, FALSE if any tests fail.
393 : */
394 : static int
395 8 : RunTests(void)
396 : {
397 : unsigned int i;
398 8 : unsigned int passes = 0;
399 8 : unsigned int fails = 0;
400 :
401 8 : i = 0;
402 40 : while (tests[i].testFn) {
403 24 : printf("Running test %2d: %s... ", i + 1, tests[i].description);
404 24 : fflush(stdout);
405 24 : if (tests[i].testFn()) {
406 24 : printf("pass\n");
407 24 : passes++;
408 : } else {
409 0 : printf("fail\n");
410 0 : fails++;
411 : }
412 24 : i++;
413 24 : ODBC_FREE();
414 : }
415 :
416 8 : if (fails == 0) {
417 8 : printf("\nAll %d tests passed.\n\n", passes);
418 : } else {
419 0 : printf("\nTest passes: %d, test fails: %d\n\n", passes, fails);
420 : }
421 :
422 : /* Return TRUE if there are no failures */
423 8 : return (!fails);
424 : }
425 :
426 : int
427 8 : main(int argc, char *argv[])
428 : {
429 8 : odbc_use_version3 = 1;
430 :
431 8 : if (RunTests())
432 : return 0; /* Success */
433 0 : return 1; /* Error code */
434 : }
|
