Short 'n Sweet - Introducing the IMDB

Short 'N Sweet

COM+ Special, Introducing the In Memory Database
By: Jeroen Ritmeijer - www.xs4all.nl/~jarit/sns
(05/24/1999)

Introduction

Although the full version of Windows 2000 is still many months away, it never hurts to look ahead and see what the future will bring us.

One of the most exciting new features in Win2K for developers is the introduction of COM+. COM+ is a combination of the current version of COM as we all know and love it with some added extras, MTS including a load balancer, the In Memory DataBase and Queued Components using the MSMQ as an asynchronous transport for COM method invocations.

In this first article we will focus on setting up and accessing the IMDB from Active Server Pages.

The Database

Initially I planned to base this demonstration on a simple MS-Access database, however I did not get the IMDB to work with an Access .mdb file. So I had to resort to the Northwind database that is installed by default with MS-SQL Server 7.

Because of a bug in the IMDB we have to make some adjustments to the orders table, which I intend to use for this demonstration.

The current version of the IMDB locks up when more than 2 records contain a value for an indexed field. It took forever to figure this one out.

Run the following SQL command to remove all values from indexed fields in the northwind.orders table.

UPDATE orders SET shippeddate = '5/6/1999' WHERE shippeddate IS NULL

Don't forget to make a backup of the northwind database before making any changes.

Configuring the IMDB

Configuring the IMDB is easy, you just need to know a few tricks to get things running.

Security settings
Before you can access the IMDB from ASP you have to add the IWAM_machinename account to the 'IMDB Trusted User' role in the 'System Application' package.

Open 'Component services' from the 'Administrative tools' in the Start menu.
Navigate to 'My Computer \ Com+ Applications \ System Application \ Roles \ IMDB Trusted User'
Right-click Users and choose 'New user' to add the IWAM_Machinename user.
As you can see I have also added the Everyone group for testing purposes.

General settings
As the name implies the IMDB runs completely in memory. In order to fit an entire table into RAM we need to reserve memory for the IMDB:

Open 'Component services'

Right-click 'My Computer' and select properties

In the 'Options' tab you can enable the IMDB. If you select 'Load tables dynamically' the tables are not loaded into memory until the first access occurs. In general this is not a good idea since the first user who queries the IMDB will have to wait a long time depending on the size of the table. In this tab you can also specify the amount of RAM the IMDB is allowed to use. Do not make the size to small or the IMDB will fail.

Adding a Data source to the IMDB

Just like in traditional databases you need to create a container that holds all the tables you want to access.

In the 'Component Services' MMC right-click 'IMDB Data Sources' and select 'New / Data source'
Fill in the following dialog box to create a data source:
- Name: The new name for the IMDB data source
- OLEDB Provider: sqloledb.1 (for SQLServer)
- Provider data source: Name of the computer the SQLServer is hosted on
- IMDB catalog name: Name of the database you want to access
- Provider properties: additional OLEDB connection string properties. In this case we fill in the user name and password

Adding tables to a data source
Now that we have created an IMDB data source it's time to add an SQLServer table to the IMDB.

Expand the newly created 'imdb_northwind' database
Right-click 'Tables' and choose 'New / Table'
A dialog appears, fill in 'orders'

The SQLServer orders table is now available in the IMDB.

To make sure the IMDB is up to date restart the IMDB service. This can be done by right clicking on 'My Computer' in the 'Component Services' MMC or by stopping the IMDB service by hand (net stop imdbserver).

Accessing the IMDB from ASP

Just like with other databases you use ADO to access the IMDB. One of the few differences is that the first release of the IMDB does not support SQL. This is not a huge problem, as the following piece of code will show.

<%
  set Conn = server.createobject("adodb.connection")
  set rs = server.createobject("adodb.recordset")
  Conn.Open "Provider=MSIMDB;Data Source=imdb_northwind"
  rs.cachesize = 100
  rs.open "orders", Conn
  while not rs.eof
      response.write rs("orderid") & " - " & rs("ShipName") & "<BR>"
      rs.movenext
  wend
  rs.close
  Conn.close
  set rs = nothing
  set Conn = nothing
%>

As you can see the only difference is in the 'rs.open' command. Normally we would write something like 'select * from orders' but since the IMDB does not support SQL the only thing we have to do is specify the name of the table.

The example above displays 830 records. What if we only want to display a subset of the entire table? Since we can't use SQL to narrow down the results we have to find another way.

Fortunately ADO exposes quite a number of methods and properties to filter, seek or order data.

The following example will display all records with an orderid between 10340 and 10350 and sort the results descending by ShipName.

<%
  set Conn = server.createobject("adodb.connection")
  set rs = server.createobject("adodb.recordset")
  Conn.Open "Provider=MSIMDB;Data Source=imdb_northwind"
  rs.cursorlocation = 3 '** adUseClient for .filter to work
  rs.cachesize = 100
  rs.open "orders", Conn
  rs.filter = "orderid > 10340 and orderid < 10350"
  rs.sort = "shipname DESC"
  while not rs.eof
      response.write rs("orderid") & "-" & rs("ShipName") & "<BR>"
      rs.movenext
  wend
  rs.close
  Conn.close
  set rs = nothing
  set Conn = nothing
%>

As you can see the filter property is similar to the standard SQL 'Where' clause, without the 'Where' keyword. More info on the filter property can be found in the MSDN library ( http://msdn.microsoft.com/library/sdkdoc/dasdk/mdap0i7m.htm)

In the example above I have also added the sort property. It's very important to set this property AFTER applying the filter. If you don't do this all records will be sorted, this is a waste of time since you are only interested in the filtered records.

That's it. Next issue we will discuss writing data to the IMDB and run some benchmarks.

Open issues

There are still some issues with the IMDB and other COM+ service that I have not yet figured out since there is not a lot of documentation out there. Please contact me if you have information on one of the following:

Accessing the IMDB in a VB program: When I try to access the IMDB in a Visual Basic 6 program I get the following error: 'The specified activation could not occur in the client context as specified.'

Creating temporary tables at runtime: According to Microsoft it should be possible to create temporary IMDB tables at runtime. They forgot to mention how.

Using views to populate the IMDB: Sometimes you need only a subset of a table in the IMDB. Adding a view to the IMDB data source seems to work fine, however I get an error when I try to open it 'Failed to connect to the backend provider'.

Queued components: When I try to enable queuing on a COM+ package I get the message 'An error occurred accessing components in the System application on this Computer'.

Hyperlinks
COM+: http://www.microsoft.com/com/tech/complus.asp
ADO: http://msdn.microsoft.com/isapi/msdnlib.idc?theURL=/library/sdkdoc/dasdk/mdwe7i0f.htm

About the author
Jeroen Ritmeijer is a Senior Software Engineer for Codim electronic media, one of the larger multimedia companies in The Netherlands. He can be reached at jarit@xs4all.nl.