[soci-users] Mixing prepared and static queries

Lalit Bhasin

2014-07-07 08:20:08 UTC

Hi All,
Just in case someone has idea on the issue mentioned in my earlier mail ? Is it not possible to use the same soci session for multiple prepared statements ( using soci::details::prepare_temp_type )

Thanks,
Lalit

On Friday, July 4, 2014 1:29 PM, Lalit Bhasin <***@yahoo.com> wrote:

Hi All,
I am getting error in mixing static and prepared statements ( using soci::details::prepare_temp_type ) on same sql session (connection). I am attaching the test code to reproduce the issue.

---
#include<soci/soci.h>
#include<exception>
#include<iostream>

int main() {

soci::session sql("mysql", "service=dbrepl user=scott password=tiger");
sql << " DROP TABLE IF EXISTS TEST_PREPSTMT ";
sql << " CREATE TABLE TEST_PREPSTMT ( ID INT ) ";

soci::details::prepare_temp_type prepStmt(sql.prepare << "INSERT INTO TEST_PREPSTMT VALUES(:val1)");

try{
sql << " INSERT INTO TEST_PREPSTMT VALUES(1) ";
} catch(std::exception &e) {
std::cerr << " First Insert failed. Error:"<< e.what() << std::endl;

}
try {

prepStmt, soci::use(2);
soci::statement stmt(prepStmt);
stmt.execute();
} catch (std::exception &e) {
std::cerr << " Second Insert failed. Error:" << e.what() << std::endl;
}

try {
sql << " INSERT INTO TEST_PREPSTMT VALUES(3) " ;
} catch (std::exception &e) {
std::cerr << " Third Insert failed. Error:" << e.what() << std::endl;
}

}

---

On running, I get below error:

$ ./a.out
Second Insert failed. Error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1
$

So, out of three inserts, second insert fails. On checking MySQL query log, server received below statements from the test application, and 4th statement (ie, second insert) is not correct:
---
292 Query DROP TABLE IF EXISTS TEST_PREPSTMT
292 Query CREATE TABLE TEST_PREPSTMT ( ID INT )
292 Query INSERT INTO TEST_PREPSTMT VALUES(1)
292 Query INSERT INTO TEST_PREPSTMT VALUES(1) 2
292 Query INSERT INTO TEST_PREPSTMT VALUES(3)
----

Please let me know if I am not using it correctly.
Thanks,
Lalit