Complex query in QSqlQuery

[xgoan]

Hi,

I have this query for MySQL:
SELECT @m:=(
SELECT COUNT(cardid)
FROM `transactions`
WHERE
operation>=1000 AND
operation<2000 AND
date<:beginDate
);

SELECT
@m:=@m+1 AS 'id',
t.date,
o.name,
t.amount
FROM `transactions` AS t
INNER JOIN `operations` AS o ON t.operation=o.id
WHERE
operation>=1000 && operation<2000 AND
date>=:beginDate AND
date<=:endDate
ORDER BY date;

And I try to execute it with QSqlQuery and it returns error. I prepare and bind the values for :beginDate and :endDate before executing it.

Can't be used querys with vars from QSqlQuery?

[wysota]

What does QSqlQuery:lastError() together with QSqlError::text() return?

[xgoan]

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
SELECT
@m:=@m+1 AS 'id',
t.date,
o.name,
t.amount
FROM `tran' at line 8 QMYSQL3: Unable to prepare statement"


This is the error, I think the problem is the SQL var

[wysota]

Do you use QSqlQuery::prepare()?

[xgoan]

bool ReportPrinter::chargeReport(const QDate &beginDate, const QDate &endDate){
const QString file=":/database/chargereport.sql";
QString sql;

if(!loadFile(file, &sql)) return false;
QSqlQuery query;

query.prepare(sql);
query.bindValue(":beginDate", beginDate);
query.bindValue(":endDate", endDate);
if(!query.exec()){
QMessageBox::critical(0, qApp->applicationName(), query.lastError().text());
return false;
}
else while(query.next()){
qDebug()<<query.value(0)<<query.value(1)<<query.value(2)<<query.value(3);
}

return true;
}

Sure this is my source code

[wysota]

What happens if you don't use prepare()?

[xgoan]

What happens if you don't use prepare()?

If I don't use prepare I can't bind the values... But I will try removing the bind values

[wysota]

You can use QString::arg() to bind values.

[xgoan]

bool ReportPrinter::chargeReport(const QDate &beginDate, const QDate &endDate){
const QString file=":/database/chargereport.sql";
QString sql;

if(!loadFile(file, &sql)) return false;
QSqlQuery query;

/* query.prepare(sql);
query.bindValue(":beginDate", beginDate);
query.bindValue(":endDate", endDate);*/
if(!query.exec(sql)){
QMessageBox::critical(0, qApp->applicationName(), query.lastError().text());
return false;
}
else while(query.next()){
qDebug()<<query.value(0)<<query.value(1)<<query.value(2)<<query.value(3);
}

return true;
}

SELECT @m:=(
SELECT COUNT(cardid)
FROM `transactions`
WHERE
operation>=1000 AND
operation<2000 );

SELECT
@m:=@m+1 AS 'id',
t.date,
o.name,
t.amount
FROM `transactions` AS t
INNER JOIN `operations` AS o ON t.operation=o.id
WHERE
operation>=1000 && operation<2000
ORDER BY date;

It gives to me the result of the first SELECT. I need the the result of 2nd SELECT :(

[wysota]

Does it work when you issue the same statement using mysql console? Are variables part of the sql specification or a feature of the console?

How about modifying the statement so that you use a subquery instead of the variable assignment?

[xgoan]

The SQL works in HeidiSQL and in mysql command.

I have been searching and I found that QSqlQuery only works with single queries, no way for a list of them :(

[jacek]

I have been searching and I found that QSqlQuery only works with single queries, no way for a list of them :(
Can't you issue two queries? The @m variable should be tied to the session. And you can always rewrite your queries into a single one.

Have you considered using sequences?

[xgoan]

And you can always rewrite your queries into a single one.

How could I do this in one query?


Have you considered using sequences?

I dont' know about sequences

[jacek]

How could I do this in one query?
I've overlooked the @m:= part. ;)

Anyway the idea is to self cross join on the result set and count all rows that should be before the current row.

I.e. something like:SELECT a.x, count(*) FROM result AS a, result AS b WHERE a.x <= b.x GROUP BY a.x ORDER BY a.xof course this isn't quite efficient and will work only if a.x is unique.


I dont' know about sequences
Sequence is an object that generates IDs. Usually it doesn't guarantee that IDs will be consecutive, esp. when you roll back transactions, but usually it doesn't matter. Although AFAIR in MySQL sequences are only in form of auto_increment fields.

If you are going to insert that data back to the database, you can just simply forget about the id field.

[wysota]

You can implement a sequence using triggers. MySQL supports them since 5.0

[xgoan]

Thanks :)
I will try this query but I think that will be better use 2 QSqlQuery, one for take the count of registers and the other to get the selected registers with C++ code because it seems that SQL vars doesn't work with QSqlQuery :(.

Anyway it's a pity that QSqlQuery doesn't support queries like these :(

[wysota]

Just a side note - I'd use a different query (it may be invalid but I mean the general idea):


SELECT
MAX(cardid)+1 AS 'id',
t.date,
o.name,
t.amount
FROM `transactions` AS t
INNER JOIN `operations` AS o ON t.operation=o.id
WHERE
operation>=1000 && operation<2000 GROUP BY cardid
ORDER BY date;

MAX is safer than COUNT.

But honestly that's a perfect usecase for triggers - you can have a trigger that will calculate the id itself and modify the query on the fly.

[mm78]

I have been searching and I found that QSqlQuery only works with single queries, no way for a list of them :(

Check out the new QSqlQuery::nextResult() function in Qt 4.4:
http://doc.trolltech.com/4.4beta/qsqlquery.html#nextResult

[xgoan]

Check out the new QSqlQuery::nextResult() function in Qt 4.4:
http://doc.trolltech.com/4.4beta/qsqlquery.html#nextResult

This could help only if it supports the SQL vars. Anyway I'm using Qt 4.3.4

[xgoan]

Just a side note - I'd use a different query (it may be invalid but I mean the general idea):


SELECT
MAX(cardid)+1 AS 'id',
t.date,
o.name,
t.amount
FROM `transactions` AS t
INNER JOIN `operations` AS o ON t.operation=o.id
WHERE
operation>=1000 && operation<2000 GROUP BY cardid
ORDER BY date;

MAX is safer than COUNT.

But honestly that's a perfect usecase for triggers - you can have a trigger that will calculate the id itself and modify the query on the fly.

It's not a valid query:

mysql> DESCRIBE `transactions`;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| cardid | char(16) | NO | PRI | | |
| date | datetime | NO | PRI | | |
| operation | int(11) | NO | | | |
| amount | decimal(4,2) | NO | | 0.00 | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> SELECT * FROM transactions LIMIT 1;
+------------------+---------------------+-----------+--------+
| cardid | date | operation | amount |
+------------------+---------------------+-----------+--------+
| %07032008125843_ | 2008-03-11 14:50:32 | 1000 | 5.00 |
+------------------+---------------------+-----------+--------+
1 row in set (0.00 sec)

As you can see I can't use MAX(cardid) because it's not correlative number. And the primary key it's done with cardid+date

[wysota]

So what do you need the variable for? I'd really implement a sequence using stored procedures if I were you. It's best to keep the logic in the database and not the client application. More security and less data transfer (thus better speed).