Is C++ OTL SQL database library using parameterized queries under the hood, or string concat?

Question

I've been looking at the OTL (Oracle, Odbc and DB2-CLI Template Library) for C++ database access. I'm unsure of whether the query I pass in is converted to a parameterized query for the underlying database, or if it's basically just concatenating all the arguments into one big string and passing the query to the database that way. I see that the query you pass in to it can include type information for the arguments, but what happens between then and the query hitting the database, I can't tell.

Answer 1

OTL author's response to my e-mail:

OTL passes queries with placeholders into the DB API layers. The naming conventions for actual bind variables are different for different DB types. Say, for Oracle,

SELECT * FROM staff WHERE fname=:f_name<char[20]>

will be translated into:

SELECT * FROM staff WHERE fname=:f_name

plus a bunch of host variable bind calls.

For MS SQL Server, or DB2, the same SELECT would look like this:

SELECT * FROM staff WHERE fname=?

It's described in the manual that you can't have a placeholder with the same name more than once for MS SQL, DB2. SQL statements with placeholder / bind variables are relatively expensive to create, so if you instantiate an parameterized SQL via an otl_stream, it makes sense to reuse the stream as much as you can.

If you have more questions, or suggestions on how I can improve the OTL manual, feel free to email me.

Cheers, Sergei

pheadbaq wrote:

Hi, I've been evaluating C++ DB libraries recently to use as a base for an ORM library I wish to build, and have been gravitating more and more towards the OTL. It looks very nice by the way, and seems like it would meet most of the needs I have. I just have one lingering question that I can't seem to clarify by reading the docs. Does OTL pass a parameterized query on to the underlying DBMS, or is it concatenating the arguments and query I pass to the OTL stream into a single string and hand that to the DBMS?

In other words, if I hand OTL this MSSQL query, along with with the string "Bob" as the bind variable:

SELECT * FROM staff WHERE fname = :f_name<char[20]>

Does the OTL parser produce this:

SELECT * FROM staff WHERE fname = 'Bob'

Or this:

SELECT * FROM staff WHERE fname = @f_name

along with my string as a parameter

I've posted this same question to StackOverflow.com if you care to respond there: Is C++ OTL SQL database library using parameterized queries under the hood, or string concat?

Thank you for your time

Answer 2

The documentation talks all about bind variables. I assume that the library is rewriting your query, but it's probably just changing the format of the bind variables into the format your DBMS expects, and then binding the values to the bind variables.