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