cppcms-users

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

[CN <cn...@fa...>]

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

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

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

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
> 

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

[CN <cn...@fa...>]

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

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

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

Can you show the full code (- you can hide passwords in connection strings)?

First of all it looks like you bind two parameters when 3 are required. i.e.

cppdb::statement st=se.create_prepared_uncached_statement("UPDATE t SET c2=? WHERE c1=? AND c2=?") << 'xx' << 2;

And what is 'xx'? It isn't even C++.

Also you probably what to call se.create_statement()

create_prepared_uncached_statement is for execution of a prepared statement many times but not letting it into cache (for example for insert on many rows one time)
create_statement - creates ordinary unprepared statement (it still uses binding) good for one time calls of custom statement or rare statements
create_prepared_statement - creates prepared statement and caches it for future reuse (default) statements that are eused frequently.

 

Artyom Beilis


----- Original Message -----
> From: CN <cn...@fa...>
> To: cpp...@li...
> Cc: 
> Sent: Thursday, November 12, 2015 10:22 AM
> Subject: Re: [Cppcms-users] cppdb::statement copy and assign
> 
>T hank 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
> 

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

[CN <cn...@fa...>]

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

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
> 

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

[CN <cn...@fa...>]

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