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

 

[D-Ward]

Am having another issue with this one;

I don't seem to be able to clear up after myself when I am opening and closing the connections, have tried it a couple of different ways but something is not getting released so I end up getting a 'too many connections' error from the server if the application runs long enough;

Using the pre-built CusorAdaptor above;

    oDataSource = "Driver={MySql ODBC 8.0 Unicode Driver};Server=" + oServer + ";Port=" + oPort + ";Database=" + oDataBase + ";User=" + oUser + ";Password=" + oPassword + ";"
    loHubSend = NewObject("adtDN","DN.vcx")
    loHubSend.sqlRawCommands.DataSource = SQLSTRINGCONNECT(oDataSource)
    loHubSend.OpenTables()
    
    APPEND BLANK IN hubRawCommands
    REPLACE CompID WITH _CompID, SiteID WITH _SiteID, HubAddress WITH 65296, Command WITH oData IN hubRawCommands
    GO TOP IN hubRawCommands
    FOR oX = 1 TO loHubSend.Objects.Count
      IF !ISNULL(loHubSend.Objects(oX).datasource)
        WAIT WINDOW loHubSend.Objects(oX).datasource
        SQLDisconnect(loHubSend.Objects(oX).datasource)
      ENDIF
    NEXT
*   SQLDisconnect(lo.DataSource)
    RELEASE loHubSend

I cycle through the objects to close the CA's, in this instance loHubSend.Objects.Count = 4, each DataSource reports this;
loHubSend.Objects(1).datasource = .NULL.
loHubSend.Objects(2).datasource = 3
loHubSend.Objects(3).datasource = .NULL.
loHubSend.Objects(4).datasource = 2

If I run the function again I get this;
loHubSend.Objects(1).datasource = .NULL.
loHubSend.Objects(2).datasource = 4
loHubSend.Objects(3).datasource = .NULL.
loHubSend.Objects(4).datasource = 3

So I am incrementing by one each time to eventually I run out of connections, where is the one I am missing ?

I have also tried this in pure code just using the one table that I want in this functio;

   lcConnString = "Driver={MySql ODBC 8.0 Unicode Driver};Server=" + oServer + ";Port=" + oPort + ";Database=" + oDatabase + ";User=" + oUser + ";Password=" + oPassword + ";"
   loCursor = CREATEOBJECT('CursorAdapter')
    
   loCursor.Alias                = 'hubRawCommands'
   loCursor.DataSourceType        = 'ODBC'
   loCursor.DataSource            = SQLSTRINGCONNECT(lcConnString)
   loCursor.SelectCmd            = "Select * from rawcommands"

    IF loCursor.cursorfill()
      loCursor.Tables                = "rawcommands"
      loCursor.KeyFieldList            = "ID"
      loCursor.UpdatableFieldList    = "SiteID, HubAddress, Command, CompID"
      loCursor.UpdateNameList        = "ID rawcommands.ID, SiteID rawcommands.SiteID, HubAddress rawcommands.HubAddress, Command rawcommands.Command, CompID rawcommands.CompID"
    
      APPEND BLANK IN hubRawCommands
      REPLACE CompID WITH _CompID, SiteID WITH _SiteID, HubAddress WITH 65296, Command WITH oData IN hubRawCommands
      TABLEUPDATE(.T., .T.)
    ELSE
      AERROR(laErrors)
      MESSAGEBOX(laErrors[2])
    ENDIF     
    USE IN SELECT('hubRawCommands')
    loCursor.CursorDetach
    RELEASE loCursor

Have tried different thing at the bottom to close and clear the connection, but again each time I run this function the DataSource increase by 1 until I run out of connections.

I guess I could open the connections and leave them open, the reason that I open and close them is that I am not using them constantly and that the connections are made so quickly it seemed reasonable to do it this way, what would be considered best practice?

 

[Chris Miller]

The connection handle number is not telling you the number of connections you have open. While it's usually 1 for the first connection, it has not the meaning of a connnection count.

Server do keep connection alive not only when using shared connections, to have a faster way of reestablishing the connections, but that also won't lead to usage of more connections, instead it would reduce usage of connections and recycle them.

Well, to analyze what happens you have to use something serverside, not within VFP, As you use a MySQL Server, there should be a log/monitoring possibility to see number of parallel connections and how/when you have too much in parallel, that's not something I'd even try to miniotor within VFP. Any VFP session can only know about itself.

ASQLHANDLES will give you an array of connection handles you have in your VFP session. Even if you do this in all clients, you won't see whether connections are used shared or exclusive by the connection handle numbers, though.

 

[D-Ward]

Hi Chriss,

I appreciate that the connection handle is not a count, but I am cycling though the loHubSend.Objects.Count which is, looking at each object within the DE and closing the connection if the object has a valid one. The issue is it's reporting there are 4 objects, 2 of them have active connections, so I close them, but one connection is being left behind each time, maybe the DE has a connection as well as the CA's, will check that.

I have just worked out the second code only bit, added a SQLDisconnect(loCursor.DataSource) after the final ENDIF and it now re-uses DataSource 1 each time and the Server is happy indefinitely, so it was keeping each connection open until it eventually run out of connections and errored

My assumption was that when I closed and detached the cursor and then released the CA object that the connection would be closed as the help suggests this is the case, but it doesn't. Even when he application is exited the connections remain open in the VFP environment, the only way to clear the connections was to shut VFP, re-open and run the application again.

Will play around with the DE and CA class object and see if I can work it out.

Darren

 

[Chris Miller]

Have you done SQL Passthrough before using Cursoradapters or have you used remote views?
You usually make a connection and use it for all your data access to the same server, not one per resultset, or cursor.

Even the code the CA builder generatesd only does the SQLStringConnect or SQLConnect, not the SQLDisconnect, that's true. But those are obviously also needed at some point. A connection exists seprately from any workarea, it's not closed when you close a workarea, not even when you release a CA object, unless you program SQLDisconnect into its Destroy event.

But it's not done because usually you open a connection shared for all your SQLExec, or Cursoradapters.

 

[D-Ward]

I agree it makes sense to keep the connection open permanently, but if I explain the use case a little more it may make sense why I need to keep closing it.

A project has a central PC that has a master VFP database, it also has an unknown number RPi's that have a local MYSQL databases. The MYSQL database is being populated from a load of RF IoT Nodes. The central PC performs a sync to all the remote databases to provide a single graphical view of all the Nodes.

I have a table on the PC with a list of RPi's on the project, I scan through the list every 30 minutes syncing all the remote data with the master database on the PC.

SELECT RaspberryPis
SCAN
Open CA Connection to Pi
Sync Data
Close CA Connection to Pi
ENDSCAN

If I want to talk to one of the IoT nodes I connect to it's associated RPi immediately, send a message that it forwards to the Node, the Nodes response is recorded in the database that is picked up the next time the PC performs a sync.

PI = NodesMaster
Open CA connection to PI
SendMessageToNode
Close CA connection to PI

There could be 100 RPi's on a project so need to manage and sync 100 MYSQL databases back to the master database.

 

[Chris Miller]

That's okay, but once you know not even a builder writes an SQLDisconnect and on top of that would know just releasing an object or closing a workarea doesn't close a connection, you know you have to program SQLDISCONNECT() and that solves your problem.

So Cursoradapters are data access objects, but the connection management has to be separately managed by a database manager class that's at least opening and closing the connections which all or some of the CAs need.

I don't have a full understanding of the IoT situation your VFP node is central in, but you can connect to as many server nodes from one central node as you like, the number of connections is limited from a server side only, isn't it? If all our RPIs have a MySQL database server on them, they are tha majority of nodes, but each of them is a server node, not a client node, or not?

Anyway, MySQL offers possibilities to list connections by

show status where `variable_name` = 'Threads_connected';

or

show processlist

And there should be a MySQL server job to constantly monitor that to see when and by which clients it may hit the limit, if there is one.

And from the VFP side, look at ther parameters of SQLSTRINGCONNECT and SQLCONNECT, both have a parameter lSharable or lShared, set them .T. to create shared/sharable connections.