Making FoxPro Ducky – Part 2 (Can This Duck Swim?)

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.

The first place to start with any project is to determine whether the project is worth pursuing. So, I began with some rudimentary tests just trying to create a command in VFP and then to see if I could get the command to run in DuckDb.

The DuckDb CLI allows you to include commands as a -c parameter when starting up the CLI which is where I started. Later on, this will get a lot more sophisticated, but for now, I just wanted to see if some of the claims I saw on the DuckDb website were really true.

For the first test, I had a sample employee CSV file with about 1,000 records and 56 columns to play with, so I created a simple command to query the employee CSV file for some specific fields (using the column header names found in the CSV file) and then export those results to different file.

Note: It took me a while to get the simple command working partly due to the path notation. In Windows, path folders are delimited with backslashes “\”, but inside DuckDb path folders are delimited with a “/”. 

* Extract Selected Fields from one CSV file to a new CSV file
*************************************************************
TEXT TO lcCmdString NOSHOW
c:\duckdb\cli\duckdb.exe -c “COPY (SELECT emplid, prefix, fname, mname, lname, suffix, rstate FROM ‘c:/duckdb/empls.csv’) TO ‘c:/duckdb/test.csv’ (HEADER);”
ENDTEXT
RUN &lcCmdString

Ok, so let’s break down this code snippet:

First, you’ll see a VFP wrapper TEXT…ENDTEXT command. I like using this command because it let’s me build a command string with multiple parts in a way that let’s me visualize more clearly how the command should be structured without including all of the VFP required quotes, plus signs and semi-colons I’d normally have to use to build a string. Later on, this TEXT…ENDTEXT wrapper will become more important when I add the MERGE feature to TEXT…ENDTEXT which parameterizes the wrapper to build the string.

Next, comes the main command which calls the duckdb CLI and includes the “-c” parameter to send instructions directly into DuckDb when the instance starts. The command I’m using here is a DuckDb SQL command but you could also send any dot (.) commands to the CLI as well. You should also note you can include a series of SQL statements to the command string parameter since the semi-colon acts as a delimiter for each command. The command I am issuing in this case tells DuckDb to perform a SELECT statement directly on the empls.csv file, return just the 7 columns I requested from the 56 column CSV file I started with and then COPY the 1000+ records returned from the query to a new CSV file named test.csv. The “(HEADER)” tag tells DuckDb there is a header row and the names of the columns found in this header will be used as the field names in DuckDb.

Finally, after closing the TEXT block with ENDTEXT, I issue a RUN command in VFP which shells out to the command prompt and executes the lcCmdString created by the TEXT…ENDTEXT block just like a DOS batch file. When I ran the code, a black box flashed on the screen (a DOS command window which automatically closes as soon as the work is done) and then I’m back at a VFP prompt. This process took about 1 second on my computer and other than the disconcerting black box flashing on the screen, the only way I knew anything happened was by going to the folder were I told DuckDb to create the test.csv file and see if it existed and whether it contained the data I requested.

SPOILER ALERT: The test.csv contained exactly the columns I wanted to extract from the original empls.csv file and it happened so fast that if I had blinked when running this command I would have missed it. Without importing the original csv into a table, DuckDb read the CSV file, extracted the columns I asked for and then copied the results to a new CSV file.

For my next test, I wanted to test the JSON reading capability of DuckDb and I had a test JSON file containing one record with at least a hundred different fields. All I wanted to retrieve for my test was one field out of the hundred fields for that one record. The code for this test was remarkably similar to the CSV version above and it looked like this:

* Import JSON file and Copy to CSV File
***************************************
TEXT TO lcCmdString NOSHOW
c:\duckdb\cli\duckdb.exe -c "COPY (SELECT phone_number FROM 'c:/duckdb/test.json') TO 'c:/duckdb/jsontocsv.csv' (HEADER);"
ENDTEXT
RUN &lcCmdString

The only big differences between the two code snippets were a) the SELECT statement this time only requested the phone_number and b) the extension on the original filename changed from CSV to JSON. That’s an important point because DuckDb can contextually determine the file type from the extensions. That’s a pretty cool feature, but later on, you’ll learn that you can also provide a more dependable directive to DuckDb that makes the file extension irrelevant. This is even better because it is not unusual to see files with TXT extensions which might be delimited files or fixed field length files and depending solely on the filename extension would produce incorrect results.

SPOILER ALERT: Once again, when I executed the command in VFP, the black box flashed momentarily on my screen and the new file appeared in the destination folder just like I expected.

So far, I had established that DuckDb was as fast as they said it was, DuckDb could read and query both CSV and JSON file directly and export the query results to new files, and finally, I had established that I could call DuckDb from VFP and push a command out to make it so something.