PaceyIV
26th March 2009, 14:16
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();
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();
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::codecF orName("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;
}
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
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
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();
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();
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::codecF orName("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;
}
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
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