Trying to choose SQL API library

Question

I am just beginning to learn how to write software that accesses an SQL server. It seems that each server implementation (Postgres, MySQL, etc.) offers API libraries for various languages (my code is in C and C++, though solutions for Java and Python would also interest me). I'm a little wary of depending on these libraries, however, because I'd prefer a vendor-neutral solution.

As near as I can tell, Microsoft's ODBC API was meant to solve such problems for C/C++ (and JDBC for Java); unixODBC seems to be one popular implementation. Am I right even so far?

Moreover, do any such libraries provide an object-oriented interface? It would be nice to not simply embed SQL queries into another, more featureful language; I'd like to have a wrapper that mimics the style of the rest of the language, too.

So is there a preferred solution along those lines? Am I asking for something weird?

Answer 1

ODBC/JDBC attempt to abstract away the database interface to provide a consistent programming model. Bear in mind that, by using such a least-common-denominator interface, you cannot take advantage of specific, non-standard features that a given DB may offer.

To get an object oriented interface to your data model, look into Object Relational Mapping (ORM) solutions such as Hibernate. ORM solutions map your objects to their representation in a relational database, generally making data persistence much simpler from an application programming perspective.

Answer 2

Indeed, ODBC/JDBC are libraries that help make the calling interface standard between vendors, but you're right that each respective RDBMS has its own flavor of SQL. ODBC/JDBC doesn't help abstract the SQL syntax.

One solution to move literal SQL out of your application code is to implement queries in stored procedures that reside in each database back-end, and then use ODBC/JDBC to call the stored procedures. You can define stored procedures with similar names and calling interface for each flavor of RDBMS you use. But be aware that the stored procedure language is also variable from one vendor to the next.

Another solution is to use an "object-relational mapping" technology such as Hibernate for Java, or NHibernate for .NET. These technologies can make it feel more "object-oriented" to work with databases, and free you from writing literal SQL in many cases.

But most ORM tools tends to focus on very simple queries. If your query is at all complex (using a GROUP BY or a JOIN for instance), using the ORM tool is harder than using literal SQL.

See also "Good ORM for C++ solutions?"

If SQL troubles you that much, you're probably not going to be happy using an RDBMS at all. Some programmers don't see the value to the Rules of Normalization, for instance. If that's true for you, you might want to look into the emerging technologies for non-relational data stores, including:

• BerkeleyDB
• Project Voldemort
• CouchDB

Answer 3

As near as I can tell, Microsoft's ODBC API was meant to solve such problems for C/C++ (and JDBC for Java); unixODBC seems to be one popular implementation. Am I right even so far?

Yes. The equivalent of ODBC or JDBC for Python is called the DB-API. Perl's equivalent is called DBI.

Moreover, do any such libraries provide an object-oriented interface? It would be nice to not simply embed SQL queries into another, more featureful language; I'd like to have a wrapper that mimics the style of the rest of the language, too.

Yeah, there are a bunch of things like this for different languages. C# has LINQ, Smalltalk has Roe and GLORP, Python has SQLAlchemy and SQLObject (and Django in Python has quite a bit of query power built into its ORM (see Simon Willison's notes)), Ruby has ActiveRecord, and so on. I don't know what you'd use in C++ but I bet it has to use a lot of ugly template hacking to approach these.

All these choices might seem overwhelming, but chances are your choice of language will be shaped by something other than the convenience of working with relational data. (If not, you should consider Prolog.) That will probably tie you more or less to some ORM you hate just like the rest of us.