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