Re: [Cppcms-users] cppdb::statement copy and assign

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

See when you bind a string value it should remain valid until exec is called.

http://cppcms.com/sql/cppdb/classcppdb_1_1statement.html#a7b96bac916e748aa2a5cff49d3f615c8

Because cppdb does not copy strings to prevent needless data copy so
what happens that set_values and where_values are get destroyed when
you leave create function and their references are not valid.


Artyom

----- Original Message -----

> From: CN <cn...@fa...>
> To: cpp...@li...
> Cc: 
> Sent: Thursday, November 12, 2015 11:53 AM
> Subject: Re: [Cppcms-users] cppdb::statement copy and assign
> 
> Because the SQL is dynamically generated, I called
> cppdb::session::create_prepared_uncached_statement() instead of
> cppdb::session::create_statement().
> 
> The old version that gave me trouble follows:
> 
> void db_conn_init(cppdb::session &db_session)
> {
>       db_session << "SET NAMES 'UTF8'" << 
> cppdb::exec;
>       db_session << "SET TIME ZONE 'UTC'" << 
> cppdb::exec;
> }
> 
> cppdb::statement create(cppdb::session &se,std::string const &table)
> {
>       cppcms::json::array keys,values;
>       //Feed elements to keys and values here.
>       std::vector<std::string> set_values;
>       std::string
>       column_list=utils::make_column_list(values,set_values);
>       std::vector<std::string> where_values;
>       std::string sql="UPDATE "+table+" SET
>       "+column_list+utils::make_where(keys,where_values);
>       //"sql" now becomes "UPDATE t SET c2=? WHERE c1=? AND 
> c2=?"
>       cppdb::statement st=se.create_prepared_uncached_statement(sql);
>       size_t set_values_size=set_values.size();
>       for(size_t i=0;i < set_values_size;++i){
>             if(set_values[i].empty())
>                   st.bind_null(i+1);
>             else
>                   st.bind(i+1,set_values[i]);
>       }
>       for(size_t i=0,n=where_values.size();i < n;++i){
>             if(where_values[i].empty())
>                   st.bind_null(set_values_size+i+1);
>             else
>                   st.bind(set_values_size+i+1,where_values[i]);
>       }
>       return st;
> }
> 
> Please note that
> cppdb::statement create(cppdb::session &se,std::string const &table)
> is a function. It is not a member function of a class.
> 
> void my_class::use()
> {
>       cppdb::session
>       
> se("postgresql:@pool_size=5;dbname=db2;user=user2;password=xxx",db_conn_init);
>       std::string table="t";
>       cppdb::statement st=create(se,"t");
>       cppdb::transaction tx(se);
>       st.exec(); //Weird error occurs here.
>       //Do more SQL here like this:
>       //se << "INSERT INTO ...";
>       tx.commit();
> }
> 
> I do not have trouble with this new version:
> 
> void my_class::use()
> {
>       cppdb::session
>       
> se("postgresql:@pool_size=5;dbname=db2;user=user2;password=xxx",db_conn_init);
>       std::string table="t";
>       cppcms::json::array keys,values;
>       //Feed elements to keys and values here.
>       std::vector<std::string> set_values;
>       std::string
>       column_list=utils::make_column_list(values,set_values);
>       std::vector<std::string> where_values;
>       std::string sql="UPDATE "+table+" SET
>       "+column_list+utils::make_where(keys,where_values);
>       //"sql" now becomes "UPDATE t SET c2=? WHERE c1=? AND 
> c2=?"
>       cppdb::statement st=se.create_prepared_uncached_statement(sql);
>       size_t set_values_size=set_values.size();
>       for(size_t i=0;i < set_values_size;++i){
>             if(set_values[i].empty())
>                   st.bind_null(i+1);
>             else
>                   st.bind(i+1,set_values[i]);
>       }
>       for(size_t i=0,n=where_values.size();i < n;++i){
>             if(where_values[i].empty())
>                   st.bind_null(set_values_size+i+1);
>             else
>                   st.bind(set_values_size+i+1,where_values[i]);
>       }
> 
>       cppdb::transaction tx(se);
>       st.exec();
>       //Do more SQL here like this:
>       //se << "INSERT INTO ...";
>       tx.commit();
> }
> 
> Best Regards,
> CN
> 
> On Thu, Nov 12, 2015, at 04:22 PM, CN wrote:
>>  Thank you for the reply!
>> 
>>  Having received your message, I went back and study frontend.* and
>>  backend.* of cppdb source code trying to understand more about it.
>>  According to my findings so far, instances of cppdb::statement in
>>  backend indeed are reference counted, meaning to me that these objects
>>  will not be destroyed as long as they are still referenced.
>> 
>>  But I can only go that deep into cppdb source. Because my original
>>  troublesome code actually uses uncached statement but I do not fully
>>  understand cppdb's statement cache mechanism, I begin to suspect this
>>  part of cppdb.
>> 
>>  I know this is not professional attitude, but the simplified version of
>>  my troublesome code follows:
>> 
>>  cppdb::statement create(cppdb::session &se)
>>  {
>>        cppdb::statement st=se.create_prepared_uncached_statement(
>>            "UPDATE t SET c2=? WHERE c1=? AND c2=?")
>>            << 'xx' << 2;
>>        return st;
>>  }
>> 
>>  void use()
>>  {
>>        cppdb::session se("connection params");
>>        cppdb::statement st=create(se);
>>        cppdb::transaction tx(se);
>>        st.exec(); //Weird error occurs here.
>>    //Do more SQL here like this:
>>    //se << "INSERT INTO ...";
>>        tx.commit();
>>  }
>> 
>>  Note that the data type of t.c2 is INTEGER.
>> 
>>  And the PostgreSQL error log follows:
>> 
>>  2015-10-31 14:20:42 CST ERROR:  invalid input syntax for integer:
>>  "cppdb_psqlstmt_17"
>>  2015-10-31 14:20:42 CST STATEMENT:  UPDATE t SET c2=$1 WHERE c1=$2 AND
>>  c2=$3
>>  2015-10-31 19:46:26 CST ERROR:  invalid input syntax for integer: 
> ""
>>  2015-10-31 19:46:26 CST STATEMENT:  UPDATE t SET c2=$1 WHERE c1=$2 AND
>>  c2=$3
>>  2015-10-31 22:42:24 CST ERROR:  invalid input syntax for integer: 
> ""
>>  2015-10-31 22:42:24 CST STATEMENT:  UPDATE t SET c2=$1 WHERE c1=$2 AND
>>  c2=$3
>>  2015-10-31 22:55:28 CST ERROR:  invalid input syntax for integer: 
> "!"
>>  2015-10-31 22:55:28 CST STATEMENT:  UPDATE t SET c2=$1 WHERE c1=$2 AND
>>  c2=$3
>> 
>>  As one might notice: How could I bind "cppdb_psqlstmt_17" to
>>  cppdb::statement's parameters?
>>  PostgreSQL complained weird arguments passed to c2 when the same
>>  cppdb::statement::exec() was called repeatedly.
>> 
>>  Any idea? Please!
>> 
>>  Best Regards,
>>  CN
>> 
>> 
>>  On Thu, Nov 12, 2015, at 12:47 AM, Artyom Beilis wrote:
>>  > Actually you are mistaken.
>>  > 
>>  > cppdb::statement, cppdb::session and cppdb::result keep the reference 
> to
>>  > underlying
>>  > backed object representing them.
>>  > 
>>  > See: 
>>  > 
> http://cppcms.com/sql/cppdb/classcppdb_1_1statement.html#adc5ddc1e8df9248b74692990cffe40cf
>>  > 
>>  > So copy is totally valid and you can use it this way without problems.
>>  > 
>>  > Actually statement would also keep a track of the session and result 
> of
>>  > statement and session as
>>  > well.
>>  > 
>>  > So it is correct way to do things.
>>  > 
>>  > Of course it is bad idea to start using copies of came 
> cppdb::statement
>>  > from two
>>  > different locations simultaneously.
>>  > 
>>  > So look for the problem in some other place.
>>  > 
>>  > Artyom
>>  > 
>>  > 
>>  > > Hi!
>>  > > 
>>  > > I recently made a mistake by doing this:
>>  > > 
>>  > > cppdb::statement create(){
>>  > >   cppdb::session se("connect parameters");
>>  > >   cppdb::statement  st=se << "SELECT * FROM t";
>>  > >   return st;
>>  > > }
>>  > > 
>>  > > void use(){
>>  > >   cppdb::statement  st=create();
>>  > >   st.query();
>>  > > }
>>  > > 
>>  > > Very weird errors happens to st.query().
>>  > > It took me quite some time to pin point the bug. After that, I 
> re-read
>>  > > the comments in cppdb/cppdb/frontend.h and I believe they confirm 
> my
>>  > > conclusion:
>>  > > 
>>  > > The problem here, I believe, is that cppdb::statement object is 
> already
>>  > > destroyed before st.query() is executed.
>>  > > 
>>  > > I am writing this note here in order to remind myself of avoiding 
> such
>>  > > practice. Hopefully no one else will make the same mistake as I 
> did!
>>  > > 
>>  > > Meanwhile, I suspect it will be better off
>>  > > 
>>  > > (a) either to remove the cppdb::statement copy and assignment 
> methods
>>  > > from cppdb source altogether
>>  > > (b) or to make cppdb::statement non-copyable
>>  > > 
>>  > > I have a feeling that these two methods serve only as a trap.
>>  > > Yes? No?
>>  > > 
>>  > > Best Regards,
>>  > > CN
>>  > > 
>>  > > -- 
>>  > > http://www.fastmail.com - Access all of your messages and folders
>>  > >                           wherever you are
>>  > > 
>>  > > 
>>  > > 
> ------------------------------------------------------------------------------
>>  > > _______________________________________________
>>  > > Cppcms-users mailing list
>>  > > Cpp...@li...
>>  > > https://lists.sourceforge.net/lists/listinfo/cppcms-users
>>  > > 
>>  > 
>>  > 
> ------------------------------------------------------------------------------
>>  > _______________________________________________
>>  > Cppcms-users mailing list
>>  > Cpp...@li...
>>  > https://lists.sourceforge.net/lists/listinfo/cppcms-users
>> 
>>  -- 
>>  http://www.fastmail.com - Access your email from home and the web
>> 
>> 
>> 
> ------------------------------------------------------------------------------
>>  _______________________________________________
>>  Cppcms-users mailing list
>>  Cpp...@li...
>>  https://lists.sourceforge.net/lists/listinfo/cppcms-users
> 
> 
> -- 
> http://www.fastmail.com - Access all of your messages and folders
>                           wherever you are
> 
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Cppcms-users mailing list
> Cpp...@li...
> https://lists.sourceforge.net/lists/listinfo/cppcms-users
>