QTableView from SQLite, empty!

[grellsworth]

I have broken the sql/relationaltablemodel example down into four distinct steps:

1) Create the database connection:
= sql/relationaltablemodel/relationaltablemodel.cpp:main() =

if (!createConnection())
   return 1;

if (!createConnection())
   return 1;

To copy to clipboard, switch view to plain text mode 

= sql/connection.h:createConnection() =

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            qApp->tr("Unable to establish a database connection.\n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }
 
    return true;
}

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
            qApp->tr("Unable to establish a database connection.\n"
                     "This example needs SQLite support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }

    return true;
}

To copy to clipboard, switch view to plain text mode 

2) Create the table in the database and add some records:
= sql/relationaltablemodel/relationaltablemodel.cpp:main() =

createRelationalTables();

createRelationalTables();

To copy to clipboard, switch view to plain text mode 

void createRelationalTables()
{
    QSqlQuery query;
    query.exec("create table employee(id int primary key, name varchar(20), city int, country int)");
    query.exec("insert into employee values(1, 'Espen', 5000, 47)");
}

void createRelationalTables()
{
    QSqlQuery query;
    query.exec("create table employee(id int primary key, name varchar(20), city int, country int)");
    query.exec("insert into employee values(1, 'Espen', 5000, 47)");
}

To copy to clipboard, switch view to plain text mode 

3) Create the relational table model and initialize it:
= sql/relationaltablemodel/relationaltablemodel.cpp:main() =

QSqlRelationalTableModel model;
 
    initializeModel(&model);

QSqlRelationalTableModel model;

    initializeModel(&model);

To copy to clipboard, switch view to plain text mode 

void initializeModel(QSqlRelationalTableModel *model)
{
    model->setTable("employee");
 
    model->setEditStrategy(QSqlTableModel::OnManualSubmit);
 
    model->select();
}

void initializeModel(QSqlRelationalTableModel *model)
{
    model->setTable("employee");

    model->setEditStrategy(QSqlTableModel::OnManualSubmit);

    model->select();
}

To copy to clipboard, switch view to plain text mode 

4) Set the QTableView to use the model:
= sql/relationaltablemodel/relationaltablemodel.cpp:main() =

QTableView *view = createView(QObject::tr("Relational Table Model"), &model);

QTableView *view = createView(QObject::tr("Relational Table Model"), &model);

To copy to clipboard, switch view to plain text mode 

QTableView *createView(const QString &title, QSqlTableModel *model)
{
    QTableView *view = new QTableView;
    view->setModel(model);
    view->setItemDelegate(new QSqlRelationalDelegate(view));
    view->setWindowTitle(title);
    return view;
}

QTableView *createView(const QString &title, QSqlTableModel *model)
{
    QTableView *view = new QTableView;
    view->setModel(model);
    view->setItemDelegate(new QSqlRelationalDelegate(view));
    view->setWindowTitle(title);
    return view;
}

To copy to clipboard, switch view to plain text mode 

Now here's my modified code, trying to do the same thing:
1) Create the database connection:
= mymuse/mainwindow.cpp:initMainWindow() =

database = new SQLiteDB();
 
   printf("1\n");
   database->createConnection();

database = new SQLiteDB();

   printf("1\n");
   database->createConnection();

To copy to clipboard, switch view to plain text mode 

= mymuse/sqlite.cpp =

bool SQLiteDB::createConnection()
{
   QString dbName = ":memory:";
 
   QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
   database.setDatabaseName(dbName);
 
   if(!database.open())
   {
      QMessageBox::critical(NULL, qApp->tr("Cannot open database"),
         qApp->tr("Unable to establish a connection with database '%1'.")
         .arg(dbName),
         QMessageBox::Cancel);
 
      return false;
   }
 
   return true;
}

bool SQLiteDB::createConnection()
{
   QString dbName = ":memory:";

   QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
   database.setDatabaseName(dbName);

   if(!database.open())
   {
      QMessageBox::critical(NULL, qApp->tr("Cannot open database"),
         qApp->tr("Unable to establish a connection with database '%1'.")
         .arg(dbName),
         QMessageBox::Cancel);

      return false;
   }

   return true;
}

To copy to clipboard, switch view to plain text mode 

2) Create the table in the database and add some records:
= mymuse/mainwindow.cpp:initMainWindow() =

printf("2\n");
   database->createTable();

printf("2\n");
   database->createTable();

To copy to clipboard, switch view to plain text mode 

= mymuse/sqlite.cpp =

bool SQLiteDB::execQuery(QString queryStr, bool showError)
{
   QSqlQuery query;
 
   if(!query.exec(queryStr))
   {
      queryErr = query.lastError();
      if(showError)
      {
         QMessageBox::critical(NULL, qApp->tr("Query error"),
            qApp->tr("%1\n%2")
            .arg(lastQueryError(SQL_DB_TEXT))
            .arg(lastQueryError(SQL_DRIVER_TEXT)),
            QMessageBox::Cancel);
      }
      return false;
   }
 
   return true;
}
 
void SQLiteDB::createTable()
{
   execQuery("create table tracks (id integer primary key autoincrement, artist varchar(50), title varchar(80), "
      "rating integer, status integer)", true);
   execQuery("insert into tracks (artist, title, rating, status) "
      "values ('The Rolling Stones', 'Satisfaction', 33, 1)", true);
}

bool SQLiteDB::execQuery(QString queryStr, bool showError)
{
   QSqlQuery query;

   if(!query.exec(queryStr))
   {
      queryErr = query.lastError();
      if(showError)
      {
         QMessageBox::critical(NULL, qApp->tr("Query error"),
            qApp->tr("%1\n%2")
            .arg(lastQueryError(SQL_DB_TEXT))
            .arg(lastQueryError(SQL_DRIVER_TEXT)),
            QMessageBox::Cancel);
      }
      return false;
   }

   return true;
}

void SQLiteDB::createTable()
{
   execQuery("create table tracks (id integer primary key autoincrement, artist varchar(50), title varchar(80), "
      "rating integer, status integer)", true);
   execQuery("insert into tracks (artist, title, rating, status) "
      "values ('The Rolling Stones', 'Satisfaction', 33, 1)", true);
}

To copy to clipboard, switch view to plain text mode 

3) Create the relational table model and initialize it:
= mymuse/mainwindow.cpp:initMainWindow() =

printf("3\n");
   database->initModel();

printf("3\n");
   database->initModel();

To copy to clipboard, switch view to plain text mode 

= mymuse/sqlite.cpp =

void SQLiteDB::initModel()
{
   model.setTable("tracks");
   model.setEditStrategy(QSqlTableModel::OnManualSubmit);
   model.select();
}

void SQLiteDB::initModel()
{
   model.setTable("tracks");
   model.setEditStrategy(QSqlTableModel::OnManualSubmit);
   model.select();
}

To copy to clipboard, switch view to plain text mode 

4) Set the QTableView to use the model:
= mymuse/mainwindow.cpp =

printf("4\n");
   database->setView(trackTable);

printf("4\n");
   database->setView(trackTable);

To copy to clipboard, switch view to plain text mode 

= mymuse/sqlite.cpp =

void SQLiteDB::setView(QTableView *table)
{
   QSqlTableModel *tblModel = &model;
 
   table->setModel(tblModel);
   table->setItemDelegate(new QSqlRelationalDelegate(table));
}

void SQLiteDB::setView(QTableView *table)
{
   QSqlTableModel *tblModel = &model;

   table->setModel(tblModel);
   table->setItemDelegate(new QSqlRelationalDelegate(table));
}

To copy to clipboard, switch view to plain text mode 

I've created my UI using designer, and have used the multiple-inheritance model to implement it. I have a class (in mainwindow.h) called:

class MainWindow : public QMainWindow, public Ui::MainWindow {};

The code is in mainwindow.cpp.

My application comes up, and numbers 1 thru 4 print out, but my QTableView remains empty, why?

Sincerely,

Gordon E.

[jacek]

Does "model" still exist when you show the table view?
Where does "trackView" come from? Are you sure it is the same QTableView as the one you see?

[grellsworth]

1)

Does "model" still exist when you show the table view?

Yes, "model" is defined in the class I use for sql code:

class SQLiteDB : public QObject
{
   Q_OBJECT
 
   public:
   // Constructors and ~Destructors
      SQLiteDB();
      virtual ~SQLiteDB();
 
   // Public Members
      QSqlRelationalTableModel model;
...
};

class SQLiteDB : public QObject
{
   Q_OBJECT

   public:
   // Constructors and ~Destructors
      SQLiteDB();
      virtual ~SQLiteDB();

   // Public Members
      QSqlRelationalTableModel model;
...
};

To copy to clipboard, switch view to plain text mode 

2)

Where does "trackView" come from?

I created my user interface using Qt's Designer. trackView is a QTableView that I added to my layout in Designer:

= FROM ui_mymuse.h =

class Ui_MainWindow
{
public:
... some widgets ...
   QWidget *centralwidget;
   QTableView *trackTable;
... later ...
   void setupUi(QMainWindow *MainWindow)
   {
      centralwidget = new QWidget(MainWindow);
      trackTable = new QTableView(centralwidget);
   }
};

class Ui_MainWindow
{
public:
... some widgets ...
   QWidget *centralwidget;
   QTableView *trackTable;
... later ...
   void setupUi(QMainWindow *MainWindow)
   {
      centralwidget = new QWidget(MainWindow);
      trackTable = new QTableView(centralwidget);
   }
};

To copy to clipboard, switch view to plain text mode 

3)

Are you sure it is the same QTableView as the one you see?

Yes, I've tried hiding/showing it in my code by using trackTable->hide() and trackTable->show() and that works correctly.

[jacek]

You first create the model and then the QSqlDatabase instance. Qt might not like this.

You can change SQLiteDB::createConnection() into a static method and invoke it before you instantiate SQLiteDB.

[grellsworth]

You first create the model and then the QSqlDatabase instance. Qt might not like this.

You can change SQLiteDB::createConnection() into a static method and invoke it before you instantiate SQLiteDB.

First of all, a BIG Thank you to jacek, for trying to help... I have had a little bit more success. My table comes up now with columns and rows, but they have no information in them, this is an improvement over a totally empty QTableView.

I tried totally getting rid of my SQLiteDB calls, and doing all the code in one place, in my initMainWidow function (which gets called by my MainWindow contructor).

Here's my code as it stands right now:

void MainWindow::initMainWindow()
{
   settings = new QSettings(APPNAME, DEVNAME);
 
   QString dbName = ":memory:";
 
   QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
   database.setDatabaseName(dbName);
 
   if(!database.open())
   {
      QMessageBox::critical(NULL, qApp->tr("Cannot open database"),
         qApp->tr("Unable to establish a connection with database '%1'.")
         .arg(dbName),
         QMessageBox::Cancel);
 
         return;
   }
 
   QSqlQuery query;
   if(!query.exec("create table tracks (id integer primary key autoincrement, artist varchar(50), "
      "title varchar(80), rating integer, status integer)"))
   {
      printf("Error 1\n");
   }
   else
   {
      printf("OK 1\n");
   }
   if(!query.exec("insert into tracks (artist, title, rating, status) values ('The Rolling Stones', 'Satisfaction', 33, 1)"))
   {
      printf("Error 2\n");
   }
   else
   {
      printf("OK 2\n");
   }
   if(!query.exec("insert into tracks (artist, title, rating, status) values ('The Doors', 'Riders on the Storm', 75, 1)"))
   {
      printf("Error 3\n");
   }
   else
   {
      printf("OK 3\n");
   }
   query.next();
 
   QSqlRelationalTableModel model;
 
   model.setTable("tracks");
   model.setEditStrategy(QSqlTableModel::OnManualSubmit);
   model.select();
 
   setTable(&model);
}
 
void MainWindow::setTable(QSqlTableModel *tblModel)
{
   trackTable->setModel(tblModel);
   trackTable->setItemDelegate(new QSqlRelationalDelegate(trackTable));
   trackTable->show();
}

void MainWindow::initMainWindow()
{
   settings = new QSettings(APPNAME, DEVNAME);

   QString dbName = ":memory:";

   QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");
   database.setDatabaseName(dbName);

   if(!database.open())
   {
      QMessageBox::critical(NULL, qApp->tr("Cannot open database"),
         qApp->tr("Unable to establish a connection with database '%1'.")
         .arg(dbName),
         QMessageBox::Cancel);

         return;
   }

   QSqlQuery query;
   if(!query.exec("create table tracks (id integer primary key autoincrement, artist varchar(50), "
      "title varchar(80), rating integer, status integer)"))
   {
      printf("Error 1\n");
   }
   else
   {
      printf("OK 1\n");
   }
   if(!query.exec("insert into tracks (artist, title, rating, status) values ('The Rolling Stones', 'Satisfaction', 33, 1)"))
   {
      printf("Error 2\n");
   }
   else
   {
      printf("OK 2\n");
   }
   if(!query.exec("insert into tracks (artist, title, rating, status) values ('The Doors', 'Riders on the Storm', 75, 1)"))
   {
      printf("Error 3\n");
   }
   else
   {
      printf("OK 3\n");
   }
   query.next();

   QSqlRelationalTableModel model;

   model.setTable("tracks");
   model.setEditStrategy(QSqlTableModel::OnManualSubmit);
   model.select();

   setTable(&model);
}

void MainWindow::setTable(QSqlTableModel *tblModel)
{
   trackTable->setModel(tblModel);
   trackTable->setItemDelegate(new QSqlRelationalDelegate(trackTable));
   trackTable->show();
}

To copy to clipboard, switch view to plain text mode 

So now I get a QTableView that looks like this:

-------------------------------------
   | 1 | 2 | 3 | 4 | 5 |
-------------------------------------
 1 |   |   |   |   |   |
-------------------------------------
 2 |   |   |   |   |   |
-------------------------------------

-------------------------------------
   | 1 | 2 | 3 | 4 | 5 |
-------------------------------------
 1 |   |   |   |   |   |
-------------------------------------
 2 |   |   |   |   |   |
-------------------------------------

To copy to clipboard, switch view to plain text mode 

The fact that I get five columns and two rows is promising, at least it sees that I have two records with five fields each, but why aren't the cells being populated with data?

Sincerely,

Gordon E.

[jacek]

QSqlRelationalTableModel model;

model.setTable("tracks");
model.setEditStrategy(QSqlTableModel::OnManualSubm it);
model.select();

setTable(&model);

You create the model on the stack, so it gets destroyed as soon as it goes out of scope, leaving a dangling pointer behind. Better create that model on the heap (i.e. using the new operator).

[grellsworth]

You create the model on the stack, so it gets destroyed as soon as it goes out of scope, leaving a dangling pointer behind. Better create that model on the heap (i.e. using the new operator).

Yeah, it works now... so I guess you have to follow the following (undocumented) rules:

1) You must create your database connection before you create your (QSqlRelatinoalTableModel) model.
2) You must create your QSqlRelationalTableModel object on the heap, using the 'new' operator.

Did I miss anything?

[jacek]

2) You must create your QSqlRelationalTableModel object on the heap, using the 'new' operator.

Not exactly. You can create it on the stack, but you must make sure it exists long enough.

[grellsworth]

OK, so it should be:

2) Make sure your QSqlRelationalTableModel object doesn't go out of scope.

[jacek]

OK, so it should be:

2) Make sure your QSqlRelationalTableModel object doesn't go out of scope.

Yes, but this isn't something that must be documented.