1.) Triggers: Why can’t the handling of triggers be consistent with the way MYSQL stores/handles triggers. Namely each trigger as its own entity. Currently triggers just roll together in the sample as shown below in an example that does nothing but is just used to describe the problem

– Trigger DDL Statements
DELIMITER $$

USE `test`$$CREATE DEFINER=`admin`@`%` TRIGGER `beforeinsert`
BEFORE INSERT On table1
FOR EACH ROW
BEGIN
    SET new.field1= new.idtable1;
END$$CREATE DEFINER=`admin`@`%` TRIGGER `beforeunsert`
BEFORE UPDATE On table1
FOR EACH ROW
BEGIN
    SET new.field1= new.idtable1;
END$$

As you can see the triggers just run into each other. At least the coloring helps a bit but it is still difficult to find/handle/change something.

If you execute a SHOW trigger command you get the triggers data  neatly seperated in columns like

TRIGGER EVENT STATEMENT ,,,,, etc

Why not make a seperate tab for each trigger (as it is a row in a table) and then display each field for some data. This should greatly improve the handling of triggers in the system. If also should make the synchronizing easier as each field already has its own value. That way lots of unnecessaty updates could be eliminated.

2.) User defined Data Types: MYSQL currently does not suppor them. It would be nice to have. But currently this feature is unreliable and can be dangerous. It should only be included in the system if it can be implemented in a rock solid way. It would be nice to have User Defined Data Types to help with consistency through the system and allow systemwide changes when for example it is found that a field size – like for example a customer # or part # fieldd – must change. Mosty for key/index fields used to help keep up the relations between tables. If a single update from the schema to the model can wipe out the Custom Data Type and replace it with a VARCHAR(whatever) or something like that then the whole concept of having the User Defined Data Types in the model is defeated. Some way to store it at the server side would have to be found – either as part of the comment for that particular field – or waiting until MYSQL actually supports user defined Data Types.

3,) Schema Privileges: WB has a whole section devoted to that. One that currently does absolutely nothing of use in my oppinion. Maybe that is why I could not find a help entry in the help system on it. True it lets you create users and define roles. Roles fall in the same category as User Defined Data Types as MYSQL currently does not support User Defined Roles. Currently in WB you can define a role and give that role certain privileges like (INSERT, SELECT…..) on particular tables and then you cann assign those roles to Users that you create. All very nice and reminiscent to featurs available in other SQL systems – but currently nothing more thant a notepad as it does not update the server.

Any user created in the model will not create a user on the Server side and any privilege granted to the User will not propagate to the server. In my opinion it is currently at best a notepad on what rights one might want to set up manually at the server ….

Again I would like to either – preferably – have this feature turned into a reliable solid tool – or removed as it does not do anything really useful at this time and could cause confusion and anger in the user. After all I might have spent hours setting up a Roles/User System in WB and now I got nothing at the server side and got to do it manually there…..

Lastly it would be nice if they could include a debugger into that system that allows you to debug your Stored Procedures/functions/triggers and step through them – but I guess I can keep hopeing.

Overall I would like to say that the project is moving in the right direction and I am using WB currently to develop my current project. So keep up the good work

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

In course of that I mentioned the process of converting from VFP (Visual Foxpro) to MYSQL and I was asked to Blog about my experiences with that and feed it to Planet MySQL. Starting to read Planet MySQL I must say that I am probably an oddity here as there does not seem to be any VFP people here. For that reason I also fed and anounced my Blog to some VFP boards and feeds. I even got picked up by a PHP blog for reasons I dont know. In the VFP community the response was quite positive an some discussions, comments and email were exchanged

As for Planet MySQL. Well I got some thumbs up and some thumbs down and what bothers me is that I do not know why. Maybe there are some comments somewhere but personally I find a thumbs up or down quite useless if not accompanied by a comment.

Why is a particular blog good? what was the good point(s)?

Whis is it bad. Is it inapropriate here, poorly written? Is the procedure outlined within that particular blog flawed? Is there a better way?

As things keep changing we all are learning so we all can benefit from some advice and some help to stay or get on the right course.

So I apreachiate the votes because it means that someone at least read it. But I would really like to get a note on why its up or especially down. My goal is to become better and to have this blog here to be my contribution and hopefully become a one stop place for anyone facing the task to switch from VFP to MYSQL. So if there is a better way then please by all means drop me a note

Hoping to see some comments from here

Martin Pirringer

Now moving to MYSQL I want to point out some instant benefits you get migrating to MYSQL and that is security. One thing probably not immediatly visible to a newbie is the power of the “DEFINER” and INVOKER security options on stored procedures. This allows clients to do things under certain circumstances that they do have no right to. I want to use the following example to demonstrate the very usefulness of that feature.

First of course we wont give any of the administrative roles to the user accounts used to access the server. We probably wont even give that account the “DELETE” right on the schema. We do not want an errant DELETE…. to run havoc on our tables – accidental or malicious as it may be. But there might be a scenario where the client needs to delete something.

We can accomplish this by wrapping the DELETE statemente into a stored procedure We created the stored procedure with a statement something like

CREATE DEFINER = `user_with delete_rights`@`%` PROCEDURE `deletesomething` (<some parameters>)
BEGIN
-- some checking of the parameters and
DELETE ...... -- our delete statement probable a prepared one

We do not need to add “SECURITY DEFINER” as that is the default option. The above means that the statements within the procedure are executed as if the user was the user_with delete_rights user and not the regular user account that logged in. This way now our client can delete what he needs to delete and we can make sure that he cannot delete anything else accidentally or otherwise.

He needs the “EXECUTE” priviledge to run the “CALL …. ” statement. But that is all he needs for that. If one is so inclined one could wrap pretty much any statement into a STORED PROCEDURE wrapper and do the same for INSERT, UPDATE and maybe even SELECT statements to enhance the security and integrity of the whole system

The task was to roll back the AR aging to a prior date. Basically a standard business problem. Accounting comes, looks at last months aging, makes some adjustments and then wants to rerun the aging, or the bank wants it as of a particular date or things just went nuts and everyone was busy with … whatevever …. and did not get to run the aging at the proper time. So to handle this a query was devised to back out all invoices and applications as to a certain date.

In the late 80’s the customer got and SBT accounting system and that was then modified and modernized over the decades so the  invoices, credits and payments are  stored in the invoice master file. fields if interest: Invamt, Paidamt, Invdte, Balance, custno, salesmn etc. Cash and credit applications are in a file called cc_apply in VFP the problem was solved with a query that ran into a little under 2 seconds and looked like this

SELECT a.custno, ;
a.invno AS invno, ;
a.invdte AS invdte, ;
a.balance + NVL((SELECT SUM(f.apply + f.disc) ;
FROM “accounts receivables!cc_apply” f;
WHERE f.invno = a.invno AND TTOD(f.appdate) > ld_date), 0) ;
- NVL((SELECT SUM(f.apply) ;
FROM “accounts receivables!cc_apply” f;
WHERE f.ccati = a.invno AND TTOD(f.appdate) > ld_date), 0) AS balance, ;
a.paidamt – NVL((SELECT SUM(f.apply + f.disc) ;
FROM “accounts receivables!cc_apply” f;
WHERE f.invno = a.invno AND TTOD(f.appdate) > ld_date), 0) AS paidamt, ;
a.invamt – NVL((SELECT SUM(f.apply) ;
FROM “accounts receivables!cc_apply” f;
WHERE f.ccati = a.invno AND TTOD(f.appdate) > ld_date), 0) AS invamt ;
FROM “accounts receivables!invoice master” a, customer\customer cu, ;
(SELECT DISTINCT e.invno as invno FROM (SELECT d.invno as invno ;
FROM “accounts receivables!invoice master” d ;
WHERE d.invdte <= ld_date AND d.balance # 0 AND !d.arstat $ “Vv” ;
UNION SELECT d.invno as invno ;
FROM “accounts receivables!invoice master” d ,;
“accounts receivables!cc_apply” b ;
WHERE d.invno = b.invno AND d.invdte <= ld_date AND d.balance = 0 AND !d.arstat $ “Vv” ; AND TTOD(b.appdate) > ld_date ;
AND (b.apply # 0 OR b.disc # 0) ;
UNION SELECT d.invno as invno ;
FROM “accounts receivables!invoice master” d ,;
“accounts receivables!cc_apply” b ;
WHERE d.invno = b.ccati AND d.invdte <= ld_date AND d.balance = 0 AND !d.arstat $ “Vv” ; AND TTOD(b.appdate) > ld_date ;
AND (b.apply # 0 OR b.disc # 0)) e) c ;
WHERE a.invno = c.invno AND a.custno = cu.custno AND !a.arstat $ “Vv” AND &lc_filter;
ORDER BY a.custno, a.invdte, a.invno ;
INTO CURSOR c_arorec NOFILTER READWRITE

&lc_filter was used to filter out some other parameters like sales rep, state, customer etc.

The query was ported into MYSQL, NVL() was replaced with COALESCE()  and the statement that took about 2 seconds under VFP took a little over 50 in MYSQL. So a different approach was needed. MYSQL, even with hints seemed to have problems using the right indexes or come up with a reasonable plan as the EXPLAIN statement showed. Even though VFP (Visual foxpro) does not have a feature to show you exactly what it is doing, on a proper written query it will do most the selecting inside the indexes if possible as that is cheaper. The bottle neck in VFP is usually the network. As the processing gets done at the workstation you want to cut he total # of bytes sent to the workstation for processing down.

In  MYSQL its disk reads. I could not convince the optimizer to do things in the sequence I wanted on that query so I had no choice than the break that query and spoon feed it to the optimizer. From past trials it appears to me that the way to go with MYSQL is to cut down rowcount and disk read/writes. So here is the way I got MYSQL to solve the above problem in under 2 seconds.

For starters to cut the row count down as early as possible I decided to employ some level of denormalisation and put the custno, salesman, state,invdte, dtepaid into the cc_apply file. I keep that in sync in the INSERT and update triggers and with foreign keys. This puts a little overhead on the INSERT and UPDATE statement but pays of big time at the time of data SELECTION. Furthermore, as we were going to do this in multiple SQL statement we wrap those statement into a stored procedure to handle our parameters and selections like

CREATE DEFINER=`whoever`@`%` PROCEDURE `arorec`(v_date DATE,
v_state varchar(2), v_salesmn varchar(2), v_custno varchar(6), v_sum TINYINT)
READS SQL DATA
DETERMINISTIC
BEGIN
SET @v_date = v_date;
SET @v_custno = v_custno;
SET @v_state = v_state;
SET @v_salesmn = v_salesmn;
SET @v_sum = v_sum; — summary or not
/*Note need to use tempres and tempcr as you cannot use temp table(tempsel) 2x in a union*/
DROP TABLE IF EXISTS tempsel;
DROP TABLE IF EXISTS tempres;
DROP TABLE IF EXISTS tempcr;

SO for starters we needed all the invoices that were created before the cut of date and that had moneys applied to them after the cut of date. As the resulting set is definitly way below the available ram on the target server we will put this resutl set into a temporary table in memory with

IF @v_custno IS null AND @v_state IS NULL AND @v_salesmn IS NULL THEN
/* GET EVERYONE*/
PREPARE stmt1 FROM ”
CREATE TEMPORARY TABLE tempsel ENGINE = MEMORY SELECT invno, Sum(apply) as apply, SUM(disc) AS disc
FROM cc_apply WHERE invdte <= ? AND appdate > ? GROUP BY invno” ;
EXECUTE stmt1 USING @v_date, @v_date;
ELSEIF @v_custno IS NOT NULL AND @v_salesmn IS NULL THEN
/* State is irrelevant as each customer has one address customere only*/
…….The selections for other criteria

Next we had to join this with the invoice and CM header records in the invoice master table plus add all the invoices that were created the cutoff date, are still open and did not have anything applied to them. Normally we could do that in a UNION, but MYSQL does not let you use a UNION on a TEMPORARY TABLE so we use another temporary table like this

PREPARE stmt1 FROM ”
CREATE TEMPORARY TABLE tempres ENGINE = MEMORY
SELECT ma.custno, ma.ticket AS refno, ma.dtepaid, ma.artype, ma.ponum AS checkno, 0000000000.00 AS future,
ma.invno, ma.balance + te.apply – te.disc AS BALANCE, ma.entered, ma.invdte, ma.invamt, ma.paidamt, getpd(@v_date, ma.invdte) AS gp
FROM `invoice master` ma INNER JOIN tempsel te ON ma.invno = te.invno
WHERE ma.invdte <= ? AND artype IN (‘ ‘, ‘I’, ‘C’, ‘W’)”;
EXECUTE stmt1 USING @v_date;
DEALLOCATE PREPARE stmt1;
ALTER TABLE tempres ADD INDEX custno(custno);
/* UNION of invoices still open without anything applied and futures*/

IF @v_custno IS null AND @v_state IS NULL AND @v_salesmn IS NULL THEN
Prepare stmt1 FROM ”
INSERT INTO tempres
SELECT ma.custno, ma.ticket AS refno, ma.dtepaid, ma.artype, ma.ponum AS checkno, 0000000000.00 AS future,
ma.invno, ma.balance, ma.entered, ma.invdte, ma.invamt, ma.paidamt, getpd(@v_date, ma.invdte) AS gp
FROM `invoice master` ma LEFt JOIN tempsel t2 ON ma.invno = t2.invno
WHERE t2.invno IS NULL and ma.open = 1 and ma.invdte <= ? AND artype IN (‘ ‘, ‘I’, ‘C’, ‘W’) UNION SELECT ma.custno, ma.ticket AS refno, ma.dtepaid, ma.artype, ma.ponum AS checkno, ma.invamt AS future, ma.invno, ma.balance + COALESCE(cc.apply, 0) – COALESCE(cc.disc, 0) AS BALANCE, ma.entered, ma.invdte, ma.invamt, ma.paidamt, getpd(@v_date, ma.invdte) AS gp FROM `invoice master` ma LEFT JOIN cc_apply cc ON ma.invno = cc.invno WHERE ma.invdte > ? AND ma.entered <= ? AND artype IN (‘ ‘, ‘I’, ‘C’, ‘W’)”;
EXECUTE stmt1 USING @v_date, @v_date, @v_date;
ELSEIF @v_custno IS NOT NULL AND @v_salesmn IS NULL THEN
……. The other statements for selection by salesmn, state, etc

We then do the same for the payments and end up with a temporary table “TEMPRES” that holds all the records needed for our aging.

In VFP we then used “SET RELATION TO” to get access to some other relevant fields for our reporting but it is much more efficient to have the MYSQL server send all the data in one result set so we are doing this in the final SELECT statement that looks something like this

SELECT te.*, cu.email, cu.statml, cu.statfmt, cu.company, cu.phone, cu.faxno, cu.contact, cu.address1,
cu.address2, cu.city, cu.state AS state, cu.zip, cu.pterms, cu.lastpay AS lastpay, cu.limit,
cu.lpymt, cu.ldate AS ldate, cu.lsale, cu.inactive AS inactive,
IF(te.gp=’C',te.invamt – te.paidamt,0.00) AS current,
IF(te.gp=’1′,te.invamt – te.paidamt,0.00) AS pd1,
IF(te.gp=’2′,te.invamt – te.paidamt,0.00) AS pd2,
IF(te.gp=’3′,te.invamt – te.paidamt,0.00) AS pd3,
IF(te.gp=’3′,te.invamt – te.paidamt,0.00) AS pd4,
IF(te.gp=’O',te.invamt – te.paidamt,0.00) AS pdover
FROM tempres te INNER JOIN customer cu ON te.custno = cu.custno
ORDER by te.custno ASC, te.invdte ASC, te.invno ASC;

The getpd() is a stored function that determines based on parameters stored in another table if we want the output in 30/60/90/120, 7/14/28/35. based on the end of the week, end of month or any date in between to allow full flexibility in the aging output.

For those interested.  On a table with about 500,000 invoices records, 300,000 records in the application table, on my development server on my laptop the whole SP takes 1.4 seconds in the worst case scenario. Granted the complete stored procedure with formattin as shown above is over 300 lines long with many statements as compared to one straight forward SQL statement in VFP. But the end result is on par with VFP with the added benefit to provide improved results over slow connections over the internet

First when using examples I try to just focus on the subject of the current blog and often strip a lot of things that are irrelevant to the point I am trying to make out. But now to the subject of keeping your code save.

If you write and SQL statement looking something like

lc_sql = “SELECT * from customer WHERE custno = ‘” + lc_custno + “‘”

Someone could enter “‘; TRUNCATE TABLE customer;’” resulting in  a SQL statement looking like

SELECT * from customer WHERE custno = ”; TRUNCATE TABLE customer; ”

Which could be quite disasterous. So to avoid this we could use parameterized queries which I cannot as I am using a seperate function to wrap around the SQLEXEC() function to do all kinds of things like connection maintenance, Error handling, Logging and even in some cases sending an email to the IT when certain conditions arise. So the above mentioned problem needs to be addressed when creating the sql statement.

The first line of protection is to properly escape any string I send. The table below is copied from the MYSQL manual showing what needs escaping

So I wrote a vfp function called addslashes()

FUNCTION Addslashes

LPARAMETERS lc_str, ll_notrim

lc_str = STRTRAN(lc_str,”\”, “\\”)

lc_str = STRTRAN(lc_str,”‘”, “\’”)

lc_str = STRTRAN(lc_str,’”‘, ‘\”‘)

lc_str = STRTRAN(lc_str,’%', ‘\%’)

lc_str = STRTRAN(lc_str,’_', ‘\_’)

lc_str = STRTRAN(lc_str,CHR(0), ‘\0′)

lc_str = STRTRAN(lc_str,CHR(10), ‘\n’)

lc_str = STRTRAN(lc_str,CHR(13), ‘\r’)

lc_str = STRTRAN(lc_str,CHR(26), ‘\z’)

lc_str = STRTRAN(lc_str,CHR(9), ‘\t’)

lc_str = STRTRAN(lc_str,CHR(8), ‘\b’)

IF !ll_notrim THEN

      lc_str = RTRIM(lc_Str)

ENDIF

RETURN lc_str

I could Have used a FOR … NEXT Loop with a CASE statement and loop through the string but found that that was slower than calling the STRTRAN() function multiple times. So now we run every string through this routine and the above SQL statement becomes

lc_sql = “SELECT * FROM customer WHERE custno = ‘” + ADDSLASHES(lc_custno) + “‘”

And the above attempt to mess with the system will produce an sql now looking like

SELECT * from customer WHERE custno = ‘\’; TRUNCATE TABLE customer; \”

Which would just return an empty record set.

This will also allow me now to insert and retrieve string like “John’s Barber Shop” or multi line entries. Furthermore I made it a rule to emulate strong typing when it comes to SQL statements. Simply to to prevent a “Garbage in”/”Garbage out” Scenario. So Each textget has its validation routine and is bound to a local cursor. VFP allows you to change the type of a variable by simple assigning some different type of data to it but fields in a cursor are of a fixed type. This also allows us to use functions to build the SQL statements based on the underlying cursor. So every string and Memofield gets treated with ADDSLASHES() each numeric with STR(), Each date field WITH DTOS() to make a MYSQL valid date string etc.

At this point some might say it looks like a lot of work and some of this is replicating what VFP does through remote views. So why use SQL Pass through and not remote views? The single aswer is performance. This way I can control what to send when and how and avoid data being sent unnecessarily or at inoportune times.

First lets assume that our tables all have lots of records. After all it does not pay to spend time on optimizing on tables with a few dozend records. I also will not go into VFP optimization here as this is a MYSQL blog.

Lets assume for our first case we might have a table with invoice detail records and a table with items that are on sale. So now we want to get maybe 2 queries. One to show a total of all the items that were on sale and the second a total of all the items not on sale. I will leave date selections etc out of this as I just want to focus in on some performance issues

In VFP we might have had something like

SELECT <fieldlist> FROM detail det WHERE INDEXSEEK(det.item, .f., “promotions”, “item”)

SELECT <fieldlist> FROM detail det WHERE .NOT. INDEXSEEK(det.item, .f., “promotions”, “item”)

respectively depending which set we want. In VFP it was quite fine to use and INDEXSEEK in the Where clause. I sometimes used it in place of a join as it seemed to work faster and in previous version of VFP was a way to get around limits in the number of JOINS and the complexity of a query vfp supports.

In MYSQL now you could replace the INDEXSEEK with an IN or EXISTS(SELECT….) Subquery  so the query rewritten will look something like

SELECT <fieldlist> FROM detail det WHERE EXISTS (SELECT * FROM promotions p WHERE p.item = det.item)

Or the “NOT EXISTS” version for the oposite result. You could also use IN but in some cases that might even be more inefficient as IN retrieves the whole matching subset and EXISTS stops at the first occurance.

If we run the above query through “EXPLAIN” to see what MYSQL is doing we will see that on ther “SELECT <fieldlist> FROM detail det” it will perform a table scan and  then the item  index of the promotions table. So having a large “details” table that has also an “item” index this is not very efficient. In MYSQL the way to improve on that is to rewrite the query as a join. like

SELECT <field list> FROM detail det JOIN promotions p ON det.item = p.item

In this case MYSQL will use both item indexes and only retrieve the items matching. It gets a little bit more complicated with the case where we want to select items that have no promotions but we can do a join there too with

SELECT <field list> FROM detail det LEFT JOIN promotions p ON det.item = p.item WHER p.item = NULL

This one again will make the selection on the index level and depending on the amount of records you are dealing with result in great performance gains.

Lets now look at another scenario. Lets assume we have a invoice master table with  as “balance” field and we want to filter out as part of a query all records that are open so we are looking for “BALANCE <> 0″ In vfp we created an index on balance  and that improved the statement

SELECT <fieldlist> FROM master WHERE balance <> 0

drastically. Now if we do the same in mysql and we check with

EXPLAIN SELECT <fieldlist> FROM master WHERE balance <> 0

We will find that MYSQL is doing a table scan. MYSQL seems to have a problem with “<> <keyfield>”

You can get a slight improvement by

SELECT <fieldlist> FROM master WHERE balance < UNION SELECT <fieldlist> FROM master WHERE balance > 0

As it seems MYSQL has an easier time using the index with just the > and the < and a UNION but you are running into unnecessary overhead there too. What I have found most efficient so far is to add a field to the table

unpaid BIT

And then create an index on this field and set it in the BEFORE UPDATE and BEFORE INSERT triggers with

IF NEW.balance = 0 THEN SET NEW.unpaid = 0; ELSE SET NEW.unpaid = 1; END IF;

THE sql statement now looks like

SELECT <fieldlist> FROM master WHERE unpaid = 1

I used the BIT type field as it nicely translates into a VFP logical field with .f. for 0 and .t. FOR 1. But you probably can achieve the same by using an TINYINT or CHR(1)  or BOOLEAN field.

The performance improvement was drastic.

Beyond this it is always good to remember to

1.) make sure you have indexes on the fields in the “WHERE” clause and they are being used. USE “EXPLAIN ….” in the MYSQL query editor to find out.

2.) keep the row count to a minimum especially if you are using subqueries. So it often makes sense to first cut the number of records down with a subquery. I will go into detail on this in a seperate blog

As always comments/suggestions are welcome as this is all work in progress

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

• Transfer Table Structures
• Set up Indexes
• Set up foreign keys

So lets look at each step more closely

Transfer Table Structures

Granted we could sit down and take a printout of all the structures and then use Workbench or something like it and start setting up the tables. But being faced with having to do that with close to 100 tables some of which having 40 or so fields this appeared like a daunting task. So I took a look to see how much could be optimized. Tables In MYSQL are created by using the CREATE TABLE ….. syntax which is pretty straight forward. Granted there are some VFP features that MYSQL does not have and some that are available in MYSQL and lacking in VFP but at least for the overlapping part one could quickly write some VFP code as follows:

FUNCTION CreateTable

PARAMETERS lc_sourcetable, lc_desttable

USE (lc_sourcetable) AGAIN ALIAS a_conv

ln_c = AFIELDS(la_f)

lc_sql = “DROP TABLE IF EXISTS `”+lc_desttable + “`”

IF !MYSQLEXEC(lc_sql, .f., “Cannot drop table”)

    RETURN .f.

ENDIF

lc_sql = “CREATE TABLE `”+lc_desttable + “` (“

FOR ln = 1 TO ln_c

    IF ln > 1

       lc_sql = lc_sql + “, “

    ENDIF

    lc_sql = lc_sql + “`” + la_f(ln, 1) + “` ”

    DO CASE

    CASE la_f(ln, 2) = “C”

        lc_sql = lc_sql + “VARCHAR(” + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + “)”

    CASE la_f(ln, 2) = “D”

        lc_sql = lc_sql + “DATE”

    CASE la_f(ln, 2) = “T”

        lc_sql = lc_sql + “DATETIME”

     CASE la_f(ln, 2) = “M”

         lc_sql = lc_sql + “TEXT”

     CASE la_f(ln, 2) = “L”

         lc_sql = lc_sql + “BIT”

     CASE la_f(ln, 2) = “N”

         lc_sql = lc_sql + “DECIMAL(” + + ALLTRIM(STR(la_f(ln, 3), 10, 0)) + ;

         ”, “ + ALLTRIM(STR(la_f(ln, 4), 10, 0)) + “)”

     CASE la_f(ln, 2) = “I”

         lc_Sql = lc_sql + “INTEGER”

     CASE la_f(ln, 2) = “G”

         lc_sql = lc_sql + “MEDIUMBLOB”

    ENDCASE

    IF !la_f(ln, 5)

        lc_sql = lc_sql + ” NULL”

    ELSE

        lc_sql = lc_sql + ” NOT NULL”

    ENDIF

    IF !EMPTY(la_f(ln, 9))

        lc_sql = lc_sql + ” DEFAULT ‘” + la_f(ln, 9) + “‘”

    ENDIF

NEXT

lc_sql = lc_sql + “) ENGINE = INNODB”

IF !MYSQLEXEC(lc_sql, .f., “Cannot create table”)

    RETURN .f.

ENDIF

RETURN .t.

This Function simple creates a CREATE TABLE statement for the VFP table <lc_sourcetable> sent to it an creates a mysql table named <lc_desttable> if it is successful then it returns .t. otherwise .f. The MYSQLEXEC() function is a udf I wrote that handles all the MYSQL traffic including setting up connections and error logging/reporting but could have been accomplished with a combination of SQLCONNECT() and SQLEXEC() functions. We are using the AFIELDS() VFP function to get an array of all the fields in the TABLE and the according properties.

Now all one needs to do is some code like

OPEN DATABASE myvfpdb

ln_tcnt = ADBOBJECTS(la_t, “TABLE”) && get a list of all the tables

FOR x = 1 TO ln_tcnt && loop through all the tables

    WAIT “Creating ” + la_t(x) WINDOW NOWAIT

    IF !createtable(“<pathtodatadir>\” + la_t(x), la_t(x))

        *what ever error handling you want to do

    ENDIF

NEXT

So now that we got the structures across we can get to

Transfer Indexes and Foreign Keys

Unfortunatly in my case I did not come up with a way that was quicker by writing some code than to do this manually. VFP allows too many things in Indexes to drastically reduce the number that could be transferred by a program. Simpe VFP things like using UDF’s or VFP functions in indexes and also FOR expressions (filters) on indexes. A quick look at the documentation showed that less than 20 % of my indexes could be transferred programatically. This also means that quite some code has to be changed so transferring the same Indexes used in VFP is not a good idea. Indexes are there to improve data retrieval so they need to be optimized to the particular engine.

VFP accomplishes referential integrity with triggers. And there again the two platforms are too different to just write a short procedure to accomplish this. And in this case MYSQL with INNODB has some features that VFP does not have. So it made more sense to do this manually and examine each case and set it up in the way most suitable for MYSQL.

Next: transferring the Data

So now that the decision was made we had to start to get to work. To show the customer progress and to see what I was up against I downloaded MYSQL Community and set up 3 Servers.

• One on my Vista Machine, to be accessed by the customer to see the progress. So any part finished and tested will be pushed to that server.
• A 2nd one on a WinXP machine in anothter location just to see on how the performance is over the net. After all we don’t want to find out it takes enough time to have a cup of coffe while waiting for the computer to perform a task
• And A 3rd on my Windows 7 laptop.
• And another one is planned on a Linux box at a later point.

So some of you might ask why 3 different Windows platforms? The simple answere is – That is what is mostly out there. This is to replace VFP systems run by small to medium sized companies. They have a budget. Mostly if you can bring it in und 10,000 you got the sale. So it benefits if one can use existing equipment.

I am happy to report that all 3 installs went smoothly and w/o any problems.

Next came the task of installing and testing the ODBC driver.

On the Vistat 32 bit and XP machine – no problem at all. On my Windows 7 I initially was perplexed as after the installation of the 64 bit version I could not find it anywhere but a little digging through the MYSQL bug database produced :

Explanation: This is not a bug.
MyODBC 3.51 is 32bit application which, by default, goes into
%SystemRoot%\sysWOW64 folder on 64bit windows. The DataSources(ODBC) link in
control panel searches for 64bit drivers in %SystemRoot%\system32 folder (silly
naming I know…).
What you have to do is:
  Locate odbcad32.exe in *%SystemRoot%\sysWOW64 folder* (DO NOT use the one in
%SystemRoot%\system32 folder pointed to in control panel!)
  Use this program instead of default 64bit version to access MyODBC
  Start it and add MyODBC driver from the list of available drivers

Even though I was installing Version 5.1.6 the above applied to that to and following the above suggestions resolved the issue.

So now that we had a working connection between VFP and MYSQL it was time to do some performance tuning. After all we want things to go nice and snappy. There is awfully little to be found on VFP/MYSQL connections so I spent the better part of a day testing and experimenting. The best settings and procedures I came up with are.

• On the VFP side use shared connections. It can take up to 1/2 second to set up a connection. Plus setting up individual connections will unecessarily increase the connection count on the server. For large record sets (>100 records) make that async. shared connections.To connect with vfp use:

  gn_conn = SQLCONNECT(“MYSQLDSN”, .T.) && Establish A shareable connection

  gn_con2 = SQLCONNECT(gn_conn) && for each subsequent connection to share that connection

  to make the connection async

  ll_success = SQLSETPROP(ln_conn,”Asynchronous”,.t.)

  And then to execute a SQL statement

  ll_success = SQLEXEC(gn_conn, lc_sql, lc_alias)

  * lc_sql holds the MYSQL SQL statement and lc_alias is the alias of the receiving cursor

  Of course the gn_conn could becone gn_con2 etc depending which shared connection you use. The advantage of using a shared connection is that you can run multiple statements asynchronously at the same time. So lets assume we have 3 SQL statements that we need to run on form LOAD then shooting them off on 3 shared asynchronous connections will take about ½ second before returning control back to our program. We now can go on and continue with our code while the results from our SQL statements are loaded in the background. This prevents long wait times – especially in slow connections.

• On the ODBC connector use the following settings (accessible by clicking the Details button on the ODBC configuration screen):Allow Big Result Sets = on

  Use Compression = on (will drastically improve availability of data on large result sets

  Disable Driver Provided cursor support = on (performance increase don’t know why)

  Force use of forward only cursors = on (performance increase)

  Allow multiple statements = on (performance)

  There is another setting that drastically improves performance but it comes currently at a steep price.

  Don’t cache results of forward only cursors = on

  If you use this option you cannot use asynchronous and shared connections as you will receive an error from the ODBC driver that states: ““You cannot execute that command now – commands out of sync SELECT ……” or something similar. I have seen some bug reports filed on this and am hopeful that this gets resolved at some future time.

Next: Transfering the Database