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();
}
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.