GuruGraffiti Writing On The Walls Of The Internet
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.
By the end of Part 5, I had worked with Claude enough to develop a command stack that actually accomplished my original goal albeit in a lot slower way than I had hoped. The stack I had in place at this point allowed me to use DuckDB’s exceptional import processing to build a persistent database and then make query results available inside of VFP. But, I felt like a lot of the time overhead it was costing came down to the fact my process was clunky, especially the need to create a new Job Command file for every query, and then firing off a new instance of DuckDB each time I needed to run a query against the DuckDB database. What I needed was a kind of mini client-server configuration with DuckDB waiting for requests from VFP and responding back to VFP with the results of the requests.
That could only mean one thing: Time to have another heart to heart discussion about abstract possibilities with my wingman, Claude.
I posted the following question to Claude in our continuing thread for this project:
I’m curious if there is any way my job file I’m instructing DuckDB to read repeatedly could be setup in such a way that I could issue updates to the job file without shelling out to new instances of the DuckDB CLI? Essentially, I’m trying to keep one instance of the CLI active and waiting on new instructions until the final instruction to kill the instance. Is this possible and if so, how?
Pretty vague and open-ended, but I was finding Claude often gave me bigger and longer answers to more open-ended questions and once again, Claude did not disappoint:
Short answer: DuckDB’s SQL dialect has no “wait for a file to exist” primitive, and the CLI’s dot-commands aren’t a scripting language with loops — so you can’t build the polling logic inside a job file that DuckDB reads. But your actual goal (one persistent DuckDB process, fed new work over time, killed on command) is absolutely achievable — you just need to move the “wait for new instructions” logic to something sitting outside DuckDB and feed commands into its stdin instead of having it open new files itself.
I bolded the key text I read in Claude’s response, because now I was seeing something I knew little about but knew I wanted to know more. Back in the old days when I first started programing I remembered the use of things like stdin and stdout with other languages, but it’s not something I normally had to work with these days and it certainly wasn’t top of mind for this project.
(I’m sure there are a thousand programmers out there who read the preceding sentences and are slapping their forehead, calling me an idiot for not knowing this stuff and that is ok. I know where my talent lies and so far, I have not needed this knowledge. Therefore, I’m documenting my stupid questions because somewhere out there is another programmer who needs to do something similar and now they won’t have to stumble around in the dark like I did. Call it my way of giving back to the programming community. If my stupid questions can help just one other programmer, then I’m happy to post them.)
Claude continued:
The -cmd (or -init) flag runs the given command/script but leaves you sitting at the interactive prompt afterward instead of exiting. That interactive prompt is just reading further SQL statements from stdin. That’s your hook: if something else keeps that stdin pipe open and writes more text into it over time, the same DuckDB process just keeps consuming statements — including more “.read somefile.sql” commands — until you send .exit or .quit.
Claude explained that by using stdin, I could even skip the Job Command files I’d been building all along and I could open one persistent DuckDB connection that hosts a Windows Named Pipe. Named pipes are bit agnostic and so it would allow my 32-bit VFP application send instructions directly to the 64-bit DuckDB command prompt. All I would need to do is use Win32 API calls to create a pipe and then send my commands through the pipe.
Sounds simple when you write it down.
According to Claude, “this method removes both the process-startup overhead and the file-polling delay, and it’s the same architectural pattern people use generally to bridge legacy 32-bit apps to 64-bit-only libraries. It’s more upfront engineering mainly because VFP’s pipe I/O has to be done via raw API declares (there isn’t a built-in FoxPro command for named pipes), but it’s a well-trodden pattern if you search for “VFP + CreateFile/WriteFile” named-pipe examples or existing FoxPro “shell execute with redirected I/O” class libraries (Rick Strahl’s older console-capture classes are a good reference for the general API-declare pattern, even though they target subprocess stdout capture rather than pipes specifically).”
Very cool. Claude was referencing Rick Strahl whom I knew through the VFP community. I’d even utilized some of Rick’s documentation and work in some other projects, but I was using DuckDB and VFP in my current stack and Claude was semi-suggesting adding a new piece in the stack and my concern was it would add a whole new layer of issues. But “Named Pipes” were kind of like Bigfoot to me anyway. I’d heard “Named Pipes” mentioned by other programmers at the proverbial water cooler, but honestly the work I’d been doing over the past 30 years never forced me into a situation where I needed to use Named Pipes before. The only pipes I’d worked with to this point were found in pipe-delimited files and I knew that wasn’t what Claude was talking about. Since I apparently left my ego at the login screen of Claude.AI, I didn’t mind admitting I didn’t understand so I asked Claude if this was like filling the Keyboard buffer to push commands into a program.
I’m pretty sure the Claude muffled a laugh first, but Claude immediately explained the difference between what I understood related to the keyboard buffer approach versus the real concept behind named-pipes. Again, it was like having an experienced programmer in the room with me explaining a complex concept to a noob with all the patience in the world.
Claude explained, “Every process has three predefined I/O channels (stdin, stdout, stderr) that are just file handles/descriptors, decided when the process is created. When you run DuckDB interactively in a terminal window, Windows happens to wire stdin to “read from the console/keyboard” — but that’s just the default. The DuckDB CLI itself has no idea whether its stdin is coming from a keyboard, a file, or another program. It just reads bytes from “whatever handle was given to me as stdin” until it sees a statement terminator.”
Claude told me that VFP’s RUN/SHELL commands which I had been using do not expose this plumbing — they’re just wrappers that just launch a process and optionally wait for it to finish. To do real stdin/stdout pipe redirection from within VFP itself, I’d have to drop down into the raw Win32 API.
I asked Claude if the code I’m using now: loShell = CREATEOBJECT(“WScript.Shell”) and then loShell.Run(lcShellCmd, 0, .T.) would expose the stdin pipe option to VFP? And Claude’s response was “.Run does not expose the pipes, but .Exec (a different method on the same WScript.Shell object) does.”
It turns out WScript.Shell also has an Exec(command) method, and it behaves very differently: it returns a WshScriptExec object with three properties — .StdIn, .StdOut, and .StdErr — each of which is a live TextStream connected to the child process’s actual stdin/stdout/stderr pipes. I could therefore, write a command to the .StdIn property and DuckDB would execute the command just as if I typed it into the DuckDB CLI.
Claude stated this method was callable from VFP just like the .run method with no API declares at all, since it’s plain COM and the logic would look something like this:
loShell = CREATEOBJECT("WScript.Shell")
loExec = loShell.Exec("duckdb.exe C:\path\mydb.duckdb")
* Feed a new job whenever you have one:
loExec.StdIn.WriteLine(".read C:\jobs\job_0042.sql")
* ... later ...
loExec.StdIn.WriteLine(".exit")But like everything else in this project so far. There’s a catch: because .StdOut and .StdErr are both active as well, then if something is written to either of those and no one reads them, the buffer will fill and my brand new persistent service will hang silently and I’ll never know (other than I’d never get a result).
The solution is to send the CLI’s own output-redirection dot-command: .output C:\jobs\duckdb_console.log at the beginning of my code and then periodically poll and empty the .StdOut queue periodically as a safety net. Just check loExec.Status (0 = still running) on a timer, and if there’s ever content pending, read it off with .StdOut.ReadAll() in small doses. I found out this is very helpful for another reason: It provides an error log file while you are developing your queries. If the process crashes and you get no results, then check the “C:\jobs\duckdb_console.log” and the reason for the crash will be recorded there.
I then went to work taking what I’d learned and I developed a proof of concept version of my project that used .StdIn to push multiple commands directly into the CLI and it worked beautifully. Almost.
This solution brought back my nemesis: the dreaded black box of the dos command that opened and now stayed open as long as the instance of DuckDB was active. Not a problem for me as a programmer, but I immediately began to think how that might confuse a user who was using an actual product. So, I headed back to convo with Claude. Claude’s response was direct:
You’ve run into a real, well-known limitation: WshShell.Exec simply doesn’t have a window-style parameter the way .Run does. Microsoft’s Exec API always launches the child with a visible console window — there’s no equivalent argument to pass. That’s the trade-off you made when you switched to .Exec to get the pipes: you gained stdin/stdout access but lost window-visibility control, because WSH’s designers didn’t expose both at once.
Gee, thanks Claude. You might have told me that in the beginning if it was so well known. But I guess it slipped your mind. So, now what? How do I get back to the hidden process with a persistent instances of DuckDB running with a pipe to StdIn so I can make this functional?
I won’t drag you all the way down with me for the next dead-end Claude suggested. The suggestion was that I run a process to find the command window when it opens and then hide it. After many attempts and lots of back and both, I came to the conclusion that this was not the answer and it was far too unstable and undependable to distribute to in an application.
However, I will tell you about an issue with one of the commands Claude suggested which included “&&”. This combination of two ampersands tells VFP that characters following the “&&” on the line are part of an in-line comment. But if you try to create a command string like this:
lcCmd = “cmd.exe /c title ” + lcTitle + ” && .\duckdb.exe .\SQLWork\datawork.db”
It will fail in VFP because it tries to process everything after the “&&” as a comment which breaks the line and causes a syntax error. Claude offered a more complex option, but my fix was even easier:
lcCmd = “cmd.exe /c title ” + lcTitle + ” &” + “& .\duckdb.exe .\SQLWork\datawork.db”
That separates the ampersands so VFP doesn’t trying to process the rest as a comment and the line works perfectly. I even think Claude was impressed when I documented the change. (Yes, I may have imagined that, but leave me my fantasies, ok?)
So far, in this episode I learned that we could force feed commands directly into the DuckDB CLI and I had a proof of concept test program that actually worked. Unfortunately, we still had the black command window to deal with, processing speed was still sub-par to my expectations and I was also going to have to challenge some of my biases against using AI to help you code solutions.
But, I’m going to save a lot of that for the next part.
