src/common/dbtable.cpp

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