projects / wxWidgets.git / blame_incremental
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (non-incremental) | history | HEAD
removed incorrect param from docs
[wxWidgets.git] / src / common / db.cpp
0 / 3368 (  0%)
CommitLineData
1///////////////////////////////////////////////////////////////////////////////
2// Name: db.cpp
3// Purpose: Implementation of the wxDb class. The wxDb class represents a connection
4// to an ODBC data source. The wxDb class allows operations on the data
5// source such as opening and closing the data source.
6// Author: Doug Card
7// Modified by: George Tasker
8// Bart Jourquin
9// Mark Johnson, wxWindows@mj10777.de
10// Mods: Dec, 1998:
11// -Added support for SQL statement logging and database cataloging
12// Mods: April, 1999
13// -Added QUERY_ONLY mode support to reduce default number of cursors
14// -Added additional SQL logging code
15// -Added DEBUG-ONLY tracking of wxTable objects to detect orphaned DB connections
16// -Set ODBC option to only read committed writes to the DB so all
17// databases operate the same in that respect
18// Created: 9.96
19// RCS-ID: $Id$
20// Copyright: (c) 1996 Remstar International, Inc.
21// Licence: wxWindows licence, plus:
22// Notice: This class library and its intellectual design are free of charge for use,
23// modification, enhancement, debugging under the following conditions:
24// 1) These classes may only be used as part of the implementation of a
25// wxWindows-based application
26// 2) All enhancements and bug fixes are to be submitted back to the wxWindows
27// user groups free of all charges for use with the wxWindows library.
28// 3) These classes may not be distributed as part of any other class library,
29// DLL, text (written or electronic), other than a complete distribution of
30// the wxWindows GUI development toolkit.
31///////////////////////////////////////////////////////////////////////////////
32
33/*
34// SYNOPSIS START
35// SYNOPSIS STOP
36*/
37
38#include "wx/wxprec.h"
39
40
41// Use this line for wxWindows v1.x
42//#include "wx_ver.h"
43// Use this line for wxWindows v2.x
44#include "wx/version.h"
45
46#if wxMAJOR_VERSION == 2
47 #ifdef __GNUG__
48 #pragma implementation "db.h"
49 #endif
50#endif
51
52#ifdef DBDEBUG_CONSOLE
53 #include "wx/ioswrap.h"
54#endif
55
56#ifdef __BORLANDC__
57 #pragma hdrstop
58#endif //__BORLANDC__
59
60#if wxMAJOR_VERSION == 2
61 #ifndef WX_PRECOMP
62 #include "wx/string.h"
63 #include "wx/object.h"
64 #include "wx/list.h"
65 #include "wx/utils.h"
66 #include "wx/msgdlg.h"
67 #include "wx/log.h"
68 #endif
69 #include "wx/filefn.h"
70 #include "wx/wxchar.h"
71#endif
72
73
74#if wxMAJOR_VERSION == 1
75# if defined(wx_msw) || defined(wx_x)
76# ifdef WX_PRECOMP
77# include "wx_prec.h"
78# else
79# include "wx.h"
80# endif
81# endif
82# define wxUSE_ODBC 1
83#endif
84
85#if wxUSE_ODBC
86
87#include <stdio.h>
88#include <string.h>
89#include <assert.h>
90#include <stdlib.h>
91#include <ctype.h>
92
93#if wxMAJOR_VERSION == 1
94 #include "db.h"
95#elif wxMAJOR_VERSION == 2
96 #include "wx/db.h"
97#endif
98
99WXDLLEXPORT_DATA(wxDbList*) PtrBegDbList = 0;
100
101
102char const *SQL_LOG_FILENAME = "sqllog.txt";
103char const *SQL_CATALOG_FILENAME = "catalog.txt";
104
105#ifdef __WXDEBUG__
106 extern wxList TablesInUse;
107#endif
108
109// SQL Log defaults to be used by GetDbConnection
110wxDbSqlLogState SQLLOGstate = sqlLogOFF;
111
112//char SQLLOGfn[wxDB_PATH_MAX+1] = SQL_LOG_FILENAME;
113//wxChar *SQLLOGfn = (wxChar*) SQL_LOG_FILENAME;
114static wxString SQLLOGfn = SQL_LOG_FILENAME;
115
116// The wxDb::errorList is copied to this variable when the wxDb object
117// is closed. This way, the error list is still available after the
118// database object is closed. This is necessary if the database
119// connection fails so the calling application can show the operator
120// why the connection failed. Note: as each wxDb object is closed, it
121// will overwrite the errors of the previously destroyed wxDb object in
122// this variable. NOTE: This occurs during a CLOSE, not a FREEing of the
123// connection
124char DBerrorList[DB_MAX_ERROR_HISTORY][DB_MAX_ERROR_MSG_LEN];
125
126#if EXPERIMENTAL_WXDB_FUNCTIONS // will be added in 2.4
127// This type defines the return row-struct form
128// SQLTablePrivileges, and is used by wxDB::TablePrivileges.
129typedef struct
130{
131 char tableQual[129];
132 char tableOwner[129];
133 char tableName[129];
134 char grantor[129];
135 char grantee[129];
136 char privilege[129];
137 char grantable[4];
138} wxDbTablePrivilegeInfo;
139#endif
140
141
142/********** wxDbColFor Constructor **********/
143wxDbColFor::wxDbColFor()
144{
145 s_Field = "";
146 int i;
147 for (i=0;i<7;i++)
148 {
149 s_Format[i] = "";
150 s_Amount[i] = "";
151 i_Amount[i] = 0;
152 }
153 i_Nation = 0; // 0=EU, 1=UK, 2=International, 3=US
154 i_dbDataType = 0;
155 i_sqlDataType = 0;
156 Format(1,DB_DATA_TYPE_VARCHAR,0,0,0); // the Function that does the work
157} // wxDbColFor::wxDbColFor()
158
159
160wxDbColFor::~wxDbColFor()
161{
162} // wxDbColFor::~wxDbColFor()
163
164
165/********** wxDbColInf Con / Destructor **********/
166wxDbColInf::wxDbColInf()
167{
168 catalog[0] = 0;
169 schema[0] = 0;
170 tableName[0] = 0;
171 colName[0] = 0;
172 sqlDataType = 0;
173 typeName[0] = 0;
174 columnSize = 0;
175 bufferLength = 0;
176 decimalDigits = 0;
177 numPrecRadix = 0;
178 nullable = 0;
179 remarks[0] = 0;
180 dbDataType = 0;
181 PkCol = 0;
182 PkTableName[0] = 0;
183 FkCol = 0;
184 FkTableName[0] = 0;
185 pColFor = NULL;
186} // wxDbColInf::wxDbColFor()
187
188
189wxDbColInf::~wxDbColInf()
190{
191 if (pColFor)
192 delete pColFor;
193 pColFor = NULL;
194} // wxDbColInf::~wxDbColInf()
195
196
197/********** wxDbTableInf Constructor ********/
198wxDbTableInf::wxDbTableInf()
199{
200 tableName[0] = 0;
201 tableType[0] = 0;
202 tableRemarks[0] = 0;
203 numCols = 0;
204 pColInf = NULL;
205} // wxDbTableInf::wxDbTableFor()
206
207
208/********** wxDbTableInf Constructor ********/
209wxDbTableInf::~wxDbTableInf()
210{
211 if (pColInf)
212 delete [] pColInf;
213 pColInf = NULL;
214} // wxDbTableInf::~wxDbTableInf()
215
216
217/********** wxDbInf Constructor *************/
218wxDbInf::wxDbInf()
219{
220 catalog[0] = 0;
221 schema[0] = 0;
222 numTables = 0;
223 pTableInf = NULL;
224} // wxDbInf::wxDbFor()
225
226
227/********** wxDbInf Destructor *************/
228wxDbInf::~wxDbInf()
229{
230 if (pTableInf)
231 delete [] pTableInf;
232 pTableInf = NULL;
233} // wxDbInf::~wxDbInf()
234
235
236/*************************************************/
237
238
239int wxDbColFor::Format(int Nation,int dbDataType,SWORD sqlDataType,short columnSize,short decimalDigits)
240{
241 // ----------------------------------------------------------------------------------------
242 // -- 19991224 : mj10777 : Create
243 // There is still a lot of work to do here, but it is a start
244 // It handles all the basic data-types that I have run into up to now
245 // The main work will have be with Dates and float Formatting
246 // (US 1,000.00 ; EU 1.000,00)
247 // There are wxWindow plans for locale support and the new wxDateTime. If
248 // they define some constants (wxEUROPEAN) that can be gloably used,
249 // they should be used here.
250 // ----------------------------------------------------------------------------------------
251 // There should also be a function to scan in a string to fill the variable
252 // ----------------------------------------------------------------------------------------
253 wxString Temp0;
254 i_Nation = Nation; // 0 = timestamp , 1=EU, 2=UK, 3=International, 4=US
255 i_dbDataType = dbDataType;
256 i_sqlDataType = sqlDataType;
257 s_Field.Printf(wxT("%s%d"),s_Amount[1].c_str(),i_Amount[1]); // OK for VARCHAR, INTEGER and FLOAT
258 if (i_dbDataType == 0) // Filter unsupported dbDataTypes
259 {
260 if ((i_sqlDataType == SQL_VARCHAR) || (i_sqlDataType == SQL_LONGVARCHAR))
261 i_dbDataType = DB_DATA_TYPE_VARCHAR;
262 if (i_sqlDataType == SQL_C_DATE)
263 i_dbDataType = DB_DATA_TYPE_DATE;
264 if (i_sqlDataType == SQL_C_BIT)
265 i_dbDataType = DB_DATA_TYPE_INTEGER;
266 if (i_sqlDataType == SQL_NUMERIC)
267 i_dbDataType = DB_DATA_TYPE_VARCHAR;
268 if (i_sqlDataType == SQL_REAL)
269 i_dbDataType = DB_DATA_TYPE_FLOAT;
270 }
271 if ((i_dbDataType == DB_DATA_TYPE_INTEGER) && (i_sqlDataType == SQL_C_DOUBLE))
272 { // DBASE Numeric
273 i_dbDataType = DB_DATA_TYPE_FLOAT;
274 }
275 switch(i_dbDataType) // -A-> Still a lot of proper formatting to do
276 {
277 case DB_DATA_TYPE_VARCHAR:
278 s_Field = "%s";
279 break;
280 case DB_DATA_TYPE_INTEGER:
281 s_Field = "%d";
282 break;
283 case DB_DATA_TYPE_FLOAT:
284 if (decimalDigits == 0)
285 decimalDigits = 2;
286 Temp0 = "%";
287 Temp0.Printf(wxT("%s%d.%d"),Temp0.c_str(),columnSize,decimalDigits);
288 s_Field.Printf(wxT("%sf"),Temp0.c_str());
289 break;
290 case DB_DATA_TYPE_DATE:
291 if (i_Nation == 0) // timestamp YYYY-MM-DD HH:MM:SS.SSS (tested for SYBASE)
292 {
293 s_Field = "%04d-%02d-%02d %02d:%02d:%02d.%03d";
294 }
295 if (i_Nation == 1) // European DD.MM.YYYY HH:MM:SS.SSS
296 {
297 s_Field = "%02d.%02d.%04d %02d:%02d:%02d.%03d";
298 }
299 if (i_Nation == 2) // UK DD/MM/YYYY HH:MM:SS.SSS
300 {
301 s_Field = "%02d/%02d/%04d %02d:%02d:%02d.%03d";
302 }
303 if (i_Nation == 3) // International YYYY-MM-DD HH:MM:SS.SSS
304 {
305 s_Field = "%04d-%02d-%02d %02d:%02d:%02d.%03d";
306 }
307 if (i_Nation == 4) // US MM/DD/YYYY HH:MM:SS.SSS
308 {
309 s_Field = "%02d/%02d/%04d %02d:%02d:%02d.%03d";
310 }
311 break;
312 default:
313 s_Field.Printf(wxT("Unknown Format(%d)-SQL(%d)"),dbDataType,sqlDataType); //
314 break;
315 };
316 return TRUE;
317} // wxDbColFor::Format()
318
319
320/********** wxDb Constructor **********/
321wxDb::wxDb(HENV &aHenv, bool FwdOnlyCursors)
322{
323 int i;
324
325 fpSqlLog = 0; // Sql Log file pointer
326 sqlLogState = sqlLogOFF; // By default, logging is turned off
327 nTables = 0;
328
329 wxStrcpy(sqlState,wxT(""));
330 wxStrcpy(errorMsg,wxT(""));
331 nativeError = cbErrorMsg = 0;
332 for (i = 0; i < DB_MAX_ERROR_HISTORY; i++)
333 wxStrcpy(errorList[i], wxT(""));
334
335 // Init typeInf structures
336 wxStrcpy(typeInfVarchar.TypeName,wxT(""));
337 typeInfVarchar.FsqlType = 0;
338 typeInfVarchar.Precision = 0;
339 typeInfVarchar.CaseSensitive = 0;
340 typeInfVarchar.MaximumScale = 0;
341
342 wxStrcpy(typeInfInteger.TypeName,wxT(""));
343 typeInfInteger.FsqlType = 0;
344 typeInfInteger.Precision = 0;
345 typeInfInteger.CaseSensitive = 0;
346 typeInfInteger.MaximumScale = 0;
347
348 wxStrcpy(typeInfFloat.TypeName,wxT(""));
349 typeInfFloat.FsqlType = 0;
350 typeInfFloat.Precision = 0;
351 typeInfFloat.CaseSensitive = 0;
352 typeInfFloat.MaximumScale = 0;
353
354 wxStrcpy(typeInfDate.TypeName,wxT(""));
355 typeInfDate.FsqlType = 0;
356 typeInfDate.Precision = 0;
357 typeInfDate.CaseSensitive = 0;
358 typeInfDate.MaximumScale = 0;
359
360 // Error reporting is turned OFF by default
361 silent = TRUE;
362
363 // Copy the HENV into the db class
364 henv = aHenv;
365 fwdOnlyCursors = FwdOnlyCursors;
366
367 // Allocate a data source connection handle
368 if (SQLAllocConnect(henv, &hdbc) != SQL_SUCCESS)
369 DispAllErrors(henv);
370
371 // Initialize the db status flag
372 DB_STATUS = 0;
373
374 // Mark database as not open as of yet
375 dbIsOpen = FALSE;
376
377} // wxDb::wxDb()
378
379
380/********** wxDb::Open() **********/
381bool wxDb::Open(char *Dsn, char *Uid, char *AuthStr)
382{
383 assert(Dsn && wxStrlen(Dsn));
384 dsn = Dsn;
385 uid = Uid;
386 authStr = AuthStr;
387
388 RETCODE retcode;
389
390 if (!FwdOnlyCursors())
391 {
392 // Specify that the ODBC cursor library be used, if needed. This must be
393 // specified before the connection is made.
394 retcode = SQLSetConnectOption(hdbc, SQL_ODBC_CURSORS, SQL_CUR_USE_IF_NEEDED);
395
396#ifdef DBDEBUG_CONSOLE
397 if (retcode == SQL_SUCCESS)
398 cout << "SQLSetConnectOption(CURSOR_LIB) successful" << endl;
399 else
400 cout << "SQLSetConnectOption(CURSOR_LIB) failed" << endl;
401#endif
402 }
403
404 // Connect to the data source
405 retcode = SQLConnect(hdbc, (UCHAR FAR *) Dsn, SQL_NTS,
406 (UCHAR FAR *) Uid, SQL_NTS,
407 (UCHAR FAR *) AuthStr,SQL_NTS);
408
409 if (retcode == SQL_SUCCESS_WITH_INFO)
410 DispAllErrors(henv, hdbc);
411 else if (retcode != SQL_SUCCESS)
412 return(DispAllErrors(henv, hdbc));
413
414/*
415 If using Intersolv branded ODBC drivers, this is the place where you would substitute
416 your branded driver license information
417
418 SQLSetConnectOption(hdbc, 1041, (UDWORD) "");
419 SQLSetConnectOption(hdbc, 1042, (UDWORD) "");
420*/
421
422 // Mark database as open
423 dbIsOpen = TRUE;
424
425 // Allocate a statement handle for the database connection
426 if (SQLAllocStmt(hdbc, &hstmt) != SQL_SUCCESS)
427 return(DispAllErrors(henv, hdbc));
428
429 // Set Connection Options
430 if (! setConnectionOptions())
431 return(FALSE);
432
433 // Query the data source for inf. about itself
434 if (! getDbInfo())
435 return(FALSE);
436
437 // Query the data source regarding data type information
438
439 //
440 // The way I determined which SQL data types to use was by calling SQLGetInfo
441 // for all of the possible SQL data types to see which ones were supported. If
442 // a type is not supported, the SQLFetch() that's called from getDataTypeInfo()
443 // fails with SQL_NO_DATA_FOUND. This is ugly because I'm sure the three SQL data
444 // types I've selected below will not alway's be what we want. These are just
445 // what happened to work against an Oracle 7/Intersolv combination. The following is
446 // a complete list of the results I got back against the Oracle 7 database:
447 //
448 // SQL_BIGINT SQL_NO_DATA_FOUND
449 // SQL_BINARY SQL_NO_DATA_FOUND
450 // SQL_BIT SQL_NO_DATA_FOUND
451 // SQL_CHAR type name = 'CHAR', Precision = 255
452 // SQL_DATE SQL_NO_DATA_FOUND
453 // SQL_DECIMAL type name = 'NUMBER', Precision = 38
454 // SQL_DOUBLE type name = 'NUMBER', Precision = 15
455 // SQL_FLOAT SQL_NO_DATA_FOUND
456 // SQL_INTEGER SQL_NO_DATA_FOUND
457 // SQL_LONGVARBINARY type name = 'LONG RAW', Precision = 2 billion
458 // SQL_LONGVARCHAR type name = 'LONG', Precision = 2 billion
459 // SQL_NUMERIC SQL_NO_DATA_FOUND
460 // SQL_REAL SQL_NO_DATA_FOUND
461 // SQL_SMALLINT SQL_NO_DATA_FOUND
462 // SQL_TIME SQL_NO_DATA_FOUND
463 // SQL_TIMESTAMP type name = 'DATE', Precision = 19
464 // SQL_VARBINARY type name = 'RAW', Precision = 255
465 // SQL_VARCHAR type name = 'VARCHAR2', Precision = 2000
466 // =====================================================================
467 // Results from a Microsoft Access 7.0 db, using a driver from Microsoft
468 //
469 // SQL_VARCHAR type name = 'TEXT', Precision = 255
470 // SQL_TIMESTAMP type name = 'DATETIME'
471 // SQL_DECIMAL SQL_NO_DATA_FOUND
472 // SQL_NUMERIC type name = 'CURRENCY', Precision = 19
473 // SQL_FLOAT SQL_NO_DATA_FOUND
474 // SQL_REAL type name = 'SINGLE', Precision = 7
475 // SQL_DOUBLE type name = 'DOUBLE', Precision = 15
476 // SQL_INTEGER type name = 'LONG', Precision = 10
477
478 // VARCHAR = Variable length character string
479 if (!getDataTypeInfo(SQL_VARCHAR, typeInfVarchar))
480 if (!getDataTypeInfo(SQL_CHAR, typeInfVarchar))
481 return(FALSE);
482 else
483 typeInfVarchar.FsqlType = SQL_CHAR;
484 else
485 typeInfVarchar.FsqlType = SQL_VARCHAR;
486
487 // Float
488 if (!getDataTypeInfo(SQL_DOUBLE,typeInfFloat))
489
490 if (!getDataTypeInfo(SQL_REAL,typeInfFloat))
491 if (!getDataTypeInfo(SQL_FLOAT,typeInfFloat))
492 if (!getDataTypeInfo(SQL_DECIMAL,typeInfFloat))
493 if (!getDataTypeInfo(SQL_NUMERIC,typeInfFloat))
494 return(FALSE);
495 else
496 typeInfFloat.FsqlType = SQL_NUMERIC;
497 else
498 typeInfFloat.FsqlType = SQL_DECIMAL;
499 else
500 typeInfFloat.FsqlType = SQL_FLOAT;
501 else
502 typeInfFloat.FsqlType = SQL_REAL;
503 else
504 typeInfFloat.FsqlType = SQL_DOUBLE;
505
506
507 // Integer
508 if (!getDataTypeInfo(SQL_INTEGER, typeInfInteger))
509 {
510 // If SQL_INTEGER is not supported, use the floating point
511 // data type to store integers as well as floats
512 if (!getDataTypeInfo(typeInfFloat.FsqlType, typeInfInteger))
513 return(FALSE);
514 else
515 typeInfInteger.FsqlType = typeInfFloat.FsqlType;
516 }
517 else
518 typeInfInteger.FsqlType = SQL_INTEGER;
519
520 // Date/Time
521 if (Dbms() != dbmsDBASE)
522 {
523 if (! getDataTypeInfo(SQL_TIMESTAMP,typeInfDate))
524 return(FALSE);
525 else
526 typeInfDate.FsqlType = SQL_TIMESTAMP;
527 }
528 else
529 {
530 if (!getDataTypeInfo(SQL_DATE,typeInfDate))
531 return(FALSE);
532 else
533 typeInfDate.FsqlType = SQL_DATE;
534 }
535
536#ifdef DBDEBUG_CONSOLE
537 cout << "VARCHAR DATA TYPE: " << typeInfVarchar.TypeName << endl;
538 cout << "INTEGER DATA TYPE: " << typeInfInteger.TypeName << endl;
539 cout << "FLOAT DATA TYPE: " << typeInfFloat.TypeName << endl;
540 cout << "DATE DATA TYPE: " << typeInfDate.TypeName << endl;
541 cout << endl;
542#endif
543
544 // Completed Successfully
545 return(TRUE);
546
547} // wxDb::Open()
548
549
550/********** wxDb::setConnectionOptions() **********/
551bool wxDb::setConnectionOptions(void)
552/*
553 * NOTE: The Intersolv/Oracle 7 driver was "Not Capable" of setting the login timeout.
554 */
555{
556 SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
557 SQLSetConnectOption(hdbc, SQL_OPT_TRACE, SQL_OPT_TRACE_OFF);
558
559 // Display the connection options to verify them
560#ifdef DBDEBUG_CONSOLE
561 long l;
562 cout << "****** CONNECTION OPTIONS ******" << endl;
563
564 if (SQLGetConnectOption(hdbc, SQL_AUTOCOMMIT, &l) != SQL_SUCCESS)
565 return(DispAllErrors(henv, hdbc));
566 cout << "AUTOCOMMIT: " << (l == SQL_AUTOCOMMIT_OFF ? "OFF" : "ON") << endl;
567
568 if (SQLGetConnectOption(hdbc, SQL_ODBC_CURSORS, &l) != SQL_SUCCESS)
569 return(DispAllErrors(henv, hdbc));
570 cout << "ODBC CURSORS: ";
571 switch(l)
572 {
573 case(SQL_CUR_USE_IF_NEEDED):
574 cout << "SQL_CUR_USE_IF_NEEDED";
575 break;
576 case(SQL_CUR_USE_ODBC):
577 cout << "SQL_CUR_USE_ODBC";
578 break;
579 case(SQL_CUR_USE_DRIVER):
580 cout << "SQL_CUR_USE_DRIVER";
581 break;
582 }
583 cout << endl;
584
585 if (SQLGetConnectOption(hdbc, SQL_OPT_TRACE, &l) != SQL_SUCCESS)
586 return(DispAllErrors(henv, hdbc));
587 cout << "TRACING: " << (l == SQL_OPT_TRACE_OFF ? "OFF" : "ON") << endl;
588
589 cout << endl;
590#endif
591
592 // Completed Successfully
593 return(TRUE);
594
595} // wxDb::setConnectionOptions()
596
597
598/********** wxDb::getDbInfo() **********/
599bool wxDb::getDbInfo(void)
600{
601 SWORD cb;
602 RETCODE retcode;
603
604 if (SQLGetInfo(hdbc, SQL_SERVER_NAME, (UCHAR*) dbInf.serverName, 80, &cb) != SQL_SUCCESS)
605 return(DispAllErrors(henv, hdbc));
606
607 if (SQLGetInfo(hdbc, SQL_DATABASE_NAME, (UCHAR*) dbInf.databaseName, 128, &cb) != SQL_SUCCESS)
608 return(DispAllErrors(henv, hdbc));
609
610 if (SQLGetInfo(hdbc, SQL_DBMS_NAME, (UCHAR*) dbInf.dbmsName, 40, &cb) != SQL_SUCCESS)
611 return(DispAllErrors(henv, hdbc));
612
613 // 16-Mar-1999
614 // After upgrading to MSVC6, the original 20 char buffer below was insufficient,
615 // causing database connectivity to fail in some cases.
616 retcode = SQLGetInfo(hdbc, SQL_DBMS_VER, (UCHAR*) dbInf.dbmsVer, 64, &cb);
617
618 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO )
619 return(DispAllErrors(henv, hdbc));
620
621 if (SQLGetInfo(hdbc, SQL_ACTIVE_CONNECTIONS, (UCHAR*) &dbInf.maxConnections, sizeof(dbInf.maxConnections), &cb) != SQL_SUCCESS)
622 return(DispAllErrors(henv, hdbc));
623
624 if (SQLGetInfo(hdbc, SQL_ACTIVE_STATEMENTS, (UCHAR*) &dbInf.maxStmts, sizeof(dbInf.maxStmts), &cb) != SQL_SUCCESS)
625 return(DispAllErrors(henv, hdbc));
626
627 if (SQLGetInfo(hdbc, SQL_DRIVER_NAME, (UCHAR*) dbInf.driverName, 40, &cb) != SQL_SUCCESS)
628 return(DispAllErrors(henv, hdbc));
629
630 if (SQLGetInfo(hdbc, SQL_DRIVER_ODBC_VER, (UCHAR*) dbInf.odbcVer, 60, &cb) == SQL_ERROR)
631 return(DispAllErrors(henv, hdbc));
632
633 retcode = SQLGetInfo(hdbc, SQL_ODBC_VER, (UCHAR*) dbInf.drvMgrOdbcVer, 60, &cb);
634 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
635 return(DispAllErrors(henv, hdbc));
636
637 if (SQLGetInfo(hdbc, SQL_DRIVER_VER, (UCHAR*) dbInf.driverVer, 60, &cb) == SQL_ERROR)
638 return(DispAllErrors(henv, hdbc));
639
640 if (SQLGetInfo(hdbc, SQL_ODBC_API_CONFORMANCE, (UCHAR*) &dbInf.apiConfLvl, sizeof(dbInf.apiConfLvl), &cb) != SQL_SUCCESS)
641 return(DispAllErrors(henv, hdbc));
642
643 if (SQLGetInfo(hdbc, SQL_ODBC_SAG_CLI_CONFORMANCE, (UCHAR*) &dbInf.cliConfLvl, sizeof(dbInf.cliConfLvl), &cb) != SQL_SUCCESS)
644 return(DispAllErrors(henv, hdbc));
645
646 if (SQLGetInfo(hdbc, SQL_ODBC_SQL_CONFORMANCE, (UCHAR*) &dbInf.sqlConfLvl, sizeof(dbInf.sqlConfLvl), &cb) != SQL_SUCCESS)
647 return(DispAllErrors(henv, hdbc));
648
649 if (SQLGetInfo(hdbc, SQL_OUTER_JOINS, (UCHAR*) dbInf.outerJoins, 2, &cb) != SQL_SUCCESS)
650 return(DispAllErrors(henv, hdbc));
651
652 if (SQLGetInfo(hdbc, SQL_PROCEDURES, (UCHAR*) dbInf.procedureSupport, 2, &cb) != SQL_SUCCESS)
653 return(DispAllErrors(henv, hdbc));
654#if EXPERIMENTAL_WXDB_FUNCTIONS // will be added in 2.4
655 if (SQLGetInfo(hdbc, SQL_ACCESSIBLE_TABLES, (UCHAR*) dbInf.accessibleTables, 2, &cb) != SQL_SUCCESS)
656 return(DispAllErrors(henv, hdbc));
657#endif
658 if (SQLGetInfo(hdbc, SQL_CURSOR_COMMIT_BEHAVIOR, (UCHAR*) &dbInf.cursorCommitBehavior, sizeof(dbInf.cursorCommitBehavior), &cb) != SQL_SUCCESS)
659 return(DispAllErrors(henv, hdbc));
660
661 if (SQLGetInfo(hdbc, SQL_CURSOR_ROLLBACK_BEHAVIOR, (UCHAR*) &dbInf.cursorRollbackBehavior, sizeof(dbInf.cursorRollbackBehavior), &cb) != SQL_SUCCESS)
662 return(DispAllErrors(henv, hdbc));
663
664 if (SQLGetInfo(hdbc, SQL_NON_NULLABLE_COLUMNS, (UCHAR*) &dbInf.supportNotNullClause, sizeof(dbInf.supportNotNullClause), &cb) != SQL_SUCCESS)
665 return(DispAllErrors(henv, hdbc));
666
667 if (SQLGetInfo(hdbc, SQL_ODBC_SQL_OPT_IEF, (UCHAR*) dbInf.supportIEF, 2, &cb) != SQL_SUCCESS)
668 return(DispAllErrors(henv, hdbc));
669
670 if (SQLGetInfo(hdbc, SQL_DEFAULT_TXN_ISOLATION, (UCHAR*) &dbInf.txnIsolation, sizeof(dbInf.txnIsolation), &cb) != SQL_SUCCESS)
671 return(DispAllErrors(henv, hdbc));
672
673 if (SQLGetInfo(hdbc, SQL_TXN_ISOLATION_OPTION, (UCHAR*) &dbInf.txnIsolationOptions, sizeof(dbInf.txnIsolationOptions), &cb) != SQL_SUCCESS)
674 return(DispAllErrors(henv, hdbc));
675
676 if (SQLGetInfo(hdbc, SQL_FETCH_DIRECTION, (UCHAR*) &dbInf.fetchDirections, sizeof(dbInf.fetchDirections), &cb) != SQL_SUCCESS)
677 return(DispAllErrors(henv, hdbc));
678
679 if (SQLGetInfo(hdbc, SQL_LOCK_TYPES, (UCHAR*) &dbInf.lockTypes, sizeof(dbInf.lockTypes), &cb) != SQL_SUCCESS)
680 return(DispAllErrors(henv, hdbc));
681
682 if (SQLGetInfo(hdbc, SQL_POS_OPERATIONS, (UCHAR*) &dbInf.posOperations, sizeof(dbInf.posOperations), &cb) != SQL_SUCCESS)
683 return(DispAllErrors(henv, hdbc));
684
685 if (SQLGetInfo(hdbc, SQL_POSITIONED_STATEMENTS, (UCHAR*) &dbInf.posStmts, sizeof(dbInf.posStmts), &cb) != SQL_SUCCESS)
686 return(DispAllErrors(henv, hdbc));
687
688 if (SQLGetInfo(hdbc, SQL_SCROLL_CONCURRENCY, (UCHAR*) &dbInf.scrollConcurrency, sizeof(dbInf.scrollConcurrency), &cb) != SQL_SUCCESS)
689 return(DispAllErrors(henv, hdbc));
690
691 if (SQLGetInfo(hdbc, SQL_SCROLL_OPTIONS, (UCHAR*) &dbInf.scrollOptions, sizeof(dbInf.scrollOptions), &cb) != SQL_SUCCESS)
692 return(DispAllErrors(henv, hdbc));
693
694 if (SQLGetInfo(hdbc, SQL_STATIC_SENSITIVITY, (UCHAR*) &dbInf.staticSensitivity, sizeof(dbInf.staticSensitivity), &cb) != SQL_SUCCESS)
695 return(DispAllErrors(henv, hdbc));
696
697 if (SQLGetInfo(hdbc, SQL_TXN_CAPABLE, (UCHAR*) &dbInf.txnCapable, sizeof(dbInf.txnCapable), &cb) != SQL_SUCCESS)
698 return(DispAllErrors(henv, hdbc));
699
700 if (SQLGetInfo(hdbc, SQL_LOGIN_TIMEOUT, (UCHAR*) &dbInf.loginTimeout, sizeof(dbInf.loginTimeout), &cb) != SQL_SUCCESS)
701 return(DispAllErrors(henv, hdbc));
702
703#ifdef DBDEBUG_CONSOLE
704 cout << "***** DATA SOURCE INFORMATION *****" << endl;
705 cout << "SERVER Name: " << dbInf.serverName << endl;
706 cout << "DBMS Name: " << dbInf.dbmsName << "; DBMS Version: " << dbInf.dbmsVer << endl;
707 cout << "ODBC Version: " << dbInf.odbcVer << "; Driver Version: " << dbInf.driverVer << endl;
708
709 cout << "API Conf. Level: ";
710 switch(dbInf.apiConfLvl)
711 {
712 case SQL_OAC_NONE: cout << "None"; break;
713 case SQL_OAC_LEVEL1: cout << "Level 1"; break;
714 case SQL_OAC_LEVEL2: cout << "Level 2"; break;
715 }
716 cout << endl;
717
718 cout << "SAG CLI Conf. Level: ";
719 switch(dbInf.cliConfLvl)
720 {
721 case SQL_OSCC_NOT_COMPLIANT: cout << "Not Compliant"; break;
722 case SQL_OSCC_COMPLIANT: cout << "Compliant"; break;
723 }
724 cout << endl;
725
726 cout << "SQL Conf. Level: ";
727 switch(dbInf.sqlConfLvl)
728 {
729 case SQL_OSC_MINIMUM: cout << "Minimum Grammar"; break;
730 case SQL_OSC_CORE: cout << "Core Grammar"; break;
731 case SQL_OSC_EXTENDED: cout << "Extended Grammar"; break;
732 }
733 cout << endl;
734
735 cout << "Max. Connections: " << dbInf.maxConnections << endl;
736 cout << "Outer Joins: " << dbInf.outerJoins << endl;
737 cout << "Support for Procedures: " << dbInf.procedureSupport << endl;
738#if EXPERIMENTAL_WXDB_FUNCTIONS // will be added in 2.4
739 cout << "All tables accessible : " << dbInf.accessibleTables << endl;
740#endif
741 cout << "Cursor COMMIT Behavior: ";
742 switch(dbInf.cursorCommitBehavior)
743 {
744 case SQL_CB_DELETE: cout << "Delete cursors"; break;
745 case SQL_CB_CLOSE: cout << "Close cursors"; break;
746 case SQL_CB_PRESERVE: cout << "Preserve cursors"; break;
747 }
748 cout << endl;
749
750 cout << "Cursor ROLLBACK Behavior: ";
751 switch(dbInf.cursorRollbackBehavior)
752 {
753 case SQL_CB_DELETE: cout << "Delete cursors"; break;
754 case SQL_CB_CLOSE: cout << "Close cursors"; break;
755 case SQL_CB_PRESERVE: cout << "Preserve cursors"; break;
756 }
757 cout << endl;
758
759 cout << "Support NOT NULL clause: ";
760 switch(dbInf.supportNotNullClause)
761 {
762 case SQL_NNC_NULL: cout << "No"; break;
763 case SQL_NNC_NON_NULL: cout << "Yes"; break;
764 }
765 cout << endl;
766
767 cout << "Support IEF (Ref. Integrity): " << dbInf.supportIEF << endl;
768 cout << "Login Timeout: " << dbInf.loginTimeout << endl;
769
770 cout << endl << endl << "more ..." << endl;
771 getchar();
772
773 cout << "Default Transaction Isolation: ";
774 switch(dbInf.txnIsolation)
775 {
776 case SQL_TXN_READ_UNCOMMITTED: cout << "Read Uncommitted"; break;
777 case SQL_TXN_READ_COMMITTED: cout << "Read Committed"; break;
778 case SQL_TXN_REPEATABLE_READ: cout << "Repeatable Read"; break;
779 case SQL_TXN_SERIALIZABLE: cout << "Serializable"; break;
780#ifdef ODBC_V20
781 case SQL_TXN_VERSIONING: cout << "Versioning"; break;
782#endif
783 }
784 cout << endl;
785
786 cout << "Transaction Isolation Options: ";
787 if (dbInf.txnIsolationOptions & SQL_TXN_READ_UNCOMMITTED)
788 cout << "Read Uncommitted, ";
789 if (dbInf.txnIsolationOptions & SQL_TXN_READ_COMMITTED)
790 cout << "Read Committed, ";
791 if (dbInf.txnIsolationOptions & SQL_TXN_REPEATABLE_READ)
792 cout << "Repeatable Read, ";
793 if (dbInf.txnIsolationOptions & SQL_TXN_SERIALIZABLE)
794 cout << "Serializable, ";
795#ifdef ODBC_V20
796 if (dbInf.txnIsolationOptions & SQL_TXN_VERSIONING)
797 cout << "Versioning";
798#endif
799 cout << endl;
800
801 cout << "Fetch Directions Supported:" << endl << " ";
802 if (dbInf.fetchDirections & SQL_FD_FETCH_NEXT)
803 cout << "Next, ";
804 if (dbInf.fetchDirections & SQL_FD_FETCH_PRIOR)
805 cout << "Prev, ";
806 if (dbInf.fetchDirections & SQL_FD_FETCH_FIRST)
807 cout << "First, ";
808 if (dbInf.fetchDirections & SQL_FD_FETCH_LAST)
809 cout << "Last, ";
810 if (dbInf.fetchDirections & SQL_FD_FETCH_ABSOLUTE)
811 cout << "Absolute, ";
812 if (dbInf.fetchDirections & SQL_FD_FETCH_RELATIVE)
813 cout << "Relative, ";
814#ifdef ODBC_V20
815 if (dbInf.fetchDirections & SQL_FD_FETCH_RESUME)
816 cout << "Resume, ";
817#endif
818 if (dbInf.fetchDirections & SQL_FD_FETCH_BOOKMARK)
819 cout << "Bookmark";
820 cout << endl;
821
822 cout << "Lock Types Supported (SQLSetPos): ";
823 if (dbInf.lockTypes & SQL_LCK_NO_CHANGE)
824 cout << "No Change, ";
825 if (dbInf.lockTypes & SQL_LCK_EXCLUSIVE)
826 cout << "Exclusive, ";
827 if (dbInf.lockTypes & SQL_LCK_UNLOCK)
828 cout << "UnLock";
829 cout << endl;
830
831 cout << "Position Operations Supported (SQLSetPos): ";
832 if (dbInf.posOperations & SQL_POS_POSITION)
833 cout << "Position, ";
834 if (dbInf.posOperations & SQL_POS_REFRESH)
835 cout << "Refresh, ";
836 if (dbInf.posOperations & SQL_POS_UPDATE)
837 cout << "Upd, ";
838 if (dbInf.posOperations & SQL_POS_DELETE)
839 cout << "Del, ";
840 if (dbInf.posOperations & SQL_POS_ADD)
841 cout << "Add";
842 cout << endl;
843
844 cout << "Positioned Statements Supported: ";
845 if (dbInf.posStmts & SQL_PS_POSITIONED_DELETE)
846 cout << "Pos delete, ";
847 if (dbInf.posStmts & SQL_PS_POSITIONED_UPDATE)
848 cout << "Pos update, ";
849 if (dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)
850 cout << "Select for update";
851 cout << endl;
852
853 cout << "Scroll Concurrency: ";
854 if (dbInf.scrollConcurrency & SQL_SCCO_READ_ONLY)
855 cout << "Read Only, ";
856 if (dbInf.scrollConcurrency & SQL_SCCO_LOCK)
857 cout << "Lock, ";
858 if (dbInf.scrollConcurrency & SQL_SCCO_OPT_ROWVER)
859 cout << "Opt. Rowver, ";
860 if (dbInf.scrollConcurrency & SQL_SCCO_OPT_VALUES)
861 cout << "Opt. Values";
862 cout << endl;
863
864 cout << "Scroll Options: ";
865 if (dbInf.scrollOptions & SQL_SO_FORWARD_ONLY)
866 cout << "Fwd Only, ";
867 if (dbInf.scrollOptions & SQL_SO_STATIC)
868 cout << "Static, ";
869 if (dbInf.scrollOptions & SQL_SO_KEYSET_DRIVEN)
870 cout << "Keyset Driven, ";
871 if (dbInf.scrollOptions & SQL_SO_DYNAMIC)
872 cout << "Dynamic, ";
873 if (dbInf.scrollOptions & SQL_SO_MIXED)
874 cout << "Mixed";
875 cout << endl;
876
877 cout << "Static Sensitivity: ";
878 if (dbInf.staticSensitivity & SQL_SS_ADDITIONS)
879 cout << "Additions, ";
880 if (dbInf.staticSensitivity & SQL_SS_DELETIONS)
881 cout << "Deletions, ";
882 if (dbInf.staticSensitivity & SQL_SS_UPDATES)
883 cout << "Updates";
884 cout << endl;
885
886 cout << "Transaction Capable?: ";
887 switch(dbInf.txnCapable)
888 {
889 case SQL_TC_NONE: cout << "No"; break;
890 case SQL_TC_DML: cout << "DML Only"; break;
891 case SQL_TC_DDL_COMMIT: cout << "DDL Commit"; break;
892 case SQL_TC_DDL_IGNORE: cout << "DDL Ignore"; break;
893 case SQL_TC_ALL: cout << "DDL & DML"; break;
894 }
895 cout << endl;
896
897 cout << endl;
898#endif
899
900 // Completed Successfully
901 return(TRUE);
902
903} // wxDb::getDbInfo()
904
905
906/********** wxDb::getDataTypeInfo() **********/
907bool wxDb::getDataTypeInfo(SWORD fSqlType, wxDbSqlTypeInfo &structSQLTypeInfo)
908{
909/*
910 * fSqlType will be something like SQL_VARCHAR. This parameter determines
911 * the data type inf. is gathered for.
912 *
913 * wxDbSqlTypeInfo is a structure that is filled in with data type information,
914 */
915 RETCODE retcode;
916 SDWORD cbRet;
917
918 // Get information about the data type specified
919 if (SQLGetTypeInfo(hstmt, fSqlType) != SQL_SUCCESS)
920 return(DispAllErrors(henv, hdbc, hstmt));
921 // Fetch the record
922 if ((retcode = SQLFetch(hstmt)) != SQL_SUCCESS)
923 {
924#ifdef DBDEBUG_CONSOLE
925 if (retcode == SQL_NO_DATA_FOUND)
926 cout << "SQL_NO_DATA_FOUND fetching inf. about data type." << endl;
927#endif
928 DispAllErrors(henv, hdbc, hstmt);
929 SQLFreeStmt(hstmt, SQL_CLOSE);
930 return(FALSE);
931 }
932 // Obtain columns from the record
933 if (SQLGetData(hstmt, 1, SQL_C_CHAR, (UCHAR*) structSQLTypeInfo.TypeName, DB_TYPE_NAME_LEN, &cbRet) != SQL_SUCCESS)
934 return(DispAllErrors(henv, hdbc, hstmt));
935
936
937 // BJO 20000503: no more needed with new GetColumns...
938#if OLD_GETCOLUMNS
939 // BJO 991209
940 if (Dbms() == dbmsMY_SQL)
941 {
942 if (!wxStrcmp(structSQLTypeInfo.TypeName, "middleint")) wxStrcpy(structSQLTypeInfo.TypeName, "mediumint");
943 if (!wxStrcmp(structSQLTypeInfo.TypeName, "middleint unsigned")) wxStrcpy(structSQLTypeInfo.TypeName, "mediumint unsigned");
944 if (!wxStrcmp(structSQLTypeInfo.TypeName, "integer")) wxStrcpy(structSQLTypeInfo.TypeName, "int");
945 if (!wxStrcmp(structSQLTypeInfo.TypeName, "integer unsigned")) wxStrcpy(structSQLTypeInfo.TypeName, "int unsigned");
946 if (!wxStrcmp(structSQLTypeInfo.TypeName, "middleint")) wxStrcpy(structSQLTypeInfo.TypeName, "mediumint");
947 if (!wxStrcmp(structSQLTypeInfo.TypeName, "varchar")) wxStrcpy(structSQLTypeInfo.TypeName, "char");
948 }
949
950 // BJO 20000427 : OpenLink driver
951 if (!wxStrncmp(dbInf.driverName, "oplodbc", 7) ||
952 !wxStrncmp(dbInf.driverName, "OLOD", 4))
953 {
954 if (!wxStrcmp(structSQLTypeInfo.TypeName, "double precision"))
955 wxStrcpy(structSQLTypeInfo.TypeName, "real");
956 }
957#endif
958
959 if (SQLGetData(hstmt, 3, SQL_C_LONG, (UCHAR*) &structSQLTypeInfo.Precision, 0, &cbRet) != SQL_SUCCESS)
960 return(DispAllErrors(henv, hdbc, hstmt));
961 if (SQLGetData(hstmt, 8, SQL_C_SHORT, (UCHAR*) &structSQLTypeInfo.CaseSensitive, 0, &cbRet) != SQL_SUCCESS)
962 return(DispAllErrors(henv, hdbc, hstmt));
963// if (SQLGetData(hstmt, 14, SQL_C_SHORT, (UCHAR*) &structSQLTypeInfo.MinimumScale, 0, &cbRet) != SQL_SUCCESS)
964// return(DispAllErrors(henv, hdbc, hstmt));
965
966 if (SQLGetData(hstmt, 15, SQL_C_SHORT,(UCHAR*) &structSQLTypeInfo.MaximumScale, 0, &cbRet) != SQL_SUCCESS)
967 return(DispAllErrors(henv, hdbc, hstmt));
968
969 if (structSQLTypeInfo.MaximumScale < 0)
970 structSQLTypeInfo.MaximumScale = 0;
971
972 // Close the statement handle which closes open cursors
973 if (SQLFreeStmt(hstmt, SQL_CLOSE) != SQL_SUCCESS)
974 return(DispAllErrors(henv, hdbc, hstmt));
975
976 // Completed Successfully
977 return(TRUE);
978
979} // wxDb::getDataTypeInfo()
980
981
982/********** wxDb::Close() **********/
983void wxDb::Close(void)
984{
985 // Close the Sql Log file
986 if (fpSqlLog)
987 {
988 fclose(fpSqlLog);
989 fpSqlLog = 0;
990 }
991
992 // Free statement handle
993 if (dbIsOpen)
994 {
995 if (SQLFreeStmt(hstmt, SQL_DROP) != SQL_SUCCESS)
996 DispAllErrors(henv, hdbc);
997 }
998
999 // Disconnect from the datasource
1000 if (SQLDisconnect(hdbc) != SQL_SUCCESS)
1001 DispAllErrors(henv, hdbc);
1002
1003 // Free the connection to the datasource
1004 if (SQLFreeConnect(hdbc) != SQL_SUCCESS)
1005 DispAllErrors(henv, hdbc);
1006
1007 // There should be zero Ctable objects still connected to this db object
1008 assert(nTables == 0);
1009
1010#ifdef __WXDEBUG__
1011 wxTablesInUse *tiu;
1012 wxNode *pNode;
1013 pNode = TablesInUse.First();
1014 wxString s,s2;
1015 while (pNode)
1016 {
1017 tiu = (wxTablesInUse *)pNode->Data();
1018 if (tiu->pDb == this)
1019 {
1020 s.sprintf(wxT("(%-20s) tableID:[%6lu] pDb:[%p]"), tiu->tableName,tiu->tableID,tiu->pDb);
1021 s2.sprintf(wxT("Orphaned found using pDb:[%p]"),this);
1022 wxLogDebug (s.c_str(),s2.c_str());
1023 }
1024 pNode = pNode->Next();
1025 }
1026#endif
1027
1028 // Copy the error messages to a global variable
1029 int i;
1030 for (i = 0; i < DB_MAX_ERROR_HISTORY; i++)
1031 wxStrcpy(DBerrorList[i],errorList[i]);
1032
1033} // wxDb::Close()
1034
1035
1036/********** wxDb::CommitTrans() **********/
1037bool wxDb::CommitTrans(void)
1038{
1039 if (this)
1040 {
1041 // Commit the transaction
1042 if (SQLTransact(henv, hdbc, SQL_COMMIT) != SQL_SUCCESS)
1043 return(DispAllErrors(henv, hdbc));
1044 }
1045
1046 // Completed successfully
1047 return(TRUE);
1048
1049} // wxDb::CommitTrans()
1050
1051
1052/********** wxDb::RollbackTrans() **********/
1053bool wxDb::RollbackTrans(void)
1054{
1055 // Rollback the transaction
1056 if (SQLTransact(henv, hdbc, SQL_ROLLBACK) != SQL_SUCCESS)
1057 return(DispAllErrors(henv, hdbc));
1058
1059 // Completed successfully
1060 return(TRUE);
1061
1062} // wxDb::RollbackTrans()
1063
1064
1065/********** wxDb::DispAllErrors() **********/
1066bool wxDb::DispAllErrors(HENV aHenv, HDBC aHdbc, HSTMT aHstmt)
1067{
1068// char odbcErrMsg[DB_MAX_ERROR_MSG_LEN];
1069 wxString odbcErrMsg;
1070
1071 while (SQLError(aHenv, aHdbc, aHstmt, (UCHAR FAR *) sqlState, &nativeError, (UCHAR FAR *) errorMsg, SQL_MAX_MESSAGE_LENGTH - 1, &cbErrorMsg) == SQL_SUCCESS)
1072 {
1073 odbcErrMsg.sprintf("SQL State = %s\nNative Error Code = %li\nError Message = %s\n", sqlState, nativeError, errorMsg);
1074 logError(odbcErrMsg.c_str(), sqlState);
1075 if (!silent)
1076 {
1077#ifdef DBDEBUG_CONSOLE
1078 // When run in console mode, use standard out to display errors.
1079 cout << odbcErrMsg.c_str() << endl;
1080 cout << "Press any key to continue..." << endl;
1081 getchar();
1082#endif
1083
1084#ifdef __WXDEBUG__
1085 wxLogDebug(odbcErrMsg.c_str(),wxT("ODBC DEBUG MESSAGE from DispAllErrors()"));
1086#endif
1087 }
1088 }
1089
1090 return(FALSE); // This function always returns false.
1091
1092} // wxDb::DispAllErrors()
1093
1094
1095/********** wxDb::GetNextError() **********/
1096bool wxDb::GetNextError(HENV aHenv, HDBC aHdbc, HSTMT aHstmt)
1097{
1098 if (SQLError(aHenv, aHdbc, aHstmt, (UCHAR FAR *) sqlState, &nativeError, (UCHAR FAR *) errorMsg, SQL_MAX_MESSAGE_LENGTH - 1, &cbErrorMsg) == SQL_SUCCESS)
1099 return(TRUE);
1100 else
1101 return(FALSE);
1102
1103} // wxDb::GetNextError()
1104
1105
1106/********** wxDb::DispNextError() **********/
1107void wxDb::DispNextError(void)
1108{
1109 wxString odbcErrMsg;
1110
1111 odbcErrMsg.sprintf("SQL State = %s\nNative Error Code = %li\nError Message = %s\n", sqlState, nativeError, errorMsg);
1112 logError(odbcErrMsg.c_str(), sqlState);
1113
1114 if (silent)
1115 return;
1116
1117#ifdef DBDEBUG_CONSOLE
1118 // When run in console mode, use standard out to display errors.
1119 cout << odbcErrMsg.c_str() << endl;
1120 cout << "Press any key to continue..." << endl;
1121 getchar();
1122#endif
1123
1124#ifdef __WXDEBUG__
1125 wxLogDebug(odbcErrMsg,wxT("ODBC DEBUG MESSAGE"));
1126#endif // __WXDEBUG__
1127
1128} // wxDb::DispNextError()
1129
1130
1131/********** wxDb::logError() **********/
1132void wxDb::logError(const char *errMsg, const char *SQLState)
1133{
1134 assert(errMsg && wxStrlen(errMsg));
1135
1136 static int pLast = -1;
1137 int dbStatus;
1138
1139 if (++pLast == DB_MAX_ERROR_HISTORY)
1140 {
1141 int i;
1142 for (i = 0; i < DB_MAX_ERROR_HISTORY; i++)
1143 wxStrcpy(errorList[i], errorList[i+1]);
1144 pLast--;
1145 }
1146
1147 wxStrcpy(errorList[pLast], errMsg);
1148
1149 if (SQLState && wxStrlen(SQLState))
1150 if ((dbStatus = TranslateSqlState(SQLState)) != DB_ERR_FUNCTION_SEQUENCE_ERROR)
1151 DB_STATUS = dbStatus;
1152
1153 // Add the errmsg to the sql log
1154 WriteSqlLog(errMsg);
1155
1156} // wxDb::logError()
1157
1158
1159/**********wxDb::TranslateSqlState() **********/
1160int wxDb::TranslateSqlState(const wxChar *SQLState)
1161{
1162 if (!wxStrcmp(SQLState, wxT("01000")))
1163 return(DB_ERR_GENERAL_WARNING);
1164 if (!wxStrcmp(SQLState, wxT("01002")))
1165 return(DB_ERR_DISCONNECT_ERROR);
1166 if (!wxStrcmp(SQLState, wxT("01004")))
1167 return(DB_ERR_DATA_TRUNCATED);
1168 if (!wxStrcmp(SQLState, wxT("01006")))
1169 return(DB_ERR_PRIV_NOT_REVOKED);
1170 if (!wxStrcmp(SQLState, wxT("01S00")))
1171 return(DB_ERR_INVALID_CONN_STR_ATTR);
1172 if (!wxStrcmp(SQLState, wxT("01S01")))
1173 return(DB_ERR_ERROR_IN_ROW);
1174 if (!wxStrcmp(SQLState, wxT("01S02")))
1175 return(DB_ERR_OPTION_VALUE_CHANGED);
1176 if (!wxStrcmp(SQLState, wxT("01S03")))
1177 return(DB_ERR_NO_ROWS_UPD_OR_DEL);
1178 if (!wxStrcmp(SQLState, wxT("01S04")))
1179 return(DB_ERR_MULTI_ROWS_UPD_OR_DEL);
1180 if (!wxStrcmp(SQLState, wxT("07001")))
1181 return(DB_ERR_WRONG_NO_OF_PARAMS);
1182 if (!wxStrcmp(SQLState, wxT("07006")))
1183 return(DB_ERR_DATA_TYPE_ATTR_VIOL);
1184 if (!wxStrcmp(SQLState, wxT("08001")))
1185 return(DB_ERR_UNABLE_TO_CONNECT);
1186 if (!wxStrcmp(SQLState, wxT("08002")))
1187 return(DB_ERR_CONNECTION_IN_USE);
1188 if (!wxStrcmp(SQLState, wxT("08003")))
1189 return(DB_ERR_CONNECTION_NOT_OPEN);
1190 if (!wxStrcmp(SQLState, wxT("08004")))
1191 return(DB_ERR_REJECTED_CONNECTION);
1192 if (!wxStrcmp(SQLState, wxT("08007")))
1193 return(DB_ERR_CONN_FAIL_IN_TRANS);
1194 if (!wxStrcmp(SQLState, wxT("08S01")))
1195 return(DB_ERR_COMM_LINK_FAILURE);
1196 if (!wxStrcmp(SQLState, wxT("21S01")))
1197 return(DB_ERR_INSERT_VALUE_LIST_MISMATCH);
1198 if (!wxStrcmp(SQLState, wxT("21S02")))
1199 return(DB_ERR_DERIVED_TABLE_MISMATCH);
1200 if (!wxStrcmp(SQLState, wxT("22001")))
1201 return(DB_ERR_STRING_RIGHT_TRUNC);
1202 if (!wxStrcmp(SQLState, wxT("22003")))
1203 return(DB_ERR_NUMERIC_VALUE_OUT_OF_RNG);
1204 if (!wxStrcmp(SQLState, wxT("22005")))
1205 return(DB_ERR_ERROR_IN_ASSIGNMENT);
1206 if (!wxStrcmp(SQLState, wxT("22008")))
1207 return(DB_ERR_DATETIME_FLD_OVERFLOW);
1208 if (!wxStrcmp(SQLState, wxT("22012")))
1209 return(DB_ERR_DIVIDE_BY_ZERO);
1210 if (!wxStrcmp(SQLState, wxT("22026")))
1211 return(DB_ERR_STR_DATA_LENGTH_MISMATCH);
1212 if (!wxStrcmp(SQLState, wxT("23000")))
1213 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1214 if (!wxStrcmp(SQLState, wxT("24000")))
1215 return(DB_ERR_INVALID_CURSOR_STATE);
1216 if (!wxStrcmp(SQLState, wxT("25000")))
1217 return(DB_ERR_INVALID_TRANS_STATE);
1218 if (!wxStrcmp(SQLState, wxT("28000")))
1219 return(DB_ERR_INVALID_AUTH_SPEC);
1220 if (!wxStrcmp(SQLState, wxT("34000")))
1221 return(DB_ERR_INVALID_CURSOR_NAME);
1222 if (!wxStrcmp(SQLState, wxT("37000")))
1223 return(DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL);
1224 if (!wxStrcmp(SQLState, wxT("3C000")))
1225 return(DB_ERR_DUPLICATE_CURSOR_NAME);
1226 if (!wxStrcmp(SQLState, wxT("40001")))
1227 return(DB_ERR_SERIALIZATION_FAILURE);
1228 if (!wxStrcmp(SQLState, wxT("42000")))
1229 return(DB_ERR_SYNTAX_ERROR_OR_ACCESS_VIOL2);
1230 if (!wxStrcmp(SQLState, wxT("70100")))
1231 return(DB_ERR_OPERATION_ABORTED);
1232 if (!wxStrcmp(SQLState, wxT("IM001")))
1233 return(DB_ERR_UNSUPPORTED_FUNCTION);
1234 if (!wxStrcmp(SQLState, wxT("IM002")))
1235 return(DB_ERR_NO_DATA_SOURCE);
1236 if (!wxStrcmp(SQLState, wxT("IM003")))
1237 return(DB_ERR_DRIVER_LOAD_ERROR);
1238 if (!wxStrcmp(SQLState, wxT("IM004")))
1239 return(DB_ERR_SQLALLOCENV_FAILED);
1240 if (!wxStrcmp(SQLState, wxT("IM005")))
1241 return(DB_ERR_SQLALLOCCONNECT_FAILED);
1242 if (!wxStrcmp(SQLState, wxT("IM006")))
1243 return(DB_ERR_SQLSETCONNECTOPTION_FAILED);
1244 if (!wxStrcmp(SQLState, wxT("IM007")))
1245 return(DB_ERR_NO_DATA_SOURCE_DLG_PROHIB);
1246 if (!wxStrcmp(SQLState, wxT("IM008")))
1247 return(DB_ERR_DIALOG_FAILED);
1248 if (!wxStrcmp(SQLState, wxT("IM009")))
1249 return(DB_ERR_UNABLE_TO_LOAD_TRANSLATION_DLL);
1250 if (!wxStrcmp(SQLState, wxT("IM010")))
1251 return(DB_ERR_DATA_SOURCE_NAME_TOO_LONG);
1252 if (!wxStrcmp(SQLState, wxT("IM011")))
1253 return(DB_ERR_DRIVER_NAME_TOO_LONG);
1254 if (!wxStrcmp(SQLState, wxT("IM012")))
1255 return(DB_ERR_DRIVER_KEYWORD_SYNTAX_ERROR);
1256 if (!wxStrcmp(SQLState, wxT("IM013")))
1257 return(DB_ERR_TRACE_FILE_ERROR);
1258 if (!wxStrcmp(SQLState, wxT("S0001")))
1259 return(DB_ERR_TABLE_OR_VIEW_ALREADY_EXISTS);
1260 if (!wxStrcmp(SQLState, wxT("S0002")))
1261 return(DB_ERR_TABLE_NOT_FOUND);
1262 if (!wxStrcmp(SQLState, wxT("S0011")))
1263 return(DB_ERR_INDEX_ALREADY_EXISTS);
1264 if (!wxStrcmp(SQLState, wxT("S0012")))
1265 return(DB_ERR_INDEX_NOT_FOUND);
1266 if (!wxStrcmp(SQLState, wxT("S0021")))
1267 return(DB_ERR_COLUMN_ALREADY_EXISTS);
1268 if (!wxStrcmp(SQLState, wxT("S0022")))
1269 return(DB_ERR_COLUMN_NOT_FOUND);
1270 if (!wxStrcmp(SQLState, wxT("S0023")))
1271 return(DB_ERR_NO_DEFAULT_FOR_COLUMN);
1272 if (!wxStrcmp(SQLState, wxT("S1000")))
1273 return(DB_ERR_GENERAL_ERROR);
1274 if (!wxStrcmp(SQLState, wxT("S1001")))
1275 return(DB_ERR_MEMORY_ALLOCATION_FAILURE);
1276 if (!wxStrcmp(SQLState, wxT("S1002")))
1277 return(DB_ERR_INVALID_COLUMN_NUMBER);
1278 if (!wxStrcmp(SQLState, wxT("S1003")))
1279 return(DB_ERR_PROGRAM_TYPE_OUT_OF_RANGE);
1280 if (!wxStrcmp(SQLState, wxT("S1004")))
1281 return(DB_ERR_SQL_DATA_TYPE_OUT_OF_RANGE);
1282 if (!wxStrcmp(SQLState, wxT("S1008")))
1283 return(DB_ERR_OPERATION_CANCELLED);
1284 if (!wxStrcmp(SQLState, wxT("S1009")))
1285 return(DB_ERR_INVALID_ARGUMENT_VALUE);
1286 if (!wxStrcmp(SQLState, wxT("S1010")))
1287 return(DB_ERR_FUNCTION_SEQUENCE_ERROR);
1288 if (!wxStrcmp(SQLState, wxT("S1011")))
1289 return(DB_ERR_OPERATION_INVALID_AT_THIS_TIME);
1290 if (!wxStrcmp(SQLState, wxT("S1012")))
1291 return(DB_ERR_INVALID_TRANS_OPERATION_CODE);
1292 if (!wxStrcmp(SQLState, wxT("S1015")))
1293 return(DB_ERR_NO_CURSOR_NAME_AVAIL);
1294 if (!wxStrcmp(SQLState, wxT("S1090")))
1295 return(DB_ERR_INVALID_STR_OR_BUF_LEN);
1296 if (!wxStrcmp(SQLState, wxT("S1091")))
1297 return(DB_ERR_DESCRIPTOR_TYPE_OUT_OF_RANGE);
1298 if (!wxStrcmp(SQLState, wxT("S1092")))
1299 return(DB_ERR_OPTION_TYPE_OUT_OF_RANGE);
1300 if (!wxStrcmp(SQLState, wxT("S1093")))
1301 return(DB_ERR_INVALID_PARAM_NO);
1302 if (!wxStrcmp(SQLState, wxT("S1094")))
1303 return(DB_ERR_INVALID_SCALE_VALUE);
1304 if (!wxStrcmp(SQLState, wxT("S1095")))
1305 return(DB_ERR_FUNCTION_TYPE_OUT_OF_RANGE);
1306 if (!wxStrcmp(SQLState, wxT("S1096")))
1307 return(DB_ERR_INF_TYPE_OUT_OF_RANGE);
1308 if (!wxStrcmp(SQLState, wxT("S1097")))
1309 return(DB_ERR_COLUMN_TYPE_OUT_OF_RANGE);
1310 if (!wxStrcmp(SQLState, wxT("S1098")))
1311 return(DB_ERR_SCOPE_TYPE_OUT_OF_RANGE);
1312 if (!wxStrcmp(SQLState, wxT("S1099")))
1313 return(DB_ERR_NULLABLE_TYPE_OUT_OF_RANGE);
1314 if (!wxStrcmp(SQLState, wxT("S1100")))
1315 return(DB_ERR_UNIQUENESS_OPTION_TYPE_OUT_OF_RANGE);
1316 if (!wxStrcmp(SQLState, wxT("S1101")))
1317 return(DB_ERR_ACCURACY_OPTION_TYPE_OUT_OF_RANGE);
1318 if (!wxStrcmp(SQLState, wxT("S1103")))
1319 return(DB_ERR_DIRECTION_OPTION_OUT_OF_RANGE);
1320 if (!wxStrcmp(SQLState, wxT("S1104")))
1321 return(DB_ERR_INVALID_PRECISION_VALUE);
1322 if (!wxStrcmp(SQLState, wxT("S1105")))
1323 return(DB_ERR_INVALID_PARAM_TYPE);
1324 if (!wxStrcmp(SQLState, wxT("S1106")))
1325 return(DB_ERR_FETCH_TYPE_OUT_OF_RANGE);
1326 if (!wxStrcmp(SQLState, wxT("S1107")))
1327 return(DB_ERR_ROW_VALUE_OUT_OF_RANGE);
1328 if (!wxStrcmp(SQLState, wxT("S1108")))
1329 return(DB_ERR_CONCURRENCY_OPTION_OUT_OF_RANGE);
1330 if (!wxStrcmp(SQLState, wxT("S1109")))
1331 return(DB_ERR_INVALID_CURSOR_POSITION);
1332 if (!wxStrcmp(SQLState, wxT("S1110")))
1333 return(DB_ERR_INVALID_DRIVER_COMPLETION);
1334 if (!wxStrcmp(SQLState, wxT("S1111")))
1335 return(DB_ERR_INVALID_BOOKMARK_VALUE);
1336 if (!wxStrcmp(SQLState, wxT("S1C00")))
1337 return(DB_ERR_DRIVER_NOT_CAPABLE);
1338 if (!wxStrcmp(SQLState, wxT("S1T00")))
1339 return(DB_ERR_TIMEOUT_EXPIRED);
1340
1341 // No match
1342 return(0);
1343
1344} // wxDb::TranslateSqlState()
1345
1346
1347/********** wxDb::Grant() **********/
1348bool wxDb::Grant(int privileges, const char *tableName, const char *userList)
1349{
1350 wxString sqlStmt;
1351
1352 // Build the grant statement
1353 sqlStmt = "GRANT ";
1354 if (privileges == DB_GRANT_ALL)
1355 sqlStmt += "ALL";
1356 else
1357 {
1358 int c = 0;
1359 if (privileges & DB_GRANT_SELECT)
1360 {
1361 sqlStmt += "SELECT";
1362 c++;
1363 }
1364 if (privileges & DB_GRANT_INSERT)
1365 {
1366 if (c++)
1367 sqlStmt += ", ";
1368 sqlStmt += "INSERT";
1369 }
1370 if (privileges & DB_GRANT_UPDATE)
1371 {
1372 if (c++)
1373 sqlStmt += ", ";
1374 sqlStmt += "UPDATE";
1375 }
1376 if (privileges & DB_GRANT_DELETE)
1377 {
1378 if (c++)
1379 sqlStmt += ", ";
1380 sqlStmt += "DELETE";
1381 }
1382 }
1383
1384 sqlStmt += " ON ";
1385 sqlStmt += tableName;
1386 sqlStmt += " TO ";
1387 sqlStmt += userList;
1388
1389#ifdef DBDEBUG_CONSOLE
1390 cout << endl << sqlStmt.c_str() << endl;
1391#endif
1392
1393 WriteSqlLog(sqlStmt.c_str());
1394
1395 return(ExecSql(sqlStmt.c_str()));
1396
1397} // wxDb::Grant()
1398
1399
1400/********** wxDb::CreateView() **********/
1401bool wxDb::CreateView(const char *viewName, const char *colList, const char *pSqlStmt, bool attemptDrop)
1402{
1403 wxString sqlStmt;
1404
1405 // Drop the view first
1406 if (attemptDrop && !DropView(viewName))
1407 return FALSE;
1408
1409 // Build the create view statement
1410 sqlStmt = "CREATE VIEW ";
1411 sqlStmt += viewName;
1412
1413 if (wxStrlen(colList))
1414 {
1415 sqlStmt += " (";
1416 sqlStmt += colList;
1417 sqlStmt += ")";
1418 }
1419
1420 sqlStmt += " AS ";
1421 sqlStmt += pSqlStmt;
1422
1423 WriteSqlLog(sqlStmt.c_str());
1424
1425#ifdef DBDEBUG_CONSOLE
1426 cout << sqlStmt.c_str() << endl;
1427#endif
1428
1429 return(ExecSql(sqlStmt.c_str()));
1430
1431} // wxDb::CreateView()
1432
1433
1434/********** wxDb::DropView() **********/
1435bool wxDb::DropView(const char *viewName)
1436{
1437/*
1438 * NOTE: This function returns TRUE if the View does not exist, but
1439 * only for identified databases. Code will need to be added
1440 * below for any other databases when those databases are defined
1441 * to handle this situation consistently
1442 */
1443// char sqlStmt[DB_MAX_STATEMENT_LEN];
1444 wxString sqlStmt;
1445
1446 sqlStmt.sprintf("DROP VIEW %s", viewName);
1447
1448 WriteSqlLog(sqlStmt.c_str());
1449
1450#ifdef DBDEBUG_CONSOLE
1451 cout << endl << sqlStmt.c_str() << endl;
1452#endif
1453
1454 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
1455 {
1456 // Check for "Base table not found" error and ignore
1457 GetNextError(henv, hdbc, hstmt);
1458 if (wxStrcmp(sqlState,wxT("S0002"))) // "Base table not found"
1459 {
1460 // Check for product specific error codes
1461 if (!((Dbms() == dbmsSYBASE_ASA && !wxStrcmp(sqlState,wxT("42000"))))) // 5.x (and lower?)
1462 {
1463 DispNextError();
1464 DispAllErrors(henv, hdbc, hstmt);
1465 RollbackTrans();
1466 return(FALSE);
1467 }
1468 }
1469 }
1470
1471 // Commit the transaction
1472 if (! CommitTrans())
1473 return(FALSE);
1474
1475 return TRUE;
1476
1477} // wxDb::DropView()
1478
1479
1480/********** wxDb::ExecSql() **********/
1481bool wxDb::ExecSql(const char *pSqlStmt)
1482{
1483 SQLFreeStmt(hstmt, SQL_CLOSE);
1484 if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt, SQL_NTS) == SQL_SUCCESS)
1485 return(TRUE);
1486 else
1487 {
1488 DispAllErrors(henv, hdbc, hstmt);
1489 return(FALSE);
1490 }
1491
1492} // wxDb::ExecSql()
1493
1494
1495/********** wxDb::GetNext() **********/
1496bool wxDb::GetNext(void)
1497{
1498 if (SQLFetch(hstmt) == SQL_SUCCESS)
1499 return(TRUE);
1500 else
1501 {
1502 DispAllErrors(henv, hdbc, hstmt);
1503 return(FALSE);
1504 }
1505
1506} // wxDb::GetNext()
1507
1508
1509/********** wxDb::GetData() **********/
1510bool wxDb::GetData(UWORD colNo, SWORD cType, PTR pData, SDWORD maxLen, SDWORD FAR *cbReturned)
1511{
1512 assert(pData);
1513 assert(cbReturned);
1514
1515 if (SQLGetData(hstmt, colNo, cType, pData, maxLen, cbReturned) == SQL_SUCCESS)
1516 return(TRUE);
1517 else
1518 {
1519 DispAllErrors(henv, hdbc, hstmt);
1520 return(FALSE);
1521 }
1522
1523} // wxDb::GetData()
1524
1525
1526/********** wxDb::GetKeyFields() **********/
1527int wxDb::GetKeyFields(char *tableName, wxDbColInf* colInf, int noCols)
1528{
1529 char szPkTable[DB_MAX_TABLE_NAME_LEN+1]; /* Primary key table name */
1530 char szFkTable[DB_MAX_TABLE_NAME_LEN+1]; /* Foreign key table name */
1531 short iKeySeq;
1532// SQLSMALLINT iKeySeq;
1533 char szPkCol[DB_MAX_COLUMN_NAME_LEN+1]; /* Primary key column */
1534 char szFkCol[DB_MAX_COLUMN_NAME_LEN+1]; /* Foreign key column */
1535 SQLRETURN retcode;
1536 SDWORD cb;
1537 int i;
1538 wxString Temp0;
1539 /*
1540 * ---------------------------------------------------------------------
1541 * -- 19991224 : mj10777 : Create ------
1542 * -- : Three things are done and stored here : ------
1543 * -- : 1) which Column(s) is/are Primary Key(s) ------
1544 * -- : 2) which tables use this Key as a Foreign Key ------
1545 * -- : 3) which columns are Foreign Key and the name ------
1546 * -- : of the Table where the Key is the Primary Key -----
1547 * -- : Called from GetColumns(char *tableName, ------
1548 * -- int *numCols,const char *userID ) ------
1549 * ---------------------------------------------------------------------
1550 */
1551
1552 /*---------------------------------------------------------------------*/
1553 /* Get the names of the columns in the primary key. */
1554 /*---------------------------------------------------------------------*/
1555 retcode = SQLPrimaryKeys(hstmt,
1556 NULL, 0, /* Catalog name */
1557 NULL, 0, /* Schema name */
1558 (UCHAR *) tableName, SQL_NTS); /* Table name */
1559
1560 /*---------------------------------------------------------------------*/
1561 /* Fetch and display the result set. This will be a list of the */
1562 /* columns in the primary key of the tableName table. */
1563 /*---------------------------------------------------------------------*/
1564 while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
1565 {
1566 retcode = SQLFetch(hstmt);
1567 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
1568 {
1569 GetData( 4, SQL_C_CHAR, szPkCol, DB_MAX_COLUMN_NAME_LEN+1, &cb);
1570 GetData( 5, SQL_C_SSHORT, &iKeySeq, 0, &cb);
1571 //-------
1572 for (i=0;i<noCols;i++) // Find the Column name
1573 if (!wxStrcmp(colInf[i].colName,szPkCol)) // We have found the Column
1574 colInf[i].PkCol = iKeySeq; // Which Primary Key is this (first, second usw.) ?
1575 } // if
1576 } // while
1577 SQLFreeStmt(hstmt, SQL_CLOSE); /* Close the cursor (the hstmt is still allocated). */
1578
1579 /*---------------------------------------------------------------------*/
1580 /* Get all the foreign keys that refer to tableName primary key. */
1581 /*---------------------------------------------------------------------*/
1582 retcode = SQLForeignKeys(hstmt,
1583 NULL, 0, /* Primary catalog */
1584 NULL, 0, /* Primary schema */
1585 (UCHAR *)tableName, SQL_NTS, /* Primary table */
1586 NULL, 0, /* Foreign catalog */
1587 NULL, 0, /* Foreign schema */
1588 NULL, 0); /* Foreign table */
1589
1590 /*---------------------------------------------------------------------*/
1591 /* Fetch and display the result set. This will be all of the foreign */
1592 /* keys in other tables that refer to the tableName primary key. */
1593 /*---------------------------------------------------------------------*/
1594 Temp0.Empty();
1595 szPkCol[0] = 0;
1596 while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
1597 {
1598 retcode = SQLFetch(hstmt);
1599 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
1600 {
1601 GetData( 3, SQL_C_CHAR, szPkTable, DB_MAX_TABLE_NAME_LEN+1, &cb);
1602 GetData( 4, SQL_C_CHAR, szPkCol, DB_MAX_COLUMN_NAME_LEN+1, &cb);
1603 GetData( 5, SQL_C_SSHORT, &iKeySeq, 0, &cb);
1604 GetData( 7, SQL_C_CHAR, szFkTable, DB_MAX_TABLE_NAME_LEN+1, &cb);
1605 GetData( 8, SQL_C_CHAR, szFkCol, DB_MAX_COLUMN_NAME_LEN+1, &cb);
1606 Temp0.Printf(wxT("%s[%s] "),Temp0.c_str(),szFkTable); // [ ] in case there is a blank in the Table name
1607 } // if
1608 } // while
1609 Temp0.Trim(); // Get rid of any unneeded blanks
1610 if (Temp0 != wxT(""))
1611 {
1612 for (i=0;i<noCols;i++)
1613 { // Find the Column name
1614 if (!wxStrcmp(colInf[i].colName,szPkCol)) // We have found the Column, store the Information
1615 wxStrcpy(colInf[i].PkTableName,Temp0.c_str()); // Name of the Tables where this Primary Key is used as a Foreign Key
1616 }
1617 } // if
1618 SQLFreeStmt(hstmt, SQL_CLOSE); /* Close the cursor (the hstmt is still allocated). */
1619
1620 /*---------------------------------------------------------------------*/
1621 /* Get all the foreign keys in the tablename table. */
1622 /*---------------------------------------------------------------------*/
1623 retcode = SQLForeignKeys(hstmt,
1624 NULL, 0, /* Primary catalog */
1625 NULL, 0, /* Primary schema */
1626 NULL, 0, /* Primary table */
1627 NULL, 0, /* Foreign catalog */
1628 NULL, 0, /* Foreign schema */
1629 (UCHAR *)tableName, SQL_NTS); /* Foreign table */
1630
1631 /*---------------------------------------------------------------------*/
1632 /* Fetch and display the result set. This will be all of the */
1633 /* primary keys in other tables that are referred to by foreign */
1634 /* keys in the tableName table. */
1635 /*---------------------------------------------------------------------*/
1636 i = 0;
1637 while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
1638 {
1639 retcode = SQLFetch(hstmt);
1640 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
1641 {
1642 GetData( 3, SQL_C_CHAR, szPkTable, DB_MAX_TABLE_NAME_LEN+1, &cb);
1643 GetData( 5, SQL_C_SSHORT, &iKeySeq, 0, &cb);
1644 GetData( 8, SQL_C_CHAR, szFkCol, DB_MAX_COLUMN_NAME_LEN+1, &cb);
1645 //-------
1646 for (i=0;i<noCols;i++) // Find the Column name
1647 {
1648 if (!wxStrcmp(colInf[i].colName,szFkCol)) // We have found the (Foreign Key) Column
1649 {
1650 colInf[i].FkCol = iKeySeq; // Which Foreign Key is this (first, second usw.) ?
1651 wxStrcpy(colInf[i].FkTableName,szPkTable); // Name of the Table where this Foriegn is the Primary Key
1652 } // if
1653 } // for
1654 } // if
1655 } // while
1656 SQLFreeStmt(hstmt, SQL_CLOSE); /* Close the cursor (the hstmt is still allocated). */
1657
1658 return TRUE;
1659
1660} // wxDb::GetKeyFields()
1661
1662
1663#if OLD_GETCOLUMNS
1664/********** wxDb::GetColumns() **********/
1665wxDbColInf *wxDb::GetColumns(char *tableName[], const char *userID)
1666/*
1667 * 1) The last array element of the tableName[] argument must be zero (null).
1668 * This is how the end of the array is detected.
1669 * 2) This function returns an array of wxDbColInf structures. If no columns
1670 * were found, or an error occured, this pointer will be zero (null). THE
1671 * CALLING FUNCTION IS RESPONSIBLE FOR DELETING THE MEMORY RETURNED WHEN IT
1672 * IS FINISHED WITH IT. i.e.
1673 *
1674 * wxDbColInf *colInf = pDb->GetColumns(tableList, userID);
1675 * if (colInf)
1676 * {
1677 * // Use the column inf
1678 * .......
1679 * // Destroy the memory
1680 * delete [] colInf;
1681 * }
1682 *
1683 * userID is evaluated in the following manner:
1684 * userID == NULL ... UserID is ignored
1685 * userID == "" ... UserID set equal to 'this->uid'
1686 * userID != "" ... UserID set equal to 'userID'
1687 *
1688 * NOTE: ALL column bindings associated with this wxDb instance are unbound
1689 * by this function. This function should use its own wxDb instance
1690 * to avoid undesired unbinding of columns.
1691 */
1692{
1693 int noCols = 0;
1694 int colNo = 0;
1695 wxDbColInf *colInf = 0;
1696
1697 RETCODE retcode;
1698 SDWORD cb;
1699
1700 wxString UserID;
1701 wxString TableName;
1702
1703 if (userID)
1704 {
1705 if (!wxStrlen(userID))
1706 UserID = uid;
1707 else
1708 UserID = userID;
1709 }
1710 else
1711 UserID = "";
1712
1713 // dBase does not use user names, and some drivers fail if you try to pass one
1714 if (Dbms() == dbmsDBASE)
1715 UserID = "";
1716
1717 // Oracle user names may only be in uppercase, so force
1718 // the name to uppercase
1719 if (Dbms() == dbmsORACLE)
1720 UserID = UserID.Upper();
1721
1722 // Pass 1 - Determine how many columns there are.
1723 // Pass 2 - Allocate the wxDbColInf array and fill in
1724 // the array with the column information.
1725 int pass;
1726 for (pass = 1; pass <= 2; pass++)
1727 {
1728 if (pass == 2)
1729 {
1730 if (noCols == 0) // Probably a bogus table name(s)
1731 break;
1732 // Allocate n wxDbColInf objects to hold the column information
1733 colInf = new wxDbColInf[noCols+1];
1734 if (!colInf)
1735 break;
1736 // Mark the end of the array
1737 wxStrcpy(colInf[noCols].tableName, wxT(""));
1738 wxStrcpy(colInf[noCols].colName, wxT(""));
1739 colInf[noCols].sqlDataType = 0;
1740 }
1741 // Loop through each table name
1742 int tbl;
1743 for (tbl = 0; tableName[tbl]; tbl++)
1744 {
1745 TableName = tableName[tbl];
1746 // Oracle table names are uppercase only, so force
1747 // the name to uppercase just in case programmer forgot to do this
1748 if (Dbms() == dbmsORACLE)
1749 TableName = TableName.Upper();
1750
1751 SQLFreeStmt(hstmt, SQL_CLOSE);
1752
1753 // MySQL and Access cannot accept a user name when looking up column names, so we
1754 // use the call below that leaves out the user name
1755 if (wxStrcmp(UserID.c_str(),wxT("")) &&
1756 Dbms() != dbmsMY_SQL &&
1757 Dbms() != dbmsACCESS)
1758 {
1759 retcode = SQLColumns(hstmt,
1760 NULL, 0, // All qualifiers
1761 (UCHAR *) UserID.c_str(), SQL_NTS, // Owner
1762 (UCHAR *) TableName.c_str(), SQL_NTS,
1763 NULL, 0); // All columns
1764 }
1765 else
1766 {
1767 retcode = SQLColumns(hstmt,
1768 NULL, 0, // All qualifiers
1769 NULL, 0, // Owner
1770 (UCHAR *) TableName.c_str(), SQL_NTS,
1771 NULL, 0); // All columns
1772 }
1773 if (retcode != SQL_SUCCESS)
1774 { // Error occured, abort
1775 DispAllErrors(henv, hdbc, hstmt);
1776 if (colInf)
1777 delete [] colInf;
1778 SQLFreeStmt(hstmt, SQL_CLOSE);
1779 return(0);
1780 }
1781
1782 while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS)
1783 {
1784 if (pass == 1) // First pass, just add up the number of columns
1785 noCols++;
1786 else // Pass 2; Fill in the array of structures
1787 {
1788 if (colNo < noCols) // Some extra error checking to prevent memory overwrites
1789 {
1790 // NOTE: Only the ODBC 1.x fields are retrieved
1791 GetData( 1, SQL_C_CHAR, (UCHAR*) colInf[colNo].catalog, 128+1, &cb);
1792 GetData( 2, SQL_C_CHAR, (UCHAR*) colInf[colNo].schema, 128+1, &cb);
1793 GetData( 3, SQL_C_CHAR, (UCHAR*) colInf[colNo].tableName, DB_MAX_TABLE_NAME_LEN+1, &cb);
1794 GetData( 4, SQL_C_CHAR, (UCHAR*) colInf[colNo].colName, DB_MAX_COLUMN_NAME_LEN+1, &cb);
1795 GetData( 5, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].sqlDataType, 0, &cb);
1796 GetData( 6, SQL_C_CHAR, (UCHAR*) colInf[colNo].typeName, 128+1, &cb);
1797 GetData( 7, SQL_C_SLONG, (UCHAR*) &colInf[colNo].columnSize, 0, &cb);
1798 GetData( 8, SQL_C_SLONG, (UCHAR*) &colInf[colNo].bufferLength, 0, &cb);
1799 GetData( 9, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].decimalDigits,0, &cb);
1800 GetData(10, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].numPrecRadix, 0, &cb);
1801 GetData(11, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].nullable, 0, &cb);
1802 GetData(12, SQL_C_CHAR, (UCHAR*) colInf[colNo].remarks, 254+1, &cb);
1803
1804 // Determine the wxDb data type that is used to represent the native data type of this data source
1805 colInf[colNo].dbDataType = 0;
1806 if (!wxStricmp(typeInfVarchar.TypeName,colInf[colNo].typeName))
1807 {
1808 if (colInf[colNo].columnSize < 1)
1809 {
1810 // IODBC does not return a correct columnSize, so we set
1811 // columnSize = bufferLength if no column size was returned
1812 colInf[colNo].columnSize = colInf[colNo].bufferLength;
1813 }
1814 colInf[colNo].dbDataType = DB_DATA_TYPE_VARCHAR;
1815 }
1816 else if (!wxStricmp(typeInfInteger.TypeName,colInf[colNo].typeName))
1817 colInf[colNo].dbDataType = DB_DATA_TYPE_INTEGER;
1818 else if (!wxStricmp(typeInfFloat.TypeName,colInf[colNo].typeName))
1819 colInf[colNo].dbDataType = DB_DATA_TYPE_FLOAT;
1820 else if (!wxStricmp(typeInfDate.TypeName,colInf[colNo].typeName))
1821 colInf[colNo].dbDataType = DB_DATA_TYPE_DATE;
1822
1823 colNo++;
1824 }
1825 }
1826 }
1827 if (retcode != SQL_NO_DATA_FOUND)
1828 { // Error occured, abort
1829 DispAllErrors(henv, hdbc, hstmt);
1830 if (colInf)
1831 delete [] colInf;
1832 SQLFreeStmt(hstmt, SQL_CLOSE);
1833 return(0);
1834 }
1835 }
1836 }
1837
1838 SQLFreeStmt(hstmt, SQL_CLOSE);
1839 return colInf;
1840
1841} // wxDb::GetColumns()
1842
1843
1844/********** wxDb::GetColumns() **********/
1845
1846wxDbColInf *wxDb::GetColumns(char *tableName, int *numCols, const char *userID)
1847//
1848// Same as the above GetColumns() function except this one gets columns
1849// only for a single table, and if 'numCols' is not NULL, the number of
1850// columns stored in the returned wxDbColInf is set in '*numCols'
1851//
1852// userID is evaluated in the following manner:
1853// userID == NULL ... UserID is ignored
1854// userID == "" ... UserID set equal to 'this->uid'
1855// userID != "" ... UserID set equal to 'userID'
1856//
1857// NOTE: ALL column bindings associated with this wxDb instance are unbound
1858// by this function. This function should use its own wxDb instance
1859// to avoid undesired unbinding of columns.
1860
1861{
1862 int noCols = 0;
1863 int colNo = 0;
1864 wxDbColInf *colInf = 0;
1865
1866 RETCODE retcode;
1867 SDWORD cb;
1868
1869 wxString UserID;
1870 wxString TableName;
1871
1872 if (userID)
1873 {
1874 if (!wxStrlen(userID))
1875 UserID = uid;
1876 else
1877 UserID = userID;
1878 }
1879 else
1880 UserID = "";
1881
1882 // dBase does not use user names, and some drivers fail if you try to pass one
1883 if (Dbms() == dbmsDBASE)
1884 UserID = "";
1885
1886 // Oracle user names may only be in uppercase, so force
1887 // the name to uppercase
1888 if (Dbms() == dbmsORACLE)
1889 UserID = UserID.Upper();
1890
1891 // Pass 1 - Determine how many columns there are.
1892 // Pass 2 - Allocate the wxDbColInf array and fill in
1893 // the array with the column information.
1894 int pass;
1895 for (pass = 1; pass <= 2; pass++)
1896 {
1897 if (pass == 2)
1898 {
1899 if (noCols == 0) // Probably a bogus table name(s)
1900 break;
1901 // Allocate n wxDbColInf objects to hold the column information
1902 colInf = new wxDbColInf[noCols+1];
1903 if (!colInf)
1904 break;
1905 // Mark the end of the array
1906 wxStrcpy(colInf[noCols].tableName, wxT(""));
1907 wxStrcpy(colInf[noCols].colName, wxT(""));
1908 colInf[noCols].sqlDataType = 0;
1909 }
1910
1911 TableName = tableName;
1912 // Oracle table names are uppercase only, so force
1913 // the name to uppercase just in case programmer forgot to do this
1914 if (Dbms() == dbmsORACLE)
1915 TableName = TableName.Upper();
1916
1917 SQLFreeStmt(hstmt, SQL_CLOSE);
1918
1919 // MySQL and Access cannot accept a user name when looking up column names, so we
1920 // use the call below that leaves out the user name
1921 if (wxStrcmp(UserID.c_str(),wxT("")) &&
1922 Dbms() != dbmsMY_SQL &&
1923 Dbms() != dbmsACCESS)
1924 {
1925 retcode = SQLColumns(hstmt,
1926 NULL, 0, // All qualifiers
1927 (UCHAR *) UserID.c_str(), SQL_NTS, // Owner
1928 (UCHAR *) TableName.c_str(), SQL_NTS,
1929 NULL, 0); // All columns
1930 }
1931 else
1932 {
1933 retcode = SQLColumns(hstmt,
1934 NULL, 0, // All qualifiers
1935 NULL, 0, // Owner
1936 (UCHAR *) TableName.c_str(), SQL_NTS,
1937 NULL, 0); // All columns
1938 }
1939 if (retcode != SQL_SUCCESS)
1940 { // Error occured, abort
1941 DispAllErrors(henv, hdbc, hstmt);
1942 if (colInf)
1943 delete [] colInf;
1944 SQLFreeStmt(hstmt, SQL_CLOSE);
1945 if (numCols)
1946 *numCols = 0;
1947 return(0);
1948 }
1949
1950 while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS)
1951 {
1952 if (pass == 1) // First pass, just add up the number of columns
1953 noCols++;
1954 else // Pass 2; Fill in the array of structures
1955 {
1956 if (colNo < noCols) // Some extra error checking to prevent memory overwrites
1957 {
1958 // NOTE: Only the ODBC 1.x fields are retrieved
1959 GetData( 1, SQL_C_CHAR, (UCHAR*) colInf[colNo].catalog, 128+1, &cb);
1960 GetData( 2, SQL_C_CHAR, (UCHAR*) colInf[colNo].schema, 128+1, &cb);
1961 GetData( 3, SQL_C_CHAR, (UCHAR*) colInf[colNo].tableName, DB_MAX_TABLE_NAME_LEN+1, &cb);
1962 GetData( 4, SQL_C_CHAR, (UCHAR*) colInf[colNo].colName, DB_MAX_COLUMN_NAME_LEN+1, &cb);
1963 GetData( 5, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].sqlDataType, 0, &cb);
1964 GetData( 6, SQL_C_CHAR, (UCHAR*) colInf[colNo].typeName, 128+1, &cb);
1965 GetData( 7, SQL_C_SLONG, (UCHAR*) &colInf[colNo].columnSize, 0, &cb);
1966 // BJO 991214 : SQL_C_SSHORT instead of SQL_C_SLONG, otherwise fails on Sparc (probably all 64 bit architectures)
1967 GetData( 8, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].bufferLength, 0, &cb);
1968 GetData( 9, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].decimalDigits,0, &cb);
1969 GetData(10, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].numPrecRadix, 0, &cb);
1970 GetData(11, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].nullable, 0, &cb);
1971 GetData(12, SQL_C_CHAR, (UCHAR*) colInf[colNo].remarks, 254+1, &cb);
1972 // Start Values for Primary/Foriegn Key (=No)
1973 colInf[colNo].PkCol = 0; // Primary key column 0=No; 1= First Key, 2 = Second Key etc.
1974 colInf[colNo].PkTableName[0] = 0; // Tablenames where Primary Key is used as a Foreign Key
1975 colInf[colNo].FkCol = 0; // Foreign key column 0=No; 1= First Key, 2 = Second Key etc.
1976 colInf[colNo].FkTableName[0] = 0; // Foreign key table name
1977
1978 // BJO 20000428 : Virtuoso returns type names with upper cases!
1979 if (Dbms() == dbmsVIRTUOSO)
1980 {
1981 wxString s = colInf[colNo].typeName;
1982 s = s.MakeLower();
1983 wxStrcmp(colInf[colNo].typeName, s.c_str());
1984 }
1985
1986 // Determine the wxDb data type that is used to represent the native data type of this data source
1987 colInf[colNo].dbDataType = 0;
1988 if (!wxStricmp(typeInfVarchar.TypeName,colInf[colNo].typeName))
1989 {
1990 if (colInf[colNo].columnSize < 1)
1991 {
1992 // IODBC does not return a correct columnSize, so we set
1993 // columnSize = bufferLength if no column size was returned
1994 colInf[colNo].columnSize = colInf[colNo].bufferLength;
1995 }
1996 colInf[colNo].dbDataType = DB_DATA_TYPE_VARCHAR;
1997 }
1998 else if (!wxStricmp(typeInfInteger.TypeName,colInf[colNo].typeName))
1999 colInf[colNo].dbDataType = DB_DATA_TYPE_INTEGER;
2000 else if (!wxStricmp(typeInfFloat.TypeName,colInf[colNo].typeName))
2001 colInf[colNo].dbDataType = DB_DATA_TYPE_FLOAT;
2002 else if (!wxStricmp(typeInfDate.TypeName,colInf[colNo].typeName))
2003 colInf[colNo].dbDataType = DB_DATA_TYPE_DATE;
2004
2005 colNo++;
2006 }
2007 }
2008 }
2009 if (retcode != SQL_NO_DATA_FOUND)
2010 { // Error occured, abort
2011 DispAllErrors(henv, hdbc, hstmt);
2012 if (colInf)
2013 delete [] colInf;
2014 SQLFreeStmt(hstmt, SQL_CLOSE);
2015 if (numCols)
2016 *numCols = 0;
2017 return(0);
2018 }
2019 }
2020
2021 SQLFreeStmt(hstmt, SQL_CLOSE);
2022
2023 // Store Primary and Foriegn Keys
2024 GetKeyFields(tableName,colInf,noCols);
2025
2026 if (numCols)
2027 *numCols = noCols;
2028 return colInf;
2029
2030} // wxDb::GetColumns()
2031
2032
2033#else // New GetColumns
2034
2035
2036/*
2037 BJO 20000503
2038 These are tentative new GetColumns members which should be more database
2039 independant and which always returns the columns in the order they were
2040 created.
2041
2042 - The first one (wxDbColInf *wxDb::GetColumns(char *tableName[], const
2043 char* userID)) calls the second implementation for each separate table
2044 before merging the results. This makes the code easier to maintain as
2045 only one member (the second) makes the real work
2046 - wxDbColInf *wxDb::GetColumns(char *tableName, int *numCols, const
2047 char *userID) is a little bit improved
2048 - It doesn't anymore rely on the type-name to find out which database-type
2049 each column has
2050 - It ends by sorting the columns, so that they are returned in the same
2051 order they were created
2052*/
2053
2054typedef struct
2055{
2056 int noCols;
2057 wxDbColInf *colInf;
2058} _TableColumns;
2059
2060
2061wxDbColInf *wxDb::GetColumns(char *tableName[], const char* userID)
2062{
2063 int i, j;
2064 // The last array element of the tableName[] argument must be zero (null).
2065 // This is how the end of the array is detected.
2066
2067 int noCols = 0;
2068
2069 // How many tables ?
2070 int tbl;
2071 for (tbl = 0 ; tableName[tbl]; tbl++);
2072
2073 // Create a table to maintain the columns for each separate table
2074 _TableColumns *TableColumns = new _TableColumns[tbl];
2075
2076 // Fill the table
2077 for (i = 0 ; i < tbl ; i++)
2078
2079 {
2080 TableColumns[i].colInf = GetColumns(tableName[i], &TableColumns[i].noCols, userID);
2081 if (TableColumns[i].colInf == NULL)
2082 return NULL;
2083 noCols += TableColumns[i].noCols;
2084 }
2085
2086 // Now merge all the separate table infos
2087 wxDbColInf *colInf = new wxDbColInf[noCols+1];
2088
2089 // Mark the end of the array
2090 wxStrcpy(colInf[noCols].tableName, wxT(""));
2091 wxStrcpy(colInf[noCols].colName, wxT(""));
2092 colInf[noCols].sqlDataType = 0;
2093
2094 // Merge ...
2095 int offset = 0;
2096
2097 for (i = 0 ; i < tbl ; i++)
2098 {
2099 for (j = 0 ; j < TableColumns[i].noCols ; j++)
2100 {
2101 colInf[offset++] = TableColumns[i].colInf[j];
2102 }
2103 }
2104
2105 delete [] TableColumns;
2106
2107 return colInf;
2108} // wxDb::GetColumns() -- NEW
2109
2110
2111wxDbColInf *wxDb::GetColumns(char *tableName, int *numCols, const char *userID)
2112//
2113// Same as the above GetColumns() function except this one gets columns
2114// only for a single table, and if 'numCols' is not NULL, the number of
2115// columns stored in the returned wxDbColInf is set in '*numCols'
2116//
2117// userID is evaluated in the following manner:
2118// userID == NULL ... UserID is ignored
2119// userID == "" ... UserID set equal to 'this->uid'
2120// userID != "" ... UserID set equal to 'userID'
2121//
2122// NOTE: ALL column bindings associated with this wxDb instance are unbound
2123// by this function. This function should use its own wxDb instance
2124// to avoid undesired unbinding of columns.
2125{
2126 SWORD noCols = 0;
2127 int colNo = 0;
2128 wxDbColInf *colInf = 0;
2129
2130 RETCODE retcode;
2131 SDWORD cb;
2132
2133 wxString UserID;
2134 wxString TableName;
2135
2136 if (userID)
2137 {
2138 if (!wxStrlen(userID))
2139 UserID = uid;
2140 else
2141 UserID = userID;
2142 }
2143 else
2144 UserID = "";
2145
2146 // dBase does not use user names, and some drivers fail if you try to pass one
2147 if (Dbms() == dbmsDBASE)
2148 UserID = "";
2149
2150 // Oracle user names may only be in uppercase, so force
2151 // the name to uppercase
2152 if (Dbms() == dbmsORACLE)
2153 UserID = UserID.Upper();
2154
2155 // Pass 1 - Determine how many columns there are.
2156 // Pass 2 - Allocate the wxDbColInf array and fill in
2157 // the array with the column information.
2158 int pass;
2159 for (pass = 1; pass <= 2; pass++)
2160 {
2161 if (pass == 2)
2162 {
2163 if (noCols == 0) // Probably a bogus table name(s)
2164 break;
2165 // Allocate n wxDbColInf objects to hold the column information
2166 colInf = new wxDbColInf[noCols+1];
2167 if (!colInf)
2168 break;
2169 // Mark the end of the array
2170 wxStrcpy(colInf[noCols].tableName, wxT(""));
2171 wxStrcpy(colInf[noCols].colName, wxT(""));
2172 colInf[noCols].sqlDataType = 0;
2173 }
2174
2175 TableName = tableName;
2176 // Oracle table names are uppercase only, so force
2177 // the name to uppercase just in case programmer forgot to do this
2178 if (Dbms() == dbmsORACLE)
2179 TableName = TableName.Upper();
2180
2181 SQLFreeStmt(hstmt, SQL_CLOSE);
2182
2183 // MySQL and Access cannot accept a user name when looking up column names, so we
2184 // use the call below that leaves out the user name
2185 if (wxStrcmp(UserID.c_str(),wxT("")) &&
2186 Dbms() != dbmsMY_SQL &&
2187 Dbms() != dbmsACCESS)
2188 {
2189 retcode = SQLColumns(hstmt,
2190 NULL, 0, // All qualifiers
2191 (UCHAR *) UserID.c_str(), SQL_NTS, // Owner
2192 (UCHAR *) TableName.c_str(), SQL_NTS,
2193 NULL, 0); // All columns
2194 }
2195 else
2196 {
2197 retcode = SQLColumns(hstmt,
2198 NULL, 0, // All qualifiers
2199 NULL, 0, // Owner
2200 (UCHAR *) TableName.c_str(), SQL_NTS,
2201 NULL, 0); // All columns
2202 }
2203 if (retcode != SQL_SUCCESS)
2204 { // Error occured, abort
2205 DispAllErrors(henv, hdbc, hstmt);
2206 if (colInf)
2207 delete [] colInf;
2208 SQLFreeStmt(hstmt, SQL_CLOSE);
2209 if (numCols)
2210 *numCols = 0;
2211 return(0);
2212 }
2213
2214 while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS)
2215 {
2216 if (pass == 1) // First pass, just add up the number of columns
2217 noCols++;
2218 else // Pass 2; Fill in the array of structures
2219 {
2220 if (colNo < noCols) // Some extra error checking to prevent memory overwrites
2221 {
2222 // NOTE: Only the ODBC 1.x fields are retrieved
2223 GetData( 1, SQL_C_CHAR, (UCHAR*) colInf[colNo].catalog, 128+1, &cb);
2224 GetData( 2, SQL_C_CHAR, (UCHAR*) colInf[colNo].schema, 128+1, &cb);
2225 GetData( 3, SQL_C_CHAR, (UCHAR*) colInf[colNo].tableName, DB_MAX_TABLE_NAME_LEN+1, &cb);
2226 GetData( 4, SQL_C_CHAR, (UCHAR*) colInf[colNo].colName, DB_MAX_COLUMN_NAME_LEN+1, &cb);
2227 GetData( 5, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].sqlDataType, 0, &cb);
2228 GetData( 6, SQL_C_CHAR, (UCHAR*) colInf[colNo].typeName, 128+1, &cb);
2229 GetData( 7, SQL_C_SLONG, (UCHAR*) &colInf[colNo].columnSize, 0, &cb);
2230 GetData( 8, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].bufferLength, 0, &cb);
2231 GetData( 9, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].decimalDigits,0, &cb);
2232 GetData(10, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].numPrecRadix, 0, &cb);
2233 GetData(11, SQL_C_SSHORT, (UCHAR*) &colInf[colNo].nullable, 0, &cb);
2234 GetData(12, SQL_C_CHAR, (UCHAR*) colInf[colNo].remarks, 254+1, &cb);
2235 // Start Values for Primary/Foriegn Key (=No)
2236 colInf[colNo].PkCol = 0; // Primary key column 0=No; 1= First Key, 2 = Second Key etc.
2237 colInf[colNo].PkTableName[0] = 0; // Tablenames where Primary Key is used as a Foreign Key
2238 colInf[colNo].FkCol = 0; // Foreign key column 0=No; 1= First Key, 2 = Second Key etc.
2239 colInf[colNo].FkTableName[0] = 0; // Foreign key table name
2240
2241#ifdef _IODBC_
2242 // IODBC returns the columnSize in bufferLength.. (bug)
2243 colInf[colNo].columnSize = colInf[colNo].bufferLength;
2244#endif
2245
2246 // Determine the wxDb data type that is used to represent the native data type of this data source
2247 colInf[colNo].dbDataType = 0;
2248 // Get the intern datatype
2249 switch (colInf[colNo].sqlDataType)
2250 {
2251 case SQL_VARCHAR:
2252 case SQL_CHAR:
2253 colInf[colNo].dbDataType = DB_DATA_TYPE_VARCHAR;
2254 break;
2255
2256 case SQL_TINYINT:
2257 case SQL_SMALLINT:
2258 case SQL_INTEGER:
2259 colInf[colNo].dbDataType = DB_DATA_TYPE_INTEGER;
2260 break;
2261 case SQL_DOUBLE:
2262 case SQL_DECIMAL:
2263 case SQL_NUMERIC:
2264 case SQL_FLOAT:
2265 case SQL_REAL:
2266 colInf[colNo].dbDataType = DB_DATA_TYPE_FLOAT;
2267 break;
2268 case SQL_DATE:
2269 colInf[colNo].dbDataType = DB_DATA_TYPE_DATE;
2270 break;
2271#ifdef __WXDEBUG__
2272 default:
2273 wxString errMsg;
2274 errMsg.sprintf("SQL Data type %d currently not supported by wxWindows", colInf[colNo].sqlDataType);
2275 wxLogDebug(errMsg,wxT("ODBC DEBUG MESSAGE"));
2276#endif
2277 }
2278 colNo++;
2279 }
2280 }
2281 }
2282 if (retcode != SQL_NO_DATA_FOUND)
2283 { // Error occured, abort
2284 DispAllErrors(henv, hdbc, hstmt);
2285 if (colInf)
2286 delete [] colInf;
2287 SQLFreeStmt(hstmt, SQL_CLOSE);
2288 if (numCols)
2289 *numCols = 0;
2290 return(0);
2291 }
2292 }
2293
2294 SQLFreeStmt(hstmt, SQL_CLOSE);
2295
2296 // Store Primary and Foreign Keys
2297 GetKeyFields(tableName,colInf,noCols);
2298
2299 ///////////////////////////////////////////////////////////////////////////
2300 // Now sort the the columns in order to make them appear in the right order
2301 ///////////////////////////////////////////////////////////////////////////
2302
2303 // Build a generic SELECT statement which returns 0 rows
2304 wxString Stmt;
2305
2306 Stmt.sprintf("select * from %s where 0=1", tableName);
2307
2308 // Execute query
2309 if (SQLExecDirect(hstmt, (UCHAR FAR *) Stmt.c_str(), SQL_NTS) != SQL_SUCCESS)
2310 {
2311 DispAllErrors(henv, hdbc, hstmt);
2312 return NULL;
2313 }
2314
2315 // Get the number of result columns
2316 if (SQLNumResultCols (hstmt, &noCols) != SQL_SUCCESS)
2317 {
2318 DispAllErrors(henv, hdbc, hstmt);
2319 return NULL;
2320 }
2321
2322 if (noCols == 0) // Probably a bogus table name
2323 return NULL;
2324
2325 // Get the name
2326 int i;
2327 short colNum;
2328 UCHAR name[100];
2329 SWORD Sword;
2330 SDWORD Sdword;
2331 for (colNum = 0; colNum < noCols; colNum++)
2332 {
2333 if (SQLColAttributes(hstmt,colNum+1, SQL_COLUMN_NAME,
2334 name, sizeof(name),
2335 &Sword, &Sdword) != SQL_SUCCESS)
2336 {
2337 DispAllErrors(henv, hdbc, hstmt);
2338 return NULL;
2339 }
2340
2341 wxString Name1 = name;
2342 Name1 = Name1.Upper();
2343
2344 // Where is this name in the array ?
2345 for (i = colNum ; i < noCols ; i++)
2346 {
2347 wxString Name2 = colInf[i].colName;
2348 Name2 = Name2.Upper();
2349 if (Name2 == Name1)
2350 {
2351 if (colNum != i) // swap to sort
2352 {
2353 wxDbColInf tmpColInf = colInf[colNum];
2354 colInf[colNum] = colInf[i];
2355 colInf[i] = tmpColInf;
2356 }
2357 break;
2358 }
2359 }
2360 }
2361 SQLFreeStmt(hstmt, SQL_CLOSE);
2362
2363 ///////////////////////////////////////////////////////////////////////////
2364 // End sorting
2365 ///////////////////////////////////////////////////////////////////////////
2366
2367 if (numCols)
2368 *numCols = noCols;
2369 return colInf;
2370
2371} // wxDb::GetColumns()
2372
2373
2374#endif // #else OLD_GETCOLUMNS
2375
2376
2377/********** wxDb::GetColumnCount() **********/
2378int wxDb::GetColumnCount(char *tableName, const char *userID)
2379/*
2380 * Returns a count of how many columns are in a table.
2381 * If an error occurs in computing the number of columns
2382 * this function will return a -1 for the count
2383 *
2384 * userID is evaluated in the following manner:
2385 * userID == NULL ... UserID is ignored
2386 * userID == "" ... UserID set equal to 'this->uid'
2387 * userID != "" ... UserID set equal to 'userID'
2388 *
2389 * NOTE: ALL column bindings associated with this wxDb instance are unbound
2390 * by this function. This function should use its own wxDb instance
2391 * to avoid undesired unbinding of columns.
2392 */
2393{
2394 int noCols = 0;
2395
2396 RETCODE retcode;
2397
2398 wxString UserID;
2399 wxString TableName;
2400
2401 if (userID)
2402 {
2403 if (!wxStrlen(userID))
2404 UserID = uid;
2405 else
2406 UserID = userID;
2407 }
2408 else
2409 UserID = wxT("");
2410
2411 // dBase does not use user names, and some drivers fail if you try to pass one
2412 if (Dbms() == dbmsDBASE)
2413 UserID = wxT("");
2414
2415 // Oracle user names may only be in uppercase, so force
2416 // the name to uppercase
2417 if (Dbms() == dbmsORACLE)
2418 UserID = UserID.Upper();
2419
2420 {
2421 // Loop through each table name
2422 {
2423 TableName = tableName;
2424 // Oracle table names are uppercase only, so force
2425 // the name to uppercase just in case programmer forgot to do this
2426 if (Dbms() == dbmsORACLE)
2427 TableName = TableName.Upper();
2428
2429 SQLFreeStmt(hstmt, SQL_CLOSE);
2430
2431 // MySQL and Access cannot accept a user name when looking up column names, so we
2432 // use the call below that leaves out the user name
2433 if (wxStrcmp(UserID.c_str(),wxT("")) &&
2434 Dbms() != dbmsMY_SQL &&
2435 Dbms() != dbmsACCESS)
2436 {
2437 retcode = SQLColumns(hstmt,
2438 NULL, 0, // All qualifiers
2439 (UCHAR *) UserID.c_str(), SQL_NTS, // Owner
2440 (UCHAR *) TableName.c_str(), SQL_NTS,
2441 NULL, 0); // All columns
2442 }
2443 else
2444 {
2445 retcode = SQLColumns(hstmt,
2446 NULL, 0, // All qualifiers
2447 NULL, 0, // Owner
2448 (UCHAR *) TableName.c_str(), SQL_NTS,
2449 NULL, 0); // All columns
2450 }
2451 if (retcode != SQL_SUCCESS)
2452 { // Error occured, abort
2453 DispAllErrors(henv, hdbc, hstmt);
2454 SQLFreeStmt(hstmt, SQL_CLOSE);
2455 return(-1);
2456 }
2457
2458 // Count the columns
2459 while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS)
2460 noCols++;
2461
2462 if (retcode != SQL_NO_DATA_FOUND)
2463 { // Error occured, abort
2464 DispAllErrors(henv, hdbc, hstmt);
2465 SQLFreeStmt(hstmt, SQL_CLOSE);
2466 return(-1);
2467 }
2468 }
2469 }
2470
2471 SQLFreeStmt(hstmt, SQL_CLOSE);
2472 return noCols;
2473
2474} // wxDb::GetColumnCount()
2475
2476
2477/********** wxDb::GetCatalog() *******/
2478wxDbInf *wxDb::GetCatalog(char *userID)
2479/*
2480 * ---------------------------------------------------------------------
2481 * -- 19991203 : mj10777 : Create ------
2482 * -- : Creates a wxDbInf with Tables / Cols Array ------
2483 * -- : uses SQLTables and fills pTableInf; ------
2484 * -- : pColInf is set to NULL and numCols to 0; ------
2485 * -- : returns pDbInf (wxDbInf) ------
2486 * -- - if unsuccesfull (pDbInf == NULL) ------
2487 * -- : pColInf can be filled with GetColumns(..); ------
2488 * -- : numCols can be filled with GetColumnCount(..); ------
2489 * ---------------------------------------------------------------------
2490 *
2491 * userID is evaluated in the following manner:
2492 * userID == NULL ... UserID is ignored
2493 * userID == "" ... UserID set equal to 'this->uid'
2494 * userID != "" ... UserID set equal to 'userID'
2495 *
2496 * NOTE: ALL column bindings associated with this wxDb instance are unbound
2497 * by this function. This function should use its own wxDb instance
2498 * to avoid undesired unbinding of columns.
2499 */
2500{
2501 wxDbInf *pDbInf = NULL; // Array of catalog entries
2502 int noTab = 0; // Counter while filling table entries
2503 int pass;
2504 RETCODE retcode;
2505 SDWORD cb;
2506 wxString tblNameSave;
2507
2508 wxString UserID;
2509
2510 if (userID)
2511 {
2512 if (!wxStrlen(userID))
2513 UserID = uid;
2514 else
2515 UserID = userID;
2516 }
2517 else
2518 UserID = wxT("");
2519
2520 // dBase does not use user names, and some drivers fail if you try to pass one
2521 if (Dbms() == dbmsDBASE)
2522 UserID = wxT("");
2523
2524 // Oracle user names may only be in uppercase, so force
2525 // the name to uppercase
2526 if (Dbms() == dbmsORACLE)
2527 UserID = UserID.Upper();
2528
2529 //-------------------------------------------------------------
2530 pDbInf = new wxDbInf; // Create the Database Arrray
2531 //-------------------------------------------------------------
2532 // Table Information
2533 // Pass 1 - Determine how many Tables there are.
2534 // Pass 2 - Create the Table array and fill it
2535 // - Create the Cols array = NULL
2536 //-------------------------------------------------------------
2537
2538 for (pass = 1; pass <= 2; pass++)
2539 {
2540 SQLFreeStmt(hstmt, SQL_CLOSE); // Close if Open
2541 tblNameSave = wxT("");
2542
2543 if (wxStrcmp(UserID.c_str(),wxT("")) &&
2544 Dbms() != dbmsMY_SQL &&
2545 Dbms() != dbmsACCESS)
2546 {
2547 retcode = SQLTables(hstmt,
2548 NULL, 0, // All qualifiers
2549 (UCHAR *) UserID.c_str(), SQL_NTS, // User specified
2550 NULL, 0, // All tables
2551 NULL, 0); // All columns
2552 }
2553 else
2554 {
2555 retcode = SQLTables(hstmt,
2556 NULL, 0, // All qualifiers
2557 NULL, 0, // User specified
2558 NULL, 0, // All tables
2559 NULL, 0); // All columns
2560 }
2561
2562 if (retcode != SQL_SUCCESS)
2563 {
2564 DispAllErrors(henv, hdbc, hstmt);
2565 pDbInf = NULL;
2566 SQLFreeStmt(hstmt, SQL_CLOSE);
2567 return pDbInf;
2568 }
2569
2570 while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS) // Table Information
2571 {
2572 if (pass == 1) // First pass, just count the Tables
2573 {
2574 if (pDbInf->numTables == 0)
2575 {
2576 GetData( 1, SQL_C_CHAR, (UCHAR*) pDbInf->catalog, 128+1, &cb);
2577 GetData( 2, SQL_C_CHAR, (UCHAR*) pDbInf->schema, 128+1, &cb);
2578 }
2579 pDbInf->numTables++; // Counter for Tables
2580 } // if (pass == 1)
2581 if (pass == 2) // Create and fill the Table entries
2582 {
2583 if (pDbInf->pTableInf == NULL) // Has the Table Array been created
2584 { // no, then create the Array
2585 pDbInf->pTableInf = new wxDbTableInf[pDbInf->numTables];
2586 noTab = 0;
2587 } // if (pDbInf->pTableInf == NULL) // Has the Table Array been created
2588
2589 GetData( 3, SQL_C_CHAR, (UCHAR*) (pDbInf->pTableInf+noTab)->tableName, DB_MAX_TABLE_NAME_LEN+1, &cb);
2590 GetData( 4, SQL_C_CHAR, (UCHAR*) (pDbInf->pTableInf+noTab)->tableType, 30+1, &cb);
2591 GetData( 5, SQL_C_CHAR, (UCHAR*) (pDbInf->pTableInf+noTab)->tableRemarks, 254+1, &cb);
2592
2593 noTab++;
2594 } // if
2595 } // while
2596 } // for
2597 SQLFreeStmt(hstmt, SQL_CLOSE);
2598
2599 // Query how many columns are in each table
2600 for (noTab=0;noTab<pDbInf->numTables;noTab++)
2601 {
2602 (pDbInf->pTableInf+noTab)->numCols = GetColumnCount((pDbInf->pTableInf+noTab)->tableName,UserID);
2603 }
2604
2605 return pDbInf;
2606
2607} // wxDb::GetCatalog()
2608
2609
2610/********** wxDb::Catalog() **********/
2611bool wxDb::Catalog(const char *userID, const char *fileName)
2612/*
2613 * Creates the text file specified in 'filename' which will contain
2614 * a minimal data dictionary of all tables accessible by the user specified
2615 * in 'userID'
2616 *
2617 * userID is evaluated in the following manner:
2618 * userID == NULL ... UserID is ignored
2619 * userID == "" ... UserID set equal to 'this->uid'
2620 * userID != "" ... UserID set equal to 'userID'
2621 *
2622 * NOTE: ALL column bindings associated with this wxDb instance are unbound
2623 * by this function. This function should use its own wxDb instance
2624 * to avoid undesired unbinding of columns.
2625 */
2626{
2627 assert(fileName && wxStrlen(fileName));
2628
2629 RETCODE retcode;
2630 SDWORD cb;
2631 char tblName[DB_MAX_TABLE_NAME_LEN+1];
2632 wxString tblNameSave;
2633 char colName[DB_MAX_COLUMN_NAME_LEN+1];
2634 SWORD sqlDataType;
2635 char typeName[30+1];
2636 SWORD precision, length;
2637
2638 wxString UserID;
2639
2640 FILE *fp = fopen(fileName,"wt");
2641 if (fp == NULL)
2642 return(FALSE);
2643
2644 SQLFreeStmt(hstmt, SQL_CLOSE);
2645
2646 if (userID)
2647 {
2648 if (!wxStrlen(userID))
2649 UserID = uid;
2650 else
2651 UserID = userID;
2652 }
2653 else
2654 UserID = wxT("");
2655
2656 // dBase does not use user names, and some drivers fail if you try to pass one
2657 if (Dbms() == dbmsDBASE)
2658 UserID = wxT("");
2659
2660 // Oracle user names may only be in uppercase, so force
2661 // the name to uppercase
2662 if (Dbms() == dbmsORACLE)
2663 UserID = UserID.Upper();
2664
2665 if (wxStrcmp(UserID.c_str(),wxT("")) &&
2666 Dbms() != dbmsMY_SQL &&
2667 Dbms() != dbmsACCESS)
2668 {
2669 retcode = SQLColumns(hstmt,
2670 NULL, 0, // All qualifiers
2671 (UCHAR *) UserID.c_str(), SQL_NTS, // User specified
2672 NULL, 0, // All tables
2673 NULL, 0); // All columns
2674 }
2675 else
2676 {
2677 retcode = SQLColumns(hstmt,
2678 NULL, 0, // All qualifiers
2679 NULL, 0, // User specified
2680 NULL, 0, // All tables
2681 NULL, 0); // All columns
2682 }
2683 if (retcode != SQL_SUCCESS)
2684 {
2685 DispAllErrors(henv, hdbc, hstmt);
2686 fclose(fp);
2687 return(FALSE);
2688 }
2689
2690 wxString outStr;
2691 tblNameSave = wxT("");
2692 int cnt = 0;
2693
2694 while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS)
2695 {
2696 if (wxStrcmp(tblName,tblNameSave.c_str()))
2697 {
2698 if (cnt)
2699 fputs("\n", fp);
2700 fputs("================================ ", fp);
2701 fputs("================================ ", fp);
2702 fputs("===================== ", fp);
2703 fputs("========= ", fp);
2704 fputs("=========\n", fp);
2705 outStr.sprintf(wxT("%-32s %-32s %-21s %9s %9s\n"),
2706 wxT("TABLE NAME"), wxT("COLUMN NAME"), wxT("DATA TYPE"), wxT("PRECISION"), wxT("LENGTH"));
2707 fputs(outStr.c_str(), fp);
2708 fputs("================================ ", fp);
2709 fputs("================================ ", fp);
2710 fputs("===================== ", fp);
2711 fputs("========= ", fp);
2712 fputs("=========\n", fp);
2713 tblNameSave = tblName;
2714 }
2715
2716 GetData(3,SQL_C_CHAR, (UCHAR *)tblName, DB_MAX_TABLE_NAME_LEN+1, &cb);
2717 GetData(4,SQL_C_CHAR, (UCHAR *)colName, DB_MAX_COLUMN_NAME_LEN+1,&cb);
2718 GetData(5,SQL_C_SSHORT,(UCHAR *)&sqlDataType,0, &cb);
2719 GetData(6,SQL_C_CHAR, (UCHAR *)typeName, sizeof(typeName), &cb);
2720 GetData(7,SQL_C_SSHORT,(UCHAR *)&precision, 0, &cb);
2721 GetData(8,SQL_C_SSHORT,(UCHAR *)&length, 0, &cb);
2722
2723 outStr.sprintf("%-32s %-32s (%04d)%-15s %9d %9d\n",
2724 tblName, colName, sqlDataType, typeName, precision, length);
2725 if (fputs(outStr.c_str(), fp) == EOF)
2726 {
2727 SQLFreeStmt(hstmt, SQL_CLOSE);
2728 fclose(fp);
2729 return(FALSE);
2730 }
2731 cnt++;
2732 }
2733
2734 if (retcode != SQL_NO_DATA_FOUND)
2735 DispAllErrors(henv, hdbc, hstmt);
2736
2737 SQLFreeStmt(hstmt, SQL_CLOSE);
2738
2739 fclose(fp);
2740 return(retcode == SQL_NO_DATA_FOUND);
2741
2742} // wxDb::Catalog()
2743
2744
2745bool wxDb::TableExists(const char *tableName, const char *userID, const char *tablePath)
2746/*
2747 * Table name can refer to a table, view, alias or synonym. Returns true
2748 * if the object exists in the database. This function does not indicate
2749 * whether or not the user has privleges to query or perform other functions
2750 * on the table.
2751 *
2752 * userID is evaluated in the following manner:
2753 * userID == NULL ... UserID is ignored
2754 * userID == "" ... UserID set equal to 'this->uid'
2755 * userID != "" ... UserID set equal to 'userID'
2756 */
2757{
2758 wxString UserID;
2759 wxString TableName;
2760
2761 assert(tableName && wxStrlen(tableName));
2762
2763 if (Dbms() == dbmsDBASE)
2764 {
2765 wxString dbName;
2766 if (tablePath && wxStrlen(tablePath))
2767 dbName.sprintf("%s\\%s.dbf",tablePath,tableName);
2768 else
2769 dbName.sprintf("%s.dbf",tableName);
2770
2771 bool exists;
2772 exists = wxFileExists(dbName.c_str());
2773 return exists;
2774 }
2775
2776 if (userID)
2777 {
2778 if (!wxStrlen(userID))
2779 UserID = uid;
2780 else
2781 UserID = userID;
2782 }
2783 else
2784 UserID = "";
2785
2786 // Oracle user names may only be in uppercase, so force
2787 // the name to uppercase
2788 if (Dbms() == dbmsORACLE)
2789 UserID = UserID.Upper();
2790
2791 TableName = tableName;
2792 // Oracle table names are uppercase only, so force
2793 // the name to uppercase just in case programmer forgot to do this
2794 if (Dbms() == dbmsORACLE)
2795 TableName = TableName.Upper();
2796
2797 SQLFreeStmt(hstmt, SQL_CLOSE);
2798 RETCODE retcode;
2799
2800 // MySQL and Access cannot accept a user name when looking up table names, so we
2801 // use the call below that leaves out the user name
2802 if (wxStrcmp(UserID,"") &&
2803 Dbms() != dbmsMY_SQL &&
2804 Dbms() != dbmsACCESS)
2805 {
2806 retcode = SQLTables(hstmt,
2807 NULL, 0, // All qualifiers
2808 (UCHAR *) UserID.c_str(), SQL_NTS, // All owners
2809 (UCHAR FAR *)TableName.c_str(), SQL_NTS,
2810 NULL, 0); // All table types
2811 }
2812 else
2813 {
2814 retcode = SQLTables(hstmt,
2815 NULL, 0, // All qualifiers
2816 NULL, 0, // All owners
2817 (UCHAR FAR *)TableName.c_str(), SQL_NTS,
2818 NULL, 0); // All table types
2819 }
2820 if (retcode != SQL_SUCCESS)
2821 return(DispAllErrors(henv, hdbc, hstmt));
2822
2823 retcode = SQLFetch(hstmt);
2824 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
2825 {
2826 SQLFreeStmt(hstmt, SQL_CLOSE);
2827 return(DispAllErrors(henv, hdbc, hstmt));
2828 }
2829
2830 SQLFreeStmt(hstmt, SQL_CLOSE);
2831
2832 return(TRUE);
2833
2834} // wxDb::TableExists()
2835
2836
2837#if EXPERIMENTAL_WXDB_FUNCTIONS // will be added in 2.4
2838/********** wxDB::TablePrivileges() **********/
2839bool wxDB::TablePrivileges(const char *tableName, const char* priv,
2840 const char *userID, const char *tablePath)
2841{
2842 wxDbTablePrivilegeInfo result;
2843 SDWORD cbRetVal;
2844 RETCODE retcode;
2845
2846 //We probably need to be able to dynamically set this based on
2847 //the driver type, and state.
2848 char curRole[]="public";
2849
2850 //Prologue here similar to db::TableExists()
2851 wxString UserID;
2852 wxString TableName;
2853
2854 assert(tableName && wxStrlen(tableName));
2855
2856 if (userID)
2857 {
2858 if (!wxStrlen(userID))
2859 UserID = uid;
2860 else
2861 UserID = userID;
2862 }
2863 else
2864 UserID = "";
2865
2866 // Oracle user names may only be in uppercase, so force
2867 // the name to uppercase
2868 if (Dbms() == dbmsORACLE)
2869 UserID = UserID.Upper();
2870
2871 TableName = tableName;
2872 // Oracle table names are uppercase only, so force
2873 // the name to uppercase just in case programmer forgot to do this
2874 if (Dbms() == dbmsORACLE)
2875 TableName = TableName.Upper();
2876
2877 SQLFreeStmt(hstmt, SQL_CLOSE);
2878
2879 retcode = SQLTablePrivileges(hstmt,
2880 NULL, 0, // All qualifiers
2881 NULL, 0, // All owners
2882 (UCHAR FAR *)TableName.GetData(), SQL_NTS);
2883
2884#ifdef DBDEBUG_CONSOLE
2885 fprintf(stderr ,"SQLTablePrivileges() returned %i \n",retcode);
2886#endif
2887 retcode = SQLBindCol (hstmt, 1, SQL_C_CHAR , &result.tableQual, 128, &cbRetVal);
2888
2889 retcode = SQLBindCol (hstmt, 2, SQL_C_CHAR , &result.tableOwner, 128, &cbRetVal);
2890
2891 retcode = SQLBindCol (hstmt, 3, SQL_C_CHAR , &result.tableName, 128, &cbRetVal);
2892
2893 retcode = SQLBindCol (hstmt, 4, SQL_C_CHAR , &result.grantor, 128, &cbRetVal);
2894
2895 retcode = SQLBindCol (hstmt, 5, SQL_C_CHAR , &result.grantee, 128, &cbRetVal);
2896
2897 retcode = SQLBindCol (hstmt, 6, SQL_C_CHAR , &result.privilege, 128, &cbRetVal);
2898
2899 retcode = SQLBindCol (hstmt, 7, SQL_C_CHAR , &result.grantable, 3, &cbRetVal);
2900
2901 retcode = SQLFetch(hstmt);
2902 while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
2903 {
2904#ifdef DBDEBUG_CONSOLE
2905 fprintf(stderr,"Scanning %s privilege on table %s.%s granted by %s to %s\n",
2906 result.privilege,result.tabowner,result.tabname,
2907 result.grantor, result.grantee);
2908#endif
2909 if (UserID.IsSameAs(result.tableOwner,false) )
2910 return TRUE;
2911
2912 if (UserID.IsSameAs(result.grantee,false) &&
2913 !strcmp(result.privilege,priv))
2914 return TRUE;
2915
2916 if (!strcmp(result.grantee,curRole) &&
2917 !strcmp(result.privilege,priv))
2918 return TRUE;
2919
2920 retcode = SQLFetch(hstmt);
2921 }
2922
2923 return FALSE;
2924
2925} // wxDB::TablePrivileges
2926#endif
2927
2928
2929/********** wxDb::SetSqlLogging() **********/
2930bool wxDb::SetSqlLogging(wxDbSqlLogState state, const char *filename, bool append)
2931{
2932 assert(state == sqlLogON || state == sqlLogOFF);
2933 assert(state == sqlLogOFF || filename);
2934
2935 if (state == sqlLogON)
2936 {
2937 if (fpSqlLog == 0)
2938 {
2939 fpSqlLog = fopen(filename, (append ? "at" : "wt"));
2940 if (fpSqlLog == NULL)
2941 return(FALSE);
2942 }
2943 }
2944 else // sqlLogOFF
2945 {
2946 if (fpSqlLog)
2947 {
2948 if (fclose(fpSqlLog))
2949 return(FALSE);
2950 fpSqlLog = 0;
2951 }
2952 }
2953
2954 sqlLogState = state;
2955 return(TRUE);
2956
2957} // wxDb::SetSqlLogging()
2958
2959
2960/********** wxDb::WriteSqlLog() **********/
2961bool wxDb::WriteSqlLog(const wxChar *logMsg)
2962{
2963 assert(logMsg);
2964
2965 if (fpSqlLog == 0 || sqlLogState == sqlLogOFF)
2966 return(FALSE);
2967
2968 if (fputs("\n", fpSqlLog) == EOF) return(FALSE);
2969 if (fputs(logMsg, fpSqlLog) == EOF) return(FALSE);
2970 if (fputs("\n", fpSqlLog) == EOF) return(FALSE);
2971
2972 return(TRUE);
2973
2974} // wxDb::WriteSqlLog()
2975
2976
2977/********** wxDb::Dbms() **********/
2978wxDBMS wxDb::Dbms(void)
2979/*
2980 * Be aware that not all database engines use the exact same syntax, and not
2981 * every ODBC compliant database is compliant to the same level of compliancy.
2982 * Some manufacturers support the minimum Level 1 compliancy, and others up
2983 * through Level 3. Others support subsets of features for levels above 1.
2984 *
2985 * If you find an inconsistency between the wxDb class and a specific database
2986 * engine, and an identifier to this section, and special handle the database in
2987 * the area where behavior is non-conforming with the other databases.
2988 *
2989 *
2990 * NOTES ABOUT ISSUES SPECIFIC TO EACH DATABASE ENGINE
2991 * ---------------------------------------------------
2992 *
2993 * ORACLE
2994 * - Currently the only database supported by the class to support VIEWS
2995 *
2996 * DBASE
2997 * - Does not support the SQL_TIMESTAMP structure
2998 * - Supports only one cursor and one connect (apparently? with Microsoft driver only?)
2999 * - Does not automatically create the primary index if the 'keyField' param of SetColDef
3000 * is TRUE. The user must create ALL indexes from their program.
3001 * - Table names can only be 8 characters long
3002 * - Column names can only be 10 characters long
3003 *
3004 * SYBASE (all)
3005 * - To lock a record during QUERY functions, the reserved word 'HOLDLOCK' must be added
3006 * after every table name involved in the query/join if that tables matching record(s)
3007 * are to be locked
3008 * - Ignores the keywords 'FOR UPDATE'. Use the HOLDLOCK functionality described above
3009 *
3010 * SYBASE (Enterprise)
3011 * - If a column is part of the Primary Key, the column cannot be NULL
3012 * - Maximum row size is somewhere in the neighborhood of 1920 bytes
3013 *
3014 * MY_SQL
3015 * - If a column is part of the Primary Key, the column cannot be NULL
3016 * - Cannot support selecting for update [::CanSelectForUpdate()]. Always returns FALSE
3017 * - Columns that are part of primary or secondary keys must be defined as being NOT NULL
3018 * when they are created. Some code is added in ::CreateIndex to try to adjust the
3019 * column definition if it is not defined correctly, but it is experimental
3020 * - Does not support sub-queries in SQL statements
3021 *
3022 * POSTGRES
3023 * - Does not support the keywords 'ASC' or 'DESC' as of release v6.5.0
3024 * - Does not support sub-queries in SQL statements
3025 *
3026 */
3027{
3028 wxChar baseName[25+1];
3029 wxStrncpy(baseName,dbInf.dbmsName,25);
3030 baseName[25] = 0;
3031
3032 // BJO 20000428 : add support for Virtuoso
3033 if (!wxStricmp(dbInf.dbmsName,"OpenLink Virtuoso VDBMS"))
3034 return(dbmsVIRTUOSO);
3035
3036
3037 if (!wxStricmp(dbInf.dbmsName,"Adaptive Server Anywhere"))
3038 return(dbmsSYBASE_ASA);
3039
3040 // BJO 20000427 : The "SQL Server" string is also returned by SQLServer when
3041 // connected through an OpenLink driver.
3042 // Is it also returned by Sybase Adapatitve server?
3043 // OpenLink driver name is OLOD3032.DLL for msw and oplodbc.so for unix
3044 if (!wxStricmp(dbInf.dbmsName,"SQL Server"))
3045 {
3046 if (!wxStrncmp(dbInf.driverName, "oplodbc", 7) ||
3047 !wxStrncmp(dbInf.driverName, "OLOD", 4))
3048 return dbmsMS_SQL_SERVER; else return dbmsSYBASE_ASE;
3049 }
3050
3051 if (!wxStricmp(dbInf.dbmsName,"Microsoft SQL Server"))
3052 return(dbmsMS_SQL_SERVER);
3053 if (!wxStricmp(dbInf.dbmsName,"MySQL"))
3054 return(dbmsMY_SQL);
3055 if (!wxStricmp(dbInf.dbmsName,"PostgreSQL")) // v6.5.0
3056 return(dbmsPOSTGRES);
3057
3058 baseName[8] = 0;
3059 if (!wxStricmp(baseName,"Informix"))
3060 return(dbmsINFORMIX);
3061
3062 baseName[6] = 0;
3063 if (!wxStricmp(baseName,"Oracle"))
3064 return(dbmsORACLE);
3065 if (!wxStricmp(dbInf.dbmsName,"ACCESS"))
3066 return(dbmsACCESS);
3067 if (!wxStricmp(dbInf.dbmsName,"MySQL"))
3068 return(dbmsMY_SQL);
3069 if (!wxStricmp(baseName,"Sybase"))
3070 return(dbmsSYBASE_ASE);
3071
3072 baseName[5] = 0;
3073 if (!wxStricmp(baseName,"DBASE"))
3074 return(dbmsDBASE);
3075
3076 return(dbmsUNIDENTIFIED);
3077
3078} // wxDb::Dbms()
3079
3080
3081/********** wxDbGetConnection() **********/
3082wxDb WXDLLEXPORT *wxDbGetConnection(wxDbConnectInf *pDbConfig, bool FwdOnlyCursors)
3083{
3084 wxDbList *pList;
3085
3086 // Scan the linked list searching for an available database connection
3087 // that's already been opened but is currently not in use.
3088 for (pList = PtrBegDbList; pList; pList = pList->PtrNext)
3089 {
3090 // The database connection must be for the same datasource
3091 // name and must currently not be in use.
3092 if (pList->Free &&
3093 (pList->PtrDb->FwdOnlyCursors() == FwdOnlyCursors) &&
3094 (!wxStrcmp(pDbConfig->Dsn, pList->Dsn))) // Found a free connection
3095 {
3096 pList->Free = FALSE;
3097 return(pList->PtrDb);
3098 }
3099 }
3100
3101 // No available connections. A new connection must be made and
3102 // appended to the end of the linked list.
3103 if (PtrBegDbList)
3104 {
3105 // Find the end of the list
3106 for (pList = PtrBegDbList; pList->PtrNext; pList = pList->PtrNext);
3107 // Append a new list item
3108 pList->PtrNext = new wxDbList;
3109 pList->PtrNext->PtrPrev = pList;
3110 pList = pList->PtrNext;
3111 }
3112 else // Empty list
3113 {
3114 // Create the first node on the list
3115 pList = PtrBegDbList = new wxDbList;
3116 pList->PtrPrev = 0;
3117 }
3118
3119 // Initialize new node in the linked list
3120 pList->PtrNext = 0;
3121 pList->Free = FALSE;
3122 wxStrcpy(pList->Dsn, pDbConfig->Dsn);
3123 pList->PtrDb = new wxDb(pDbConfig->Henv,FwdOnlyCursors);
3124
3125 // Connect to the datasource
3126 if (pList->PtrDb->Open(pDbConfig->Dsn, pDbConfig->Uid, pDbConfig->AuthStr))
3127 {
3128 pList->PtrDb->SetSqlLogging(SQLLOGstate,SQLLOGfn,TRUE);
3129 return(pList->PtrDb);
3130 }
3131 else // Unable to connect, destroy list item
3132 {
3133 if (pList->PtrPrev)
3134 pList->PtrPrev->PtrNext = 0;
3135 else
3136 PtrBegDbList = 0; // Empty list again
3137 pList->PtrDb->CommitTrans(); // Commit any open transactions on wxDb object
3138 pList->PtrDb->Close(); // Close the wxDb object
3139 delete pList->PtrDb; // Deletes the wxDb object
3140 delete pList; // Deletes the linked list object
3141 return(0);
3142 }
3143
3144} // wxDbGetConnection()
3145
3146
3147/********** wxDbFreeConnection() **********/
3148bool WXDLLEXPORT wxDbFreeConnection(wxDb *pDb)
3149{
3150 wxDbList *pList;
3151
3152 // Scan the linked list searching for the database connection
3153 for (pList = PtrBegDbList; pList; pList = pList->PtrNext)
3154 {
3155 if (pList->PtrDb == pDb) // Found it, now free it!!!
3156 return (pList->Free = TRUE);
3157 }
3158
3159 // Never found the database object, return failure
3160 return(FALSE);
3161
3162} // wxDbFreeConnection()
3163
3164
3165/********** wxDbCloseConnections() **********/
3166void WXDLLEXPORT wxDbCloseConnections(void)
3167{
3168 wxDbList *pList, *pNext;
3169
3170 // Traverse the linked list closing database connections and freeing memory as I go.
3171 for (pList = PtrBegDbList; pList; pList = pNext)
3172 {
3173 pNext = pList->PtrNext; // Save the pointer to next
3174 pList->PtrDb->CommitTrans(); // Commit any open transactions on wxDb object
3175 pList->PtrDb->Close(); // Close the wxDb object
3176 delete pList->PtrDb; // Deletes the wxDb object
3177 delete pList; // Deletes the linked list object
3178 }
3179
3180 // Mark the list as empty
3181 PtrBegDbList = 0;
3182
3183} // wxDbCloseConnections()
3184
3185
3186/********** wxDbNumberConnectionsInUse() **********/
3187int WXDLLEXPORT wxDbConnectionsInUse(void)
3188{
3189 wxDbList *pList;
3190 int cnt = 0;
3191
3192 // Scan the linked list counting db connections that are currently in use
3193 for (pList = PtrBegDbList; pList; pList = pList->PtrNext)
3194 {
3195 if (pList->Free == FALSE)
3196 cnt++;
3197 }
3198
3199 return(cnt);
3200
3201} // wxDbConnectionsInUse()
3202
3203
3204/********** wxDbSqlLog() **********/
3205bool wxDbSqlLog(wxDbSqlLogState state, const wxChar *filename)
3206{
3207 bool append = FALSE;
3208 wxDbList *pList;
3209
3210 for (pList = PtrBegDbList; pList; pList = pList->PtrNext)
3211 {
3212 if (!pList->PtrDb->SetSqlLogging(state,filename,append))
3213 return(FALSE);
3214 append = TRUE;
3215 }
3216
3217 SQLLOGstate = state;
3218 SQLLOGfn = filename;
3219
3220 return(TRUE);
3221
3222} // wxDbSqlLog()
3223
3224
3225#if EXPERIMENTAL_WXDB_FUNCTIONS // will be added in 2.4
3226/********** wxDbCreateDataSource() **********/
3227int wxDbCreateDataSource(const char *driverName, const char *dsn, const char *description,
3228 bool sysDSN, const char *defDir, wxWindow *parent)
3229/*
3230 * !!!! ONLY FUNCTIONAL UNDER MSW with VC6 !!!!
3231 * Very rudimentary creation of an ODBC data source.
3232 */
3233{
3234 int result = FALSE;
3235
3236#ifdef __WXMSW__
3237 int dsnLocation;
3238 wxString setupStr;
3239
3240 if (sysDSN)
3241 dsnLocation = ODBC_ADD_SYS_DSN;
3242 else
3243 dsnLocation = ODBC_ADD_DSN;
3244
3245 // NOTE: The decimal 2 is an invalid character in all keyword pairs
3246 // so that is why I used it, as wxString does not deal well with
3247 // embedded nulls in strings
3248 setupStr.sprintf("DSN=%s%cDescription=%s%cDefaultDir=%s%c",dsn,2,description,2,defDir,2);
3249
3250 // Replace the separator from above with the '\0' seperator needed
3251 // by the SQLConfigDataSource() function
3252 int k;
3253 do
3254 {
3255 k = setupStr.Find((wxChar)2,TRUE);
3256 if (k != wxNOT_FOUND)
3257 setupStr[(UINT)k] = '\0';
3258 }
3259 while (k != wxNOT_FOUND);
3260
3261 result = SQLConfigDataSource((HWND)parent->GetHWND(), dsnLocation,
3262 driverName, setupStr.c_str());
3263
3264 if (!result)
3265 {
3266 // check for errors caused by ConfigDSN based functions
3267 DWORD retcode = 0;
3268 WORD cb;
3269 wxChar errMsg[500+1];
3270 errMsg[0] = '\0';
3271
3272 SQLInstallerError(1,&retcode,errMsg,500,&cb);
3273 if (retcode)
3274 {
3275 if (!silent)
3276 {
3277#ifdef DBDEBUG_CONSOLE
3278 // When run in console mode, use standard out to display errors.
3279 cout << errMsg << endl;
3280 cout << "Press any key to continue..." << endl;
3281 getchar();
3282#endif // DBDEBUG_CONSOLE
3283
3284#ifdef __WXDEBUG__
3285 wxLogDebug(errMsg,wxT("ODBC DEBUG MESSAGE"));
3286#endif // __WXDEBUG__
3287 }
3288 }
3289 }
3290 else
3291 result = TRUE;
3292
3293#else // using iODBC, so this function is not supported
3294#ifdef __WXDEBUG__
3295 wxLogDebug("wxDbCreateDataSource() not available except under MSW","DEBUG MESSAGE");
3296#endif
3297#endif // __WXMSW__
3298
3299 return result;
3300
3301} // wxDbCreateDataSource()
3302#endif
3303
3304
3305/********** wxDbGetDataSource() **********/
3306bool wxDbGetDataSource(HENV henv, char *Dsn, SWORD DsnMax, char *DsDesc, SWORD DsDescMax,
3307 UWORD direction)
3308/*
3309 * Dsn and DsDesc will contain the data source name and data source
3310 * description upon return
3311 */
3312{
3313 SWORD cb1,cb2;
3314
3315 if (SQLDataSources(henv, direction, (UCHAR FAR *) Dsn, DsnMax, &cb1,
3316 (UCHAR FAR *) DsDesc, DsDescMax, &cb2) == SQL_SUCCESS)
3317 return(TRUE);
3318 else
3319 return(FALSE);
3320
3321} // wxDbGetDataSource()
3322
3323
3324// Change this to 0 to remove use of all deprecated functions
3325#if wxODBC_BACKWARD_COMPATABILITY
3326/********************************************************************
3327 ********************************************************************
3328 *
3329 * The following functions are all DEPRECATED and are included for
3330 * backward compatability reasons only
3331 *
3332 ********************************************************************
3333 ********************************************************************/
3334bool SqlLog(sqlLog state, const wxChar *filename)
3335{
3336 return wxDbSqlLog((enum wxDbSqlLogState)state, filename);
3337}
3338/***** DEPRECATED: use wxGetDataSource() *****/
3339bool GetDataSource(HENV henv, char *Dsn, SWORD DsnMax, char *DsDesc, SWORD DsDescMax,
3340 UWORD direction)
3341{
3342 return wxDbGetDataSource(henv, Dsn, DsnMax, DsDesc, DsDescMax, direction);
3343}
3344/***** DEPRECATED: use wxDbGetConnection() *****/
3345wxDb WXDLLEXPORT *GetDbConnection(DbStuff *pDbStuff, bool FwdOnlyCursors)
3346{
3347 return wxDbGetConnection((wxDbConnectInf *)pDbStuff, FwdOnlyCursors);
3348}
3349/***** DEPRECATED: use wxDbFreeConnection() *****/
3350bool WXDLLEXPORT FreeDbConnection(wxDb *pDb)
3351{
3352 return wxDbFreeConnection(pDb);
3353}
3354/***** DEPRECATED: use wxDbCloseConnections() *****/
3355void WXDLLEXPORT CloseDbConnections(void)
3356{
3357 wxDbCloseConnections();
3358}
3359/***** DEPRECATED: use wxDbConnectionsInUse() *****/
3360int WXDLLEXPORT NumberDbConnectionsInUse(void)
3361{
3362 return wxDbConnectionsInUse();
3363}
3364#endif
3365
3366
3367#endif
3368 // wxUSE_ODBC
wxWidgets (Impactor)