...and his 23 piece band.
by Ross Mack - GUI Computing
VB5 represents a major step forward in a number of Data Access technologies. There has been a host of improvements in virtually all facets of Data Access. Some of the most exciting improvements have been in DAO, VB's Data Access Objects. In particular the new ODBCDirect WorkSpaces introduced for working with ODBC data sources. However, before we look at that let's have a look at some of the lower level changes that are instrumental in implementing this new approach.
DAO's objects have been split up to allow more efficient loading of the objects actually being used. Rarely used objects, like security objects, are not loaded by default. Whereas, commonly used objects like fields, RecordSets, and WorkSpaces are loaded with the DBEngine objects. This reduces the load time of the DAO libraries as well as the memory used.
Remote Data Objects (RDO) has been significantly revised. RDO objects can now raise events to indicate when certain data access actions have occurred. For example, an event is raised to indicate that an asynchronous action has been completed. RDO has also been revised to allow far more asynchronous operations than before, this includes establishing connections and opening RecordSets.
Another change to RDO allows objects to be 'free-floating'. This means RecordSet and QueryDef objects can exist independently of a connection and can be moved from one connection to another.
Furthermore, the connection between DAO and JET has been loosened so that DAO can be loaded without loading JET. This allows DAO to use RDO as an alternate database engine when connecting to ODBC data sources. This is actually very cool, it means that DAO has become a more generalised database manipulation language. It also means that DAO can leverage the new RDO features when using RDO as it's database engine. This includes it's control of cursor location (client- or server-side), enhanced control of stored procedures, multiple result sets and so on.
ODBCDirect Overview & WorkSpaces.
So how does ODBCDirect really work? ODBCDirect really defines a new sort of DAO WorkSpace. Remember the WorkSpace objects in VB4 (And the SetDefaultWorkspace statement in VB3) that you only ever seemed to use to support the OpenDatabase call? Well, they are actually an object with some functionality. For example, they are the controlling objects for any transactions (BeginTrans, CommitTrans) you may be using. They were also the place you specified username and password information for your database connections to JET databases. Now, in VB5, you can create two different types of WorkSpaces. The first is the traditional JET WorkSpace from previous versions of DAO, the second is the new ODBCDirect WorkSpace.
These new WorkSpaces allow DAO to control RDO which is itself a very thin layer above ODBC. The ODBC driver manager and drivers (which must be level 2 compliant or level 1 with a number of level 2 functions) do the real work. This means the whole client architecture is very thin and quite efficient. Also, because VB5 ships with ODBC 3.0 which is a very efficient 32bit implementation the data access speeds can be really impressive.
Now, back to Workspaces.
By default, the DBEngine object will create JET style WorkSpaces as they are requested. However, this can be overridden. The following code creates the normal default JET WorkSpace and opens a database:
Dim ws as WorkSpace Dim db as Database Set ws = DBEngine.WorkSpaces(0) Set db = ws.OpenDatabase("biblio.mdb")
As always the DBEngine object is loaded implicitly whenever a DAO object is referenced in code. It also creates an initial WorkSpace object
(DBEngine.WorkSpaces(0))as soon as it is first referenced. Because in the above code we did not ask for any particular sort of WorkSpace we got the default which is a JET style WorkSpace.
We can change the default WorkSpace type created by the DBEngine using the DefaultType property. Or we can specifically request a WorkSpace type when calling the CreateWorkSpace method of the DBEngine object. Two constants are used for this: dbUseODBC to create RDO type WorkSpaces and dbUseJet to create traditional JET WorkSpaces. Consider the following code.
DBEngine.DefaultType = dbUseODBC
DBEngine.DefaultType = dbUseJet
Or, in the CreateWorkSpace call
Set wkspc = DBEngine.CreateWorkSpace("MyWspace","sa","password",dbUseODBC)
It's probably also worth noting at this point that any reference to the DBEngine object in VB5 can be done implicitly. This means that the above three lines of code could have been written as follows.
DefaultType = dbUseODBC DefaultType = dbUseJet Set wkspc = CreateWorkSpace("MyWspace","sa","password",dbUseODBC)
Another very cool side effect of this behaviour is that until you create a JET WorkSpace JET will not be loaded into memory. In fact it need not be installed on the machine at all as it is not used. This gives you a saving in memory (more than 1MB) when you are using ODBCDirect WorkSpaces exclusively. However, you still get to use virtually the same DAO code that you are used to for manipulating your database objects. There are some differences to reflect the different abilities of JET and RDO, but everything will look quite familiar to the seasoned DAO/JET programmer.
You are free to use both types of WorkSpaces is the same application.
Opening Data Sources.
Once you have an ODBCDirect WorkSpace how do you get to the data? Well, that's actually quite easy and quite familiar. There are two ways to do this, you can use a Database object or you can use a Connection object.
The ODBCDirect Database Object.
Database objects are the same objects you would use in a JET WorkSpace to open a JET database (or to use JET to connect to an ISAM or an ODBC back end). They are provided here largely for ease of porting code and so that existing libraries expecting Database objects will still work. Apart from that there is no real reason to use a Database object as it restricts you to the functionality common to both ODBCDirect data sources and JET databases. Don't panic, they work fine but they don't expose all the functionality available to Connection objects.
The code would look like this:
Dim wkspc As WorkSpace Dim db As Database DefaultType = dbUseODBC ' Set ODBCDirect as the default Set wkspc = WorkSpaces(0) Set db = wkspc.OpenDatabase("", false, false, "ODBC;DSN=MyDSN;DATABASE=pubs;")
The ODBCDirect Connection Object.
Using a Connection object is the preferred method of opening a connection to an ODBC data source using ODBCDirect. It allows you to use all the extended functionality of ODBCDirect. Some of these enhanced abilities are;
These connection objects are largely the Connection objects created internally for ODBCDirect by RDO, so RDO programmers will also find them to be quite familiar.
The code would look like this:
Dim wkspc As WorkSpace Dim cn As Connection DefaultType = dbUseODBC ' Set ODBCDirect as the default Set wkspc = WorkSpaces(0) Set cn = wkspc.OpenConnection("MyConn", dbDriverNoPrompt, False, "ODBC;DSN=MyDSN;")The Best of Both worlds.
You also have the option of using either a Connection object or a Database object and getting to the other one later as required. How is this done? Well, the Connection object has a property called Database, which exposes the equivalent Database object. In addition, the Database object (in ODBCDirect WorkSpaces) has a Connection property, which exposes the equivalent Connection object. Consider the following code;
Dim wkspc As WorkSpace Dim db As Database Dim cn As Connection Dim rs As Recordset DefaultType = dbUseODBC ' Set ODBCDirect as the default Set wkspc = WorkSpaces(0) Set cn = wkspc.OpenConnection("MyConn", dbDriverNoPrompt, False, "ODBC;DSN=MyDSN;") Set db = cn.Database Set rs = db.OpenRecordset("tablename")
One of the coolest features of ODBCDirect is the ability to execute various operations asynchronously. This means that ODBCDirect passes the request for whatever to happen back to the database server and allows your code to continue processing. When the database server has finished it informs RDO which, in turn, informs ODBCDirect. While this is going on your application can continue to execute code like loading and painting forms (which is ridiculously quick in VB5 anyway), responding to user events and whatever other processing you might want.
When using asynchronous processing there is one method and one property that are important. The property is called StillExecuting and is exposed by the Connection, QueryDef, and RecordSet objects. When True this property indicates that asynchronous processing is still occurring and that any resultant objects are still invalid. This means that if you are asynchronously opening a Connection that connection object is invalid while it's StillExecuting property is True. The same is true of RecordSet objects.
The method for working with asynchronous processing is called Cancel and is again exposed by Connection, QueryDef and RecordSet objects. This method allows you to cancel asynchronous processing. For example, you could give the user a Stop button that they can use to cancel queries that are taking to long. In this scenario you would simply invoke the Cancel method of the QueryDef or RecordSet in question when the user clicks the button. The code might look like this:
Sub cmdStopThat_Click () ' Ensure that something is going on. If cn.StillExecuting then ' Cancel it via the Connection object cn.Cancel End If End Sub
I will briefly run through the different things you can do asynchronously using ODBCDirect providing some sample code as to how they work.
One of the operations that always takes time when using remote ODBC data sources is establishing the initial connection so that you can begin working with the data. ODBC 3.0 relieves this problem somewhat by caching connections locally. However, the first connection always takes time. Fortunately, ODBCDirect allows you to establish your connection asynchronously so that your code can continue executing while the connection is established. This means your application can start by requesting it's ODBCDirect connections then continue to load it's initial forms, and perform whatever other initialisation is required and then check to see if the connections are ready yet. This makes your application seem faster and more responsive to the user as it starts up.
How is this done? Using the options parameter of the OpenConnection method you add the dbRunAsync constant to tell the WorkSpace you want the connection to be established asynchronously. Remember from above how we open a connection?
SConn = "ODBC;DSN=Pubs;UID=sa;PWD=;" Set cn = wkspc.OpenConnection("MyConn", dbDriverNoPrompt, False, sConn
To have this code run asynchronously we simply add the dbRunAsync constant to the options parameter like this:
SConn = "ODBC;DSN=Pubs;UID=sa;PWD=;" Set cn = wkspc.OpenConnection("MyConn", dbDriverNoPrompt + dbRunAsync, False, sConn)
This tells the WorkSpace to open a connection called "MyConn" without prompting for any information from the user and processing the connection asynchronously. It also specifies that the connection should not be read only and that the last parameter is the connection string. For full details on the OpenConnection method see the VB5 Books Online.
Because asynchronous processing is largely centralised to the Connection object itself, you can only execute one asynchronous query at a time, per connection. If you really need to execute more than one query asynchronously at the same time, you have the option of opening additional Connection objects.
Here is some sample code which simply counts until the connection is established, just to give you an idea of how this all works.
Sub DoOpen () Dim iCount As Integer Dim ws As WorkSpace Dim cn As Connection Dim sConn As String DefaultType = dbUseODBC Set ws = WorkSpaces(0) SConn = "ODBC;DSN=Pubs;UID=sa;PWD=;" Set cn = ws.OpenConnection("", dbRunAsync + dbDriverNoPrompt, False, sConn) Do iCount = iCount + 1 DoEvents Loop While cn.StillExecuting MsgBox "I counted to " & CStr(iCount), vbInformation + vbOKOnly, "Connected" cn.Close End Sub
Asynchronous processing is also available for query execution of all sorts. This includes both the Execute method and the OpenRecordset methods of the new QueryDef object, which is used to generate executable queries in ODBCDirect WorkSpaces. It is important to note at this point that QueryDef objects in ODBCDirect WorkSpaces are unlike QueryDef objects in JET WorkSpaces in that they are never persistent. When you create a QueryDef object while using DAO to connect to an Access database this, by default, creates a persistent QueryDef object which is written to the database. ODBCDirect QueryDefs are never persistent.
Let's have a look at some code that uses a QueryDef to open a RecordSet:
Dim qry As QueryDef Dim rs As RecordSet Dim sSQL As String Dim iCount As Integer sSQL = "SELECT * FROM authors WHERE au_fname = 'Bert'" Set qry = cn.CreateQueryDef("Test",sSQL) Set rs = qry.OpenRecordset(dbOpenSnapshot, dbRunAsync) Do iCount = iCount + 1 DoEvents Loop While qry.StillExecuting Debug.Print "Counted to " & CStr(iCount) & " while fetching" Do While Not rs.EOF Debug.Print rs!au_lname & ", " & rs!au_fname rs.MoveNext Loop rs.Close qry.Close
Similarly, we could execute an action query asynchronously with the following code:
Dim qry As QueryDef Dim sSQL As String Dim iCount As Integer sSQL = "DELETE * FROM authors WHERE au_fname = 'Bert'" Set qry = cn.CreateQueryDef("Test",sSQL) qry.Execute, dbRunAsync ' Execute asynchronously Do iCount = iCount + 1 DoEvents Loop While qry.StillExecuting Debug.Print "Counted to " & CStr(iCount) & " while deleting" qry.Close
Simple executable queries like this can also be performed directly using the Connection object:
cn.Execute "DELETE FROM mytable", dbRunAsync
By default, QueryDefs are executed on the back end as Prepared Statements. Essentially, this means that they are compiled as temporary stored procedures before they are executed. This can be overridden by setting the Prepare property of the QueryDef like so:
Set qry = cn.CreateQueryDef("Test",sSQL) qry.Prepare = dbQUnprepare qry.Execute, dbRunAsync ' Execute asynchronously
Using the Connection's or the QueryDef's Cancel method action queries and select queries can both be cancelled. However, you should ensure that your action queries are wrapped in Transactions so that changes made until they are cancelled can be rolled back. Essentially what this means is that when an action query is cancelled everything it has done up until that point stays done unless you roll it back using a transaction. Furthermore, if your query is wrapped in a transaction and you call CommitTrans while it is still executing it will immediately switch to running synchronously at the CommitTrans line until it has finished.
Some Asynchronous RecordSet Stuff.
As you saw above a RecordSet can be opened asynchronously using a QueryDef. RecordSets can also be opened asynchronously using the options parameter of the OpenRecordset method. The code would look like this:
Set rs = cn.OpenRecordset(sSQL, dbOpenSnapshot, dbForwardOnly+dbRunAsync, dbReadOnly)
The first argument is the SQL statement, the second is the type of RecordSet to open. The third argument is where we can provide a number of parameters concerning the type of RecordSet opened and how it is opened, including specifying asynchronous processing and whether the SQL is executed as a prepared statement. The last argument indicates what sort of locking is required.
Another interesting asynchronous operation allowed by the RecordSet is the MoveLast method. This method now accepts a parameter to indicate whether to execute asynchronously. Of course the effectiveness of this depends on where your cursors are created (on the server or on the client) but it is still very useful. Moreover, it's simple.
There are many great new features in VB5. It is a very slick, solid version with enhancements in virtually every area. ODBCDirect represents one of the areas where it has leapt ahead of previous versions. For the many of us who are working with ODBC back ends it represents an excellent alternative to the connection methods we are using now while combining the strengths of many of them. The symbiosis between DAO and RDO makes porting applications easy and the result is more efficient, leaner, code that works better. And that's the sort of improvements we always need in development tools.
If you are doing ODBC in VB5 (or any version of VB) and haven't tried ODBCDirect, I strongly recommend you do so.