Added support more support for DB2

Added support for PervasiveSQL
Fixed MSAccess testing of DROP TABLE result when the table does not exist

git-svn-id: https://svn.wxwidgets.org/svn/wx/wxWidgets/trunk@10136 c3d73ce0-8a6f-49c7-b76d-6d57e0e08775

diff --git a/src/common/db.cpp b/src/common/db.cpp
index 218d94e777208d6711e9cbd55c1ec598056b6a24..4c0f20543dce6863d3d9d91e473e64f330ea3d51 100644 (file)
--- a/src/common/db.cpp
+++ b/src/common/db.cpp
@@ -1892,9 +1892,16 @@ bool wxDb::DropView(const wxString &viewName)
 /********** wxDb::ExecSql()  **********/
 bool wxDb::ExecSql(const wxString &pSqlStmt)
 {
+    RETCODE retcode;
+
     SQLFreeStmt(hstmt, SQL_CLOSE);
-    if (SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS) == SQL_SUCCESS)
+
+    retcode = SQLExecDirect(hstmt, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
+    if (retcode == SQL_SUCCESS || 
+        (Dbms() == dbmsDB2 && (retcode == SQL_SUCCESS_WITH_INFO || retcode == SQL_NO_DATA_FOUND)))
+    {
         return(TRUE);
+    }
     else
     {
         DispAllErrors(henv, hdbc, hstmt);
@@ -3123,7 +3130,9 @@ bool wxDb::TableExists(const wxString &tableName, const wxChar *userID, const wx
     if (!UserID.IsEmpty() &&
         Dbms() != dbmsMY_SQL &&
         Dbms() != dbmsACCESS &&
-        Dbms() != dbmsMS_SQL_SERVER)
+        Dbms() != dbmsMS_SQL_SERVER &&
+        Dbms() != dbmsDB2 &&
+        Dbms() != dbmsPERVASIVE_SQL)
     {
         retcode = SQLTables(hstmt,
                             NULL, 0,                                  // All qualifiers
@@ -3373,6 +3382,11 @@ wxDBMS wxDb::Dbms(void)
  *
  * DB2
  *        - Primary keys must be declared as NOT NULL
+ *        - Table and index names must not be longer than 13 characters in length (technically
+ *          table names can be up to 18 characters, but the primary index is created using the
+ *          base table name plus "_PIDX", so the limit if the table has a primary index is 13.
+ *
+ * PERVASIVE SQL
  *
  */
 {
@@ -3418,6 +3432,10 @@ wxDBMS wxDb::Dbms(void)
     if (!wxStricmp(dbInf.dbmsName,wxT("PostgreSQL")))  // v6.5.0
         return((wxDBMS)(dbmsType = dbmsPOSTGRES));
 
+    baseName[9] = 0;
+       if (!wxStricmp(dbInf.dbmsName,wxT("Pervasive")))
+               return((wxDBMS)(dbmsType = dbmsPERVASIVE_SQL));
+
     baseName[8] = 0;
     if (!wxStricmp(baseName,wxT("Informix")))
         return((wxDBMS)(dbmsType = dbmsINFORMIX));

diff --git a/src/common/dbtable.cpp b/src/common/dbtable.cpp
index a9cd88abb1d72314b1c0f2e9facfcb6c2aae6625..0798b39dc721e7b9beae0be76c5d604f7d663062 100644 (file)
--- a/src/common/dbtable.cpp
+++ b/src/common/dbtable.cpp
@@ -417,7 +417,7 @@ ODBC 3.0 says to use this form
 /***************************** PRIVATE FUNCTIONS *****************************/
 
 
-/********** wxDbTable::bindUpdateParams() **********/
+/********** wxDbTable::bindParams() **********/
 bool wxDbTable::bindParams(bool forUpdate)
 {
     wxASSERT(!queryOnly);
@@ -431,17 +431,18 @@ bool wxDbTable::bindParams(bool forUpdate)
     // Bind each column of the table that should be bound
     // to a parameter marker
     int i;
-        UWORD colNo;
-    for (i = 0, colNo = 1; i < noCols; i++)
+    UWORD colNo;
+
+    for (i=0, colNo=1; i < noCols; i++)
     {
         if (forUpdate)
         {
-            if (! colDefs[i].Updateable)
+            if (!colDefs[i].Updateable)
                 continue;
         }
         else
         {
-            if (! colDefs[i].InsertAllowed)
+            if (!colDefs[i].InsertAllowed)
                 continue;
         }
 
@@ -541,8 +542,6 @@ bool wxDbTable::bindUpdateParams(void)
 /********** wxDbTable::bindCols() **********/
 bool wxDbTable::bindCols(HSTMT cursor)
 {
-//RG-NULL    static SDWORD  cb;
-    
     // Bind each column of the table to a memory address for fetching data
     UWORD i;
     for (i = 0; i < noCols; i++)
@@ -618,12 +617,21 @@ bool wxDbTable::getRec(UWORD fetchType)
 /********** wxDbTable::execDelete() **********/
 bool wxDbTable::execDelete(const wxString &pSqlStmt)
 {
+    RETCODE retcode;
+
     // Execute the DELETE statement
-    if (SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
-        return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
+    retcode = SQLExecDirect(hstmtDelete, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
 
-    // Record deleted successfully
-    return(TRUE);
+    if (retcode == SQL_SUCCESS ||
+        retcode == SQL_NO_DATA_FOUND ||
+        retcode == SQL_SUCCESS_WITH_INFO)
+    {
+        // Record deleted successfully
+        return(TRUE);
+    }
+
+    // Problem deleting record
+    return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));
 
 }  // wxDbTable::execDelete()
 
@@ -631,12 +639,21 @@ bool wxDbTable::execDelete(const wxString &pSqlStmt)
 /********** wxDbTable::execUpdate() **********/
 bool wxDbTable::execUpdate(const wxString &pSqlStmt)
 {
+    RETCODE retcode;
+
     // Execute the UPDATE statement
-    if (SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
-        return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
+    retcode = SQLExecDirect(hstmtUpdate, (UCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
 
-    // Record deleted successfully
-    return(TRUE);
+    if (retcode == SQL_SUCCESS ||
+        retcode == SQL_NO_DATA_FOUND ||
+        retcode == SQL_SUCCESS_WITH_INFO)
+    {
+        // Record updated successfully
+        return(TRUE);
+    }
+
+    // Problem updating record
+    return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
 
 }  // wxDbTable::execUpdate()
 
@@ -1001,7 +1018,7 @@ void wxDbTable::BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool disti
     for (i = 0; i < noCols; i++)
     {
         // If joining tables, the base table column names must be qualified to avoid ambiguity
-        if (appendFromClause)
+        if (appendFromClause || pDb->Dbms() == dbmsACCESS)
         {
             pSqlStmt += queryTableName;
             pSqlStmt += wxT(".");
@@ -1016,7 +1033,7 @@ void wxDbTable::BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool disti
     if (!distinct && CanUpdByROWID())
     {
         // If joining tables, the base table column names must be qualified to avoid ambiguity
-        if (appendFromClause)
+        if (appendFromClause || pDb->Dbms() == dbmsACCESS)
         {
             pSqlStmt += wxT(",");
             pSqlStmt += queryTableName;
@@ -1115,7 +1132,7 @@ void wxDbTable::BuildUpdateStmt(wxString &pSqlStmt, int typeOfUpd, const wxStrin
 
     bool firstColumn = TRUE;
 
-    pSqlStmt.Printf(wxT("UPDATE %s SET "), tableName.c_str());
+    pSqlStmt.Printf(wxT("UPDATE %s SET "), tableName.Upper().c_str());
 
     // Append a list of columns to be updated
     int i;
@@ -1405,14 +1422,22 @@ bool wxDbTable::CreateTable(bool attemptDrop)
             case dbmsSYBASE_ASE:
             case dbmsMY_SQL:
             {
-                /* MySQL goes out on this one. We also declare the relevant key NON NULL above */
+                // MySQL goes out on this one. We also declare the relevant key NON NULL above
                 sqlStmt += wxT(",PRIMARY KEY (");
                 break;
             }
             default:
             {
                 sqlStmt += wxT(",CONSTRAINT ");
-                sqlStmt += tableName;
+                //  DB2 is limited to 18 characters for index names
+                if (pDb->Dbms() == dbmsDB2)
+                {
+                    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."));
+                    sqlStmt += tableName.substr(0, 13);
+                }
+                else
+                    sqlStmt += tableName;
+
                 sqlStmt += wxT("_PIDX PRIMARY KEY (");
                 break;
             }
@@ -1428,15 +1453,15 @@ bool wxDbTable::CreateTable(bool attemptDrop)
                 sqlStmt += colDefs[i].ColName;
             }
         }
-       sqlStmt += wxT(")");
+        sqlStmt += wxT(")");
 
-       if (pDb->Dbms() == dbmsSYBASE_ASA ||
-           pDb->Dbms() == dbmsSYBASE_ASE)
-       {
-        sqlStmt += wxT(" CONSTRAINT ");
-        sqlStmt += tableName;
-        sqlStmt += wxT("_PIDX");
-       }
+        if (pDb->Dbms() == dbmsSYBASE_ASA ||
+            pDb->Dbms() == dbmsSYBASE_ASE)
+        {
+            sqlStmt += wxT(" CONSTRAINT ");
+            sqlStmt += tableName;
+            sqlStmt += wxT("_PIDX");
+        }
     }
     // Append the closing parentheses for the create table statement
     sqlStmt += wxT(")");
@@ -1487,17 +1512,19 @@ bool wxDbTable::DropTable()
     cout << endl << sqlStmt.c_str() << endl;
 #endif
 
-    if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
+     RETCODE retcode = SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt.c_str(), SQL_NTS);
+    if (retcode != SQL_SUCCESS)
     {
         // Check for "Base table not found" error and ignore
         pDb->GetNextError(henv, hdbc, hstmt);   
-        if (wxStrcmp(pDb->sqlState, wxT("S0002")) &&
-            wxStrcmp(pDb->sqlState, wxT("S1000")))  // "Base table not found" 
-        {    
+        if (wxStrcmp(pDb->sqlState, wxT("S0002")) /*&&
+            wxStrcmp(pDb->sqlState, wxT("S1000"))*/)  // "Base table not found" 
+        {
             // Check for product specific error codes
-            if (!((pDb->Dbms() == dbmsSYBASE_ASA  && !wxStrcmp(pDb->sqlState,wxT("42000")))   ||  // 5.x (and lower?)
-                (pDb->Dbms() == dbmsSYBASE_ASE    && !wxStrcmp(pDb->sqlState,wxT("37000")))   ||   
-                (pDb->Dbms() == dbmsPOSTGRES      && !wxStrcmp(pDb->sqlState,wxT("08S01")))))     
+            if (!((pDb->Dbms() == dbmsSYBASE_ASA    && !wxStrcmp(pDb->sqlState,wxT("42000")))   ||  // 5.x (and lower?)
+                  (pDb->Dbms() == dbmsSYBASE_ASE    && !wxStrcmp(pDb->sqlState,wxT("37000")))   ||   
+                  (pDb->Dbms() == dbmsPERVASIVE_SQL && !wxStrcmp(pDb->sqlState,wxT("S1000")))   ||  // Returns an S1000 then an S0002
+                  (pDb->Dbms() == dbmsPOSTGRES      && !wxStrcmp(pDb->sqlState,wxT("08S01")))))     
             {
                 pDb->DispNextError();
                 pDb->DispAllErrors(henv, hdbc, hstmt);
@@ -1520,7 +1547,7 @@ bool wxDbTable::DropTable()
 
 /********** wxDbTable::CreateIndex() **********/
 bool wxDbTable::CreateIndex(const wxString &idxName, bool unique, UWORD noIdxCols,
-                                                                        wxDbIdxDef *pIdxDefs, bool attemptDrop)
+                                     wxDbIdxDef *pIdxDefs, bool attemptDrop)
 {
     wxString sqlStmt;
 
@@ -1704,7 +1731,7 @@ bool wxDbTable::DropIndex(const wxString &idxName)
 /********** wxDbTable::SetOrderByColNums() **********/
 bool wxDbTable::SetOrderByColNums(UWORD first, ... )
 {
-    int               colNo = first;  // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
+    int        colNo = first;  // using 'int' to be able to look for wxDB_NO_MORE_COLUN_NUMBERS
     va_list     argptr;
 
     bool        abort = FALSE;
@@ -2152,7 +2179,7 @@ wxDbColDataPtr* wxDbTable::SetColDefs(wxDbColInf *pColInfs, UWORD numCols)
                     pColDataPtrs[index].SqlCtype   = SQL_C_TIMESTAMP;
                     break;
                 case DB_DATA_TYPE_BLOB:
-                                                 int notSupportedYet = 0;
+                    int notSupportedYet = 0;
                     wxASSERT_MSG(notSupportedYet, wxT("This form of ::SetColDefs() cannot be used with BLOB columns"));
                     pColDataPtrs[index].PtrDataObj = /*BLOB ADDITION NEEDED*/NULL;
                     pColDataPtrs[index].SzDataObj  = /*BLOB ADDITION NEEDED*/sizeof(void *);