Struggling with SQL

[AlastairOz]

I am having trouble with trying to get my results onto my forms.
When the SQL is in a container like a cursor adapter, it works OK.
The problem is when I want to have a set of results based on records in cursor adaptors. (Instead of filtering)
For instance:
Select * from cAdaptorA Where cAdaptorA.ID=cAdaptorB.ID
this then returns the records that I want but in a browse window.
I know I need to stipulate INTO caluse, But I want the results to go into cAdaptorA or be used as such.
What do I do?

 

[Olaf Doschke]

The only source for records in a cursoradapter is the records coming from it's SelectCmd from the CursorFill() or CursorRefresh() method. Or initialising with AutoOpen, which indirectly also uses the CursorFill() method.

What is your overall goal? It seems to me in terms of a session of record editing you want to retrieve record groups just in time, just what the user wants to edit, and then finally do one tableupdate() to save all changes made to all the groups.

That's not possible this way, you retrieve records in a CA, then can change that set retrieved, add new ones of course, but not retrieve further/different records from the source database until you save the current set. For example if you knew from the start you want to edit records belonging to both customers X and Y, you can't read customer X records first, edit them, append customer X records to the already changes CA cursor and then finally save all changes, before you can refresh or fill a ca with further/other records from the source database you need to save.

The two possible options are
-CursorFill() will clear the current set and retrieve new records in accordance to the SelectCmd.
-CursorRefresh() will refresh the records you already have in the CA, but not add to them.

Both methods deny being exectured and throw an error, if there are unsaved changes to the CA cursor, so both first need a tableupdate().

There is no such thing as a CursorAppend() or CursorExtend() method to add a new part of the underlying table to the current set of records in the CA for example via a changed paramterization of the same SQL Command.

So you need to retrieve all records you want to edit within a session at once into a cursoradapter or you need to save changes to the current set and retrieve a new set.

In a 1:many situation of eg customers and orders you can do this:

You have an adaptor cadCustomers and cadOrders creating cursors crsCustomers and crsOrders, the SQL of cadCustomers being "Select * From Customers".

Then set the SQL of the cadOrders via cadOrders.SelectCmd = "Select ... From Orders Where CustomerID = ?This.CustomerID", add a userdefined property to the cadOrders cursoradapter called CustomerID, set cadOrders.CustomerID = crsCustomers.ID and do cadOrders.CursorFill()

You might do without the property and set the SelectCmd = "Select ... From Orders Where CustomerID = crsCustomers.ID", but this will make the cadOrders only wor in conjunction with a cursor named crsCustomers.

When changing the customer you'd tableupdate("crsOrders") - remember retrieving new records only works if the current cursor has no changes, then again set cadOrders.CustomerID = crsCustomers.ID and then call cadOrders.CursorFill() to retrieve the orders of the current selected customer, which changed from the previous one.

Bye, Olaf.

 

[Olaf Doschke]

By the way, this bahaviour is not due to cursoradapters. If you use updatable views - local or remote - you can also only refresh their content via REQUERY(), if there are no changes in their buffer, that is if GetNextModified(0,"aliasname",.t.) returns 0 and this is asserted after a successfull TableUpdate().

Bye, Olaf.

 

[AlastairOz]

Thanks

 

[AndrewMozley]

Does anyone have a printed copy of this book that they would like to sell?