query prepare with sql math

[GuL]

This is my code:

QSqlQuery query;
 
		query.prepare("select :a / :b");
 
		query.addBindValue( ui.a_lineEdit->text() );
		query.addBindValue( ui.b_lineEdit->text() );
 
		query.exec();
 
 
		while (query.next()) {
		QString res = query.value(0).toString();
		ui.res_lineEdit->setText(res);

QSqlQuery query;
		
		query.prepare("select :a / :b");
				
		query.addBindValue( ui.a_lineEdit->text() );
		query.addBindValue( ui.b_lineEdit->text() );

		query.exec();
		
		
		while (query.next()) {
		QString res = query.value(0).toString();
		ui.res_lineEdit->setText(res);

To copy to clipboard, switch view to plain text mode 

ui.a_lineEdit->text() is 9 and ui.b_lineEdit->text() is 3
If I try to +,- or * the result shows up in ui.res_lineEdit->setText(res), but when I try to divide it doesnt show me anything. Why?

Renan

[jacek]

What do query.exec() and query.lastError() return?

[GuL]

code:

QSqlQuery query;
 
		query.prepare("select :a / :b");
 
		query.addBindValue( ui.a_lineEdit->text() );
		query.addBindValue( ui.b_lineEdit->text() );
 
		query.exec();
		QMessageBox::critical(0, tr("Error"), 
		QString("The error:\n%1").arg(query.lastError().text())); 
 
 
		while (query.next()) {
		QString res = query.value(0).toString();
		ui.res_lineEdit->setText(res);

QSqlQuery query;
		
		query.prepare("select :a / :b");
				
		query.addBindValue( ui.a_lineEdit->text() );
		query.addBindValue( ui.b_lineEdit->text() );

		query.exec();
		QMessageBox::critical(0, tr("Error"), 
		QString("The error:\n%1").arg(query.lastError().text())); 
		
		
		while (query.next()) {
		QString res = query.value(0).toString();
		ui.res_lineEdit->setText(res);

To copy to clipboard, switch view to plain text mode 

query.lastError() returns me nothing.

What could I do to know what query.exec() returns?

Renan

[spirit]

exec() returns true or false. if exec() returns false that means that an error appeared during query executing.

[GuL]

QSqlQuery query;
 
		query.prepare("select :a / :b");
 
		query.addBindValue( ui.a_lineEdit->text() );
		query.addBindValue( ui.b_lineEdit->text() );
 
		if (!query.exec()){
			QMessageBox::critical(0, tr("Error"), 
			QString("There is an error with query.exec()!"));
		}else{
 
			query.exec();
			QMessageBox::critical(0, tr("Error"), 
			QString("There is no error with query.exec():\n%1").arg(query.lastError().text()));		
 
			while (query.next()) {
			QString res = query.value(0).toString();
			ui.res_lineEdit->setText(res);
			}

QSqlQuery query;
		
		query.prepare("select :a / :b");
				
		query.addBindValue( ui.a_lineEdit->text() );
		query.addBindValue( ui.b_lineEdit->text() );

		if (!query.exec()){
			QMessageBox::critical(0, tr("Error"), 
			QString("There is an error with query.exec()!"));
		}else{

			query.exec();
			QMessageBox::critical(0, tr("Error"), 
			QString("There is no error with query.exec():\n%1").arg(query.lastError().text()));		
		
			while (query.next()) {
			QString res = query.value(0).toString();
			ui.res_lineEdit->setText(res);
			}

To copy to clipboard, switch view to plain text mode 

There is no error with query.exec() and query.lastError() returns me nothing.

Any suggestions?

Renan

[spirit]

sorry, it works.

[GuL]

what query.exec() and query.lastError() returns?

[spirit]

this code returns "2"

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setHostName("localhost");
	db.setDatabaseName("test.db");
	db.setUserName("system");
	db.setPassword("sysdba");
	if (!db.open())
		qWarning() << db.lastError().text();
	else {
		QSqlQuery query(db);
		query.prepare("CREATE TABLE test (a int, b int)");
		if (!query.exec())
			qWarning() << query.lastError().text();
		else {
			qDebug() << query.exec("INSERT INTO test (a, b) VALUES (1, 2)");
			qDebug() << query.exec("INSERT INTO test (a, b) VALUES (3, 4)");
			qDebug() << query.exec("INSERT INTO test (a, b) VALUES (5, 5)");
		query.prepare("SELECT :a / :b");
			query.addBindValue(4);
			query.addBindValue(2);
			qDebug() << query.exec();
			query.next();
			qDebug() << query.value(0);
		}
	}

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
	db.setHostName("localhost");
	db.setDatabaseName("test.db");
	db.setUserName("system");
	db.setPassword("sysdba");
	if (!db.open())
		qWarning() << db.lastError().text();
	else {
		QSqlQuery query(db);
		query.prepare("CREATE TABLE test (a int, b int)");
		if (!query.exec())
			qWarning() << query.lastError().text();
		else {
			qDebug() << query.exec("INSERT INTO test (a, b) VALUES (1, 2)");
			qDebug() << query.exec("INSERT INTO test (a, b) VALUES (3, 4)");
			qDebug() << query.exec("INSERT INTO test (a, b) VALUES (5, 5)");
		query.prepare("SELECT :a / :b");
			query.addBindValue(4);
			query.addBindValue(2);
			qDebug() << query.exec();
			query.next();
			qDebug() << query.value(0);
		}
	}

To copy to clipboard, switch view to plain text mode 

[GuL]

I have tested your code and it returns "2".

Something strange is happening, because when I change

query.prepare("select :a / :b");

query.prepare("select :a / :b");

To copy to clipboard, switch view to plain text mode 

to

query.prepare("select :a + :b");

query.prepare("select :a + :b");

To copy to clipboard, switch view to plain text mode 

or

query.prepare("select :a - :b");

query.prepare("select :a - :b");

To copy to clipboard, switch view to plain text mode 

or

query.prepare("select :a * :b");

query.prepare("select :a * :b");

To copy to clipboard, switch view to plain text mode 

it returns me the right result.
Do you see anything wrong with my code?


Renan

[spirit]

what kind of database do you use? mysql, right?

[GuL]

yes mysql.
here is the the pushbutton code:

void testeMYSQL2::on_soma_pushButton_clicked()
{
	QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
        db.setHostName(ui.hostname_lineEdit->text());
        db.setDatabaseName(ui.database_lineEdit->text());
        db.setUserName(ui.username_lineEdit->text());
        db.setPassword(ui.password_lineEdit->text());
	db.open();
	if (!db.open()){
		QMessageBox::critical(0, tr("Error"), 
		QString("The error:\n%1").arg(db.lastError().text())); 
	}
	else{
		QSqlQuery query;
 
		query.prepare("SELECT :a / :b");
 
// 		query.addBindValue( ui.a_lineEdit->text() );
// 		query.addBindValue( ui.b_lineEdit->text() );
// 		query.addBindValue( 400 );
// 		query.addBindValue( 2 );
 		query.bindValue(":a", ui.a_lineEdit->text() );
 		query.bindValue(":b", ui.b_lineEdit->text() );
 
		if (!query.exec()){
			QMessageBox::critical(0, tr("Error"), 
			QString("There is an error with query.exec()!"));
		}else{
 
//			query.exec();
			QMessageBox::critical(0, tr("Error"), 
			QString("There is NO error with query.exec():\n%1").arg(query.lastError().text()));		
 
			while (query.next()) {
			QString res = query.value(0).toString();
			ui.res_lineEdit->setText(res);
			}
		}
	}
	db.close();
}

void testeMYSQL2::on_soma_pushButton_clicked()
{
	QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
        db.setHostName(ui.hostname_lineEdit->text());
        db.setDatabaseName(ui.database_lineEdit->text());
        db.setUserName(ui.username_lineEdit->text());
        db.setPassword(ui.password_lineEdit->text());
	db.open();
	if (!db.open()){
		QMessageBox::critical(0, tr("Error"), 
		QString("The error:\n%1").arg(db.lastError().text())); 
	}
	else{
		QSqlQuery query;
		
		query.prepare("SELECT :a / :b");
				
// 		query.addBindValue( ui.a_lineEdit->text() );
// 		query.addBindValue( ui.b_lineEdit->text() );
// 		query.addBindValue( 400 );
// 		query.addBindValue( 2 );
 		query.bindValue(":a", ui.a_lineEdit->text() );
 		query.bindValue(":b", ui.b_lineEdit->text() );

		if (!query.exec()){
			QMessageBox::critical(0, tr("Error"), 
			QString("There is an error with query.exec()!"));
		}else{

//			query.exec();
			QMessageBox::critical(0, tr("Error"), 
			QString("There is NO error with query.exec():\n%1").arg(query.lastError().text()));		
		
			while (query.next()) {
			QString res = query.value(0).toString();
			ui.res_lineEdit->setText(res);
			}
		}
	}
	db.close();
}

To copy to clipboard, switch view to plain text mode 

Renan

[GuL]

Here is something really strange:
if I change:

query.prepare("SELECT :a / :b");

query.prepare("SELECT :a / :b");

To copy to clipboard, switch view to plain text mode 

to

query.prepare("SELECT (:a / :b)*1");

query.prepare("SELECT (:a / :b)*1");

To copy to clipboard, switch view to plain text mode 

I get the result.
Maybe a QT bug?

Renan

[jacek]

Indeed it behaves strange. Could you prepare a minimal compilable example?

[GuL]

I have uploaded a simple compilable example, as jacek asked.

Renan

I have uploaded it again.

[spirit]

I can't download testemysql2.cpp

[GuL]

I have uploaded it again.

Renan

[spirit]

looks like bug in Qt.
this code works, you can use it

...
query.prepare(QString("select %1/%2").arg(ui.lineEdit->text()).arg(ui.lineEdit_2->text()));
...

...
query.prepare(QString("select %1/%2").arg(ui.lineEdit->text()).arg(ui.lineEdit_2->text()));
...

To copy to clipboard, switch view to plain text mode 

[GuL]

Thanks again spirit.

I will wait one or two days to report these to trolltech. Maybe someone knows why this is happening.

I'm using QT 4.3.4 open source Linux Ubuntu 8.04. I don't if this happens with QT 4.4.1.

Renan

[spirit]

I've tested on Qt 4.4.1, the result is the same.

[jacek]

I've minimized it a bit further (see attachment) and it appears that for Qt 4.4.1 on Linux I get the "No results." message, which means that the database doesn't return any data. Of course it works for * operator.

I've also found a workaround:

query.addBindValue( 4.0 );
query.addBindValue( 2.0 );

query.addBindValue( 4.0 );
query.addBindValue( 2.0 );

To copy to clipboard, switch view to plain text mode