How can i execute a database script within Qt?

[NoRulez]

Hey @all,

i have a sql script in the resource file, which creates all necessary database tables, indexes and insert some initial data.

How can i execute these script?

Here is an example of such script:

CREATE TABLE "table1" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "bla1" TEXT,
);
CREATE TABLE "table2" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "bla2" TEXT,
);

CREATE TABLE "table1" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "bla1" TEXT,
);
CREATE TABLE "table2" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "bla2" TEXT,
);

To copy to clipboard, switch view to plain text mode 

Best Regards
NoRulez

[numbat]

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QStringList>
#include <QSqlQuery>
#include <QTreeView>
#include <QFile>
#include <QSqlError>
 
int ExecuteSqlScriptFile(QSqlDatabase & db, const QString & fileName)
{
    QFile file(fileName);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
        return  0;
 
    QTextStream in(&file);
    QString sql = in.readAll();
    QStringList sqlStatements = sql.split(';', QString::SkipEmptyParts);
    int successCount = 0;
 
    foreach(const QString& statement, sqlStatements)
    {
        if (statement.trimmed() != "")
        {
            QSqlQuery query(db);
            if (query.exec(statement))
                successCount++;
            else
                qDebug() << "Failed:" << statement << "\nReason:" << query.lastError();
        }
    }
    return successCount;
}
 
 
int main(int argc, char * argv[])
{
    QApplication a(argc, argv);
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    db.open();
 
    ExecuteSqlScriptFile(db, "sql.sql");
 
    QSqlTableModel  tbl(0, db);
    tbl.setTable("table2");
    tbl.select();
 
    QTreeView tv;
    tv.setModel(&tbl);
    tv.show();
 
    return a.exec();
}

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QStringList>
#include <QSqlQuery>
#include <QTreeView>
#include <QFile>
#include <QSqlError>

int ExecuteSqlScriptFile(QSqlDatabase & db, const QString & fileName)
{
    QFile file(fileName);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
        return  0;

    QTextStream in(&file);
    QString sql = in.readAll();
    QStringList sqlStatements = sql.split(';', QString::SkipEmptyParts);
    int successCount = 0;

    foreach(const QString& statement, sqlStatements)
    {
        if (statement.trimmed() != "")
        {
            QSqlQuery query(db);
            if (query.exec(statement))
                successCount++;
            else
                qDebug() << "Failed:" << statement << "\nReason:" << query.lastError();
        }
    }
    return successCount;
}


int main(int argc, char * argv[])
{
    QApplication a(argc, argv);

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

    ExecuteSqlScriptFile(db, "sql.sql");

    QSqlTableModel  tbl(0, db);
    tbl.setTable("table2");
    tbl.select();

    QTreeView tv;
    tv.setModel(&tbl);
    tv.show();

    return a.exec();
}

To copy to clipboard, switch view to plain text mode 

Tested with this script:

CREATE TABLE `table1` (
    `id` INTEGER,
    `bla1` TEXT,
    PRIMARY KEY(`id`)
);
CREATE TABLE `table2` (
    `id` INTEGER,
    `bla2` TEXT,
    PRIMARY KEY(`id`)
);
INSERT INTO `table2` (bla2) VALUES ('Hello World!');

CREATE TABLE `table1` (
    `id` INTEGER,
    `bla1` TEXT,
    PRIMARY KEY(`id`)
);
CREATE TABLE `table2` (
    `id` INTEGER,
    `bla2` TEXT,
    PRIMARY KEY(`id`)
);
INSERT INTO `table2` (bla2) VALUES ('Hello World!');

To copy to clipboard, switch view to plain text mode 

Note: If your script is many megabytes, you may want to load it incrementally.

[numbat]

Note: The above won't work if there are embedded ; in sql strings. Be careful.

[numbat]

OK, take two. This handles semicolons in single quoted strings. It still doesn't handle (rarer) double quoted strings or comments.

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QStringList>
#include <QSqlQuery>
#include <QTreeView>
#include <QFile>
#include <QSqlError>
 
 
int ParseSqlScriptFile(QSqlDatabase & db, const QString & fileName)
{
    QFile file(fileName);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
        return  0;
 
    QTextStream in(&file);
    QString sql = in.readAll();
    if (sql.length() == 0)
        return 0;
 
    QList<int> splitPoints;
    enum { IN_STR, IN_ESC, NORMAL } state = NORMAL;
    int successCount = 0;
 
    for (int i = 0; i < sql.length(); i++)
    {
        const int character = sql.at(i).unicode();
        switch (state)
        {
        case IN_STR:
            switch (character)
            {
            case '\'':
                state = NORMAL;
                break;
            case '\\':
                state = IN_ESC;
                break;
            }
            break;
 
        case IN_ESC:
            state = IN_STR;
            break;
 
        case NORMAL:
            switch (character)
            {
            case ';':
                splitPoints.push_back(i);
                break;
 
            case '\'':
                state = IN_STR;
                break;
            }
        }
    }
 
    splitPoints.push_back(sql.length() - 1);
 
    for (int i = 0, j = 0; i < splitPoints.length(); i++)
    {
        QString statement = sql.mid(j, splitPoints.at(i) - j + 1);
        j = splitPoints.at(i) + 1;
 
        if (statement.trimmed().length() > 0)
        {
            QSqlQuery query(db);
            if (query.exec(statement))
                successCount++;
            else
                qDebug() << "Failed:" << statement << "\nReason:" << query.lastError();
        }
    }
 
    return successCount;
}
 
int main(int argc, char * argv[])
{
    QApplication a(argc, argv);
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    db.open();
 
    int queryCount = ParseSqlScriptFile(db, "sql.sql");
    qDebug() << "Successful queries:" << queryCount;
 
    QSqlTableModel  tbl(0, db);
    tbl.setTable("table2");
    tbl.select();
 
    QTreeView tv;
    tv.setModel(&tbl);
    tv.show();
 
    return a.exec();
}

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QSqlTableModel>
#include <QStringList>
#include <QSqlQuery>
#include <QTreeView>
#include <QFile>
#include <QSqlError>


int ParseSqlScriptFile(QSqlDatabase & db, const QString & fileName)
{
    QFile file(fileName);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text))
        return  0;

    QTextStream in(&file);
    QString sql = in.readAll();
    if (sql.length() == 0)
        return 0;

    QList<int> splitPoints;
    enum { IN_STR, IN_ESC, NORMAL } state = NORMAL;
    int successCount = 0;

    for (int i = 0; i < sql.length(); i++)
    {
        const int character = sql.at(i).unicode();
        switch (state)
        {
        case IN_STR:
            switch (character)
            {
            case '\'':
                state = NORMAL;
                break;
            case '\\':
                state = IN_ESC;
                break;
            }
            break;

        case IN_ESC:
            state = IN_STR;
            break;

        case NORMAL:
            switch (character)
            {
            case ';':
                splitPoints.push_back(i);
                break;

            case '\'':
                state = IN_STR;
                break;
            }
        }
    }

    splitPoints.push_back(sql.length() - 1);

    for (int i = 0, j = 0; i < splitPoints.length(); i++)
    {
        QString statement = sql.mid(j, splitPoints.at(i) - j + 1);
        j = splitPoints.at(i) + 1;

        if (statement.trimmed().length() > 0)
        {
            QSqlQuery query(db);
            if (query.exec(statement))
                successCount++;
            else
                qDebug() << "Failed:" << statement << "\nReason:" << query.lastError();
        }
    }

    return successCount;
}

int main(int argc, char * argv[])
{
    QApplication a(argc, argv);

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

    int queryCount = ParseSqlScriptFile(db, "sql.sql");
    qDebug() << "Successful queries:" << queryCount;

    QSqlTableModel  tbl(0, db);
    tbl.setTable("table2");
    tbl.select();

    QTreeView tv;
    tv.setModel(&tbl);
    tv.show();

    return a.exec();
}

To copy to clipboard, switch view to plain text mode