SELECT Selects Old Data From Somewhere

[Steve Meyerson]

I try to work with cursors as much as possible.

The AllClients cursor (downloaded from MySql) contains the main data (about 28,000 records, 60 fields)

The user starts with the Clients cursor created with

SELECT * FROM AllClients INTO CURSOR Clients READWRITE

When the user changes a record in Clients, that same change is made (by the program) in the AllClients cursor.

If the user then selects a criteria, a new Clients cursor is created. Example:

SELECT * FROM AllClients INTO CURSOR Clients WHERE State='NC'

The problem is the new Clients cursor contains the old data (before the edit).

To fix this I had to copy the downloaded AllClients cursor to a dbf instead of leaving it as a cursor.

I tried the FLUSH command (didn't work). I believe it might have something to do with buffering (CURSORSETPROP?), but I don't know what parameter(s) to use.

Is there any way I could keep AllClients a cursor instead of a dbf?

Thanks for any ideas.

Steve

 

[Chris Miller]

Well, either query AllClients WITH BUFFERING=.T. or don't buffer AllClients.

Overall it's no good idea to fetch all data into a cursor and just use MYSQL as a data repository. Use it as your active database backend and create updatable cursors by any available means, ie remote views, SQLEXEC including some CURSORSETPROP making the cursor updatable (towards the MySQL table) or by Cursoradapter.

You'll still have the Clients cursor, but no AllClients cursor, the "AllClients cursor" should be the MySQL Clients table. Even when you think this is too slow, it's a reason to only fetch clients of a state, for example, as in your sample query.

Chriss

 

[Mike Lewis]

I agree that this is probably a buffering issue. There is no reason to buffer the cursor. Buffering is off by default, and there is no reason to switch it on.

Just to amplify Chris's other point: Part of the philosophy of a client/server system is that as much as possible of the sorting and filtering is done on the server, not the client. You should get rid of AllClients. At the point where you want clients in NC, just ask for those clients from the server. In other words, send this command to the server: [tt]SELECT * FROM <table on the server> WHERE State = NC[/tt].

Going further, I would guess that it is very unlikely that you would need all 60 fields for your North Carolinian clients. So instead of [tt]SELECT *[/tt], do [tt]SELECT[/tt] followed by a list of the fields that you do want.

The result of all this is that far less data will travel across the network, with all the performance benefits that that will bring.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Steve, another option you could consider is a parameterised remote view. You can set this up interactively in VFP's View Designer, or programmatically using the [tt]CREATE SQL VIEW[/tt] command. Either way, set the filter (the WHERE clause) to something like [tt]State = ?parmState[/tt], where parmState is an ordinary variable in your code.

When you need to access the view, open with [tt]USE ... NODATA[/tt]. Then store the value of the relevant state (NC in this example) in the parameter ([tt]parmState[/tt] in this example). Then call the [tt]REQUERY()[/tt] function.

The effect of all this will be to populate the view with the most up-to-date records for the specified state. When you want to move on to a different state, update the [tt]parmState[/tt] variable and call [tt]REQUERY()[/tt] again.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Meyerson]

Thanks Mike & Chris. Many good ideas. Actually, my screen displays most of the 60 fields for each record as the user selects (or scrolls) from the list of (~28,000) clients. When the user changes the value of a field, I update the corresponding records in the AllClients cursor (now a dbf), the Clients cursor and the MySQL database. It's amazingly fast. I don't know if there's a way to do this without the "intermediate" cursor (or view).

With regard to the display, I'm afraid pulling all fields from MySQL into a cursor (or "view") for each record as the user scrolls through the list might slow the display somewhat.

BTW the AllClients cursor is SELECTed from the web host (MySQL database). Its 28,000 records download in 10 seconds on my desktop and 30 seconds on my client's laptop. So I limit this action to startup only.

SET FILTER problem: The control source for the list is the Clients cursor which is SELECTed from AllClients.dbf. I formed a SET FILTER clause from user's selections to use on the Clients cursor, but it gave some unexpected results. So instead, I overwrite the Clients cursor with:

SELECT * FROM AllClients INTO CURSOR Clients WHERE ...

It works ok, but only after I made AllClients a dbf instead of a cursor. I noticed NO performance hit.

Steve

 

[Chris Miller]

Well,

you're missing one point. Your logic of updating both the local AllClients DBF and MySQL works fine for a single workstation. But in a multi-user system done this way, any other workstation only sees those changes with a restart. Or in short: Every workstation only sees the MySQL data as it was at the program's start, plus its own changes.

That also means when workstation 1 change client A's first name and saves it, workstation 2has the old first name and changes the last name and saves that, finally the first name is back to what it was and only the last name is changed. Disregarding these problems because of their low probability means what I initially said: Your system isn't really client/server. You're only using it as a data repository you check-out at the start and check-in at the end. I understand you do make every change to AllClients.dbf in the MySQL Clients table, too, whenever they happen. But only users starting afterwards will load these changes. Earlier started Workstations won't see that change until they restart, so effectively even though you do live-updates of the MySQL backend, you don't transfer that data elsewhere as immediately as you change it.

Besides, you even can have that problem when working with a DBF backend and buffered changes, so that's not just a topic of client/server with a remote backend vs client/server with DBFs. But indeed many of the changes of other workstations arrive everywhere else when the users of other workstations scroll to the changed record, as VFP reads from the DBF not just once at query time, but has a pointer (current record) to the server-side DBF file that's a live view on the data.

Chriss

 

[Koen Piller]

Steve,

Its 28,000 records download in 10 seconds on my desktop and 30 seconds on my client's laptop. So I limit this action to startup only.

10 secs??? that is outrageous slow, not to speak about the 30 secs you mention for clients laptop.
Please show us the code you use to make your cursor.

Stay healthy,
Koen

 

[Mike Lewis]

Steve, if you would consider some of the suggestions in this thread (such as my suggestion for using a remote view), you wouldn't need to restrict yourself to downloading only on startup. As Chris has said, doing so means that your data will quickly get out of date. And as Koen suggested, 10 - 30 seconds is a lot of overhead, even if it is only done once at startup.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Meyerson]

Wow. Quite a few issues.

Chris, you are absolutely correct that changes made to the MySQL database are not reflected on other workstations. I originally designed it for a friend who was using a limited program written in PHP by a programmer no longer around. Instead of learning PHP, I wrote it in VFP with much more capability.

When he asked me if he could have an associate(s?) run it on their computer, I took the easy road and added an update button which would reload the data from the SQL database.

To make the update seen by other workstations, Mike's "remote view" suggestion sounds like the way to go if/when he gets associates. I'm not sure how that would work. I (mistakenly?) thought a remote view is really only cursor created from a SELECT statement and question how it would be seen automatically by another computer. I assume I'm incorrect on this. Can you explain?

Koen, about the slow download: Using ODBC 5.3. About 10 small tables & 1 large table are downloaded. Here's the code used to download the large table with 28,000 records so far:

** Download Web Clients TO AllClientsCursor (includes both web & Va newbies)
cYr = ALLTRIM(.InclAfterYear)  && User-determmined
cmd = 'SELECT * FROM Clients' + IIF(EMPTY(m.cYr), '', ;
  " WHERE Entered>='"+m.cYr+"-01-01'" + IIF(.InclUnpaids, ' OR Pmt1Amt=0', ''))
=SQLPREPARE(.Fh,"&cmd",'AllClientsCursor')  && Overwrite if called from Update
IF SQLEXEC(.Fh) = -1
  =MESSAGEBOX('Unable to download Clients data.',0,'DOWNLOAD CLIENT DATA')
  RETURN .F.
ENDIF
=SQLDISCONNECT(0)

Suggestions?

Thanks, everyone!

Steve

 

[Mike Lewis]

To make the update seen by other workstations, Mike's "remote view" suggestion sounds like the way to go if/when he gets associates. I'm not sure how that would work. I (mistakenly?) thought a remote view is really only cursor created from a SELECT statement and question how it would be seen automatically by another computer.

Steve, you are right about the cursor. A remote view is essentially a wrapper for SQL commands that retrieve data from the server and store it in a VFP cursor (although it is not called that).

The way it is "seen by another computer" is that the other computer has a copy of the same remote view. Think of a remote view as a set of instructions for retrieving the data. When user A opens the view, those instructions are executed, and he gets a copy of the data at exists at that moment, stored in a local cursor on his computer. Some time later, when user B opens the view, the same thing happens on his local computer. If the data changed between user A and user B opening it, user B will see the more up-to-date copy.

At any time, either user can refresh the data by issuing a REQUERY(). This will retrieve the most up-to-date data, and continue to store it in the same cursor on the local machine.

However, the real benefit of a client-server system comes when you ask the server to filter the data in some way. To go back to your original question, if, at any one time, you are only interested in customers from a given state, you would put a filter condition (that is, a WHERE clause) in the view. The filter would recognise a parameter, which is essentially a memory variable which holds the code for the state you are interested in. Then, after you issue a REQUERY(), the view (that is, the cursor) will only contain customers from that state.

The last piece of the puzzle is to add the NODATA clause to the USE command that you use to open the view. That will avoid the long delay that occurs at start up when the data is first downloaded.

I hope this makes sense. If necessary, check back to my previous post, where I give a bit more information about this.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vernpace]

Steve,

Designing database applications can be very challenging. A good design is key for performance and customer experience.

In my opinion, bringing down and displaying 60 fields and 28,000 records in a UI is not good design - particularly displaying 28,000 records. Is that what your users want? Did you ask them? Ideally, UI design should use discrete sections and the database should be designed accordingly. Did you design the database? Good relational data design is critical and is an art.

I also recommend using Cursor Adapters as they are much more flexible (and faster) than Remote Views. This requires some learning and the development of classes - there is a lot of information in VFP help and on the Internet to assist you.

Also, you should forget about SET FILTER - it is squirrelly at best.

 

[Mike Lewis]

Also, you should forget about SET FILTER - it is squirrelly at best.

Just to be clear, when I talked about filtering data on the server (see above), I was using the term "filter" in a general sense: nothing to do with VFP's SET FITER command.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Meyerson]

Vern..displaying 28,000 records. Is that what your users want? Did you ask them?

Actually, yes and yes. For example client calls Bruce. "This is Mary Smith. Joe won't sign." Bruce scrolls through a portion of the list of similar names to quickly identify the one with spouse Joe. This may not the best example, but Bruce is happy with the whole thing (which does much more), makes money, and gives me a little of it.

But, as both of you correctly point out, I need a better design, using cursor adapters and/or remote views, if he expands. Thank you so much, Mike, for that great explanation of remote views (better than the help file )

Regarding the slow download, his connection is WiFi (mine is Ethernet). He downloads once and leaves the program running all day (or even consecutive days). Disconnects often occurred during the long day. To compensate, I minimized connection time by connecting and disconnecting immediately before and after each web access.

Steve

 

[Steve Meyerson]

Mike,

I think Vern was referring to my SET FILTER comments rather than yours which you related to the WHERE clause.

ps: I learned about SET FILTER the hard way by discovering the "squirelly" behavior.

Steve

 

[torturedmind]

Steve,

May I also suggest you use bind variables/parameterized query? Your statement

cmd = 'SELECT * FROM Clients' + IIF(EMPTY(m.cYr), '', ;
  " WHERE Entered>='"+m.cYr+"-01-01'" + IIF(.InclUnpaids, ' OR Pmt1Amt=0', ''))

could be

cmd = 'SELECT * FROM Clients' + IIF(EMPTY(m.cYr), '', ;
  " WHERE Entered>=[COLOR=#CC0000][b]?cAnyDate[/b][/color]" + IIF(.InclUnpaids, ' OR Pmt1Amt=0', ''))

where cAnyDate is your bind variable and your whole SQL statement becomes a parameterized query. This way, the server will only parse one statement (the latter) by just substituting the variable with the value you supply. The one you currently have is parsed every time. A good read on the subject will help you understand its advantages.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Chris Miller]

What Mike didn't have in his already good remote view description is what distinguishes it from a mere SQL query retrieving data. It can be made an updatable view by telling VFP the essential information needed to automatically construct and execute the necessary UPDATES, INSERTS, and also DELETES by a TABLEUPDATE().

The view designer guides you through this and asks you to specify the MySQL primary key field and which fields you want updatable (typically all but the primary key).
There are also tools making this easier, mainly the SQLtoolbar button will give you the SQL Query code and anything related to the updatable nature of the view, which makes it simpler to edit in my opinion.

And regarding filtering, you can write out parameters for the queries by

SELECT ... WHERE MySQLTable.MySQLFieldX = ?m.VFPParametervariable ...

Where the value of the variable named VFPParametervariable value is taken for the where condition. For example, it could be the first name "Joe". Bruce then doesn't have to sort by firstname and scroll to Joe, you can set the parameter variable to 'Joe' and USE or REQUERY the view and have those as the only grid records.

Chriss

 

[Steve Meyerson]

Thanks, Chris. Isn't your statement essentially the same as Kilroy's suggested statement, with the "?" preceding the variable name? And from what I can tell, the ?variableName replaces the actual value I used in my SELECT statement.

Right now, I'm working on some other stuff (IRS 1099 forms) with deadlines so I haven't the time to rewrite. But I am saving all your valuable comments and will use them. Thank you again for your help and your time!!

Steve

 

[TamarGranor]

Using parameters in a SQL command is safer than substituting in variables directly. It protects you against SQL injection:

https://www.w3schools.com/sql/sql_injection.asp.

Here's the funny version of the problem:

https://xkcd.com/327/

Tamar

 

[Steve Meyerson]

Using parameters in a SQL command is safer than substituting in variables directly. It protects you against SQL injection:

https://www.w3schools.com/sql/sql_injection.asp.

Thanks Tamar for the link. It's scary. Wouldn't the hacker have to know the field name for user id (which would probably not be hard to guess)?

In VFP, would use of SQLPREPARE() count as using "paramters" in an SQL command?

Steve

 

[Chris Miller]

Steve, SQLPREPARE is not necessary, you can use the syntax with ? in views, in SQLEXEC, but indeed SQLPREPARE supports it, too. The main reason for SQLPREPARE is to let a database know you want to execute this query several times, so it's precompiled, the optimal query plan is saved and you can then execute it within a scan loop, changing parameters every time. Or you can use it as a simple way of defining (temporary) stored procedures without needing to know how to create and call one in the remote database (MySQL).

I don't know whether SQLPrepare works with MySQL, but it'd be easy to check out. I do know that MySQL's language includes SQL Prepare and you can therefore execute a MySQL script doing an SQL Prepare in MySQL language and use that, if VFPs way doesn't work.

https://dev.mysql.com/doc/refman/8.0/en/prepare.html

As the docs tell, you give the prepared query a name and then can use that later until Which means you best put the definitions into the routine connecting to MySQL and also call this code to reconnect, so the prepared statements your application uses will always be defined.

The most reliable way to use parameterization is to actually create stored procs and then the only necessary MySQL you execute from VFP will be EXEC yourproc(?m.Param1, ?m.Param2) etc., just like prepared statements are executed.

In the end you'll always use VFPs preparation method for parameter passing. That's not bad, it's obviously necessary to get the data from VFP over to the MySQL server. Notice, you don't need to create variables, you can also pass in ?DATE() for today's date or ?alias.fieldname to pass in the value of a DBF field.

It's more effective to use an updatable view with TABLEUPDATE(), as that will do the job including to use a prepared statement and saves all changes in one go for you. You decide: Write the code to do your INSERT, UPDATES, DELETED and loop through VFP data to get it to MySQL or learn how to make a remote view or cursoradapter updatable to only need TABLEUPDATE() for saving all buffered changes.

Chriss