PDA

View Full Version : How can i execute a database script within Qt?



NoRulez
12th January 2010, 08:58
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,
);

Best Regards
NoRulez

numbat
12th January 2010, 09:51
#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();
}

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!');

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

numbat
13th January 2010, 07:42
Note: The above won't work if there are embedded ; in sql strings. Be careful.

numbat
14th January 2010, 09:37
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();
}