Hi,
This sounds similar to something I'm working on at the moment. You can
still bind if you don't know the tablename and columns until runtime. The
way I've done it is roughly:-
std::string sql = "insert into tablename (col1, col2, col3)
values (:a, :b, ;c)"; // This sql is generated programatically
soci::details::prepare_temp_type preparedStmt =
dbConnection.prepare << sql;
for (unsigned int i = 0; i < numberOfColumns; i++)
preparedStmt, use(xxxxxx); // Put in whatever you want to use
here
statement st (preparedStmt);
st.execute();
Hope that helps.
Cheers
Andy
On 27 March 2014 04:11, Lalit Bhasin <lalit_...@yahoo.com> wrote:
> Hello Guys,
> I am facing some issue in performing insert operation containing escape
> sequence using soci library. Same DDL operation is successful when using
> mysql backend c library, To show the problem, I have written small
> ping-pong code as below:
> --
> #include <soci/soci.h>
> #include <soci/mysql/soci-mysql.h>
> #include <iostream>
> #include <istream>
> #include <ostream>
> #include <string>
> #include <exception>
>
> using namespace soci;
> using namespace std;
> char* escape_string( soci::session &sql, std::string &str) {
>
> soci::mysql_session_backend *mysql_backend =
> static_cast<soci::mysql_session_backend *>(sql.get_backend());
> const char *i_str = str.c_str();
> unsigned long i_length = str.length();
> unsigned long o_length = i_length * 2 + 1 ; //worst case where we
> need to escape all characters.
> char *o_str = (char *) malloc( o_length * sizeof(o_length)); //need's
> to be CLEANEDUP by calleee
> mysql_real_escape_string(mysql_backend->conn_,o_str,i_str, i_length);
> return o_str;
> }
>
> int main () {
>
> soci::session sql("mysql", "service=db user=scott password=tiger");
> std::string c1 = "word1'word2:word3";
> char *o_str = escape_string(sql, c1);
> std::string q = "INSERT INTO MY_TEST ( COMMENTS ) VALUE ('";
> q.append(o_str);
> q+= "')";
> try {
> sql << q;
> std::cout << " \nSOCI Insert successful";
> } catch (std::exception &e) {
> std::cout << "\n SOCI Exception : " << e.what() << "\n";
> }
> soci::mysql_session_backend *mysql_backend =
> static_cast<soci::mysql_session_backend *>(sql.get_backend());
> if (mysql_query(mysql_backend->conn_, q.c_str()) ){
> fprintf(stderr, "MYSQL Backend Lib error: %s\n",
> mysql_error(mysql_backend->conn_));
> exit(1);
> } else {
> std::cout << "\n MYSQL Backend Lib Insert successful\n";
>
> }
> --
>
> Here is what I am doing in above code:
> - Create an INSERT statement for table MY_TEST. The value for column
> COMMENTS contains special character ( single quote), which I am escaping
> using mysql_real_escape_string().
> - Do INSERT operation first using soci library and the using mysql
> client library. The INSERT operation is successful through mysql client
> library, but fails with soci as below:
>
> --clip------
> [shell]$ ./a.out
>
> SOCI Exception : 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 ''word1\'word2' at line 1
>
> MYSQL Backend Lib Insert successful
> ---clap-------
>
>
>
> When I check the exact insert statement received by mysql server. Server
> received different statements in both cases as below ( first row is for
> soci, and second row is for mysql client in below):
>
> ---clip--
> mysql> select event_time, argument from mysql.general_log where argument
> like '%INSERT%';
>
> +---------------------+-------------------------------------------------------------------------------------+
> | event_time | argument
> |
>
> +---------------------+-------------------------------------------------------------------------------------+
> | 2014-03-27 11:28:43 | INSERT INTO MY_TEST ( COMMENTS ) VALUE
> ('word1\'word2
> | 2014-03-27 11:28:43 | INSERT INTO MY_TEST ( COMMENTS ) VALUE
> ('word1\'word2:word3')
>
> ----------------
> --clap--
>
> As you see, soci has truncated some part of the statement before sending
> it to server , and hence DDL operation fails. While mysql client library
> has parsed the statement correctly and sent it to server.
>
>
> ---clip--
> mysql> select * from MY_TEST;
> +-------------------+
> | COMMENTS |
> +-------------------+
> | word1'word2:word3 |
> +-------------------+
> 1 row in set (0.00 sec)
>
>
> mysql> desc MY_TEST;
> +----------+--------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+--------------+------+-----+---------+-------+
> | COMMENTS | varchar(255) | YES | | NULL | |
> +----------+--------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
>
>
> --clap---
>
>
> I am not very sure if this is soci problem, or if I am doing something
> wrong here. Moreover I know the better way to escape the special characters
> is using placeholder/prepared statement. But above is just a test code. In
> actual scenario, the table name and column names (and number of columns)
> are know only during run time and it is not feasible to use placeholder
> queries or prepared statement.
>
> Please let me know if I am doing something wrong, or if it is known issue.
> Thanks
> in advance for help.
> Best Regards,
> Lalit
>
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> soci-users mailing list
> soci-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/soci-users
>
>
------------------------------------------------------------------------------
_______________________________________________
soci-users mailing list
soci-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/soci-users
