Query building in a database agnostic way

Question

In a C++ application that can use just about any relational database, what would be the best way of generating queries that can be easily extended to allow for a database engine's eccentricities?

In other words, the code may need to retrieve data in a way that is not consistent among the various database engines. What's the best way to design the code on the client side to generate queries in a way that will make supporting a new database engine a relatively painless affair.

For example, if I have (MFC)code that looks like this:

CString query = "SELECT id FROM table"
results = dbConnection->Query(query);

and we decide to support some database that uses, um, "AVEC" instead of "FROM". Now whenever the user uses that database engine, this query will fail.

Options so far:

• Worst option: have the code making the query check the database type.
• Better option: Create query request method on the db connection object that takes a unique query "code" and returns the appropriate query based on the database engine in use.
• Betterer option: Create a query builder class that allows the caller to construct queries without using any SQL directly. Once the query is completed, caller can invoke a "Generate" method which returns a query string approrpriate for the active database engine
• Best option: ??

Note: The database engine itself is abstracted away through some thin layers of our own creation. It's the queries themselves are the only remaining problem.

Solution:
I've decided to go with the "better" option (query "selector") for two reasons.

1. Debugging: As mentioned below, debugging is going to be slightly easier with the selector approach since the queries are pre-built and listed out in a readable form in code.
2. Flexibility: It occurred to me that there are some databases which might have vastly better and completely different ways of solving a particular query. For example, with Access I perform a complicated query on multiple tables each time because I have to, but on Sql Server I'd like to setup a view. Selecting from the view and from several tables are completely different queries (i think) and this query selector would handle it easily.

Answer 1

You need your own query-writing object, which can be inherited from by database-specific implementations.

So you would do something like:

DbAgnosticQueryObject query = new PostgresSQLQuery();
query.setFrom('foo');
query.setSelect('id');
// and so on
CString queryString = query.toString();

It can get pretty complicated in there once you go past simple selects from a single table. There are already ORM packages out there that deal with a lot of these nuances; it may be worth at looking at them instead of writing your own.