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