As you likely know, VFP is a 32-bit application so it needs a 32-bit ODBC driver to access databases like Microsoft SQL Server and MySQL. However, some databases only come with 64-bit drivers (see Paul Tarver's account of getting data from DuckDB) or no longer support 32-bit drivers. I recently had a Stonefield Query customer who wanted to use a 64-bit driver for MySQL because the 32-bit drivers haven't been updated in a long time and likely have some security concerns in today's environment. I decided not to follow Paul's path but investigate a different route: using the 64-bit version of Visual FoxPro Advanced (VFPA-64).
A quick test confirmed that VFPA-64 could use a 64-bit driver to access a database. However, I didn't want to compile Stonefield Query in VFPA-64 because it uses a lot of 32-bit DLLs and ActiveX controls that don't have 64-bit versions available or would require installing and testing newer DLLs. The solution was pretty simple: write a small EXE in VFPA-64 that accepts a connection string, a SQL statement, and the path for a table, then call it from the 32-bit Stonefield Query to connect to the database, execute the query, and copy the resulting cursor into the specified table (it creates a database container with the same name and path as the table and uses COPY TO (tcResultTable) DATABASE (lcDatabase) in case there are long column names in the cursor). I used a table rather than XML, JSON, or some other mechanism to transfer the data between the two EXEs because it's much faster than doing conversion at both ends. I created the class that executes the query as OLEPUBLIC so it's a COM object I just have to instantiate once to avoid the performance hit of starting up an EXE on every query (yes, you can instantiate a 64-bit COM object in a 32-bit application as long as the COM object is an EXE rather than a DLL).
Now, with a quick configuration setup, customers can query databases with 64-bit ODBC drivers from Stonefield Query with very little performance impact. Some day, I may create a 64-bit version of Stonefield Query but for now I get the best of both worlds.
