Complex query in QSqlQuery

[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

[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