PDA

View Full Version : Strange issues with QSqlQuery



SIFE
16th May 2011, 23:44
I have this SLOT:

void ConfSetup::setNextPage()
{
int currentIndex = ui->stackedWidget->currentIndex();

switch(currentIndex)
{
case 1:
{
ui->stackedWidget->setCurrentIndex(currentIndex + 1);
}
break;

case 2:
{
db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("mysql");
db.setHostName(ui->serverEdit->text());
db.setPort(ui->portEdit->text().toInt());
db.setUserName(ui->userEdit->text());
db.setPassword(ui->passwordEdit->text());

if(!db.open())
{
QMessageBox::critical(0, trUtf8("Fail to login"), trUtf8("Wrong user or password"));

}
else
{
ui->stackedWidget->setCurrentIndex(currentIndex + 1);
}
}
break;

case 3:
{
QSqlQuery query;
query.prepare("SELECT user FROM user WHERE user=:user");
// query.prepare("SELECT user FROM user WHERE user='root'");
query.bindValue(":user", ui->userDbEdit->text());
query.exec();

if(query.numRowsAffected() > 0)
QMessageBox::critical(0, trUtf8("User exist"), trUtf8("This user already token"));

else
{
qDebug("%s" , query.executedQuery().toStdString().c_str());
qDebug() << db.databaseName();
qDebug() << db.isOpen();
qDebug() << query.lastError();
qDebug("%s" , query.lastQuery().toStdString().c_str());
qDebug("%d" , query.isActive());
qDebug("%s" , db.driverName().toStdString().c_str());
qDebug() << db.lastError();
ui->stackedWidget->setCurrentIndex(currentIndex + 1);
}
}
break;

case 4:
{
QSqlQuery query;
query.prepare("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=:database");
query.bindValue(":database", ui->dbNameEdit->text());
query.exec();

if(query.numRowsAffected() == 1)
QMessageBox::critical(0, trUtf8("Fail to create database"), trUtf8("This database already exist"));

else
ui->stackedWidget->setCurrentIndex(currentIndex + 1);
}
break;

case 5:
{
QSqlQuery query;
query.prepare("INSERT INTO Users(User, Password)" \
"VALUES(:user, :password)");
query.bindValue(":user", ui->userEdit->text());
query.bindValue(":password", ui->passwordEdit->text());
query.exec();
ui->stackedWidget->setCurrentIndex(currentIndex + 1);
}
break;

}
// ui->comboBox->addItem(ui->userDbEdit->text());
// ui->lineEdit->setText(addSlashes(ui->slashesEdit->text()));
}
And the connection like so:

connect(ui->nextButton0, SIGNAL(clicked()), this, SLOT(setNextPage()));
connect(ui->nextButton1, SIGNAL(clicked()), this, SLOT(setNextPage()));
//etc
The query is correct because I test it in phpMyAdmin, qDebug output show this:

SELECT user FROM user WHERE user=? //should be ... user = user, but formated like this because MySQL doesn't support native prepared statment
"mysql"
true
QSqlError(-1, "", "")
SELECT user FROM user WHERE user=:user
1
QMYSQL
QSqlError(-1, "", "")
Even if I execute this query:

query.prepare("SELECT user FROM user WHERE user='root'");
query still return -1.

wysota
16th May 2011, 23:57
What do you mean that query returns -1? If you mean QSqlError(-1,"", "") then it means there is no error. Does your query get executed properly? Using numRowsAffected() with SELECT statements doesn't make sense, even the docs state that so don't rely on it.

SIFE
17th May 2011, 01:27
My query executed properly, my problem with numRowsAffected() always return -1, is there a way to get number of rows has been returned.

wysota
17th May 2011, 01:55
Yes, read the docs of the methods you are using. Besides, if you are interested in the number of rows, then ask the database about the number of rows and not about rows themselves.

SELECT COUNT(*) FROM user WHERE user='root'

By the way, are you sure the query you posted is really valid? You have a table called "user" that has a column that's also called "user"?

SIFE
17th May 2011, 03:04
I found size method return number of rows founded.

By the way, are you sure the query you posted is really valid? You have a table called "user" that has a column that's also called "user"?
Yes, its valid, this is mysql database that chiped with MySQL server, you can give a table and a column some name.

wysota
17th May 2011, 11:51
If you are only interested in the number of rows matching the criteria then don't use size() but rather COUNT(*). Then you'll always get one row with the number of entries instead of getting multiple rows which is less efficient.