NCBI C++ ToolKit
testodbc.c
Go to the documentation of this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

Go to the SVN repository for this file.

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