Image of Navigational Map linked to Home / Contents / Search ToolBook ODBC

Stephan Grieger - GUI Computing
Image of Line Break

Until version now, ToolBook has not been taken seriously as a development platform for applications which needed to store, retrieve, and report on large quantities of data. The reason, in part, is due to the fact that all data was stored internally to the executable that you created.

ToolBook's data storage and retrieval was based on a page system. That is to say, that as you entered data into pages in ToolBook, these pages would be stored as part of the executable. In this way, as users manipulated data, the executable's of your systems would vary in size according to what was happening to the data.

Finally, Asymetrix have released their Database Connection DLL's which now enable ToolBook applications to target not only dBase and Paradox files, but also the increasingly popular Access format (amongst others). Basically, if the back end you are targeting is ODBC compliant, then ToolBook will be able to use it.

In this article I will be solely concentrating on using the Database Connection utility with Microsoft Access. There has been limited time in my current project to explore any further, though I am assured by Asymetrix that working with the other file formats works fine.

If you are considering taking one of your current ToolBook applications - or are about to develop a new ToolBook application - and you intend to target an Access database, then my advise at this point is to think again and perhaps use Paradox or even dBase as your back end.

Now while this may sound a little harsh, let me qualify why I have said this.

The Database Connection utility comes with a full API for manipulating data. The problem is that when you try to write or delete records (a minor thing - I know), the Database Connection API's get themselves and your data confused. For example, if you were writing ten records out one after another, all ten records will contain the same data regardless of what you are actually writing out. It seems to be that the first record is written out correctly, but all subsequent records are the same as the first, even though the values you are passing across are different.

The only way we found to avoid the problem was to use SQL statements for all writes and deletes.

For example, if you were going to write out ten records, you would need to execute ten SQL statements. The code below outlines a typical write routine.

First thing is to open the database. The ODBC connection must be there via ODBC setup.

  To Get OpenDatabase

  ' Dim a few variables.
  Local hCon

  ' Connect to a valid ODBC connection and return a handle 
  ' to that connection.
  hCon = dbCreateConnection("","DSN=Your ODBC Database Connection")

  '  Check status of the connection to see if it was opened 
  '  successfully.
  if dbOK() then
    ' Set Autoupdate off.
    get dbSetConnectionProperty(hCon,"autoUpdate","update")
    ' Return the connection handle.
    return hCon
  else
    ' Return the error generated.
    return dbError()
  end if
	
  End

Now apply the update SQL which will locate the desired record and modify its contents.

  To Handle SaveData hCon

  ' Dim a few variables.
  Local UpdateSQL
  System UniqueKey

  ' Create the Update Query SQL.
  Set UpdateSQL To "UPDATE DISTINCTROW TableName SET TableName.Field1 = 
   '" & Text Of Field "fldField1" of page id 0 & "', TableName.Field2 = 
   '" & Text Of Field "fldField2" of page id 0 & "' WHERE 
   ((TableName.FieldID=" & UniqueKey & "));"

  ' Execute the Update Query.
  Get dbExecuteSQL(hCon, UpdateSQL)

  End

If you were going to add a new record, you would need to apply an Add Query.

This methodology works well but does come with its own set of unique problems.

First there is the inconvenience of having to develop SQL statements for all your updates and deletes. This process, however, can be simplified if you use Microsoft Access to aid you in the development of these SQL statements. Basically, you can use Access to create the SQL Statement which you can then copy straight into ToolBook and it will work. (Remember to change all hard coded values to variables etc.)

The other problem is that this process can take some time to execute, seeing as each SQL statement takes a given amount of time. If you have large amounts of data that needs to be saved, such as a grid, then expect this process to take several seconds. If you are saving data from multiple pages, then this time lag will increase fairly significantly.

Warning


The documentation which comes shipped with the Database Connection is fairly simple. It does not cover nearly enough of what we would consider to be adequate. With that in mind, it is a good idea to have both the book, the help file, and Asymetrix's tech support web page open at the same time.

Technical support for the product from the States, when using the Access database, is almost non-existent and requires some patience.

Hint


If you are using the Database Connection in version 4.0 of MMTB, then you will need to go through all the system books and modify all references to version 3.0 DLL's to read version 4.0 DLL's.

All in all though, this being the first release of the product, it does seem to work and does hold a lot of promise for the future.



Written by: Stephan Grieger
August '96


Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]