When modifying VFP Forms to run the MYSQL Backend the first question most likely will be: How do I get the data from MYSQL efficiently to my VFP Form without rewriting the whole thing from scratch. The form previously accessing VFP tables most likely opened them in the Data Environment so it might be tempting to just replace them with remote cursors or with cursor adapters mimicking the VFP tables and views previously used. Granted the Form might just work with minimal effort but in most cases this approach would be very inefficient. At the same token the VFP controls on the form most likely are bound to some cursor/alias by use of the recordsource or controlsource properties. So unless one wants to do a lot of coding establishing those links later, some fitting cursor needs to exist before the controls are initialized.
Lets assume a simple scenario where we have a customer screen, with a field where you enter the customer code, a little browse control that pops up to give you some choices in case your entry does not exist, and then lots of controls displaying the customer information. The original screen most likely worked by just putting the customer table into the dataenvironment and then bind the controls to it.
So if we replace that with a remote view in the dataenvironment the remote view would have to have “SELECT * FROM customer” as its select statement. If the customer file is very small and not expected to grow much it might be just the easiest to insert a “customer” cursoradaptor into the dataenvironment and use the builder to establish the connection and update method and let VFP take care of the rest. The drawback of this method is that most likely a lot of data would be exchanged between the server and the form that is not really needed. So if the customer table is large and we have a slow connection – maybe over the internet – the performance might be unacceptable. So what can we do to improve performance without getting into too big of a rewrite.
Accessing Data remotly we probably want to keep the traffic between VFP and MYSQL at a minimum. We can accomplish that by using SQL passthrough This gives us full control and the possibility to optimize.
First we are going to have to establish a cursor for the controls before the load. I have found that the forms “LOAD” event is a good place for that. I have a UDF called MYSQLEXEC() that handles all the connection establishing and communication with the MYSQL server. I might write in another blog about it but for now just use it. Its Syntax is MYSQLEXEC(<sqlstatement>, <cursoralias>,<errormessage>, <assync>) It returns .t or .f. based on its success
So in the LOAD event I would put
IF !MYSQLEXEC(“SELECT * FROM customer LIMIT 0”, “customer”, “Cannot access customer File”, .f.)
THISFORM.RELEASE
RETURN
ENDIF
This will create a cursor with 0 records – which is enough for the controls to have something to bind to.
Later in the VALID or LOSTFOCUS events of the Customer # textbox I might have had some code like
SELECT customer
IF !SEEK(THIS.value)
- whatever we did to display the browse screen/control
ENDIF
I could now replace this with
IF !MYSQLEXEC(“SELECT * FROM customer WHERE custno =’” + THIS.value + “’”, “c_temp”, “Cannot access customer File”, .f.)
THISFORM.RELEASE
RETURN
ENDIF
SELECT customer
ZAP && remove previous record from the customer cursor
INSERT INTO customer SELECT * FROM c_temp
You might ask why we do not load the data directly into the customer cursor. The reason is that SQLEXEC which we have to use for passthrough will close the old “customer” cursor and then reestablish a new one which causes grids and other controls to unbind. In grids all columns and controls attached to them would also loose their bindings and we would have to write a routine to establish it again. By ZAPping the customer cursor and then inserting from the c_temp cursor into the customer cursor no unbinding happens and all we have to do is a
THISFORM.REFRESH
So with this method we only transferred the structure an one record over the connection. To further improve on this we could replace the “SELECT *” WITH “SELECT <fieldlist>” to only retrieve the fields we need.
This might especially be benefitial for the Browse screen which we might display in case the entered customer # does not exist. Lets assume that in case the customer selected and incorrect customer code or just entered a portion of it we want to display maybe 20 records close to the customers selection with 10 records before and 10 after it. In VFP we might not have cared and attached the whole “customer” table to the browse screen to allow the user to scroll through the whole database but that again would require to retrieve at the very least a certain number of fields for every record in the table. In foxpro we might have had something like: (assuming lc_custno contains the customer # that was not found)
SELECT customer
SET SOFTSEEK ON
SEEK lc_custno
SKIP -10
IF BOF()
GO TOP
ENDIF
- display/refresh the grid. (in the lostfocus event of course)
For mysql we first might change the binding from “customer” to something like “seekcust” to not interfere with the “customer” cursor used by the other controls and create a sql string something like this:
lc_sql = “SELECT * FROM (SELECT custno, company from customer where custno < ‘” + ;
lc_custno + “’ ORDER by custno desc limit 10) a “ + ;
“UNION SELECT * FROM( SELECT custno, company from customer where custno >= ‘” + ;
lc_custno + “’ ORDER by custno asc limit 10) a” + ;
“ORDER BY custno ASC”
By Selecting the 10 after and the 10 before the selected value and then sorting them by customer # we just transmit custno and company for 20 records. We then could put a “Next” an “Prev” button onto that little popup control that retrieve the next/prev 20 records. When the customer selects a customer we could stuff that value into the customer # setection textget and execute the “VALID” event