X-Git-Url: https://git.saurik.com/wxWidgets.git/blobdiff_plain/a1ee9ea29c3a00779d97be65b9f99e8f644b9a1a..29f7914424a7941e9374a5445f95574f2f041659:/src/common/dbtable.cpp diff --git a/src/common/dbtable.cpp b/src/common/dbtable.cpp index 5a888dec03..d1166229c4 100644 --- a/src/common/dbtable.cpp +++ b/src/common/dbtable.cpp @@ -1,5 +1,5 @@ /////////////////////////////////////////////////////////////////////////////// -// Name: table.cpp +// Name: dbtable.cpp // Purpose: Implementation of the wxTable class. // Author: Doug Card // Modified by: @@ -18,6 +18,10 @@ // the wxWindows GUI development toolkit. /////////////////////////////////////////////////////////////////////////////// +#ifdef __GNUG__ +#pragma implementation "dbtable.h" +#endif + /* // SYNOPSIS START // SYNOPSIS STOP @@ -46,14 +50,15 @@ #include #include #include +#include -#ifdef __WXUNIX__ +#ifdef __UNIX__ // The HPUX preprocessor lines below were commented out on 8/20/97 // because macros.h currently redefines DEBUG and is unneeded. // # ifdef HPUX // # include // # endif -# ifdef __WXLINUX__ +# ifdef LINUX # include # endif #endif @@ -63,16 +68,22 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char * { // Assign member variables pDb = pwxDB; // Pointer to the wxDB object + strcpy(tableName, tblName); // Table Name if (qryTblName) // Name of the table/view to query strcpy(queryTableName, qryTblName); else strcpy(queryTableName, tblName); - noCols = nCols; // No. of cols in the table - where = 0; // Where clause - orderBy = 0; // Order By clause - selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase + assert(pDb); // Assert is placed after table name is assigned for error reporting reasons + if (!pDb) + return; + + noCols = nCols; // No. of cols in the table + where = 0; // Where clause + orderBy = 0; // Order By clause + from = 0; // From clause + selectForUpdate = FALSE; // SELECT ... FOR UPDATE; Indicates whether to include the FOR UPDATE phrase // Grab the HENV and HDBC from the wxDB object henv = pDb->henv; @@ -121,7 +132,7 @@ wxTable::wxTable(wxDB *pwxDB, const char *tblName, const int nCols, const char * // Datasource does not support static cursors. Driver // will substitute a cursor type. Call SQLGetStmtOption() // to determine which cursor type was selected. - if (SQLGetStmtOption(c1, SQL_CURSOR_TYPE, &cursorType) != SQL_SUCCESS) + if (SQLGetStmtOption(c1, SQL_CURSOR_TYPE, (UCHAR*) &cursorType) != SQL_SUCCESS) pDb->DispAllErrors(henv, hdbc, c1); #ifdef _CONSOLE cout << "Static cursor changed to: "; @@ -214,15 +225,18 @@ wxTable::~wxTable() /********** wxTable::Open() **********/ bool wxTable::Open(void) { + if (!pDb) + return FALSE; + int i; char sqlStmt[DB_MAX_STATEMENT_LEN]; // Verify that the table exists in the database if (!pDb->TableExists(tableName)) { - wxString s; - s.Printf("Error opening '%s', table/view does not exist in the database.", tableName); - pDb->LogError(WXSTRINGCAST(s)); + char s[128]; + sprintf(s, "Error opening '%s', table/view does not exist in the database.", tableName); + pDb->LogError(s); return(FALSE); } @@ -272,6 +286,8 @@ bool wxTable::Open(void) } strcat(sqlStmt, ")"); + pDb->WriteSqlLog(sqlStmt); + // Prepare the insert statement for execution if (SQLPrepare(hstmtInsert, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); @@ -293,6 +309,7 @@ bool wxTable::Query(bool forUpdate, bool distinct) /********** wxTable::QueryBySqlStmt() **********/ bool wxTable::QueryBySqlStmt(char *pSqlStmt) { + pDb->WriteSqlLog(pSqlStmt); return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt)); @@ -328,12 +345,15 @@ bool wxTable::query(int queryType, bool forUpdate, bool distinct, char *pSqlStmt // Set the SQL SELECT string if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in, - GetSelectStmt(sqlStmt, queryType, distinct); // so generate a select statement. + { // so generate a select statement. + GetSelectStmt(sqlStmt, queryType, distinct); + pDb->WriteSqlLog(sqlStmt); + } // Make sure the cursor is closed first if (! CloseCursor(hstmt)) return(FALSE); - + // Execute the SQL SELECT statement if (SQLExecDirect(hstmt, (UCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt : sqlStmt), SQL_NTS) != SQL_SUCCESS) @@ -358,9 +378,21 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) if (distinct) strcat(pSqlStmt, "DISTINCT "); + // Was a FROM clause specified to join tables to the base table? + // Available for ::Query() only!!! + bool appendFromClause = FALSE; + if (typeOfSelect == DB_SELECT_WHERE && from && strlen(from)) + appendFromClause = TRUE; + // Add the column list for (int i = 0; i < noCols; i++) { + // If joining tables, the base table column names must be qualified to avoid ambiguity + if (appendFromClause) + { + strcat(pSqlStmt, queryTableName); + strcat(pSqlStmt, "."); + } strcat(pSqlStmt, colDefs[i].ColName); if (i + 1 < noCols) strcat(pSqlStmt, ","); @@ -369,11 +401,23 @@ void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct) // If the datasource supports ROWID, get this column as well. Exception: Don't retrieve // the ROWID if querying distinct records. The rowid will always be unique. if (!distinct && CanUpdByROWID()) - strcat(pSqlStmt, ",ROWID"); + { + // If joining tables, the base table column names must be qualified to avoid ambiguity + if (appendFromClause) + { + strcat(pSqlStmt, ","); + strcat(pSqlStmt, queryTableName); + strcat(pSqlStmt, ".ROWID"); + } + else + strcat(pSqlStmt, ",ROWID"); + } // Append the FROM tablename portion strcat(pSqlStmt, " FROM "); strcat(pSqlStmt, queryTableName); + if (appendFromClause) + strcat(pSqlStmt, from); // Append the WHERE clause. Either append the where clause for the class // or build a where clause. The typeOfSelect determines this. @@ -450,7 +494,7 @@ UWORD wxTable::GetRowNum(void) { UDWORD rowNum; - if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, &rowNum) != SQL_SUCCESS) + if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS) { pDb->DispAllErrors(henv, hdbc, hstmt); return(0); @@ -464,16 +508,13 @@ UWORD wxTable::GetRowNum(void) /********** wxTable::bindInsertParams() **********/ bool wxTable::bindInsertParams(void) { - SWORD fSqlType; - UDWORD precision; - SWORD scale; - -//glt CcolDef *tColDef; + SWORD fSqlType = 0; + UDWORD precision = 0; + SWORD scale = 0; // Bind each column (that can be inserted) of the table to a parameter marker for (int i = 0; i < noCols; i++) { -//glt tColDef = &colDefs[i]; if (! colDefs[i].InsertAllowed) continue; switch(colDefs[i].DbDataType) @@ -509,7 +550,7 @@ bool wxTable::bindInsertParams(void) break; } if (SQLBindParameter(hstmtInsert, i+1, SQL_PARAM_INPUT, colDefs[i].SqlCtype, - fSqlType, precision, scale, colDefs[i].PtrDataObj, + fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj, precision+1,&colDefs[i].CbValue) != SQL_SUCCESS) return(pDb->DispAllErrors(henv, hdbc, hstmtInsert)); } @@ -522,9 +563,9 @@ bool wxTable::bindInsertParams(void) /********** wxTable::bindUpdateParams() **********/ bool wxTable::bindUpdateParams(void) { - SWORD fSqlType; - UDWORD precision; - SWORD scale; + SWORD fSqlType = 0; + UDWORD precision = 0; + SWORD scale = 0; // Bind each UPDATEABLE column of the table to a parameter marker for (int i = 0, colNo = 1; i < noCols; i++) @@ -564,7 +605,7 @@ bool wxTable::bindUpdateParams(void) break; } if (SQLBindParameter(hstmtUpdate, colNo++, SQL_PARAM_INPUT, colDefs[i].SqlCtype, - fSqlType, precision, scale, colDefs[i].PtrDataObj, + fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj, precision+1, &colDefs[i].CbValue) != SQL_SUCCESS) return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate)); } @@ -582,7 +623,7 @@ bool wxTable::bindCols(HSTMT cursor) // Bind each column of the table to a memory address for fetching data for (int i = 0; i < noCols; i++) { - if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, colDefs[i].PtrDataObj, + if (SQLBindCol(cursor, i+1, colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj, colDefs[i].SzDataObj, &cb) != SQL_SUCCESS) return(pDb->DispAllErrors(henv, hdbc, cursor)); } @@ -606,6 +647,9 @@ bool wxTable::CloseCursor(HSTMT cursor) /********** wxTable::CreateTable() **********/ bool wxTable::CreateTable(void) { + if (!pDb) + return FALSE; + int i, j; char sqlStmt[DB_MAX_STATEMENT_LEN]; @@ -617,12 +661,24 @@ bool wxTable::CreateTable(void) sprintf(sqlStmt, "DROP TABLE %s", tableName); if (SQLExecDirect(hstmt, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) { - // Check for sqlState = S0002, "Table or view not found". - // Ignore this error, bomb out on any other error. - // SQL Sybase Anwhere v5.5 returns an access violation error here - // (sqlstate = 42000) rather than an S0002. + /* Check for sqlState = S0002, "Table or view not found". + * Ignore this error, bomb out on any other error. + * SQL Sybase Anwhere v5.5 returns an access violation error here + * (sqlstate = 42000) rather than an S0002. */ + + /* PostgreSQL 6.4.0 returns "08S01" or in written form + "ERROR: Relation ... Does Not Exist", Robert Roebling */ + + /* MySQL 3.23.33b returns "S1000" or in written form + "ERROR: Unknown table ...", Robert Roebling */ + + /* This routine is bullshit, Robert Roebling */ + pDb->GetNextError(henv, hdbc, hstmt); - if (strcmp(pDb->sqlState, "S0002") && strcmp(pDb->sqlState, "42000")) + if (strcmp(pDb->sqlState, "S0002") && + strcmp(pDb->sqlState, "S1000") && + strcmp(pDb->sqlState, "42000") && + strcmp(pDb->sqlState, "08S01")) { pDb->DispNextError(); pDb->DispAllErrors(henv, hdbc, hstmt); @@ -632,6 +688,8 @@ bool wxTable::CreateTable(void) } } + pDb->WriteSqlLog(sqlStmt); + // Commit the transaction and close the cursor if (! pDb->CommitTrans()) return(FALSE); @@ -694,13 +752,21 @@ bool wxTable::CreateTable(void) // For varchars, append the size of the string if (colDefs[i].DbDataType == DB_DATA_TYPE_VARCHAR) { + char s[10]; // strcat(sqlStmt, "("); // strcat(sqlStmt, itoa(colDefs[i].SzDataObj, s, 10)); // strcat(sqlStmt, ")"); - wxString s; - s.Printf("(%d)", colDefs[i].SzDataObj); + sprintf(s, "(%d)", colDefs[i].SzDataObj); strcat(sqlStmt, s); } + +#ifdef __WXGTK__ + if (colDefs[i].KeyField) + { + strcat(sqlStmt, " NOT NULL"); + } +#endif + needComma = TRUE; } // If there is a primary key defined, include it in the create statement @@ -714,9 +780,15 @@ bool wxTable::CreateTable(void) } if (j) // Found a keyfield { +#ifndef __WXGTK__ + /* MySQL goes out on this one. We also declare the relevant key NON NULL above */ strcat(sqlStmt, ",CONSTRAINT "); strcat(sqlStmt, tableName); strcat(sqlStmt, "_PIDX PRIMARY KEY ("); +#else + strcat(sqlStmt, ", PRIMARY KEY ("); +#endif + // List column name(s) of column(s) comprising the primary key for (i = j = 0; i < noCols; i++) { @@ -730,7 +802,9 @@ bool wxTable::CreateTable(void) strcat(sqlStmt, ")"); } // Append the closing parentheses for the create table statement - strcat(sqlStmt, ")"); + strcat(sqlStmt, ")"); + + pDb->WriteSqlLog(sqlStmt); #ifdef _CONSOLE cout << endl << sqlStmt << endl; @@ -776,17 +850,24 @@ bool wxTable::CreateIndex(char * idxName, bool unique, int noIdxCols, CidxDef *p for (int i = 0; i < noIdxCols; i++) { strcat(sqlStmt, pIdxDefs[i].ColName); + + /* Postgres doesnt cope with ASC */ +#ifndef __WXGTK__ if (pIdxDefs[i].Ascending) strcat(sqlStmt, " ASC"); else strcat(sqlStmt, " DESC"); +#endif + if ((i + 1) < noIdxCols) - strcat(sqlStmt, ","); + strcat(sqlStmt, ", "); } // Append closing parentheses strcat(sqlStmt, ")"); + pDb->WriteSqlLog(sqlStmt); + #ifdef _CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -837,6 +918,7 @@ int wxTable::Insert(void) /********** wxTable::Update(pSqlStmt) **********/ bool wxTable::Update(char *pSqlStmt) { + pDb->WriteSqlLog(pSqlStmt); return(execUpdate(pSqlStmt)); @@ -850,6 +932,8 @@ bool wxTable::Update(void) // Build the SQL UPDATE statement GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS); + pDb->WriteSqlLog(sqlStmt); + #ifdef _CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -867,6 +951,8 @@ bool wxTable::UpdateWhere(char *pWhereClause) // Build the SQL UPDATE statement GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause); + pDb->WriteSqlLog(sqlStmt); + #ifdef _CONSOLE cout << endl << sqlStmt << endl << endl; #endif @@ -884,6 +970,8 @@ bool wxTable::Delete(void) // Build the SQL DELETE statement GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS); + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL DELETE statement return(execDelete(sqlStmt)); @@ -897,6 +985,8 @@ bool wxTable::DeleteWhere(char *pWhereClause) // Build the SQL DELETE statement GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause); + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL DELETE statement return(execDelete(sqlStmt)); @@ -910,6 +1000,8 @@ bool wxTable::DeleteMatching(void) // Build the SQL DELETE statement GetDeleteStmt(sqlStmt, DB_DEL_MATCHING); + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL DELETE statement return(execDelete(sqlStmt)); @@ -979,7 +1071,7 @@ void wxTable::GetUpdateStmt(char *pSqlStmt, int typeOfUpd, char *pWhereClause) // Get the ROWID value. If not successful retreiving the ROWID, // simply fall down through the code and build the WHERE clause // based on the key fields. - if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, rowid, ROWID_LEN, &cb) == SQL_SUCCESS) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) { strcat(pSqlStmt, "ROWID = '"); strcat(pSqlStmt, rowid); @@ -1031,7 +1123,7 @@ void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) // Get the ROWID value. If not successful retreiving the ROWID, // simply fall down through the code and build the WHERE clause // based on the key fields. - if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, rowid, ROWID_LEN, &cb) == SQL_SUCCESS) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) { strcat(pSqlStmt, "ROWID = '"); strcat(pSqlStmt, rowid); @@ -1061,10 +1153,10 @@ void wxTable::GetDeleteStmt(char *pSqlStmt, int typeOfDel, char *pWhereClause) * They are not included as part of the where clause. */ -void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere) +void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere, char *qualTableName) { bool moreThanOneColumn = FALSE; - wxString colValue; + char colValue[255]; // Loop through the columns building a where clause as you go for (int i = 0; i < noCols; i++) @@ -1082,30 +1174,35 @@ void wxTable::GetWhereClause(char *pWhereClause, int typeOfWhere) else moreThanOneColumn = TRUE; // Concatenate where phrase for the column + if (qualTableName && strlen(qualTableName)) + { + strcat(pWhereClause, qualTableName); + strcat(pWhereClause, "."); + } strcat(pWhereClause, colDefs[i].ColName); strcat(pWhereClause, " = "); switch(colDefs[i].SqlCtype) { case SQL_C_CHAR: - colValue.Printf("'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj); + sprintf(colValue, "'%s'", (UCHAR FAR *) colDefs[i].PtrDataObj); break; case SQL_C_SSHORT: - colValue.Printf("%hi", *((SWORD *) colDefs[i].PtrDataObj)); + sprintf(colValue, "%hi", *((SWORD *) colDefs[i].PtrDataObj)); break; case SQL_C_USHORT: - colValue.Printf("%hu", *((UWORD *) colDefs[i].PtrDataObj)); + sprintf(colValue, "%hu", *((UWORD *) colDefs[i].PtrDataObj)); break; case SQL_C_SLONG: - colValue.Printf("%li", *((SDWORD *) colDefs[i].PtrDataObj)); + sprintf(colValue, "%li", *((SDWORD *) colDefs[i].PtrDataObj)); break; case SQL_C_ULONG: - colValue.Printf("%lu", *((UDWORD *) colDefs[i].PtrDataObj)); + sprintf(colValue, "%lu", *((UDWORD *) colDefs[i].PtrDataObj)); break; case SQL_C_FLOAT: - colValue.Printf("%.6f", *((SFLOAT *) colDefs[i].PtrDataObj)); + sprintf(colValue, "%.6f", *((SFLOAT *) colDefs[i].PtrDataObj)); break; case SQL_C_DOUBLE: - colValue.Printf("%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj)); + sprintf(colValue, "%.6f", *((SDOUBLE *) colDefs[i].PtrDataObj)); break; } strcat(pWhereClause, colValue); @@ -1150,9 +1247,11 @@ bool wxTable::IsColNull(int colNo) bool wxTable::CanSelectForUpdate(void) { +#ifndef __WXGTK__ if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE) return(TRUE); else +#endif return(FALSE); } // wxTable::CanSelectForUpdate() @@ -1161,7 +1260,8 @@ bool wxTable::CanSelectForUpdate(void) bool wxTable::CanUpdByROWID(void) { -//@@@@@@glt - returning FALSE for testing purposes, as the ROWID is not getting updated correctly +//NOTE: Returning FALSE for now until this can be debugged, +// as the ROWID is not getting updated correctly return FALSE; if ((! strcmp(pDb->dbInf.dbmsName, "Oracle")) || (! strcmp(pDb->dbInf.dbmsName, "ORACLE"))) @@ -1260,7 +1360,8 @@ void wxTable::SetColDefs (int index, char *fieldName, int dataType, void *pData, int cType, int size, bool keyField, bool upd, bool insAllow, bool derivedCol) { - if (strlen(fieldName) > DB_MAX_COLUMN_NAME_LEN) // glt 4/21/97 + // Please, no uint, it doesn't exist for VC++ + if (strlen(fieldName) > (unsigned int) DB_MAX_COLUMN_NAME_LEN) // glt 4/21/97 { strncpy (colDefs[index].ColName, fieldName, DB_MAX_COLUMN_NAME_LEN); colDefs[index].ColName[DB_MAX_COLUMN_NAME_LEN] = 0; // glt 10/23/97 @@ -1345,6 +1446,9 @@ ULONG wxTable::Count(void) strcpy(sqlStmt, "SELECT COUNT(*) FROM "); strcat(sqlStmt, queryTableName); + if (from && strlen(from)) + strcat(sqlStmt, from); + // Add the where clause if one is provided if (where && strlen(where)) { @@ -1352,6 +1456,8 @@ ULONG wxTable::Count(void) strcat(sqlStmt, where); } + pDb->WriteSqlLog(sqlStmt); + // Execute the SQL statement if (SQLExecDirect(hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS) { @@ -1367,7 +1473,7 @@ ULONG wxTable::Count(void) } // Obtain the result - if (SQLGetData(hstmtCount, 1, SQL_C_ULONG, &l, sizeof(l), &cb) != SQL_SUCCESS) + if (SQLGetData(hstmtCount, 1, SQL_C_ULONG, (UCHAR*) &l, sizeof(l), &cb) != SQL_SUCCESS) { pDb->DispAllErrors(henv, hdbc, hstmtCount); return(0); @@ -1408,9 +1514,10 @@ bool wxTable::Refresh(void) // Get the ROWID value. If not successful retreiving the ROWID, // simply fall down through the code and build the WHERE clause // based on the key fields. - if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, rowid, ROWID_LEN, &cb) == SQL_SUCCESS) + if (SQLGetData(hstmt, noCols+1, SQL_C_CHAR, (UCHAR*) rowid, ROWID_LEN, &cb) == SQL_SUCCESS) { - strcat(whereClause, "ROWID = '"); + strcat(whereClause, queryTableName); + strcat(whereClause, ".ROWID = '"); strcat(whereClause, rowid); strcat(whereClause, "'"); } @@ -1418,7 +1525,7 @@ bool wxTable::Refresh(void) // If unable to use the ROWID, build a where clause from the keyfields if (strlen(whereClause) == 0) - GetWhereClause(whereClause, DB_WHERE_KEYFIELDS); + GetWhereClause(whereClause, DB_WHERE_KEYFIELDS, queryTableName); // Requery the record where = whereClause; @@ -1442,4 +1549,5 @@ bool wxTable::Refresh(void) } // wxTable::Refresh() #endif - // wxUSE_ODBC + // wxUSE_ODBC +