Database in a file?

[MTK358]

How do I use QSqlQuery to manipulate a database that's stored in a single file, not access a already-running program like MySQL?

[tbscope]

Use SQLite. A SQLite db is either a single file or in memory.

[saa7_go]

You can use sqlite database to stored data in a single file.

[MTK358]

Use SQLite. A SQLite db is either a single file or in memory.

Should I directly use SQLite's C++ API or use it as a "back end" for Qt's API (and if so, how)?

[saa7_go]

You can use QSQLITE driver to access sqlite database. Look at http://doc.trolltech.com/4.6/sql-cachedtable.html, especially in "Connecting to a Database" part.

[MTK358]

QSQLITE seems to work well.

Now a slightly unrelated problem, but I don't think I should start another thread for it:

How do I get a list of all the columns in a table?

[Lykurg]

How do I get a list of all the columns in a table?

I guess you can do it in SQLite by queriing the tablemaster or simply fetch one row, and use QSqlRecord for getting the column names.

[ecanela]

i think is better approach use the class QSqlRecord to get the field names from a SQL table,

QStringList MySQlClass::fieldNames(QString tableName)
{
    QString query ("select * from ");
    query = query.append(tableName)   
 
    QSqlQuery sqlQuery(query);
    QSqlRecord record = sqlQuery.record();  //get the field information for the current query.
 
   QStringList fieldNames;
    for ( int x = 0; x < record.count(); ++x)
   {
       fieldsName.append( record.fieldName(x) );
   }
   return fieldNames;
}

QStringList MySQlClass::fieldNames(QString tableName)
{
    QString query ("select * from ");
    query = query.append(tableName)   
 
    QSqlQuery sqlQuery(query);
    QSqlRecord record = sqlQuery.record();  //get the field information for the current query.

   QStringList fieldNames;
    for ( int x = 0; x < record.count(); ++x)
   {
       fieldsName.append( record.fieldName(x) );
   }
   return fieldNames;
}

To copy to clipboard, switch view to plain text mode 

this should work. and works on MySQL, Firebird, SQlite, etc

if you need the database in one file and SQlite is a little small for yout project. firebird is a another option.

[brcain]

You can use QSQLITE driver to access sqlite database. Look at http://doc.trolltech.com/4.6/sql-cachedtable.html, especially in "Connecting to a Database" part.

Using QSQLITE driver, how do you persist the SQLite database to a file? That's the power of SQLite ... a serverless, self-contained, file-based SQL solution.

I was able to get the Cached Table Example to compile and run, but I don't see how to save it to a file.

[brcain]

I was able to answer my own question with a little experimentation. Although I'd like to find this in the documentation ... still looking.

The example, uses an in-memory database.

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

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

To copy to clipboard, switch view to plain text mode 

I noticed that changing the database name, resulted in using a file for the database. If it doesn't exist, one is created; if it exists, then that's the one that's loaded. Sweet!!!

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

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

To copy to clipboard, switch view to plain text mode 

[Lykurg]

You will find this in the documentation of QSQLite: http://www.sqlite.org/docs.html.

[brcain]

You will find this in the documentation of QSQLite: http://www.sqlite.org/docs.html.

I saw the SQLite documentation describing sqlite3_open(":memory:"), but I didn't correlate that to QSqlDatabase::setDatabaseName(QString&). I see it now though