no query unable to fetch row

hubeytqew

I want to create a db file then create a table if the db file does not exists...so for the first run.
Where is the problem? I get the error in the if(!qry.exec()) line. I think error comes due to the create db file part. Which is file.open(QIODevice::ReadWrite)

So I think, I can not create and open file properly.

if(!QFileInfo::exists("some_location/database.db"))
    {
        QFile file("some_location/database.db");
        file.open(QIODevice::ReadWrite);
        QSqlDatabase dbcreate = QSqlDatabase::addDatabase("QSQLITE");
        dbcreate.setDatabaseName("some_location/database.db");
        dbcreate.open();
        QSqlQuery qry;
        qry.prepare("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(64), password varchar(64), source varchar(64), key varchar(64)");
        if(!qry.exec())
        {
            QMessageBox::warning(this, tr("error::"), qry.lastError().text());
        }
    }

hubeytqew

.......this is so annoying.... I missed paranthesis :D:D:D:D:D

@hubeytqew said in no query unable to fetch row:

@JonB @Christian-Ehrlicher
I havent seen christian's first "dont use qfile" message or I missed it. Now, I remove QFile line.
qry.prepare("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(64), password varchar(64), source varchar(64), key varchar(64)");

There should be one more paranthesis ("...., key varchar(64))");
.................................................................................................................................^

So,

@ChrisW67 said in no query unable to fetch row:

You generally use prepare() on data manipulation queries (SELECT, UPDATE, DELETE), potentially with parameters, and not data definition queries (CREATE TABLE etc.)
Try removing QSqlQuery and executing the SQL directly using QSqlDatabase::exec().

prepare() and QSqlDatabase::exec() and QSqlQuery::exec() now working correctly.

@ChrisW67 said in no query unable to fetch row:

@hubeytqew Did you do as I asked, or are you just stating what you think is happening?

There is no problem with the location at all.
The problem was elsewhere....

Christian Ehrlicher

Check the return value of dbcreate.open() and don't open the database with a QFile (why do you need to open the database file with QFile at all?)

hubeytqew

@Christian-Ehrlicher
if I dont open a file, how can I read&write this file, idk? which method should I open a file?
dbcreate.open() return value is true.

ChrisW67

@hubeytqew QSqlDatabase using the Sqlite driver will open or create, read, write, and manage the file you set with setDatabaseName().

Your code should not assume the QSqlDatabase::open() call returns true and just carry on regardless.

if (dbcreate.open()) {
    QSqlQuery qry;
    qry.prepare("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(64), password varchar(64), source varchar(64), key varchar(64)");
    if(!qry.exec())    {
        QMessageBox::warning(this, tr("error::"), qry.lastError().text());
    }
}
else  {
// report the failure to open
}

Be careful if you are using relative paths to identify the database file. The process current working directory may not be where you think it is.

As for the title of this thread, "no query unable to fetch row", nothing in the code presented attempts to fetch anything. The premise of the code presented is that the database does not exist.

hubeytqew

@ChrisW67
I know what is the purpose of the code and weird error about it actually the point of I can not understand.

The code you write is getting same error. There is no problem with the location of the db file. Also, I use sqlite database.

ChrisW67

I assume the the call to prepare() is failing.

You generally use prepare() on data manipulation queries (SELECT, UPDATE, DELETE), potentially with parameters, and not data definition queries (CREATE TABLE etc.)
Try removing QSqlQuery and executing the SQL directly using QSqlDatabase::exec().

hubeytqew

@ChrisW67

    if(!QFileInfo::exists("location/database.db"))
    {
        QFile file("location/database.db");
        file.open(QIODevice::ReadWrite);
        QSqlDatabase dbcreate = QSqlDatabase::addDatabase("QSQLITE");
        dbcreate.setDatabaseName("location/database.db");
        if(dbcreate.open())
        {
            dbcreate.exec("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(64), password varchar(64), source varchar(64), key varchar(64)");
        }
    }

still there is no table in the db file..interesting

Christian Ehrlicher

@hubeytqew said in no query unable to fetch row:

    QFile file("location/database.db");
    file.open(QIODevice::ReadWrite);

still there is no table in the db file..interesting

Because you still have the same error in there - remove the QFile usage!

JonB

@hubeytqew said in no query unable to fetch row:

still there is no table in the db file..interesting

You do not try to show a message/error if the dbcreate.open() fails nor do you test and report an error if the dbcreate.exec() fails. This is very unhelpful both to you and us. Error checking, especially during development, is vital.

Two people said you should not try to create the file via QFile.open(), yet you still do so. Why not follow peoples' advice?

hubeytqew

@JonB @Christian-Ehrlicher
I havent seen christian's first "dont use qfile" message or I missed it. Now, I remove QFile line.

    if(!QFileInfo::exists("location/database.db"))
        {
            QSqlDatabase dbcreate = QSqlDatabase::addDatabase("QSQLITE");
            dbcreate.setDatabaseName("location/database.db");
            if(dbcreate.open())
            {
                QSqlQuery qry;
                qry.prepare("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(64), password varchar(64), source varchar(64), key varchar(64)");
                if(!qry.exec())
                {
                    QMessageBox::warning(this, tr("error::"), qry.lastError().text());
                }
            }
            else
            {
                QMessageBox::warning(this, "Error", "Error here");
            }
        }

if there is no database file, it is creating by the code, ok. But "No query unable to fetch row" error still here. Btw, there is no table inside of it.

@JonB said in no query unable to fetch row:

You do not try to show a message/error if the dbcreate.open() fails nor do you test and report an error if the dbcreate.exec() fails.

I add show message if dbcreate.open() fail but actually it dont.

ChrisW67

@ChrisW67 said in no query unable to fetch row:

Be careful if you are using relative paths to identify the database file. The process current working directory may not be where you think it is.

Just repeating my earlier warning.

As an experiment, make the database path absolute (e.g. "/tmp/location.db") and check that file is created, and contains your table.

hubeytqew

@ChrisW67 the file location is a folder which exist on desktop.
"C:/Users/myusername/Desktop/project/database.db"
If database file not in the project folder, it is creating but there is no table inside of it.

ChrisW67

@hubeytqew Did you do as I asked, or are you just stating what you think is happening?

BTW: Your original code, with the unnecessary QFile lines, would create an empty file regardless of what the QSqlDatabase driver was doing.

hubeytqew

@ChrisW67
now, I do what you say. Result did not changed.
C:/Temp/database.db is the location

@ChrisW67 said in no query unable to fetch row:

BTW: Your original code, with the unnecessary QFile lines, would create an empty file regardless of what the QSqlDatabase driver was doing.

Ok, I get it. I am creating file with setDatabaseName(). QFile is not exist anymore.

hubeytqew

.......this is so annoying.... I missed paranthesis :D:D:D:D:D

@hubeytqew said in no query unable to fetch row:

@JonB @Christian-Ehrlicher
I havent seen christian's first "dont use qfile" message or I missed it. Now, I remove QFile line.
qry.prepare("CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY AUTOINCREMENT, username varchar(64), password varchar(64), source varchar(64), key varchar(64)");

There should be one more paranthesis ("...., key varchar(64))");
.................................................................................................................................^

So,

@ChrisW67 said in no query unable to fetch row:

You generally use prepare() on data manipulation queries (SELECT, UPDATE, DELETE), potentially with parameters, and not data definition queries (CREATE TABLE etc.)
Try removing QSqlQuery and executing the SQL directly using QSqlDatabase::exec().

prepare() and QSqlDatabase::exec() and QSqlQuery::exec() now working correctly.

@ChrisW67 said in no query unable to fetch row:

@hubeytqew Did you do as I asked, or are you just stating what you think is happening?

There is no problem with the location at all.
The problem was elsewhere....

JonB

@hubeytqew said in no query unable to fetch row:

.......this is so annoying.... I missed paranthesis :D:D:D:D:D

And did you check the return result from qry.prepare()? As advised, suggest you always check every return result available, especially when developing/something goes wrong....

Malachi

I was having a similar issue, but in my case I didn't realize that the QSqlQuery constructor executes the SQL immediately if you pass in the string.

That created the "unable to fetch row" condition when I subsequently called .exec since I was erroneously instructing it to run the same query again.