How to use SQLCache

For the most common method of using SQLCache we can take the example of a VBScript ASP page connecting to MS SQL Server Database.

We begin in the normal way by getting the connection object.

Set oConn = Server.CreateObject("ADODB.Connection")

In normal ADO would open the connection using the following string, which includes a reference to a system data source name.

oConn.Open "DSN=myDSN;UID=sa;PWD=;DATABASE=mydatabase"

or we could use the provider directly:

oConn.Open "Provider=SQLOLEDB;Data Source=myserver;User Id=sa;Password=;Initial Catalog=mydatabse"

and then we would issue SQL commands that would return a recordset, like this:

Set oRs = oConn.Execute("SELECT * From authors")

Using SQLCache, all we need to do is change the connection string, which would become:

oConn.Open "Provider=SQLCache.Cache;Original Provider=MSDASQL;User Id=sa;Password=;Data Source=myDSN;Initial Catalog=mydatabase"

The above is using the OLE DB provider for ODBC, for backward compatibility. For connections to MS SQL 2000 server the native OLE DB provider could be used instead. Like this:

oConn.Open "Provider=SQLCache.Cache;Original Provider=SQLOLEDB;User Id=sa;Password=;Data Source=serverName;Initial Catalog=mydatabase"

And we can then use as normal, for example:

Set oRs = oConn.Execute("SELECT * From authors")