- // Build an insert statement using parameter markers
- if (!queryOnly && noCols > 0)
- {
- bool needComma = FALSE;
- sprintf(sqlStmt, "INSERT INTO %s (", tableName);
- for (i = 0; i < noCols; i++)
- {
- if (! colDefs[i].InsertAllowed)
- continue;
- if (needComma)
- wxStrcat(sqlStmt, ",");
- wxStrcat(sqlStmt, colDefs[i].ColName);
- needComma = TRUE;
- }
- needComma = FALSE;
- wxStrcat(sqlStmt, ") VALUES (");
- for (i = 0; i < noCols; i++)
- {
- if (! colDefs[i].InsertAllowed)
- continue;
- if (needComma)
- wxStrcat(sqlStmt, ",");
- wxStrcat(sqlStmt, "?");
- needComma = TRUE;
- }
- wxStrcat(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));
- }
-
- // Completed successfully
- return(TRUE);
-
-} // wxTable::Open()
-
-/********** wxTable::Query() **********/
-bool wxTable::Query(bool forUpdate, bool distinct)
-{
-
- return(query(DB_SELECT_WHERE, forUpdate, distinct));
-
-} // wxTable::Query()
-
-/********** wxTable::QueryBySqlStmt() **********/
-bool wxTable::QueryBySqlStmt(char *pSqlStmt)
-{
- pDb->WriteSqlLog(pSqlStmt);
-
- return(query(DB_SELECT_STATEMENT, FALSE, FALSE, pSqlStmt));
-
-} // wxTable::QueryBySqlStmt()
-
-/********** wxTable::QueryMatching() **********/
-bool wxTable::QueryMatching(bool forUpdate, bool distinct)
-{
-
- return(query(DB_SELECT_MATCHING, forUpdate, distinct));
-
-} // wxTable::QueryMatching()
-
-/********** wxTable::QueryOnKeyFields() **********/
-bool wxTable::QueryOnKeyFields(bool forUpdate, bool distinct)
-{
-
- return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
-
-} // wxTable::QueryOnKeyFields()
-
-/********** wxTable::GetPrev() **********/
-bool wxTable::GetPrev(void)
-{
- if (pDb->FwdOnlyCursors())
- {
- wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxTable"));
- return FALSE;
- }
- else
- return(getRec(SQL_FETCH_PRIOR));
-} // wxTable::GetPrev()
-
-/********** wxTable::operator-- **********/
-bool wxTable::operator--(int)
-{
- if (pDb->FwdOnlyCursors())
- {
- wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxTable"));
- return FALSE;
- }
- else
- return(getRec(SQL_FETCH_PRIOR));
-} // wxTable::operator--
-
-/********** wxTable::GetFirst() **********/
-bool wxTable::GetFirst(void)
-{
- if (pDb->FwdOnlyCursors())
- {
- wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxTable"));
- return FALSE;
- }
- else
- return(getRec(SQL_FETCH_FIRST));
-} // wxTable::GetFirst()
-
-/********** wxTable::GetLast() **********/
-bool wxTable::GetLast(void)
-{
- if (pDb->FwdOnlyCursors())
- {
- wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxTable"));
- return FALSE;
- }
- else
- return(getRec(SQL_FETCH_LAST));
-} // wxTable::GetLast()
-
-/********** wxTable::GetSelectStmt() **********/
-void wxTable::GetSelectStmt(char *pSqlStmt, int typeOfSelect, bool distinct)
-{
- char whereClause[DB_MAX_WHERE_CLAUSE_LEN];
-
- whereClause[0] = 0;
-
- // Build a select statement to query the database
- wxStrcpy(pSqlStmt, "SELECT ");
-
- // SELECT DISTINCT values only?
- if (distinct)
- wxStrcat(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 && wxStrlen(from))
- appendFromClause = TRUE;
-
- // Add the column list
- int i;
- for (i = 0; i < noCols; i++)
- {
- // If joining tables, the base table column names must be qualified to avoid ambiguity
- if (appendFromClause)
- {
- wxStrcat(pSqlStmt, queryTableName);
- wxStrcat(pSqlStmt, ".");
- }
- wxStrcat(pSqlStmt, colDefs[i].ColName);
- if (i + 1 < noCols)
- wxStrcat(pSqlStmt, ",");
- }
-
- // 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())
- {
- // If joining tables, the base table column names must be qualified to avoid ambiguity
- if (appendFromClause)
- {
- wxStrcat(pSqlStmt, ",");
- wxStrcat(pSqlStmt, queryTableName);
- wxStrcat(pSqlStmt, ".ROWID");
- }
- else
- wxStrcat(pSqlStmt, ",ROWID");
- }
-
- // Append the FROM tablename portion
- wxStrcat(pSqlStmt, " FROM ");
- wxStrcat(pSqlStmt, queryTableName);
-
- // Sybase uses the HOLDLOCK keyword to lock a record during query.
- // The HOLDLOCK keyword follows the table name in the from clause.
- // Each table in the from clause must specify HOLDLOCK or
- // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
- // is parsed but ignored in SYBASE Transact-SQL.
- if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
- wxStrcat(pSqlStmt, " HOLDLOCK");
-
- if (appendFromClause)
- wxStrcat(pSqlStmt, from);
-
- // Append the WHERE clause. Either append the where clause for the class
- // or build a where clause. The typeOfSelect determines this.
- switch(typeOfSelect)
- {
- case DB_SELECT_WHERE:
- if (where && wxStrlen(where)) // May not want a where clause!!!
- {
- wxStrcat(pSqlStmt, " WHERE ");
- wxStrcat(pSqlStmt, where);
- }
- break;
- case DB_SELECT_KEYFIELDS:
- GetWhereClause(whereClause, DB_WHERE_KEYFIELDS);
- if (wxStrlen(whereClause))
- {
- wxStrcat(pSqlStmt, " WHERE ");
- wxStrcat(pSqlStmt, whereClause);
- }
- break;
- case DB_SELECT_MATCHING:
- GetWhereClause(whereClause, DB_WHERE_MATCHING);
- if (wxStrlen(whereClause))
- {
- wxStrcat(pSqlStmt, " WHERE ");
- wxStrcat(pSqlStmt, whereClause);
- }
- break;
- }
-
- // Append the ORDER BY clause
- if (orderBy && wxStrlen(orderBy))
- {
- wxStrcat(pSqlStmt, " ORDER BY ");
- wxStrcat(pSqlStmt, orderBy);
- }
-
- // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
- // parses the FOR UPDATE clause but ignores it. See the comment above on the
- // HOLDLOCK for Sybase.
- if (selectForUpdate && CanSelectForUpdate())
- wxStrcat(pSqlStmt, " FOR UPDATE");
-
-} // wxTable::GetSelectStmt()
-
-/********** wxTable::GetRowNum() **********/
-UWORD wxTable::GetRowNum(void)
-{
- UDWORD rowNum;
-
- if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
- {
- pDb->DispAllErrors(henv, hdbc, hstmt);
- return(0);
- }
-
- // Completed successfully
- return((UWORD) rowNum);
-
-} // wxTable::GetRowNum()
-
-/********** wxTable::CloseCursor() **********/
-bool wxTable::CloseCursor(HSTMT cursor)
-{
- if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
- return(pDb->DispAllErrors(henv, hdbc, cursor));
-
- // Completed successfully
- return(TRUE);
-
-} // wxTable::CloseCursor()
-
-/********** wxTable::CreateTable() **********/
-bool wxTable::CreateTable(bool attemptDrop)
-{
- if (!pDb)
- return FALSE;
-
- int i, j;
- char sqlStmt[DB_MAX_STATEMENT_LEN];
+void wxDbTable::setCbValueForColumn(int columnIndex)
+{
+ switch(colDefs[columnIndex].DbDataType)
+ {
+ case DB_DATA_TYPE_VARCHAR:
+ case DB_DATA_TYPE_MEMO:
+ if (colDefs[columnIndex].Null)
+ colDefs[columnIndex].CbValue = SQL_NULL_DATA;
+ else
+ colDefs[columnIndex].CbValue = SQL_NTS;
+ break;
+ case DB_DATA_TYPE_INTEGER:
+ if (colDefs[columnIndex].Null)
+ colDefs[columnIndex].CbValue = SQL_NULL_DATA;
+ else
+ colDefs[columnIndex].CbValue = 0;
+ break;
+ case DB_DATA_TYPE_FLOAT:
+ if (colDefs[columnIndex].Null)
+ colDefs[columnIndex].CbValue = SQL_NULL_DATA;
+ else
+ colDefs[columnIndex].CbValue = 0;
+ break;
+ case DB_DATA_TYPE_DATE:
+ if (colDefs[columnIndex].Null)
+ colDefs[columnIndex].CbValue = SQL_NULL_DATA;
+ else
+ colDefs[columnIndex].CbValue = 0;
+ break;
+ case DB_DATA_TYPE_BLOB:
+ if (colDefs[columnIndex].Null)
+ colDefs[columnIndex].CbValue = SQL_NULL_DATA;
+ else
+ if (colDefs[columnIndex].SqlCtype == SQL_C_WXCHAR)
+ colDefs[columnIndex].CbValue = SQL_NTS;
+ else
+ colDefs[columnIndex].CbValue = SQL_LEN_DATA_AT_EXEC(colDefs[columnIndex].SzDataObj);
+ break;
+ }
+}
+
+/********** wxDbTable::bindParams() **********/
+bool wxDbTable::bindParams(bool forUpdate)
+{
+ wxASSERT(!queryOnly);
+ if (queryOnly)
+ return false;
+
+ SWORD fSqlType = 0;
+ SDWORD precision = 0;
+ SWORD scale = 0;
+
+ // Bind each column of the table that should be bound
+ // to a parameter marker
+ int i;
+ UWORD colNumber;
+
+ for (i=0, colNumber=1; i < m_numCols; i++)
+ {
+ if (forUpdate)
+ {
+ if (!colDefs[i].Updateable)
+ continue;
+ }
+ else
+ {
+ if (!colDefs[i].InsertAllowed)
+ continue;
+ }
+
+ switch(colDefs[i].DbDataType)
+ {
+ case DB_DATA_TYPE_VARCHAR:
+ fSqlType = pDb->GetTypeInfVarchar().FsqlType;
+ precision = colDefs[i].SzDataObj;
+ scale = 0;
+ break;
+ case DB_DATA_TYPE_MEMO:
+ fSqlType = pDb->GetTypeInfMemo().FsqlType;
+ precision = colDefs[i].SzDataObj;
+ scale = 0;
+ break;
+ case DB_DATA_TYPE_INTEGER:
+ fSqlType = pDb->GetTypeInfInteger().FsqlType;
+ precision = pDb->GetTypeInfInteger().Precision;
+ scale = 0;
+ break;
+ case DB_DATA_TYPE_FLOAT:
+ fSqlType = pDb->GetTypeInfFloat().FsqlType;
+ precision = pDb->GetTypeInfFloat().Precision;
+ scale = pDb->GetTypeInfFloat().MaximumScale;
+ // SQL Sybase Anywhere v5.5 returned a negative number for the
+ // MaxScale. This caused ODBC to kick out an error on ibscale.
+ // I check for this here and set the scale = precision.
+ //if (scale < 0)
+ // scale = (short) precision;
+ break;
+ case DB_DATA_TYPE_DATE:
+ fSqlType = pDb->GetTypeInfDate().FsqlType;
+ precision = pDb->GetTypeInfDate().Precision;
+ scale = 0;
+ break;
+ case DB_DATA_TYPE_BLOB:
+ fSqlType = pDb->GetTypeInfBlob().FsqlType;
+ precision = colDefs[i].SzDataObj;
+ scale = 0;
+ break;
+ }
+
+ setCbValueForColumn(i);
+
+ if (forUpdate)
+ {
+ if (SQLBindParameter(hstmtUpdate, colNumber++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
+ fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
+ precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
+ {
+ return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
+ }
+ }
+ else
+ {
+ if (SQLBindParameter(hstmtInsert, colNumber++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
+ fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
+ precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
+ {
+ return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
+ }
+ }
+ }
+
+ // Completed successfully
+ return true;
+
+} // wxDbTable::bindParams()
+
+
+/********** wxDbTable::bindInsertParams() **********/
+bool wxDbTable::bindInsertParams(void)
+{
+ return bindParams(false);
+} // wxDbTable::bindInsertParams()
+
+
+/********** wxDbTable::bindUpdateParams() **********/
+bool wxDbTable::bindUpdateParams(void)
+{
+ return bindParams(true);
+} // wxDbTable::bindUpdateParams()
+
+
+/********** wxDbTable::bindCols() **********/
+bool wxDbTable::bindCols(HSTMT cursor)
+{
+ // Bind each column of the table to a memory address for fetching data
+ UWORD i;
+ for (i = 0; i < m_numCols; i++)
+ {
+ if (SQLBindCol(cursor, (UWORD)(i+1), colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
+ colDefs[i].SzDataObj, &colDefs[i].CbValue ) != SQL_SUCCESS)
+ return (pDb->DispAllErrors(henv, hdbc, cursor));
+ }
+
+ // Completed successfully
+ return true;
+} // wxDbTable::bindCols()
+
+
+/********** wxDbTable::getRec() **********/
+bool wxDbTable::getRec(UWORD fetchType)
+{
+ RETCODE retcode;
+
+ if (!pDb->FwdOnlyCursors())
+ {
+ // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
+ SQLULEN cRowsFetched;
+ UWORD rowStatus;
+
+ retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
+ if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
+ {
+ if (retcode == SQL_NO_DATA_FOUND)
+ return false;
+ else
+ return(pDb->DispAllErrors(henv, hdbc, hstmt));
+ }
+ else
+ {
+ // Set the Null member variable to indicate the Null state
+ // of each column just read in.
+ int i;
+ for (i = 0; i < m_numCols; i++)
+ colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
+ }
+ }
+ else
+ {
+ // Fetch the next record from the record set
+ retcode = SQLFetch(hstmt);
+ if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
+ {
+ if (retcode == SQL_NO_DATA_FOUND)
+ return false;
+ else
+ return(pDb->DispAllErrors(henv, hdbc, hstmt));
+ }
+ else
+ {
+ // Set the Null member variable to indicate the Null state
+ // of each column just read in.
+ int i;
+ for (i = 0; i < m_numCols; i++)
+ colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
+ }
+ }
+
+ // Completed successfully
+ return true;
+
+} // wxDbTable::getRec()
+
+
+/********** wxDbTable::execDelete() **********/
+bool wxDbTable::execDelete(const wxString &pSqlStmt)
+{
+ RETCODE retcode;
+
+ // Execute the DELETE statement
+ retcode = SQLExecDirect(hstmtDelete, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
+
+ 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()
+
+
+/********** wxDbTable::execUpdate() **********/
+bool wxDbTable::execUpdate(const wxString &pSqlStmt)
+{
+ RETCODE retcode;
+
+ // Execute the UPDATE statement
+ retcode = SQLExecDirect(hstmtUpdate, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);
+
+ if (retcode == SQL_SUCCESS ||
+ retcode == SQL_NO_DATA_FOUND ||
+ retcode == SQL_SUCCESS_WITH_INFO)
+ {
+ // Record updated successfully
+ return true;
+ }
+ else if (retcode == SQL_NEED_DATA)
+ {
+ PTR pParmID;
+ retcode = SQLParamData(hstmtUpdate, &pParmID);
+ while (retcode == SQL_NEED_DATA)
+ {
+ // Find the parameter
+ int i;
+ for (i=0; i < m_numCols; i++)
+ {
+ if (colDefs[i].PtrDataObj == pParmID)
+ {
+ // We found it. Store the parameter.
+ retcode = SQLPutData(hstmtUpdate, pParmID, colDefs[i].SzDataObj);
+ if (retcode != SQL_SUCCESS)
+ {
+ pDb->DispNextError();
+ return pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
+ }
+ break;
+ }
+ }
+ retcode = SQLParamData(hstmtUpdate, &pParmID);
+ }
+ 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()
+
+
+/********** wxDbTable::query() **********/
+bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const wxString &pSqlStmt)
+{
+ wxString sqlStmt;
+
+ if (forUpdate)
+ // The user may wish to select for update, but the DBMS may not be capable
+ selectForUpdate = CanSelectForUpdate();
+ else
+ selectForUpdate = false;
+
+ // Set the SQL SELECT string
+ if (queryType != DB_SELECT_STATEMENT) // A select statement was not passed in,
+ { // so generate a select statement.
+ BuildSelectStmt(sqlStmt, queryType, distinct);
+ pDb->WriteSqlLog(sqlStmt);
+ }
+
+ // Make sure the cursor is closed first
+ if (!CloseCursor(hstmt))
+ return false;
+
+ // Execute the SQL SELECT statement
+ int retcode;
+ retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt.c_str() : sqlStmt.c_str()), SQL_NTS);
+ if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
+ return(pDb->DispAllErrors(henv, hdbc, hstmt));
+
+ // Completed successfully
+ return true;
+
+} // wxDbTable::query()
+
+
+/***************************** PUBLIC FUNCTIONS *****************************/
+
+
+/********** wxDbTable::Open() **********/
+bool wxDbTable::Open(bool checkPrivileges, bool checkTableExists)
+{
+ if (!pDb)
+ return false;
+
+ int i;
+ wxString sqlStmt;
+ wxString s;
+
+ // Calculate the maximum size of the concatenated
+ // keys for use with wxDbGrid
+ m_keysize = 0;
+ for (i=0; i < m_numCols; i++)
+ {
+ if (colDefs[i].KeyField)
+ {
+ m_keysize += colDefs[i].SzDataObj;
+ }
+ }
+
+ s.Empty();
+
+ bool exists = true;
+ if (checkTableExists)
+ {
+ if (pDb->Dbms() == dbmsPOSTGRES)
+ exists = pDb->TableExists(tableName, NULL, tablePath);
+ else
+ exists = pDb->TableExists(tableName, pDb->GetUsername(), tablePath);
+ }
+
+ // Verify that the table exists in the database
+ if (!exists)
+ {
+ s = wxT("Table/view does not exist in the database");
+ if ( *(pDb->dbInf.accessibleTables) == wxT('Y'))
+ s += wxT(", or you have no permissions.\n");
+ else
+ s += wxT(".\n");
+ }
+ else if (checkPrivileges)
+ {
+ // Verify the user has rights to access the table.
+ bool hasPrivs wxDUMMY_INITIALIZE(true);
+
+ if (pDb->Dbms() == dbmsPOSTGRES)
+ hasPrivs = pDb->TablePrivileges(tableName, wxT("SELECT"), pDb->GetUsername(), NULL, tablePath);
+ else
+ hasPrivs = pDb->TablePrivileges(tableName, wxT("SELECT"), pDb->GetUsername(), pDb->GetUsername(), tablePath);
+
+ if (!hasPrivs)
+ s = wxT("Connecting user does not have sufficient privileges to access this table.\n");
+ }
+
+ if (!s.empty())
+ {
+ wxString p;
+
+ if (!tablePath.empty())
+ p.Printf(wxT("Error opening '%s/%s'.\n"),tablePath.c_str(),tableName.c_str());
+ else
+ p.Printf(wxT("Error opening '%s'.\n"), tableName.c_str());
+
+ p += s;
+ pDb->LogError(p.GetData());
+
+ return false;
+ }
+
+ // Bind the member variables for field exchange between
+ // the wxDbTable object and the ODBC record.
+ if (!queryOnly)
+ {
+ if (!bindInsertParams()) // Inserts
+ return false;
+
+ if (!bindUpdateParams()) // Updates
+ return false;
+ }
+
+ if (!bindCols(*hstmtDefault)) // Selects
+ return false;
+
+ if (!bindCols(hstmtInternal)) // Internal use only
+ return false;
+
+ /*
+ * Do NOT bind the hstmtCount cursor!!!
+ */
+
+ // Build an insert statement using parameter markers
+ if (!queryOnly && m_numCols > 0)
+ {
+ bool needComma = false;
+ sqlStmt.Printf(wxT("INSERT INTO %s ("),
+ pDb->SQLTableName(tableName.c_str()).c_str());
+ for (i = 0; i < m_numCols; i++)
+ {
+ if (! colDefs[i].InsertAllowed)
+ continue;
+ if (needComma)
+ sqlStmt += wxT(",");
+ sqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
+ needComma = true;
+ }
+ needComma = false;
+ sqlStmt += wxT(") VALUES (");
+
+ int insertableCount = 0;
+
+ for (i = 0; i < m_numCols; i++)
+ {
+ if (! colDefs[i].InsertAllowed)
+ continue;
+ if (needComma)
+ sqlStmt += wxT(",");
+ sqlStmt += wxT("?");
+ needComma = true;
+ insertableCount++;
+ }
+ sqlStmt += wxT(")");
+
+ // Prepare the insert statement for execution
+ if (insertableCount)
+ {
+ if (SQLPrepare(hstmtInsert, (SQLTCHAR FAR *) sqlStmt.c_str(), SQL_NTS) != SQL_SUCCESS)
+ return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
+ }
+ else
+ insertable = false;
+ }
+
+ // Completed successfully
+ return true;
+
+} // wxDbTable::Open()
+
+
+/********** wxDbTable::Query() **********/
+bool wxDbTable::Query(bool forUpdate, bool distinct)
+{
+
+ return(query(DB_SELECT_WHERE, forUpdate, distinct));
+
+} // wxDbTable::Query()
+
+
+/********** wxDbTable::QueryBySqlStmt() **********/
+bool wxDbTable::QueryBySqlStmt(const wxString &pSqlStmt)
+{
+ pDb->WriteSqlLog(pSqlStmt);
+
+ return(query(DB_SELECT_STATEMENT, false, false, pSqlStmt));
+
+} // wxDbTable::QueryBySqlStmt()
+
+
+/********** wxDbTable::QueryMatching() **********/
+bool wxDbTable::QueryMatching(bool forUpdate, bool distinct)
+{
+
+ return(query(DB_SELECT_MATCHING, forUpdate, distinct));
+
+} // wxDbTable::QueryMatching()
+
+
+/********** wxDbTable::QueryOnKeyFields() **********/
+bool wxDbTable::QueryOnKeyFields(bool forUpdate, bool distinct)
+{
+
+ return(query(DB_SELECT_KEYFIELDS, forUpdate, distinct));
+
+} // wxDbTable::QueryOnKeyFields()
+
+
+/********** wxDbTable::GetPrev() **********/
+bool wxDbTable::GetPrev(void)
+{
+ if (pDb->FwdOnlyCursors())
+ {
+ wxFAIL_MSG(wxT("GetPrev()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
+ return false;
+ }
+ else
+ return(getRec(SQL_FETCH_PRIOR));
+
+} // wxDbTable::GetPrev()
+
+
+/********** wxDbTable::operator-- **********/
+bool wxDbTable::operator--(int)
+{
+ if (pDb->FwdOnlyCursors())
+ {
+ wxFAIL_MSG(wxT("operator--:Backward scrolling cursors are not enabled for this instance of wxDbTable"));
+ return false;
+ }
+ else
+ return(getRec(SQL_FETCH_PRIOR));
+
+} // wxDbTable::operator--
+
+
+/********** wxDbTable::GetFirst() **********/
+bool wxDbTable::GetFirst(void)
+{
+ if (pDb->FwdOnlyCursors())
+ {
+ wxFAIL_MSG(wxT("GetFirst():Backward scrolling cursors are not enabled for this instance of wxDbTable"));
+ return false;
+ }
+ else
+ return(getRec(SQL_FETCH_FIRST));
+
+} // wxDbTable::GetFirst()
+
+
+/********** wxDbTable::GetLast() **********/
+bool wxDbTable::GetLast(void)
+{
+ if (pDb->FwdOnlyCursors())
+ {
+ wxFAIL_MSG(wxT("GetLast()::Backward scrolling cursors are not enabled for this instance of wxDbTable"));
+ return false;
+ }
+ else
+ return(getRec(SQL_FETCH_LAST));
+
+} // wxDbTable::GetLast()
+
+
+/********** wxDbTable::BuildDeleteStmt() **********/
+void wxDbTable::BuildDeleteStmt(wxString &pSqlStmt, int typeOfDel, const wxString &pWhereClause)
+{
+ wxASSERT(!queryOnly);
+ if (queryOnly)
+ return;
+
+ wxString whereClause;
+
+ whereClause.Empty();
+
+ // Handle the case of DeleteWhere() and the where clause is blank. It should
+ // delete all records from the database in this case.
+ if (typeOfDel == DB_DEL_WHERE && (pWhereClause.length() == 0))
+ {
+ pSqlStmt.Printf(wxT("DELETE FROM %s"),
+ pDb->SQLTableName(tableName.c_str()).c_str());
+ return;
+ }
+
+ pSqlStmt.Printf(wxT("DELETE FROM %s WHERE "),
+ pDb->SQLTableName(tableName.c_str()).c_str());
+
+ // Append the WHERE clause to the SQL DELETE statement
+ switch(typeOfDel)
+ {
+ case DB_DEL_KEYFIELDS:
+ // If the datasource supports the ROWID column, build
+ // the where on ROWID for efficiency purposes.
+ // e.g. DELETE FROM PARTS WHERE ROWID = '111.222.333'
+ if (CanUpdateByROWID())
+ {
+ SQLLEN cb;
+ wxChar rowid[wxDB_ROWID_LEN+1];
+
+ // 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, (UWORD)(m_numCols+1), SQL_C_WXCHAR, (UCHAR*) rowid, sizeof(rowid), &cb) == SQL_SUCCESS)
+ {
+ pSqlStmt += wxT("ROWID = '");
+ pSqlStmt += rowid;
+ pSqlStmt += wxT("'");
+ break;
+ }
+ }
+ // Unable to delete by ROWID, so build a WHERE
+ // clause based on the keyfields.
+ BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
+ pSqlStmt += whereClause;
+ break;
+ case DB_DEL_WHERE:
+ pSqlStmt += pWhereClause;
+ break;
+ case DB_DEL_MATCHING:
+ BuildWhereClause(whereClause, DB_WHERE_MATCHING);
+ pSqlStmt += whereClause;
+ break;
+ }
+
+} // BuildDeleteStmt()
+
+
+/***** DEPRECATED: use wxDbTable::BuildDeleteStmt(wxString &....) form *****/
+void wxDbTable::BuildDeleteStmt(wxChar *pSqlStmt, int typeOfDel, const wxString &pWhereClause)
+{
+ wxString tempSqlStmt;
+ BuildDeleteStmt(tempSqlStmt, typeOfDel, pWhereClause);
+ wxStrcpy(pSqlStmt, tempSqlStmt);
+} // wxDbTable::BuildDeleteStmt()
+
+
+/********** wxDbTable::BuildSelectStmt() **********/
+void wxDbTable::BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool distinct)
+{
+ wxString whereClause;
+ whereClause.Empty();
+
+ // Build a select statement to query the database
+ pSqlStmt = wxT("SELECT ");
+
+ // SELECT DISTINCT values only?
+ if (distinct)
+ pSqlStmt += wxT("DISTINCT ");
+
+ // Was a FROM clause specified to join tables to the base table?
+ // Available for ::Query() only!!!
+ bool appendFromClause = false;
+#if wxODBC_BACKWARD_COMPATABILITY
+ if (typeOfSelect == DB_SELECT_WHERE && from && wxStrlen(from))
+ appendFromClause = true;
+#else
+ if (typeOfSelect == DB_SELECT_WHERE && from.length())
+ appendFromClause = true;
+#endif
+
+ // Add the column list
+ int i;
+ wxString tStr;
+ for (i = 0; i < m_numCols; i++)
+ {
+ tStr = colDefs[i].ColName;
+ // If joining tables, the base table column names must be qualified to avoid ambiguity
+ if ((appendFromClause || pDb->Dbms() == dbmsACCESS) && tStr.Find(wxT('.')) == wxNOT_FOUND)
+ {
+ pSqlStmt += pDb->SQLTableName(queryTableName.c_str());
+ pSqlStmt += wxT(".");
+ }
+ pSqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
+ if (i + 1 < m_numCols)
+ pSqlStmt += wxT(",");
+ }
+
+ // 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 && CanUpdateByROWID())
+ {
+ // If joining tables, the base table column names must be qualified to avoid ambiguity
+ if (appendFromClause || pDb->Dbms() == dbmsACCESS)
+ {
+ pSqlStmt += wxT(",");
+ pSqlStmt += pDb->SQLTableName(queryTableName);
+ pSqlStmt += wxT(".ROWID");
+ }
+ else
+ pSqlStmt += wxT(",ROWID");
+ }
+
+ // Append the FROM tablename portion
+ pSqlStmt += wxT(" FROM ");
+ pSqlStmt += pDb->SQLTableName(queryTableName);
+// pSqlStmt += queryTableName;
+
+ // Sybase uses the HOLDLOCK keyword to lock a record during query.
+ // The HOLDLOCK keyword follows the table name in the from clause.
+ // Each table in the from clause must specify HOLDLOCK or
+ // NOHOLDLOCK (the default). Note: The "FOR UPDATE" clause
+ // is parsed but ignored in SYBASE Transact-SQL.
+ if (selectForUpdate && (pDb->Dbms() == dbmsSYBASE_ASA || pDb->Dbms() == dbmsSYBASE_ASE))
+ pSqlStmt += wxT(" HOLDLOCK");
+
+ if (appendFromClause)
+ pSqlStmt += from;
+
+ // Append the WHERE clause. Either append the where clause for the class
+ // or build a where clause. The typeOfSelect determines this.
+ switch(typeOfSelect)
+ {
+ case DB_SELECT_WHERE:
+#if wxODBC_BACKWARD_COMPATABILITY
+ if (where && wxStrlen(where)) // May not want a where clause!!!
+#else
+ if (where.length()) // May not want a where clause!!!
+#endif
+ {
+ pSqlStmt += wxT(" WHERE ");
+ pSqlStmt += where;
+ }
+ break;
+ case DB_SELECT_KEYFIELDS:
+ BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
+ if (whereClause.length())
+ {
+ pSqlStmt += wxT(" WHERE ");
+ pSqlStmt += whereClause;
+ }
+ break;
+ case DB_SELECT_MATCHING:
+ BuildWhereClause(whereClause, DB_WHERE_MATCHING);
+ if (whereClause.length())
+ {
+ pSqlStmt += wxT(" WHERE ");
+ pSqlStmt += whereClause;
+ }
+ break;
+ }
+
+ // Append the ORDER BY clause
+#if wxODBC_BACKWARD_COMPATABILITY
+ if (orderBy && wxStrlen(orderBy))
+#else
+ if (orderBy.length())
+#endif
+ {
+ pSqlStmt += wxT(" ORDER BY ");
+ pSqlStmt += orderBy;
+ }
+
+ // SELECT FOR UPDATE if told to do so and the datasource is capable. Sybase
+ // parses the FOR UPDATE clause but ignores it. See the comment above on the
+ // HOLDLOCK for Sybase.
+ if (selectForUpdate && CanSelectForUpdate())
+ pSqlStmt += wxT(" FOR UPDATE");
+
+} // wxDbTable::BuildSelectStmt()
+
+
+/***** DEPRECATED: use wxDbTable::BuildSelectStmt(wxString &....) form *****/
+void wxDbTable::BuildSelectStmt(wxChar *pSqlStmt, int typeOfSelect, bool distinct)
+{
+ wxString tempSqlStmt;
+ BuildSelectStmt(tempSqlStmt, typeOfSelect, distinct);
+ wxStrcpy(pSqlStmt, tempSqlStmt);
+} // wxDbTable::BuildSelectStmt()
+
+
+/********** wxDbTable::BuildUpdateStmt() **********/
+void wxDbTable::BuildUpdateStmt(wxString &pSqlStmt, int typeOfUpdate, const wxString &pWhereClause)
+{
+ wxASSERT(!queryOnly);
+ if (queryOnly)
+ return;
+
+ wxString whereClause;
+ whereClause.Empty();
+
+ bool firstColumn = true;
+
+ pSqlStmt.Printf(wxT("UPDATE %s SET "),
+ pDb->SQLTableName(tableName.c_str()).c_str());
+
+ // Append a list of columns to be updated
+ int i;
+ for (i = 0; i < m_numCols; i++)
+ {
+ // Only append Updateable columns
+ if (colDefs[i].Updateable)
+ {
+ if (!firstColumn)
+ pSqlStmt += wxT(",");
+ else
+ firstColumn = false;
+
+ pSqlStmt += pDb->SQLColumnName(colDefs[i].ColName);
+// pSqlStmt += colDefs[i].ColName;
+ pSqlStmt += wxT(" = ?");
+ }
+ }
+
+ // Append the WHERE clause to the SQL UPDATE statement
+ pSqlStmt += wxT(" WHERE ");
+ switch(typeOfUpdate)
+ {
+ case DB_UPD_KEYFIELDS:
+ // If the datasource supports the ROWID column, build
+ // the where on ROWID for efficiency purposes.
+ // e.g. UPDATE PARTS SET Col1 = ?, Col2 = ? WHERE ROWID = '111.222.333'
+ if (CanUpdateByROWID())
+ {
+ SQLLEN cb;
+ wxChar rowid[wxDB_ROWID_LEN+1];
+
+ // 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, (UWORD)(m_numCols+1), SQL_C_WXCHAR, (UCHAR*) rowid, sizeof(rowid), &cb) == SQL_SUCCESS)
+ {
+ pSqlStmt += wxT("ROWID = '");
+ pSqlStmt += rowid;
+ pSqlStmt += wxT("'");
+ break;
+ }
+ }
+ // Unable to delete by ROWID, so build a WHERE
+ // clause based on the keyfields.
+ BuildWhereClause(whereClause, DB_WHERE_KEYFIELDS);
+ pSqlStmt += whereClause;
+ break;
+ case DB_UPD_WHERE:
+ pSqlStmt += pWhereClause;
+ break;
+ }
+} // BuildUpdateStmt()
+
+
+/***** DEPRECATED: use wxDbTable::BuildUpdateStmt(wxString &....) form *****/
+void wxDbTable::BuildUpdateStmt(wxChar *pSqlStmt, int typeOfUpdate, const wxString &pWhereClause)
+{
+ wxString tempSqlStmt;
+ BuildUpdateStmt(tempSqlStmt, typeOfUpdate, pWhereClause);
+ wxStrcpy(pSqlStmt, tempSqlStmt);
+} // BuildUpdateStmt()
+
+
+/********** wxDbTable::BuildWhereClause() **********/
+void wxDbTable::BuildWhereClause(wxString &pWhereClause, int typeOfWhere,
+ const wxString &qualTableName, bool useLikeComparison)
+/*
+ * Note: BuildWhereClause() currently ignores timestamp columns.
+ * They are not included as part of the where clause.
+ */
+{
+ bool moreThanOneColumn = false;
+ wxString colValue;
+
+ // Loop through the columns building a where clause as you go
+ int colNumber;
+ for (colNumber = 0; colNumber < m_numCols; colNumber++)
+ {
+ // Determine if this column should be included in the WHERE clause
+ if ((typeOfWhere == DB_WHERE_KEYFIELDS && colDefs[colNumber].KeyField) ||
+ (typeOfWhere == DB_WHERE_MATCHING && (!IsColNull((UWORD)colNumber))))
+ {
+ // Skip over timestamp columns
+ if (colDefs[colNumber].SqlCtype == SQL_C_TIMESTAMP)
+ continue;
+ // If there is more than 1 column, join them with the keyword "AND"
+ if (moreThanOneColumn)
+ pWhereClause += wxT(" AND ");
+ else
+ moreThanOneColumn = true;
+
+ // Concatenate where phrase for the column
+ wxString tStr = colDefs[colNumber].ColName;
+
+ if (qualTableName.length() && tStr.Find(wxT('.')) == wxNOT_FOUND)
+ {
+ pWhereClause += pDb->SQLTableName(qualTableName);
+ pWhereClause += wxT(".");
+ }
+ pWhereClause += pDb->SQLColumnName(colDefs[colNumber].ColName);
+
+ if (useLikeComparison && (colDefs[colNumber].SqlCtype == SQL_C_WXCHAR))
+ pWhereClause += wxT(" LIKE ");
+ else
+ pWhereClause += wxT(" = ");
+
+ switch(colDefs[colNumber].SqlCtype)
+ {
+ case SQL_C_CHAR:
+#ifdef SQL_C_WCHAR
+ case SQL_C_WCHAR:
+#endif
+ //case SQL_C_WXCHAR: SQL_C_WXCHAR is covered by either SQL_C_CHAR or SQL_C_WCHAR
+ colValue.Printf(wxT("'%s'"), GetDb()->EscapeSqlChars((wxChar *)colDefs[colNumber].PtrDataObj).c_str());
+ break;
+ case SQL_C_SHORT:
+ case SQL_C_SSHORT:
+ colValue.Printf(wxT("%hi"), *((SWORD *) colDefs[colNumber].PtrDataObj));
+ break;
+ case SQL_C_USHORT:
+ colValue.Printf(wxT("%hu"), *((UWORD *) colDefs[colNumber].PtrDataObj));
+ break;
+ case SQL_C_LONG:
+ case SQL_C_SLONG:
+ colValue.Printf(wxT("%li"), *((SDWORD *) colDefs[colNumber].PtrDataObj));
+ break;
+ case SQL_C_ULONG:
+ colValue.Printf(wxT("%lu"), *((UDWORD *) colDefs[colNumber].PtrDataObj));
+ break;
+ case SQL_C_FLOAT:
+ colValue.Printf(wxT("%.6f"), *((SFLOAT *) colDefs[colNumber].PtrDataObj));
+ break;
+ case SQL_C_DOUBLE:
+ colValue.Printf(wxT("%.6f"), *((SDOUBLE *) colDefs[colNumber].PtrDataObj));
+ break;
+ default:
+ {
+ wxString strMsg;
+ strMsg.Printf(wxT("wxDbTable::bindParams(): Unknown column type for colDefs %d colName %s"),
+ colNumber,colDefs[colNumber].ColName);
+ wxFAIL_MSG(strMsg.c_str());
+ }
+ break;
+ }
+ pWhereClause += colValue;
+ }
+ }
+} // wxDbTable::BuildWhereClause()
+
+
+/***** DEPRECATED: use wxDbTable::BuildWhereClause(wxString &....) form *****/
+void wxDbTable::BuildWhereClause(wxChar *pWhereClause, int typeOfWhere,
+ const wxString &qualTableName, bool useLikeComparison)
+{
+ wxString tempSqlStmt;
+ BuildWhereClause(tempSqlStmt, typeOfWhere, qualTableName, useLikeComparison);
+ wxStrcpy(pWhereClause, tempSqlStmt);
+} // wxDbTable::BuildWhereClause()
+
+
+/********** wxDbTable::GetRowNum() **********/
+UWORD wxDbTable::GetRowNum(void)
+{
+ UDWORD rowNum;
+
+ if (SQLGetStmtOption(hstmt, SQL_ROW_NUMBER, (UCHAR*) &rowNum) != SQL_SUCCESS)
+ {
+ pDb->DispAllErrors(henv, hdbc, hstmt);
+ return(0);
+ }
+
+ // Completed successfully
+ return((UWORD) rowNum);
+
+} // wxDbTable::GetRowNum()
+
+
+/********** wxDbTable::CloseCursor() **********/
+bool wxDbTable::CloseCursor(HSTMT cursor)
+{
+ if (SQLFreeStmt(cursor, SQL_CLOSE) != SQL_SUCCESS)
+ return(pDb->DispAllErrors(henv, hdbc, cursor));
+
+ // Completed successfully
+ return true;
+
+} // wxDbTable::CloseCursor()
+
+
+/********** wxDbTable::CreateTable() **********/
+bool wxDbTable::CreateTable(bool attemptDrop)
+{
+ if (!pDb)
+ return false;
+
+ int i, j;
+ wxString sqlStmt;
projects / wxWidgets.git / blobdiff