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 Members Helper classes and data structures The following classes and structs are defined in dbtable.cpp/.h for use with the wxDbTable class. * wxDbColDef * wxDbColDataPtr * wxDbIdxDef 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::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() 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() 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(HSTMTcursor) 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. 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() 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() 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. DO NOT DELETE THE wxDB_DEFAULT_CURSOR! !! wxDbTable::DeleteMatching 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: 1. Clear all "columns" using wxDbTable::ClearMemberVars(). 2. Set the FIRST_NAME column equal to "JOHN". 3. 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. 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'"); !! 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() 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. See also wxDbTable::GetFromClause, wxDbTable::SetFromClause !! 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() 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 !! 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() 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() 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 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() 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() 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::-- !! 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() 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() 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() 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. See also wxDb::TableExists, wxDb::TablePrivileges wxDbTable::SetColDefs !! 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. See also wxDbTable::GetOrderByClause, wxDbTable::SetFromClause !! 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. Be sure to set the wxDbTable's "where", "orderBy", and "from" member variables to "" if they are not to be used in the query. Otherwise, the results returned may have unexpected results (or no results) due to improper 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: 1. 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. 2. 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 3. 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 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 !! wxDbTable::Refresh 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.) <<注意>> この関数は、テーブルが唯一のプライマリインデックスを持っている場合にのみ動作することが保証されている。そうでなければ、１つ以上のレコードがフェッチされ、リフレッシュされるべき正しいレコードを保証できなくなる。データベース内の現在のデータを反映するために、テーブルのカラムがリフレッシュされる。 "" 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) <<引数>> ::index :::カラム数。（0からn-1。ここで、nはwxDbTableコンストラクタが呼ばれたときに、このwxDbTableインスタンスの定義をする際に指定されたカラム数である。） ::fieldName :::関連付けられるデータテーブル内のカラム名。 ::dataType :::論理的なデータ種別。有効な論理種別は以下の通り。 DB_DATA_TYPE_VARCHAR : 文字列 DB_DATA_TYPE_INTEGER : 非浮動小数点数 DB_DATA_TYPE_FLOAT : 浮動小数点数 DB_DATA_TYPE_DATE : 日付 ::pData :::データ行がデータソースから返される際に、カラムの値を保持するためのデータオブジェクトへのポインタ。 ::cType :::SQL C種別。データをSQL表現し、pDataに格納する際のデータ種別。他にも利用可能な種別があるが、最も共通なものは以下の通り。 SQL_C_CHAR // 文字列。できれば、SQL_C_WXCHARを使用すべき。 SQL_C_WXCHAR // 文字列。unicodeでも非unicodeでも意識せずに使用できる。 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 // db.hに定義されている SQL_C_ENUM // db.hに定義されている ::size :::pDataオブジェクトの最大バイト数。 ::keyField :::オプション。このカラムがプライマリインデックスの一部かどうかを示す。デフォルトはfalse。 ::updateable :::オプション。このカラムの更新が許されるかどうか。デフォルトはtrue。 ::insertAllowed :::オプション。このカラムへの挿入が許されるかどうか。デフォルトはtrue。 ::derivedColumn :::オプション。派生したカラム（クエリ専用で、ベーステーブルのカラムではない）かどうか。デフォルトはfalse。 ::colInfs :::numCols個のカラム定義を作成するために必要な全ての情報を含む、wxDbColInfインスタンスの配列へのポインタ。 ::numCols :::colInfsで示されるwxDbColInf型変数の要素数。wxDbColInfにより作成されたカラム定義である。 ""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. <<注意>> ''pData''が文字列の場合、'size'にはpDataにNULL終端を含められるだけのバイト数を指定し、そのスペースを確保しなければならない。 "" 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. この関数のはじめの書式を使用すると、カラム定義を作成できなかった場合には、falseが返る。指定されたカラムのインデックスがwxDbTableインスタンスで定義されたカラム数を超えると、assertを投げ。（デバッグビルドの場合は）ログに出力し、falseを返す。 "" 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. ２つ目の書式でカラム定義に失敗すると、NULLが返る。 "" A failure to create the column definition in the second form results in a value of NULL being returned. どちらの書式も、自身のwxDbTableオブジェクトでカラムを定義するためのショートカットが用意されている。wxDbTableオブジェクトでカラムを記述する際には、任意に派生されたwxDbTableコンストラクタの中でこの関数を使用すれば良い。 "" 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. カラム定義についてデータソースにクエリするためにwxDb::GetColums関数が使用される場合には、カラム定義が既にwxDbColInfoに格納されていなければならないため、その前にこの関数の２つ目の書式が使用される。wxDb::GetColumsを使用した後に個の関数を使用する使用例は、データテーブルが１つのデータソースに存在するときに、そのテーブルのカラム定義を他のデータソースやテーブルにコピーする場合である。 "" 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. <<使用例>> // この関数を使用しない冗長な記述 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; // この関数を使用したショートカット 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) <<引数>> ::hstmtActivate :::オプション。カレントカーソルに設定するカーソルへのポインタ。カーソルハンドルを渡さないと、wxDbTableインスタンスが始めに作られたときに作成された、wxDbTableのデフォルトの（オリジナルの）カーソルに戻すことができる。デフォルトはwxDB_DEFAULT_CURSORである。 ""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. <<注意>> カーソルを入れ替える際、wxDbTableオブジェクトのメンバ変数は、カレントカーソルが示している列のカラム値に自動的に書き換えられる。もしカーソルが何も示していない場合、メンバ変数内のデータは未定義である。 この関数を呼ぶ前に使用されていたカーソルに戻すための唯一の方法は、この関数を呼ぶ前にwxDbTable::GetCursorを使用してカレントカーソルのHSTMTをプログラム的に取得し、そのカーソルへのポインタを保存しておくことである。 "" 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::GetNewCursor, wxDbTable::GetCursor, wxDbTable::SetCursor !! wxDbTable::SetFromClause void SetFromClause(const wxString &From) 外部のテーブルに含まれるカラムにアクセスするため、wxDbTableのベーステーブルと外部結合される外部テーブルを示す、プライベートなクラスメンバwxDbTable::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 <<引数>> ::From :::結合されたテーブルのカラムが結果集合に返されるよう、また、そのカラムがベーステーブルのカラムと比較できるよう、ベーステーブルのカラムと外部結合されるテーブル名のリストをカンマ区切りしたリスト。wxDbTableクラスはクエリ文を作成する際、自動的にベーステーブルをFROM節に含めるため、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. <<注意>> 複数のテーブルのレコードを外部結合するために、wxDbTable::QueryとwxDbTable::Countメンバ関数から呼ばれる。 FROM節を使用する際、キーワード"FROM"を含んではならない。 クエリを実行するときにFROM節を使用する際、ベーステーブルのカラム名なのか、外部結合されたテーブルのカラム名なのか、どちらのカラムなのかをデータソースが知りうるよう、対応するWHERE節を含めなければならない。 "" 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. <<例>> ... // ベーステーブルは"LOCATION"テーブルであり、 // "PART"テーブルが共通フィールド"PART_NUMBER"を介して // 外部結合されている。 location->SetWhereClause("LOCATION.PART_NUMBER = PART.PART_NUMBER") location->SetFromClause("PART"); ... <<参考>> wxDbTable::From, wxDbTable::GetFromClause !! wxDbTable::SetColNull bool SetColNull(UWORD colNumber, bool set=true) bool SetColNull(const wxString &colName, bool set=true) どちらの書式も、このwxDbTableオブジェクトに関連付けられたテーブル内のカラムを象徴するメンバ変数をNULLに設定する。 １つ目の書式は、wxDbTableインスタンスを作成する際に使用されたカラム定義内のインデックスでカラムを指定する。一方、２つ目の書式は、指定された実際のカラム名を指定する。 "" 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. <<引数>> ::colNumber :::始めにこのwxDbTableオブジェクトを定義したときに使用したカラム定義のインデックス "" Index into the column definitions used when first defining this wxDbTable object. ::colName :::NULLにしたい、実際のデータテーブルカラム名 "" Actual data table column name that is to be set to NULL. ::set :::カラムをNULLにするかどうか。trueを渡すとカラムはNULLになり、falseを指定すると、NULLにしない。デフォルトはtrue。 "" 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. <<注意>> この関数はデータベースを更新しない。メモリ内のメンバ変数への操作だけである。この値をディスクに保存する場合は、挿入関数や更新関数を使用する。 "" 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) クエリの結果集合に含まれる行の順番を決定する、プライベートなクラスメンバwxDbTable::orderByを指定するためのアクセス関数。 関数wxDbTable::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 <<引数>> ::OrderBy :::返される結果集合をアルファベット順に並び替えることを示すカラム名をカンマ区切りしたリスト。FROM節も指定する場合、ORDER BY節で指定される各カラム名は、ドット表記を使用して、カラムが属するテーブル名に続いて記述される（TABLE_NAME.COLUMN_NAME）。 "" 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). <<注意>> ORDER BY節を設定する際、キーワード"ORDER BY"を含めてはならない。 "" Do not include the keywords "ORDER BY" when setting the ORDER BY clause. <<例>> ... parts->SetOrderByClause("PART_DESCRIP, QUANTITY"); ... ... location->SetOrderByClause("LOCATION.POSITION, PART.PART_NUMBER); ... <<参考>> wxDbTable::OrderBy, wxDbTable::GetOrderByClause !! wxDbTable::SetQueryTimeout bool SetQueryTimeout(UDWORD nSeconds) クエリのためのタイムアウト時間を設定する。 "" Allows a time period to be set as the timeout period for queries. <<引数>> ::nSeconds :::クエリが完了するまでのタイムアウト時間（秒数） "" The number of seconds to wait for the query to complete before timing out. <<注意>> 今のところ、OracleもAccessもこの関数をサポートしていない。この関数が正常に動作するかどうかに依存する前に、他のデータベースでサポートされているかどうか評価する必要がある。 "" 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) データソースによって結果集合に返される行を決定するための、プライベートなクラスメンバwxDbTable::whereを設定するためのアクセス関数。 この関数は、wxDbTable::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 <<引数>> ::Where :::SQL "where"節。この節には、正しいwhere節を含むSQL言語を含めることができる。FROM節も指定されている場合、ORDER BY節で指定された各カラム名は、ドット表記を使用することにより、カラムが属するテーブル名に続いて記述される（TABLE_NAME.COLUMN_NAME）。 "" 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). <<注意>> WHERE節を設定する際、キーワード"WHERE"を含んではならない。 "" Do not include the keywords "WHERE" when setting the WHERE clause. <<例>> ... // 単純なwhere節 parts->SetWhereClause("PART_NUMBER = '32'"); ... // 任意の比較操作 parts->SetWhereClause("PART_DESCRIP LIKE 'HAMMER%'"); ... // 関数呼び出しを含む、複数条件 parts->Where("QTY > 0 AND {fn UCASE(PART_DESCRIP)} LIKE '%DRILL%'"); ... // 引数と、複数の論理の組み合わせ parts->Where("((QTY > 10) OR (ON_ORDER > 0)) AND ON_HOLD = 0"); ... // このクエリは、共通なフィールドPART_NUMBERを持つ // PARTとLOCATIONテーブルを結合して（FROM節で要求された）外部結合を使用する parts->Where("PART.ON_HOLD = 0 AND \ PART.PART_NUMBER = LOCATION.PART_NUMBER AND \ LOCATION.PART_NUMBER > 0"); <<参考>> wxDbTable::Where, wxDbTable::GetWhereClause !! wxDbTable::Update bool Update() bool Update(const wxString &pSqlStmt) 始めの書式は、現在のカーソルが現在示している行を、カラムにバインドされたメモリ変数内の変数で更新する。更新を実行するための実際のSQL文は、ODBCクラスにより自動的に作成され、実行される。 "" 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. ２つ目の書式は、データベース内のレコードを更新するためのSQL文を介したフルアクセスが可能である。正確なSQL UPDATE文を書き、実行するためにこの関数に渡さなければならない。SQLの方言を最大限に使用することにより、より高度な更新を実行できる。SQL文は、更新を実行するためにドライバ、データソースに要求された正確なシンタックスである必要である。通常、以下のように使用される。 UPDATE tablename SET col1=X, col2=Y, ... where ... "" 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 ... <<引数>> ::pSqlStmt :::実行するSQL UPDATE文へのポインタ "" Pointer to SQL UPDATE statement to be executed. <<注意>> 更新をコミット、ロールバックするために、この関数を使用した後、wxDb::CommitTransまたはwxDb::RollbackTransを呼ばなければならない。 "" A wxDb::CommitTrans or wxDb::RollbackTrans must be called after use of this function to commit or rollback the update. <<例>> wxString sqlStmt; sqlStmt = "update PART set QTY = 0 where PART_NUMBER = '32'"; !! wxDbTable::UpdateWhere bool UpdateWhere(const wxString &pWhereClause) wxDbTableオブジェクトのベーステーブルを更新する。この時、pWhereClauseで指定された判断基準に合致する行だけが更新される。 このwxDbTableインスタンスのメンバ変数にバインドされた、更新可能であると定義された全てのカラムに対し、現在メモリ変数に保持している情報で更新する。 "" 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. <<引数>> ::pWhereClause :::正確なSQL WHERE節へのポインタ。キーワード'WHERE'を含んではならない。 "" Pointer to a valid SQL WHERE clause. Do not include the keyword 'WHERE'. <<注意>> この関数を使用してインデックスの一部であるカラムを更新する場合には、ユニークなキー制約を破らないように使用しなければならない。 更新のコミットやロールバックのために、この関数を使用した後、wxDb::CommitTransまたはwxDb::RollbackTransを呼ばなければならない。 "" 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) プライベートなクラスメンバwxDbTable::whereへのアクセス関数。このテーブルインスタンスに対する、現在のwhere節を返すためのwxDbTable::GetWhereClause（始めの書式）や、where節を設定するためのwxDbTable::SetWhereClause（２つ目の書式）と同じように使うことができる。 <<引数>> ::Where :::正当なSQLのWHERE節。キーワード'WHERE'を含んではならない。 <<戻り値>> 始めの書式の関数は、wxDbTableメンバ変数::whereの現在の値を返す。 ２つ目の書式の関数は、戻り値を持たず、成功したときには常にwhere節を設定する。 <<参考>> wxDbTable::GetWhereClause, wxDbTable::SetWhereClause !! wxDbTable::operator ++ bool operator ++() wxDbTable::GetNextと同じものである。 <<参考>> wxDbTable::GetNext !! wxDbTable::operator -- bool operator --() wxDbTable::GetPrevと同じものである。 <<参考>> wxDbTable::GetPrev