Using MYSQL to maintain the VFP frontend.

The main reason for that VFP to MYSQL conversion project was that the customer is in the process of growth and is establishing multiple locations which all will use the same system to some extend. In addition to that we are going from a system where everyone on the system did pretty much everything to one where there are only going to be a few people who have access to everything and most will just work in their “special corner” of the system. Some will just do order entry, some receivables, some sales personel will have only access to their clients etc etc.

At present the VFP system resides on a shared Network drive and if a modification is needed well then one just replaces or adds a Screen or report on that particular shared network drive. Now it would be necessary to push this to multiple locations, in different configurations and that shared network drive wont do anymore – especially over the internet.

Luckely all VFP Screens, Classes and Reports are stored in tables and each object has its own unique object ID number. So the next phase of this project is to store those tables on the MYSQL server. The clients will just get a Loader program in the Loader program you log in and then the loader program executes a CALL statement to the MYSQL server

CALL sendprogramlist(<userid>)

On the server is a table with the user ids and the particular Start form that they will see and the classes that they will need. so the loader program will get a cursor looking something like

name, type, startup(Y/N to tell which program to start with)

Now the loader program Now all the loader needs to do call the getprogram stored function and save it in the apropriate fiel

SQLEXEC(gn_conn, “CALL getprogram(<program name>, <userid>), lc_fname)

lc_fname is the filename comprised out of the filename from the proper extension based on the type (scx, frx, vcx) etc. The only special handling is needed for FXP and MPX files. There is a little hit in the initial load time of the application as the initial startup screen and VCX files need to be retrieved from the server but in my oppinion this outweighs the benefits.

Then during program execution where one had a code looking something like

DO FORM <formname> WITH <arglist>

Now we insert a little codeblock before that statement

SQLEXEC(gn_conn, “CALL getprogram(<program name>, <userid>), <formname>)

DO FORM <formname> WITH … && our initial statement

ERASE <formname>  &&delete the form.

On the SQL server now the getprogram procedure can do all kinds of things like checking if the user has the right to access that form if not we can send him a standard “Access denied” form and send an email to the developer that the client was able to request a form he was not supposed to see. because why put it on that particular users menu when (s)he is not allowed to use it. The routine can also check all objects on a particular form against a table and remove any objects from a FORM that are off limits for that particular user. So for example if that user is not allowed to delete a record – then why send him a form with a delete button on it or maybe that particular user is not supposed to see the cost or profit margine of a particular item. So now we can have a system administrator remove objects from a particular users form. In code most things are handled in the events and methods of each object as it is and references to that object from other objects is rare. But this can be handled too quite simply.

Lets assume we want to update the value property of an object that might get removed from another object. All we have to do now is something like

IF TYPE(<objectreference>) = “O”

       <objectreference>.value = <expr>

ENDIF

So all that would be needed is to wrap the statement into an IF … ENDIF block

Othere benefits from this approach are that the code resides only for short periods on the clients machine and is therefor pretty save. Especially as one can blank out the code fields as VFP only needs the objcode (compiled code) to run. With that we can keep the source safe. And mainly updating becomes now a breeze. When we now change a form all we do is replace it once in the table on the SQL server and any client no matter where (s)he is located will get the new form or feature, menu item, report the next time (s)he requests it. No more need of kicking people off to change something or get numerous calls “I got the update what do I do with it”.

All the client now has to install is the loader program. Everything else is handled by the forms and objects this loader program loads. And if a user gets terminated and takes the loader program with him/her on the way out the door well then all (s)he hopefully gets the next time (s)he logs in is her own special startup program saying

YOU HAVE BEEN TERMINATED

Tags: , ,

One Response to “Using MYSQL to maintain the VFP frontend.”

  1. Susana says:

    I have 4 connections created from a class as objects. Every connection open a database. My problem is the fact that I lost connection to one server for some reason, which I have no idea what it is. It could be working fine for days and then I had a lot of problems for the lost connection. I read in some pages about it and I found it was the error 1466. I read that I can test the connection so I used:

    oException = SQLGETPROP(lnHandleGeneral, “Asynchronous”)
    oException = SQLSETPROP(lnHandleGeneral, “Datasource”)

    But I that creates me more errors and I can’t force the connection again… What should I do?

Leave a Reply