First Contact – VFP/MYSQL interaction

 

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

4 Responses to “First Contact – VFP/MYSQL interaction”

  1. francois says:

    Now, here I find myself in shambles. I have no clue how to apply this in practice. Where is the ODBC? Where should I add these commands?

  2. admin says:

    Download the ODBC connector from MYSQL.com you can find it in the Downloads section.

    On your computer you can find it then in the administrative tools section of your control panel

    HTH

  3. Raymond L. says:

    I was fumbling with this myself, and found excellent information about the various ODBC command options you can use with MySQL here:
    http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

    You can use DSN-less connections by setting up and storing a connection string such as:
    m.cConnStr = “Driver={MySQL ODBC 5.1 Driver};Server=www.yourserver.com;Port=3306;Database=yourdatabase;User=yourmysqluser;Password=yourmysqlpassword;Option=67108864″

    and the last parameter, “Option” you would set to a number that is the sum of the values of each option you want to enable (enumerated on the above link).

    Once you have your SQL connection string correct and including the options you want, simply pass that connection string to VFP’s SQLSTRINGCONNECT function like so:
    m.iMySQL = SQLSTRINGCONNECT(m.cConnStr,.t.)

    As long as the value returned by SQLSTRINGCONNECT is greater than 0, you are successful, and can use that return value as your “connection handle” for subsequent interactions w/MySQL in your VFP program. If you get a return value of 0 (or less?), you should retrieve the error message. I do it like this:

    m.iMySQL = SQLSTRINGCONNECT(m.cConnStr,.t.)
    IF m.iMySQL 0
    m.iErrorNo = aSQLError[1,1]
    m.cErrorMsg = aSQLError[1,3]
    MESSAGEBOX(“SQL Error #” + ALLTRIM(STR(m.iErrorNo)) + “:” + CHR(13) + CHR(10) + CHR(13)+CHR(10) + m.cErrorMsg, 16, ‘SQL Error’)
    ENDIF
    RETURN (m.iCnt > 0)
    ENDFUNC

  4. Raymond L. says:

    Looks like the error checking function’s code above got mangled. Basically, check value returned by SQLSTRINGCONNECT. If less than 1, there’s an error. Use AERROR() function to retrieve an array that includes error details. Look up AERROR in VFP’s help file for some guidance there if you need it. Once you have that information you can pass it on to the user, and exit.

Leave a Reply