Using the CA9Builder Classes

[stanlyn]

Hi,

Someone pointed me to the ca9builder wizard and I was able to create a class for each table. I was also able to use them by adding them to a form's DE, and they close when the form closes. They should do that as my forms use private datasessions and cursors and DEs are scoped to the active datasession.

1. How do I use them in a .prg? I know I can copy its code, but there must be a better way of somehow instantiating it?

2. When used in a .prg, do I need to also have a DE?

3. How would I create a cursor that lives as long as the app lives? I would suspect maybe creating a global object with createobject() or newobject() inside the main.prg... Is this correct?

4. Would a form with a private datasession and a empty DE have access to the global cursor?

A code snippet example would be most helpful.

Thanks,
Stanley

 

[Chris Miller]

This asks a lot.

Let me say you should perhaps first know CAs can also be created outside of a DE, that should reduce your questions dratically.

The usual way to use a CA class is to put it inside a DE class (independent from a form) which can be added to a form.
Why so keen on a PRG solution? Bad experience with file defects of VCX or SCX? This is just a matter of your development computer, when it ends up in a build of an EXE there is no chance class libraries or forms "erode" over their lifetime by use, the EXE doesn't change.

Chriss

 

[stanlyn]

Thanks for answering 2

Why so keen on a PRG solution?

Not really keen, but certainly need to know how to talk to the database in a program or a menu at anywhere and anytime, much the same way I do with regular vfp data access like:

if not used('table')
   use 'table' in 0
endif

select 'table'
* read or write data

This asks a lot.

I don't see why, but ok, lets deal with one access layer at a time, like using the ca class within code. What does that look like? How is it implemented so to use the already built ca class and only change the needed parts.

Isn't the whole idea behind the CA9Builder class is to create a generic class of each table in your database? The author had instructions on using them with a form, but nothing else, like using them in programs, menus, or ad-hoc sort of way and that is why I started this thread.

Am I assuming correctly for #3 above?

And, is the answer to #4 above a yes or no?

Off to test some of these ideas,

Thanks,
Stanley

 

[TamarGranor]

Anything created in datasession #1 (which is the default data session) cannot be seen in other data sessions. However, you can, in code, switch data sessions with SET DATASESSION. But that still doesn't allow you to work in one data session with a table from another.

Tamar

 

[stanlyn]

Hi Tamar,

Just to clarify... Forget about sql anything for a moment and lets talk native vfp.

If I were to have 10 forms that references a zipcode table and each form uses a private datasession, then I would have 10 different datasessions and no way to access a table from a different datasession. That doesn't make much sense as all 10 datasessions points to the same underlying tables. If you update a zipcode record while in datasession #3, that updated record will appear in all the other datasessions after a refresh.

I have all the VFP books by Hentzenwerke, can you suggest the ones to study for the cursor adapter implementation as I've been working with the Client Server one and its discussions are not complete leaving lots of questions.

1. How is the generated ca class instantiated and accessed in code, like in my main.prg and lives until the app is closed?

2. Having 10 forms with private data sessions creates 10 different datasessions? T or F

3. Can I access the ca instantiated in the main program from a form with a private data session by using the "set datasession to _screen.ds" in the form's load or init? Setting _screen.ds would be done as soon as the datasession was created with "_screen.ds = set('datasession')" and would also be global.

Thanks,
Stanley

 

[Chris Miller]

The CA creates a cuersor, it's called cursorddapter for a reason. After that you can work on the cursor. Tht's the basic idea in very short.
So actually you could replace a USE table with CREATEOBJECT('tableca').

But again, the idea is as it was suggested to you with views already: you don't select all data from a table, that doesn't work well with large tables.

So USE table, then SET FILTER TO or LOCATE is a bad programming style with any remote database access, be it remote views or CA. Wtite the SQL that gets you tht record you want, for example. Or queries that fetch a page of data or the last week/month/quarter, never all data.

The only other difficulty is, that you want to keep the cursoradapter object alive, so don't just create it as a local variable. The DE class (separate from forms) is a good 'container' for this so you only need to care about this one DE instance reference or add it to a form, then the CA objects stay available.


Chriss

 

[Chris Miller]

Another topic will be default values.

You define MSSQL tables with default values. In part simply as you know it from VFP, you can have constants or call a function, but this time MSQL (T-SQL) functions, not VFP.
Then, when you use a CA to have the VFP work area of the MSSQL table you can APPEND BLANK and that will mean a final INSERT into the MSSQL data, but you then can't have the default values from MSSQL, those are only the default values of records generated inside SQL Server with an insert skipping fields. So the routine is to INSERT first, then fech and edit and commit.

And there is a way to say you want all default values like an APPEND BLANK does in T-SQL:
INSERT INTO tablename VALUES (default, default,...) with as many default keywords as the table has fields.

If you like to keep creating records on the VFP side, then the defaults will only play a role for fields not in the updatable fields list. APPEND BLANK gives you defaults as they are by the datatype alone.

Let's say you want a datetime of when the record was added, then you either give this datetime field an GETDATE() default value (that's a T-SQL function) and don't add it to the updatable fields, or you replace the VFP cursor field with DATETIME() (VFP function) and then insert that by TABLEUPDATE() and the default valkue defined on the server table plays no role for this record. It's good to define defaults in MSSQL nevertheless, because you will surely also administer and work on the data manually.


Chriss

 

[TamarGranor]

What I was trying to say about data sessions is that if a form opens some tables in a private data session, but then in the form, you SET DATASESSION TO some other data session, you won't have access to the tables that belong to the form's data session.

If you're planning to use CAs in the default data session to create cursors for all your tables, and then access those cursors in all your forms, then using private data sessions would make no sense. However, for client-server, opening and populating all tables at the beginning of your application is a bad, bad idea (as Chris has been telling you). It defeats a major point of client-server, which is to only bring the data you need right now to the local machine.

Yes, 10 forms with private data sessions means 10 data sessions. (Actually 11, because the default data session always exists.)

Tamar

 

[stanlyn]

The only other difficulty is, that you want to keep the cursoradapter object alive, so don't just create it as a local variable. The DE class (separate from forms) is a good 'container' for this so you only need to care about this one DE instance reference or add it to a form, then the CA objects stay available.

Is it better to have a DE class for each individual cursor, OR only access the cursor class on its own, much like we do with a "use" statement which deals with tables individually, instead of by group.

It make sense to have a DE when using a group of CAs' or when multiple CAs' have relations between them.

When building a new app, how do you tackle it?
Do you pre-build anything like CAs' or DEs'?
Do you create single cursor DEs?
Do you also build remote views for quick maintenance of SQL data?

I have been using the where clause for some time now to limit the result set.

Concerning defaults, thanks for the details on how it works. So for clarity, if I want the sql defaults, then best to insert a new record using spt insert suppling only enough data to satisfy the "not null" fields, then fetch that record and modify, then tableupdate(). The main point is to have sql insert the record with its default values first, before updating it from the vfp cursor? Yes, No

Or best yet is to change the vfp cursor record the way you want and tableupdate(), foregoing all the sql defaults? And use the sql defaults only when working within SSMS or other UI to manage the sql data?

How do you deal with the CA's property sheet's 255 char limit? The CA9Builder claims to have fixed this but doesn't go into any details. See new thread about this...

Thanks,
Stanley

 

[Chris Miller]

One DE for one cursor? No, bad idea. If you want to query tables individually you donÄt need a DE class at all, CAs don't need any container class.
But when you're used to DE this concept still works with the DE class, and you can set one of them to be used by a form, so that should put the idea to put one CA into a DE to rest.

I suggest you read and try a bit before asking questions, s some of them are really answering themselves or become irrelevant. For example the idea of one CA per DE. The DE always is about a group of tables/views and now also CA's you want available. With CAs - like with views - you also have the nodataonload option, if not by setting the query up with WHERE 1=0. So the DE doesn't need to know in advance which data to fetch. Having the workareas you can then act on them and use CA methods like cursorfill when you have set query parameters (by user input, for examople).

Well, and the adapter nature shows itself when you query no data at first, then set parameters and REQUERY(), which triggers the cursorfill or cursorrefresh methods of the CA responsible for that cursor.


Chriss

 

[tomk3]

Hello,


sorry, this does not answer your question but may be of interest because I think it is easier to handle.

After some testing I switched (back) from CA to remoteviews.
RV can be built easily with the designer , including filter (where clause).

You can use and index them like tables and open them with a connnection string (which can come from an .ini, for example to switch backend)
A tableupdate and requery is basically all which differs from dbf.

We do not use private datasessions, just default one and requery in load of form where necessary or use SPT for lookups.

Regards
tom

I know its old technique but for us it works fine.

 

[Mike Lewis]

Tom, I tend to agree with you about remote views - up to a point. Remote views still have place. I recently had to create a table-upsizing tool, and I found this easier to do with remote views. It's also easy to create forms using drag-and-drop from a remote view. And for less-experienced developers, they have the big advantage of behaving almost exactly like DBFs.

I'm not saying remove viewers are better than cursor adapters. On the contrary, cursor adapters have several advantages. But remote views are not an "old technique" that should be completely abandoned.

One small point: you mentioned that you can index a remote view. That's true, but you need to be aware that the index only exists for the life of the view. Once you close the view, the index disappears (which is obvious really, given that the view doesn't actually contain any persistent data).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

What I was trying to say about data sessions is that if a form opens some tables in a private data session, but then in the form, you SET DATASESSION TO some other data session, you won't have access to the tables that belong to the form's data session.

Why can't I save the datasession to a var before switching to a datasession, then reset it back when done?

I really want to use private datasessions in forms.

How does a DE relate to a datasession?

How does a CA relate to a datasession?

How is a datasession created for non-form use?

Does making a datasession private or not inside or outside a form the result of it's scope (local, private or public)?

If you're planning to use CAs in the default data session to create cursors for all your tables, and then access those cursors in all your forms, then using private data sessions would make no sense.

I was thinking on how to make it as easy to use as "use native tables" where they are all available all the time with NO data loaded. Just set the cBaseSelectCmd, cOrderClause and cFilterClause variables and requery(), whether in a form or not. Remember how we can from anywhere and at any time, we can check if a table is used and if not, we "use table in 0" and start acting on it. And I know I cannot "USE" a table, its just that paridyme I reaching for. I am trying to get as close to this as possible.

However, for client-server, opening and populating all tables at the beginning of your application is a bad, bad idea (as Chris has been telling you). It defeats a major point of client-server, which is to only bring the data you need right now to the local machine.

I understood Chris as saying to not populate them. I did not understand him saying that I cannot open them. I agree, that populating all the tables would be bad and I'm not doing that by using the lNoDataOnLoad variable. I was planning on having them all setup and ready to invoke when needed with NO data loaded. Is there a problem with having 10-20 table queued up with NO data loaded. (Queued up meaning ready to invoke and be part of a datsession)

Would "GETCURSORADAPTER( )" help in any way by somehow having the ca_* class available all the time and switching to it with this command? How does this command for sql compare to the native "use" command? I see very little help about how to use it.

Thanks,
Stanley

 

[stanlyn]

Thanks Chris,

Your last post answered lots questions. I see DEs as a management group for multiple CAs, and you have confirmed it.

You also confirmed what Naomi from a different form suggests that she uses CAs by themselves with no DE.

I suggest you read and try a bit before asking questions,

I do and when something doesn't work as expected and I really don't know what to expect, or what is possible, it is good to ask people that know to see what is possible to develop a mindset. You know, "should I do it this way or should I do it that way"? With all the different possible combinations for access, error handling, plumbing, compatibilities, and lots of pros and cons for each, its best to ask experts like you first.

There seems to be no uniform way of doing something with the sql access stuff, and of-course only really basic scenarios are covered in the books.

I'm also asking about their universal use, in and out of forms, (programs, menus and forms) and see nothing in the books and forums tackling that arrangement.

Thanks,
Stanley

 

[stanlyn]

Hi Tom and Mike,

Thanks for voting for default datasessions (non-private) as most I have seen suggests private, which begs a question.
Are you able to go about your data access similar to how native is done? I have had issues with default sessions where data from one form bleeds into another form.

I started this adventure with remote views and after two weeks trying to get past an issue that never got resolved and reading that CAs are the thing to learn. I also saw lots of developers speaking of limitations of remote views such as "must be part of a database" and others. I switched and started playing with CAs and do see the extra power and flexibility. However, quick and dirty data access is missing. Then I found the CA9Builder that quickly built about 40 classes in a moment pretty much ready to use without a DE, and still no easy access of data. The documentation was written for an existing CA user and not for someone just starting out.

I also mentioned above that I see where the remote views would be helpful when quick assess is needed or you need to build a data entry form on a sql table.

I liked the remote view as I can right click the view and select browse and instantly be dealing with data. And somewhat cumbersome if using outside of a form. I'm sure someone will says it's a piece of cake, and they would be right, if I only knew what they know about it. I'm not there yet...

Curious, is most people here self-taught, as in hammering out a way that works for them, then stays with it?

Thanks,
Stanley

 

[tomk3]

Hello again,

as always : it depends.

We include the database , icons, dlls,... in a table which ist compiled (included) into exe. On startup these are extracted to users \temp and opened there (no 1709 error anymore) with NODATA and CONNSTRING clause (dsnless) and indices are built. RVs stay open, requery when necessary, SPT for add. cursors.

And many years ago we made a tool which takes a dir of dbs and creates a sqldatabase incl. indices, a vfp dbc with basic rvs and transfers data into sql.
Because RVs are very similar to dbf , code migration was not su much work.
Yes there is some lazyness and "never touch a running system"

Our .NET guys like CA because its more similar to c# datahandling when they have to understand VFP code (rare).

I never needed the advantages of CA, but I like the easines of rv, like use - browse -, so thats why I stay with RV and default datasession.
- we access XML via chilkat oder MSxxx classes or use MSSQL bulk import.
- we only use MS SQL Server and connection string for opening RVs, connection string comes from a crypted licence file.
I never needed to switch data source on runtime, but even that could be done by applying a different conn.string and reopening.
- Type conversion is done in propertys of RV or via cast

And : It is VFP, so there are always different ways to handle it.

Regards
tom

 

[Steve Meyerson]

And : It is VFP, so there are always different ways to handle it.

FWIW,

This is certainly not a recommendation to handle your or anyone else's situation, just to share one of these "different" approaches to my probably unique situation.

Without using CA, DE, .vcx, multiple data sessions, I use the KISS principal and the lightening data handling speed of the native VFP9 in my exe on a Win10 machine with enough (4G?) memory.

In this particular case, all the data resides in a web-based MySQL database. I have a single user with about 30,000 clients (or potential clients) which I list on the side of the main screen with about 50 at a time visible, along with several combo boxes containing data from the database.

Instead of downloading a minimum of data each time the user asks for it, I download it ALL from the web host when the program starts. After that, I update every edit (change) in a record immediately to the web database. It is quick and not even noticeable to the user.

The main problem in this case is It takes 15-30 seconds to download the entire database, which is an eternity in most cases. But my client starts the program only once in the day (or even several days) and does not close it (he minimizes it often). From there, performance of everything is instant. He's happy with that.

Of course if more users are added, a means of viewing updates immediately could be a problem which would require reprogramming.

Steve

 

[TamarGranor]

<< Why can't I save the datasession to a var before switching to a datasession, then reset it back when done? >>

You can, but what's the point of doing so? The whole idea of private data sessions is so that you can work with the same tables in different forms at the same time without stepping on each other. If you want to work with tables (cursors) that are open in the default data session, why give your forms a private data session?

<< I really want to use private datasessions in forms. >>

Yes, that's a good idea, but that means your forms don't work with tables open in the default data session.

<<How does a DE relate to a datasession?>>

If a form has tables in the DE, they are opened in the form's data session.

I haven't worked with cursoradapters enough (or recently enough) to answer your other questions. Not doing a lot of client/server at the moment, but when I do, I tend to use SQL pass-through, because it's where I'm comfortable. (Had CAs been available from the beginning, I might have landed there, but by the time they came along, I was comfortable with SPT.)

Tamar

<< How does a CA relate to a datasession? >>

If a CA is instantiated and a cursor populated in a form, the cursor exists in the form's data session.

<< How is a datasession created for non-form use? >>

You don't usually do this, but you can do it by creating a DE subclass and instantiating it.

<< Does making a datasession private or not inside or outside a form the result of it's scope (local, private or public)? >>

We don't usually use the term "scope" to refer to data sessions. Variables have scope. Data sessions are either private or the default data session (#1) that's always there in VFP.

<<I was thinking on how to make it as easy to use as "use native tables" where they are all available all the time with NO data loaded. Just set the cBaseSelectCmd, cOrderClause and cFilterClause variables and requery(), whether in a form or not. Remember how we can from anywhere and at any time, we can check if a table is used and if not, we "use table in 0" and start acting on it. And I know I cannot "USE" a table, its just that paridyme I reaching for. I am trying to get as close to this as possible. >>

Here's the thing. The same restrictions apply to native tables. They're always open in a particular data session, whether that's the default data session (#1) or a private data session belonging to a form. If you're using private data sessions, in order for them to work with a particular table, it has to be open in that data session. They're not all available all the time.

 

[Chris Miller]

Overall, your questions point to the idea to "open all tables" at application start and then be through with it, to be able to reuse all your code.

Tamar explained a lot, so that idea is not very good, when you want forms with private datasessions you also want to use them, ie open up tables, views, create CAs in them, not beforehand in the default datasession. If you would switch to datasession 1, you could do without the private datasessions as you don't use them. You can't bind some controls to workareas of the default datasession and others to workareas of the forms private datasession, so this idea of switching sessions will not give you a solution to use both of them at the same time.

And now you perhaps get, why you should never simply SELECT * FROM table just to have the equivalent of a USE. A USE doesn't fetch data, it merely opens up a handle on a DBF and you can access all data. A CA or view with SELECT * FROM table does fetch all data, with a large table that takes long. You don't do that. You only fetch data as necessary or even start with CAs not fetching data on loadt, just creating an empty cursor so the data binding works, as the cursor and its fields exist with the correct data types.

One way to be able to recycle your LOCATE code, for example, is to at least limit the amount of data you fetch by a dimension, ie a month/quarter, a department, data associated with the logged in user, a project, whatever is most relevant. So at least think in the direction what data to fetch so a LOCATE will find its row within that part of the data. But never fetch all, be selective with your data loading, then it also won't matter to redo this with each form start or with each user interaction requesting data by a filter they can set within the form.

And consider this: You can change SelectCmd at runtime, as long as the result has the same structure. So that's one more degree of freedom of CAs against views, you don't just have parameters to change the fetched rows, you can do much more. You may also use secondary non-updatable CAs to fetch data and then APPEND that to a main CA for the commit, you then just need to set field states as if those appended rows where fetched by this CA.

Chriss