Trying to select a SQL API library
I am just starting to learn how to write software that accesses an SQL server. It seems that every server implementation (Postgres, MySQL, etc.) offers API libraries for different languages (my code is in C and C ++, although Java and Python solutions interested me as well). However, I am a little wary of depending on these libraries because I would prefer a vendor-neutral solution.
As far as I can tell, the Microsoft ODBC API should have solved such problems for C / C ++ (and JDBC for Java); It seems that unixODBC is one of the popular implementations. Am I still right?
Also, do any such libraries provide an object oriented interface? It would be nice not to just embed SQL statements in another, more functional language; I would like to have a wrapper that mimics the style of the rest of the language.
So, is there a preferred solution along these lines? Am I asking for something strange?
Indeed, ODBC / JDBC are libraries that help make the caller interface standard between providers, but you are correct that each respective DBMS has its own flavor of SQL. ODBC / JDBC does not help abstract SQL syntax.
One solution for moving literal SQL out of your application code is to implement queries in stored procedures that are found in every database in your database, and then use ODBC / JDBC to call the stored procedures. You can define stored procedures with similar names and calling interface for each RDBMS you use. But keep in mind that the language of the stored procedure also varies from one provider 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 more "object-oriented" for working with databases and free you from writing literal SQL in many cases.
But most ORM tools tend to focus on very simple queries. If your query is generally complex (like using GROUP BY
or JOIN
), using an ORM tool is more difficult than using a SQL literal.
See also Good ORM for C ++ Solutions?
If SQL bothers you that much, you probably won't be happy with an RDBMS. For example, some programmers don't see the meaning in the Normalization Rules. If this is true for you, you may need to look into emerging technologies for non-relational data storage, including:
source to share
As far as I can tell, the Microsoft ODBC API should have solved such problems for C / C ++ (and JDBC for Java); It seems that unixODBC is one of the popular implementations. Am I still right?
Yes. The Python equivalent of ODBC or JDBC is called DB-API . The Perl equivalent is called DBI.
Also, do any such libraries provide an object oriented interface? It would be nice not to just embed SQL statements in another, more functional language; I would like to have a wrapper that mimics the style of the rest of the language.
Yes, there is such a thing in different languages. C # has LINQ , Smalltalk has Roe and GLORP , Python has SQLAlchemy and SQLObject (and Django in Python has quite a lot of query power built into its ORM (see Simon Willison notes )), Ruby has ActiveRecord, etc. I don't know what you would use in C ++, but I'm sure there is a lot of ugly template hacking to do this.
All of these choices may seem overwhelming, but most likely your choice of language will be determined by something other than the convenience of working with relational data. (If not, you should consider Prolog.) This will probably connect you more or less to some ORMs that you hate, like everyone else.
source to share
ODBC / JDBC tries to divert the database interface to provide a consistent programming model. Keep in mind that by using this lowest common denominator interface, you cannot take advantage of the specific non-standard functionality that this database has to offer.
To get an object-oriented interface to your data model, consider Object Relational Mapping (ORM) solutions like Hibernate . ORM solutions map your objects to their representation in a relational database and generally make storing data much easier from an application programming perspective.
source to share