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;

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;

To copy to clipboard, switch view to plain text mode 

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"

"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"

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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;

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;

To copy to clipboard, switch view to plain text mode 

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.x

SELECT a.x, count(*) FROM result AS a, result AS b WHERE a.x <= b.x GROUP BY a.x ORDER BY a.x

To copy to clipboard, switch view to plain text mode 

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

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;

To copy to clipboard, switch view to plain text mode 

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/qsq...tml#nextResult

[xgoan]

Check out the new QSqlQuery::nextResult() function in Qt 4.4:
http://doc.trolltech.com/4.4beta/qsq...tml#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;

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;

To copy to clipboard, switch view to plain text mode 

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> 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)

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode 

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