Parameter binding for connection::execute about sqlpp11 HOT 10 CLOSED

Additional link: https://github.com/jansen-oss/tools/tree/master/cpp/sqlite

from sqlpp11.

Hmm, it would be quite easy in the connector libraries, e.g. in the sqlite connector, just like the code sample you sent.

Since you're suggesting to add this to sqlpp11, do you have a syntax in mind for sqlpp11? For instance to ensure that the number of placeholders and the number of parameters match?

from sqlpp11.

Yes, I understand your concerns. Without an additional parsing pass to validate the number of parameters specified in the string, it would not be type safe. I suppose a masochist could do this generically with something like Metaparse, but that seems out of the scope for sqlpp11. Otherwise, it is only possible to get the parameter count via database API calls (like sqlite3's sqlite3_bind_parameter_count(sqlite3_stmt*)) at runtime, meaning they would not be type safe in terms of arity.

from sqlpp11.

Right, Metaparse and similar have been suggested by others and I have thankfully declined :-)

Hmm, but this gave me an idea: We could have a patchwork query. It consists of parts that may be strings or sqlpp11 expressions or parameters (if it should be used as a prepared statement). So your example would be:

db(sqlpp::patchwork("PRAGMA writeable_schema = ", sqlpp::value(writableSchema)));

Or I could have something like:

sqlpp::patchwork(select(all_of(tab).from(tab), " special feature ", where(tab.alpha == 17));

What do you think? I've been thinking about allowing arbitrary code for quite some time now. This might be the way to do it. I might even be able to deduce the return type this way...

from sqlpp11.

This would indeed be handy. It seems that some care would need to be taken when introducing ad hoc fragments into the expression tree structure. It would close the door to (or at least complicate) the addition of any expression optimization or database-specific expression tree restructuring. Also, any addition of ad hoc strings eliminates type safety.

A related issue I am encountering is that I need to get row data out of arbitrary commands which do not represent tables under my control. An example is that we rely on output from sqlite3 pragmas. E.g., PRAGMA TABLE_INFO(my_table) returns row results. It would be fine for me to externally specify the table structure of the result as I expect it, but currently there is no mechanism to get results back that I'm aware of.

An alternate mechanism for performing such database specific mechanisms (aside from extending the connector API) is to simply provide access to the raw connection object. There is precedent for this, for example in Asio which allows access to the native_handle() pointer.

from sqlpp11.

On 2014-10-01 19:46, Thomas marsh wrote:

This would indeed be handy. It seems that some care would need to be
taken when introducing ad hoc fragments into the expression tree
structure. It would close the door to (or at least complicate) the
addition of any expression optimization or database-specific
expression tree restructuring. Also, any addition of ad hoc strings
eliminates type safety.

Yes, such a function would require extra care by the developer, similar
to the already existing verbatim(). And using it would have the
impacts you mentioned.

On the other hand, this function would hopefully be used only when there
is no alternative.

A related issue I am encountering is that I need to get row data out
of arbitrary commands which do not represent tables under my control.

Out of curiosity, why aren't these tables under your control?

An example is that we rely on output from sqlite3 pragmas. E.g.,
|PRAGMA TABLE_INFO(my_table)| returns row results. It would be fine
for me to externally specify the table structure of the result as I
expect it, but currently there is no mechanism to get results back
that I'm aware of.

Hmm. Yes, you would need to have something similar to the
select_column_list if you wanted to do something like this with a
patchwork query. Would not be hard to do, but might result in slightly
awkward code, e.g.

for (const auto& row : db(patchwork(columns(all_of(tab_table_info)),

"pragma table_info(", tab, ")")))
{
// ...
}

Actually, I think it would be better to add a specific method for
getting the table info in the sqlite3 connector library. Something like

for (const auto& row : db(sqlpp::sqlite3::table_info(tab)))
{
      // ....
}

I haven't looked into this topic in any detail yet, but it would be
interesting for other databases, too, of course.

An alternate mechanism for performing such database specific
mechanisms (aside from extending the connector API) is to simply
provide access to the raw connection object. There is precedent for
this, for example in Asio which allows access to the |native_handle()|
pointer.

Right, that is always possible, of course.

Best,

Roland

from sqlpp11.

Thanks for all the dialogue around this, Roland.

The table that is not under my control is master. Of course, it is quite common that users have to interact with master (or equivalent), or otherwise depend on system stored procs / pragmas. These are cases where the structure of the result is known, but it would be unwise to document the entire table structure statically within a client program.

Adding utility methods to the database connector are of course an option. In the short term I will experiment with working on the raw db handle, and take some time to determine what utility methods on the database connector would make sense. (E.g., list of tables, test table existence, table info, etc.)

The patchwork approach would be a welcome addition to handle these cases without requiring modifications to the library. That would be a good workaround for users unable to contribute the the sqlpp11 development directly.

from sqlpp11.

You're welcome and thanks for your input!

OK, so I'll work on the patchwork method next. That might take a few days though, and I am open to suggestions regarding the name of that method :-)

Regarding utility methods, some of them would make sense to offer in sqlpp11, e.g. getting a list of tables in the database or the list of columns in a table. The implemtentation would depend on the connector, of course.

from sqlpp11.

Hi,

It took much longer than expected, but I now have a first version of the custom_query (aka patchwork) in the develop branch. If you want to test with sqlite3, you will need to use the develop branch of the sqlite3-connector as well.. See the tests/CustomQueryTest.cpp for some code examples.

I haven't gotten around to test all use cases we discussed, so I guess there are a few things missing. Please let me know, when you stumble over something.

I know that not all statement member functions have free function equivalents that could be used with custom queries yet. I'll add those next...

Have fun,

Roland

from sqlpp11.

custom_query is now usable since release 0.30

from sqlpp11.