Cursor Adapter

[D-Ward]

Hi All,

I have a project where you have a number of RPi's that are collecting data from wireless nodes, data is going into a MariaDB MySQL database held locally on the RPi. I have a VFP app that has a local database and sync the MySQL data to it for analysis etc.

I started off with SQL pass through, but am now looking at the CursorAdapter, can't believe I didn't start here, seems like a a great tool. Have been through several tutorials and also been reading the recent posts on here on the same subject, and thin I am almost there, but feels a little hybrid and have an issue that I am sure is easy to resolve.

ODBC driver is installed, I started off creating a connection through the Class Browser with everything set up for a single connection and got it working, but as I have several tables on several RPi's so has to be dynamic. So have created a CursorAdapter that has three cursors, each one retrieves all records from the table (not a huge number of records in the tables).



I then have a timer in my application that will sync the data in the background once every ten minutes. It opens the connections, cycles through a list of IP addresses for the RPi's, then closes the connections again at the end.

PUBLIC lo
lo = NewObject("adtDexNet","DexNet.vcx")
oDataSource = "Driver={MySql ODBC 8.0 Unicode Driver}; Server=" + oIPAddress + ";Port=" + oPort + ";Database=dexnet;User= " + oUser + ";Password = " + oPassword + ";"
lo.DataSource = SQLSTRINGCONNECT(oDataSource)
lo.msqlProducts.DataSource = SQLSTRINGCONNECT(oDataSource)
lo.mysqlNodes.DataSource = SQLSTRINGCONNECT(oDataSource)
lo.mysqlRawCommands.DataSource = SQLSTRINGCONNECT(oDataSource)
lo.OpenTables()

SELECT hubNodes && this is one of the tables on the RPi
SCAN
  IF SEEK(hubNodes.NodeID, "coNodes", "NodeID") && coNodes is the table in the VFP DB
    SELECT dnSyncComm  && I have a table with info on what fields should sync
    SCAN
      oMacro = ALLTRIM(dnSyncComm.Condition)
      IF &oMacro
        oMacro = "REPLACE " + ALLTRIM(dnSyncComm.Repl1) + " IN " + IIF(RIGHT(ALLTRIM(dnSyncComm.Command), 1) = "O", "hub", "co") + ALLTRIM(dnSyncComm.Table)
        &oMacro
        APPEND BLANK IN dnHubSync
        oMacro = "REPLACE " + ALLTRIM(dnSyncComm.Repl2) + " IN dnHubSync"
        &oMacro
      ENDIF
    ENDSCAN   
  ELSE
     WAIT WINDOW "Node not found : " + ALLTRIM(STR(hubNodes.NodeID))
  ENDIF
ENDSCAN

** Close and release the MySQL connection
FOR oX = 1 TO lo.Objects.Count
  SQLDisconnect(lo.Objects(oX).datasource)
NEXT
SQLDisconnect(lo.DataSource)
RELEASE lo

It is doing what I need, but feel like it could all be done all in code and do away with the class object. If I decide to add more tables to the app, the class on each PC has to be re-built, or if I only want to use one table on the RPi it seems overkill to have to open all the tables because that is how the class has been set up. It may be that this is the best/easier way of doing things, but would appreciate any input or suggestions.

Darren

 

[Chriss Miller]

Only using the cursoradapter to read data is indeed overkill.

I wonder how you use three cursors with one cursoradapter, you can modify it's alias, but at any one time it only maintains one cursor.
A cursoradapter is like a local or remote view and mainly allows updatable cursors, adapts to legacy codde usage (maybe not 2.6 legacy, but legacy SQL view) like REQUERY on a cursor alias triggers the CursorRefresh() method (and is not allowed in transaction as you may know from reading recent threads.

You can adapt the Select Cmd, especially if you don't use a cursorschema to be used for the result/CA cursor, but even if using a schema, the SelctCmd may change, as long as the result it returns fits the schema. So for examplee you can modify the where clause, add inner joins for the purpose of filtering data but not adding other fields, union sql, etc. So unlike views, where the SQL defines the view and the only felxibility is in view parameters, you have more options. You also don't profit of that when all your SQL is reading all data of small tables, all you need is SELECT * FROM table.

What CAs also are not is a vehicle to query from one DB and then store into anohter, i.e. a migration tool, though an updatable CA can be your "shuttle" into a database, you don't write Unsert/Update SQL towards a target DB (also MySLQ MSSQL) but you do an updatable CA with a query like SELECT * FROM targettable WHERE 1=0 that creates an empty result, then APPEND into that CA cursor and TABLEUPDATE updates the target table. The append may use a cursor you read form the source DB with either another CA or simply SQLExec.

This can be advanced to also UPDATE already in the target DB by loading data to update into the CA that's maintaining the targettable and doing REPLACE/UPDATE, whatever you like (buffered) in the CA cursor to finally commit changes mixed in to new records with a TABLEUPDATE().

The only thing you seem to make use of is the combination of connection and SQL a CA is instead of the separate SQLCONNECT and SQLEXEC. 'A bit of an encapsulation advantage, but not that much, is it?

The full usage of CA is as a Data Access class, sometiomes called business object, if you extend it with user defined methods checking/validating/ensuring business rules and in short query and update or delete or add data with it. You also mostly profit from it for remote database access, not that much for DBFs, though native is one mode of operation, too. Well, and ADO is one more mode only available in Cas and not for remote views unless you have a ODBC to OLEDB bridge driver or something like that. What you do would usually make use of multiple CA classes, though.

Which also means unlike Textbox or other simple classes people tend to use as native class without first making a subclass like a NumericTextbox, DateTextbox, etc. a CA is not intended to be used as a native class but as a CustomerCA, OrderCA, etc. or in as many classes as you'd usually define views for your database work, then use those user defined CAs, not the native CA.

Before you can use the native CA you have to provide the information about DataSource, Table, Keyfields, UpdatabelFields, etc. - that alone should tell you you better only once set up all those properties and store that into a class specific to a table.

 

[Chriss Miller]

If you wonder how to build a class family of CAs that's sharing a connection, for example:

Well, subclass the native CA as NorthwindCA, for example, and just code the connection part, no specialisation on a table yet. In Init you could check whether a shared connection exists already and only if not establish a new connection stored somewhere central (which might be one specific CA instance you ensure exists first or a property or subobject (like dbmanager class) of goApp or _screen. Or a simple alternative: Outsource the task to make the database connection to a dbmanager class and call that to give you the connection handle needed for the Noprthwind DB. The job to keep a shared connection handle then is on that dbmanager. Many application frameworks work that way and have managers like dbmanager, langaugemanager (internationalization), formmanager, etc.

Then you do CAs for each view you want with SQL specific to one table, usually, but maybe also more complex SQL to query something across multiple tables, which can still also be updatable (also with views, by the way).

The single table CAs can inherit from the NorthwindCA the connection to the NonrthwindDB (init and some properties related to the database) and fill in the blanks you have to fill for a single table of that DB, and what you have then is one central class caring for the connection and individual view CA classes, not every view will make it's own connection, then., for example.

And that's just one way of centralizing tasks like the db connection in a class family. Your ideas are the limit, if your data is international in itself a user define property of langauge might act as parameter for which fields or tables to use of the internationalized database. Add a language_assign method and it's triggered when the language changes. It's then possible to cascade a language change into the individual CAs managing their alias/workarea to care for fields that need to change their value to the correpsonding language. Not an easy task, but doable in principle.

You have classes and instances, with methods and events not just functions, variables, parameters. OOP instead of procedural work with data.
When you use a CA to maintain one specifc alias, all its properties could be considered (user defined) properties of that workarea, though CURSORGETPROP() will not look into CA properties, and CURSORSETPROP() will also not set CA properties, you could with your own classes and use it to extend workareas even with events that correspopnd to CA property assignments.

 

[D-Ward]

Hi Chris,

I am using the cursoradapter to read and write data, I sync in both directions.

The remote MySQL database and the local VFP database are duplicates, same tables and fields. All the data fields for example "Data1, Data2, Data3 ....' have corresponding time stamp fields (Data1DateTime, Data2DateTime, Data3DateTime ...' some fields in a record could be newer in MySQL and some could be newer in VFP.

The table dnSyncComm that I scan through holds the logic for comparing each field, if it is .T. then it uses an indicator to push or pull the update;

MySQL table = "hubNodes"
VFP table = coNodes

oMacro = "REPLACE " + ALLTRIM(dnSyncComm.Repl1) + " IN " + IIF(RIGHT(ALLTRIM(dnSyncComm.Command), 1) = "O", "hub", "co") + ALLTRIM(dnSyncComm.Table)
^^ Fields to repalce ^^ ^^ "hub" or "co" ^^ ^^ table = Nodes ^^

If I am updating the VFP table the replace does this, if I am updating the MySQL table it is updated at the end of the SCAN loop as I have buffering set to Record and not Table in the CA,

The CA creates 3 cursors and you can use them independently, you just swap back for forth using the SELECT command and update them, so the CA supports multiple cursors at the same time.
What I have just found, it only creates cursors that you assign DataSources to without throwing an error, which makes sense I guess.
So if I;
lo.msqlProducts.DataSource = SQLSTRINGCONNECT(oDataSource)
lo.mysqlNodes.DataSource = SQLSTRINGCONNECT(oDataSource)
lo.mysqlRawCommands.DataSource = SQLSTRINGCONNECT(oDataSource)
lpenTables()

I get 3 cursors returned to play with and update, but if I just do 1;

lo.mysqlNodes.DataSource = SQLSTRINGCONNECT(oDataSource)
lpenTables()

It only returns one cursor, so although they are all set in the class you don't have to fetch them if you don't want them.

I think I may be answering my own questions as I go along, apologies. Think I will continue the same way, add all the tables to the class as separate cursors and just calls the ones that I need to deal with at the time.

Darren

 

[Chriss Miller]

By the name cursoradapter a cursoradapter maintains one cursor only. If you're using CursorFill and CursorRefresh you fill or refresh (requery) the alias given in the Alaius property of the CA. Not any other.

If you code methods that featch data form somewhere into several aliases/workareas/cursors, then that's not using the CA itself. That's just your code withint the CA doing whatever it does, inclkuding usage of workareas, but only one of them ius maintained by the CA. If you change Alias, you also change which workarea is maintained, but you could cause strange3 situations, therefore before changing alias you would use CursorDetach. Likewise you can use CursorAttach to attach the CA to another alias.

If you don't use these aspects, you really don't use the CA, you could also use the custom class as basis of what you're doing within your code.

Edit1: detail question about your code excerpts:
is lo a CA? or are it's three properties msqlProducts, mysqlNodes,. mysqlRawCommands three CA instances?

Edit2: Okay, forget the question, your screenshots are telling enough, you have 3 cursoradapters in a dataenvironment. Why are you saying you use one CA for 3 cursors, then. That's misleading and not what you do.

 

[D-Ward]

Hi Chris,

Not meaning to misled, merely showing the limitation of my knowledge. My assumption on getting to this point was that I had created a CA with 3 child cursors, but following what you say, which makes sense, this is not what I have, it is in fact a data environment with 3 CA's each controlling a single cursor individually, apologies.

I have this working, but am sure I can do more to improve the workflow, am re-reading your replies and am starting to understand the architecture of the CA and the class hierarchy a little better (all a learning curve).

As always, I appreciate the detail and time spent on your replies.

Darren

 

[Chriss Miller]

It's okay, I could have looked more closely at your screenshot to begin with. I could ask now if you're actually more fond of the CA class or the dataenvironment class, but that's also not a main point.

It can also be see4n why you think of your CAs as cursors, as the tab of the dataenvironment builder is talking of cursors. As the label says in it that tab is merely for adding cursoradapters to the DE, both already existing ones or CAs you create from scratch in that moment, the DE builder even has abuilder button starting the CA builder, if you want. So the DE builder acts as a roof builder in this way.

What I thnk is actually missing in the DE builder is adding usual tables and views into the DE aws can be simply added into a form DE. The dialog to pick DBFs or views actually automatically pops up when you modify a form DE and its still empty. Look into any form DE and you'll also see that tables and views you have in there are shown as graphical objects which are called cursors, That's actually a cursor object, not a cursoradapter object that is a class instance only existing in a DE. Everything, no matter if table, view or cursoradapter will be a cursor in a DE, a cursor1 to cursorX, And the properties of these objects might overlap with properties of the table view or CA that they represent. i.e. when you add a table (to a form DE) the cursor object alias name will default to the table name, just like the USE command of a table uses the table name as alias name of the workarea it uses.

So the structure of a DE is having a collection of cursor objects in them, which by natur of the builder can only be CAs, but when you save a form DE as a DE class (which is an option while modyfing he form DE) you also get the DE cursor objects that are for tables and views. I just see another flaw of that now at least for me on my notebook: The DE class is not resizable in its width and only has a width of a few pixels, so I don't see the DE. The property window also shows no width and height property to use for resizing the DE "canvas" with all the cursors in it.

 

[D-Ward]

Hi Chriss,

In terms of fondness, it would be like picking a favorite child . The DE is so powerful, but the combination of working with the CA (which I had not used before) opened up new possibilities in terms of interacting with external data sources, previously had done all the work through SQL pass through, which has it's place, but when you 'own' both datasets the CA is much quicker and easier to control and debug.

I have been away on vacation, hence the delay in my reply, but have been working on the DE/CA class this week, and think I am there now. I was having some issues with connection counts, scanning through all the RPi's in in the project to sync multiple MySQL databases back to the single VFP one. Changed some of the code that is auto generated by the class builder and all seems well.

Thank you again for the time you invested in assisting me, is very much appreciated. I am moving on the next part, so will be back .......

Darren