Click here for information on the updates to this enhancement.
ADO (ActiveX Data Objects) is a Microsoft technology which allows data to be published over the web (either a private Intranet, or the Internet). It uses another technology, OLE DB, to provide data from any compliant ODBC data source. We have made changes to three DP4 system libraries: ODBCLIBT.DLL; SQLLIBT.DLL; and SYSLIBT.DLL, which enable ADO to work with DP4 databases. The changes may also be useful in other situations where OLE DB is being used, though this has not been investigated.
In a way, at least some of these changes are bug fixes. However the effect of the changes is a major improvement to the functionality of DP4, and there may be compatibility issues for existing applications using DP4 via ODBC. The changes made are as follows:
When ADO queries a database, it makes a great many queries about the capabilities of the database, and tries to set various database behaviours. Some of these behaviours are not relevant to DP4. The previous version of ODBCLIBT.DLL returned an error code when these behaviours were requested, which prevented ADO from acccessing the database. In a number of instances this was not the correct behaviour. In this type of situation ODBC drivers are supposed to return a special code which indicates that the requested behaviour was not available and a different behaviour was selected instead. After making this change ADO can talk to DP4 databases. This change should enable any OLE DB data consumer to use DP4.
When reporting the column label for single occurrences of multi occurs fields SQLLIBT.DLL did not previously include the occurs number in the label, though it has to be specified in order to select the appropriate occurrence. This prevented multi-occurs fields from being accessed by name via ADO.
When ADO uses SQL to query a database, it also asks ODBC to report the name of every column in the query. If the code using ADO refers to fields in the record-set by name, ADO uses these names, and not the names specified in the query. This causes some problems with DP4, because our SQL interpreter is fairly flexible - it will ignore underscores in names, and allows various methods of specifying roles and occurs numbers. To access columns by name the ADO code must specify the names exactly as our SQL library reports them.
| DP4 Field Name | Occurs | Name report by SQL | Comments |
|---|---|---|---|
| field | single | field | |
| field.role | single | role$field | There are configurable settings which allow ODBCLIBT.DLL to mangle this name in various ways. This may be useful when using Oracle products. |
| field | multi | field[1],field[2] etc. | If ODBCLIBT.DLL recognizes queries as coming from Microsoft Access it replaces square brackets with curly braces. This behaviour can be configured to happen by default. |
| field.role | multi | role$field[1], role$field[2] etc. |
SYSLIBT.DLL is the library responsible for communication between applications and the DP4 database manager. A change was necessary to enable this library to work when ODBCLIBT.DLL is loaded by the Microsoft Internet Information Server on Windows NT. Previously there was a problem with security which prevented this from working. This change is not necessary to publish DP4 data using Personal Web Server on a Windows 95 or 98 machine.
Important! We have experienced some problems with running DP4+ADO on some configurations of Windows NT server. You are strongly recommended to install at least version 2.5 of MDAC (Microsoft Data Access Components) before trying to access DP4 via ADO. MDAC is included on MSDN subscriptions CDs, and is available as a free Microsoft download. We have not yet tested with the new MDAC2.6 release.
To access DP4 databases via ADO on Windows NT/Windows 2000. DP4 must be started as a service, not an application. i.e. you must start DP4 with srvw32 -start and not srvw32 -load.
On both IIS and Personal Web Server, there is a minor problem caused by an unpleasant behaviour of these products: after accessing data from an ODBC data source, the ODBC drivers used remain loaded in the server's address space, even once the database is closed. No doubt Microsoft claim this is to improve performance, though it is just as likely that they forgot to free the resources (funny how everyone else is supposed to free up everything), and it certainly helps explain how memory seems to vanish when using IE5 on Windows 98. This causes severe problems for DP4 if the DP4 service is stopped. The hanging libraries loaded by the web server prevent a clean shutdown from taking place, and it will not be possible to reload DP4 afterwards.
On Windows 9x the cure is to get hold of a copy of the Process Viewer program supplied with Visual C++. This shows up a process called INETINFO. This can be terminated with Process Viewer, seemingly without any ill-effects. (This process does not show up in Task Manager). It is necessary to restart Personal Web Server after doing this. Please note that stopping and starting web publishing from the SysTray web server icon has no effect - the process is not terminated by doing this.
On Windows NT the situation seems more problematical: somewhere in the IIS property pages there is a checkbox which allows you to disable caching of ISAPI applications. (Look for a Configuration... button on the Home Directory Tab. This seems to have some effect. However ODBCLIBT.DLL does not seem to be freed up completely reliably. Stopping the IIS Asmin service will do the trick, but it does not seem very easy to restart this service sucessfully again without a reboot.
Accessing DP4 databases via ADO should be done strictly on an experimental database for the time being. We have only tested a very few simple operations as yet, and it is quite likely further changes will be necessary either to ODBCLIBT.DLL or SQLLIBT.DLL.
The SQLLIBT.DLL included with 14 November version was missing an update it required to work with the ODBCLIBT.DLL included with the download. This caused the ODBC Catalog functions to fail.
SQLLIBT.DLL now supports the use of AS to allow the column name to be set to a "username" within the SQL query (e.g. select count(*) as custcount from customer). This is useful for two reasons:
Please note that "usernames" for columns cannot be used anywhere else in your SQL statement. In particular they cannot be used in WHERE clauses. You must use the regular DP4 column name.
We have made some more changes to these files in order to support the latest version of Crystal Reports and also a product called "Web Objects". The changes for Crystal Reports are not vital. See A4000011 for more information. The changes for "Web Objects" are not actually Web Related, and could impact other ODBC clients for good or ill in the handling of dates and times. Web Objects appears not to understand SQL Time fields, and also takes a somewhat extreme approach in the WHERE clause it uses for updating records - specifying a value for all the fields in the table, and not just the keys. This caused it to fail when updating tables containing a time field. The happened it asked our SQL interface to compare a time field with a timestamp field in the WHERE clause. Previously our library would only do this if the date part of the timestamp field was null. However, Web Objects passes the date Jan 01 1970 (The start of the universe according to Unix). We have changed our SQL library to allow comparisons of timestamps with either dates or times. Only the date part or the time part are considered in the comparison. (This is arguably wrong for dates, where an alternative interpretation would be to treat the date as a timestamp with a time of 0:00:00. However this interpretation is more likely to be helpful, and we are not aware of a standard in this area).
SQLLIBT.DLL has been updated in the download to fix bugs introduced by fix K4000007. See the updated information on that bug for full details.
You must install ALL these components together. The new ODBCLIBT.DLL is not compatible with earlier releases of SQLLIBT.DLL and will fail to load.
See also E4000017 for information about other performance optimisations in this version.
Beta software is supplied without warranty and should not be used in live sites without the agreement of Itim Technology Solutions. Compatibility with an eventual final release cannot be guaranteed.
The download also includes the source of a simple 4.5xx QAB program which can be posted to any database, and which can be used to generate a simple ASP page for any table on the database. Please note that this program does not currently work in 4.6xx as it uses a format flag that cannot be stored in 4.6xx maps.