wxDbTable

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

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.]

wxDbTable::wxDbTable

wxDbTable(wxDb * pwxDb, const wxString & tblName, const UWORD numColumns, const wxString & qryTblName = "", bool qryOnly = !wxDB_QUERY_ONLY, const wxString & tblPath = "")

Default constructor.

Parameters

pwxDb: Pointer to the wxDb instance to be used by this wxDbTable instance.

tblName: The name of the table in the RDBMS.

numColumns: The number of columns in the table. (Do NOT include the ROWID column in the count if using Oracle).

qryTblName: OPTIONAL. The name of the table or view to base your queries on. This argument allows you to specify a table/view other than the base table for this object to base your queries on. This allows you to query on a view for example, but all of the INSERT, UPDATE and DELETES will still be performed on the base table for this wxDbTable object. Basing your queries on a view can provide a substantial performance increase in cases where your queries involve many tables with multiple joins. Default is "".

qryOnly: OPTIONAL. Indicates whether the table will be accessible for query purposes only, or should the table create the necessary cursors to be able to insert, update, and delete data from the table. Default is !wxDB_QUERY_ONLY.

tblPath: OPTIONAL. Some datasources (such as dBase) require a path to where the table is stored on the system. Default is "".

wxDbTable::wxDbTable

virtual ~wxDbTable()

Virtual default destructor.

wxDbTable::BuildDeleteStmt

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: Pointer to buffer for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfDel: The type of delete statement being performed. Can be one of three values: DB_DEL_KEYFIELDS, DB_DEL_WHERE or DB_DEL_MATCHING

pWhereClause: OPTIONAL. If the typeOfDel is DB_DEL_WHERE, then you must also pass in a SQL WHERE clause in this argument. Default is "".

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.

wxDbTable::BuildSelectStmt

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: Pointer to storage for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfSelect: The type of select statement being performed. Can be one of four values: DB_SELECT_KEYFIELDS, DB_SELECT_WHERE, DB_SELECT_MATCHING or DB_SELECT_STATEMENT.

distinct: Whether to select distinct records only.

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.

wxDbTable::BuildUpdateStmt

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: Pointer to storage for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfUpdate: The type of update statement being performed. Can be one of two values: DB_UPD_KEYFIELDS or DB_UPD_WHERE.

pWhereClause: OPTIONAL. If the typeOfUpdate is DB_UPD_WHERE, then you must also pass in a SQL WHERE clause in this argument. Default is "".

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.

wxDbTable::BuildWhereClause

void BuildWhereClause(wxString & pWhereClause, int typeOfWhere, const wxString & qualTableName="", bool useLikeComparison=false)

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: Pointer to storage for the SQL statement retrieved. To be sure you have adequate space allocated for the SQL statement, allocate DB_MAX_STATEMENT_LEN bytes.

typeOfWhere: The type of where clause to generate. Can be one of two values: DB_WHERE_KEYFIELDS or DB_WHERE_MATCHING.

qualTableName: OPTIONAL. Prepended to all base table column names. For use when a FROM clause has been specified with the wxDbTable::SetFromClause, to clarify which table a column name reference belongs to. Default is "".

useLikeComparison: OPTIONAL. Should the constructed WHERE clause utilize the LIKE comparison operator. If false, then the '=' operator is used. Default is false.

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.

wxDbTable::CanSelectForUpdate

bool CanSelectForUpdate(void)

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.

wxDbTable::CanUpdateByROWID

bool CanUpdateByROWID(void)

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";

wxDbTable::ClearMemberVar

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: Column number that is to be cleared. This number (between 0 and (numColumns-1)) is the index of the column definition created using the wxDbTable::SetColDefs function.

setToNull: OPTIONAL. Indicates whether the column should be flagged as being a NULL value stored in the bound memory variable. If true, then any value stored in the bound member variable is cleared. Default is false.

wxDbTable::ClearMemberVars

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: OPTIONAL. Indicates whether all columns should be flagged as having a NULL value stored in the bound memory variable. If true, then any value stored in the bound member variable is cleared. Default is false.

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()

wxDbTable::CloseCursor

bool CloseCursor(HSTMT cursor)

Closes the specified cursor associated with the wxDbTable object.

Parameters

cursor: The cursor to be closed.

Remarks

Typically handled internally by the ODBC class library, but may be used by the programmer if desired.

$\fbox{\vbox{DO NOT CLOSE THE wxDB\_DEFAULT\_CURSOR!}}$

wxDbTable::Count

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: OPTIONAL. This argument allows the use of the DISTINCT keyword against a column name to cause the returned count to only indicate the number of rows in the result set that have a unique value in the specified column. An example is shown below. Default is "*", meaning a count of the total number of rows matching is returned, regardless of uniqueness.

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");

wxDbTable::CreateIndex

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: Name of the Index. Name must be unique within the table space of the datasource.

unique: Indicates if this index is unique.

numIndexColumns: Number of columns in the index.

pIndexDefs: A pointer to an array wxDbIdxDef structures.

attemptDrop: OPTIONAL. Indicates if the function should try to execute a wxDbTable::DropIndex on the index name provided before trying to create the index name. Default is true.

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);

wxDbTable::CreateTable

bool CreateTable(bool attemptDrop=true)

Creates a table based on the definitions previously defined for this wxDbTable instance.

Parameters

attemptDrop: OPTIONAL. Indicates whether the driver should attempt to drop the table before trying to create it. Some datasources will not allow creation of a table if the table already exists in the table space being used. Default is true.

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.

wxDbTable::DB_STATUS

bool DB_STATUS(void)

Accessor function that returns the wxDb private member variable DB_STATUS for the database connection used by this instance of wxDbTable.

wxDbTable::Delete

bool Delete(void)

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.

wxDbTable::DeleteCursor

bool DeleteCursor(HSTMT * hstmtDel)

Allows a program to delete a cursor.

Parameters

hstmtDel: Handle of the cursor to delete.

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.

$\fbox{\vbox{DO NOT DELETE THE wxDB\_DEFAULT\_CURSOR!}}$

wxDbTable::DeleteMatching

bool DeleteMatching(void)

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:

Clear all "columns" using wxDbTable::ClearMemberVars().
Set the FIRST_NAME column equal to "JOHN".
Call wxDbTable::DeleteMatching().

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();

wxDbTable::DeleteWhere

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: SQL WHERE clause. This WHERE clause determines which records will be deleted from the table interfaced through the wxDbTable instance. The WHERE clause passed in must be compliant with the SQL 92 grammar. Do not include the keyword 'WHERE'

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.

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'");

wxDbTable::DropIndex

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: Name of the index to be dropped.

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.

wxDbTable::DropTable

bool DropTable(void)

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.

wxDbTable::From

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: A comma separated list of table names that are to be outer joined with the base table's columns so that the joined table's columns may be returned in the result set or used as a portion of a comparison with the base table's columns. NOTE that the base tables name must NOT be included in the FROM clause, as it is automatically included by the wxDbTable class in constructing query statements.

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.

wxDbTable::GetColDefs

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.

wxDbTable::GetCursor

HSTMT GetCursor(void)

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.

wxDbTable::GetDb

wxDb * GetDb()

Accessor function for the private member variable pDb which is a pointer to the datasource connection that this wxDbTable instance uses.

wxDbTable::GetFirst

bool GetFirst(void)

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

wxDb::IsFwdOnlyCursors

wxDbTable::GetFromClause

const wxString & GetFromClause()

Accessor function that returns the current FROM setting assigned with the wxDbTable::SetFromClause.

See also

wxDbTable::From

wxDbTable::GetLast

bool GetLast(void)

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

See also

wxDb::IsFwdOnlyCursors

wxDbTable::GetNewCursor

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: OPTIONAL. Should this new cursor be set to be the current cursor after successfully creating the new cursor. Default is false.

bindColumns: OPTIONAL. Should this new cursor be bound to all the memory variables that the default cursor is bound to. Default is true.

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.

wxDbTable::GetNext

bool GetNext(void)

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::++

wxDbTable::GetNumberOfColumns

UWORD GetNumberOfColumns()

Accessor function that returns the number of columns that are statically bound for access by the wxDbTable instance.

wxDbTable::GetOrderByClause

const wxString & GetOrderByClause()

Accessor function that returns the current ORDER BY setting assigned with the wxDbTable::SetOrderByClause.

See also

wxDbTable::OrderBy

wxDbTable::GetPrev

bool GetPrev(void)

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

wxDbTable::GetQueryTableName

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

wxDbTable constructor

wxDbTable::GetRowNum

UWORD GetRowNum(void)

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.

wxDbTable::GetTableName

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.

wxDbTable::GetTablePath

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.

wxDbTable::GetWhereClause

const wxString & GetWhereClause()

Accessor function that returns the current WHERE setting assigned with the wxDbTable::SetWhereClause

See also

wxDbTable::Where

wxDbTable::Insert

int Insert(void)

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.

Remarks

A 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);
    }

wxDbTable::IsColNull

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: The column number of the bound column as defined by the wxDbTable::SetColDefs calls which defined the columns accessible to this wxDbTable instance.

Remarks

NULL column support is currently not fully implemented as of wxWidgets 2.4.

wxDbTable::IsCursorClosedOnCommit

bool IsCursorClosedOnCommit(void)

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.

wxDbTable::IsQueryOnly

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.

wxDbTable::Open

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: Indicates whether the Open() function should check whether the current connected user has at least SELECT privileges to access the table to which they are trying to open. Default is false.

checkTableExists: Indicates whether the Open() function should check whether the table exists in the database or not before opening it. Default is true.

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.

wxDbTable::OrderBy

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: A comma separated list of column names that indicate the alphabetized/numeric sorting sequence that the result set is to be returned in. If a FROM clause has also been specified, each column name specified in the ORDER BY clause should be prefaced with the table name to which the column belongs using DOT notation (TABLE_NAME.COLUMN_NAME).

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.

wxDbTable::Query

virtual bool Query(bool forUpdate=false, bool distinct=false)

Parameters

forUpdate: OPTIONAL. Gives you the option of locking records as they are retrieved. If the RDBMS is not capable of the FOR UPDATE clause, this argument is ignored. See wxDbTable::CanSelectForUpdate for additional information regarding this argument. Default is false.

distinct: OPTIONAL. Allows selection of only distinct values from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT applies to all columns returned in the result set, not individual columns. Default is false.

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 outer 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.

$\fbox{\vbox{Be sure to set the wxDbTable's ''where'', ''orderBy'', and ''from'' ... ...oper or incorrect query parameters constructed from the uninitialized clauses.}}$

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

wxDbTable::QueryBySqlStmt

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: Pointer to the SQL SELECT statement to be executed.

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:

Must return the correct number of columns. In the derived wxDbTable constructor, it is specified how many columns are in the wxDbTable object. The SELECT statement must return exactly that many columns.
The columns must be returned in the same sequence as specified when defining the bounds columns wxDbTable::SetColDefs, and the columns returned must be of the proper data type. For example, if column 3 is defined in the wxDbTable bound column definitions to be a float, the SELECT statement must return a float for column 3 (e.g. PRICE * 1.10 to increase the price by 10
The ROWID can be included in your SELECT statement as the last column selected, if the datasource supports it. Use wxDbTable::CanUpdateByROWID() to determine if the ROWID can be selected from the datasource. If it can, much better performance can be achieved on updates and deletes by including the ROWID in the SELECT statement.

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)

wxDbTable::QueryMatching

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: OPTIONAL. Gives you the option of locking records as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of the FOR UPDATE clause, this argument is ignored. See wxDbTable::CanSelectForUpdate for additional information regarding this argument. Default is false.

distinct: OPTIONAL. Allows selection of only distinct values from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT applies to all columns returned in the result set, not individual columns. Default is false.

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

wxDbTable::QueryOnKeyFields

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: OPTIONAL. Gives you the option of locking records as they are queried (SELECT ... FOR UPDATE). If the RDBMS is not capable of the FOR UPDATE clause, this argument is ignored. See wxDbTable::CanSelectForUpdate for additional information regarding this argument. Default is false.

distinct: OPTIONAL. Allows selection of only distinct values from the query (SELECT DISTINCT ... FROM ...). The notion of DISTINCT applies to all columns returned in the result set, not individual columns. Default is false.

Remarks

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

wxDbTable::Refresh

bool Refresh(void)

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.

wxDbTable::SetColDefs

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: Column number (0 to n-1, where n is the number of columns specified as being defined for this wxDbTable instance when the wxDbTable constructor was called.

fieldName: Column name from the associated data table.

dataType: Logical data type. Valid logical types include:

    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

pData: Pointer to the data object that will hold the column's value when a row of data is returned from the datasource.

cType: SQL C Type. This defines the data type that the SQL representation of the data is converted to to be stored in pData. Other valid types are available also, but these are the most common ones:

    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

size: Maximum size in bytes of the pData object.

keyField: OPTIONAL. Indicates if this column is part of the primary index. Default is false.

updateable: OPTIONAL. Are updates allowed on this column? Default is true.

insertAllowed: OPTIONAL. Inserts allowed on this column? Default is true.

derivedColumn: OPTIONAL. Is this a derived column (non-base table column for query only)? Default is false.

colInfs: Pointer to an array of wxDbColInf instances which contains all the information necessary to create numCols column definitions.

numCols: Number of elements of wxDbColInf type that are pointed to by colInfs, which are to have column definitions created from them.

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);

wxDbTable::SetCursor

void SetCursor(HSTMT * hstmtActivate = (void **) wxDB_DEFAULT_CURSOR)

Parameters

hstmtActivate: OPTIONAL. Pointer to the cursor that is to become the current cursor. Passing no cursor handle will reset the cursor back to the wxDbTable's default (original) cursor that was created when the wxDbTable instance was first created. Default is wxDB_DEFAULT_CURSOR.

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.

wxDbTable::SetFromClause

void SetFromClause(const wxString & From)

Accessor function for setting the private class member wxDbTable::from that indicates what other tables should be outer 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: A comma separated list of table names that are to be outer joined with the base table's columns so that the joined table's columns may be returned in the result set or used as a portion of a comparison with the base table's columns. NOTE that the base tables name must NOT be included in the FROM clause, as it is automatically included by the wxDbTable class in constructing query statements.

Remarks

Used by the wxDbTable::Query and wxDbTable::Count member functions to allow outer 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
    // outer 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");
    ...

wxDbTable::SetColNull

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: Index into the column definitions used when first defining this wxDbTable object.

colName: Actual data table column name that is to be set to NULL.

set: Whether the column is set to NULL or not. Passing true sets the column to NULL, passing false sets the column to be non-NULL. Default is true.

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.

wxDbTable::SetOrderByClause

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: A comma separated list of column names that indicate the alphabetized sorting sequence that the result set is to be returned in. If a FROM clause has also been specified, each column name specified in the ORDER BY clause should be prefaced with the table name to which the column belongs using DOT notation (TABLE_NAME.COLUMN_NAME).

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);
    ...

wxDbTable::SetQueryTimeout

bool SetQueryTimeout(UDWORD nSeconds)

Allows a time period to be set as the timeout period for queries.

Parameters

nSeconds: The number of seconds to wait for the query to complete before timing out.

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.

wxDbTable::SetWhereClause

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: SQL "where" clause. This clause can contain any SQL language that is legal in standard where clauses. If a FROM clause has also been specified, each column name specified in the ORDER BY clause should be prefaced with the table name to which the column belongs using DOT notation (TABLE_NAME.COLUMN_NAME).

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 outer 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");

wxDbTable::Update

bool Update(void)

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 ...

Parameters

pSqlStmt: Pointer to SQL UPDATE statement to be executed.

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'";

wxDbTable::UpdateWhere

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: Pointer to a valid SQL WHERE clause. Do not include the keyword 'WHERE'.

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).

wxDbTable::Where

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