FoxSQL: teaching AccountView to speak SQL without making ODBC the landlord

The idea started with a simple but dangerous question: can we make AccountView data feel like a normal database connection from C#? Not "export a CSV and pray." Not "install another generic provider and pretend 1998 is fine." A real client API: open a connection, send SQL, bind parameters, receive rows, page through large result sets, and inspect diagnostics when the machine starts sweating.

That question matters because many business systems are full of valuable FoxPro data that still runs the company just fine, while the software around it has moved on to web dashboards, mobile screens, APIs, services, queues, and developers who get nervous when a production integration depends on a mapped drive and good vibes.

The first decision was the most important one: FoxSQL does not try to become a new FoxPro. It does not reimplement DBF, CDX, FPT, locking, buffering, or the many small rules that live inside AccountView. Instead, the service keeps Visual FoxPro in the execution path. FoxSQL provides the modern bridge; Fox keeps ownership of the data behavior. This is less glamorous than inventing a database engine, but dramatically less likely to ruin somebody's accounting day.

What FoxSQL can do today

The shape of the bridge

The working architecture is deliberately pragmatic: a C# application talks to FoxSQL.Client; the client opens a pooled TCP connection to FoxSQL.Service; the service serializes access to the Fox runtime and executes the approved SQL route. Visual FoxPro remains the engine for complex queries and data mutation. C++ handles the low-level Windows and Fox-facing reality, because 32-bit COM does not become elegant just because we ask nicely.

That separation matters. C# never directly mutates DBF/CDX/FPT files. The bridge avoids a COM call per row. Expensive Fox work is measured. Writes are behind configuration gates. And when a screen needs page 17 of a large sorted result, FoxSQL can fetch a window of rows instead of rebuilding the universe for the seventeenth time.

How the build actually happened

The process was not a straight line, which is usually how you know it touched real software. The early phase was a feasibility spike: prove that FoxPro and AccountView could be reached, prove that data could move without OLE DB or ODBC, and prove that the service could keep a runtime alive instead of doing expensive startup work per statement.

From there the project grew in layers: first the protocol and C# client contract, then result serialization, then SQL classification and parameter binding, then paging, mutation rules, RECNO-specific update/delete hot paths, result-cache invalidation, connection pooling, shared Fox state scrubbing, buffer reuse, bulk DBF result reading, and finally explicit server-side cursors. In other words: the fun kind of plumbing, where every millisecond has a name tag.

The benchmarks shaped the design. A naive WHERE RECNO() = ... update can turn into an accidental table walk if it takes the wrong route. FoxSQL added dedicated record-number paths that use native record positioning for the cases where the application already knows the record. That is not premature optimization; that is seeing the pothole, labeling it, and putting a small bridge over it.

What comes next

The next step is to make FoxSQL useful beyond .NET applications. The C# client is the first serious interface because it gives us a proper database-shaped contract: connection strings, commands, parameters, adapters, readers, cursors, and predictable error handling. But the bridge is not meant to stop at desktop or service-to-service C# code.

A direct JSON response mode is planned so callers can ask FoxSQL for data and receive clean JSON without first turning everything into a DataTable. That opens the door for lightweight integrations, admin panels, mobile endpoints, and web tooling where JSON is the natural shape of the conversation. The binary rowset path can stay fast for heavy clients; JSON can become the friendly front door for everything else.

That also means PHP websites can become first-class consumers. A PHP site should be able to call FoxSQL, run an approved query, bind parameters, and render live AccountView data on a page without touching DBF files, installing ODBC drivers, or teaching the web server ancient desktop rituals. The website asks for data; FoxSQL handles the FoxPro reality; AccountView remains the source of truth.

At that point FoxSQL becomes what the name has been aiming at all along: a real SQL server layer on top of FoxPro and AccountView. Not Microsoft SQL Server, not a fake database-shaped costume, but a genuine server process with a protocol, authentication, query validation, execution policies, diagnostics, result formats, and multiple client types. Old data, modern access. Sensible boundaries. Fewer late-night rituals involving driver installers.

The punchline

FoxSQL is interesting because it does not pretend legacy data becomes modern by wrapping it in fashionable vocabulary. It respects the old engine, contains the dangerous parts, and gives modern applications a disciplined way in: pooled connections, parameterized commands, rowsets, diagnostics, paging, cursors, JSON-ready integration paths, installer tooling, and a Windows service that can be validated instead of wished into production.

The result is a bridge with just enough attitude: FoxPro keeps doing FoxPro things, C# gets a proper database-shaped API, PHP gets a clean route to live business data, and ODBC can finally stop being blamed for every slow query in the building.