search

SOCI / soci

Official repository of the SOCI - The C++ Database Access Library
http://soci.sourceforge.net/
Boost Software License 1.0
1.42k stars 478 forks source link

SQLite3 does not support multiple SQL statements in one call #674

Open M-Fabian opened 6 years ago

M-Fabian commented 6 years ago

The SQLite3 backend does not support multiple SQL statements in one call, e.g. CREATE TABLE Table1(Foo TEXT);CREATE TABLE Table2(Bar TEXT); Only the first statement is processed. I went into the debugger and found the reason:

soci-3.2.3/backends/sqlite3/statement.cpp, line 57: The tail of sqlite3_prepare_v2 is ignored. Hence only the first SQL statement in the string is processed. The PostgreSQL backend does this correctly, so I consider this a bug of the SQLite3 backend. The backend should process the given string until an error occurs or the tail is empty.

vadz commented 6 years ago

Interesting, I had no idea that multiple statements per call were supported, to be honest. Is there any advantage in supporting it, compared to just telling people to use 2 statements?

M-Fabian commented 6 years ago

Calling the database just once instead of several times is always advantageous because it saves unnecessary function calls. On top of that, big databases like PostgreSQL or MySQL will surely take advantage of the fact that they know the whole sequence of commands. More context always gives room for optimizations. Especially if the statements consist of something with "BEGIN;...;COMMIT;".

As I said, the PostgreSQL backend does support multiple statements per call, so I think people would expect the same for SQLite3. Simply ignoring part of the input can't be right ;-)

vadz commented 6 years ago

In case of SQLite it doesn't seem to save anything, however, does it? I.e. the only argument for doing it for it that I see is compatibility with PostgreSQL and it's indeed a good argument, I agree. BTW, do you know what happens with the other backends, e.g. ODBC (with MS SQL) or Oracle? We really should start by adding a unit test for this functionality to know where does it work and where it doesn't.

I also wonder about what would happen if any of these statements had input or output parameters, does PostgreSQL handle this too?

Anyhow, I agree that ignoring input is not right, but the simplest fix would be to just throw an exception if there is any unparsed input. But if you'd like to implement something better, I don't really object to it, I am just not sure if it's really that useful.

AndrewRademacher commented 6 years ago

@M-Fabian, I also need this feature for use in a migration management tool. Did you have a particular solution for this in mind? I'm also happy to start digging around to help find a solution.