MariaDB - Speeding Up Records Migration

I started testing MariaDB as my new backend and while it is simple enough to do the switch onto that, there are some things that I need to adjust on my VFP codes.  Some of those are the field types, for logical fields I have to use TINYINT (for MySQL ODBC 5.1 or later) or BIT (for MySQL ODBC 3.51), for dates it does not accept {} for an empty date, etc.

However, this post is not about that. It is more on the speed of migration approach.  Bear in mind I am new with this, only less than a week but I think regardless of it, this can help some who might be using MariaDB or MySQL as backends.

One way to connect from VFP to MariaDB is via SPT (SQL Pass-Through). With a code like this:

If SQLExec(gomyapp.DbBaseConn,'INSERT INTO MyTable (MyField) VALUES (?m.lcMyValue)') <= 0

      Aerror(laError)

      Messagebox(laError[2])

Endif

xBox on MySQL Backend

One of my subscribers is using MySQL as his backend so I requested him to provide a tutorial, as I do not use that backend myself, on how to use xBox on that one.  This guide can be useful to others too who has a different backend aside from that of VFP.  So here it is, it is quite simple actually:

Author:  Glenn Palic
Date: April 1, 2017

How to use xBox on MySQL Backend

1.  Make a connection with your mysql backend using ODBC in load event of your form

Example:

Public pnConnectionHandle,oConn

oConn= "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=Yourdb;Port=3306;uid=username;pwd=password;Option=3;"

SQLSetprop(0,"DispLogin",3)

pnConnectionHandle = Sqlstringconnect(oConn)

If pnConnectionHandle > 0

      *   successs

Else

      Messagebox("Can't connect to the Server please inform network administrator.",0+32,"Error")

      Quit

Endif

2.  As xBox uses InteractiveChange event, then do it like this now:

Local lcSQL, lcName

lcName =[%]+Alltrim(This.Value)+[%]

TEXT TO lcSQL NOSHOW

  Select LastName, FirstName, CustomerID From customer WHERE LastName like ?lcName

ENDTEXT

SQLExec(pnconnectionhandle,m.lcstr,"junk")

If !Eof()

      TEXT TO lcSQL NOSHOW  

            Select * From junk ORDER By 1 into Cursor junkemp NOFILTER

      ENDTEXT

      This._searchgrid(m.lcSQL,'junkemp.LastName','junkemp.CustomerID')

Endif

And that is it!

The only difference between using VFP table is it does not need that first transfer to junk above.  The second transfer to junkemp is necessary for the _SearchGrid method to kick in.  I guess there won't be too much difference too on another backend such as MSSQL.  Cheers!