Creating CursorAdapter Completely in Code? 1

[stanlyn]

Hi,

What is the best way of creating a cursor adapter for use with a form, report, or a .prg in code other than hacking the .srx?

Can it be placed in a .prg file so that it can be easily edited by reading variables.

Currently, it seems too hardcoded, as either I have to open the form in the gui, and edit the adapters property sheet, or run the builder, or hack the .scx file.

What is standard practice?

Thanks, Stanley

 

[Mike Lewis]

Hi Stanley,

The CursorAdapter is a class, so you can easily instantiate it in code, set its properties and call its methods. For example:

loCursor = CREATEOBJECT("CursorAdapter")
WITH loCursor
 .Alias = "MyCursor"
 .DatasourceType = "ODBC"
 .Datasource = SQLCONNECT("MyConnection")
 .SelectCmd = "SELECT * FROM SomeTable"
 
 IF CursorFill()
   * Cursor now available
 ELSE
   * Something went wrong
 ENDIF 
ENDWITH

This is just off the top of my head, but it should give you the general idea.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[stanlyn]

Hi Mike,

So how would it be attached/associated with a form or a report, or a .prg? I see that the form has a property for the DE class, but I didn't see where you could attach a cursor adapter.

So, can the adapter be in its own class, or just be a member of a DE class? I did see where I can save a DE as a class. I can select a cursor adapter that is part of a DE and when saving as a class, the resulting dialog only shows with the DE checked.

How would one create a DE that the main.prg opens before the read events for use by all forms and reports, and procedures? (much the same way as native tables behave)

Any ideas?

Stanley

 

[stanlyn]

Also Mike,

Lets say that the DE contains 2 cursor adapters named customers and orders.

1. Can they be and how are they joined?

2. How are they addressed from each other ie.
select cust_id, orders.cust_id, orders.inv_num from customers where orders.inv_num = '123'

3. Any limitations in the select, update, insert, and delete statements regarding the builder or hacking the .scx? I did ree the 255 char limit in the properties window.

4. Any good readings on VFP9 DEs and CAs? I've read the article in "What New in Visual FoxPro 8" by Whil Hentzenwerke as well as the one by Venelina Jordanova from the SW2011 conference.

Thanks, Stanley

 

[Mike Lewis]

Stanley,

The code I showed you ultimately creates a cursor, which you can use in much the same way as any other cursor or table. But it's not the most convenient option for binding the controls on a form.

In that case, I would probably use the CursorAdapter Builder:

1. Open the form's Data Environment.

2. Right-click, and choose Add CursorAdapter.

3. Right-click on the CursorAdapter, and choose Builder.

4. Fill in the required details in the Builder dialog.

Note in particular the Auto-Update tab, which lets you specify the options for an updatable cursor. You will need to specify a primary key, and say which fields are to be updatable.

Once you have the cursor adapter in the data environment, you can treat it much like a table, including dragging fields onto the form, using it as a control source, etc.

You can also join two cursor adapters, although you would normally do the join within the SQL command.

I hope this goes some way to answering your questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

1. No, but you can join their result cursors, but that's not the way to go, really.

If you put views in a DE you could do the same, but it will query view cursors and afterwards will join data of these views into a third, "normal" cursor. Which then is neither connected to the view cursors nor to the underlying tables of the views.

2. You would rather put that SQL inside one CA or let the CA query each table data seperately and SET RELATION.

But wlel, what cursor name they generate is given be the CA Alias property, that's what names you get and can adress as cursors.

3. there even is no 255 char limit in property window, that's a limit of string literals, strings in code written in string delimiters. You typically use auto-update and only specify selects, not update or insert or delete.

4. If you have only worked with tables before, you will first need to get a grip on remote data access. CAs are the better choice over views, but in principle they are views and you work with them as with views.

They even are a nice choice for working with DBFs. The fewer tables you have the more often I'd go for 1-tier design and use tables, you may know by now this can get sluggish, once table data grows larger. Querying just the portion of data you work on is getting more ipolrtant then, and then a more complex approach with data access objects like views or cursoradapters are get's more important.

Besides Views and Cursoradapter Cursors make it easier to use them as grid datasource. Though this is just a little aspect, it's one costing newbies quite some time, once they start requerying data to a grid, getting side effects of losing grid design or code. Therfore I also already advised usage of CA to beginners. Makes it less rapidly development, a steeper learning curve, but one leading to a better initial design - just my oppinion.

Bye, Olaf.

 

[Olaf Doschke]

A hint on how to work with CAs:

1. For more ease of updatable CAs only updating a single remote table, you will have seperate CA classes
a) for each single table (mostly updatable)
b) for each query joining tables (mostly readonly)
2. You will have DE Classes in which you drag&drop CA classes you want to use in context, individually per form.

What I do often, by demand, is poulating treeviews. That mostly only requires type 1a) CAs for each level of nodes. If a tree node is expanded I can query child data for the parent node via the child table CA, therefore I don't need a cursor having joined data in the first place.

If you query joined data for modification, you afterwards query the same data into simple CAs and update them, so this takes a two step process. Simpler joins of eg two tables, can have the primary keys of these two tables in the KeyFields property and then Tableupdate(1,.t.,"CA_Alias") updates both tables, but I never needed that.

What you still can do with the simple single table CAs and their cursroschema matching that single table is do SQLs joining other tables for filtering, all you need to take care of is only having the schema fields in the result, eg you can do a CA for orders and query not only by customer.id but also customer.name, changing SelectCmd of the CA to:

select orders.* from orders inner join customers on customers.id = orders.customerid where customer.name = 'whatever'

or

select orders.* from orders where orders.customerid in (any complex subquery on customers having customerid in it's result)

So the CA allows you to change SQL-SelectCmd on the fly, if you don't change the result set schema. Views won't allow that. Even only using that to assemble a dynamic where clause is helpful.

Bye, Olaf.

 

[Olaf Doschke]

Oh,

and in a framework I use containers are used as bizobjects, which can eg be added to a form by form.addobject() in it's load, these containers have a property naming the DEClass to use and a property called oDataenvironment, into which that DE class is set via this.odataenironment = createobject(this.cDEclass) and so neither the form.DE itself, nor form.DEClass are used and the DEClasses don't have a parent object, reference to them is just stored in a peroperty.

You can easily instanciate more than one bizobject handling more than one DEclass, each having individually put together CA classes, so you can organise them in more lightweight portions of tables you individially put together.

This way I can reall concentrate on aspects/domains/functionalities/categeories of the application on the biz object level and DEClass level, not only on the form level.

Even before CAs business objects and data access classes were part of most any application framework giving you more easy to plan the composition of tables, as a DE or now DEClass does.

Bye, Olaf.

 

[stanlyn]

Olaf,

>> But wlel, what cursor name they generate is given be the CA Alias property, that's what names you get and can adress as cursors.

So the CA's alias would contain two comma delimited names representing each of the cursors that a single join would create, or a single cursor containing data from both joined tables all represented by the CA's alias?


>> 3. there even is no 255 char limit in property window, that's a limit of string literals, strings in code written in string delimiters. You typically use auto-update and only specify selects, not update or insert or delete.


So, without filling in the insert info, the CA can insert a new record when I add one to the local cursor? If I insert into the local cursor by using either append blank or insert into, that local cursor data will be pushed back out to the sql table, all without adding code to the InsertCmd section of the builder?


In this terminology, I think of SQL tables as "tables" which is the remote side of the adapter, and "cursors" as the local side of the adapter. Is this correct?


>> simple single table CAs

Are you referring this to a single cursor where the join is part of the SQL statement??


>> So the CA allows you to change SQL-SelectCmd on the fly,

Where do I do the changing/assignments?? thisform.?????????????????.SelectCmd = "select orders.* from orders inner join customers on customers.id = orders.customerid where customer.name = 'whatever'"


>> in a framework I use containers are used as bizobjects,

How are these containers created, as I thought that a container can only live inside a form or other lower objects. Here I understand you as saying that your containers live outside the form, correct?? How are they saved on the harddrive, file types? I like this approach, but it does appear to be complicated, but yet so simple once you've done it...


Time to make some of this stuff work...

Thanks Olaf and Mike!
Stanley

 

[Olaf Doschke]

>So the CA's alias would contain two comma delimited names
>representing each of the cursors that a single join would create
No
>or a single cursor containing data from both joined tables all >represented by the CA's alias?

Yes, one Alias, as with any SQL-Select, the result is one resultset, having one alias name.

It's Cursoradapter, not Cursorsadapter. Like a remote view, it will only add the result to the datasession, not the tables. Or unlike a local view, it will not put tables there, that can only be done with DBFs, not with anything remote. And you only work on that alias. In local views you could also work on the underlying tybles, but that is finagling your way around the view. With CA of DBFs you have the same effect as with local views, DBFs are opened to, remote tables are never "opened", such a concept doesn't exist, they are queried and a result is forwarded to a client, in cas of the CA in form of one, single cursor.

>So, without filling in the insert info, the CA can insert a new record when I add one to the local cursor?

Yes, once you specify waht is key fields and what fields are updatable and how they are adressed remotely via CA properties the CA does auto.generate Insert- Update- and DElete-Statements, as a view also does, this is nothing new.

In CA you can see what is generated, eg in BeforeInsert Event of the CA the insert command, that will be used is given as a parameter and you can still intercept this, also an option you don't have with views or anything else. Same goes for updates and deletes with the corresponding events.

>Where do I do the changing/assignments?? >thisform.?????????????????.SelectCmd

Well, depends on how you work with the CAs. Eg you could create a base CA class which in it's init adds itself to a collection with it's alias as the key. Eg once in main.prg do
goCACollection = Createobject("Collection")

In CA.Init you do goCACollection.add(This, This.Alias) for example, in CA.Destry you do goCACollection.remove(This.Alias)

Then you can find the CA during it's existance by goCACollection.item(Alias).

You can also loop all DE.Objects until you find the CA with the alias you search. Indeed you will seldom need to address the CA, you rather adress the cursor. The only need to adress the CA object is, when you want to call the initial CursorFill(), but that could be part of the CA.Init code, too, so once CAs are up and running, you have that alias to work with.

You trigger Cursorrefresh() via REQUERY(Alias) and you trigger all the insert/update/delete events via Tableupdate(). The service of the CA is to create the cursor and letting you work on it, like it was a DBF, you can recycle code you already have for other cursors, views, or tables.

If you were used to Views you also wouldn't ask how to adress the view, the view cursor is the view, so to say.

>How are these containers created, as I thought
>that a container can only live inside a form
>or other lower objects.

No, every calss can be instanciated without any parent. Try the command window and type o=Createobejct("")

> Here I understand you as saying that your
> containers live outside the form, correct??
No, I said usually the biz objects are added to the form in form.load() via form.addobject(), you could also really add them at design time. But yes, you could also instanciate a biz object without a form, eg for testing from the command window.

The goodness of the container as a baseclass for something nonvisual as a business or data accesss object is.

It can be added to, you can stack objects inside each other, if it makes sense they can see each other as child or parent objects.

The "Custom" base class is rather intended for such nonvisual classes, and it also offers Addobject() to put together custom objects, but you can't do so at design time already.

There are some restrictions of class instanciation, eg you can't add a form to a form via form.addobject(), but you can intanciate any class into the viod, even controls, for which this does not make sense, really.

Bye, Olaf.

 

[stanlyn]

Wonder why I'm getting this error on one of the .prgs and not on the other?
Error "Command contains unrecognized phrase or keyword" occurs on the last listed line "loCursor =............"

This .prg works ok with NO errors...
Local loCursor, ovfp, nhnd, lSuccess
SET STEP ON
Clear
Set Exclusive Off
Set Multilocks On
loCursor = Createobject('CA_OcrText2Entry')


The error occurs in this one, on the last line...
Local loPageCursor, ovfp, nhnd, lSuccess
SET STEP ON
Set Exclusive Off
Set Multilocks On
loPageCursor = Createobject('CA_Page')

I copied and pasted this code in from the programs so there would be no chance for errors...

I've checked and double checked and see nothing wrong. I've renamed both the loPageCursor and CA_Page and it still errors.

Do you see anything?
Stanley

 

[stanlyn]

I get the above one fixed as the problem was in the class creation code...

Thanks, Stanley

 

[Olaf Doschke]

If instanciating (calling Createobject()), that error can also come from the class, though reported for the CreateObject() line. You then may have a problem with the class properties, which evaluate expressions even before class init() runs, so the error is reported in the CreateObject() line, while it comes from the class, Code has not yet stepped into Init() of the class, that's why.

So look into the CA_Page class definition.

PRG classes are maybe not the way to go, I use VCXes. It's a metter of taste, you can get it running, but eg viewclass.prg created by class browser is producing non working code for grids in forms, so I wouldn't rely on that. Self written PRGs are another thing, but I would always prefer VCXes.

Bye, Olaf.

 

[stanlyn]

Ok, I've got both programs working in code that is creating 2 cursors. Now, how do I connect it to the form? I've tried adding the program call in the form's DE.BeforeOpenTables method, but it errors on anything but leaving it blank. It error when closing the DE dialog. The DE is empty and is the form's default. The form's DEClass and class properties are both empty.

I've also tried putting the calls in the top of the load, but thats probably too late as it would not instantiated before the grids become live with an invalid controlsource.

Any ideas?
Stanley

 

[Olaf Doschke]

Load() happens even before Init(), the form is an exception to the rule Init() is first. So that would be ok.

But you create a local variable loCursor, that is released, once you come back from the prg.

Again, I do have container class, which load CAs (or in my case another container loading a DE loading CAs, but doesn't matter).

You have to ensure the CA objects stay in memory, so store them somewhere, eg in Load you can

Thisform.Addproperty("oCACustomers",CreateObject("CA_Cusomters"))
Thisform.Addproperty("oCAOrders",CreateObject("CA_Orders"))

This way you have access to the CAs via these form properties.

Bye, Olaf.

 

[Olaf Doschke]

Or once again the idea of a global collection of CA objects, in code sections, to make it more prominent:

...
goCACollection = Createobject("Collection")
...

Init()
...
goCACollection.add(This, This.Alias)
* or perhaps if working in private datasessions
* goCACollection.add(This, Str(Set('Datasession'))+This.Alias)

Destroy()
...
goCACollection.remove(Set('Datasession')+This.Alias)

Then to address a CA, knowing the Alias and datasession you're in:

oCA = goCACollection.item(Str(Set('Datasession'))+"somalias")
oCA.CursorReffresh() &&...or CursorFill() or AutoOpen()...

I already talked of all that code and it will keep the CAs alive. You just have to make a CA baseclass having the Init() and Destroy() code (besides other code handling the Connection, for example). Then inherit that in all your CAs.

Bye, Olaf.

 

[Olaf Doschke]

goCACollection.remove(Str(Set('Datasession'))+This.Alias) of course. Or goCACollection.remove(This.Alias) if not including the datasession.

Bye, Olaf.

 

[Olaf Doschke]

Forget about that Collection idea for now, I played with it a bit, it will not work this way around, Destroy() never happens, until the item is removed from goCACollection. Was just an idea that works nice for forms, as they are automagically removed from collections when released(). In fact this would be an idea, add a Release() method to the CA baseclass and remove itself from the collection there, after pending changes are written by Tableupdate(...) and the alias is close, for example.

Bye, Olaf.

 

[stanlyn]

Where should I put code that will show me the SelectCmd string with the actual values as part of the string instead of the ?variables? I have tried putting a "MESSAGEBOX(this.SelectCmd ) in the AfterCursorfill() event and it does not show the expanded version...

Where can I place code to show this while the code is running?

The problem I'm having is that after changing the variable values and executing a new cursorfill(), somethimes (not all) the results do not change and they should, so I'd like to know exactly what is being passed to cursorfill().

Is there anything else that I need to set?

EG.
Instead of this,
select county_pagekey form Page where left(Page.county_pagekey,5)=?gcCounty

show me this,
select county_pagekey form Page where left(Page.county_pagekey,5)='KY123'

Thanks,
Stanley

 

[Olaf Doschke]

There is no expansion, you don't have to expand, VFP sends this parameteized query plus the variable to SQL Server and SQL Server does the expansion. There is a big advantage of this:

It prevents sql injection. For example you can't break a string by expanding with a user input including a ', ending a string. Eg you would do "SELECT * FROM users where name = '"+thisform.txtUsername.Value+"'" and then expand with a user input of ['; DROP DATABASE;], what would happen? The expanded query would look:

SELECT * FROM users where name = ''; DROP DATABASE;

And if the user has the right to drop the database, that would drop (delete) the database.

With parameterisation SQL-Server get's the variable passed in it's type, you e have the advantage to pass on datetimes and need not to worry, how this would translate in SQL Server into a string form of a date.

The other big advantage of this is no matter if you query 'KY123', 'KZ987' or anything else, the query is static and if SQL Server is clever enough it reuses the previous compilation of the same SQL.

Bye, Olaf.