CursorAdapter Custom Code

[stanlyn]

Hi again,

The init of my cursoradapter contains...

*** Setup code: DO NOT REMOVE
local llReturn
do case
	case not pemstatus(This, '__VFPSetup', 5)
		This.AddProperty('__VFPSetup', 0)
	case This.__VFPSetup = 1
		This.__VFPSetup = 2
	case This.__VFPSetup = 2
		This.__VFPSetup = 0
		return
endcase
set multilocks on
llReturn = dodefault()
*** End of Setup code: DO NOT REMOVE

*** Setup code: DO NOT REMOVE
if This.__VFPSetup = 1
	This.__VFPSetup = 2
endif
return llReturn
*** End of Setup code: DO NOT REMOVE

Where do I put my custom code when everything here suggests that I don't change anything...

Thanks,
Stanley

 

[Chris Miller]

What do you want to put in the init?

If you have this code, you have used the VFP cursor adapter builder that already was helping you to set all the CA properties and that means you're mainly done with the purpose of the CA. The main behavior is controlled by setting properties, the builder did that. So every initialization is already done by having set properties.

So that's the first point of view on that. Either use the builder or don't. If using the builder, you have such generated code. This code leaves places for your own code, toot:

[highlight #4E9A06][COLOR=#FFFFFF]place for our code[/color][/highlight]
*** Setup code: DO NOT REMOVE
local llReturn
do case
	case not pemstatus(This, '__VFPSetup', 5)
		This.AddProperty('__VFPSetup', 0)
	case This.__VFPSetup = 1
		This.__VFPSetup = 2
	case This.__VFPSetup = 2
		This.__VFPSetup = 0
		return
endcase
set multilocks on
llReturn = dodefault()
*** End of Setup code: DO NOT REMOVE
[highlight #4E9A06][COLOR=#FFFFFF]place for our code[/color][/highlight]
*** Setup code: DO NOT REMOVE
if This.__VFPSetup = 1
	This.__VFPSetup = 2
endif
return llReturn
*** End of Setup code: DO NOT REMOVE

Mainly the second place.

There's also another place to put code, and for that you have to think 3 dimensionally, or OOP. You can put code not in this 2D code plane (not in this Init), but in the Init of a derived class. So you can keep the generated Cursoradapter class as is, and then write your own code in a subclass of the generated CA class, then use your own derived CA class. Just don't forget to call DODEFAULT so the builder Init() logic works.

And that also works the other way around, you could write your own base cursoradpater with default code, for example init code to get a dbc filename from configuration data managed by your own framework or a sqlhandle for remote access you already opened. Then derive further cursoradapters from your base class and then use the builder on them. Notice the builder is available if the baseclass is cursoradapter, it also works on derived classes you first create on your own. The builder is friendly to this usage, it does a DODEFAULT() that calls inherited code.

Besides these possibilities change the builder itself, the builder is contained in Tools/xsource.zip. After unzipping that you could edit the VFPSourcce/Wizards/debuilder/debuilder.pjx. I know this has a lot of further hurdles. So in the first place, use inheritance to add you own code as base code or as subclass code, that's your main OOP vectors.

Chriss

 

[stanlyn]

Hi Chriss,

Either use the builder or don't.

This came from adding a CA to a form's DE where I selected all fields as updatable except for PK. I'm creating a full read/write everything CA for every table the form needs. I'm doing this as its the quickest way to get a form up and running and have the design time functionality for setting source for controls.

I'll then fine tune it in code by changing the SelectCmd and filtering properties and cursorfill() in the form's init or a search or nav button.

So, I'm using the builder to quickly get the property values that I'll modify based on needs at runtime.

What is wrong with the methodology? If I'm missing something here, I'd like to know how to improve, and now is the time to get it right (within reason).

Thanks,
Stanley

 

[Chris Miller]

There's nothing wrong about using the builder, but why do you want to add code to something that is complete after you used the builder? That was the question.

In your other thread I also already answered that using the DE context menu item "Add Cursoradapter" is not at all the best way to get a cursoradapter into a DE, as it means you start each CA from scratch with the native cursoradapter class. It pays to create a base class for our CAs, that, for example, maks use of your framework application class to know which DBC your data is in or which SQL handle you use for your remote databse connection or such base things and then more specific classes. Just the usual way you design a class hierarchy starting from the most common to ever more specialized classes.

One point about that:

I'll then fine tune it in code by changing the SelectCmd

Well, you can, but think of the analogy of a view. You don't change the query of a view, you change parameters of a paramterized query. It's far easier to change the SelectCmd than to alter a view, that's an advantage, of course. But in the first place, different SelectCmd means different CA.

To be able to adapt a WHERE clause, for example, you can use the same technique as with views and use parameters ?viewparam. That's better than changing literal values of a WHERE clause.
You could also separately store a where clause in a user defined property and add or change it just in time, the BeforeCursorFill event can be used for that, for example.

Chriss

 

[stanlyn]

Hi Chriss,

Yes, I just saw and responded to building the DE and CA class outside the form and adding it to the form.

So, I should build custom DEs that will be used in a form, then build custom CAs that will be added to the custom DE for a specific form. All this outside the scope of a form? This would lead to many DEs and many CAs, correct?

different SelectCmd means different CA

Are you sure? As I've read a lot about changing the properties of the "oCA" and re-execute the cursorfill() method. The where clause and SelectCmd are just two of the properties that can be changed along with the field lists for readwrite ability. I remember having to close something first... maybe CloseTables() or cursor.

Thanks,
Stanley

 

[Chris Miller]

Well again think of views.

Just because a CA is custom for a query, does it mean you only use it in one place / on one form? That depends on you. Just like a view can be added to multiple DE, a CA can be added to multiple D Classees. Also, mjultiple forms could use the same DEClass, if they work on the same data.

Everything is combinable and so it makes sense to have that. With the old concept, you're not using this flexibility. Or you do, but only on the level of the single tables.

And even if you define a CA and only use it in one DCLass and only use that in one form, you still have no more work. You need to define the CA anyway, be it directly in a DE of a form, which you then can't reuse elsewhere, or in its own class at first. And a DECLass is mainyl acontainer into which you drag&drop the CAs you want to combine.

So pulling this all together is mainly rag&drop and goes fast. You complain about these composition steps you need to do, but you forget that you would instead rather repeat all the steps necessary to get another CA in another form querying the same or almost the same data.

Chriss

 

[Chris Miller]

Are you sure?

about the concpet of different SelectCmd = different CA.

Read on, before you ask, I alreadyy expandded abot what is possible in that concept. but the moment you change fieldlist, i.e. get a completely different resultset strcture, is surely when this isn't the CA anmore. A CA is like more like a view and a view also doesn't change it's SQL, you have several views for differnt queries, you can varry querries by the concept of parameters, and that exact same concept also works in Cs and does remove the need to change the SelectCmd or even just the WheRE clause.

I also told you about BeforeCursorFill. Please, it seems to me you stop reading when you get at a point that you disagree with. I already gave you more outlook on what you can do and how you can do this. You ask a question that is already answered.

Chriss

 

[Chris Miller]

The essence of "different SelectCmd = different CA" is that:

A CA compares to a view, not to an SQLEXEC comand in which you feed in any query. And the reason for that is that all the properties you set of fields, updatable fields, primary key etc. are specific to the query. So if you would write init code that receives a parameter tcSQL and set SelectCmd to that, your CA would be like a SQLExec and not like a view, it would also often not work, as other properties don't fit with that query.

You can make a CA more flexible than a view, but you shouldn't think of a CA as one SQL Passthrough class to deal with all SQLExecs. That's not getting the concept of CAs.

Chriss

 

[stanlyn]

Chriss,

And even if you define a CA and only use it in one DCLass and only use that in one form, you still have no more work. You need to define the CA anyway, be it directly in a DE of a form, which you then can't reuse elsewhere, or in its own class at first. And a DECLass is mainyl acontainer into which you drag&drop the CAs you want to combine.

Thanks for driving this home and now the reasoning for doing the classes outside the form's DE is clear now.

So pulling this all together is mainly rag&drop and goes fast. You complain about these composition steps you need to do, but you forget that you would instead rather repeat all the steps necessary to get another CA in another form querying the same or almost the same data.

I didn't forget, instead I didn't know any better and ignorant. But, thats why I'm here and thanks for your patience.

the reason for that is that all the properties you set of fields, updatable fields, primary key etc. are specific to the query.

It is for this reason that I should be able to change a generic CA's properties for any query using the table. And yes, you have to get the field lists and schema correct.

I did discover something that supports your statement. I was using the full (all fields) readwrite CA for a lookup to build a list for the combobox. This CA was added to the DE class outside the form and will only be used for the combobox lookup. I needed to use the distinct clause and could not as it was always factoring in the PK which caused the distinct to be ignored. So, best I can see, I would need another ReadOnly CA for lookups that doesn't include the PK field. Everything else worked as expected, except for the PK causing distinct to fail.

So, is it customary to build a second CA as ReadOnly?

Thanks,
Stanley

 

[Chris Miller]

I should be able to change a generic CA's properties for any query using the table.

I understand what you think would be a good CA base class for you. But that's then liited to single table queries. And I think you know and are fine with that. Let me think how you get there by using the builder and then removing the things unnecessary for this abstract base class. I would perhaps not do this as a base class but as some metadata about each table, that's essential: The PK, the updatable field (in general, not per use case, so that's all but PK fields), so actually mainly the documentation of what's the PK of each table.

Can you tell an example of a list of data you gett from a table without PK and DISTINCT. I guess you're just querying a table that's not designed in the princples of data normalization. Any combobox list should be doable as a query SELECT itemdisplayexpression(fields of thetable), PK of thetable FROM thetable without needing distinct.

If you for example want all customers that ordered a specific product, that means modifying this basis by joins used to filter this list. The basis always is SELECT name, id from customers and the filtering by inner joins with orders that have an orderitem of a specific product id, for example:

SELECT customer.name, customer.id from customers 
inner join orders on order.customerid = customer.id
inner join orderitems on orderitems.orderid = order.id and orderitem.productid=X

Uh, and I busted here, as indeed this also needs a distinct: If a customer made multiple orders of the product this focusses on, he would appear multiple times in the list, so it is

SELECT DISTINCT customer.name, customer.id from customers 
inner join orders on order.customerid = customer.id
inner join orderitems on orderitems.orderid = order.id and orderitem.productid=X

And I'm sure there is a way to get this going without DISTINCT, but I wouldn't care to find it. What's more important is that you likelyy would expect to filter for the productID in a where clause, but that's not always the way to filter in SQL. An inner join is a filter here, as it strongly reduces the data to orders having an orderitem with the right productid.

And, also important, the customer.id column here does not disturb the DISTINCT to work. You remove duplicate pairs of the same customer.id and name only, even when some customer names are equal and the customers distinguish by id, of course, and usually also by address, etc.

Chriss

 

[Chris Miller]

Some aspects about architecture strategy is perhaps, to not think of the DE and its CA as your only stage of querying all data your form then has and only has.

The concept of CA that explains the name part "adapter" is to notice you can trigger its cursorfills in the lifetime of the form by REQUERY('cacursoralias'). For example after changing parameters used in the parameterized query of a CA.

You also can, of course, use the data cursors the D generates to do further SQL or also xbase manipulations of the cursors.

And are there readonly CA? Of course, then you don't specify the informations necessary to make the CA an updatable CA. So in short, like there are updatable and non-updatable views, too.
Besides you can just not do TABLEUPDATE and instead discard the buffered changes. And you can always use the data from an updatable CA workarea and query data into further cursors. Or create them with xbase code, again. You can mix everyhting you want to mix on the level of workareas, that's never a problem.

It's just that only the workareas of the updatable CAs are your backlink to the database. The same as with updatable SPT cursors or updatable view cursors.

And another major thought is that you're not stating with potetnially all data by a USE (the xbase paradigm) bit rather start with empty CAs in a form, to first determine what you need as parameter. For example the X for the specific product. And what you need to get the X is not even the full list of all products the user picks from, but a textbox in which the user enters the product name or part of it or a textbox/combo to specify the product type. So the inital data is usually none or just shorter lists like producttypes you can fetch fully in short time.

Chriss

 

[Chris Miller]

One more thing:

the reasoning for doing the classes outside the form's DE is clear now.

Well, and it also means you can put the CAs into a D or instanciate them anywhere else you need them directly, not into a DE. Which also is a simple answer to your thread title "CursorAdapters, Standalone vs Inside DataEnv". Once you have a CA class - and that's what you will have - you don't just have it inside a D, you can drag it into a DE or not, just like you can add a view to a DE or simply USE a view in code.

Notice the creation of a CA instance can already trigger the first cursorfill and so compares to the USE of a view. You can also decide that the CA not automatically goes to the cursor creation, if you want the developer to be able to set CA properties that are used as query parameters of the SelectCmd, for example, and then manually call cursorfill and/or cursorrefresh or do REQUERY(). And all of this makes it similar , once more, to views.





Chriss