QSqlQueryModel from an Access database converted in SQLite

[PaceyIV]

I'm trying to import a M$ Access database *.mdb into a SQLite.
My database is made by Extreme Movie Manager, a windows software to manage a Movie collection.

I read it on my Linux with MDB Tools Library.

In Qt I can read the table that I made whit this peace of code:

QSqlQuery query;
    query.exec("select MovieID, Title from Movies");
 
    while(query.next())
        qDebug() << query.value(0).toString() << " - " << query.value(1).toString();

QSqlQuery query;
    query.exec("select MovieID, Title from Movies");

    while(query.next())
        qDebug() << query.value(0).toString() << " - " << query.value(1).toString();

To copy to clipboard, switch view to plain text mode 

But if I try to show the database on a QTableView I obtain this error message: "The program has unexpectedly finished."

QSqlQueryModel *model;
model->setQuery("select MovieID, Title from Movies");
model->setHeaderData(0, Qt::Horizontal, QObject::tr("MovieID"));
model->setHeaderData(1, Qt::Horizontal, QObject::tr("Title"));
 
QTableView *view = new QTableView;
view->setModel(model);
view->setWindowTitle("Prova");
view->show();

QSqlQueryModel *model;
model->setQuery("select MovieID, Title from Movies");
model->setHeaderData(0, Qt::Horizontal, QObject::tr("MovieID"));
model->setHeaderData(1, Qt::Horizontal, QObject::tr("Title"));

QTableView *view = new QTableView;
view->setModel(model);
view->setWindowTitle("Prova");
view->show();

To copy to clipboard, switch view to plain text mode 

The program fail on the second line.

Below the second question the complete code.

What type of data should I specify in the SQLite table create command? I saw this page: http://www.sqlite.org/datatype3.html. But here there aren't int o varchar that I can see in the Qt Examples.

Main

#include <QtGui/QApplication>
//#include <QTextCodec>
 
//#include <QtGui>
#include <QSqlQueryModel>
#include <QTableView>
 
#include "connection.h"
 
int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    //QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
    //QTextCodec::setCodecForTr(QTextCodec::codecForName("UTF-8"));
 
    if (!createConnection("example.mdb"))
        return 1;
 
    QSqlQuery query;
    query.exec("select MovieID, Title from Movies");
 
    while(query.next())
        qDebug() << query.value(0).toString() << " - " << query.value(1).toString();
 
    QSqlQueryModel *model;
    model->setQuery("select MovieID, Title from Movies");
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("MovieID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Title"));
 
    QTableView *view = new QTableView;
    view->setModel(model);
    view->setWindowTitle("Prova");
    view->show();
 
    return app.exec();
 
    delete view;
}

#include <QtGui/QApplication>
//#include <QTextCodec>

//#include <QtGui>
#include <QSqlQueryModel>
#include <QTableView>

#include "connection.h"

int main(int argc, char *argv[])
{
    QApplication app(argc, argv);
    //QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8"));
    //QTextCodec::setCodecForTr(QTextCodec::codecForName("UTF-8"));

    if (!createConnection("example.mdb"))
        return 1;

    QSqlQuery query;
    query.exec("select MovieID, Title from Movies");

    while(query.next())
        qDebug() << query.value(0).toString() << " - " << query.value(1).toString();

    QSqlQueryModel *model;
    model->setQuery("select MovieID, Title from Movies");
    model->setHeaderData(0, Qt::Horizontal, QObject::tr("MovieID"));
    model->setHeaderData(1, Qt::Horizontal, QObject::tr("Title"));

    QTableView *view = new QTableView;
    view->setModel(model);
    view->setWindowTitle("Prova");
    view->show();

    return app.exec();

    delete view;
}

To copy to clipboard, switch view to plain text mode 

Connection.h

#ifndef CONNECTION_H
#define CONNECTION_H
 
#include <QtDebug>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
 
// MDB Tools
#include <mdbtools.h>
#include <mdbsql.h>
 
const QString strCreateTable = "CREATE TABLE Movies ("
"MovieID INTEGER PRIMARY KEY"                           //   1
", Title TEXT"
")";
 
const QString strAddData = "INSERT INTO Movies ("
"MovieID, Title"
") VALUES ("
":MovieID, :Title" 
")";
 
static void addData(QSqlQuery *qry, MdbSQL *sql)
{
    qry->prepare( strAddData );
    int i=0;
    qry->bindValue(":MovieID", sql->bound_values[i++]); // TODO Long
    qry->bindValue(":Title", sql->bound_values[i++]);
}
 
static bool createConnection(const QString &fileName)
{
    qDebug() << "MDBSQL: Engine initialize";
    MdbSQL *sql = mdb_sql_init ();
 
    qDebug() << "MDBSQL: Open database: " << fileName;
    if (!(sql->mdb = mdb_open (fileName.toLatin1() , MDB_NOFLAGS))) {
        qDebug() << "MDBSQL: Failed to open database";
        QMessageBox::warning(0, qApp->tr("Cannot open database"),
                             qApp->tr("Unable to establish a connection"
                                      "to the database %1").arg(fileName));
        mdb_sql_exit(sql);
        mdb_exit();
        return false;
    }
 
    qDebug() << "SQLite: Create database on memory";
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        qDebug() << "SQlite: Failed to create database";
        QMessageBox::warning(0, qApp->tr("Cannot open database"),
                             qApp->tr("Unable to establish a connection"
                                      "to the database %1").arg(fileName));
        mdb_sql_exit(sql);
        mdb_exit();
        return false;
    }
 
    QSqlQuery query;
    query.prepare( strCreateTable );
    if (!query.exec()) {
        qDebug() << "SQLITE: Failed to create the table to store data";
        QMessageBox::warning(0, qApp->tr("Cannot open database"),
                             qApp->tr("Unable to establish a connection"
                                      "to the database %1").arg(fileName));
        mdb_sql_close(sql);
        mdb_sql_exit(sql);
        mdb_exit();
        return false;
    }
 
    qDebug() << "SQLite: Start convertion database from MDB Tool Library";
    sql = mdb_sql_run_query (sql, "SELECT * FROM Movies");
    mdb_sql_bind_all(sql);
    while (mdb_fetch_row(sql->cur_table)) {
        addData(&query, sql);
        if (!query.exec()) {
            qDebug() << "SQLITE: Failed to add a record on the database";
            QMessageBox::warning(0, qApp->tr("Cannot open database"),
                                    qApp->tr("Unable to establish a connection"
                                             "to the database %1").arg(fileName));
            mdb_sql_close (sql);
            mdb_sql_exit(sql);
            mdb_exit();
            return false;
        }
    }
    qDebug() << "SQLite: Load data complete!";
 
    qDebug() << "Terminate MDB SQL Engine";
    mdb_sql_reset(sql);
    mdb_sql_close(sql);
    mdb_sql_exit(sql);
    mdb_exit();
 
 
    return true;
}
 
#endif // CONNECTION_H

#ifndef CONNECTION_H
#define CONNECTION_H

#include <QtDebug>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

// MDB Tools
#include <mdbtools.h>
#include <mdbsql.h>

const QString strCreateTable = "CREATE TABLE Movies ("
"MovieID INTEGER PRIMARY KEY"                           //   1
", Title TEXT"
")";

const QString strAddData = "INSERT INTO Movies ("
"MovieID, Title"
") VALUES ("
":MovieID, :Title" 
")";

static void addData(QSqlQuery *qry, MdbSQL *sql)
{
    qry->prepare( strAddData );
    int i=0;
    qry->bindValue(":MovieID", sql->bound_values[i++]); // TODO Long
    qry->bindValue(":Title", sql->bound_values[i++]);
}

static bool createConnection(const QString &fileName)
{
    qDebug() << "MDBSQL: Engine initialize";
    MdbSQL *sql = mdb_sql_init ();

    qDebug() << "MDBSQL: Open database: " << fileName;
    if (!(sql->mdb = mdb_open (fileName.toLatin1() , MDB_NOFLAGS))) {
        qDebug() << "MDBSQL: Failed to open database";
        QMessageBox::warning(0, qApp->tr("Cannot open database"),
                             qApp->tr("Unable to establish a connection"
                                      "to the database %1").arg(fileName));
        mdb_sql_exit(sql);
        mdb_exit();
        return false;
    }

    qDebug() << "SQLite: Create database on memory";
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        qDebug() << "SQlite: Failed to create database";
        QMessageBox::warning(0, qApp->tr("Cannot open database"),
                             qApp->tr("Unable to establish a connection"
                                      "to the database %1").arg(fileName));
        mdb_sql_exit(sql);
        mdb_exit();
        return false;
    }

    QSqlQuery query;
    query.prepare( strCreateTable );
    if (!query.exec()) {
        qDebug() << "SQLITE: Failed to create the table to store data";
        QMessageBox::warning(0, qApp->tr("Cannot open database"),
                             qApp->tr("Unable to establish a connection"
                                      "to the database %1").arg(fileName));
        mdb_sql_close(sql);
        mdb_sql_exit(sql);
        mdb_exit();
        return false;
    }

    qDebug() << "SQLite: Start convertion database from MDB Tool Library";
    sql = mdb_sql_run_query (sql, "SELECT * FROM Movies");
    mdb_sql_bind_all(sql);
    while (mdb_fetch_row(sql->cur_table)) {
        addData(&query, sql);
        if (!query.exec()) {
            qDebug() << "SQLITE: Failed to add a record on the database";
            QMessageBox::warning(0, qApp->tr("Cannot open database"),
                                    qApp->tr("Unable to establish a connection"
                                             "to the database %1").arg(fileName));
            mdb_sql_close (sql);
            mdb_sql_exit(sql);
            mdb_exit();
            return false;
        }
    }
    qDebug() << "SQLite: Load data complete!";

    qDebug() << "Terminate MDB SQL Engine";
    mdb_sql_reset(sql);
    mdb_sql_close(sql);
    mdb_sql_exit(sql);
    mdb_exit();


    return true;
}

#endif // CONNECTION_H

To copy to clipboard, switch view to plain text mode 

Project

# -------------------------------------------------
# Project created by QtCreator 2009-03-26T12:16:36
# -------------------------------------------------
QT += sql \
    core
TARGET = mdb2qt
TEMPLATE = app
SOURCES += main.cpp
HEADERS += connection.h
FORMS += 
 
# Mdb Tools
LIBS += -lmdb \
    -lmdbsql \
    -lglib-2.0
INCPATH += /usr/include/glib-2.0 \
    /usr/lib/glib-2.0/include

# -------------------------------------------------
# Project created by QtCreator 2009-03-26T12:16:36
# -------------------------------------------------
QT += sql \
    core
TARGET = mdb2qt
TEMPLATE = app
SOURCES += main.cpp
HEADERS += connection.h
FORMS += 

# Mdb Tools
LIBS += -lmdb \
    -lmdbsql \
    -lglib-2.0
INCPATH += /usr/include/glib-2.0 \
    /usr/lib/glib-2.0/include

To copy to clipboard, switch view to plain text mode 

[PaceyIV]

Question 1 fixed by myself. There was a * in model without the new QSqlQueryModel.