Converting a Simple Form

 

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

3 Responses to “Converting a Simple Form”

  1. NIKITAKTAK says:

    Good design blog. Author fellow, will be your constant visitor. I learned many interesting thanks.

  2. Sergio says:

    Dear Martin

    What you are writing here are perhaps usefull tips&tricks for limited scale
    quick&dirty partial conversions when needed, rather then real way to go about re-writing into proper client-server architecture.

    VFP apps especially older ones are mostly so called ‘fat client’ apps, where application is opening shared foxpro data directly. There is myriad
    of app. styles and designs out there.
    Converting those to proper Client/Server application is not exactly
    trivial task, and in many cases might mean almost full rewrite.
    Luckily, there is rich literature on this subject written by good authors (Kramek,Hentzen etc.) and anyone should certainly read at least few of those books or blogs prior undertaking serious conversion to Client/Server with mySQL as back end.

    Don’t get this as criticisam, I just posted comment in order to indulge your readers to do little bit of more research on these matters before
    commiting themselves to a particular aproach.

    Looking forward to you blogs on solving practical problems with mySQL database as backend, as this is also my prefered choice for backend.

    Regards
    Sergio

  3. Elvir says:

    Good examples. I got Hentzenwere book about this problem but too much text about things that many VFP users already knows, but a less really useful information. Thank you. Keep this thread on!

Leave a Reply