Issue with Database Access in Middle Tier

[Robert S]

I've built a ActiveX exe Data Access Layer which uses ADO to connect to an Access Database. The client PC's use the object's GetRecordSet() method to gain access to the data. These recordsets are used by Crystal Reports to produce the applications reports. Here's the problem, The Server PC (ActiveX exe) runs out of memory while its looping through the recordset and updating it. It happens when a client PC request a large recordset. The ActiveX exe uses Client Cursor Service because I need to process through the recordset and call a function to decrypt the Encypted CardNumber, updating the recordset but not the database. This is done prior to sending it over the network to the client.
Here's how I set the recordset properties and open the recordset.

Dim cnn As Connection
Dim rsConnected As Recordset
Dim sSQl As String


Set cnn = New Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabasePath & ";Persist Security Info=False"

Set rsConnected = New Recordset
sSQl = "SELECT * FROM Transactions"
rsConnected.CursorLocation = adUseClient
rsConnected.Open sSQl, cnn, adOpenDynamic, adLockOptimistic

'Disconnect Recordset from database
rsConnected.ActiveConnection = Nothing

'Process through recordset, decrypt credit card column
If Not (rsConnected.BOF = True And rsConnected.EOF = True) Then
rsConnected.MoveFirst
Do While rsConnected.EOF = False
rsConnected.Fields("EncCardNumber").value = DecyrptData(rsConnected.Fields("EncCardNumber").value)
rsConnected.Update
rsConnected.MoveNext
Loop
End If


Thanks for any help you can offer.

[bhanuprakash]

in your code i would suggest you to use the sql update command instead of using recordset.update since every time you this it consume some part of you rmemory and when the set of records are more then you memory over flows.Instead you can make use of bind variables where the same update query is used for many times and memory is most properly utilised to give you example i am writting down the bind variable code

dim cmdprm as new adodb.command
do while ....
cmdprm.activeconnection = xyz
cmdprm.commandtype = adcmdtext
prm1 = DecyrptData(rsConnected.Fields("EncCardNumber").value)
set tmpprm = cmdprm.createparameter("prm1",adchar,adparaminput,15,prm1)
cmdprm.parameters.append tmpprm
sql = "update transactions set EncCardNumber=?"
cmdprm.commandtext = sql
cmdprm.execute sql
cmdprm.cancel
set cmdprm = nothing
rsConnected.MoveNext
loop


there is some thing known as the sql area which keeps tracks of how recursively the same query is executed so i this case the same query is used so the memory is most effectively used.

the other reason could be you are using an activex.exe after instantiating and using the activex class you should finish the activex by setting it to nothing or else it will still be in memory and nexttime you instantiate it will create another reference of that object.

if it is database related i would suggest you use bind variables it executes fast also.

[Robert S]

Thank you for your response. But It will not work in my case because you are using the execute method on a command object to update the database. I don't want to update the database. The database column 'EncCardNumber' contains an encyrpted string. I need to extract the dataset from the database, decyrpt the Credit Card Number from the Encrypted String, pass the recordset to the client for reporting and NOT touch the database. I'm assumming your solution updates the database? By the way what are bind variables? Didn't find them within help files.

[bhanuprakash]

what does your DecyrptData function do is it a simple function or an object.

[Robert S]

It's a user-defined function, which makes a call to a DLL function. The DLL was written by one of our C++ developers. The way the function works is you pass it a encrypted string and it returns plain text.