QSQLITE logic error

[cyberboy]

Hi,

I have a QSQLITE database and I want to insert some stuff, I'm using a prepare query and finally a exec function. But I get the message:

SQL Logic error or missing database, unable to fetch rows

This is the code:

this->connect();

if(this->isConnected){
QSqlQuery query;

query.prepare("INSERT INTO clients (`cid`, `firstName`, `lastName`, `address`, `zipCode`, `city`, `phoneNr`, `email`) "
" VALUES( '', :firstName, :lastName, :address, :zipCode, :city, :phoneNr, :email)");
query.bindValue(":firstName", strFirstName);
query.bindValue(":lastName", strLastName);
query.bindValue(":address", strAddress);
query.bindValue(":zipCode", strZipCode);
query.bindValue(":city", strCity);
query.bindValue(":phoneNr", strPhoneNr);
query.bindValue(":email", strEmail);

if(query.exec()){
emit databaseChanged();
return true;
}else{
QSqlError error = query.lastError();
QString sError = error.text();
QMessageBox::critical(this, tr("SQL error"), sError);
return false;
}
this->closeConnection();
}

Does somebody know how to solve this?

Thx,

Cyberboy

[jpn]

Did you call QSqlDatabase::setDatabaseName()?

[cyberboy]

Yes I have a initialize function for the database here is de code :


db::db(){

//loading the drivers
database = QSqlDatabase::addDatabase ("QSQLITE");
//set database name
database.setDatabaseName("vredeveldOrderDatabase");
//set connection flag
this->isConnected = database.open();
//set error
QSqlError qError = database.lastError();
QString sError = qError.text();
if(!this->isConnected){
//if isn't connected show error message
QMessageBox::critical(this, tr("Database connection isn't open!"),
sError,
QMessageBox::Ok);
}
}


And here the connect function


bool db::connect(){
if(this->isConnected == false){
database.open();
this->isConnected = true;
}
return true;
}


And the code in the previous post is a function for adding clients.

So I did call the setDatabaseName function....

[jpn]

Print QSqlQuery::lastQuery() and try executing it with SQLite Browser. Maybe it gives a more detailed error message.

[cyberboy]

Thanks for the tip, this is the error message I get

insert into clients (`cid`, `firstName`, `lastName`, `address`, `zipCode`, `city`, `phoneNr`, `email`) values ( '', :firstName, :lastName, :address, :zipCode, :city, :phoneNr, :email)

So the prepare and the bindValue isn't working properly, is there anybody familiar with this problem?

I've found something I'm passing QString vars in the bindValue, I've changed it to QVariant but that works neither.

Could it be that it won't work because I'm converting a QString to QVariant;

Here's the code to get the text

QString strFirstName = firstNameLine->text();
QString strLastName = lastNameLine->text();
QString strAddress = addressLine->text();
QString strZipCode = zipCodeLine->text();
QString strCity = cityLine->text();
QString strPhoneNr = phoneNrLine->text();
QString strEmail = emailLine->text();


if(database->addClient(strFirstName, strLastName, strAddress, strZipCode, strCity, strPhoneNr, strEmail)){
clientDialog->accept();
}else{
QMessageBox::critical(clientDialog, "Error", "Er is een fout opgetreden bij het invoeren van een klant, neem contact op met de ontwikkelaar!");
}


and here's the code of the function


bool db::addClient(QVariant strFirstName, QVariant strLastName, QVariant strAddress, QVariant strZipCode, QVariant strCity, QVariant strPhoneNr = "", QVariant strEmail = ""){

[jpn]

Sorry, my bad. Seems that neither QSqlQuery::lastQuery() nor QSqlQuery::executedQuery() will print bound values. You can print them with QSqlQuery::boundValues() though:


#include <QtCore>
#include <QtSql>

int main(int argc, char* argv[])
{
QCoreApplication app(argc, argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(":memory:");
if (!db.open())
{
qWarning() << "cannot open database";
return -1;
}

QSqlQuery query;
query.exec("create table person (id int primary key, firstname varchar(20), lastname varchar(20))");

query.prepare("insert into person (`id`, `firstname`, `lastname`) values (:id, :firstname, :lastname)");
query.bindValue(":id", 101);
query.bindValue(":firstname", "Danny");
query.bindValue(":lastname", "Young");

qDebug() << query.exec()
<< query.executedQuery()
<< query.boundValues();
}

[cyberboy]

Damn this is weird!

Here is the piece of code that sends the error messages:

//Woops there went something terrible wrong, popup the message box
QSqlError error = query.lastQuery();
QString sError = error.text();
QMessageBox::critical(this, tr("SQL error"), sError);
QString ssError = query.executedQuery();
QMessageBox::critical(this, tr("SQL error"), ssError);
QMapIterator<QString, QVariant> i(query.boundValues());
while (i.hasNext()) {
i.next();
QMessageBox::warning(this, tr("SQL Warning"), i.value().toString().toAscii().data());

}


And when I open the QDialog box and fill in this information
1
2
3
4
5
6
7
The SQL Warning message box displays it in this order
3
5
7
1
2
6
4
I don't know if that's a problem?

And the query.executedQuery()
returns this:

insert into clients (`cid`, `firstName`, `lastName`, `address`, `zipCode`, `city`, `phoneNr`, `email`) values ( '', ? , ? , ? , ? , ? , ? , ? )

So he didn't bind a value in the query?

[jpn]

Would you mind opening the database to SQLite Browser and executing the query?


So he didn't bind a value in the query?
Am I talking to a wall? I just told you that in my previous message, didn't I?

[cyberboy]

I'm really sorry, I did put the query in the SQLite browser.
And it said no error :D

But I discovered something!
First I made a full query and that one worked.
Second I made a prepare query but I didn't take the values from the QDialog but I defined my own QVariant var = "values"; and that one works too :)

So the problem is, at least I think, that the QString isn't converted well to a QVariant.

[jpn]

Output bound values like my example shows and you'll get more detailed information.

[cyberboy]

this may sound really stupid, but when I do a qDebug() I don't know where it dumps the output :confused: (yeah I'm a beginner)

[przemoc]

this may sound really stupid, but when I do a qDebug() I don't know where it dumps the output :confused: (yeah I'm a beginner)
I'm also a beginner, but I know that using documentation is crucial: qDebug() (http://doc.trolltech.com/latest/qtglobal.html#qDebug). If you don't know what is stderr (is it possible for programmer?), try Wikipedia: stderr (http://en.wikipedia.org/wiki/Standard_streams#Standard_error_.28stderr.29).