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 10 : TestRawODBCPreparedQuery(void)
32 : {
33 : SQLTCHAR *queryString;
34 10 : SQLLEN lenOrInd = 0;
35 10 : SQLSMALLINT supplierId = 4;
36 : int count;
37 :
38 10 : AB_FUNCT(("TestRawODBCPreparedQuery (in)"));
39 :
40 : /* INIT */
41 :
42 10 : odbc_connect();
43 :
44 : /* MAKE QUERY */
45 :
46 10 : 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 60 : while (SQLMoreResults(odbc_stmt) == SQL_SUCCESS)
64 40 : continue;
65 :
66 10 : queryString = T("SELECT * FROM #Products WHERE SupplierID = ?");
67 :
68 10 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &supplierId, 0, &lenOrInd, "S");
69 :
70 10 : CHKPrepare(queryString, SQL_NTS, "S");
71 :
72 10 : CHKExecute("S");
73 :
74 10 : count = 0;
75 :
76 50 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
77 30 : count++;
78 : }
79 10 : AB_PRINT(("Got %d rows", count));
80 :
81 10 : 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 10 : odbc_disconnect();
94 :
95 10 : AB_FUNCT(("TestRawODBCPreparedQuery (out): ok"));
96 10 : return true;
97 : }
98 :
99 : /*
100 : * Test that makes a parameterized ODBC query using SQLExecDirect.
101 : */
102 : static bool
103 10 : TestRawODBCDirectQuery(void)
104 : {
105 10 : SQLLEN lenOrInd = 0;
106 10 : SQLSMALLINT supplierId = 1;
107 : int count;
108 :
109 10 : AB_FUNCT(("TestRawODBCDirectQuery (in)"));
110 :
111 : /* INIT */
112 :
113 10 : odbc_connect();
114 :
115 : /* MAKE QUERY */
116 :
117 10 : 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 60 : while (SQLMoreResults(odbc_stmt) == SQL_SUCCESS)
135 40 : continue;
136 :
137 10 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &supplierId, 0, &lenOrInd, "S");
138 :
139 10 : CHKExecDirect(T("SELECT * FROM #Products WHERE SupplierID = ?"), SQL_NTS, "S");
140 :
141 10 : count = 0;
142 :
143 50 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
144 30 : count++;
145 : }
146 10 : AB_PRINT(("Got %d rows", count));
147 :
148 10 : 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 10 : odbc_disconnect();
161 :
162 10 : AB_FUNCT(("TestRawODBCDirectQuery (out): ok"));
163 10 : 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 10 : 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 :
183 10 : AB_FUNCT(("TestRawODBCGuid (in)"));
184 :
185 10 : odbc_connect();
186 :
187 10 : if (!odbc_db_is_microsoft()) {
188 2 : odbc_disconnect();
189 2 : return true;
190 : }
191 :
192 8 : AB_PRINT(("Creating #pet table"));
193 :
194 8 : queryString = "CREATE TABLE #pet (name VARCHAR(20), owner VARCHAR(20), "
195 : "species VARCHAR(20), sex CHAR(1), age INTEGER, " "guid UNIQUEIDENTIFIER DEFAULT NEWID() ); ";
196 8 : CHKExecDirect(T(queryString), SQL_NTS, "SNo");
197 :
198 8 : odbc_command_with_result(odbc_stmt, "DROP PROC GetGUIDRows");
199 :
200 8 : AB_PRINT(("Creating stored proc GetGUIDRows"));
201 :
202 8 : queryString = "CREATE PROCEDURE GetGUIDRows (@guidpar uniqueidentifier) AS \
203 : SELECT name, guid FROM #pet WHERE guid = @guidpar";
204 8 : CHKExecDirect(T(queryString), SQL_NTS, "SNo");
205 :
206 8 : AB_PRINT(("Insert row 1"));
207 :
208 8 : queryString = "INSERT INTO #pet( name, owner, species, sex, age ) \
209 : VALUES ( 'Fang', 'Mike', 'dog', 'm', 12 );";
210 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
211 :
212 8 : AB_PRINT(("Insert row 2"));
213 :
214 : /*
215 : * Ok - new row with explicit GUID, but parameterised age.
216 : */
217 8 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
218 : VALUES ( 'Splash', 'Dan', 'fish', 'm', ?, \
219 : '12345678-1234-1234-1234-123456789012' );";
220 :
221 8 : lenOrInd = 0;
222 8 : age = 3;
223 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_SSHORT, SQL_INTEGER, 0, 0, &age, 0, &lenOrInd, "S");
224 :
225 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
226 8 : CHKFreeStmt(SQL_CLOSE, "S");
227 :
228 8 : AB_PRINT(("Insert row 3"));
229 : /*
230 : * Ok - new row with parameterised GUID.
231 : */
232 8 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
233 : VALUES ( 'Woof', 'Tom', 'cat', 'f', 2, ? );";
234 :
235 8 : lenOrInd = SQL_NTS;
236 8 : strcpy(guid, "87654321-4321-4321-4321-123456789abc");
237 :
238 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0, 0, guid, 0, &lenOrInd, "S");
239 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
240 :
241 8 : AB_PRINT(("Insert row 4"));
242 : /*
243 : * Ok - new row with parameterised GUID.
244 : */
245 8 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
246 : VALUES ( 'Spike', 'Diane', 'pig', 'f', 4, ? );";
247 :
248 8 : lenOrInd = SQL_NTS;
249 8 : strcpy(guid, "1234abcd-abcd-abcd-abcd-123456789abc");
250 :
251 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 36, 0, guid, 0, &lenOrInd, "S");
252 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
253 :
254 8 : AB_PRINT(("Insert row 5"));
255 : /*
256 : * Ok - new row with parameterised GUID.
257 : */
258 8 : queryString = "INSERT INTO #pet( name, owner, species, sex, age, guid ) \
259 : VALUES ( 'Fluffy', 'Sam', 'dragon', 'm', 16, ? );";
260 :
261 8 : sqlguid.Data1 = 0xaabbccdd;
262 8 : sqlguid.Data2 = 0xeeff;
263 8 : sqlguid.Data3 = 0x1122;
264 8 : sqlguid.Data4[0] = 0x11;
265 8 : sqlguid.Data4[1] = 0x22;
266 8 : sqlguid.Data4[2] = 0x33;
267 8 : sqlguid.Data4[3] = 0x44;
268 8 : sqlguid.Data4[4] = 0x55;
269 8 : sqlguid.Data4[5] = 0x66;
270 8 : sqlguid.Data4[6] = 0x77;
271 8 : sqlguid.Data4[7] = 0x88;
272 :
273 8 : lenOrInd = 16;
274 8 : strcpy(guid, "1234abcd-abcd-abcd-abcd-123456789abc");
275 :
276 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_GUID, SQL_GUID, 16, 0, &sqlguid, 16, &lenOrInd, "S");
277 8 : status = SQLExecDirect(odbc_stmt, T(queryString), SQL_NTS);
278 8 : if (status != SQL_SUCCESS) {
279 0 : AB_ERROR(("Insert row 5 failed"));
280 0 : AB_ERROR(("Sadly this was expected in *nix ODBC. Carry on."));
281 : }
282 :
283 : /*
284 : * Now retrieve rows - especially GUID column values.
285 : */
286 8 : AB_PRINT(("retrieving name and guid"));
287 8 : queryString = "SELECT name, guid FROM #pet";
288 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
289 56 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
290 40 : CHKGetData(1, SQL_CHAR, name, 20, 0, "S");
291 40 : CHKGetData(2, SQL_CHAR, guid, 37, 0, "S");
292 :
293 40 : AB_PRINT(("name: %-10s guid: %s", name, guid));
294 : }
295 :
296 : /*
297 : * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
298 : * state if we try SELECT again).
299 : */
300 8 : odbc_reset_statement();
301 :
302 :
303 : /*
304 : * Now retrieve rows - especially GUID column values.
305 : */
306 :
307 8 : AB_PRINT(("retrieving name and guid again"));
308 8 : queryString = "SELECT name, guid FROM #pet";
309 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
310 56 : while (CHKFetch("SNo") == SQL_SUCCESS) {
311 40 : CHKGetData(1, SQL_CHAR, name, 20, 0, "S");
312 40 : CHKGetData(2, SQL_GUID, &sqlguid, 16, 0, "S");
313 :
314 40 : AB_PRINT(("%-10s %08X-%04X-%04X-%02X%02X-%02X%02X%02X%02X%02X%02X",
315 : name,
316 : (int) (sqlguid.Data1), sqlguid.Data2,
317 : sqlguid.Data3, sqlguid.Data4[0], sqlguid.Data4[1],
318 : sqlguid.Data4[2], sqlguid.Data4[3], sqlguid.Data4[4],
319 : sqlguid.Data4[5], sqlguid.Data4[6], sqlguid.Data4[7]));
320 : }
321 :
322 : /*
323 : * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
324 : * state if we try SELECT again).
325 : */
326 8 : odbc_reset_statement();
327 :
328 : /*
329 : * Now retrieve rows via stored procedure passing GUID as param.
330 : */
331 8 : AB_PRINT(("retrieving name and guid"));
332 :
333 8 : queryString = "{call GetGUIDRows(?)}";
334 8 : lenOrInd = SQL_NTS;
335 8 : strcpy(guid, "87654321-4321-4321-4321-123456789abc");
336 :
337 8 : CHKBindParameter(1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_GUID, 0, 0, guid, 0, &lenOrInd, "S");
338 8 : CHKExecDirect(T(queryString), SQL_NTS, "S");
339 24 : while (SQLFetch(odbc_stmt) == SQL_SUCCESS) {
340 8 : CHKGetData(1, SQL_CHAR, name, 20, 0, "S");
341 8 : CHKGetData(2, SQL_CHAR, guid, 37, 0, "S");
342 :
343 8 : AB_PRINT(("%-10s %s", name, guid));
344 : }
345 :
346 : /*
347 : * Realloc cursor handle - (Windows ODBC considers it an invalid cursor
348 : * state after a previous SELECT has occurred).
349 : */
350 8 : odbc_reset_statement();
351 :
352 : /* cleanup */
353 8 : odbc_command_with_result(odbc_stmt, "DROP PROC GetGUIDRows");
354 :
355 : /* CLOSEDOWN */
356 :
357 8 : odbc_disconnect();
358 :
359 8 : AB_FUNCT(("TestRawODBCGuid (out): ok"));
360 8 : return true;
361 : }
362 :
363 : /**
364 : * Array of tests.
365 : */
366 : static const DbTestEntry tests[] = {
367 : /* 1 */ {TestRawODBCDirectQuery, "Raw ODBC direct query"},
368 : /* 2 */ {TestRawODBCPreparedQuery, "Raw ODBC prepared query"},
369 : /* 3 */ {TestRawODBCGuid, "Raw ODBC GUID"},
370 : /* end */ {NULL, NULL}
371 : };
372 :
373 : /**
374 : * Code to iterate through all tests to run.
375 : *
376 : * \return
377 : * true if all tests pass, false if any tests fail.
378 : */
379 : static bool
380 10 : RunTests(void)
381 : {
382 : unsigned int i;
383 10 : unsigned int passes = 0;
384 10 : unsigned int fails = 0;
385 :
386 10 : i = 0;
387 50 : while (tests[i].testFn) {
388 30 : printf("Running test %2d: %s... ", i + 1, tests[i].description);
389 30 : fflush(stdout);
390 30 : if (tests[i].testFn()) {
391 30 : printf("pass\n");
392 30 : passes++;
393 : } else {
394 0 : printf("fail\n");
395 0 : fails++;
396 : }
397 30 : i++;
398 30 : ODBC_FREE();
399 : }
400 :
401 10 : if (fails == 0) {
402 10 : printf("\nAll %d tests passed.\n\n", passes);
403 : } else {
404 0 : printf("\nTest passes: %d, test fails: %d\n\n", passes, fails);
405 : }
406 :
407 : /* Return true if there are no failures */
408 10 : return (fails == 0);
409 : }
410 :
411 : int
412 10 : main(void)
413 : {
414 10 : odbc_use_version3 = true;
415 :
416 10 : if (RunTests())
417 : return 0; /* Success */
418 0 : return 1; /* Error code */
419 : }
|
