Visual Basic and MTS 1

[Pranali]

This is my Com component

cCriteria

Option Explicit
'Implements iCriteria
Private cn As ADODB.Connection

Public Function GetCriteria(BYVAL strsql As String) As ADODB.Recordset

On Error GoTo PROC_ERR

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

cn.CursorLocation = adUseClient

Set cmd = CreateObject("ADODB.Command&quot
cmd.CommandType = adCmdText
cmd.CommandText = strsql
cmd.ActiveConnection = cn
Set rs = CreateObject("ADODB.Recordset&quot
'rs.CursorLocation = adUseClient
rs.CursorType = adOpenForwardOnly
Set rs = cmd.Execute
Set GetCriteria = rs

PROC_EXIT:
Set rs = Nothing
Set cmd = Nothing
Exit Function

PROC_ERR:
Debug.Print Err.Description
App.StartLogging App.Path & "\error.log", vbLogToFile
App.LogEvent Err.Description, vbLogEventTypeError
Resume PROC_EXIT

End Function


I compile the component and install it onto my MTS server.Createa
package
and export it. and install the component on my system Now I try to use it In my client app

Public SelectObj As WalmartServer.cCriteria
Set SelectObj = CreateObject("WalmartServer.cCriteria&quot
Set rsClient = SelectObj.GetCriteria(strSQL)


I get the Following error on this Statement

Set rsClient = SelectObj.GetCriteria(strSQL)

Automation Variable used not supported by Visual Basic.
OR
Method 'GetCriteria' of object '_cCriteria' failed



This problem only comes when I try to install the component and try to
run
the app,....but everything is fine in the debug mode...Like running the
whole app as a group.


Reply as soon as possible will be appreciated....If you have anymore
questions , I will be happy to answer them

 

[chiph]

You should disconnect the recordset before returning from your function. This is done by setting the ActiveCOnnection property to nothing.

But, more than that, I would advise against returning a recordset object from your MTS function. I have seen strange problems caused by this. I would recommend converting it to a XML string, and returning the string (which is always safe). In the calling program, you can then convert it back into a recordset using the load method.

Chip H.

 

[Pranali]

Thank You very much Chiph.
Can you point me to an example of converting Recordset to XML string and string to Recordset.

Thanks Again
Pranali.

 

[jmarler]

Chip,
The company that I am working for right now has mandated that all marshalling be done with ADO recordsets. Packed byte arrays and XML not allowed . . . trust me, not my idea (it a political thing), but I just do as I'm told in some cases. I know that MS claims that disconnected ADO recordsets will marshal fine (and I really hope that is true), so I am wondering what problems you have seen, and are can they be reproduced? Thanks!
- Jeff Marler B-)

 

[chiph]

Jeff -

I haven't seen any specific problems, only that I have seen problems marshalling other objects, and well, a Recordset is an object, so by extension....

Pranali -

To use ADO's way of saving (persisting) a recordset you use the Save method on the Recordset object with the adPersistXML flag
[tt]
Dim rs as New ADODB.Recordset
rs.Save "c:\yourFile.xml", adPersistXML
[/tt]
To load it back in you use the open method:
[tt]
dim rs as New ADODB.Recordset
rs.Open "c:\yourFile.xml"
[/tt]
But honestly, it's not that good. Any <NULL> columns don't show up in the resulting XML, which can cause problems reading it back in.

The best way is to write your own functions that process a recordset. One to produce an XML string, the other to turn it back into a recordset.

In the &quot;RStoXML&quot; function, you want to do something like:
[tt]
Dim MyField As ADODB.Field

Debug.Print &quot;<?xml version=&quot;; 1#; &quot;?>&quot;
Debug.Print &quot;<MyRecordset>&quot;
Do While Not myRS.EOF
Debug.Print &quot;<MyRow>&quot;
For Each MyField In myRS.Fields
Select Case MyField.Type
Case adChar, adVarChar:
Debug.Print &quot;<&quot; & Trim$(MyField.Name) & &quot;>&quot;;
Debug.Print &quot;<![CDATA[&quot; & MyField.Value & &quot;]]>&quot;;
Debug.Print &quot;</&quot; & Trim$(MyField.Name) & &quot;>&quot;;
Case adInteger, adSmallInt, adUnsignedInt:
Debug.Print &quot;<&quot; & Trim$(MyField.Name) & &quot;>&quot;;
' Add additional attributes to describe the
' field's datatype, length, precision, etc.
Debug.Print MyField.Value;
Debug.Print &quot;</&quot; & Trim$(MyField.Name) & &quot;>&quot;
End Select
Next MyField
Debug.Print &quot;</MyRow>&quot;
myRS.MoveNext
Loop
Debug.Print &quot;</MyRecordset>&quot;
[/tt]

To recreate the recordset, you'd load the XML string into a XML DOMDocument and navigate through the tree. For each node, you'd add another field to your record object, and when you hit the last field in a record, you'd add that record to the recordset.

Chip H.

 

[Pranali]

Thanks a lot...It helped me a lot.
I also tried using Disconnected recordset and its working fine.But I am also going to try XML strings...


I changed the function from

Public Function GetCriteria(ByVal strsql As String) As ADODB.RECORDSET

to

Public Function GetCriteria(ByVal strsql As String) As Object

 

[jmarler]

Chip,
Thanks for your reponse! I completely agree with you when when you mentioned marshalling &quot;other&quot; objects, however, according to MS, even though an ADO recordset is technically an object, it internally will serialize its state, pass that state data via stub and proxy and then rebuild the ADO recordset object with that serialized state data. As far as I have heard, ADO recordsets are the only objects that will automatically do this . . . I am still a bit leary and was wondering what your experience (or anyone else's foe that matter) was these . . . from what I have seen thus far, they seem to be working ok. - Jeff Marler B-)