wxJavaScript : db - wxDbTable

back to db

wxDbTable

^{generated from ../src/mod_db/dbtable.cpp}

A wxDbTable instance provides re-usable access to rows of data in a table contained within the associated ODBC datasource.

Constants

TypeOfDel

Name	Description
DEL_WHERE
DEL_KEYFIELDS
DEL_MATCHING

TypeOfSel

Name	Description
SELECT_WHERE
SELECT_KEYFIELDS
SELECT_MATCHING
SELECT_STATEMENT

TypeOfUpd

Name	Description
UPD_WHERE
UPD_KEYFIELDS

TypeOfWhere

Name	Description
WHERE_KEYFIELDS
WHERE_MATCHING

Constructor

wxDbTable

wxDbTable(Db, 
          Table, 
          NumColumns, 
          QryTableName = "", 
          QryOnly = true, 
          Path = "")

Name	Type	Default	Description
Db	wxDb		The database to use
Table	String		The name of the base table
NumColumns	Integer		Number of columns to retrieve
QryTableName	String		The name of the table or view to base your queries on.
QryOnly	boolean	true	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.
Path	String		The path where the table is stored (for file based datasources like Dbase).

Constructs a new wxDbTable object

Properties

Name	Type	Description
CanUpdByROWID	Boolean	Returns true when the datasource can use ROWID (Oracle only)
canSelectForUpdate	Boolean	Returns true when the datasource can handle "SELECT ... FOR UPDATE"
colDefs	wxDbColDef array	An array with all column definitions.
db	wxDb	Gets the wxDb object.
encoding	String	The encoding to use for string columns. If one column has another encoding you can use encoding to override this. The default encoding is UTF-8.
from	String	Get/Set 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. Remark: 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.
isCursorClosedOnCommit	Boolean	Returns true if the cursor associated with this wxDbTable object is closed after a commit or rollback operation.
isQueryOnly	Boolean	Returns a value indicating if this wxDbTable object was created to allow only queries to be performed on the bound columns.
numberOfColumns	Integer	Number of columns
orderBy	String	A comma separated list of column names that indicate the alphabetized/numeric sorting sequence that the result set is to be returned in. Do not include 'ORDER BY'.
tableName	String	Gets the base tablename
tablePath	String	Gets the path to the data table.
where	String	Get/Set the WHERE clause. Do not include the keyword 'WHERE'.

Methods

buildDeleteStmt

buildDeleteStmt(TypeOfDel, 
                WhereClause = "") : String

Name	Type	Default	Description
TypeOfDel	Integer		Type of deletion. See TypeOfDel.
WhereClause	String		If the TypeOfDel is DEL_WHERE, then you must also pass in a SQL WHERE clause in this argument. Default is "".

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 whereClause and fromClause are ignored by this function.

buildSelectStmt

buildSelectStmt(TypeOfSel, 
                Distinct) : String

Name	Type	Default	Description
TypeOfSel	Integer		Type of selection. See TypeOfSel.
Distinct	Boolean		Whether to select distinct records only

buildUpdateStmt

buildUpdateStmt(TypeOfUpdate, 
                WhereClause = "") : String

Name	Type	Default	Description
TypeOfUpdate	Integer		Type of deletion. See TypeOfUpdate.
WhereClause	String		If the TypeOfDel is DEL_WHERE, then you must also pass in a SQL WHERE clause in this argument. Default is "".

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 where and from are ignored by this function.

buildWhereStmt

buildWhereStmt(TypeOfWhere, 
               QualTableName = "", 
               UseLikeComparison = false) : String

Name	Type	Default	Description
TypeOfWhere	Integer		The type of the where clause to generate. See TypeOfWhere
QualTableName	String		Prepended to all base table column names. For use when a FROM clause has been specified with the from, to clarify which table a column name reference belongs to
UseLikeComparison	Boolean	false	Should the constructed WHERE clause utilize the LIKE comparison operator. If false, then the '=' operator is used.

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.

clearMemberVar

clearMemberVar(ColNo, 
               SetToNull = false)

Name	Type	Default	Description
ColNo	Integer		This number (between 0 and (numberOfColumns - 1)
SetToNull	Boolean	false	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

Initializes a bound column of the wxDbTable instance to zero. In the case of a string, zero is copied to the first byte of the string.

clearMemberVars

clearMemberVars(SetToNull = false)

Name	Type	Default	Description
SetToNull	Boolean	false	Indicates whether the columns 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.

This is useful before calling functions such as queryMatching or deleteMatching since these functions build their WHERE clauses from non-zero columns. To call either queryMatching or deleteMatching use this sequence:

clearMemberVars()
Assign columns values you wish to match on
Call queryMatching() or deleteMatching()

close

close()

This method doesn't exist in wxWidgets, but is added because some databases needs the possibility to close a database. In wxWidgets a database can't be closed, when there are still tables active. In FoxFire for example, it is necessary to close the database manually, because otherwise the data is not written to the database. In normal circumstances this method should not be used! For more information see link.

count

count(Args = *) : Integer

Name	Type	Default	Description
Args	String	*	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. Default is "*", meaning a count of the total number of rows matching is returned, regardless of uniqueness.

Returns the number of records which would be in the result set using the current query parameters specified in the WHERE and FROM clauses.
Example:


      var users = new wxDbTable();
      users.where = "";
      var totalNumberOfUsers = users.count();
      var totalNumberOfUniqueFirstNames = users.count("DISTINCT FIRST_NAME");

createIndex

createIndex(IdxName, 
            Unique, 
            IdxDefs, 
            AttempDrop = true) : Boolean

Name	Type	Default	Description
IdxName	String		Name of the Index. Name must be unique within the table space of the datasource.
Unique	Boolean		Indicates if this index is unique
IdxDefs	wxDbIdxDef array		An array with wxDbIdxDef elements
AttempDrop	Boolean	true	Indicates if the function should try to execute a dropIndex on the index name provided before trying to create the index name. Default is 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.

createTable

createTable(AttemptDrop = true) : Boolean

Name	Type	Default	Description
AttemptDrop	Boolean	true	Indicates whether the driver should attempt to drop the table before trying to create it. Default is true.

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

deleteMatching

deleteMatching() : Boolean

This method allows you to delete records from your wxDbTable object by specifying the data in the columns to match on. To delete all users with a first name of "JOHN", do the following:

Clear all "columns" using clearMemberVars.
Set the FIRST_NAME column equal to "JOHN".
Call 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 commitTrans or rollbackTrans must be called after use of this function to commit or rollback the deletion.

deleteRow

deleteRow() : Boolean

Deletes the row from the table indicated by the current cursor. Don't forget to set the keyField property of the key fields, because this is used by wxDbTable to know which row to delete.

Remark: This method is renamed to deleteRow, because delete is a reserved word in JavaScript.

deleteWhere

deleteWhere(Where) : Boolean

Name	Type	Default	Description
Where	String		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'.

Deletes all rows from the table which match the criteria specified in the WHERE clause that is passed in. 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 commitTrans must be called after use of this function to commit the deletions.

dropIndex

dropIndex(Index) : Boolean

Name	Type	Default	Description
Index	String		Name of the index to drop

Allows an index on the associated table to be dropped (deleted) if the user login has sufficient privileges to do so.

dropTable

dropTable() : Boolean

Deletes the associated table if the user has sufficient privileges to do so.

getFirst

getFirst() : Boolean

Retrieves the FIRST row in the record set as defined by the current query. Before retrieving records, a query must be performed using query, queryOnKeyFields, queryMatching or queryBySqlStmt.

Remarks : This function can only be used when isFwdOnlyCursors is 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.

getLast

getLast() : Boolean

Retrieves the LAST row in the record set as defined by the current query. Before retrieving records, a query must be performed using query, queryOnKeyFields, queryMatching or queryBySqlStmt.

Remarks : This function can only be used when isFwdOnlyCursors is 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.

getNext

getNext() : Boolean

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 query, queryOnKeyFields, queryMatching or queryBySqlStmt. 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.

An example:


    var table = new wxDbTable(db, "user", 2);
    var col = table.colDefs[0];
    col.colName = "id";
    col.dbDataType = wxDb.DATA_TYPE_INTEGER;

    col = table.colDefs[1];
    col.colName = "name";
    col.dbDataType = wxDb.DATA_TYPE_VARCHAR;
    col.sizeData = 30;

    if ( table.open() )
    {
      if ( table.query() )
      {
        while(table.getNext())
        {
          wxMessageBox(table.colDefs[1].data);
        }
      }
      else
      {
        wxMessageBox('query failed');
      }
    }

getPrev

getPrev() : Boolean

Retrieves the PREVIOUS 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 query, queryOnKeyFields, queryMatching or queryBySqlStmt. 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.

insert

insert() : Boolean

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.

Remark: Don't forget to set the insertAllowed property of each column.

An example:


    // We're about to insert a row
    // so make sure the qryOnly argument is false
    var table = new wxDbTable(db, "user", 1, "", false);

    var columns = table.colDefs;

    columns[0].colName = "name";
    columns[0].dbDataType = wxDb.DATA_TYPE_VARCHAR;
    columns[0].sizeData = 30;
    columns[0].insertAllowed = true;

    table.open();
    columns[0].data = "franky";
    if ( table.insert() )
      wxMessageBox("insert success!");
    else
      wxMessageBox("insert failed");

open

open(CheckPriv = false, 
     CheckTable = true) : Boolean

Name	Type	Default	Description
CheckPriv	Boolean	false	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.
CheckTable	Boolean	true	Indicates whether the open function should check whether the table exists in the database or not before opening it.

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.

Every wxDbTable object must be opened before it can be used.

query

query(ForUpdate = false, 
      Distinct = false) : Boolean

Name	Type	Default	Description
ForUpdate	Boolean	false	Gives you the option of locking records as they are retrieved.
Distinct	Boolean	false	Allows selection of only distinct values.

This method queries records from the datasource based on the three wxDbTable members: where, orderBy, and from.

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 getFirst (only if backward scrolling cursors are available) or getNext. Typically, no data from the result set is returned to the client driver until a request such as 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.

An example:


        // Incomplete code sample
       parts.where = "DESCRIPTION = 'FOOD'";
       parts.orderBy = "EXPIRATION_DATE";
       parts.from = "";
       // 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
       }

queryBySqlStmt

queryBySqlStmt(Stmt) : Boolean

Name	Type	Default	Description
Stmt	String		SQL SELECT statement

This is the most powerful form of the query methods available. This method allows a programmer to write his 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 which is specified in the wxDbTable constructor. 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 using colDefs, 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 canUpdByROWID 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 update function after using this type of query. To update tables other than the base table, use the 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 getFirst or getNext.

queryMatching

queryMatching(ForUpdate = false, 
              Distinct = false) : Boolean

Name	Type	Default	Description
ForUpdate	Boolean	false	Gives you the option of locking records as they are retrieved.
Distinct	Boolean	false	Allows selection of only distinct values.

queryMatching allows querying of records from the table associated with the wxDbTable object by matching "columns" to values.

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 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 getFirst or getNext.

WHERE and FROM clauses specified using where and fromClause are ignored by this function.

queryOnKeyFields

queryOnKeyFields(ForUpdate = false, 
                 Distinct = false) : Boolean

Name	Type	Default	Description
ForUpdate	Boolean	false	Gives you the option of locking records as they are retrieved.
Distinct	Boolean	false	Allows selection of only distinct values.

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.

refresh

refresh() : Boolean

This function re-reads the bound columns into the memory variables, setting them to the current values stored on the disk.

setColNull

setColNull(ColNo, 
           SetToNull = true) : Boolean

Name	Type	Default	Description
ColNo	Integer		This number (between 0 and (noCols-1)) is the index of the column in the colDefs property.
SetToNull	Boolean	true	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.

setColNull(ColName, 
           SetToNull = true) : Boolean

Name	Type	Default	Description
ColName	String		The name of the column.
SetToNull	Boolean	true	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.

Sets a member variable representing a column in the table associated with this wxDbTable object to be set to NULL.

update

update() : Boolean

update(Stmt) : Boolean

Name	Type	Default	Description
Stmt	String		A full SQL UPDATE statement.

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. Make sure that you set the keyField and updateable as needed.

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.

An example:


      // We're about to update some rows
      // so make sure the qryOnly argument is false
      var table = new wxDbTable(db, "user", 2, "", false);

      var col = table.colDefs[0];
      col.colName = "id";
      col.dbDataType = wxDb.DATA_TYPE_INTEGER;
      // update uses the key fields to know which
      // row to update
      col.keyField = true;

      col = table.colDefs[1];
      col.colName = "name";
      col.dbDataType = wxDb.DATA_TYPE_VARCHAR;
      col.sizeData = 30;
      // Mark this column as updateable
      col.updateable = true;

      table.open();
      table.where = "name = 'bronx'";
      if ( table.query(true) )
      {
        if( table.getNext() )
        {
           table.colDefs[1].data = "franky";
           if ( table.update() )
           {
             wxMessageBox('update success');
           }
           else
           {
            wxMessageBox('update failed');
           }
        }
      }
      else
       wxMessageBox("query failed");

updateWhere

updateWhere(Where) : Boolean

Name	Type	Default	Description
Where	String		A valid SQL WHERE clause.

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 property set to true will be updated with the information currently held in the memory variable.

wxJavaScript

wxDbTable

Constants

TypeOfDel

TypeOfSel

TypeOfUpd

TypeOfWhere

Constructor

wxDbTable

Properties

Methods

buildDeleteStmt

buildSelectStmt

buildUpdateStmt

buildWhereStmt

clearMemberVar

clearMemberVars

close

count

createIndex

createTable

deleteMatching

deleteRow

deleteWhere

dropIndex

dropTable

getFirst

getLast

getNext

getPrev

insert

open

query

queryBySqlStmt

queryMatching

queryOnKeyFields

refresh

setColNull

update

updateWhere

Table of Contents

Constructor

Properties

Methods