Making FoxPro Ducky – Part 4 (My New Wingman Claude)

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.

At this point in my DuckDB project, I have to admit I was pretty discouraged. So far, every attempt I made to use DuckDB to do the heavy lifting of importing very large files had ended rather dramatically in a dead-end. With no 32-bit ODBC driver to DuckDB databases, and apparently no way to get typed fields returned in a cursor to VFP via SQLite, this is when it got weird. But is was also when I learned not to hate using AI in programming.

On a regular basis over the past year or so, I had been reading articles about things like “vide coding” where people who don’t know how to program, use AI to write a bunch of code that seems to work on the surface, but often contains lots of bugs. In my own experience trying to re-factor old code with another AI tool, the code generated by the AI was often buggy and I spent as much time debugging the AI refactored code and making it work as it would have taken to completely rewrite the code manually from the ground up. As someone who has been writing code since I was 13 years old (trust me, it’s been a long time!), I haven’t developed enough trust in AI to commit to using it for anything critical in my work.

And yet.. at the beginning of this drawn-out story, I told you I wanted to give Claude.AI a chance based on the rave reviews I was hearing from other programmers in my realm who had personally used the tool to great affect according to them. So, when I began this experiment, I decided I would try to use Claude.AI, if for nothing else, then as a glorified research assistant. Turns out my experience so far with Claude.AI kind of like having another programmer in the room with me who had knowledge in areas I did not. For example, I was trying to accomplish something outside the box with DuckDB and while I’m pretty sophisticated when it comes to FoxPro capabilities, commands and functions, I was a complete novice when it came to DuckDB. I have mentioned previously that the design of the DuckDB documentation is extensive and helpful but only if you know where to find the information you need and you know exactly what you are looking for. It’s like trying to look up a word in the dictionary to learn how to spell it.

Turns out Claude.AI is not just a great research assistant when trying to figure out whether a command exists or the syntax to use, but it is also excellent at providing relevant information beyond the original question. Sometimes, the extended part of Claude’s answers would spark an idea I had not considered originally. The ability to chase an theory in a conversational way, bouncing ideas off Claude to find possibilities was a game changer. In a bit of foreshadowing, an extended conversation with Claude.AI would ultimately give me the key to this entire puzzle.

After the MEMO field failure of using SQLite, I put all of this project aside to “let it simmer” and after a couple of days, a germ of a new idea began to form:

If I can’t return typed data fields directly to cursors in VFP, could I do so indirectly via CSV result files?

The gist of the idea was what if I fired off an instance of DuckDB, created a persistent database (rather than the in-memory databases I’d created so far), import multiple files in to multiple tables, then close the instance leaving the database behind. Then, fire off another instance of DuckDB, open the existing database, issue a complex SQL query with joins and post the results to a CSV file. Then, return to the VFP environment to import the CSV file of results into a VFP cursor. By making the DuckDB database persistent, I could then run as many queries against the database as needed and get the results back into VFP using it’s native import capabilities.

You may recall in Part 3, I mentioned Claude had noted while you can send multiple SQL commands into DuckDB through the CLI command switch “-c”; you can also push “dot commands” which give you access to CLI console commands like .cd, .import, .log,  .open, .quit, and .read. The .read command became critical to this next phase because the length of the SQL strings I wanted to push into DuckDB was quickly becoming unwieldy using only the command line.

Claude and I discussed my thoughts on this new idea at length and essentially, Claude confirmed my idea was sound and offered several caveats I should watch out for going forward. Between the two of us, we came up with the following outline of a process:

1. Start a VFP program
2. Init a shell object with WScript.Shell
3. Delete any existing work files
4. Init a variable to hold the path to the import files
5. Use TEXT…ENDTEXT MERGE to create a SQL command string to create multiple tables and import multiple files from the path into the different tables.
6. Save the SQL Command String to a Job Command File
7. Shell out to DuckDB and use the auto-create feature of DuckDb to create the database and then use the -c command line switch to direct DuckDB to .read the Job Command file
8. Return to VFP once DuckDB finishes the Job Command File
9. Use TEXT…ENDTEXT MERGE to create a SQL SELECT statement with a JOIN & a WHERE clause and then export the Query results to a CSV file.

After debugging and testing, here’s the VFP code which accomplishes steps defined in the plan outline above.

* Import Multiple CSV Files and save as DuckDB Persistent Database
* Then issue a SELECT statement with JOIN as second repeatable Job
* and save results to a standard CSV file suitable for importing into VFP
*************************************************************************
LOCAL loShell, lcCmdString, lnResult
SET SAFETY OFF

CLEAR
a = SECONDS()
loShell = CREATEOBJECT("WScript.Shell")

DELETE FILE "./SQLWork/datawork.db"
DELETE FILE "./SQLWork/datawork.db.wal"
DELETE FILE "./SQLWork/job01.sql"
DELETE FILE "./SQLWork/job02.sql"

lcImportPath = "T:/DataWorks/TestData/"

TEXT TO lcSQLCmd TEXTMERGE NOSHOW
CREATE TABLE empls AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Employee Export 01.csv'));
INSERT INTO empls (SELECT * FROM read_csv_auto('<<lcImportPath>>Employee Export 02.csv'));

CREATE TABLE earns AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Q1 Earnings.csv'));
INSERT INTO earns (SELECT * FROM read_csv_auto('<<lcImportPath>>Q2 Earnings.csv'));

CREATE TABLE deducts AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Q1 Deductions.csv', types={'Document Identifier': 'VARCHAR'}));
INSERT INTO deducts (SELECT * FROM read_csv_auto('<<lcImportPath>>Q2 Deductions.csv', types={'Document Identifier': 'VARCHAR'}));

CREATE TABLE taxes AS (SELECT * FROM read_csv_auto('<<lcImportPath>>Q1 Taxes.csv', types={'Document Identifier': 'VARCHAR', 'Hours Worked': 'VARCHAR'}));
INSERT INTO taxes (SELECT * FROM read_csv_auto('<<lcImportPath>>Q2 Taxes.csv', types={'Document Identifier': 'VARCHAR', 'Hours Worked': 'VARCHAR'}));
ENDTEXT

STRTOFILE(lcSQLCmd,"./SQLWork/job01.sql")

lcShellCmd = '.\duckdb.exe ".\SQLWork\datawork.db" -bail -c ".read ./SQLWork/job01.sql"'

lnResult = loShell.Run(lcShellCmd, 0, .T.) && 0 = hide window; .T. = wait until the command finishes

IF lnResult <> 0
    MESSAGEBOX("DuckDB returned error code " + TRANSFORM(lnResult))

ELSE

	b = SECONDS()
	? "Job #1 Time: " + ALLTRIM(TRANSFORM(b-a)) + " seconds"
ENDIF

TEXT TO lcSQLCmd TEXTMERGE NOSHOW
COPY (SELECT 
"t"."Company Name",
"t"."Employee Number",
"e"."Employee SSN/SIN",
"e"."Employee First Name",
"e"."Employee Middle Name",
"e"."Employee Last Name",
"e"."Employee Name Suffix",
"e"."Pay Class Name",
"e"."Pay Type Name",
"e"."Employment Status Name",
"t"."Check Date",
"t"."Pay Type Description",
"t"."Payroll Tax Name",
"t"."Payroll Tax Description",
"t"."Tax Authority Name",
"t"."Is Employee Tax",
"t"."Is Employer Tax",
"t"."Tax Amount",
"t"."Curr Wage Basis",
"t"."Subject Wages",
"t"."Gross Wages",
"t"."Work State Code",
"t"."Is Voided"
FROM taxes t
LEFT JOIN empls e
ON "t"."Employee Number" = "e"."Employee Number"
WHERE "t"."Employee Number" = 35183
) TO './SQLWork/queryresults.csv' (FORMAT csv, DELIMITER ',', HEADER);
ENDTEXT

STRTOFILE(lcSQLCmd,".\SQLWork\job02.sql")

lcShellCmd = '.\duckdb.exe ".\SQLWork\datawork.db" -bail -c ".read ./SQLWork/job02.sql"'

lnResult = loShell.Run(lcShellCmd, 0, .T.) && 0 = hide window; .T. = wait until the command finishes

IF lnResult <> 0
    MESSAGEBOX("DuckDB returned error code " + TRANSFORM(lnResult))

ELSE

	c = SECONDS()
	? "Job #2 Time: " + ALLTRIM(TRANSFORM(c-b)) + " seconds"
ENDIF

NOTE: The VARCHAR definitions on certain lines of the DuckDB import commands are necessary because sometimes DuckDB has trouble automatically detecting the field types. You can define every column if you want, or you can do like I’ve done here to just fix the columns that DuckDB needs help with.

The import files contained a total of 1,726,871 records and the job01 creation of the database and importing all of the records into multiple tables took 19.549 seconds while the SELECT statement with the export to a CSV file in job02 took a total of 1.672 seconds. While the import part of the process was still producing respectable results averaging an import time of 0.000011 seconds per record, the SELECT statement & Export to CSV totaled over 1.5 seconds to return the results for a single employee. There were over 30,000 employee records in my test dataset, so assuming I looped through the full list of employees and issued the job query for each employee the total time would work out to almost 14 hours to finish. That’s not including whatever processing I wanted to do with each result inside of VFP. 

Once again, I’d run into a situation where I have proven that what I want to do is technically possible but not feasible in a practical sense.

I went back to Claude and documented the latest results. The discussion that ensued had both of us trying to determine the exact cause of the speed problem. I did several tests Claude suggested and ultimately we came to the conclusion that the overhead of firing off a new instance of DuckDb for job02 made up a significant portion of the time I was seeing to get the results to the CSV file.

Once again, my frustration with the lack of an efficient solution made it clear I needed to put this project on the back burner for a couple more days.

After I’d had some time to decompress and think about what I had accomplished, so far, I decided to return to Claude, open a brand new chat and begin anew. I summarized the progress or lack thereof like this:

I want to utilize DuckDB with Visual Foxpro 9.0. Since DuckDB has a 64bit ODBC driver and VFP only supports a 32bit ODBC driver, I have worked out how to build a stack of commands in a text variable, save the stack to a text file, then shell out of VFP to run the DuckDB CLI with a command to read and execute the command stack in the text file I created. Results are dumped to a CSV file which I can then import back into VFP for further processing. This works and the import is very speedy, but obviously, I want to get data back into VFP in the most efficient manner. Am I missing something? Is there another way to link these two tools together so they can use a shared database? I tried exporting results from DuckDB to a SQLite.db file, but when I query that file from VFP, everything comes back in MEMO fields. I need something more elegant to take advantage of DuckDB’s data processing speed and then access the data generated by DuckDB from within Visual Foxpro. Any thoughts on this?

Claude’s response was as fast as it was shocking:

Good news: you’re not missing an obvious trick — this is a genuinely awkward interop case because of the 32-bit/64-bit ODBC split, and what you’ve built (shell out, write a command stack, dump CSV, APPEND FROM) is actually close to the standard pattern people use for this kind of bridge. But there are a few things that could meaningfully speed up or clean up the last mile.

Why your SQLite attempt gave you memo fields: This is almost certainly an ODBC driver metadata issue, not a VFP limitation. When DuckDB exports to SQLite, columns typically end up with TEXT affinity and no declared length. Most SQLite ODBC drivers report undeclared/unlengthed TEXT columns as SQL_LONGVARCHAR with a very large or unbounded length. VFP’s remote view/ODBC layer maps any character column over 254 bytes to Memo — so it’s doing exactly what it’s told, based on bad metadata.

A faster native path: skip CSV parsing, use SDF: APPEND FROM … TYPE CSV in VFP is already reasonably fast, but it still has to tokenize delimiters/quotes and guess or coerce types. If you pre-create the target DBF with exact field definitions (rather than letting VFP infer types from CSV), and have DuckDB write fixed-width output instead of CSV (using lpad(), rpad(), format() in your DuckDB SELECT to pad each column to a known width), you can then use VFP to APPEND FROM … TYPE SDF. Fixed-width import in VFP skips delimiter parsing entirely and is typically the fastest text-based bulk load VFP supports. The tradeoff is you have to be disciplined about column widths matching your DBF field widths exactly (including sign/decimal handling for numerics and date formatting), but for a repeated ETL pipeline that’s a one-time template to build.

Worth investigating: DuckDB’s spatial extension can write DBF directly: DuckDB’s spatial extension wraps GDAL/OGR, and GDAL has an OGR driver that writes plain DBF (independent of shapefiles).

Hold up!

What?

DuckDb can write directly to DBF files? Say What?

So, where was this little tidbit in the documentation? Turns out it was not in the DuckDB documentation. Nor was it part of the documentation of the SPATIAL extension documentation. It was not even in the GDAL Integration documentation for the SPATIAL extension. No, it was all the way down the chain to the ESRI Shapefile documentation implemented by the GDAL Integration in the DuckDB SPATIAL extension.

There simply is no way I would have ever found this possibility without Claude.AI.

Based on a Q&A with my new wingman, Claude, it was clear the next trick needed to be figuring out how to make DuckDB import a bunch of data and then export that data into DBF files which I could open natively in VFP for further processing.

Woohoo! I think I have finally found the break-through I have been wanting.

Or, at least, so I thought…