Hi All,

We have a VFP frontend and SQL Server backend. I am using a cursor adapter to link the two and it is working VERY well. Until now, I have been using TEXT .. ENDTEXT to form up the SQL statements. Using the TEXTMERGE, I embed any keys and filters using the <<>> macro replacement feature of the TEXT .. ENDTEXT block.

I have started experimenting with using a Parameterized View instead and have run into a problem. My first step was to update the SQL in the SelectCmd property with a ?lc_key in the WHERE clause. My next step was to modify the CursorFill() method where I set a LOCAL variable for lc_key and then call DODEFAULT() to execute the function.

This works -- sort of. What I get is this: The cursor returned has the right record in it for the primary key I send over using the parameterized view. However, when the LOCAL variable (lc_key) falls out of scope and is destroyed at the end of the CursorFill method, the cursor containing the result record is lost. The cursor remains, but is empty.

I did an ASSERT .F. in the CursorFill method before the LOCAL lc_key loses scope and the cursor has the right record. I then allow the code to move out of the CursorFill method (back to the command prompt) and the cursor is now empty (the record is gone).

My guess is, the SQL Engine for VFP depends on the actual parameter variable in order to maintain the result record set in the cursor. When and if the varable falls out of scope, there is nothing for the VFP SQL Engine to reference, so the response is to remove the record and leave and empty cursor.

What I have found that works is: I make the variable a PRIVATE (from the command prompt) or a PUBLIC from the CursorFill method and because the variable is in memory and not destroyed, the record in the result set remains. As clean-up, I release the variable in the Destroy of the CursorAdapter.

There are MANY problems with this solution and I am asking for suggestions towards another and better solution.

The main problem with this solution is one of: What happens when I have more than one instance of this cursor adapter with different keys? This is VERY likely to happen. When it does, and each cursor adapter has its CursorFill method called, it will overwrite the previous adapters key data. Also, when one of the adapters is destroyed, it will release the variable, causing it to go out of scope and any other adapters with reference will respond by removing their records from the result set. This is PRESUMING I can even get that far. What I envision is multiple adapters, each with a result cursor, one variable and all result sets having the same record even though I need different records based on the key.

MOREOVER ...

I actually did create two instances of the same adapter with two different keys. What I get is both good and a little strange.

When I call the CursorFill on Adapter_A.CursorFill(), I get the right record in a cursor (alias is set to "csr_part" + SYS(2015)). Then I call Adapter_B.CursorFill() and get the right record in another cursor. My PUBLIC variable gc_key is set to the second key for Adapter_B, yet the first cursor for A remains as is (I expected the results of A to be the results of B as the parameter has changed).

If I now RELEASE gc_key (take it out of scope for BOTH adapters) they keep their data. As a matter of fact, I can make updates and issue a TABLEUPDATE(.T.) on both of them and the changes are passed over to SQL Server as one might expect.

So, given the right behavior, albeit a little strange, I am left to conclude that I can create an instance of an Adapter with it's key and then destroy the PUBLIC variable immediately afterwards. This seem insanely sloppy and convoluted, but appears to work ok.

Curiouser and curiouser ...

Does anyone have a way of handling this matter in a straight-forward fashion?

Thanks in advance!
Larry