Re: [Cppcms-users] cppdb: getting the full query

[Artyom <art...@ya...>]

Hello,

> We almost never pass a ready-made query, but  use dynamic queries most of the 

> time.
> 
> When we  do:
>     cppdb::result res = sql << "SELECT * FROM user  WHERE id = ? << user_id;
> it's very useful in many circumstances to know  which user_id was actually 
> passed.
> Internally, cppdb must be creating the  full query before passing it to the 
> backend.
> I need to have the full  query with the replacements made:
>     "SELECT * FROM user  WHERE id = 123"

That is exactly what you are not going to get as CppDB uses almost anywhere
prepared statements, that means that query that is passed is actually:

   "SELECT * FROM user  WHERE id = ?"

And parameter was binded separately on the Specific DB API level, like 
PQexecParams
or PQexecPrepared

This gives significant performance boost (100%-200%) as statements are prepared 
only
once and the parameters are transferred independently.

So don't expect to make this helpful.

Also you should remember that the query itself is not globally available so for 
example
if result binding fails there is no way to get the query itself or in many cases
the prepared statement already lost the information about the original query
as it keeps its statement object rather then the query itself.

> 
> Moreover, in a real application, some queries can become  increasingly complex 

> and increasingly dynamic (e.g. see Drupal and some of  its API like 
> db_rewrite_query()...!) 
> Often, in order to understand why  we don't get the result we expect, we need 
> to have the very exact query as  run by the SQL server.

Generally it is good idea not to generate SQL queries on the fly but rather
create a set of statements you use and pass parameters via prepared statements.

>
> I don't understand the cppdb internals  and don't understand your first point 
> ("the query just does not exists or is  invalid").
> But surely, at some point you must be sending a full query to the  server, so 
> it should be fairly easy to retrieve  it.

This is exactly the point, it is not. It may become some server side object
and the call actually would look like:

exec prepared cppdb_stmt_3(123);

This library has very different internals in comparison to dbixx.

Artyom