How to check if Sqlite Database is valid?

Opa114

How can i check if a loaded (via file dialog) sqlite database is

a valid sqlite Database (e.g. version 3)
if the sqlite database is valid, did the content (columns in the table) did match to the ones in my create statement

For exampe my table inside my sqlite databse is created like this:

QSqlQuery query;
query.prepare("CREATE TABLE IF NOT EXISTS myTable"
                  "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
                  "data BLOB NOT NULL UNIQUE, "
                  "moreData BLOB, "
                  "moreData2 BLOB, "
                  "date DATETIME DEFAULT CURRENT_TIMESTAMP)");
query.exec();

There are any built in tools or commands for this? Or mst implement it by myself. If yes, how can i do this?

mrdebug

Please have a look
http://doc.qt.io/qt-5/qsqlquery.html#exec
exec() returns true or false.
If false lastError() contains the error as QSqlError.

SGaist

Hi,

To add to @mrdebug, the QSQLITE driver is for SQLite 3, you have to explicitly use a different driver for SQLite 2.
If a database file cannot be found, it will be created. That's nothing Qt specific, it's how SQLite works. If you want to know whether you start from a new empty db or from an existing one, the you have to first check if the file exists.

Opa114

Thanks for reply.
@mrdebug so you mean i could make a query a do a select on all my columns i want to check if them available? and if i got an error there i know the loaded database is not correct?

I found the tables() function, which i can run on my QSqlDatabase-Object andthen with exists i can check if a specific table exists - this check for the columns inside my table would be great.

@SGaist ah thanks for the hint with the driver, that is good to know, so tehre i'm fine to load the correct version of an sqlite database.

mrdebug

Hi, I suggest you to create a class to manage exceptions, so if you have a situation like this

if (!MyQuery.Exec("select ..")) GeneralException.raise();
if (!MyQuery.Exec("select ..")) GeneralException.raise();
if (!MyQuery.Exec("select ..")) GeneralException.raise();

if a query fails you will jump in the exception function manager.

If you want to have the list of the columns present in a sqlite database you should use the sqlite features, so
"pragma table_info(Table01)"
To have the list of the tables I think there is a similar way.

Regards.