Database: How to use an external file?

[goes2bob]

I was wondering if anyone could help point me in the right direction.

I am trying developing an application that uses an external sqlite database file.
I am able to create tables and populate them within my application, but I am finding it rather hard to find any documentation on how to use an existing sqlite file as the database to open/add/modify.

If I have an external sqlite3 database file that I created at the command line, and would like my app to use it.
It may be as easy as including the file? Or a little more to the setDatabaseName() line ??
if I need to add the file, I am not sure how to do that ( QFile, ????).

Thanks in advance...
goes2Bob

[jpn]

Have you seen the SQL examples shipped with Qt? They all use sqlite (see connection.h).

[goes2bob]

I have looked at conneciton.h and the examples. It appears that for the examples they use the 4 line db that they create from this connection.h header file. I would like to use an existing db that I have created.
For instance, the app uses a food database that I want to use to look-up values and use while the app is executing. I also want to be able to allow the user to add things to the db and have it in the db the next time the app is launched.

goes2Bob

[jpn]

Sorry, but what's the problem? What did you try so far? Just call QSqlDatabase::setDatabaseName() with a file name instead of ":memory:" and skip create table statements if they already exist.

[goes2bob]

I did. I got a query error that says the table does not exist? I did however put the open database statement in the .cpp file where I was using it. Does this matter? it looked like this:

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("test.db");

Do you see anything wrong here(missing ":" or something, I noticed the connection.h uses a ":" in front and back of memory)? My database open statement returns successfully, but the query error states that it cannot find a table I know is in there - The query statement I am using works from the command line.
One note, this is the first reference to "test.db" I have in the project, meaning it is not included anywhere else, could this be something?

goes2Bob

[jpn]

Hmm, are you 100% sure test.db lies in current working directory? What does

#include <QtDebug>
...
qDebug() << db.tables();

#include <QtDebug>
...
qDebug() << db.tables();

To copy to clipboard, switch view to plain text mode 

output?

[goes2bob]

I looked last night at where the db file was and it is in my workspace. By workspace, I mean that it is the /<proj name>/release directory (where I am running the executable). I also added the qDebug statement and it came back as "()" - I am guessing no tables, which explains why it cannot find the table. I was looking at the QSqlDatabase docs and found an example for an MS Access db statement which looked like this:

db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=myaccessfile.mdb");

db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=myaccessfile.mdb");

To copy to clipboard, switch view to plain text mode 

does there have to be anything in the "setDatabaseName" statement telling the application that the file is external? Remember as I stated in previous post that I have not included the external file in any way. The first time the application sees the filename is in the setDatabaseName statement. Should it be included in the .pro file? or as an extern?
Thanks in advance again,
goes2Bob

[jpn]

No, that's MS Access specific - SQLite takes the file name. I still suspect it's a problem with relative paths and that the current working directory is not the same where .db file is located.

Since you have test.db next to application executable, you could try:

QDir dir(QApplication::applicationDirPath());
db.setDatabaseName(dir.filePath("test.db"));
qDebug() << db.tables();

QDir dir(QApplication::applicationDirPath());
db.setDatabaseName(dir.filePath("test.db"));
qDebug() << db.tables();

To copy to clipboard, switch view to plain text mode 

[bender86]

You can also check if database exists using QFile::exists or QFileInfo::exists.

[goes2bob]

Success ! ! adding the "setDatabaseName(dir.filePath("test.db")); " allowed the app to find the file - and queries work just as they should.
This begs the question: why was that path direction necessary?

I am using the Eclipse IDE, Qt 4.2, on WinXP.

Thanks for your patience,
goes2Bob

[jpn]

Consider the difference between:

C:\myapp> release\myapp.exe

and

C:\myapp\release> myapp.exe

When launching the application in first way, current working directory is "C:\myapp" whereas in the latter way it's "C:\myapp\release". Windows IDEs tend to launch the application from project dir, not from release or debug subdirectory.