Disconnected recordsets in ADO / classic ASP
Disconnected recordsets can be used to keep the time, in which a database connection is open, down to a minimum.
If for example you need to loop through large amounts of records, perhaps heavily manipulating data before use, the connection to the database can be open for very long. Maybe long enough to be a problem if there are many concurrent hits on the page.
One solution to this is to disconnected recordsets, that keep the database connection open just long enough for the recordset to be build, whereafter the connection is closed. You can then take your time looping through the recordset, without using database resources.
The implementation is based on 4 Guys From Rolla's article Using Disconnected Recordsets
Function GetRS(strSQL) ' Returns a disconnected recordset
Const adOpenStatic = 3
Const adUseClient = 3
Const adLockBatchOptimistic = 4
Set objCon = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objCon, adOpenStatic, adLockBatchOptimistic
Set objRS.ActiveConnection = Nothing
Set GetRS = objRS
Set objCon = Nothing
Function GetPersonList() ' Returns formatted data from the discon. recordset
Set objRS = GetRS("SELECT fullName, birthDate FROM persons")
If Not objRS.State = 0 Then
If Not objRS.EOF Then
Do While Not objRS.EOF
strOutput = strOutput & objRS.Fields("birthDate").value & _
" " & objRS.Fields("fullName").value & "<br />" & vbcrlf
Set objRS = Nothing
strOutput = "No items found"
strOutput = "No output"
GetPersonList = strOutput