SQL is complicated and strange and subtly different depending on the server, particularly for complex queries, which means anything that a user might find useful.

Glom built SQL queries internally, concatenating strings together with some helper functions. But that was still fragile and repetitive so I recently ported Glom to use the new GdaSqlBuilder API, via its Gda::SqlBuilder C++ wrapper. I don’t know of any similar open-source API for this in C or C++.

GdaSqlBuilder doesn’t completely hide the almost-free-form structure of SQL, but it does encourage you to only write queries that makes sense. As a bonus, you don’t have to worry about correct quoting, correctly representing values as text, correctly escaping that text, or correctly quoting table and field names. It should also hide most server-specific syntax, making it easier to port applications to different database servers. I’m happy to remove some of that awkward code from Glom.

Vivien Malerba was very responsive to my feedback, so I now think that the GdaSqlBuilder API is about as good as it can be in C. I think Gda::SqlBuilder is even nicer, thanks to C++ method overloading.

But it still doesn’t feel quite right – you can’t guess what the functions do without reading the documentation, many functions have similar names, and some functions can only be used on certain types of queries at certain times. I guess the Select and non-Select queries should be in separate (related) classes, instead of just mentioning “select” in the method name, but that would lead to annoying casting in C. Maybe I’ll do that in the C++ API.