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

Question

I was considering OTL (Oracle, Odbc, and the DB2-CLI Template Library) to access a C ++ database. I'm not sure if the query I'm passing is converted to a parameterized query for the base database, or if it basically just concatenates all the arguments into one big string and passes the query to the database this way. I see that the query you pass to it may include type information for the arguments, but what happens in the meantime, and the query falling into the database, I can’t say.

Answer 1

Reply of the author of OTL to my e-mail:

OTL passes aggregate queries to the database API levels. The naming conventions for the actual binding variables are different for different types of databases. 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 binding calls.

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

 SELECT * FROM staff WHERE fname=? 

The manual describes that for MS SQL, DB2 you cannot have the same repository with the same name. SQL statements with placeholder / bind variables are relatively expensive to create, so if you create parameterized SQL via otl_stream, it makes sense to reuse the stream as much as possible.

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

Cheers, Sergey

pheadbaq wrote:

Hi, I recently evaluated C ++ DB libraries to use them as a base for the ORM library that I would like to build, and is increasingly gravitating towards OTL. It looks like it looks very pretty, and it looks like it will satisfy most of my needs. I have only one lingering question, which I cannot clarify by reading the documents. Does OTL pass a parameterized query to the underlying DBMS, or concatenate the arguments and query that I pass to the OTL stream on one line and pass it to the DBMS?

In other words, if I pass OTL this MSSQL query along with the string "Bob" as a binding variable:

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

Does the OTL analyzer create this:

 SELECT * FROM staff WHERE fname = 'Bob' 

Or that:

 SELECT * FROM staff WHERE fname = @f_name 

along with my string as parameter

I sent the same question to StackOverflow.com if you want to answer there: Is the C ++ OTL SQL database library using parameterized queries under the hood or the string concat?

thank you for your time

Answer 2

The documentation talks about binding variables. I assume the library is rewriting your request, but probably just changing the format of the binding variables in the format expected by your DBMS, and then binding the values ​​to the binding variables.