Archive

Posts Tagged ‘sql’

C++: Qt and SQL

February 18, 2010 Leave a comment

Today I want to write something about Qt and its SQL-support. It is quite easy to use it, much more simple than working directly with ODBC and/or SQLite or other libraries because Qt has “build-in” support, you just have to add QT += sql to your .pro-file and specify which library you want to work with.

But let’s start now. I want to demonstrate how easy it is to create a simple timetable with SQLite that looks something like this if you wrap it into a GUI:

Here are the includes:

#include <QTableView>
#include <QSqlDriver>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlTableModel>
// #include <QSqlError>

At first, create the database using SQLite via

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

Then open it (and check it meanwhile) with

if(!db.open()) {
   exit(0); // or do something else
}

Now insert the table using a SQL query:

QSqlQuery query;
query.exec("CREATE TABLE IF NOT EXISTS tableName ("
           "monday varchar(200), "
           "tuesday varchar(200), "
           "wednesday varchar(200), "
           "thursday varchar(200), "
           "friday varchar(200), "
           "saturday varchar(200), "
           "sunday varchar(200))");

After that create a model:

QSqlTableModel model;
model.setTable("tableName");
model.setEditStrategy(QSqlTableModel::OnManualSubmit);

If you choose “OnManualSubmit” as edit strategy you have to save the changes you make inside the model by your own (explanation comes later).
Then set the model header data (Monday-Sunday in the picture):

model.setHeaderData(0, Qt::Horizontal, "Monday");
model.setHeaderData(1, Qt::Horizontal, "Tuesday");
model.setHeaderData(2, Qt::Horizontal, "Wednesday");
model.setHeaderData(3, Qt::Horizontal, "Thursday");
model.setHeaderData(4, Qt::Horizontal, "Friday");
model.setHeaderData(5, Qt::Horizontal, "Saturday");
model.setHeaderData(6, Qt::Horizontal, "Sunday");

and set the model to the view to display it:

model.select();
QTableView view;
view.setModel(model);

But what if you want to add an additional line with an entry in every row? The best news is that you don’t have to write any SQL for adding or deleting if you don’t want to:

int rowCnt = model.rowCount();
model.insertRow(rowCnt);
for(int i = 0; i < model.columnCount(); i++) {
   model.setData(model.index(rowCnt, i), "Put this Entry in every Row");
}
model.submitAll();
model.select();
view.setModel(model);

And if you want to delete the last row:

model.removeRow(model.rowCount() - 1);
model.submitAll();
model.select();
view.setModel(model);

You have to call “submitAll” because of the selected strategy┬á“OnManualSubmit” that doesn’t save by it’s own.
To clear the whole database, drop the table by querying

query.exec("DROP TABLE IF EXISTS tableName");

After setting up a model and a view you can change your table inside the view, press “save” and everything else is “done by Qt”!

You can find the whole Application at CallToPower – Software – qTimeTable, have fun experimenting with it!

Follow

Get every new post delivered to your Inbox.