Introduction
The DB module ports the wxODBC classes from wxwidgets to JavaScript.
Before you are able to access a datasource, you must have installed and configured an ODBC driver. Doing this is system specific, so it will not be covered in detail here.
wxODBC - Basic Step-By-Step Guide
This guide is taken from the wxWidgets website and ported to how you have to use the wxODBC
classes in JavaScript.
To use the classes in an application, there are five basic steps:
- Define datasource connection information
- Get a datasource connection
- Create table definition
- Open the table
- Use the table
Following each of these steps is detailed to explain the step, and to hopefully mention as many of the pitfalls that beginning users fall in to when first starting to use the classes. Throughout the steps, small snippets of code are provided to show the syntax of performing the step.
Define datasource connection information
To be able to connect to a datasource through the ODBC driver, a program must supply a minimum of three pieces of information: Datasource name, User ID, and Authorization string (password). A fourth piece of information, a default directory indicating where the data file is stored, is required for Text and dBase drivers for ODBC.
The wxWidgets data class wxDbConnectInf exists for holding all of these values, plus some others that may be desired.
The dsn must exactly match the datasource name used to configure the ODBC datasource (in the ODBC Administrator (MSW only) or in the .odbc.ini file).
The uid is the User ID that is to be used to log in to the datasource. This User ID must already have been created and assigned rights within the datasource to which you are connecting. The user that the connection is establish by will determine what rights and privileges the datasource connection will allow the program to have when using the connection that this connection information was used to establish. Some datasources are case sensitive for User IDs, and though the wxODBC classes attempt to hide this from you by manipulating whatever data you pass in to match the datasource's needs, it is always best to pass the uid in the case that the datasource requires.
The authStr is the password for the User ID specified in the uid member. As with the uid , some datasources are case sensitive (in fact most are). The wxODBC classes do NOT try to manage the case of the authStr at all. It is passed verbatim to the datasource, so you must use the case that the datasource is expecting.
The defaultDir member is used with file based datasources (i.e. dBase, FoxPro, text files). It contains a full path to the location where the data table or file is located. When setting this value, use forward slashes '/' rather than backslashes ' avoid compatibility differences between ODBC drivers.
Get a Datasource Connection
You must first create a wxDbConnectInf object. In the wxDbConnectInf instance, provide a valid Dns, Uid, and AuthStr (along with a 'defaultDir' if necessary).
var connectionInfo = new wxDbConnectInf("MyDsn");
connectionInfo.uid = "MyUserName";
connectionInfo.authStr = "MyPassword";A shorter form of doing the above steps is encapsulated into the constructor for wxDbConnectInf.
var connectionInfo = new wxDbConnectInf("MyDsn", "MyUserName", "MyPassword");
Once the wxDbConnectInf instance is initialized, you are ready to connect to the datasource.
To manually create datasource connections, you must create a wxDb instance, and then open it.
var db = new wxDb(connectionInf);
var opened = db.open(connectionInf);
The first line does the house keeping needed to initialize all the members of the wxDb class. The second line actually sends the request to the ODBC driver to open a connection to its associated datasource using the parameters supplied in the call to open.
A time-saver is the "copy connection" features of open. If manually creating a wxDb instance and opening it, you must pass an existing connection to the open function yourself to gain the performance benefit of copying existing connection settings.
Create Table Definition
To make life much simpler, the wxDbTable class encapsulates all of the SQL specific API calls to access data.
The first step in accessing data in a datasource's tables via the wxDbTable class is to create a wxDbTable instance.
var table = new wxDbTable(db, tableName, numTableColumns, "", false, "");
When you create the instance, you indicate the previously established datasource connection to be used to access the table, the name of the primary table that is to be accessed with the datasource's tables, how many columns of each row are going to be returned, the name of the view of the table that will actually be used to query against (works with Oracle only at this time), whether the data returned is for query purposes only, and finally the path to the table, if different than the path specified when connecting to the datasource.
Each of the above parameters are described in detail in the wxDbTable class' description, but one special note here about the fifth parameter - the queryOnly setting. If a wxDbTable instance is created as query only, then no inserts/deletes/updates can be performed using this instance of the wxDbTable. Any calls to commitTrans or rollbackTrans against the datasource connection used by this wxDbTable instance are ignored by this instance. If the wxDbTable instance is created with not query only as shown above, then all the cursors and other overhead associated with being able to insert/update/delete data in the table are created, and thereby those operations can then be performed against the associated table with this wxDbTable instance.
If a table is to be accessed via a wxDbTable instance, and the table will only be read from, not written to, there is a performance benefit (not as many cursors need to be maintained/updated, hence speeding up access times), as well as a resource savings due to fewer cursors being created for the wxDbTable instance. Also, with some datasources, the number of simultaneous cursors is limited.
When defining the columns to be retrievable by the wxDbTable instance, you can specify anywhere from one column up to all columns in the table.
var col = table.colDefs[0];
col.colName = "FIRST_NAME";
col.dbDataType = wxDb.DATA_TYPE_VARCHAR;
col.sizeData = 30;
col.keyField = true;
col.updateable = true;
var col = table.colDefs[1];
col.colName = "LAST_NAME";
col.dbDataType = wxDb.DATA_TYPE_VARCHAR;
col.sizeData = 30;
col.keyField = true;
col.updateable = true;Notice that column definitions start at index 0 and go up to one less than the number of columns specified when the wxDbTable instance was created (in this example, two columns - one with index 0, one with index 1).
The above lines of code "bind" the datasource columns to the data property of wxDbColDef . So when the application makes a call to getNext (or any other function that retrieves data from the result set), the data property will have the column value stored into it.
The data property has undefined data until a call to a function that retrieves data from a result set is made (e.g. getNext , getPrev , etc). The properties are not initialized to any data by the wxODBC classes, and they still contain undefined data after a call to query . Only after a successful call to one of the ::getXxxx() functions is made do the properties contain valid data.
It is not necessary to define column definitions for columns whose data is not going to be returned to the client. For example, if you want to query the datasource for all users with a first name of 'GEORGE', but you only want the list of last names associated with those rows (why return the FIRST_NAME column every time when you already know it is 'GEORGE'), you would only have needed to define one column above.
You may have as many wxDbTable instances accessing the same table using the same wxDb instance as you desire. There is no limit imposed by the classes on this. All datasources supported (so far) also have no limitations on this.
Open the table
Opening the table is not technically doing anything with the datasource itself. Calling open simply does all the housekeeping of checking that the specified table exists, that the current connected user has at least SELECT privileges for accessing the table, setting up the requisite cursors, binding columns and cursors, and constructing the default INSERT statement that is used when a new row is inserted into the table (non-QUERY_ONLY tables only).
if (! table.open())
{
// An error occurred opening (setting up) the table
}The only reason that a call to open is likely to fail is if the user has insufficient privileges to even SELECT the table. Other problems could occur, such as being unable to bind columns, but these other reason point to some lack of resource (like memory). Any errors generated internally in the open function are logged to the error log if SQL logging is turned on for the classes.
Use the table
To use the table and the definitions that are now set up, we must first define what data we want the datasource to collect in to a result set, tell it where to get the data from, and in which sequence we want the data returned.
// the WHERE clause limits/specifies which rows in the table
// are to be returned in the result set
table.where = "FIRST_NAME = 'GEORGE'";
// Result set will be sorted in ascending alphabetical
// order on the data in the 'LAST_NAME' column of each row
// If the same last name is in the table for two rows,
// sub-sort on the 'AGE' column
table.orderBy = "LAST_NAME, AGE";
// No other tables (joins) are used for this query
table.from = "";The above lines will be used to tell the datasource to return in the result all the rows in the table whose column "FIRST_NAME" contains the name 'GEORGE' (note the required use of the single quote around the string literal) and that the result set will return the rows sorted by ascending last names (ascending is the default, and can be overridden with the "DESC" keyword for datasources that support it - "LAST_NAME DESC"). Specifying a blank WHERE clause will result in the result set containing all rows in the datasource.
Specifying a blank ORDERBY clause means that the datasource will return the result set in whatever sequence it encounters rows which match the selection criteria. What this sequence is can be hard to determine. Typically it depends on the index that the datasource used to find the rows which match the WHERE criteria. BEWARE - relying on the datasource to return data in a certain sequence when you have not provided an ORDERBY clause will eventually cause a problem for your program. Databases can be tuned to be COST-based, SPEED-based, or some other basis for how it gets your result set. In short, if you need your result set returned in a specific sequence, ask for it that way by providing an ORDERBY clause.
Using an ORDERBY clause can be a performance hit, as the database must sort the items before making the result set available to the client. Creating efficient indexes that cause the data to be "found" in the correct ORDERBY sequence can be a big performance benefit. Also, in the large majority of cases, the database will be able to sort the records faster than your application can read all the records in (unsorted) and then sort them. Let the database do the work for you!
Notice in the example above, a column that is not included in the bound data columns ('AGE') will be used to sub-sort the result set.
The FROM clause in this example is blanked, as we are not going to be performing any table joins with this simple query. When the FROM clause is blank, it is assumed that all columns referenced are coming from the default table for the wxDbTable instance.
After the selection criteria have been specified, the program can now ask the datasource to perform the search and create a result set that can be retrieved:
// Instruct the datasource to perform a query based on the
// criteria specified above in the where/orderBy/from clauses.
if (!table.query())
{
// An error occurred performing the query
}Typically, when an error occurs when calling query , it is a syntax problem in the WHERE clause that was specified. The exact SQL (datasource-specific) reason for what caused the failure of query (and all other operations against the datasource can be found by parsing the table's database connection's errorList[] array property for the stored text of the error.
When the query returns true, the database was able to successfully complete the requested query using the provided criteria. This does not mean that there are any rows in the result set, it just mean that the query was successful.
IMPORTANT: The result created by the call to query can take one of two forms. It is either a snapshot of the data at the exact moment that the database determined the record matched the search criteria, or it is a pointer to the row that matched the selection criteria. Which form of behavior is datasource dependent. If it is a snapshot, the data may have changed since the result set was constructed, so beware if your datasource uses snapshots and call refresh . Most larger brand databases do not use snapshots, but it is important to mention so that your application can handle it properly if your datasource does.
To retrieve the data, one of the data fetching routines must be used to request a row from the result set, and to store the data from the result set into the bound data property. After query has completed successfully, the default/current cursor is placed so it is pointing just before the first record in the result set. If the result set is empty (no rows matched the criteria), then any calls to retrieve data from the result set will return false.
var col = table.colDefs[0];
while (table.getNext())
{
wxMessageBox(col.data);
}The sample code above will read the next record in the result set repeatedly until the end of the result set has been reached. The first time that getNext is called right after the successful call to query , it actually returns the first record in the result set.
When getNext is called and there are no rows remaining in the result set after the current cursor position, getNext (as well as all the other getXxxxx() functions) will return false.
© 2002 - 2007 Franky Braem.