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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Laugh out loud!
Thank you so much for your enlightenment!
cppdb is rock sound.

Best Regards,
CN

On Thu, Nov 12, 2015, at 06:49 PM, Artyom Beilis wrote:
> 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 - The professional email service