A wxDbTable instance provides re-usable access to rows of data in a table contained within the associated ODBC datasource
See the database classes overview for an introduction to using the ODBC classes.
Include files
<wx/dbtable.h>
<wx/db.h>
Members Helper classes and data structures
The following classes and structs are defined in dbtable.cpp/.h for use with the wxDbTable class.
Constants
wxDB_DEFAULT_CURSOR Primary cursor normally used for cursor based
operations.
wxDB_QUERY_ONLY Used to indicate whether a table that is opened
is for query only, or if insert/update/deletes
will be performed on the table. Less overhead
(cursors and memory) are allocated for query
only tables, plus read access times are faster
with some datasources.
wxDB_ROWID_LEN [Oracle only] - Used when CanUpdateByRowID()
is true. Optimizes updates so they are faster
by updating on the Oracle-specific ROWID column
rather than some other index.
wxDB_DISABLE_VIEW Use to indicate when a database view should not
be if a table is normally set up to use a view.
[Currently unsupported.]
Members
wxDbTable::wxDbTable
wxDbTable::wxDbTable
wxDbTable::BuildDeleteStmt
wxDbTable::BuildSelectStmt
wxDbTable::BuildUpdateStmt
wxDbTable::BuildWhereClause
wxDbTable::CanSelectForUpdate
wxDbTable::CanUpdateByROWID
wxDbTable::ClearMemberVar
wxDbTable::ClearMemberVars
wxDbTable::CloseCursor
wxDbTable::Count
wxDbTable::CreateIndex
wxDbTable::CreateTable
wxDbTable::DB_STATUS
wxDbTable::Delete
wxDbTable::DeleteCursor
wxDbTable::DeleteMatching
wxDbTable::DeleteWhere
wxDbTable::DropIndex
wxDbTable::DropTable
wxDbTable::From
wxDbTable::GetColDefs
wxDbTable::GetCursor
wxDbTable::GetDb
wxDbTable::GetFirst
wxDbTable::GetFromClause
wxDbTable::GetLast
wxDbTable::GetNewCursor
wxDbTable::GetNext
wxDbTable::GetNumberOfColumns
wxDbTable::GetOrderByClause
wxDbTable::GetPrev
wxDbTable::GetQueryTableName
wxDbTable::GetRowNum
wxDbTable::GetTableName
wxDbTable::GetTablePath
wxDbTable::GetWhereClause
wxDbTable::Insert
wxDbTable::IsColNull
wxDbTable::IsCursorClosedOnCommit
wxDbTable::IsQueryOnly
wxDbTable::Open
wxDbTable::OrderBy
wxDbTable::Query
wxDbTable::QueryBySqlStmt
wxDbTable::QueryMatching
wxDbTable::QueryOnKeyFields
wxDbTable::Refresh
wxDbTable::SetColDefs
wxDbTable::SetCursor
wxDbTable::SetFromClause
wxDbTable::SetColNull
wxDbTable::SetOrderByClause
wxDbTable::SetQueryTimeout
wxDbTable::SetWhereClause
wxDbTable::Update
wxDbTable::UpdateWhere
wxDbTable::Where
wxDbTable::operator ++
wxDbTable::operator --
wxDbTable(wxDb *pwxDb, const wxString &tblName, const UWORD numColumns, const wxString &qryTblName = "", bool qryOnly = !wxDB_QUERY_ONLY, const wxString &tblPath = "")
Default constructor.
Parameters
pwxDb
virtual ~wxDbTable()
Virtual default destructor.
void BuildDeleteStmt(wxString &pSqlStmt, int typeOfDel, const wxString &pWhereClause="")
Constructs the full SQL statement that can be used to delete all rows matching the criteria in the pWhereClause.
Parameters
pSqlStmt
Remarks
This member function constructs a SQL DELETE statement. This can be used for debugging purposes if you are having problems executing your SQL statement.
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause are ignored by this function.
void BuildSelectStmt(wxString &pSqlStmt, int typeOfSelect, bool distinct)
Constructs the full SQL statement that can be used to select all rows matching the criteria in the pWhereClause. This function is called internally in the wxDbTable class whenever the function wxDbTable::Query is called.
NOTE: Only the columns specified in wxDbTable::SetColDefs statements are included in the list of columns returned by the SQL statement created by a call to this function.
Parameters
pSqlStmt
Remarks
This member function constructs a SQL SELECT statement. This can be used for debugging purposes if you are having problems executing your SQL statement.
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause are ignored by this function.
void BuildUpdateStmt(wxString &pSqlStmt, int typeOfUpd, const wxString &pWhereClause="")
Constructs the full SQL statement that can be used to update all rows matching the criteria in the pWhereClause.
If typeOfUpdate is DB_UPD_KEYFIELDS, then the current values in the bound columns are used to determine which row(s) in the table are to be updated. The exception to this is when a datasource supports ROW IDs (Oracle). The ROW ID column is used for efficiency purposes when available.
NOTE: Only the columns specified in wxDbTable::SetColDefs statements are included in the list of columns updated by the SQL statement created by a call to this function. Any column definitions that were defined as being non-updateable will be excluded from the SQL UPDATE statement created by this function.
Parameters
pSqlStmt
Remarks
This member function allows you to see what the SQL UPDATE statement looks like that the ODBC class library builds. This can be used for debugging purposes if you are having problems executing your SQL statement.
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause are ignored by this function.
Constructs the portion of a SQL statement which would follow the word 'WHERE' in a SQL statement to be passed to the datasource. The returned string does NOT include the word 'WHERE'.
Parameters
pWhereClause
Remarks
This member function allows you to see what the SQL WHERE clause looks like that the ODBC class library builds. This can be used for debugging purposes if you are having problems executing your own SQL statements.
If using 'typeOfWhere' set to DB_WHERE_MATCHING, any bound columns currently containing a NULL value are not included in the WHERE clause's list of columns to use in the comparison.
bool CanSelectForUpdate()
Use this function to determine if the datasource supports SELECT ... FOR UPDATE. When the keywords "FOR UPDATE" are included as part of your SQL SELECT statement, all records retrieved (not just queried, but actually retrieved using wxDbTable::GetNext, etc) from the result set are locked.
Remarks
Not all datasources support the "FOR UPDATE" clause, so you must use this member function to determine if the datasource currently connected to supports this behavior or not before trying to select using "FOR UPDATE".
If the wxDbTable instance was created with the parameter wxDB_QUERY_ONLY, then this function will return false. For all known databases which do not support the FOR UPDATE clause, this function will return false also.
bool CanUpdateByROWID()
CURRENTLY ONLY POSSIBLE IF USING ORACLE.
--- CURRENTLY DISABLED FOR *ALL* DATASOURCES --- NOV 1 2000 - gt
Every Oracle table has a hidden column named ROWID. This is a pointer to the physical location of the record in the datasource and allows for very fast updates and deletes. The key is to retrieve this ROWID during your query so it is available during an update or delete operation.
Use of the ROWID feature is always handled by the class library except in the case of wxDbTable::QueryBySqlStmt. Since you are passing in the SQL SELECT statement, it is up to you to include the ROWID column in your query. If you do not, the application will still work, but may not be as optimized. The ROWID is always the last column in the column list in your SQL SELECT statement. The ROWID is not a column in the normal sense and should not be considered part of the column definitions for the wxDbTable object.
Remarks
The decision to include the ROWID in your SQL SELECT statement must be deferred until runtime since it depends on whether you are connected to an Oracle datasource or not.
Example
// Incomplete code sample
wxDbTable parts;
.....
if (parts.CanUpdateByROWID())
{
// Note that the ROWID column must always be the last column selected
sqlStmt = "SELECT PART_NUM, PART_DESC, ROWID" FROM PARTS";
}
else
sqlStmt = "SELECT PART_NUM, PART_DESC FROM PARTS";
void ClearMemberVar(UWORD colNumber, bool setToNull=false)
Same as wxDbTable::ClearMemberVars except that this function clears only the specified column of its values, and optionally sets the column to be a NULL column.
colNumber
void ClearMemberVars(bool setToNull=false)
Initializes all bound columns of the wxDbTable instance to zero. In the case of a string, zero is copied to the first byte of the string.
setToNull
Remarks
This is useful before calling functions such as wxDbTable::QueryMatching or wxDbTable::DeleteMatching since these functions build their WHERE clauses from non-zero columns. To call either wxDbTable::QueryMatching or wxDbTable::DeleteMatching use this sequence:
1) ClearMemberVars() 2) Assign columns values you wish to match on 3) Call wxDbTable::QueryMatching() or wxDbTable::DeleteMatching()
bool CloseCursor(HSTMTcursor)
Closes the specified cursor associated with the wxDbTable object.
Parameters
cursor
Remarks
Typically handled internally by the ODBC class library, but may be used by the programmer if desired.
ULONG Count(const wxString &args="*")
Returns the number of records which would be in the result set using the current query parameters specified in the WHERE and FROM clauses.
Parameters
args
Remarks
This function can be called before or after an actual query to obtain the count of records in the result set. Count() uses its own cursor, so result set cursor positioning is not affected by calls to Count().
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause ARE used by this function.
Example
USERS TABLE
FIRST_NAME LAST_NAME
----------- ----------
John Doe
Richard Smith
Michael Jones
John Carpenter
// Incomplete code sample
wxDbTable users;
.....
users.SetWhereClause("");
// This Count() will return 4, as there are four users listed above
// that match the query parameters
totalNumberOfUsers = users.Count();
// This Count() will return 3, as there are only 3 unique first names
// in the table above - John, Richard, Michael.
totalNumberOfUniqueFirstNames = users.Count("DISTINCT FIRST_NAME");
bool CreateIndex(const wxString &IndexName, bool unique, UWORD numIndexColumns, wxDbIdxDef *pIndexDefs, bool attemptDrop=true)
This member function allows you to create secondary (non-primary) indexes on your tables. You first create your table, normally specifying a primary index, and then create any secondary indexes on the table. Indexes in relational model are not required. You do not need indexes to look up records in a table or to join two tables together. In the relational model, indexes, if available, provide a quicker means to look up data in a table. To enjoy the performance benefits of indexes, the indexes must be defined on the appropriate columns and your SQL code must be written in such a way as to take advantage of those indexes.
Parameters
IndexName
Remarks
The first parameter, index name, must be unique and should be given a meaningful name. Common practice is to include the table name as a prefix in the index name (e.g. For table PARTS, you might want to call your index PARTS_Index1). This will allow you to easily view all of the indexes defined for a given table grouped together alphabetically.
The second parameter indicates if the index is unique or not. Uniqueness is enforced at the RDBMS level preventing rows which would have duplicate indexes from being inserted into the table when violating a unique index's uniqueness.
In the third parameter, specify how many columns are in your index. This number must match the number of columns defined in the 'pIndexDefs' parameter.
The fourth parameter specifies which columns make up the index using the wxDbIdxDef structure. For each column in the index, you must specify two things, the column name and the sort order (ascending / descending). See the example below to see how to build and pass in the wxDbIdxDef structure.
The fifth parameter is provided to handle the differences in datasources as to whether they will automatically overwrite existing indexes with the same name or not. Some datasources require that the existing index must be dropped first, so this is the default behavior.
Some datasources (MySQL, and possibly others) require columns which are to be part of an index to be defined as NOT NULL. When this function is called, if a column is not defined to be NOT NULL, a call to this function will modify the column definition to change any columns included in the index to be NOT NULL. In this situation, if a NULL value already exists in one of the columns that is being modified, creation of the index will fail.
PostGres is unable to handle index definitions which specify whether the index is ascending or descending, and defaults to the system default when the index is created.
It is not necessary to call wxDb::CommitTrans after executing this function.
Example
// Create a secondary index on the PARTS table
wxDbIdxDef IndexDef[2]; // 2 columns make up the index
wxStrcpy(IndexDef[0].ColName, "PART_DESC"); // Column 1
IndexDef[0].Ascending = true;
wxStrcpy(IndexDef[1].ColName, "SERIAL_NO"); // Column 2
IndexDef[1].Ascending = false;
// Create a name for the index based on the table's name
wxString indexName;
indexName.Printf("%s_Index1",parts->GetTableName());
parts->CreateIndex(indexName, true, 2, IndexDef);
bool CreateTable(bool attemptDrop=true)
Creates a table based on the definitions previously defined for this wxDbTable instance.
Parameters
attemptDrop
Remarks
This function creates the table and primary index (if any) in the table space associated with the connected datasource. The owner of these objects will be the user id that was given when wxDb::Open was called. The objects will be created in the default schema/table space for that user.
In your derived wxDbTable object constructor, the columns and primary index of the table are described through the wxDbColDef structure. wxDbTable::CreateTable uses this information to create the table and to add the primary index. See wxDbTable ctor and wxDbColDef description for additional information on describing the columns of the table.
It is not necessary to call wxDb::CommitTrans after executing this function.
bool DB_STATUS()
Accessor function that returns the wxDb private member variable DB_STATUS for the database connection used by this instance of wxDbTable.
bool Delete()
Deletes the row from the table indicated by the current cursor.
Remarks
Use wxDbTable::GetFirst, wxDbTable::GetLast, wxDbTable::GetNext or wxDbTable::GetPrev to position the cursor to a valid record. Once positioned on a record, call this function to delete the row from the table.
A wxDb::CommitTrans or wxDb::RollbackTrans must be called after use of this function to commit or rollback the deletion.
NOTE: Most datasources have a limited size "rollback" segment. This means that it is only possible to insert/update/delete a finite number of rows without performing a wxDb::CommitTrans or wxDb::RollbackTrans. Size of the rollback segment varies from database to database, and is user configurable in most databases. Therefore it is usually best to try to perform a commit or rollback at relatively small intervals when processing a larger number of actions that insert/update/delete rows in a table.
bool DeleteCursor(HSTMT *hstmtDel)
Allows a program to delete a cursor.
Parameters
hstmtDel
Remarks
For default cursors associated with the instance of wxDbTable, it is not necessary to specifically delete the cursors. This is automatically done in the wxDbTable destructor.
NOTE: If the cursor could not be deleted for some reason, an error is logged indicating the reason. Even if the cursor could not be deleted, the HSTMT that is passed in is deleted, and the pointer is set to NULL.
bool DeleteMatching()
This member function allows you to delete records from your wxDbTable object by specifying the data in the columns to match on.
Remarks
To delete all users with a first name of "JOHN", do the following:
The WHERE clause is built by the ODBC class library based on all non-NULL columns. This allows deletion of records by matching on any column(s) in your wxDbTable instance, without having to write the SQL WHERE clause.
A wxDb::CommitTrans or wxDb::RollbackTrans must be called after use of this function to commit or rollback the deletion.
NOTE: Row(s) should be locked before deleting them to make sure they are not already in use. This can be achieved by calling wxDbTable::QueryMatching, and then retrieving the records, locking each as you go (assuming FOR UPDATE is allowed on the datasource). After the row(s) have been successfully locked, call this function.
NOTE: Most datasources have a limited "rollback" segment. This means that it is only possible to insert/update/delete a finite number of rows without performing a wxDb::CommitTrans or wxDb::RollbackTrans. Size of the rollback segment varies from database to database, and is user configurable in most databases. Therefore it is usually best to try to perform a commit or rollback at relatively small intervals when processing a larger number of actions that insert/update/delete rows in a table.
Example
// Incomplete code sample to delete all users with a first name
// of "JOHN"
users.ClearMemberVars();
wxStrcpy(users.FirstName,"JOHN");
users.DeleteMatching();
bool DeleteWhere(const wxString &pWhereClause)
Deletes all rows from the table which match the criteria specified in the WHERE clause that is passed in.
Parameters
pWhereClause
Remarks
This is the most powerful form of the wxDbTable delete functions. This function gives access to the full power of SQL. This function can be used to delete records by passing a valid SQL WHERE clause. Sophisticated deletions can be performed based on multiple criteria using the full functionality of the SQL language.
A wxDb::CommitTrans must be called after use of this function to commit the deletions.
Note: This function is limited to deleting records from the table associated with this wxDbTable object only. Deletions on joined tables is not possible.
NOTE: Most datasources have a limited size "rollback" segment. This means that it is only possible to insert/update/delete a finite number of rows without performing a wxDb::CommitTrans or wxDb::RollbackTrans. Size of the rollback segment varies from database to database, and is user configurable in most databases. Therefore it is usually best to try to perform a commit or rollback at relatively small intervals when processing a larger number of actions that insert/update/delete rows in a table.
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause are ignored by this function.
Example
// Delete parts 1 thru 10 from containers 'X', 'Y' and 'Z' that
// are magenta in color
parts.DeleteWhere("(PART_NUMBER BETWEEN 1 AND 10) AND \
CONTAINER IN ('X', 'Y', 'Z') AND \
UPPER(COLOR) = 'MAGENTA'");
bool DropIndex(const wxString &IndexName)
Allows an index on the associated table to be dropped (deleted) if the user login has sufficient privileges to do so.
Parameters
IndexName
Remarks
If the index specified in the 'IndexName' parameter does not exist, an error will be logged, and the function will return a result of false.
It is not necessary to call wxDb::CommitTrans after executing this function.
bool DropTable()
Deletes the associated table if the user has sufficient privileges to do so.
Remarks
This function returns true if the table does not exist, but only for supported databases (see wxDb::Dbms). If a datasource is not specifically supported, and this function is called, the function will return false.
Most datasources/ODBC drivers will delete any indexes associated with the table automatically, and others may not. Check the documentation for your database to determine the behavior.
It is not necessary to call wxDb::CommitTrans after executing this function.
const wxString & From()
void From(const wxString &From)
Accessor function for the private class member wxDbTable::from. Can be used as a synonym for wxDbTable::GetFromClause (the first form of this function) or wxDbTable::SetFromClause (the second form of this function).
Parameters
From
Return value
The first form of this function returns the current value of the wxDbTable member variable ::from.
The second form of the function has no return value, as it will always set the from clause successfully.
See also
wxDbTable::GetFromClause, wxDbTable::SetFromClause
wxDbColDef * GetColDefs()
Accessor function that returns a pointer to the array of column definitions that are bound to the columns that this wxDbTable instance is associated with.
To determine the number of elements pointed to by the returned wxDbColDef pointer, use the wxDbTable::GetNumberOfColumns function.
Remarks
These column definitions must not be manually redefined after they have been set.
HSTMT GetCursor()
Returns the HSTMT value of the current cursor for this wxDbTable object.
Remarks
This function is typically used just before changing to use a different cursor so that after the program is finished using the other cursor, the current cursor can be set back to being the cursor in use.
See also
wxDbTable::SetCursor, wxDbTable::GetNewCursor
wxDb * GetDb()
Accessor function for the private member variable pDb which is a pointer to the datasource connection that this wxDbTable instance uses.
bool GetFirst()
Retrieves the FIRST row in the record set as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query, wxDbTable::QueryOnKeyFields, wxDbTable::QueryMatching or wxDbTable::QueryBySqlStmt.
Remarks
This function can only be used if the datasource connection used by the wxDbTable instance was created with FwdOnlyCursors set to false. If the connection does not allow backward scrolling cursors, this function will return false, and the data contained in the bound columns will be undefined.
See also
const wxString & GetFromClause()
Accessor function that returns the current FROM setting assigned with the wxDbTable::SetFromClause.
See also
bool GetLast()
Retrieves the LAST row in the record set as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query, wxDbTable::QueryOnKeyFields, wxDbTable::QueryMatching or wxDbTable::QueryBySqlStmt.
Remarks
This function can only be used if the datasource connection used by the wxDbTable instance was created with FwdOnlyCursors set to false. If the connection does not allow backward scrolling cursors, this function will return false, and the data contained in the bound columns will be undefined.
See also
HSTMT * GetNewCursor(bool setCursor=false, bool bindColumns=true)
This function will create a new cursor that can be used to access the table being referenced by this wxDbTable instance, or to execute direct SQL commands on without affecting the cursors that are already defined and possibly positioned.
Parameters
setCursor
Remarks
This new cursor must be closed using wxDbTable::DeleteCursor by the calling program before the wxDbTable instance is deleted, or both memory and resource leaks will occur.
bool GetNext()
Retrieves the NEXT row in the record set after the current cursor position as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query, wxDbTable::QueryOnKeyFields, wxDbTable::QueryMatching or wxDbTable::QueryBySqlStmt.
Return value
This function returns false when the current cursor has reached the end of the result set. When false is returned, data in the bound columns is undefined.
Remarks
This function works with both forward and backward scrolling cursors.
See also wxDbTable::++
UWORD GetNumberOfColumns()
Accessor function that returns the number of columns that are statically bound for access by the wxDbTable instance.
const wxString & GetOrderByClause()
Accessor function that returns the current ORDER BY setting assigned with the wxDbTable::SetOrderByClause.
See also
bool GetPrev()
Retrieves the PREVIOUS row in the record set before the current cursor position as defined by the current query. Before retrieving records, a query must be performed using wxDbTable::Query, wxDbTable::QueryOnKeyFields, wxDbTable::QueryMatching or wxDbTable::QueryBySqlStmt.
Return value
This function returns false when the current cursor has reached the beginning of the result set and there are now other rows prior to the cursors current position. When false is returned, data in the bound columns is undefined.
Remarks
This function can only be used if the datasource connection used by the wxDbTable instance was created with FwdOnlyCursors set to false. If the connection does not allow backward scrolling cursors, this function will return false, and the data contained in the bound columns will be undefined.
See also
wxDb::IsFwdOnlyCursors, wxDbTable::--
const wxString & GetQueryTableName()
Accessor function that returns the name of the table/view that was indicated as being the table/view to query against when this wxDbTable instance was created.
See also
UWORD GetRowNum()
Returns the ODBC row number for performing positioned updates and deletes.
Remarks
This function is not being used within the ODBC class library and may be a candidate for removal if no use is found for it.
Row number with some datasources/ODBC drivers is the position in the result set, while in others it may be a physical position in the database. Check your database documentation to find out which behavior is supported.
const wxString & GetTableName()
Accessor function that returns the name of the table that was indicated as being the table that this wxDbTable instance was associated with.
const wxString & GetTablePath()
Accessor function that returns the path to the data table that was indicated during creation of this wxDbTable instance.
Remarks
Currently only applicable to dBase and MS-Access datasources.
const wxString & GetWhereClause()
Accessor function that returns the current WHERE setting assigned with the wxDbTable::SetWhereClause
See also
int Insert()
Inserts a new record into the table being referenced by this wxDbTable instance. The values in the member variables of the wxDbTable instance are inserted into the columns of the new row in the database.
Return value
DB_SUCCESS Record inserted successfully (value = 1)
DB_FAILURE Insert failed (value = 0)
DB_ERR_INTEGRITY_CONSTRAINT_VIOL
The insert failed due to an integrity
constraint violation (duplicate non-unique
index entry) is attempted.
RemarksA wxDb::CommitTrans or wxDb::RollbackTrans must be called after use of this function to commit or rollback the insertion.
Example
// Incomplete code snippet
wxStrcpy(parts->PartName, "10");
wxStrcpy(parts->PartDesc, "Part #10");
parts->Qty = 1000;
RETCODE retcode = parts->Insert();
switch(retcode)
{
case DB_SUCCESS:
parts->GetDb()->CommitTrans();
return(true);
case DB_ERR_INTEGRITY_CONSTRAINT_VIOL:
// Current data would result in a duplicate key
// on one or more indexes that do not allow duplicates
parts->GetDb()->RollbackTrans();
return(false);
default:
// Insert failed for some unexpected reason
parts->GetDb()->RollbackTrans();
return(false);
}
bool IsColNull(UWORD colNumber) const
Used primarily in the ODBC class library to determine if a column value is set to "NULL". Works for all data types supported by the ODBC class library.
Parameters
colNumber
Remarks
NULL column support is currently not fully implemented as of wxWidgets 2.4.
bool IsCursorClosedOnCommit()
Accessor function to return information collected during the opening of the datasource connection that is used by this wxDbTable instance. The result returned by this function indicates whether an implicit closing of the cursor is done after a commit on the database connection.
Return value
Returns true if the cursor associated with this wxDbTable object is closed after a commit or rollback operation. Returns false otherwise.
Remarks
If more than one wxDbTable instance used the same database connection, all cursors which use the database connection are closed on the commit if this function indicates true.
bool IsQueryOnly()
Accessor function that returns a value indicating if this wxDbTable instance was created to allow only queries to be performed on the bound columns. If this function returns true, then no actions may be performed using this wxDbTable instance that would modify (insert/delete/update) the table's data.
bool Open(bool checkPrivileges=false, bool checkTableExists=true)
Every wxDbTable instance must be opened before it can be used. This function checks for the existence of the requested table, binds columns, creates required cursors, (insert/select and update if connection is not wxDB_QUERY_ONLY) and constructs the insert statement that is to be used for inserting data as a new row in the datasource.
NOTE: To retrieve data into an opened table, the of the table must be bound to the variables in the program via call(s) to wxDbTable::SetColDefs before calling Open().
See the database classes overview for an introduction to using the ODBC classes.
Parameters
checkPrivileges
checkTableExists
Remarks
If the function returns a false value due to the table not existing, a log entry is recorded for the datasource connection indicating the problem that was detected when checking for table existence. Note that it is usually best for the calling routine to check for the existence of the table and for sufficient user privileges to access the table in the mode (wxDB_QUERY_ONLY or !wxDB_QUERY_ONLY) before trying to open the table for the best possible explanation as to why a table cannot be opened.
Checking the user's privileges on a table can be quite time consuming during the open phase. With most applications, the programmer already knows that the user has sufficient privileges to access the table, so this check is normally not required.
For best performance, open the table, and then use the wxDb::TablePrivileges function to check the users privileges. Passing a schema to the TablePrivileges() function can significantly speed up the privileges checks.
See also
wxDb::TableExists, wxDb::TablePrivileges wxDbTable::SetColDefs
const wxString & OrderBy()
void OrderBy(const wxString &OrderBy)
Accessor function for the private class member wxDbTable::orderBy. Can be used as a synonym for wxDbTable::GetOrderByClause (the first form of this function) or wxDbTable::SetOrderByClause (the second form of this function).
Parameters
OrderBy
Return value
The first form of this function returns the current value of the wxDbTable member variable ::orderBy.
The second form of the function has no return value.
See also
wxDbTable::GetOrderByClause, wxDbTable::SetFromClause
virtual bool Query(bool forUpdate=false, bool distinct=false)
Parameters
forUpdate
Remarks
This function queries records from the datasource based on the three wxDbTable members: "where", "orderBy", and "from". Use wxDbTable::SetWhereClause to filter on records to be retrieved (e.g. All users with a first name of "JOHN"). Use wxDbTable::SetOrderByClause to change the sequence in which records are returned in the result set from the datasource (e.g. Ordered by LAST_NAME). Use wxDbTable::SetFromClause to allow inner joining of the base table (the one being associated with this instance of wxDbTable) with other tables which share a related field.
After each of these clauses are set/cleared, call wxDbTable::Query() to fetch the result set from the datasource.
This scheme has an advantage if you have to requery your record set frequently in that you only have to set your WHERE, ORDER BY, and FROM clauses once. Then to refresh the record set, simply call wxDbTable::Query() as frequently as needed.
Note that repeated calls to wxDbTable::Query() may tax the database server and make your application sluggish if done too frequently or unnecessarily.
The base table name is automatically prepended to the base column names in the event that the FROM clause has been set (is non-null) using wxDbTable::SetFromClause.
The cursor for the result set is positioned before the first record in the result set after the query. To retrieve the first record, call either wxDbTable::GetFirst (only if backward scrolling cursors are available) or wxDbTable::GetNext. Typically, no data from the result set is returned to the client driver until a request such as wxDbTable::GetNext is performed, so network traffic and database load are not overwhelmed transmitting data until the data is actually requested by the client. This behavior is solely dependent on the ODBC driver though, so refer to the ODBC driver's reference material for information on its behaviors.
Values in the bound columns' memory variables are undefined after executing a call to this function and remain that way until a row in the result set is requested to be returned.
The wxDbTable::Query() function is defined as "virtual" so that it may be overridden for application specific purposes.
Example
// Incomplete code sample
parts->SetWhereClause("DESCRIPTION = 'FOOD'");
parts->SetOrderByClause("EXPIRATION_DATE");
parts->SetFromClause("");
// Query the records based on the where, orderBy and from clauses
// specified above
parts->Query();
// Display all records queried
while(parts->GetNext())
dispPart(parts); // user defined function
bool QueryBySqlStmt(const wxString &pSqlStmt)
Performs a query against the datasource by accepting and passing verbatim the SQL SELECT statement passed to the function.
Parameters
pSqlStmt
Remarks
This is the most powerful form of the query functions available. This member function allows a programmer to write their own custom SQL SELECT statement for requesting data from the datasource. This gives the programmer access to the full power of SQL for performing operations such as scalar functions, aggregate functions, table joins, and sub-queries, as well as datasource specific function calls.
The requirements of the SELECT statement are the following:
Even though data can be selected from multiple tables (joins) in your select statement, only the base table associated with this wxDbTable object is automatically updated through the ODBC class library. Data from multiple tables can be selected for display purposes however. Include columns in the wxDbTable object and mark them as non-updateable (See wxDbColDef for details). This way columns can be selected and displayed from other tables, but only the base table will be updated automatically when performed through the wxDbTable::Update function after using this type of query. To update tables other than the base table, use the wxDbTable::Update function passing a SQL statement.
After this function has been called, the cursor is positioned before the first record in the record set. To retrieve the first record, call either wxDbTable::GetFirst or wxDbTable::GetNext.
Example
// Incomplete code samples
wxString sqlStmt;
sqlStmt = "SELECT * FROM PARTS WHERE STORAGE_DEVICE = 'SD98' \
AND CONTAINER = 12";
// Query the records using the SQL SELECT statement above
parts->QueryBySqlStmt(sqlStmt);
// Display all records queried
while(parts->GetNext())
dispPart(&parts);
Example SQL statements
----------------------
// Table Join returning 3 columns
SELECT PART_NUM, part_desc, sd_name
from parts, storage_devices
where parts.storage_device_id =
storage_devices.storage_device_id
// Aggregate function returning total number of
// parts in container 99
SELECT count(*) from PARTS where container = 99
// Order by clause; ROWID, scalar function
SELECT PART_NUM, substring(part_desc, 1, 10), qty_on_hand + 1, ROWID
from parts
where warehouse = 10
order by PART_NUM desc // descending order
// Subquery
SELECT * from parts
where container in (select container
from storage_devices
where device_id = 12)
virtual bool QueryMatching(bool forUpdate=false, bool distinct=false)
QueryMatching allows querying of records from the table associated with the wxDbTable object by matching "columns" to values.
For example: To query the datasource for the row with a PART_NUMBER column value of "32", clear all column variables of the wxDbTable object, set the PartNumber variable that is bound to the PART_NUMBER column in the wxDbTable object to "32", and then call wxDbTable::QueryMatching().
Parameters
forUpdate
Remarks
The SQL WHERE clause is built by the ODBC class library based on all non-zero/non-NULL columns in your wxDbTable object. Matches can be on one, many or all of the wxDbTable's columns. The base table name is prepended to the column names in the event that the wxDbTable's FROM clause is non-null.
This function cannot be used to perform queries which will check for columns that are 0 or NULL, as the automatically constructed WHERE clause only will contain comparisons on column member variables that are non-zero/non-NULL.
The primary difference between this function and wxDbTable::QueryOnKeyFields is that this function can query on any column(s) in the wxDbTable object. Note however that this may not always be very efficient. Searching on non-indexed columns will always require a full table scan.
The cursor is positioned before the first record in the record set after the query is performed. To retrieve the first record, the program must call either wxDbTable::GetFirst or wxDbTable::GetNext.
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause are ignored by this function.
Example
// Incomplete code sample
parts->ClearMemberVars(); // Set all columns to zero
wxStrcpy(parts->PartNumber,"32"); // Set columns to query on
parts->OnHold = true;
parts->QueryMatching(); // Query
// Display all records queried
while(parts->GetNext())
dispPart(parts); // Some application defined function
bool QueryOnKeyFields(bool forUpdate=false, bool distinct=false)
QueryOnKeyFields provides an easy mechanism to query records in the table associated with the wxDbTable object by the primary index column(s). Simply assign the primary index column(s) values and then call this member function to retrieve the record.
Note that since primary indexes are always unique, this function implicitly always returns a single record from the database. The base table name is prepended to the column names in the event that the wxDbTable's FROM clause is non-null.
Parameters
forUpdate
Remarks
The cursor is positioned before the first record in the record set after the query is performed. To retrieve the first record, the program must call either wxDbTable::GetFirst or wxDbTable::GetNext.
WHERE and FROM clauses specified using wxDbTable::SetWhereClause and wxDbTable::SetFromClause are ignored by this function.
Example
// Incomplete code sample
wxStrcpy(parts->PartNumber, "32");
parts->QueryOnKeyFields();
// Display all records queried
while(parts->GetNext())
dispPart(parts); // Some application defined function
bool Refresh()
This function re-reads the bound columns into the memory variables, setting them to the current values stored on the disk.
The cursor position and result set are unaffected by calls to this function. (The one exception is in the case where the record to be refreshed has been deleted by some other user or transaction since it was originally retrieved as part of the result set. For most datasources, the default behavior in this situation is to return the value that was originally queried for the result set, even though it has been deleted from the database. But this is datasource dependent, and should be tested before relying on this behavior.)
Remarks
This routine is only guaranteed to work if the table has a unique primary index defined for it. Otherwise, more than one record may be fetched and there is no guarantee that the correct record will be refreshed. The table's columns are refreshed to reflect the current data in the database.
bool SetColDefs(UWORD index, const wxString &fieldName, int dataType, void *pData, SWORD cType, int size, bool keyField = false, bool updateable = true, bool insertAllowed = true, bool derivedColumn = false)
wxDbColDataPtr * SetColDefs(wxDbColInf *colInfs, UWORD numCols)
Parameters
index
DB_DATA_TYPE_VARCHAR : strings
DB_DATA_TYPE_INTEGER : non-floating point numbers
DB_DATA_TYPE_FLOAT : floating point numbers
DB_DATA_TYPE_DATE : dates
DB_DATA_TYPE_BLOB : binary large objects
DB_DATA_TYPE_MEMO : large strings
pData
SQL_C_CHAR // string - deprecated: use SQL_C_WXCHAR
SQL_C_WXCHAR // string - Used transparently in unicode or non-unicode builds
SQL_C_LONG
SQL_C_ULONG
SQL_C_SHORT
SQL_C_USHORT
SQL_C_FLOAT
SQL_C_DOUBLE
SQL_C_NUMERIC
SQL_C_TIMESTAMP
SQL_C_BOOLEAN // defined in db.h
SQL_C_ENUM // defined in db.h
sizecolInfs
Remarks
If pData is to hold a string of characters, be sure to include enough space for the NULL terminator in pData and in the byte count of size.
Using the first form of this function, if the column definition is not able to be created, a value of false is returned. If the specified index of the column exceeds the number of columns defined in the wxDbTable instance, an assert is thrown and logged (in debug builds) and a false is returned.
A failure to create the column definition in the second form results in a value of NULL being returned.
Both forms of this function provide a shortcut for defining the columns in your wxDbTable object. Use this function in any derived wxDbTable constructor when describing the column/columns in the wxDbTable object.
The second form of this function is primarily used when the wxDb::GetColumns function was used to query the datasource for the column definitions, so that the column definitions are already stored in wxDbColInf form. One example use of using wxDb::GetColumns then using this function is if a data table existed in one datasource, and the table's column definitions were to be copied over to another datasource or table.
Example
// Long way not using this function
wxStrcpy(colDefs[0].ColName, "PART_NUM");
colDefs[0].DbDataType = DB_DATA_TYPE_VARCHAR;
colDefs[0].PtrDataObj = PartNumber;
colDefs[0].SqlCtype = SQL_C_WXCHAR;
colDefs[0].SzDataObj = PART_NUMBER_LEN;
colDefs[0].KeyField = true;
colDefs[0].Updateable = false;
colDefs[0].InsertAllowed= true;
colDefs[0].DerivedCol = false;
// Shortcut using this function
SetColDefs(0, "PART_NUM", DB_DATA_TYPE_VARCHAR, PartNumber,
SQL_C_WXCHAR, PART_NUMBER_LEN, true, false, true, false);
void SetCursor(HSTMT *hstmtActivate = (void **) wxDB_DEFAULT_CURSOR)
Parameters
hstmtActivate
Remarks
When swapping between cursors, the member variables of the wxDbTable object are automatically refreshed with the column values of the row that the current cursor is positioned at (if any). If the cursor is not positioned, then the data in member variables is undefined.
The only way to return back to the cursor that was in use before this function was called is to programmatically determine the current cursor's HSTMT BEFORE calling this function using wxDbTable::GetCursor and saving a pointer to that cursor.
See also
wxDbTable::GetNewCursor, wxDbTable::GetCursor, wxDbTable::SetCursor
void SetFromClause(const wxString &From)
Accessor function for setting the private class member wxDbTable::from that indicates what other tables should be inner joined with the wxDbTable's base table for access to the columns in those other tables.
Synonym to this function is one form of wxDbTable::From
Parameters
From
Remarks
Used by the wxDbTable::Query and wxDbTable::Count member functions to allow inner joining of records from multiple tables.
Do not include the keyword "FROM" when setting the FROM clause.
If using the FROM clause when performing a query, be certain to include in the corresponding WHERE clause a comparison of a column from either the base table or one of the other joined tables to each other joined table to ensure the datasource knows on which column values the tables should be joined on.
Example
...
// Base table is the "LOCATION" table, and it is being
// inner joined to the "PART" table via the field "PART_NUMBER"
// that can be related between the two tables.
location->SetWhereClause("LOCATION.PART_NUMBER = PART.PART_NUMBER")
location->SetFromClause("PART");
...
See alsowxDbTable::From, wxDbTable::GetFromClause
bool SetColNull(UWORD colNumber, bool set=true)
bool SetColNull(const wxString &colName, bool set=true)
Both forms of this function allow a member variable representing a column in the table associated with this wxDbTable object to be set to NULL.
The first form allows the column to be set by the index into the column definitions used to create the wxDbTable instance, while the second allows the actual column name to be specified.
Parameters
colNumber
Remarks
No database updates are done by this function. It only operates on the member variables in memory. Use and insert or update function to store this value to disk.
void SetOrderByClause(const wxString &OrderBy)
Accessor function for setting the private class member wxDbTable::orderBy which determines sequence/ordering of the rows returned in the result set of a query.
A synonym to this function is one form of the function wxDbTable::OrderBy
Parameters
OrderBy
Remarks
Do not include the keywords "ORDER BY" when setting the ORDER BY clause.
Example
...
parts->SetOrderByClause("PART_DESCRIP, QUANTITY");
...
...
location->SetOrderByClause("LOCATION.POSITION, PART.PART_NUMBER);
...
See alsowxDbTable::OrderBy, wxDbTable::GetOrderByClause
bool SetQueryTimeout(UDWORD nSeconds)
Allows a time period to be set as the timeout period for queries.
Parameters
nSeconds
Remarks
Neither Oracle or Access support this function as of yet. Other databases should be evaluated for support before depending on this function working correctly.
void SetWhereClause(const wxString &Where)
Accessor function for setting the private class member wxDbTable::where that determines which rows are returned in the result set by the datasource.
A synonym to this function is one form of the function wxDbTable::Where
Parameters
Where
Remarks
Do not include the keywords "WHERE" when setting the WHERE clause.
Example
...
// Simple where clause
parts->SetWhereClause("PART_NUMBER = '32'");
...
// Any comparison operators
parts->SetWhereClause("PART_DESCRIP LIKE 'HAMMER%'");
...
// Multiple comparisons, including a function call
parts->Where("QTY > 0 AND {fn UCASE(PART_DESCRIP)} LIKE '%DRILL%'");
...
// Using parameters and multiple logical combinations
parts->Where("((QTY > 10) OR (ON_ORDER > 0)) AND ON_HOLD = 0");
...
// This query uses an inner join (requiring a FROM clause also)
// that joins the PART and LOCATION table on he common field
// PART_NUMBER.
parts->Where("PART.ON_HOLD = 0 AND \
PART.PART_NUMBER = LOCATION.PART_NUMBER AND \
LOCATION.PART_NUMBER > 0");
See alsowxDbTable::Where, wxDbTable::GetWhereClause
bool Update()
bool Update(const wxString &pSqlStmt)
The first form of this function will update the row that the current cursor is currently positioned at with the values in the memory variables that are bound to the columns. The actual SQL statement to perform the update is automatically created by the ODBC class, and then executed.
The second form of the function allows full access through SQL statements for updating records in the database. Write any valid SQL UPDATE statement and submit it to this function for execution. Sophisticated updates can be performed using the full power of the SQL dialect. The full SQL statement must have the exact syntax required by the driver/datasource for performing the update. This usually is in the form of:
UPDATE tablename SET col1=X, col2=Y, ... where ...
ParameterspSqlStmt
Remarks
A wxDb::CommitTrans or wxDb::RollbackTrans must be called after use of this function to commit or rollback the update.
Example
wxString sqlStmt;
sqlStmt = "update PART set QTY = 0 where PART_NUMBER = '32'";
bool UpdateWhere(const wxString &pWhereClause)
Performs updates to the base table of the wxDbTable object, updating only the rows which match the criteria specified in the pWhereClause.
All columns that are bound to member variables for this wxDbTable instance that were defined with the "updateable" parameter set to true will be updated with the information currently held in the memory variable.
Parameters
pWhereClause
Remarks
Care should be used when updating columns that are part of indexes with this function so as not to violate an unique key constraints.
A wxDb::CommitTrans or wxDb::RollbackTrans must be called after use of this function to commit or rollback the update(s).
const wxString & Where()
void Where(const wxString& Where)
Accessor function for the private class member wxDbTable::where. Can be used as a synonym for wxDbTable::GetWhereClause (the first form of this function) to return the current where clause or wxDbTable::SetWhereClause (the second form of this function) to set the where clause for this table instance.
Parameters
Where
Return value
The first form of this function returns the current value of the wxDbTable member variable ::where.
The second form of the function has no return value, as it will always set the where clause successfully.
See also
wxDbTable::GetWhereClause, wxDbTable::SetWhereClause
bool operator ++()
Synonym for wxDbTable::GetNext
See also
bool operator --()
Synonym for wxDbTable::GetPrev
See also