Unique Name for Temporary cursor, which is to be used in two different occasions.

[MSiddeek]

I have created a temporary cursor from existing sql table on form load event of Purchase form, as follows. Just in case if the supplier not available, there is a command button on the purchase form to create supplier, it calls the supplier creation form, where I have written the same code to create temporary supplier cursor, on this occasion supplier is created and the supplier create form get closes. But the purchase form throws a message saying Alias '__5GMX....' is not found.

public supplier
supplier="_"+SYS(2015)
lnHandle = SQLStringConnect("Driver={SQL Server};Server=SOMEONE\SQLEXPRESS;Database=softcode;user=sa;Pwd=password")

SQLEXEC(lnHandle,"SELECT * FROM supplier","ftemp")
SELECT * FROM ftemp INTO CURSOR &supplier READWRITE

I think when supplier form creates supplier="_"+SYS(2015), it eliminate the temporary variable create by purchase form. is there any way I can overcome this problem I mean variable supplier specific for a form??.

MSiddeek.

 

[Olaf Doschke]

No, setting a variable doesn't close a workarea.

But other code still acting on the variable name "supplier" now acts on another cursor, as you cahnged the variable value.

Why put an alias name into a public variable? If you have a form specific alias, put this name into a form property, if it is business (data access) object specific, make it a property of that. Typical scoping of names of workaeras for forms is in properties, not in variables, so learn how to add user defined properties where you want to store that. And for controls you have the native controlsource binding properties.

And besides trhat, you don't need to copy over ftemp to the final workarea name, SQLEXEC allows you to specify the alias name as you want right away.

Just an advice: I got into the habit of prefixing random aliases with a context specific name part, eg set supplier = "supplier"+Sys(2015) and you also have it simpler in debugging to identify workareas and what data is in them.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Just as a demo, why changing the value of a variable does not close some alias:

Close Tables All

Open Database (_samples+"northwind\northwind.dbc")

Public suppliers
suppliers = "suppliers"+Sys(2015)
Use suppliers Alias (suppliers) In 0

suppliers = "suppliers"+Sys(2015)
Use suppliers Alias (suppliers) In 0 Again

Select (suppliers)
Set
Set Step On 
* See two used workareas

The problem is you only have one variable now and two scopes each needing a scoped variable for its scope. If that's the same form started twice or the same data access object used in two contexts doesn't matter. This just shows how a public variable is a wrong choice for it. You likely need more than just a local variable, as many methods of the form handle the workarea, but once you need more than one instance of something you also need two variables or properties and the same code cannot work with variables (neither local nor private nor public variables work), so the only choice is properties.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[MSiddeek]

Olaf,

Thank you for the answer, tips and advice, I set the variable into form property, this time I got the error saying supplier not found.

Further I copied ftemp into final work, because I treat supplier as cursor rather than a table.

MSiddeek

 

[Olaf Doschke]

> this time I got the error saying supplier not found.
I don't know how to help with that one if you don't give any details.

Separate workareas mean you have separate record pointers in the two separate workareas and since the data can change between the two points in time you may not find the one in the other, this is not like USE of DBFs, once you SQLEXEC and then insert new records in another form, they don't get added to the cursor that once fetched SELECT * FROM suppliers. So most likely that's now a problem because you're not aware how to really handle remote data and query results. If you need some new record in both forms, you need to refresh your cursor.

>I copied ftemp into final work, because I treat supplier as cursor rather than a table.
ftemp already is a workarea. SQLEXEC gets the remote data into your process as cursor in a workarea, You copy from workarea to workarea. Instead just tell SQLEXE which workarea you want to end in, and it does so in the first step. There is no need for ftemp, even if your SQLEXEC already ia done in a function for that always queries into the workarea ftemp. Make it a parameter of your wrapper function to query with the target workarea alias name, otherwise, you're just wasting time.

Bye. Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name