Re: [Cppcms-users] cppdb::statement copy and assign
Brought to you by:
artyom-beilis
Menu
▾
▴
Re: [Cppcms-users] cppdb::statement copy and assign
|
From: CN <cn...@fa...> - 2015-11-12 09:53:16
|
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
|
