QSqlQuery::value: not positioned on a valid record

Ocascante

Hello,

I did a little application using an sqlite database. I added the sqlite database to my project resources files because i need to pack the database into the application

My code to open the database:
QSqlDatabase db;
db = QSqlDatabase::addDatabase("QSQLITE");

db.setDatabaseName(":/Rutasdb.sqlite");
if(db.open()){
qDebug()<<"Database Open!!";
query.exec("SELECT COUNT(*) FROM table);
while (query.next(){ .....
}
else
{
qDebug()<<"NO DATABASE";
}
The program opens the database , but generates the error and does not display the data.

QtCreator 5.5.1 - Ubuntu Linux 14.04

Thanks for your help!

mrjj

hi and welcome

Does the exact same code work when you load it from
hard drive ?

Its possible to open it in READONLY mode.
https://www.sqlite.org/c3ref/open.html
SQLITE_OPEN_READONLY
maybe you can do that in Qt too ?
(since as resource, its read only)

Found it:
db.setConnectOptions(QLatin1String("QSQLITE_OPEN_READONLY"));

https://bugreports.qt.io/browse/QTBUG-34920

Ocascante

Hi,
Yes, the same code works ok loading from hard disk. I test this code:
QSqlDatabase db;
db.setConnectOptions(QLatin1String("QSQLITE_OPEN_READONLY"));
db = QSqlDatabase::addDatabase("QSQLITE");
.....

Got same error

mrjj

@Ocascante
Hmm. Ok.
So it seems it don't like the db to be in a resource.
just to be 100% sure can you swap the lines?
QSqlDatabase db;
db = QSqlDatabase::addDatabase("QSQLITE");
db.setConnectOptions(QLatin1String("QSQLITE_OPEN_READONLY"));

.....

Ocascante

@mrjj I did it, same error!!

The code:

if(db.open()){
qDebug()<<"DATABASE OPEN";
query.exec("SELECT COUNT(*) FROM Hours");
....

Debug:

DATABASE OPEN
QSqlQuery::exec: database not open

mrjj

@Ocascante
Ok. so if same code works from hard drive, then it don't like ressources

https://forum.qt.io/topic/3949/sqlite-database-in-project

Ocascante

@mrjj Do you know any other way to include a database ( not to create ) in a project?
Thanks again!

mrjj

@Ocascante
well you could have it as resource and then write it out to file.
Then use the file.
Since you will need tons of DLLS too and such. its it not an option to
just include the db file?

Ocascante

@mrjj I did a test including a .csv resource , and then creating the table from this file. The problem is that 1500 records with 5 fields each one, takes more than 5 minutes loading to the database.

mrjj

@Ocascante
ok. and the reason you want it in the resource, is to protect it from fiddling ?

SGaist

Hi and welcome to devnet,

As @mrjj suggested, SQLite can't read database from Qt's resources, it's not written for that. However you can copy the database from your executable to the hard drive in one of the writable location provide provided by QStandardPaths

Ocascante

@mrjj There are two main reasons:

I want to build this application for android
and to protect it from fiddling.

mcosta

Hi,

you could embed a database dump and import it each time you start the application

Ocascante

@mcosta Hi,

I need import a new database file, only when there is an application update.

Ocascante

@Ocascante Hi, thanks for your help. I found the solution using qt resources to pack my sqlite database, the code:

QString addin_path =QStandardPaths::writableLocation(QStandardPaths::DataLocation);

QDir dir(addin_path);
if (!dir.exists())
    dir.mkpath(addin_path);
if (!dir.exists("addins"))
    dir.mkdir("addins");

dir.cd("addins");
addin_path = dir.absoluteFilePath("Rutasdb.sqlite");
qDebug()<<addin_path;

if (QFile::exists(addin_path))
    QFile::remove(addin_path);

QFile::copy(":/Rutasdb.sqlite", addin_path);

QSqlDatabase db;
db = QSqlDatabase::addDatabase("QSQLITE");
db.setConnectOptions(QLatin1String("QSQLITE_OPEN_READONLY"));

db.setDatabaseName(addin_path);
db.open();

Now i can upgrade the data every update.

Thanks again.