Making FoxPro Ducky – Part 3 (Can SQLite The Way?)

To recap from Part 1, the goals of this rabbit hole project were to use DuckDB’s fast processing engine alongside an existing Visual FoxPro 9 (VFP) application, to take advantage of DuckDB’s import/query speed on large datasets (100M+ rows, ~2GB) while keeping VFP as the primary application layer. The primary technical obstacle was that DuckDB only ships a 64-bit ODBC driver, and VFP9 only supports 32-bit ODBC which ruled out a direct database connection.

If you have ever worked with FoxPro, Visual or otherwise, then you probably already know that FoxPro has a couple of physical limitations on the size of files and records that are pretty important. The VFP specifications say you can have up to a billion records in any table. However, the maximum size of any table is 2Gb, so the reality is the space issue is far more a problem than the record count. It has been posited by others that this limitation was imposed to prevent VFP from competing directly with Microsoft SQL. Who knows? What I do know is as a VFP programmer, if necessary, you can design your database with as many 2Gb tables as you want to try to get around this. Each VFP record can have up to 65,500 characters and a maximum of 254 fields.

DuckDB, on the other hand, has a completely different specification for tables and fields. Their website notes that they have users accessing databases which are over 2Tb in size. The columnar nature of this system means DuckDB has the ability to support up to 2,147,483,647 fields on a single record. Now, it isn’t often I run across client files this large, but I have on occasion run into files which exceeded the maximum number of fields (columns) VFP could support.

As a matter of fact, once upon a time, a long, long time ago, I worked on a project that included some files I needed to process with over 350 columns. At the time, I tried to read the file in one character at a time, manually parsing the data character by character, field by field, record by record and while it worked, it took forever. After tweaking all I could tweak on the process, I started to look outside of VFP for an alternative and at that time, I found SQLite. SQLite had a lot of features including I could create a batch file while in VFP, shell out to the batch file, and fire up SQLite, push commands into SQLite via the command line, to import really wide tables with lots of columns and create a persistent database on the fly to hold the data, then return to VFP and use the 32-Bit ODBC driver for SQLite to issue pass-through SQL statements to the SQLite database and get results in temporary cursors I could work with natively in VFP.

If you are starting to get a sense of deja vu at this point, so was I.

When this project started first started, I started a chat with Claude.AI, more or less using the AI as a reference document for DuckDB. I would want to do something, and I would go to Claude.AI and ask what the syntax would be in DuckDB. This was working great and because even though the documentation for DuckDB is extensive and very helpful, sometimes you need to know two pieces of information that are found in two completely different locations in the DuckDB documentation. So, using Claude.AI to ask questions, like “Is there anyone out there who has a third-party 32-bit ODBC Driver for DuckDB?” was like having a private research assistant.

So, I asked Claude.AI if DuckDb could create SQLite databases and was surprised to learn there was an extension you could add to DuckDB which would allow you read and write to SQLite databases. My idea was to still leverage DuckDB’s speed and combine it with SQLite’s 32-bit ODBC driver. This was fantastic news, because if I could use DuckDB to do the heavy lifting of importing into a SQLite database, then I could use the SQLite 32-Bit ODBC driver to issue SQL Passthrough commands to pull data into VFP. Claude.AI pointed me to the documentation I needed to work out the commands and once I got all of the syntax worked out, I created the first code where I imported data directly into a newly created SQLite database. I wanted to make it little bit more interesting, so I used a test file with 40 columns and almost 450,000 records to get a sense of how long something like this would take:

* Import CSV File and Save as SQLite Database
*********************************************
CLEAR
a = SECONDS()
TEXT TO lcCmdString NOSHOW
c:\duckdb\cli\duckdb.exe -c "INSTALL sqlite;LOAD sqlite;ATTACH 'C:/Duckdb/wages.sqlite' AS sqlite_db (TYPE sqlite);CREATE TABLE sqlite_db.wages AS (SELECT * FROM read_csv_auto('C:/duckdb/wages.csv'));DETACH sqlite_db;"
ENDTEXT
RUN &lcCmdString
b = SECONDS()
? "time: " + ALLTRIM(TRANSFORM(b-a)) + " seconds"

At this point, I was still using the command line switch to pass commands to the CLI, but you can see that there were multiple instructions in the command: Install sqlite, then load sqllite, attach a SQLite Database which didn’t exist (ie: create the database and make it persistent), create a table and insert all the records in the CSV file into the table using the Column Headers as field names. As you can see, I captured a starting time and an ending time and calculated the seconds it took to complete process. The results were astounding.

From start to finish, the total time was on average 2.5 seconds. I couldn’t believe my eyes. I ran that code snippet multiple times before I started to believe it. Even on a good day, I can’t get close to those numbers for importing data into a VFP table you can use for queries. Especially, if I type the fields and convert dates to dates and strings to numbers.

My heart was beating just a little bit faster now because I thought I saw the finish line. But reality was about to make a house call.

I was still getting the black box that flashed on the screen when I ran my commands, so I thought perhaps I could use Windows “WScript.Shell” to make the call to DuckDB because that would allow me to hide the DOS Cmd window and make this more of a background process.

I revised the code and got the following:

* Import CSV File and Save as SQLite Database
* However, this version hides the CMD window from User
******************************************************
LOCAL loShell, lcCmdString, lnResult
CLEAR
a = SECONDS()
loShell = CREATEOBJECT("WScript.Shell")

TEXT TO lcCmdString NOSHOW
c:\duckdb\cli\duckdb.exe -c "INSTALL sqlite;LOAD sqlite;ATTACH 'C:/Duckdb/wages.sqlite' AS sqlite_db (TYPE sqlite);CREATE TABLE sqlite_db.wages AS (SELECT * FROM read_csv_auto('C:/duckdb/wages.csv'));DETACH sqlite_db;"
ENDTEXT

* 0 = hide window
* .T. = wait until the command finishes
lnResult = loShell.Run(lcCmdString, 0, .T.)

IF lnResult <> 0

    MESSAGEBOX("DuckDB returned error code " + TRANSFORM(lnResult))

ELSE

	b = SECONDS()
	? "time: " + ALLTRIM(TRANSFORM(b-a)) + " seconds"

ENDIF

lcDSNLess = "DRIVER={SQLite3 ODBC Driver};Database=C:\DuckDb\wages.sqlite;"
lnConnection = SQLSTRINGCONNECT(lcDSNLess)

IF lnConnection > 0

	TEXT TO lcSQLCmd NOSHOW
	SELECT DISTINCT CAST([Pay Type Description] as varchar(40)) as ptype from wages  
	ENDTEXT

	SQLEXEC(lnConnection, lcSQLCmd, "PayTypes")
	
ENDIF

IF USED("PayTypes")

	SELECT paytypes
	BROWSE 
	
ENDIF

The process is the same as the earlier SQLite test (create a SQLite database, import a large CSV directly into a new table) but now I was using “WScript.Shell” instead of “RUN” to kick off the DuckDB process and hide the black DOS Cmd window from popping up. I also asked for some results to be pulled back into VFP via ODBC.

The ODBC query results were returned in a cursor in VFP and I was browsing a DISTINCT list of Pay Types in approximately 2.6 seconds.

Though I had solved the black box issue and I’d made a complete trip from VFP -> DuckDB -> SQLite -> VFP, the problem was now something I never anticipated. And it wasn’t until much later that I discovered with Claude’s help exactly what caused the problem.

Yes, I got results back into VFP but the field in the cursor created was a MEMO field. Not the Character typed field I was expecting. But a MEMO field. This meant for me to use any of the data in this temporary cursor, I’d have to convert the MEMO values to text or numeric or date or whatever. And nothing I did would change anything. I tried CAST to set the data types on the query. I tried giving field definitions in the import process and nothing I did made any difference. There simply was no way to get the imported data back in any other field type. Without typed fields, then I was still going to have to do a lot of converting on the fly which would slow the entire process down.

It was at this point after a couple of weeks of working on this project that I almost gave up. This was taking too long and I wasn’t getting the results I wanted and after thinking I was at the end, the goal posts just moved 100 years away again while I sat looking a bunch of MEMO fields.

At this point, I felt like perhaps I was chasing the wrong rabbit in this experiment.

I should also point out this was the turning point when Claude.AI began to change my mind about how to use AI effectively in programming (without asking Claude to write all of the code).