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