cppcms-users

[Cppcms-users] Bizarre prepared statement problem

[<ele...@ex...>]

I'm truly stumped by this SQL query.

This works as expected:

SELECT * FROM af_scheduled_items i INNER JOIN af_scheduled_items_history
h1 ON h1.root_id=i.id WHERE h1.date_fitted <= '2012-05-17 23:59:59' AND
h1.id IN (SELECT max(h2.id) FROM af_scheduled_items_history h2 WHERE
h2.date_fitted <= '2012-05-17 23:59:59' AND h2.root_id=i.id) AND
h1.aircraft_id = ? ORDER BY i.id ASC

Returns 2 rows which is what's expected.

If I replace the h2.date_fitted with ?

SELECT * FROM af_scheduled_items i INNER JOIN af_scheduled_items_history
h1 ON h1.root_id=i.id WHERE h1.date_fitted <= '2012-05-17 23:59:59' AND
h1.id IN (SELECT max(h2.id) FROM af_scheduled_items_history h2 WHERE
h2.date_fitted <= ? AND h2.root_id=i.id) AND h1.aircraft_id = ? ORDER BY
i.id ASC

This returns 1 row.

And if I replace both date_fitted dates with ?

SELECT * FROM af_scheduled_items i INNER JOIN af_scheduled_items_history
h1 ON h1.root_id=i.id WHERE h1.date_fitted <= ? AND h1.id IN (SELECT
max(h2.id) FROM af_scheduled_items_history h2 WHERE h2.date_fitted <= ?
AND h2.root_id=i.id) AND h1.aircraft_id = ? ORDER BY i.id ASC

This returns sometimes 1 row, sometimes 2 rows.

Can someone with enough wisdom explain this?

Re: [Cppcms-users] Bizarre prepared statement problem

[<ele...@ex...>]

UPDATE:

Something even stranger. If I replace all the hardcoded times and run it
through a prepared statement

This works(always returns 2 rows)

r = sql << os.str()
	<< "2012-05-17 23:59:59"
	<< "2012-05-17 23:59:59"
        << aircraft_id;

but this doesnt(returns sometimes 1, sometimes 2 rows):

string ts = "2012-05-17 23:59:59";

r = sql << os.str()
	<< ts
	<< ts
        << aircraft_id;

What the hell is going on here?

Re: [Cppcms-users] Bizarre prepared statement problem

[Marcel H. <ke...@co...>]

Am 21.05.2012 06:52, schrieb ele...@ex...:
> UPDATE:
>
> Something even stranger. If I replace all the hardcoded times and run it
> through a prepared statement
>
> This works(always returns 2 rows)
>
> r = sql<<  os.str()
> 	<<  "2012-05-17 23:59:59"
> 	<<  "2012-05-17 23:59:59"
>          <<  aircraft_id;
>
> but this doesnt(returns sometimes 1, sometimes 2 rows):
>
> string ts = "2012-05-17 23:59:59";
>
> r = sql<<  os.str()
> 	<<  ts
> 	<<  ts
>          <<  aircraft_id;
>
> What the hell is going on here?
strange strange. I don't really know if this could help, but: ts is a 
string and you give two times the same string into the 
preparedstatement. You could try to copy ts to ts2 (string ts2=ts) and 
try that again with << ts << ts2. And then if that helps, that would be 
strange though ;)

Find out what's going on there ! ^^

Regards

Re: [Cppcms-users] Bizarre prepared statement problem

[<ele...@ex...>]

>> What the hell is going on here?
> strange strange. I don't really know if this could help, but: ts is a
> string and you give two times the same string into the
> preparedstatement. You could try to copy ts to ts2 (string ts2=ts) and
> try that again with << ts << ts2. And then if that helps, that would be
> strange though ;)
>
> Find out what's going on there ! ^^


Yeah I tried cloning the string via a copy constructor:

sql << os.str() << string(ts) << string(ts);

but it made no difference. I believe string always deep copies.

In the end, instead of passing it a string, I used strptime() to convert
the string to a std::tm struct and it's working fine. Very weird.

Re: [Cppcms-users] Bizarre prepared statement problem

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

>

>Yeah I tried cloning the string via a copy constructor:
>
>sql << os.str() << string(ts) << string(ts);
>
>but it made no difference. I believe string always deep copies.
>
>In the end, instead of passing it a string, I used strptime() to convert
>the string to a std::tm struct and it's working fine. Very weird.
>

Few points:

1. What DB (mysql/sqlite/posgressql/mssql) ?
2. Can you enable logging (at DB level to see what exactly passed)?
3. What is the column type?
4. Does it work from direct SQL access?
5. How do you count rows?


Generally is seems little bit strange.


Artyom

Re: [Cppcms-users] Bizarre prepared statement problem

[<ele...@ex...>]

 > Few points:
>
> 1. What DB (mysql/sqlite/posgressql/mssql) ?
> 2. Can you enable logging (at DB level to see what exactly passed)?
> 3. What is the column type?
> 4. Does it work from direct SQL access?
> 5. How do you count rows?
>


1) Sqlite
2) How do you do that?
3) The column type is NUMERIC
4) Yes
5) Counted rows by inserting them into a vector and then size()

Petr

Re: [Cppcms-users] Bizarre prepared statement problem

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

----- Original Message -----
> 
>>  Few points:
>> 
>>  1. What DB (mysql/sqlite/posgressql/mssql) ?
>>  2. Can you enable logging (at DB level to see what exactly passed)?
>>  3. What is the column type?
>>  4. Does it work from direct SQL access?
>>  5. How do you count rows?
>> 
> 
> 
> 1) Sqlite
> 2) How do you do that?

You can't with sqlite

> 3) The column type is NUMERIC

Have you tried to change the type to datetime? Because
converting dates and numbers is not always well defined...

Also show the *full* code and full schema.



 
Artyom Beilis
--------------
CppCMS - C++ Web Framework:   http://cppcms.com/
CppDB - C++ SQL Connectivity: http://cppcms.com/sql/cppdb/

Re: [Cppcms-users] Bizarre prepared statement problem

[<ele...@ex...>]

> 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;
}