Streams and Persistence
The Recordset object Save method stores, or persists, a Recordset in a file, and the Open method restores the Recordset from that file.
With ADO 2.7 or later, the Save and Open methods can persist a Recordset to a Stream object as well. This feature is especially useful when working with Remote Data Service (RDS) and Active Server Pages (ASP).
For more information about how persistence can be used by itself on ASP pages, see the current ASP documentation.
The following are a few scenarios that show how Stream objects and persistence can be used.
Scenario 1
This scenario simply saves a Recordset to a file and then to a Stream. It then opens the persisted stream into another Recordset.
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim stm As ADODB.Stream
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set stm = New ADODB.Stream
rs1.Open "SELECT * FROM Customers", "Provider=sqloledb;" & _
"Data Source=MyServer;Initial Catalog=Northwind;" & _
"Integrated Security=SSPI;""", adopenStatic, adLockReadOnly, adCmdText
rs1.Save "c:\myfolder\mysubfolder\myrs.xml", adPersistXML
rs1.Save stm, adPersistXML
rs2.Open stm
Scenario 2
This scenario persists a Recordset into a Stream in XML format. It then reads the Stream into a string that you can examine, manipulate, or display.
Dim rs As ADODB.Recordset
Dim stm As ADODB.Stream
Dim strRst As String
Set rs = New ADODB.Recordset
Set stm = New ADODB.Stream
' Open, save, and close the recordset.
rs.Open "SELECT * FROM Customers", "Provider=sqloledb;" & _
"Data Source=MyServer;Initial Catalog=Northwind;" & _
"Integrated Security=SSPI;"""
rs.Save stm, adPersistXML
rs.Close
Set rs = nothing
' Put saved Recordset into a string variable.
strRst = stm.ReadText(adReadAll)
' Examine, manipulate, or display the XML data.
...
Scenario 3
This example code shows ASP code persisting a Recordset as XML directly to the Response object:
...
<%
response.ContentType = "text/xml"
' Create and open a Recordset.
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "select * from Customers", "Provider=sqloledb;" & _
"Data Source=MyServer;Initial Catalog=Northwind;" & _
"Integrated Security=SSPI;"""
' Save Recordset directly into output stream.
rs.Save Response, adPersistXML
' Close Recordset.
rs.Close
Set rs = nothing
%>
...
Scenario 4
In this scenario, ASP code writes the contents of the Recordset in ADTG format to the client. The Microsoft Cursor Service for OLE DB can use this data to create a disconnected Recordset.
A new property on the RDS DataControl, URL, points to the .asp page that generates the Recordset. This means a Recordset object can be obtained without RDS using the server-side DataFactory object or the user writing a business object. This simplifies the RDS programming model significantly.
Server-side code, named https://server/directory/recordset.asp:
<%
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "select au_fname, au_lname, phone from Authors", ""& _
"Provider=sqloledb;Data Source=MyServer;" & _
"Initial Catalog=Pubs;Integrated Security=SSPI;"
response.ContentType = "multipart/mixed"
rs.Save response, adPersistADTG
%>
Client-side code:
<HTML>
<HEAD>
<TITLE>RDS Query Page</TITLE>
</HEAD>
<body>
<CENTER>
<H1>Remote Data Service 2.5</H1>
<TABLE DATASRC="#DC1">
<TR>
<TD><SPAN DATAFLD="au_fname"></SPAN></TD>
<TD><SPAN DATAFLD="au_lname"></SPAN></TD>
<TD><SPAN DATAFLD="phone"></SPAN></TD>
</TR>
</TABLE>
<BR>
<OBJECT classid="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33"
ID=DC1 HEIGHT=1 WIDTH = 1>
<PARAM NAME="URL" VALUE="https://server/directory/recordset.asp">
</OBJECT>
</SCRIPT>
</BODY>
</HTML>
Developers also have the option of using a Recordset object on the client:
...
function GetRs()
{
rs = CreateObject("ADODB.Recordset");
rs.Open "https://server/directory/recordset.asp"
DC1.SourceRecordset = rs;
}
...