CursorAdapters, Standalone vs Inside DataEnv

[stanlyn]

Hi,

I'm restarting my journey with cursor adapters that I have not yet mastered after 7 months being pulled away to deal with the cleanup of the 1000-year flood that hit us here in eastern KY on July 28 of last year. We were at ground zero. My last post on Tek-Tips was just a few days before.

Having paying customers using our software has provided us an income that allowed us to deal with this crisis on a full-time basis. We have had to pace ourselves in the recovery efforts, as the shear expense of replacing items that were destroyed or washed away is staggering. Imagine trying to immediately replace all your vehicles, furniture, clothes, household items, servers, inventory, tooling that took years to accumulate. Impossible for most of us...

Many lessons learned, and having paying customers for our software is near the top, as we lost almost everything, home and business furnishings and figuring out what we should do next, software dev is at the top of the list.

Another lesson learned was all SSDs in workstations and servers survived after being submerged in 8 feet of muddy flood water for 2 days. I washed and dried them and was able to recover 100% of all data on all SSD units, whereas we lost 100% of all the hard drives. They could have been recovered if we could have acted faster, as the first month was dealing with shelter. I was able to recover 40+ years of data from backups. The flood got to our production equipment as well as our backup servers. The servers were all in one fireproof area. Since then, they and the switch room have all been statistically relocated to safeguard against both fire and flooding. Talk about being close, six more inches and we would have lost all of our backups (tapes and hard drives).

Sorry for getting so far off topic, so back to some questions...

I'm using VFP9sp2 and this is all about remote MSSQL and forms. I have looked high and low and cannot find any form examples of using a cursoradapter within a form, and not to mention doing remote lookus for combobox populations.

1. Do I have to use the form's DE to be able to drag & drop fields onto the form.

2. Instantiating a cursoradapter class in the form's load or init events does NOT allow for selecting control data sources? We lose the RAD features?

3. If both 1 and 2 above are true, then should all forms be built with cursoradapters as part of the form's DE?

3. How is the best way to do remote data lookups? By including a cursoradapter for the lookup inside the DE, or instantiate a cursoradapter class that was prebuilt and outside the DE, as a stand-alone cursoradapter class?

4. I've looked at all docs from Doug, Whil, Tamar to find such an example, with none found. I also looked at Drew Speedie's Visual MaxFrame framework for an example and he had none as VFP8 was just released. Can someone provide an example or point me to one that deals with lookups, relations and navigation? I am willing to pay...

Thanks,
Stanley

 

[ggreen61]

Sorry that you had to endure the flooding; it is good that you have been able to recover. Interesting that the SSDs survived the flooding. I guess they are sealed very well, and as such, were protected. To your question...

I don't use cursor adapters. I do use the DE at design time in order to gain the benefit of the tables being listed in the form. In the form's DE BeforeOpenTables() event, I add code to change the VFP tables (remove). I then write the SQL code to retrieve what the form needs from the tables into cursors as read-write. In some controls, I store the cursor contents that is needed (such as combobox or listbox) into columns that are hidden (set the column property width values to zero); the loading of these controls is done in the control's Init() event since the DE is processed before any control is instantiated. This way I do not need the keep the cursor open. I track any changes being made to the cursor by the users via an additional column during the table select by defining a field "0 AS changed" (0-no change, 1-changed, 2-new record). In the update or save processing, I start a TRANSACTION and perform the updates/inserts as appropriate based on the changed field value. This way I can initiate a rollback if there is a failure to update.

Greg

 

[Chris Miller]

DE is a natural environment of CAs. But like you can make use of the USE command elsewhere, you can make use of a CA elsewhere and separately from a DE. Both usages are fine. Espcially, if you're at oop redesign usage of D classes and not form DEs. Followed by usage of form classes, not SCX forms.

Chriss

 

[stanlyn]

Hi Greg,

I'm a little confused with your answer, so I'm going to reply to each part with what I think you mean, or pose a question...

>> I don't use cursor adapters. I do use the DE at design time in order to gain the benefit of the tables being listed in the form.

1. So, for the customer form that shows orders, you build out the DE containing a CA for "customers" and another CA for orders".

2. If yes, then do you create the CAs as read/write ALL fields except the PK?
3. Do you set any relations at this step?


>> In the form's DE BeforeOpenTables() event, I add code to change the VFP tables (remove).

4. Why would you remove the tables from the CA since the form is based on the removed CA. Makes no sense. It does make sense to change the CA's SelectCmd and filtering, then doing a cursorfill(). Please explain this a bit more...


>> I then write the SQL code to retrieve what the form needs from the tables into cursors as read-write.

5. Where do you put this code and what would a code snippet look like?


>> In some controls, I store the cursor contents that is needed (such as combobox or listbox) into columns that are hidden (set the column property width values to zero); the loading of these controls is done in the control's Init() event since the DE is processed before any control is instantiated. This way I do not need the keep the cursor open.

6. Where are the columns located that you are storing the cursor contents to?


>> I track any changes being made to the cursor by the users via an additional column during the table select by defining a field "0 AS changed" (0-no change, 1-changed, 2-new record). In the update or save processing, I start a TRANSACTION and perform the updates/inserts as appropriate based on the changed field value. This way I can initiate a rollback if there is a failure to update.

7. I understand this, assuming the cursor you mention is the one made available by the CA. You mentioned earlier not needing to keep the cursor open, thereby closing it, so a little confusing here.

Thanks,
Stanley

 

[Chris Miller]

I can't speak for Griff, but as he says he doesn't use cursor adapters he describes what he does instead.

And indeed just like with updatable views and specifying pk field and updatetype and some more properties, you have less work with putting back edited data into the tables than b using readwrite cursors. I men, that's the advantage views lready have over just SQL into cursor readwrite.





Chriss

 

[Chris Miller]

To get back your productivity with tables in the DE, you do not use the context menu item "Add Cursoradapter" of a forms DE. That adds a base class CA with nothing and is a red herring on how to work with CAs in a DE of a forms. You go ths way, stanlyn:

1. Create a DE class, that's a new visual class type.
2. To add a CA into such a DE you can drag&drop CA classes from your CA classlibraries into such a DE class. Of course this implies you also create CA classes.
3. Add such a DE class to a form by setting the form DEClass property. You get a warning dialog this will replace the usual DE of the form. That's what you want, so confirm it.
4 Now open the form-> Dataenvironment and you find your DEclass prepared in steps 1 and 2 with objects that you can drag into the form, just like view os tables in a normal DE.

Chriss

 

[stanlyn]

Chriss,

have over just SQL into cursor readwrite

I've never heard of this... Is this a CA?

Thanks,
Stanley

 

[stanlyn]

Chriss,

I'm also seeing references to skipping the DE class/stuff altogether. Instead just create a CA in code with the oCA = createobject("caClient", "cClient"), set the form's BindControls=.F. and build out the oCA properties then set BindControls = .T. after cursorfill(). Can maybe live in the load or init methods, or better location for creating the "oCA"?

Your thoughts...
Stanley

 

[Chris Miller]

No SQL into cursor readwrite is literally that, simplest example:

Select * From mytable into cursor mycursor readwrite

It makes mycursor editable, but all edits get lost, unless you actively program to update the source with changes. You have all that simpler with updatable views or CAs. I get the impression you never used any SQL so far, did you?

If you are not familiar with SQL, OOP classes and then want to change from a DE with DBFs put into it to CAs instead, and also have no experience with how updatable viws work, then you have at least three hurdles to take at once. I don't know if you really get there, then.

Chriss

 

[stanlyn]

Chriss,

I'll work with your suggestion and get back.

Also, what do you mean by the "D" in...

with tables in the D

Is it DB-database or DE... Guessing, I would go with database?

Thanks,
Stanley

 

[Chris Miller]

I meant in the DE. You drag from the DE to the form to get either a grid 8dragging a whole table) or a single control (dragging a field). That's the productivity you talked about, isn't it?
If you follow the steps I posted above you get back to this. The DE object for the CA that already is defined as a class before adding it to the DE will be just like a view or a table and list its fields and is draggable to the form from there to add controls already bound to the CA cursor.

Chriss

 

[Chris Miller]

Seems my E key needs to be cleaned, after several edits now my last post is telling you what I mean with D.

Chriss

 

[stanlyn]

Chriss,

Looks like I guessed it wrong as I saw several references where you typed DE, so its logical to assume no mal-functioning equipment (keyboard). Anyway thanks so much...

Stanley

 

[stanlyn]

Hi Chriss,

No SQL into cursor readwrite is literally that, simplest example:
Select * From mytable into cursor mycursor readwrite
It makes mycursor editable, but all edits get lost, unless you actively program to update the source with changes. You have all that simpler with updatable views or CAs. I get the impression you never used any SQL so far, did you?

I get it, but never heard it called that. I have used this for years for populating comboboxes (without the readwrite) and cursors that drives reports.

If you are not familiar with SQL, OOP classes and then want to change from a DE with DBFs put into it to CAs instead,

I have done a lot of SPT, so I am very familar with SQL as well as using SQL syntax against VFP tables for reports.

have no experience with how updatable views work, then you have at least three hurdles to take at once.

I am struggling with views and cursoradapter implementation in forms, as there are no published examples. And I have asked repeatedly for one or a reference to one, and everyone just skips over it as if I never asked. The sample documentation does not include any. I believe I can get there if I had a single example form that has a parent/child relationship with an operational search procedure (not the blackbox versions that gets generated using a form wizard) along with a combobox for lookups.

Also, I do need more OOP skills to turn the whole thing into everything class based, which I never intended to do. I never intended to move to form classes as there are too many .scx based forms that works just fine with dbf against live tables.

I get the impression that class based forms is a requirement, and I hope not... I also get the impression that you live in VFP on a daily basis based on the breath of your knowledge and skill set. I see you on cloud 9 and I'm maybe on cloud 5 or 6. But, thats why I come here for answers and help from experts like you and the many others that have chimed in.

I don't know if you really get there, then.

I have to get there, no choice... as converting to .net in the short time I have to get some projects done is out of the question. I actually considered it...

Thanks,
Stanley

 

[Chris Miller]

Stanlyn,

I think the most important thing I need to address is how updatable views work. As your answer to that is off the scale, to me.

You said:

am struggling with views and cursoradapter implementation in forms, as there are no published examples. And I have asked repeatedly for one or a reference to one, and everyone just skips over it as if I never asked. The sample documentation does not include any. I believe I can get there if I had a single example form that has a parent/child relationship with an operational search procedure (not the blackbox versions that gets generated using a form wizard) along with a combobox for lookups.

Updatable views are not about relationships of two aliases, like you meantion by talking of "parent/child relationship with an operational search procedure".

A view populates one workarea, it's one list of data, one result. The problem you're talking about is the overall strategy of what views (or in the long term what CAs to define to replace USE of DBFs in a working way, but that's another topic.

There is no documentation about architecture of applications, that's not a topic of a language documentation help, you would learn that from OOP architecture principle books, not from the VFP help. but how updatable views works, that's mainly the documentation of the portions of the designers/builders and the principles of buffering of data and usage of TABLEUPDATE().

One help topic is that:
Managing Updates by Using Views
Then The documentation of the corresponding tab of the view designer
Update Criteria Tab, View Designer

And for CAs the documentation of the builders Auto-Update tab:
Auto-Update Tab, CursorAdapter Builder

And for SPT, the topic how to make a cursor fetched by SQLExec updatable with several CURSORSETPROP.

So, that means overall there's the topic of buffering usage I forgot to mention as it's already so natural to me. All these techniques are based on the same properties vfp needs to knwo about the fields of a workarea, to get one thing going, that's very essential:

You only design the SQL-SELECT of your data access and get result sets that way, that only have the condition they contain the keys necessary to work with in UPDATE, DELETE and also in INSERTs.
You then mainly use these queries to fetch your data into cursors that in themselves are writable, every change made from the point after data retreival then is buffered and field states are stored, that tell VFP which records are modified, deleted (as you know still in the records just with a deletion marker), and also which records are new. And based on that knopwledge and old vals and current vals TABLEUPDATE can write and execute the SQL-INSERTS, SQL-UPDATES and SQL-DELETES for betting the buffered state of your cursors back into the database.

That's that topic.

As far as I can tell from your respinses, CAs are not for you. It'll only frustrate you and I don't think anybody can teach you what you need to know via a forum. Besides teaching the necessary technical details as already said this needs teaching of the architecture concepts behind all this, too, and that's surely not found in the VFP help.



Chriss