///////////////////////////////////////////////////////////////////////////////
-// Name: table.cpp
+// Name: dbtable.cpp
// Purpose: Implementation of the wxTable class.
// Author: Doug Card
// Modified by:
// the wxWindows GUI development toolkit.
///////////////////////////////////////////////////////////////////////////////
+#ifdef __GNUG__
+#pragma implementation "dbtable.h"
+#endif
+
/*
// SYNOPSIS START
// SYNOPSIS STOP
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
+#include <assert.h>
-#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 <macros.h>
// # endif
-# ifdef __WXLINUX__
+# ifdef LINUX
# include <sys/minmax.h>
# endif
#endif
{
// 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;
// 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: ";
/********** 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);
}
}
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));
/********** wxTable::QueryBySqlStmt() **********/
bool wxTable::QueryBySqlStmt(char *pSqlStmt)
{
+ pDb->WriteSqlLog(pSqlStmt);
return(query(DB_SELECT_STATEMENT, FALSE, FALSE, 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)
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, ",");
// 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.
{
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);
/********** 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)
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));
}
/********** 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++)
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));
}
// 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));
}
/********** wxTable::CreateTable() **********/
bool wxTable::CreateTable(void)
{
+ if (!pDb)
+ return FALSE;
+
int i, j;
char sqlStmt[DB_MAX_STATEMENT_LEN];
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);
}
}
+ pDb->WriteSqlLog(sqlStmt);
+
// Commit the transaction and close the cursor
if (! pDb->CommitTrans())
return(FALSE);
// 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
}
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++)
{
strcat(sqlStmt, ")");
}
// Append the closing parentheses for the create table statement
- strcat(sqlStmt, ")");
+ strcat(sqlStmt, ")");
+
+ pDb->WriteSqlLog(sqlStmt);
#ifdef _CONSOLE
cout << endl << sqlStmt << endl;
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
/********** wxTable::Update(pSqlStmt) **********/
bool wxTable::Update(char *pSqlStmt)
{
+ pDb->WriteSqlLog(pSqlStmt);
return(execUpdate(pSqlStmt));
// Build the SQL UPDATE statement
GetUpdateStmt(sqlStmt, DB_UPD_KEYFIELDS);
+ pDb->WriteSqlLog(sqlStmt);
+
#ifdef _CONSOLE
cout << endl << sqlStmt << endl << endl;
#endif
// Build the SQL UPDATE statement
GetUpdateStmt(sqlStmt, DB_UPD_WHERE, pWhereClause);
+ pDb->WriteSqlLog(sqlStmt);
+
#ifdef _CONSOLE
cout << endl << sqlStmt << endl << endl;
#endif
// Build the SQL DELETE statement
GetDeleteStmt(sqlStmt, DB_DEL_KEYFIELDS);
+ pDb->WriteSqlLog(sqlStmt);
+
// Execute the SQL DELETE statement
return(execDelete(sqlStmt));
// Build the SQL DELETE statement
GetDeleteStmt(sqlStmt, DB_DEL_WHERE, pWhereClause);
+ pDb->WriteSqlLog(sqlStmt);
+
// Execute the SQL DELETE statement
return(execDelete(sqlStmt));
// Build the SQL DELETE statement
GetDeleteStmt(sqlStmt, DB_DEL_MATCHING);
+ pDb->WriteSqlLog(sqlStmt);
+
// Execute the SQL DELETE statement
return(execDelete(sqlStmt));
// 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);
// 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);
* 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++)
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);
bool wxTable::CanSelectForUpdate(void)
{
+#ifndef __WXGTK__
if (pDb->dbInf.posStmts & SQL_PS_SELECT_FOR_UPDATE)
return(TRUE);
else
+#endif
return(FALSE);
} // wxTable::CanSelectForUpdate()
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")))
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
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))
{
strcat(sqlStmt, where);
}
+ pDb->WriteSqlLog(sqlStmt);
+
// Execute the SQL statement
if (SQLExecDirect(hstmtCount, (UCHAR FAR *) sqlStmt, SQL_NTS) != SQL_SUCCESS)
{
}
// 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);
// 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, "'");
}
// 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;
} // wxTable::Refresh()
#endif
- // wxUSE_ODBC
+ // wxUSE_ODBC
+
projects / wxWidgets.git / blobdiff