Accessing cursor from class created as Session object

[chriscboy]

Hi,

I have written a class based on the session object that is used to access VFP and SQL Server data (using SPT). It works very well except when I want to access a results cursor returned from a SELECT statement. I need to get access to the datasession of the object in order to view the cursors that get returned from SQLExecute(). Below is an example of the code i am using to get the data:

o = CreateObject("emssqlclass.emssql")
o.SQLConnectionString = "uid=xxx;pwd=xxx;Server=xxx;Driver={SQL Server};Database=CRM"
If o.SQLConnect(.T.)
? "Connected"
o.ExecuteSQL("SELECT * FROM dbo.Company","curtest")
o.SQLDisconnect()
EndIf
Release o

Now this should create a cursor called "curtest" which i can access. If I change the class to a custom object I can access the cursor fine.

Is there any way I can set the datasession to that of the object in order to access the cursor?

Regards

Chris

 

[AndyKr]

Chris

You can use the SET DATASESSION command to change into the datasession of the Session Object:

o = CreateObject("emssqlclass.emssql")
SET DATASESSION TO o.DataSessionID

Then you will be able to access the cursor but only as long as the object persists! If you release the object it will close its datasession, releasing any cursors that it has created.

So if you want to release the object, a better solution will be to switch the connection object into the current datasession before you create the cursor...

lnSession = SET( "DATASESSION" )
o = CreateObject("emssqlclass.emssql")
o.DataSessionID = lnSession

This way the object is brought into the current datasession





----
Andy Kramek
Visual FoxPro MVP

 

[chriscboy]

Hi Andy,

Thanks for the response. I have tried this but get the following error, when trying to set the DataSessionID of the object :

OLE Error code 0x80020006: Unknown name

The class object is compiled as a Multi-threaded COM server DLL. Do I need to add a property in the class definition to expose it, as it doesn't seem to be there?

Thanks

Chris

 

[Olaf Doschke]

As a COM server you can't get at the cursor. If you were using the session class you could set DataSession = 1 (default datasession) in the class definition, but if you create a COM class this runs in it's own memory. It's in the process of the calling exe but still not in your scope. You would need to convert the resultcursor to xml, return that and reconvert to a cursor (cursortoxml/xmltocursor, needs MSXML to work on another client besides vfp runtimes and get's slow with large cursors), or store a dbf on disk and return it's filename.

Bye, Olaf.

 

[Olaf Doschke]

...or return an adodb.recordset, which would make that DLL usable from other languages too, but is of course also a slower option as xml.

 

[chriscboy]

Thanks for the ideas Olaf.

The recordsets wont be large (about 100 records) so I might use the XML appoach. Maybe add a property which will contain the XML results cursor.

 

[Olaf Doschke]

make sure to create the XML with an inline schema, see parameters in the help. Otherwise results of xmltocursor could be different from the original cursor.

Bye, Olaf.

 

[AndyKr]

>>The class object is compiled as a Multi-threaded COM server DLL. Do I need to add a property in the class definition to expose it, as it doesn't seem to be there?

If this is a COM server then you cannot do this. The reason is that the server is running in a different thread and there is no way to access its data directly.

The alternatives are to:
[1] Return the results as XML and then convert back to a cursor

[2] Return the result as a RecordSet and convert back to a cursor. (You will need the VFPCOM utility for the CursorToRS() and RSToCursor() functions - it's a free download from Microsoft).

Either will work well, though I generally use XML for this type of operation.

----
Andy Kramek
Visual FoxPro MVP

 

[chriscboy]

Thank you all for your answers. I ended up adding two new properties to my class, one holds the results as XML, and one holds the results as an ADO Recordset.

I can now get at the results easily whether I am using VFP or Word automation.

Chris