Thread: Database Access

Ideally you would want some library that doesn't connect through ODBC. SQLAPI++ appears to be a good solution if it wasn't for the fact it costs around 250 USD.

SOCI seems to be almost ideal, only for MS SQL Server will it connect through ODBC.

Ideally you would want some library that doesn't connect through ODBC.

Why is this so?

Originally Posted by laserlight

Why is this so?

Have you read my mind?

Purely for performance reasons. ODBC adds another layer that can be bypassed if the server engine offers a direct connection through an API of their own.

An independent library that can take advantage of an existent API is always a bonus, I would say.

Originally Posted by Mario F.

Purely for performance reasons. ODBC adds another layer that can be bypassed if the server engine offers a direct connection through an API of their own.

An independent library that can take advantage of an existent API is always a bonus, I would say.

But he wanted the DBMS independent... And you're talking about DB server API. For me it's like using something DBMS dependent... Isn't it?

An indepent driver for DB access will use whatever mechanism that shortens the distance between the driver API and the DB Server.

Take a look at the JDBC. Particularly the second graphic that shows access to a Server that provides a client library with and without ODBC.

What happens is that the server provides an API. An intelligent independent library will try to use the API directly, avoiding the extra ODBC layer, unless of course the coder establishes an ODBC connection (or the server administrator doesn't make the API available).

For every server supported, the library driver can be designed to take advantage of the server API.

Every DBMS has to ship their own ODBC driver implementation. The difference is?

The important thing is that the choice is transparent to the user of the API. Which it is not quite, in JDBC (and AFAIK in ODBC), because there's always those pesky little SQL dialect issues.

He's looking for a library that can connect to several different servers. ODBC is not a library.

freeodbc++ is, as any of the ones he shown on his post.

Wow! Thank you all for your responses, the discussion turned out to be very helpful.

Just as a comment, after reading your posts i encountered with the following FAQ entry in the page of the DTL regarding ODBC, since this is the only method they use:

Do you have any plans to add support for native drivers?

Not any time soon. The primary reason for doing this would be performance. We have tried to encapsulate all of the ODBC calls in three classes called DBStmt, DBConnection and DBEnvironment so we could bind to a native driver should we so desire. In practice, this is not a priority for us since ODBC gives us good database portability and my belief is that, used properly, ODBC is just as fast (or faster!) than native drivers. The real key is knowing how to use the ODBC API effeciently by e.g. not re-connecting for each query, using bulk inserts to send large data sets, using forward only cursors to do simple reads and so on. As much as possible we have tried to use ODBC "best practices" in DTL so that we make efficient use of the API and code the uses DTL will be fast. We also provide a simple performance benchmark and "profile" project that we have used to track the overhead that DTL imposes above and beyond the ODBC API. Here are links to benchmarks that have been done to compare ODBC versus native drivers.

Overview of ODBC perfomance v.s. native drivers and list of references
ODBC v.s. native driver benchmarks
More discussion of ODBC versus native drivers

Thanks!

Glad we could help

However I can't but comment one passage of that quote:

In practice, this is not a priority for us since ODBC gives us good database portability and my belief is that, used properly, ODBC is just as fast (or faster!) than native drivers.

Assuming, 1. The Server ODBC implementation is not done through its API and 2. The server ODBC implementation is as fast (or faster!) than the server API.

Also, while interesting to take a look at, the tests shown are 12 years old, one of the links points to a 404 and the other discussion topic is 13 years old.

I'm not wanting to turn this into an against-ODBC diatribe, I certainly favor ODBC for its simplicity. But not only server APIs are much more refined these days with a strong demand for direct server access being a mainstream reality, but also ODBC is quiet often implemented in function of the server APIs creating an extra layer between the client application and the server.