]> git.saurik.com Git - wxWidgets.git/blame - src/common/dbtable.cpp
fix for wxSplitPath(foo/.filename)
[wxWidgets.git] / src / common / dbtable.cpp
CommitLineData
108106cf 1///////////////////////////////////////////////////////////////////////////////
1fc5dd6f 2// Name: dbtable.cpp
108106cf
JS
3// Purpose: Implementation of the wxTable class.
4// Author: Doug Card
67e9aaa3 5// Modified by: George Tasker
89894079
VZ
6// Mods: April 1999
7// -Dynamic cursor support - Only one predefined cursor, as many others as
8// you need may be created on demand
9// -Reduced number of active cursors significantly
10// -Query-Only wxTable objects
108106cf
JS
11// Created: 9.96
12// RCS-ID: $Id$
13// Copyright: (c) 1996 Remstar International, Inc.
14// Licence: wxWindows licence, plus:
1e92909e 15// Notice: This class library and its intellectual design are free of charge for use,
108106cf
JS
16// modification, enhancement, debugging under the following conditions:
17// 1) These classes may only be used as part of the implementation of a
18// wxWindows-based application
19// 2) All enhancements and bug fixes are to be submitted back to the wxWindows
20// user groups free of all charges for use with the wxWindows library.
21// 3) These classes may not be distributed as part of any other class library,
22// DLL, text (written or electronic), other than a complete distribution of
23// the wxWindows GUI development toolkit.
24///////////////////////////////////////////////////////////////////////////////
25
26/*
27// SYNOPSIS START
28// SYNOPSIS STOP
29*/
30
a2115c88
GT
31// Use this line for wxWindows v1.x
32//#include "wx_ver.h"
33// Use this line for wxWindows v2.x
34#include "wx/version.h"
35#include "wx/wxprec.h"
36
37#if wxMAJOR_VERSION == 2
89894079
VZ
38# ifdef __GNUG__
39# pragma implementation "dbtable.h"
40# endif
108106cf 41#endif
108106cf 42
a2115c88 43#ifdef DBDEBUG_CONSOLE
1e92909e 44 #include <iostream.h>
a2115c88 45#endif
108106cf
JS
46
47#ifdef __BORLANDC__
1e92909e 48 #pragma hdrstop
108106cf
JS
49#endif //__BORLANDC__
50
a2115c88 51#if wxMAJOR_VERSION == 2
1e92909e
GT
52 #ifndef WX_PRECOMP
53 #include "wx/string.h"
54 #include "wx/object.h"
55 #include "wx/list.h"
56 #include "wx/utils.h"
57 #include "wx/msgdlg.h"
89894079
VZ
58 #endif
59 #include "wx/filefn.h"
a2115c88 60#endif
108106cf 61
a2115c88 62#if wxMAJOR_VERSION == 1
89894079
VZ
63# if defined(wx_msw) || defined(wx_x)
64# ifdef WX_PRECOMP
65# include "wx_prec.h"
66# else
67# include "wx.h"
68# endif
69# endif
70# define wxUSE_ODBC 1
a2115c88 71#endif
108106cf 72
a2115c88 73#if wxUSE_ODBC
108106cf
JS
74
75#include <stdio.h>
76#include <stdlib.h>
77#include <string.h>
1fc5dd6f 78#include <assert.h>
67e9aaa3 79
a2115c88 80#if wxMAJOR_VERSION == 1
89894079 81 #include "table.h"
a2115c88 82#elif wxMAJOR_VERSION == 2
89894079 83 #include "wx/dbtable.h"
a2115c88 84#endif
108106cf 85
1fc5dd6f 86#ifdef __UNIX__
108106cf
JS
87// The HPUX preprocessor lines below were commented out on 8/20/97
88// because macros.h currently redefines DEBUG and is unneeded.
89// # ifdef HPUX
90// # include <macros.h>
91// # endif
1fc5dd6f 92# ifdef LINUX
108106cf
JS
93# include <sys/minmax.h>
94# endif
95#endif
96
a2115c88
GT
97ULONG lastTableID = 0;
98
99
e041ce57 100#ifdef __WXDEBUG__
89894079 101 wxList TablesInUse;
a2115c88
GT
102#endif
103
104
108106cf 105/********** wxTable::wxTable() **********/
a2115c88 106wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols,
89894079 107 const char *qryTblName, bool qryOnly, const char *tblPath)
108106cf 108{
89894079
VZ
109 pDb = pwxDB; // Pointer to the wxDB object
110 henv = 0;
111 hdbc = 0;
112 hstmt = 0;
113 hstmtDefault = 0; // Initialized below
114 hstmtCount = 0; // Initialized first time it is needed
115 hstmtInsert = 0;
116 hstmtDelete = 0;
117 hstmtUpdate = 0;
118 hstmtInternal = 0;
119 colDefs = 0;
120 tableID = 0;
121 noCols = nCols; // No. of cols in the table
122 where = 0; // Where clause
123 orderBy = 0; // Order By clause
124 from = 0; // From clause
125 selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase
126 queryOnly = qryOnly;
127
128 assert (tblName);
129
130 wxStrcpy(tableName, tblName); // Table Name
131 if (tblPath)
132 wxStrcpy(tablePath, tblPath); // Table Path - used for dBase files
2f74ed28
GT
133 else
134 tablePath[0]=0;
89894079
VZ
135
136 if (qryTblName) // Name of the table/view to query
137 wxStrcpy(queryTableName, qryTblName);
138 else
139 wxStrcpy(queryTableName, tblName);
140
141 if (!pDb)
142 return;
143
144 pDb->nTables++;
145
1e92909e 146 wxString s;
89894079 147 tableID = ++lastTableID;
1e92909e 148 s.sprintf("wxTable constructor (%-20s) tableID:[%6lu] pDb:[%p]", tblName,tableID,pDb);
a2115c88 149
e041ce57 150#ifdef __WXDEBUG__
89894079 151 CstructTablesInUse *tableInUse;
1e92909e 152 tableInUse = new CstructTablesInUse();
89894079 153 tableInUse->tableName = tblName;
1e92909e
GT
154 tableInUse->tableID = tableID;
155 tableInUse->pDb = pDb;
89894079 156 TablesInUse.Append(tableInUse);
a2115c88
GT
157#endif
158
1e92909e 159 pDb->WriteSqlLog(s.GetData());
89894079
VZ
160
161 // Grab the HENV and HDBC from the wxDB object
162 henv = pDb->henv;
163 hdbc = pDb->hdbc;
164
165 // Allocate space for column definitions
166 if (noCols)
167 colDefs = new wxColDef[noCols]; // Points to the first column defintion
168
169 // Allocate statement handles for the table
170 if (!queryOnly)
171 {
172 // Allocate a separate statement handle for performing inserts
173 if (SQLAllocStmt(hdbc, &hstmtInsert) != SQL_SUCCESS)
174 pDb->DispAllErrors(henv, hdbc);
175 // Allocate a separate statement handle for performing deletes
176 if (SQLAllocStmt(hdbc, &hstmtDelete) != SQL_SUCCESS)
177 pDb->DispAllErrors(henv, hdbc);
178 // Allocate a separate statement handle for performing updates
179 if (SQLAllocStmt(hdbc, &hstmtUpdate) != SQL_SUCCESS)
180 pDb->DispAllErrors(henv, hdbc);
181 }
182 // Allocate a separate statement handle for internal use
183 if (SQLAllocStmt(hdbc, &hstmtInternal) != SQL_SUCCESS)
184 pDb->DispAllErrors(henv, hdbc);
185
186 // Set the cursor type for the statement handles
187 cursorType = SQL_CURSOR_STATIC;
188 if (SQLSetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
189 {
190 // Check to see if cursor type is supported
191 pDb->GetNextError(henv, hdbc, hstmtInternal);
192 if (! wxStrcmp(pDb->sqlState, "01S02")) // Option Value Changed
193 {
194 // Datasource does not support static cursors. Driver
195 // will substitute a cursor type. Call SQLGetStmtOption()
196 // to determine which cursor type was selected.
197 if (SQLGetStmtOption(hstmtInternal, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS)
198 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
a2115c88 199#ifdef DBDEBUG_CONSOLE
89894079
VZ
200 cout << "Static cursor changed to: ";
201 switch(cursorType)
202 {
203 case SQL_CURSOR_FORWARD_ONLY:
204 cout << "Forward Only"; break;
205 case SQL_CURSOR_STATIC:
1e92909e 206 cout << "Static"; break;
89894079
VZ
207 case SQL_CURSOR_KEYSET_DRIVEN:
208 cout << "Keyset Driven"; break;
209 case SQL_CURSOR_DYNAMIC:
210 cout << "Dynamic"; break;
211 }
212 cout << endl << endl;
108106cf 213#endif
89894079
VZ
214 }
215 else
216 {
217 pDb->DispNextError();
218 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
219 }
220 }
a2115c88 221#ifdef DBDEBUG_CONSOLE
89894079
VZ
222 else
223 cout << "Cursor Type set to STATIC" << endl << endl;
108106cf
JS
224#endif
225
89894079
VZ
226 if (!queryOnly)
227 {
228 // Set the cursor type for the INSERT statement handle
229 if (SQLSetStmtOption(hstmtInsert, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
230 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
231 // Set the cursor type for the DELETE statement handle
232 if (SQLSetStmtOption(hstmtDelete, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
233 pDb->DispAllErrors(henv, hdbc, hstmtDelete);
234 // Set the cursor type for the UPDATE statement handle
235 if (SQLSetStmtOption(hstmtUpdate, SQL_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY) != SQL_SUCCESS)
236 pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
237 }
238
239 // Make the default cursor the active cursor
240 hstmtDefault = NewCursor(FALSE,FALSE);
241 assert(hstmtDefault);
242 hstmt = *hstmtDefault;
108106cf
JS
243
244} // wxTable::wxTable()
245
67e9aaa3 246
108106cf
JS
247/********** wxTable::~wxTable() **********/
248wxTable::~wxTable()
249{
1e92909e 250 wxString s;
89894079
VZ
251 if (pDb)
252 {
1e92909e
GT
253 s.sprintf("wxTable destructor (%-20s) tableID:[%6lu] pDb:[%p]", tableName,tableID,pDb);
254 pDb->WriteSqlLog(s.GetData());
89894079 255 }
a2115c88 256
e041ce57 257#ifdef __WXDEBUG__
89894079
VZ
258 if (tableID)
259 {
a4086039 260 TablesInUse.DeleteContents(TRUE);
89894079 261 bool found = FALSE;
1e92909e 262
89894079
VZ
263 wxNode *pNode;
264 pNode = TablesInUse.First();
265 while (pNode && !found)
266 {
267 if (((CstructTablesInUse *)pNode->Data())->tableID == tableID)
268 {
269 found = TRUE;
270 if (!TablesInUse.DeleteNode(pNode))
1e92909e 271 wxMessageBox (s.GetData(),"Unable to delete node!");
89894079
VZ
272 }
273 else
274 pNode = pNode->Next();
275 }
276 if (!found)
277 {
1e92909e
GT
278 wxString msg;
279 msg.sprintf("Unable to find the tableID in the linked\nlist of tables in use.\n\n%s",s.GetData());
280 wxMessageBox (msg.GetData(),"NOTICE...");
89894079
VZ
281 }
282 }
a2115c88 283#endif
e041ce57 284
89894079
VZ
285 // Decrement the wxDB table count
286 if (pDb)
287 pDb->nTables--;
288
289 // Delete memory allocated for column definitions
290 if (colDefs)
291 delete [] colDefs;
292
293 // Free statement handles
294 if (!queryOnly)
295 {
296 if (hstmtInsert)
297 if (SQLFreeStmt(hstmtInsert, SQL_DROP) != SQL_SUCCESS)
298 pDb->DispAllErrors(henv, hdbc);
299 if (hstmtDelete)
300 if (SQLFreeStmt(hstmtDelete, SQL_DROP) != SQL_SUCCESS)
301 pDb->DispAllErrors(henv, hdbc);
302 if (hstmtUpdate)
303 if (SQLFreeStmt(hstmtUpdate, SQL_DROP) != SQL_SUCCESS)
304 pDb->DispAllErrors(henv, hdbc);
305 }
306 if (hstmtInternal)
307 if (SQLFreeStmt(hstmtInternal, SQL_DROP) != SQL_SUCCESS)
308 pDb->DispAllErrors(henv, hdbc);
309
310 // Delete dynamically allocated cursors
311 if (hstmtDefault)
312 DeleteCursor(hstmtDefault);
313 if (hstmtCount)
314 DeleteCursor(hstmtCount);
108106cf
JS
315
316} // wxTable::~wxTable()
317
67e9aaa3
GT
318
319
6919c53f
GT
320/***************************** PRIVATE FUNCTIONS *****************************/
321
67e9aaa3
GT
322
323
6919c53f
GT
324/********** wxTable::bindInsertParams() **********/
325bool wxTable::bindInsertParams(void)
326{
89894079
VZ
327 assert(!queryOnly);
328 if (queryOnly)
329 return(FALSE);
330
331 SWORD fSqlType = 0;
332 UDWORD precision = 0;
333 SWORD scale = 0;
334
335 // Bind each column (that can be inserted) of the table to a parameter marker
336 int i,colNo;
337 for (i = 0, colNo = 1; i < noCols; i++)
338 {
339 if (! colDefs[i].InsertAllowed)
340 continue;
341 switch(colDefs[i].DbDataType)
342 {
343 case DB_DATA_TYPE_VARCHAR:
344 fSqlType = pDb->typeInfVarchar.FsqlType;
345 precision = colDefs[i].SzDataObj;
346 scale = 0;
347 colDefs[i].CbValue = SQL_NTS;
348 break;
349 case DB_DATA_TYPE_INTEGER:
350 fSqlType = pDb->typeInfInteger.FsqlType;
351 precision = pDb->typeInfInteger.Precision;
352 scale = 0;
353 colDefs[i].CbValue = 0;
354 break;
355 case DB_DATA_TYPE_FLOAT:
356 fSqlType = pDb->typeInfFloat.FsqlType;
357 precision = pDb->typeInfFloat.Precision;
358 scale = pDb->typeInfFloat.MaximumScale;
359 // SQL Sybase Anywhere v5.5 returned a negative number for the
360 // MaxScale. This caused ODBC to kick out an error on ibscale.
361 // I check for this here and set the scale = precision.
362 //if (scale < 0)
363 // scale = (short) precision;
364 colDefs[i].CbValue = 0;
365 break;
366 case DB_DATA_TYPE_DATE:
367 fSqlType = pDb->typeInfDate.FsqlType;
368 precision = pDb->typeInfDate.Precision;
369 scale = 0;
370 colDefs[i].CbValue = 0;
371 break;
372 }
373 // Null values
374 if (colDefs[i].Null)
375 {
376 colDefs[i].CbValue = SQL_NULL_DATA;
377 colDefs[i].Null = FALSE;
378 }
379 if (SQLBindParameter(hstmtInsert, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
380 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
381 precision+1,&colDefs[i].CbValue) != SQL_SUCCESS)
382 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
383 }
384
385 // Completed successfully
386 return(TRUE);
6919c53f
GT
387
388} // wxTable::bindInsertParams()
389
67e9aaa3 390
6919c53f
GT
391/********** wxTable::bindUpdateParams() **********/
392bool wxTable::bindUpdateParams(void)
393{
89894079
VZ
394 assert(!queryOnly);
395 if (queryOnly)
396 return(FALSE);
397
398 SWORD fSqlType = 0;
399 UDWORD precision = 0;
400 SWORD scale = 0;
401
402 // Bind each UPDATEABLE column of the table to a parameter marker
403 int i,colNo;
404 for (i = 0, colNo = 1; i < noCols; i++)
405 {
406 if (! colDefs[i].Updateable)
407 continue;
408 switch(colDefs[i].DbDataType)
409 {
410 case DB_DATA_TYPE_VARCHAR:
411 fSqlType = pDb->typeInfVarchar.FsqlType;
412 precision = colDefs[i].SzDataObj;
413 scale = 0;
414 colDefs[i].CbValue = SQL_NTS;
415 break;
416 case DB_DATA_TYPE_INTEGER:
417 fSqlType = pDb->typeInfInteger.FsqlType;
418 precision = pDb->typeInfInteger.Precision;
419 scale = 0;
420 colDefs[i].CbValue = 0;
421 break;
422 case DB_DATA_TYPE_FLOAT:
423 fSqlType = pDb->typeInfFloat.FsqlType;
424 precision = pDb->typeInfFloat.Precision;
425 scale = pDb->typeInfFloat.MaximumScale;
426 // SQL Sybase Anywhere v5.5 returned a negative number for the
427 // MaxScale. This caused ODBC to kick out an error on ibscale.
428 // I check for this here and set the scale = precision.
429 //if (scale < 0)
430 // scale = (short) precision;
431 colDefs[i].CbValue = 0;
432 break;
433 case DB_DATA_TYPE_DATE:
434 fSqlType = pDb->typeInfDate.FsqlType;
435 precision = pDb->typeInfDate.Precision;
436 scale = 0;
437 colDefs[i].CbValue = 0;
438 break;
439 }
440 if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
1e92909e
GT
441 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
442 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
89894079
VZ
443 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
444 }
445
446 // Completed successfully
447 return(TRUE);
6919c53f
GT
448
449} // wxTable::bindUpdateParams()
450
67e9aaa3 451
6919c53f
GT
452/********** wxTable::bindCols() **********/
453bool wxTable::bindCols(HSTMT cursor)
454{
89894079
VZ
455 static SDWORD cb;
456
457 // Bind each column of the table to a memory address for fetching data
458 int i;
459 for (i = 0; i < noCols; i++)
460 {
461 if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
1e92909e 462 colDefs[i].SzDataObj, &cb) != SQL_SUCCESS)
89894079
VZ
463 return(pDb->DispAllErrors(henv, hdbc, cursor));
464 }
465
466 // Completed successfully
467 return(TRUE);
6919c53f
GT
468
469} // wxTable::bindCols()
470
67e9aaa3 471
6919c53f
GT
472/********** wxTable::getRec() **********/
473bool wxTable::getRec(UWORD fetchType)
474{
89894079
VZ
475 RETCODE retcode;
476
477 if (!pDb->FwdOnlyCursors())
478 {
479 // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
480 UDWORD cRowsFetched;
481 UWORD rowStatus;
482
483 retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
484 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
485 if (retcode == SQL_NO_DATA_FOUND)
486 return(FALSE);
487 else
488 return(pDb->DispAllErrors(henv, hdbc, hstmt));
489 }
490 else
491 {
492 // Fetch the next record from the record set
493 retcode = SQLFetch(hstmt);
494 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
495 {
496 if (retcode == SQL_NO_DATA_FOUND)
497 return(FALSE);
498 else
499 return(pDb->DispAllErrors(henv, hdbc, hstmt));
500 }
501 }
502
503 // Completed successfully
504 return(TRUE);
6919c53f
GT
505
506} // wxTable::getRec()
507
67e9aaa3 508
6919c53f
GT
509/********** wxTable::execDelete() **********/
510bool wxTable::execDelete(const char *pSqlStmt)
511{
89894079
VZ
512 // Execute the DELETE statement
513 if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
514 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
6919c53f 515
89894079
VZ
516 // Record deleted successfully
517 return(TRUE);
6919c53f
GT
518
519} // wxTable::execDelete()
520
67e9aaa3 521
6919c53f
GT
522/********** wxTable::execUpdate() **********/
523bool wxTable::execUpdate(const char *pSqlStmt)
524{
89894079
VZ
525 // Execute the UPDATE statement
526 if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt, SQL_NTS) != SQL_SUCCESS)
527 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
6919c53f 528
89894079
VZ
529 // Record deleted successfully
530 return(TRUE);
6919c53f
GT
531
532} // wxTable::execUpdate()
533
67e9aaa3 534
6919c53f
GT
535/********** wxTable::query() **********/
536bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt)
537{
89894079 538 char sqlStmt[DB_MAX_STATEMENT_LEN];
6919c53f 539
89894079
VZ
540 // Set the selectForUpdate member variable
541 if (forUpdate)
542 // The user may wish to select for update, but the DBMS may not be capable
543 selectForUpdate = CanSelectForUpdate();
544 else
545 selectForUpdate = FALSE;
6919c53f 546
89894079
VZ
547 // Set the SQL SELECT string
548 if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in,
549 { // so generate a select statement.
550 GetSelectStmt(sqlStmt, queryType, distinct);
551 pDb->WriteSqlLog(sqlStmt);
552 }
6919c53f 553
89894079
VZ
554 // Make sure the cursor is closed first
555 if (! CloseCursor(hstmt))
556 return(FALSE);
6919c53f 557
89894079
VZ
558 // Execute the SQL SELECT statement
559 int retcode;
6919c53f 560
89894079
VZ
561 retcode = SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt : sqlStmt), SQL_NTS);
562 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
563 return(pDb->DispAllErrors(henv, hdbc, hstmt));
6919c53f 564
89894079
VZ
565 // Completed successfully
566 return(TRUE);
6919c53f
GT
567
568} // wxTable::query()
569
570
67e9aaa3
GT
571/***************************** PUBLIC FUNCTIONS *****************************/
572
6919c53f 573
108106cf
JS
574/********** wxTable::Open() **********/
575bool wxTable::Open(void)
576{
89894079
VZ
577 if (!pDb)
578 return FALSE;
579
580 int i;
1e92909e
GT
581// char sqlStmt[DB_MAX_STATEMENT_LEN];
582 wxString sqlStmt;
2f74ed28 583 wxString *s = NULL;
89894079
VZ
584
585 // Verify that the table exists in the database
2f74ed28
GT
586// if (!pDb->TableExists(tableName,pDb->GetUsername(),tablePath))
587 if (!pDb->TableExists(tableName,NULL,tablePath))
89894079 588 {
2f74ed28
GT
589 s =new wxString("Table/view does not exist in the database");
590 if (*(pDb->dbInf.accessibleTables) == 'Y')
591 {
592 (*s)+=", or you have insufficient permissions.\n";
593 }
594 else
595 {
596 (*s)+=".\n";
597 }
598 }
599 else
600 {
601 // Verify the user has rights to access the table.
602 // Shortcut boolean evaluation to optimize out call to TablePrivs
603 // Unfortunely this optimization doesn't seem to be reliable!
604 if (/* *(pDb->dbInf.accessibleTables) == 'N' && */
605 !pDb->TablePrivileges(tableName,"SELECT",NULL,tablePath))
606 s = new wxString("Current logged in user has insufficient privileges to access this table.\n");
607 }
608
609 if (s)
610 {
611 wxString p;
89894079 612 if (wxStrcmp(tablePath,""))
2f74ed28
GT
613 p.sprintf("Error opening '%s/%s'.\n",tablePath,tableName);
614 else
615 p.sprintf("Error opening '%s'.\n", tableName);
616
617 p += (*s);
618 pDb->LogError(p.GetData());
619
89894079
VZ
620 return(FALSE);
621 }
622
623 // Bind the member variables for field exchange between
624 // the wxTable object and the ODBC record.
625 if (!queryOnly)
626 {
627 if (!bindInsertParams()) // Inserts
628 return(FALSE);
629 if (!bindUpdateParams()) // Updates
630 return(FALSE);
631 }
632 if (!bindCols(*hstmtDefault)) // Selects
633 return(FALSE);
634 if (!bindCols(hstmtInternal)) // Internal use only
635 return(FALSE);
636 /*
637 * Do NOT bind the hstmtCount cursor!!!
638 */
639
640 // Build an insert statement using parameter markers
641 if (!queryOnly && noCols > 0)
642 {
643 bool needComma = FALSE;
1e92909e 644 sqlStmt.sprintf("INSERT INTO %s (", tableName);
89894079
VZ
645 for (i = 0; i < noCols; i++)
646 {
647 if (! colDefs[i].InsertAllowed)
648 continue;
649 if (needComma)
1e92909e
GT
650 sqlStmt += ",";
651 sqlStmt += colDefs[i].ColName;
89894079
VZ
652 needComma = TRUE;
653 }
654 needComma = FALSE;
1e92909e 655 sqlStmt += ") VALUES (";
89894079
VZ
656 for (i = 0; i < noCols; i++)
657 {
658 if (! colDefs[i].InsertAllowed)
659 continue;
660 if (needComma)
1e92909e
GT
661 sqlStmt += ",";
662 sqlStmt += "?";
89894079
VZ
663 needComma = TRUE;
664 }
1e92909e 665 sqlStmt += ")";
89894079
VZ
666
667// pDb->WriteSqlLog(sqlStmt);
668
669 // Prepare the insert statement for execution
1e92909e 670 if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS)
89894079
VZ
671 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
672 }
673
674 // Completed successfully
675 return(TRUE);
108106cf
JS
676
677} // wxTable::Open()
678
67e9aaa3 679
108106cf
JS
680/********** wxTable::Query() **********/
681bool wxTable::Query(bool forUpdate, bool distinct)
682{
683
89894079 684 return(query(DB_SELECT_WHERE, forUpdate, distinct));
108106cf
JS
685
686} // wxTable::Query()
687
67e9aaa3 688
108106cf
JS
689/********** wxTable::QueryBySqlStmt() **********/
690bool wxTable::QueryBySqlStmt(char *pSqlStmt)
691{
89894079 692 pDb->WriteSqlLog(pSqlStmt);
108106cf 693
89894079 694 return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt));
108106cf
JS
695
696} // wxTable::QueryBySqlStmt()
697
67e9aaa3 698
108106cf
JS
699/********** wxTable::QueryMatching() **********/
700bool wxTable::QueryMatching(bool forUpdate, bool distinct)
701{
702
89894079 703 return(query(DB_SELECT_MATCHING, forUpdate, distinct));
108106cf
JS
704
705} // wxTable::QueryMatching()
706
67e9aaa3 707
108106cf
JS
708/********** wxTable::QueryOnKeyFields() **********/
709bool wxTable::QueryOnKeyFields(bool forUpdate, bool distinct)
710{
711
89894079 712 return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
108106cf
JS
713
714} // wxTable::QueryOnKeyFields()
715
67e9aaa3 716
a3439c7d
GT
717/********** wxTable::GetPrev() **********/
718bool wxTable::GetPrev(void)
719{
89894079
VZ
720 if (pDb->FwdOnlyCursors())
721 {
722 wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxTable"));
723 return FALSE;
724 }
725 else
726 return(getRec(SQL_FETCH_PRIOR));
a3439c7d
GT
727} // wxTable::GetPrev()
728
67e9aaa3 729
a3439c7d
GT
730/********** wxTable::operator-- **********/
731bool wxTable::operator--(int)
732{
89894079
VZ
733 if (pDb->FwdOnlyCursors())
734 {
735 wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxTable"));
736 return FALSE;
737 }
738 else
739 return(getRec(SQL_FETCH_PRIOR));
a3439c7d
GT
740} // wxTable::operator--
741
67e9aaa3 742
a3439c7d
GT
743/********** wxTable::GetFirst() **********/
744bool wxTable::GetFirst(void)
745{
89894079
VZ
746 if (pDb->FwdOnlyCursors())
747 {
748 wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxTable"));
749 return FALSE;
750 }
751 else
752 return(getRec(SQL_FETCH_FIRST));
a3439c7d
GT
753} // wxTable::GetFirst()
754
67e9aaa3 755
a3439c7d
GT
756/********** wxTable::GetLast() **********/
757bool wxTable::GetLast(void)
758{
89894079
VZ
759 if (pDb->FwdOnlyCursors())
760 {
761 wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxTable"));
762 return FALSE;
763 }
764 else
765 return(getRec(SQL_FETCH_LAST));
a3439c7d
GT
766} // wxTable::GetLast()
767
67e9aaa3 768
108106cf
JS
769/********** wxTable::GetSelectStmt() **********/
770void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct)
771{
89894079
VZ
772 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
773
774 whereClause[0] = 0;
775
776 // Build a select statement to query the database
777 wxStrcpy(pSqlStmt, "SELECT ");
778
779 // SELECT DISTINCT values only?
780 if (distinct)
781 wxStrcat(pSqlStmt, "DISTINCT ");
782
783 // Was a FROM clause specified to join tables to the base table?
784 // Available for ::Query() only!!!
785 bool appendFromClause = FALSE;
786 if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from))
787 appendFromClause = TRUE;
788
789 // Add the column list
790 int i;
791 for (i = 0; i < noCols; i++)
792 {
793 // If joining tables, the base table column names must be qualified to avoid ambiguity
794 if (appendFromClause)
795 {
796 wxStrcat(pSqlStmt, queryTableName);
797 wxStrcat(pSqlStmt, ".");
798 }
799 wxStrcat(pSqlStmt, colDefs[i].ColName);
800 if (i + 1 < noCols)
801 wxStrcat(pSqlStmt, ",");
802 }
803
804 // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve
805 // the ROWID if querying distinct records. The rowid will always be unique.
806 if (!distinct && CanUpdByROWID())
807 {
808 // If joining tables, the base table column names must be qualified to avoid ambiguity
809 if (appendFromClause)
810 {
811 wxStrcat(pSqlStmt, ",");
812 wxStrcat(pSqlStmt, queryTableName);
813 wxStrcat(pSqlStmt, ".ROWID");
814 }
815 else
816 wxStrcat(pSqlStmt, ",ROWID");
817 }
818
819 // Append the FROM tablename portion
820 wxStrcat(pSqlStmt, " FROM ");
821 wxStrcat(pSqlStmt, queryTableName);
822
823 // Sybase uses the HOLDLOCK keyword to lock a record during query.
824 // The HOLDLOCK keyword follows the table name in the from clause.
825 // Each table in the from clause must specify HOLDLOCK or
826 // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
827 // is parsed but ignored in SYBASE Transact-SQL.
828 if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
829 wxStrcat(pSqlStmt, " HOLDLOCK");
830
831 if (appendFromClause)
832 wxStrcat(pSqlStmt, from);
833
834 // Append the WHERE clause. Either append the where clause for the class
835 // or build a where clause. The typeOfSelect determines this.
836 switch(typeOfSelect)
837 {
838 case DB_SELECT_WHERE:
839 if (where && wxStrlen(where)) // May not want a where clause!!!
840 {
841 wxStrcat(pSqlStmt, " WHERE ");
842 wxStrcat(pSqlStmt, where);
843 }
844 break;
845 case DB_SELECT_KEYFIELDS:
846 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
847 if (wxStrlen(whereClause))
848 {
849 wxStrcat(pSqlStmt, " WHERE ");
850 wxStrcat(pSqlStmt, whereClause);
851 }
852 break;
853 case DB_SELECT_MATCHING:
854 GetWhereClause(whereClause, DB_WHERE_MATCHING);
855 if (wxStrlen(whereClause))
856 {
857 wxStrcat(pSqlStmt, " WHERE ");
858 wxStrcat(pSqlStmt, whereClause);
859 }
860 break;
861 }
862
863 // Append the ORDER BY clause
864 if (orderBy && wxStrlen(orderBy))
865 {
866 wxStrcat(pSqlStmt, " ORDER BY ");
867 wxStrcat(pSqlStmt, orderBy);
868 }
869
870 // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
871 // parses the FOR UPDATE clause but ignores it. See the comment above on the
872 // HOLDLOCK for Sybase.
873 if (selectForUpdate && CanSelectForUpdate())
874 wxStrcat(pSqlStmt, " FOR UPDATE");
108106cf
JS
875
876} // wxTable::GetSelectStmt()
877
67e9aaa3 878
108106cf
JS
879/********** wxTable::GetRowNum() **********/
880UWORD wxTable::GetRowNum(void)
881{
89894079 882 UDWORD rowNum;
108106cf 883
89894079
VZ
884 if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
885 {
886 pDb->DispAllErrors(henv, hdbc, hstmt);
887 return(0);
888 }
108106cf 889
89894079
VZ
890 // Completed successfully
891 return((UWORD) rowNum);
108106cf
JS
892
893} // wxTable::GetRowNum()
894
67e9aaa3 895
108106cf
JS
896/********** wxTable::CloseCursor() **********/
897bool wxTable::CloseCursor(HSTMT cursor)
898{
89894079
VZ
899 if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
900 return(pDb->DispAllErrors(henv, hdbc, cursor));
108106cf 901
89894079
VZ
902 // Completed successfully
903 return(TRUE);
108106cf
JS
904
905} // wxTable::CloseCursor()
906
67e9aaa3 907
108106cf 908/********** wxTable::CreateTable() **********/
a2115c88 909bool wxTable::CreateTable(bool attemptDrop)
108106cf 910{
89894079
VZ
911 if (!pDb)
912 return FALSE;
1fc5dd6f 913
89894079 914 int i, j;
1e92909e
GT
915// char sqlStmt[DB_MAX_STATEMENT_LEN];
916 wxString sqlStmt;
108106cf 917
a2115c88 918#ifdef DBDEBUG_CONSOLE
89894079 919 cout << "Creating Table " << tableName << "..." << endl;
108106cf
JS
920#endif
921
89894079
VZ
922 // Drop table first
923 if (attemptDrop && !DropTable())
924 return FALSE;
108106cf 925
89894079 926 // Create the table
a2115c88 927#ifdef DBDEBUG_CONSOLE
89894079
VZ
928 for (i = 0; i < noCols; i++)
929 {
930 // Exclude derived columns since they are NOT part of the base table
931 if (colDefs[i].DerivedCol)
932 continue;
933 cout << i + 1 << ": " << colDefs[i].ColName << "; ";
934 switch(colDefs[i].DbDataType)
935 {
936 case DB_DATA_TYPE_VARCHAR:
937 cout << pDb->typeInfVarchar.TypeName << "(" << colDefs[i].SzDataObj << ")";
938 break;
939 case DB_DATA_TYPE_INTEGER:
940 cout << pDb->typeInfInteger.TypeName;
941 break;
942 case DB_DATA_TYPE_FLOAT:
943 cout << pDb->typeInfFloat.TypeName;
944 break;
945 case DB_DATA_TYPE_DATE:
946 cout << pDb->typeInfDate.TypeName;
947 break;
948 }
949 cout << endl;
950 }
108106cf
JS
951#endif
952
89894079
VZ
953 // Build a CREATE TABLE string from the colDefs structure.
954 bool needComma = FALSE;
1e92909e
GT
955 sqlStmt.sprintf("CREATE TABLE %s (", tableName);
956
89894079
VZ
957 for (i = 0; i < noCols; i++)
958 {
959 // Exclude derived columns since they are NOT part of the base table
960 if (colDefs[i].DerivedCol)
961 continue;
962 // Comma Delimiter
963 if (needComma)
1e92909e 964 sqlStmt += ",";
89894079 965 // Column Name
1e92909e
GT
966 sqlStmt += colDefs[i].ColName;
967 sqlStmt += " ";
89894079
VZ
968 // Column Type
969 switch(colDefs[i].DbDataType)
970 {
971 case DB_DATA_TYPE_VARCHAR:
1e92909e 972 sqlStmt += pDb->typeInfVarchar.TypeName; break;
89894079 973 case DB_DATA_TYPE_INTEGER:
1e92909e 974 sqlStmt += pDb->typeInfInteger.TypeName; break;
89894079 975 case DB_DATA_TYPE_FLOAT:
1e92909e 976 sqlStmt += pDb->typeInfFloat.TypeName; break;
89894079 977 case DB_DATA_TYPE_DATE:
1e92909e 978 sqlStmt += pDb->typeInfDate.TypeName; break;
89894079
VZ
979 }
980 // For varchars, append the size of the string
981 if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR)
982 {
1e92909e 983 wxString s;
89894079
VZ
984 // wxStrcat(sqlStmt, "(");
985 // wxStrcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10));
986 // wxStrcat(sqlStmt, ")");
1e92909e
GT
987 s.sprintf("(%d)", colDefs[i].SzDataObj);
988 sqlStmt += s.GetData();
89894079
VZ
989 }
990
991 if (pDb->Dbms() == dbmsSYBASE_ASE || pDb->Dbms() == dbmsMY_SQL)
992 {
993 if (colDefs[i].KeyField)
994 {
1e92909e 995 sqlStmt += " NOT NULL";
89894079
VZ
996 }
997 }
998
999 needComma = TRUE;
1000 }
1001 // If there is a primary key defined, include it in the create statement
1002 for (i = j = 0; i < noCols; i++)
1003 {
1004 if (colDefs[i].KeyField)
1005 {
1006 j++;
1007 break;
1008 }
1009 }
1010 if (j && pDb->Dbms() != dbmsDBASE) // Found a keyfield
1011 {
1012 if (pDb->Dbms() != dbmsMY_SQL)
1013 {
1e92909e
GT
1014 sqlStmt += ",CONSTRAINT ";
1015 sqlStmt += tableName;
1016 sqlStmt += "_PIDX PRIMARY KEY (";
89894079
VZ
1017 }
1018 else
1019 {
1020 /* MySQL goes out on this one. We also declare the relevant key NON NULL above */
1e92909e 1021 sqlStmt += ", PRIMARY KEY (";
89894079
VZ
1022 }
1023
1024 // List column name(s) of column(s) comprising the primary key
1025 for (i = j = 0; i < noCols; i++)
1026 {
1027 if (colDefs[i].KeyField)
1028 {
1029 if (j++) // Multi part key, comma separate names
1e92909e
GT
1030 sqlStmt += ",";
1031 sqlStmt += colDefs[i].ColName;
89894079
VZ
1032 }
1033 }
1e92909e 1034 sqlStmt += ")";
89894079
VZ
1035 }
1036 // Append the closing parentheses for the create table statement
1e92909e 1037 sqlStmt += ")";
a2115c88 1038
1e92909e 1039 pDb->WriteSqlLog(sqlStmt.GetData());
1fc5dd6f 1040
a2115c88 1041#ifdef DBDEBUG_CONSOLE
1e92909e 1042 cout << endl << sqlStmt.GetData() << endl;
108106cf
JS
1043#endif
1044
89894079 1045 // Execute the CREATE TABLE statement
1e92909e 1046 RETCODE retcode = SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS);
89894079
VZ
1047 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1048 {
1049 pDb->DispAllErrors(henv, hdbc, hstmt);
1050 pDb->RollbackTrans();
1051 CloseCursor(hstmt);
1052 return(FALSE);
1053 }
1054
1055 // Commit the transaction and close the cursor
1056 if (! pDb->CommitTrans())
1057 return(FALSE);
1058 if (! CloseCursor(hstmt))
1059 return(FALSE);
1060
1061 // Database table created successfully
1062 return(TRUE);
108106cf
JS
1063
1064} // wxTable::CreateTable()
1065
67e9aaa3 1066
a2115c88
GT
1067/********** wxTable::DropTable() **********/
1068bool wxTable::DropTable()
1069{
89894079
VZ
1070 // NOTE: This function returns TRUE if the Table does not exist, but
1071 // only for identified databases. Code will need to be added
1072 // below for any other databases when those databases are defined
1073 // to handle this situation consistently
a2115c88 1074
1e92909e
GT
1075// char sqlStmt[DB_MAX_STATEMENT_LEN];
1076 wxString sqlStmt;
a2115c88 1077
1e92909e 1078 sqlStmt.sprintf("DROP TABLE %s", tableName);
a2115c88 1079
1e92909e 1080 pDb->WriteSqlLog(sqlStmt.GetData());
a2115c88
GT
1081
1082#ifdef DBDEBUG_CONSOLE
1e92909e 1083 cout << endl << sqlStmt.GetData() << endl;
a2115c88
GT
1084#endif
1085
1e92909e 1086 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS)
89894079
VZ
1087 {
1088 // Check for "Base table not found" error and ignore
1089 pDb->GetNextError(henv, hdbc, hstmt);
1090 if (wxStrcmp(pDb->sqlState,"S0002")) // "Base table not found"
1091 {
1092 // Check for product specific error codes
1e92909e
GT
1093 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // 5.x (and lower?)
1094 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"S1000")) || // untested
1095 (pDb->Dbms() == dbmsPOSTGRES && !wxStrcmp(pDb->sqlState,"08S01")))) // untested
89894079
VZ
1096 {
1097 pDb->DispNextError();
1098 pDb->DispAllErrors(henv, hdbc, hstmt);
1099 pDb->RollbackTrans();
1100 CloseCursor(hstmt);
1101 return(FALSE);
1102 }
1103 }
1104 }
1105
1106 // Commit the transaction and close the cursor
1107 if (! pDb->CommitTrans())
1108 return(FALSE);
1109 if (! CloseCursor(hstmt))
1110 return(FALSE);
1111
1112 return(TRUE);
a2115c88
GT
1113} // wxTable::DropTable()
1114
67e9aaa3 1115
108106cf 1116/********** wxTable::CreateIndex() **********/
6919c53f 1117bool wxTable::CreateIndex(const char * idxName, bool unique, int noIdxCols, CidxDef *pIdxDefs, bool attemptDrop)
108106cf 1118{
1e92909e
GT
1119// char sqlStmt[DB_MAX_STATEMENT_LEN];
1120 wxString sqlStmt;
89894079
VZ
1121
1122 // Drop the index first
1123 if (attemptDrop && !DropIndex(idxName))
1124 return (FALSE);
1125
1126 // Build a CREATE INDEX statement
1e92909e 1127 sqlStmt = "CREATE ";
89894079 1128 if (unique)
1e92909e 1129 sqlStmt += "UNIQUE ";
89894079 1130
1e92909e
GT
1131 sqlStmt += "INDEX ";
1132 sqlStmt += idxName;
1133 sqlStmt += " ON ";
1134 sqlStmt += tableName;
1135 sqlStmt += " (";
89894079
VZ
1136
1137 // Append list of columns making up index
1138 int i;
1139 for (i = 0; i < noIdxCols; i++)
1140 {
1e92909e
GT
1141 sqlStmt += pIdxDefs[i].ColName;
1142 /* Postgres doesn't cope with ASC */
89894079
VZ
1143 if (pDb->Dbms() != dbmsPOSTGRES)
1144 {
1145 if (pIdxDefs[i].Ascending)
1e92909e 1146 sqlStmt += " ASC";
89894079 1147 else
1e92909e 1148 sqlStmt += " DESC";
89894079
VZ
1149 }
1150
1151 if ((i + 1) < noIdxCols)
1e92909e 1152 sqlStmt += ",";
89894079
VZ
1153 }
1154
1155 // Append closing parentheses
1e92909e 1156 sqlStmt += ")";
89894079 1157
1e92909e 1158 pDb->WriteSqlLog(sqlStmt.GetData());
1fc5dd6f 1159
a2115c88 1160#ifdef DBDEBUG_CONSOLE
1e92909e 1161 cout << endl << sqlStmt.GetData() << endl << endl;
108106cf
JS
1162#endif
1163
89894079 1164 // Execute the CREATE INDEX statement
1e92909e 1165 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS)
89894079
VZ
1166 {
1167 pDb->DispAllErrors(henv, hdbc, hstmt);
1168 pDb->RollbackTrans();
1169 CloseCursor(hstmt);
1170 return(FALSE);
1171 }
108106cf 1172
89894079
VZ
1173 // Commit the transaction and close the cursor
1174 if (! pDb->CommitTrans())
1175 return(FALSE);
1176 if (! CloseCursor(hstmt))
1177 return(FALSE);
108106cf 1178
89894079
VZ
1179 // Index Created Successfully
1180 return(TRUE);
108106cf
JS
1181
1182} // wxTable::CreateIndex()
1183
67e9aaa3 1184
a2115c88 1185/********** wxTable::DropIndex() **********/
6919c53f 1186bool wxTable::DropIndex(const char * idxName)
a2115c88 1187{
89894079
VZ
1188 // NOTE: This function returns TRUE if the Index does not exist, but
1189 // only for identified databases. Code will need to be added
1190 // below for any other databases when those databases are defined
1191 // to handle this situation consistently
a2115c88 1192
1e92909e
GT
1193// char sqlStmt[DB_MAX_STATEMENT_LEN];
1194 wxString sqlStmt;
a2115c88 1195
89894079 1196 if (pDb->Dbms() == dbmsACCESS)
1e92909e 1197 sqlStmt.sprintf("DROP INDEX %s ON %s",idxName,tableName);
89894079 1198 else if (pDb->Dbms() == dbmsSYBASE_ASE)
1e92909e 1199 sqlStmt.sprintf("DROP INDEX %s.%s",tableName,idxName);
89894079 1200 else
1e92909e 1201 sqlStmt.sprintf("DROP INDEX %s",idxName);
a2115c88 1202
1e92909e 1203 pDb->WriteSqlLog(sqlStmt.GetData());
a2115c88
GT
1204
1205#ifdef DBDEBUG_CONSOLE
1e92909e 1206 cout << endl << sqlStmt.GetData() << endl;
a2115c88
GT
1207#endif
1208
1e92909e 1209 if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS)
89894079
VZ
1210 {
1211 // Check for "Index not found" error and ignore
1212 pDb->GetNextError(henv, hdbc, hstmt);
1213 if (wxStrcmp(pDb->sqlState,"S0012")) // "Index not found"
1214 {
1215 // Check for product specific error codes
1e92909e
GT
1216 if (!((pDb->Dbms() == dbmsSYBASE_ASA && !wxStrcmp(pDb->sqlState,"42000")) || // v5.x (and lower?)
1217 (pDb->Dbms() == dbmsSYBASE_ASE && !wxStrcmp(pDb->sqlState,"S0002")) || // Base table not found
1218 (pDb->Dbms() == dbmsMY_SQL && !wxStrcmp(pDb->sqlState,"42S02")) // untested
89894079
VZ
1219 ))
1220 {
1221 pDb->DispNextError();
1222 pDb->DispAllErrors(henv, hdbc, hstmt);
1223 pDb->RollbackTrans();
1224 CloseCursor(hstmt);
1225 return(FALSE);
1226 }
1227 }
1228 }
1229
1230 // Commit the transaction and close the cursor
1231 if (! pDb->CommitTrans())
1232 return(FALSE);
1233 if (! CloseCursor(hstmt))
1234 return(FALSE);
1235
1236 return(TRUE);
a2115c88
GT
1237} // wxTable::DropIndex()
1238
67e9aaa3 1239
108106cf
JS
1240/********** wxTable::Insert() **********/
1241int wxTable::Insert(void)
1242{
89894079
VZ
1243 assert(!queryOnly);
1244 if (queryOnly)
1245 return(DB_FAILURE);
1246
1247 bindInsertParams();
1248
1249 // Insert the record by executing the already prepared insert statement
1250 RETCODE retcode;
1251 retcode=SQLExecute(hstmtInsert);
1252 if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
1253 {
1254 // Check to see if integrity constraint was violated
1255 pDb->GetNextError(henv, hdbc, hstmtInsert);
1256 if (! wxStrcmp(pDb->sqlState, "23000")) // Integrity constraint violated
1257 return(DB_ERR_INTEGRITY_CONSTRAINT_VIOL);
1258 else
1259 {
1260 pDb->DispNextError();
1261 pDb->DispAllErrors(henv, hdbc, hstmtInsert);
1262 return(DB_FAILURE);
1263 }
1264 }
1265
1266 // Record inserted into the datasource successfully
1267 return(DB_SUCCESS);
108106cf
JS
1268
1269} // wxTable::Insert()
1270
67e9aaa3 1271
108106cf
JS
1272/********** wxTable::Update() **********/
1273bool wxTable::Update(void)
1274{
89894079
VZ
1275 assert(!queryOnly);
1276 if (queryOnly)
1277 return(FALSE);
a2115c88 1278
89894079 1279 char sqlStmt[DB_MAX_STATEMENT_LEN];
108106cf 1280
89894079
VZ
1281 // Build the SQL UPDATE statement
1282 GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
108106cf 1283
89894079 1284 pDb->WriteSqlLog(sqlStmt);
1fc5dd6f 1285
a2115c88 1286#ifdef DBDEBUG_CONSOLE
1e92909e 1287 cout << endl << sqlStmt.GetData() << endl << endl;
108106cf
JS
1288#endif
1289
89894079
VZ
1290 // Execute the SQL UPDATE statement
1291 return(execUpdate(sqlStmt));
108106cf
JS
1292
1293} // wxTable::Update()
1294
67e9aaa3 1295
6919c53f
GT
1296/********** wxTable::Update(pSqlStmt) **********/
1297bool wxTable::Update(const char *pSqlStmt)
1298{
89894079
VZ
1299 assert(!queryOnly);
1300 if (queryOnly)
1301 return(FALSE);
6919c53f 1302
89894079 1303 pDb->WriteSqlLog(pSqlStmt);
6919c53f 1304
89894079 1305 return(execUpdate(pSqlStmt));
6919c53f
GT
1306
1307} // wxTable::Update(pSqlStmt)
1308
67e9aaa3 1309
108106cf 1310/********** wxTable::UpdateWhere() **********/
6919c53f 1311bool wxTable::UpdateWhere(const char *pWhereClause)
108106cf 1312{
89894079
VZ
1313 assert(!queryOnly);
1314 if (queryOnly)
1315 return(FALSE);
a2115c88 1316
89894079 1317 char sqlStmt[DB_MAX_STATEMENT_LEN];
108106cf 1318
89894079
VZ
1319 // Build the SQL UPDATE statement
1320 GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
108106cf 1321
89894079 1322 pDb->WriteSqlLog(sqlStmt);
1fc5dd6f 1323
a2115c88 1324#ifdef DBDEBUG_CONSOLE
1e92909e 1325 cout << endl << sqlStmt.GetData() << endl << endl;
108106cf
JS
1326#endif
1327
89894079
VZ
1328 // Execute the SQL UPDATE statement
1329 return(execUpdate(sqlStmt));
108106cf
JS
1330
1331} // wxTable::UpdateWhere()
1332
67e9aaa3 1333
108106cf
JS
1334/********** wxTable::Delete() **********/
1335bool wxTable::Delete(void)
1336{
89894079
VZ
1337 assert(!queryOnly);
1338 if (queryOnly)
1339 return(FALSE);
a2115c88 1340
89894079 1341 char sqlStmt[DB_MAX_STATEMENT_LEN];
108106cf 1342
89894079
VZ
1343 // Build the SQL DELETE statement
1344 GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
108106cf 1345
89894079 1346 pDb->WriteSqlLog(sqlStmt);
1fc5dd6f 1347
89894079
VZ
1348 // Execute the SQL DELETE statement
1349 return(execDelete(sqlStmt));
108106cf
JS
1350
1351} // wxTable::Delete()
1352
67e9aaa3 1353
108106cf 1354/********** wxTable::DeleteWhere() **********/
6919c53f 1355bool wxTable::DeleteWhere(const char *pWhereClause)
108106cf 1356{
89894079
VZ
1357 assert(!queryOnly);
1358 if (queryOnly)
1359 return(FALSE);
a2115c88 1360
89894079 1361 char sqlStmt[DB_MAX_STATEMENT_LEN];
108106cf 1362
89894079
VZ
1363 // Build the SQL DELETE statement
1364 GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
108106cf 1365
89894079 1366 pDb->WriteSqlLog(sqlStmt);
1fc5dd6f 1367
89894079
VZ
1368 // Execute the SQL DELETE statement
1369 return(execDelete(sqlStmt));
108106cf
JS
1370
1371} // wxTable::DeleteWhere()
1372
67e9aaa3 1373
108106cf
JS
1374/********** wxTable::DeleteMatching() **********/
1375bool wxTable::DeleteMatching(void)
1376{
89894079
VZ
1377 assert(!queryOnly);
1378 if (queryOnly)
1379 return(FALSE);
a2115c88 1380
89894079 1381 char sqlStmt[DB_MAX_STATEMENT_LEN];
108106cf 1382
89894079
VZ
1383 // Build the SQL DELETE statement
1384 GetDeleteStmt(sqlStmt, DB_DEL_MATCHING);
108106cf 1385
89894079 1386 pDb->WriteSqlLog(sqlStmt);
1fc5dd6f 1387
89894079
VZ
1388 // Execute the SQL DELETE statement
1389 return(execDelete(sqlStmt));
108106cf
JS
1390
1391} // wxTable::DeleteMatching()
1392
67e9aaa3 1393
108106cf 1394/********** wxTable::GetUpdateStmt() **********/
6919c53f 1395void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, const char *pWhereClause)
108106cf 1396{
89894079
VZ
1397 assert(!queryOnly);
1398 if (queryOnly)
1399 return;
1400
1401 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1402 bool firstColumn = TRUE;
1403
1404 whereClause[0] = 0;
1405 sprintf(pSqlStmt, "UPDATE %s SET ", tableName);
1406
1407 // Append a list of columns to be updated
1408 int i;
1409 for (i = 0; i < noCols; i++)
1410 {
1411 // Only append Updateable columns
1412 if (colDefs[i].Updateable)
1413 {
1414 if (! firstColumn)
1415 wxStrcat(pSqlStmt, ",");
1416 else
1417 firstColumn = FALSE;
1418 wxStrcat(pSqlStmt, colDefs[i].ColName);
1419 wxStrcat(pSqlStmt, " = ?");
1420 }
1421 }
1422
1423 // Append the WHERE clause to the SQL UPDATE statement
1424 wxStrcat(pSqlStmt, " WHERE ");
1425 switch(typeOfUpd)
1426 {
1427 case DB_UPD_KEYFIELDS:
1428 // If the datasource supports the ROWID column, build
1429 // the where on ROWID for efficiency purposes.
1430 // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
1431 if (CanUpdByROWID())
1432 {
1433 SDWORD cb;
1434 char rowid[ROWID_LEN];
1435
1436 // Get the ROWID value. If not successful retreiving the ROWID,
1437 // simply fall down through the code and build the WHERE clause
1438 // based on the key fields.
1439 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1440 {
1441 wxStrcat(pSqlStmt, "ROWID = '");
1442 wxStrcat(pSqlStmt, rowid);
1443 wxStrcat(pSqlStmt, "'");
1444 break;
1445 }
1446 }
1447 // Unable to delete by ROWID, so build a WHERE
1448 // clause based on the keyfields.
1449 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1450 wxStrcat(pSqlStmt, whereClause);
1451 break;
1452 case DB_UPD_WHERE:
1453 wxStrcat(pSqlStmt, pWhereClause);
1454 break;
1455 }
108106cf
JS
1456} // GetUpdateStmt()
1457
67e9aaa3 1458
108106cf 1459/********** wxTable::GetDeleteStmt() **********/
6919c53f 1460void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, const char *pWhereClause)
108106cf 1461{
89894079
VZ
1462 assert(!queryOnly);
1463 if (queryOnly)
1464 return;
1465
1466 char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
1467
1468 whereClause[0] = 0;
1469
1470 // Handle the case of DeleteWhere() and the where clause is blank. It should
1471 // delete all records from the database in this case.
1472 if (typeOfDel == DB_DEL_WHERE && (pWhereClause == 0 || wxStrlen(pWhereClause) == 0))
1473 {
1474 sprintf(pSqlStmt, "DELETE FROM %s", tableName);
1475 return;
1476 }
1477
1478 sprintf(pSqlStmt, "DELETE FROM %s WHERE ", tableName);
1479
1480 // Append the WHERE clause to the SQL DELETE statement
1481 switch(typeOfDel)
1482 {
1483 case DB_DEL_KEYFIELDS:
1484 // If the datasource supports the ROWID column, build
1485 // the where on ROWID for efficiency purposes.
1486 // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
1487 if (CanUpdByROWID())
1488 {
1489 SDWORD cb;
1490 char rowid[ROWID_LEN];
1491
1492 // Get the ROWID value. If not successful retreiving the ROWID,
1493 // simply fall down through the code and build the WHERE clause
1494 // based on the key fields.
1495 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1496 {
1497 wxStrcat(pSqlStmt, "ROWID = '");
1498 wxStrcat(pSqlStmt, rowid);
1499 wxStrcat(pSqlStmt, "'");
1500 break;
1501 }
1502 }
1503 // Unable to delete by ROWID, so build a WHERE
1504 // clause based on the keyfields.
1505 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
1506 wxStrcat(pSqlStmt, whereClause);
1507 break;
1508 case DB_DEL_WHERE:
1509 wxStrcat(pSqlStmt, pWhereClause);
1510 break;
1511 case DB_DEL_MATCHING:
1512 GetWhereClause(whereClause, DB_WHERE_MATCHING);
1513 wxStrcat(pSqlStmt, whereClause);
1514 break;
1515 }
108106cf
JS
1516
1517} // GetDeleteStmt()
1518
67e9aaa3 1519
108106cf 1520/********** wxTable::GetWhereClause() **********/
67e9aaa3 1521void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, const char *qualTableName)
108106cf
JS
1522/*
1523 * Note: GetWhereClause() currently ignores timestamp columns.
1524 * They are not included as part of the where clause.
1525 */
108106cf 1526{
89894079
VZ
1527 bool moreThanOneColumn = FALSE;
1528 char colValue[255];
1529
1530 // Loop through the columns building a where clause as you go
1531 int i;
1532 for (i = 0; i < noCols; i++)
1533 {
1534 // Determine if this column should be included in the WHERE clause
1535 if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[i].KeyField) ||
1536 (typeOfWhere == DB_WHERE_MATCHING && (! IsColNull(i))))
1537 {
1538 // Skip over timestamp columns
1539 if (colDefs[i].SqlCtype == SQL_C_TIMESTAMP)
1540 continue;
1541 // If there is more than 1 column, join them with the keyword "AND"
1542 if (moreThanOneColumn)
1543 wxStrcat(pWhereClause, " AND ");
1544 else
1545 moreThanOneColumn = TRUE;
1546 // Concatenate where phrase for the column
1547 if (qualTableName && wxStrlen(qualTableName))
1548 {
1549 wxStrcat(pWhereClause, qualTableName);
1550 wxStrcat(pWhereClause, ".");
1551 }
1552 wxStrcat(pWhereClause, colDefs[i].ColName);
1553 wxStrcat(pWhereClause, " = ");
1554 switch(colDefs[i].SqlCtype)
1555 {
1556 case SQL_C_CHAR:
1557 sprintf(colValue, "'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj);
1558 break;
1559 case SQL_C_SSHORT:
1560 sprintf(colValue, "%hi", *((SWORD *) colDefs[i].PtrDataObj));
1561 break;
1562 case SQL_C_USHORT:
1563 sprintf(colValue, "%hu", *((UWORD *) colDefs[i].PtrDataObj));
1564 break;
1565 case SQL_C_SLONG:
1566 sprintf(colValue, "%li", *((SDWORD *) colDefs[i].PtrDataObj));
1567 break;
1568 case SQL_C_ULONG:
1569 sprintf(colValue, "%lu", *((UDWORD *) colDefs[i].PtrDataObj));
1570 break;
1571 case SQL_C_FLOAT:
1572 sprintf(colValue, "%.6f", *((SFLOAT *) colDefs[i].PtrDataObj));
1573 break;
1574 case SQL_C_DOUBLE:
1575 sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj));
1576 break;
1577 }
1578 wxStrcat(pWhereClause, colValue);
1579 }
1580 }
108106cf
JS
1581} // wxTable::GetWhereClause()
1582
67e9aaa3 1583
108106cf
JS
1584/********** wxTable::IsColNull() **********/
1585bool wxTable::IsColNull(int colNo)
1586{
89894079
VZ
1587 switch(colDefs[colNo].SqlCtype)
1588 {
1589 case SQL_C_CHAR:
1590 return(((UCHAR FAR *) colDefs[colNo].PtrDataObj)[0] == 0);
1591 case SQL_C_SSHORT:
1592 return(( *((SWORD *) colDefs[colNo].PtrDataObj)) == 0);
1593 case SQL_C_USHORT:
1594 return(( *((UWORD*) colDefs[colNo].PtrDataObj)) == 0);
1595 case SQL_C_SLONG:
1596 return(( *((SDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1597 case SQL_C_ULONG:
1598 return(( *((UDWORD *) colDefs[colNo].PtrDataObj)) == 0);
1599 case SQL_C_FLOAT:
1600 return(( *((SFLOAT *) colDefs[colNo].PtrDataObj)) == 0);
1601 case SQL_C_DOUBLE:
1602 return((*((SDOUBLE *) colDefs[colNo].PtrDataObj)) == 0);
1603 case SQL_C_TIMESTAMP:
1604 TIMESTAMP_STRUCT *pDt;
1605 pDt = (TIMESTAMP_STRUCT *) colDefs[colNo].PtrDataObj;
1606 if (pDt->year == 0 && pDt->month == 0 && pDt->day == 0)
1607 return(TRUE);
1608 else
1609 return(FALSE);
1610 default:
1611 return(TRUE);
1612 }
108106cf
JS
1613} // wxTable::IsColNull()
1614
67e9aaa3 1615
108106cf 1616/********** wxTable::CanSelectForUpdate() **********/
108106cf
JS
1617bool wxTable::CanSelectForUpdate(void)
1618{
89894079
VZ
1619 if (pDb->Dbms() == dbmsMY_SQL)
1620 return FALSE;
a2115c88 1621
89894079
VZ
1622 if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)
1623 return(TRUE);
1624 else
1625 return(FALSE);
108106cf
JS
1626
1627} // wxTable::CanSelectForUpdate()
1628
67e9aaa3 1629
108106cf
JS
1630/********** wxTable::CanUpdByROWID() **********/
1631bool wxTable::CanUpdByROWID(void)
1632{
67e9aaa3
GT
1633/*
1634 * NOTE: Returning FALSE for now until this can be debugged,
89894079 1635 * as the ROWID is not getting updated correctly
67e9aaa3 1636 */
89894079 1637 return FALSE;
108106cf 1638
89894079
VZ
1639 if (pDb->Dbms() == dbmsORACLE)
1640 return(TRUE);
1641 else
1642 return(FALSE);
108106cf
JS
1643
1644} // wxTable::CanUpdByROWID()
1645
67e9aaa3 1646
108106cf
JS
1647/********** wxTable::IsCursorClosedOnCommit() **********/
1648bool wxTable::IsCursorClosedOnCommit(void)
1649{
89894079
VZ
1650 if (pDb->dbInf.cursorCommitBehavior == SQL_CB_PRESERVE)
1651 return(FALSE);
1652 else
1653 return(TRUE);
108106cf
JS
1654
1655} // wxTable::IsCursorClosedOnCommit()
1656
67e9aaa3 1657
108106cf
JS
1658/********** wxTable::ClearMemberVars() **********/
1659void wxTable::ClearMemberVars(void)
1660{
89894079
VZ
1661 // Loop through the columns setting each member variable to zero
1662 int i;
1663 for (i = 0; i < noCols; i++)
1664 {
1665 switch(colDefs[i].SqlCtype)
1666 {
1667 case SQL_C_CHAR:
1668 ((UCHAR FAR *) colDefs[i].PtrDataObj)[0] = 0;
1669 break;
1670 case SQL_C_SSHORT:
1671 *((SWORD *) colDefs[i].PtrDataObj) = 0;
1672 break;
1673 case SQL_C_USHORT:
1674 *((UWORD*) colDefs[i].PtrDataObj) = 0;
1675 break;
1676 case SQL_C_SLONG:
1677 *((SDWORD *) colDefs[i].PtrDataObj) = 0;
1678 break;
1679 case SQL_C_ULONG:
1680 *((UDWORD *) colDefs[i].PtrDataObj) = 0;
1681 break;
1682 case SQL_C_FLOAT:
1683 *((SFLOAT *) colDefs[i].PtrDataObj) = 0.0f;
1684 break;
1685 case SQL_C_DOUBLE:
1686 *((SDOUBLE *) colDefs[i].PtrDataObj) = 0.0f;
1687 break;
1688 case SQL_C_TIMESTAMP:
1689 TIMESTAMP_STRUCT *pDt;
1690 pDt = (TIMESTAMP_STRUCT *) colDefs[i].PtrDataObj;
1691 pDt->year = 0;
1692 pDt->month = 0;
1693 pDt->day = 0;
1694 pDt->hour = 0;
1695 pDt->minute = 0;
1696 pDt->second = 0;
1697 pDt->fraction = 0;
1698 break;
1699 }
1700 }
108106cf
JS
1701
1702} // wxTable::ClearMemberVars()
1703
67e9aaa3 1704
108106cf
JS
1705/********** wxTable::SetQueryTimeout() **********/
1706bool wxTable::SetQueryTimeout(UDWORD nSeconds)
1707{
89894079
VZ
1708 if (SQLSetStmtOption(hstmtInsert, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1709 return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
1710 if (SQLSetStmtOption(hstmtUpdate, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1711 return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
1712 if (SQLSetStmtOption(hstmtDelete, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1713 return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
1714 if (SQLSetStmtOption(hstmtInternal, SQL_QUERY_TIMEOUT, nSeconds) != SQL_SUCCESS)
1715 return(pDb->DispAllErrors(henv, hdbc, hstmtInternal));
1716
1717 // Completed Successfully
1718 return(TRUE);
108106cf
JS
1719
1720} // wxTable::SetQueryTimeout()
1721
67e9aaa3 1722
108106cf 1723/********** wxTable::SetColDefs() **********/
6919c53f 1724void wxTable::SetColDefs (int index, const char *fieldName, int dataType, void *pData,
89894079
VZ
1725 int cType, int size, bool keyField, bool upd,
1726 bool insAllow, bool derivedCol)
108106cf 1727{
89894079
VZ
1728 if (!colDefs) // May happen if the database connection fails
1729 return;
1730
1731 if (wxStrlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN)
1732 {
1733 wxStrncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN);
1734 colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0;
1735 }
1736 else
1737 wxStrcpy(colDefs[index].ColName, fieldName);
1738
1739 colDefs[index].DbDataType = dataType;
1740 colDefs[index].PtrDataObj = pData;
1741 colDefs[index].SqlCtype = cType;
1742 colDefs[index].SzDataObj = size;
1743 colDefs[index].KeyField = keyField;
1744 colDefs[index].DerivedCol = derivedCol;
1745 // Derived columns by definition would NOT be "Insertable" or "Updateable"
1746 if (derivedCol)
1747 {
1748 colDefs[index].Updateable = FALSE;
1749 colDefs[index].InsertAllowed = FALSE;
1750 }
1751 else
1752 {
1753 colDefs[index].Updateable = upd;
1754 colDefs[index].InsertAllowed = insAllow;
1755 }
1756
1757 colDefs[index].Null = FALSE;
1758
108106cf
JS
1759} // wxTable::SetColDefs()
1760
67e9aaa3
GT
1761
1762/********** wxTable::SetColDef() **********/
1b7274a8
BJ
1763// BJO20000121 : changed prototype in order to return proper pointer on wxColDataPtr's array
1764//bool wxTable::SetColDefs(wxColInf *pColInfs, ULONG numCols, wxColDataPtr *pColDataPtrs)
1765wxColDataPtr* wxTable::SetColDefs (wxColInf *pColInfs, ULONG numCols)
67e9aaa3 1766{
89894079
VZ
1767 assert(pColInfs);
1768 wxColDataPtr *pColDataPtrs = NULL;
67e9aaa3 1769
89894079
VZ
1770 if (pColInfs)
1771 {
1772 ULONG index;
67e9aaa3 1773
1b7274a8 1774
89894079 1775 pColDataPtrs = new wxColDataPtr[numCols+1];
67e9aaa3
GT
1776
1777 for (index = 0; index < numCols; index++)
89894079 1778 {
67e9aaa3 1779/*
89894079
VZ
1780 wxString title,msg;
1781 title.sprintf("Catalog: %s, Schema: %s, Table name: %s",pColInfs[index].catalog,pColInfs[index].schema,pColInfs[index].tableName);
1782 msg.sprintf("Column name: %s\nData type: %04d\nType name: %s\nColumn size: %d\nBuffer len: %d\nDecimals:%d\nRadix: %d\nNullable: %d\nRemarks: %s",
1783 pColInfs[index].colName,pColInfs[index].sqlDataType,pColInfs[index].typeName,pColInfs[index].columnSize,pColInfs[index].bufferLength,pColInfs[index].decimalDigits,pColInfs[index].numPrecRadix,pColInfs[index].nullable,pColInfs[index].remarks);
1784 msg += " \nDB_DATA_TYPE: ";
1785 switch(pColInfs[index].dbDataType)
1786 {
1787 case DB_DATA_TYPE_VARCHAR:
1788 msg += pDb->typeInfVarchar.TypeName; break;
1789 case DB_DATA_TYPE_INTEGER:
1790 msg += pDb->typeInfInteger.TypeName; break;
1791 case DB_DATA_TYPE_FLOAT:
1792 msg += pDb->typeInfFloat.TypeName; break;
1793 case DB_DATA_TYPE_DATE:
1794 msg += pDb->typeInfDate.TypeName; break;
1795 }
1796 wxMessageBox(msg.GetData(),title.GetData());
67e9aaa3 1797*/
89894079
VZ
1798 // Process the fields
1799 switch (pColInfs[index].dbDataType)
1800 {
1801 case DB_DATA_TYPE_VARCHAR:
1802 {
1803 pColDataPtrs[index].PtrDataObj = new char[pColInfs[index].bufferLength+1];
1804 pColDataPtrs[index].SzDataObj = pColInfs[index].bufferLength;
1805 pColDataPtrs[index].SqlCtype = SQL_C_CHAR;
1806 break;
1807 }
1808 case DB_DATA_TYPE_INTEGER:
1809 {
67e9aaa3 1810 // Can be long or short
89894079
VZ
1811 if (pColInfs[index].bufferLength == sizeof(long))
1812 {
1813 pColDataPtrs[index].PtrDataObj = new long;
1814 pColDataPtrs[index].SzDataObj = sizeof(long);
1815 pColDataPtrs[index].SqlCtype = SQL_C_SLONG;
1816 }
1817 else
1818 {
1819 pColDataPtrs[index].PtrDataObj = new short;
1820 pColDataPtrs[index].SzDataObj = sizeof(short);
1821 pColDataPtrs[index].SqlCtype = SQL_C_SSHORT;
1822 }
1823 break;
1824 }
1825 case DB_DATA_TYPE_FLOAT:
1826 {
1827 // Can be float or double
1828 if (pColInfs[index].bufferLength == sizeof(float))
1829 {
1830 pColDataPtrs[index].PtrDataObj = new float;
1831 pColDataPtrs[index].SzDataObj = sizeof(float);
1832 pColDataPtrs[index].SqlCtype = SQL_C_FLOAT;
1833 }
1834 else
1835 {
1836 pColDataPtrs[index].PtrDataObj = new double;
1837 pColDataPtrs[index].SzDataObj = sizeof(double);
1838 pColDataPtrs[index].SqlCtype = SQL_C_DOUBLE;
1839 }
1840 break;
1841 }
1842 case DB_DATA_TYPE_DATE:
1843 {
1844 pColDataPtrs[index].PtrDataObj = new TIMESTAMP_STRUCT;
1845 pColDataPtrs[index].SzDataObj = sizeof(TIMESTAMP_STRUCT);
1846 pColDataPtrs[index].SqlCtype = SQL_C_TIMESTAMP;
1847 break;
1848 }
1849 }
67e9aaa3
GT
1850
1851 SetColDefs (index,pColInfs[index].colName,pColInfs[index].dbDataType, pColDataPtrs[index].PtrDataObj, pColDataPtrs[index].SqlCtype, pColDataPtrs[index].SzDataObj);
89894079
VZ
1852 }
1853 }
1854 return (pColDataPtrs);
67e9aaa3
GT
1855} // wxTable::SetColDef()
1856
1857
108106cf 1858/********** wxTable::SetCursor() **********/
a2115c88 1859void wxTable::SetCursor(HSTMT *hstmtActivate)
108106cf 1860{
89894079
VZ
1861 if (hstmtActivate == DEFAULT_CURSOR)
1862 hstmt = *hstmtDefault;
1863 else
1864 hstmt = *hstmtActivate;
108106cf
JS
1865
1866} // wxTable::SetCursor()
1867
67e9aaa3 1868
285c163f
GT
1869/********** wxTable::Count(const char *) **********/
1870ULONG wxTable::Count(const char *args)
108106cf 1871{
89894079 1872 ULONG l;
1e92909e
GT
1873// char sqlStmt[DB_MAX_STATEMENT_LEN];
1874 wxString sqlStmt;
89894079
VZ
1875 SDWORD cb;
1876
1877 // Build a "SELECT COUNT(*) FROM queryTableName [WHERE whereClause]" SQL Statement
1e92909e
GT
1878 sqlStmt = "SELECT COUNT(";
1879 sqlStmt += args;
1880 sqlStmt += ") FROM ";
1881 sqlStmt += queryTableName;
89894079
VZ
1882
1883 if (from && wxStrlen(from))
1e92909e 1884 sqlStmt += from;
89894079
VZ
1885
1886 // Add the where clause if one is provided
1887 if (where && wxStrlen(where))
1888 {
1e92909e
GT
1889 sqlStmt += " WHERE ";
1890 sqlStmt += where;
89894079
VZ
1891 }
1892
1e92909e 1893 pDb->WriteSqlLog(sqlStmt.GetData());
89894079
VZ
1894
1895 // Initialize the Count cursor if it's not already initialized
1896 if (!hstmtCount)
1897 {
1898 hstmtCount = NewCursor(FALSE,FALSE);
1899 assert(hstmtCount);
1900 if (!hstmtCount)
1901 return(0);
1902 }
1903
1904 // Execute the SQL statement
1e92909e 1905 if (SQLExecDirect(*hstmtCount, (UCHAR FAR *) sqlStmt.GetData(), SQL_NTS) != SQL_SUCCESS)
89894079
VZ
1906 {
1907 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1908 return(0);
1909 }
1910
1911 // Fetch the record
1912 if (SQLFetch(*hstmtCount) != SQL_SUCCESS)
1913 {
1914 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1915 return(0);
1916 }
1917
1918 // Obtain the result
1919 if (SQLGetData(*hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS)
1920 {
1921 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1922 return(0);
1923 }
1924
1925 // Free the cursor
1926 if (SQLFreeStmt(*hstmtCount, SQL_CLOSE) != SQL_SUCCESS)
1927 pDb->DispAllErrors(henv, hdbc, *hstmtCount);
1928
1929 // Return the record count
1930 return(l);
108106cf
JS
1931
1932} // wxTable::Count()
1933
67e9aaa3 1934
108106cf
JS
1935/********** wxTable::Refresh() **********/
1936bool wxTable::Refresh(void)
1937{
89894079
VZ
1938 bool result = TRUE;
1939
1940 // Switch to the internal cursor so any active cursors are not corrupted
1941 HSTMT currCursor = GetCursor();
1942 hstmt = hstmtInternal;
1943
1944 // Save the where and order by clauses
1945 char *saveWhere = where;
1946 char *saveOrderBy = orderBy;
1947
1948 // Build a where clause to refetch the record with. Try and use the
1949 // ROWID if it's available, ow use the key fields.
1950 char whereClause[DB_MAX_WHERE_CLAUSE_LEN+1];
1951 wxStrcpy(whereClause, "");
1952 if (CanUpdByROWID())
1953 {
1954 SDWORD cb;
1955 char rowid[ROWID_LEN+1];
1956
1957 // Get the ROWID value. If not successful retreiving the ROWID,
1958 // simply fall down through the code and build the WHERE clause
1959 // based on the key fields.
1960 if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS)
1961 {
1962 wxStrcat(whereClause, queryTableName);
1963 wxStrcat(whereClause, ".ROWID = '");
1964 wxStrcat(whereClause, rowid);
1965 wxStrcat(whereClause, "'");
1966 }
1967 }
1968
1969 // If unable to use the ROWID, build a where clause from the keyfields
1970 if (wxStrlen(whereClause) == 0)
1971 GetWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName);
1972
1973 // Requery the record
1974 where = whereClause;
1975 orderBy = 0;
1976 if (!Query())
1977 result = FALSE;
1978
1979 if (result && !GetNext())
1980 result = FALSE;
1981
1982 // Switch back to original cursor
1983 SetCursor(&currCursor);
1984
1985 // Free the internal cursor
1986 if (SQLFreeStmt(hstmtInternal, SQL_CLOSE) != SQL_SUCCESS)
1987 pDb->DispAllErrors(henv, hdbc, hstmtInternal);
1988
1989 // Restore the original where and order by clauses
1e92909e 1990 where = saveWhere;
89894079
VZ
1991 orderBy = saveOrderBy;
1992
1993 return(result);
108106cf
JS
1994
1995} // wxTable::Refresh()
1996
67e9aaa3
GT
1997
1998/********** wxTable::SetNull(int colNo) **********/
a2115c88
GT
1999bool wxTable::SetNull(int colNo)
2000{
89894079
VZ
2001 if (colNo < noCols)
2002 return(colDefs[colNo].Null = TRUE);
2003 else
2004 return(FALSE);
a2115c88 2005
67e9aaa3
GT
2006} // wxTable::SetNull(int colNo)
2007
a2115c88
GT
2008
2009/********** wxTable::SetNull(char *colName) **********/
6919c53f 2010bool wxTable::SetNull(const char *colName)
a2115c88 2011{
89894079
VZ
2012 int i;
2013 for (i = 0; i < noCols; i++)
2014 {
2015 if (!wxStricmp(colName, colDefs[i].ColName))
2016 break;
2017 }
2018
2019 if (i < noCols)
2020 return(colDefs[i].Null = TRUE);
2021 else
2022 return(FALSE);
a2115c88
GT
2023
2024} // wxTable::SetNull(char *colName)
2025
67e9aaa3 2026
a2115c88
GT
2027/********** wxTable::NewCursor() **********/
2028HSTMT *wxTable::NewCursor(bool setCursor, bool bindColumns)
2029{
89894079
VZ
2030 HSTMT *newHSTMT = new HSTMT;
2031 assert(newHSTMT);
2032 if (!newHSTMT)
2033 return(0);
2034
2035 if (SQLAllocStmt(hdbc, newHSTMT) != SQL_SUCCESS)
2036 {
2037 pDb->DispAllErrors(henv, hdbc);
2038 delete newHSTMT;
2039 return(0);
2040 }
2041
2042 if (SQLSetStmtOption(*newHSTMT, SQL_CURSOR_TYPE, cursorType) != SQL_SUCCESS)
2043 {
2044 pDb->DispAllErrors(henv, hdbc, *newHSTMT);
2045 delete newHSTMT;
2046 return(0);
2047 }
2048
2049 if (bindColumns)
2050 {
2051 if(!bindCols(*newHSTMT))
2052 {
2053 delete newHSTMT;
2054 return(0);
2055 }
2056 }
2057
2058 if (setCursor)
2059 SetCursor(newHSTMT);
2060
2061 return(newHSTMT);
2062
2063} // wxTable::NewCursor()
67e9aaa3 2064
a2115c88
GT
2065
2066/********** wxTable::DeleteCursor() **********/
2067bool wxTable::DeleteCursor(HSTMT *hstmtDel)
2068{
89894079 2069 bool result = TRUE;
a2115c88 2070
89894079
VZ
2071 if (!hstmtDel) // Cursor already deleted
2072 return(result);
a2115c88 2073
89894079
VZ
2074 if (SQLFreeStmt(*hstmtDel, SQL_DROP) != SQL_SUCCESS)
2075 {
2076 pDb->DispAllErrors(henv, hdbc);
2077 result = FALSE;
2078 }
a2115c88 2079
89894079 2080 delete hstmtDel;
a2115c88 2081
89894079 2082 return(result);
a2115c88
GT
2083
2084} // wxTable::DeleteCursor()
2085
2086#endif // wxUSE_ODBC
1fc5dd6f 2087