Thread: Re: [Cppcms-users] Bizarre prepared statement problem
Brought to you by:
artyom-beilis
Menu
▾
▴
cppcms-users
|
From: <ele...@ex...> - 2012-05-22 04:21:21
|
> Have you tried to change the type to datetime? Because
> converting dates and numbers is not always well defined...
Isn't SQLites datetime data type affinity to NUMERIC anyway?
>
> Also show the *full* code and full schema.
>
Ive slightly simplified the SQL query in my original email, but here is
the whole function.
cppdb::result Base::get_all_items(unsigned int aircraft_id, const Table&
tbl, int date_limited_only)
{
cppdb::result r;
//std::tm ts = dt_to_time(session_timestamp());
string ts = "2012-05-17 23:59:59";
ostringstream os;
os << "SELECT i.id, i.name, i.remarks, i.part_number, h1.id AS rev_id, ";
os << tbl.column_string("h1") << " ";
os << "FROM " << tbl.name() << "s i ";
os << "INNER JOIN " << tbl.name() << "s_history h1 ON h1.root_id=i.id ";
os << "AND h1.days >= ? AND h1.date_fitted <= ? AND (h1.disabled_at IS
NULL OR h1.disabled_at > ?) ";
os << "AND h1.id IN (SELECT max(h2.id) FROM " << tbl.name() <<
"s_history h2 WHERE h2.date_fitted <= ? AND h2.root_id=i.id)";
os << "WHERE h1.aircraft_id = ? ORDER BY i.id ASC";
r = sql << os.str()
<< date_limited_only
<< ts
<< ts
<< ts
<< aircraft_id;
return r;
}
|
|
From: <ele...@ex...> - 2012-05-22 04:23:55
|
Table schema: create table af_publication_items_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, root_id INTEGER REFERENCES af_publication_items(id) NOT NULL, parent_id INTEGER REFERENCES af_publication_items(id), aircraft_id INTEGER REFERENCES aircrafts(id) NOT NULL, action TEXT NOT NULL, date_action TEXT, life REAL NOT NULL DEFAULT 0, date_fitted NUMERIC NOT NULL, fitted_at REAL NOT NULL DEFAULT 0, tsn REAL NOT NULL DEFAULT 0, serial_number TEXT, days INTEGER DEFAULT 0, dsn INTEGER DEFAULT 0, created_at NUMERIC DEFAULT CURRENT_TIMESTAMP, disabled_at NUMERIC ); |
|
From: Artyom B. <art...@ya...> - 2012-05-22 16:59:02
|
> cppdb::result Base::get_all_items(...)
> {
>
> cppdb::result r;
> string ts = "2012-05-17 23:59:59";
> ...
> r=sql ....
> << date_limited_only
> << ts
> << ts
> << ts
> << aircraft_id;
>
> return r; // <----
> }
>
And than in some other place
r = get_all_times(...)
while(r.next())
...
In the first request of r.next() the query is actually
executed, when **ts** goes out of the scope and becomes invalid.
and during query the ts is not valid.
That is why it does not work.
I can suggest switch to:
get_all_times
{
r = sql ....
r.next();
return r;
}
r=get_all_times();
while(!r.empty()) {
r >> ...
r.next()
}
Artyom Beilis
|
|
From: Artyom B. <art...@ya...> - 2012-05-22 17:02:28
|
----- Original Message -----
> From: Artyom Beilis <art...@ya...>
> To: "cpp...@li..." <cpp...@li...>
> Cc:
> Sent: Tuesday, May 22, 2012 7:58 PM
> Subject: Re: [Cppcms-users] Bizarre prepared statement problem
>
>
>
>> cppdb::result Base::get_all_items(...)
>
>> {
>>
>> cppdb::result r;
>> string ts = "2012-05-17 23:59:59";
>> ...
>> r=sql ....
>> << date_limited_only
>> << ts
>> << ts
>> << ts
>> << aircraft_id;
>>
>> return r; // <----
>> }
>>
>
> And than in some other place
>
> r = get_all_times(...)
> while(r.next())
> ...
>
> In the first request of r.next() the query is actually
> executed, when **ts** goes out of the scope and becomes invalid.
> and during query the ts is not valid.
>
> That is why it does not work.
>
> I can suggest switch to:
>
> get_all_times
> {
> r = sql ....
> r.next();
> return r;
>
> }
>
> r=get_all_times();
>
> while(!r.empty()) {
> r >> ...
> r.next()
> }
>
> Artyom Beilis
>
On the second thought...
It is bug because it is really unexpected behavior..
And stuff like this
r = sql << "SELECT ... ? .. " << (a+b);
while(r.next()) {
}
Would not work properly
Please open a bug report with HIGH priority
Thanks for the enlightenment!
Artyom
|
|
From: Artyom B. <art...@ya...> - 2012-05-22 20:07:00
|
> On the second thought...
>
>
> It is bug because it is really unexpected behavior..
>
> And stuff like this
>
>
> r = sql << "SELECT ... ? .. " << (a+b);
>
> while(r.next()) {
>
> }
>
> Would not work properly
>
> Please open a bug report with HIGH priority
>
> Thanks for the enlightenment!
>
> Artyom
>
Fixed in svn trunk in chageneset 2178.
Please take it and see if it solve the problem (it should)
I switched from using SQLITE_STATIC to full copy.
I think I'm going to release a hot fix release for cppdb
because some assumptions on the scope of the statement
were incorrect.
Artyom
|
|
From: <ele...@ex...> - 2012-05-23 07:46:18
|
> Fixed in svn trunk in chageneset 2178. > > Please take it and see if it solve the problem (it should) > > I switched from using SQLITE_STATIC to full copy. > > I think I'm going to release a hot fix release for cppdb > because some assumptions on the scope of the statement > were incorrect. > Thanks Artyom, I will test the patch ASAP, (although Im not at home right now), so I will report back tomorrow. Can you tell me what you changed? Is returning cppdb::result without calling next() expected to work now? I did also want to ask you why did you not design cppdb::result as an STL-like container with iterators? The way it is now I find it a little strange. For example if I run a query and terminate with cppdb::row, calling next() will return false even if a row exists. Thanks, petr |
|
From: <ele...@ex...> - 2012-05-23 08:02:10
|
Read about SQLITE_STATIC vs SQLITE_TRANSIENT, interesting stuff. Does this mean that doing a manual cppdb::statement and bind() is now going to work with temporaries as well? Petr |
|
From: Artyom B. <art...@ya...> - 2012-05-23 08:19:14
|
----- Original Message ----- > From: "ele...@ex..." <ele...@ex...> > Subject: Re: [Cppcms-users] Bizarre prepared statement problem > > Read about SQLITE_STATIC vs SQLITE_TRANSIENT, interesting stuff. > > Petr > See: http://thread.gmane.org/gmane.comp.db.sqlite.general/74538 The problem that unlike in all other databases, sqlite3 requires that binded value expected to be valid during all the statement execution process. That is very different from the semantics in MySQL, PostrgreSQL and ODBC drivers. The semantics of cppdb::statement requires that binded string value be valid until query()/exec()/row() or operator result() is executed. This worked 100% for all other DB but not for sqlite3. That is why full copy semantics now applied. > Does this mean that doing a manual cppdb::statement and bind() is now > going to work with temporaries as well? > No, it may work now for sqlite3 but still may be changed in future. For example if at some point result() would contain only table with all the results and not actually call sqlite3_step() I would be able to use SQLITE_STATIC again. Also it is not promised with other DB. So do not relay on such a behavior Artyom Beilis -------------- CppCMS - C++ Web Framework: http://cppcms.com/ CppDB - C++ SQL Connectivity: http://cppcms.com/sql/cppdb/ |
|
From: <ele...@ex...> - 2012-05-23 08:18:19
|
Tried to build cppdb-trunk got this error: [ 34%] Building CXX object CMakeFiles/cppdb.dir/drivers/odbc_backend.cpp.o /home/petr/Downloads/cppdb/cppdb-trunk/drivers/odbc_backend.cpp:46: error: size of array 'assert_on_sqlwchar_is_16' is negative /home/petr/Downloads/cppdb/cppdb-trunk/drivers/odbc_backend.cpp: In function 'std::basic_string<wchar_t, std::char_traits<wchar_t>, std::allocator<wchar_t> > cppdb::odbc_backend::tosqlwide(const std::string&)': /home/petr/Downloads/cppdb/cppdb-trunk/drivers/odbc_backend.cpp:333: warning: dereferencing type-punned pointer will break strict-aliasing rules Any ideas? System is FreeBSD 9. gcc 4.2.1 Petr |
|
From: Artyom B. <art...@ya...> - 2012-05-23 08:29:46
|
----- Original Message -----
> From: "ele...@ex..." <ele...@ex...>
>
>> Fixed in svn trunk in chageneset 2178.
>>
>> Please take it and see if it solve the problem (it should)
>>
>> I switched from using SQLITE_STATIC to full copy.
>>
>> I think I'm going to release a hot fix release for cppdb
>> because some assumptions on the scope of the statement
>> were incorrect.
>>
>
> Thanks Artyom,
>
> I will test the patch ASAP, (although Im not at home right now), so I will
> report back tomorrow.
>
> Can you tell me what you changed? Is returning cppdb::result without
> calling next() expected to work now?
Yes, the change is not in the behavior of the next but rather copying
the value (see mo other mail)
>
> I did also want to ask you why did you not design cppdb::result as an
> STL-like container with iterators?
>
Because using an iterator would require something like:
for(cppdb::result::iterator p=res.begin();p!=res.end();++p) {
cppdb::row r = *p;
r >> ....
}
There are several problems with it:
What is row, is it copied?
Is it a reference?
Where it is stored
what "r >> val" changes?
I agree that is is not "STL-complient" but iterator semantics was
not working well there, so I took more JDBC like approach and it
works well.
> The way it is now I find it a little strange. For example if I run a query
> and terminate with cppdb::row, calling next() will return false even if a
> row exists.
The semantics between cppdb::result::row and cppdb::result::query is very different.
Calling cppdb::result::row() calls next and checks if the next row exists.
If for example you call
cppdb::result r = sql << "SELECT name from users " << cppdb::row;
Instead of
cppdb::result r = sql << "SELECT name from users where id=?" << id << cppdb::row;
It would throw if the result is multi-row result (if backend supports such checks)
The behavior with row is to do something like
cppdb::result r = sql << "SELECT name from users where id=?" << id << cppdb::row;
if(!r.empty()) {
// we got user
r >> name;
}
You can even run
sql << "SELECT name from users where id=?" << id << cppdb::row >> name;
(Of course the last one would throw if there is no such user)
It has different semantics
If you call next() on row result you are expected to get false as
it is a single row.
Artyom Beilis
--------------
CppCMS - C++ Web Framework: http://cppcms.com/
CppDB - C++ SQL Connectivity: http://cppcms.com/sql/cppdb/
|
|
From: <ele...@ex...> - 2012-05-23 08:46:20
|
Thanks for all the explanation. I did notice another thing. On one of my statements I forgot to terminate with cppdb::row and then a check for r.blank() was true. Is this expected? Petr |
|
From: Artyom B. <art...@ya...> - 2012-05-23 08:32:26
|
----- Original Message ----- > From: "ele...@ex..." <ele...@ex...> > To: cpp...@li... > Cc: Artyom Beilis <art...@ya...> > Sent: Wednesday, May 23, 2012 11:18 AM > Subject: Re: [Cppcms-users] Bizarre prepared statement problem > >T ried to build cppdb-trunk got this error: > > [ 34%] Building CXX object CMakeFiles/cppdb.dir/drivers/odbc_backend.cpp.o > /home/petr/Downloads/cppdb/cppdb-trunk/drivers/odbc_backend.cpp:46: error: > size of array 'assert_on_sqlwchar_is_16' is negative > /home/petr/Downloads/cppdb/cppdb-trunk/drivers/odbc_backend.cpp: In > function 'std::basic_string<wchar_t, std::char_traits<wchar_t>, > std::allocator<wchar_t> > cppdb::odbc_backend::tosqlwide(const > std::string&)': > /home/petr/Downloads/cppdb/cppdb-trunk/drivers/odbc_backend.cpp:333: > warning: dereferencing type-punned pointer will break strict-aliasing > rules > > Any ideas? > > System is FreeBSD 9. gcc 4.2.1 > > Petr > > You are using iODBC instead of UnixODBC. ODCB standard requires that SQLWCHAR_T is 16 bit value, iODBC implements this incorrectly: - Either install UnixODBC instead of iODBC - Disable ODBC in cppdb with an option -DDISABLE_ODBC=ON Artyom Beilis -------------- CppCMS - C++ Web Framework: http://cppcms.com/ CppDB - C++ SQL Connectivity: http://cppcms.com/sql/cppdb/ |
|
From: <ele...@ex...> - 2012-05-23 08:56:57
|
I ssh'd into my workstation and installed cppdb-trunk, tested it and now it correctly displayes 2 rows every time. So I guess this bug is squashed! Petr |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X