void MQLEdit::execQuery()
{
if(!_loggedIn) {
QMessageBox::warning(this, tr("Not Connected"),
tr("You must be connected to a database in order to execute a query."));
return;
}
_results->_table->setRowCount(0);
_results->_table->setColumnCount(0);
_sql->_log->clear();
_log->_log->clear();
_log->_log->append(tr("---- Parsing Query ----\n"));
MetaSQLQuery mql(_text->toPlainText());
_log->_log->append(mql.parseLog());
if(mql.isValid()) {
_log->_log->append(tr("Query parsed."));
_log->_log->append(tr("---- Executing Query ----"));
ParameterList plist = _pEdit->getParameterList();
XSqlQuery qry = mql.toQuery(plist);
_sql->_log->append(qry.executedQuery());
if(qry.isActive()) {
QSqlRecord rec = qry.record();
int ncols = rec.count();
_results->_table->setColumnCount(ncols);
int c;
for(c = 0; c < ncols; c++) {
_results->_table->setHorizontalHeaderItem(c, new QTableWidgetItem(rec.fieldName(c)));
}
int nrows = 0;
while(qry.next()) {
_results->_table->setRowCount(nrows + 1);
for(c = 0; c < ncols; c++) {
_results->_table->setItem(nrows, c, new QTableWidgetItem(qry.value(c).toString()));
}
nrows++;
}
showResults();
} else {
_log->_log->append(tr("Failed to execute query."));
QSqlError err = qry.lastError();
_log->_log->append(err.text());
}
} else {
_log->_log->append(tr("ERROR: Invalid query!"));
showLog();
}
}
int LoadMetasql::writeToDB(const QByteArray &pdata, const QString pkgname, QString &errMsg)
{
if (pdata.isEmpty())
{
errMsg = TR("<font color=orange>The MetaSQL statement %1 is empty.</font>")
.arg(_name);
return -2;
}
QString metasqlStr = QString(pdata);
QStringList lines = metasqlStr.split("\n");
QRegExp groupRE = QRegExp("(^\\s*--\\s*GROUP:\\s*)(.*)",Qt::CaseInsensitive);
QRegExp nameRE = QRegExp("(^\\s*--\\s*NAME:\\s*)(.*)", Qt::CaseInsensitive);
QRegExp notesRE = QRegExp("(^\\s*--\\s*NOTES:\\s*)(.*)",Qt::CaseInsensitive);
QRegExp dashdashRE = QRegExp("(^\\s*--\\s*)(.*)");
for (int i = 0; i < lines.size(); i++)
{
if (DEBUG)
qDebug("LoadMetasql::writeToDB looking at %s", qPrintable(lines.at(i)));
if (groupRE.indexIn(lines.at(i)) >= 0)
{
_group = groupRE.cap(2);
if (DEBUG)
qDebug("LoadMetasql::writeToDB() found group %s", qPrintable(_group));
}
else if (nameRE.indexIn(lines.at(i)) >= 0)
{
_name = nameRE.cap(2);
if (DEBUG)
qDebug("LoadMetasql::writeToDB() found name %s", qPrintable(_name));
}
else if (notesRE.indexIn(lines.at(i)) >= 0)
{
_comment = notesRE.cap(2);
while (dashdashRE.indexIn(lines.at(++i)) >= 0)
_comment += " " + dashdashRE.cap(2);
if (DEBUG)
qDebug("LoadMetasql::writeToDB() found notes %s", qPrintable(_comment));
}
}
if (DEBUG)
qDebug("LoadMetasql::writeToDB(): name %s group %s notes %s\n%s",
qPrintable(_name), qPrintable(_group), qPrintable(_comment),
qPrintable(metasqlStr));
QString destschema = "public";
if (_schema.isEmpty() && pkgname.isEmpty())
; // leave it alone
else if (_schema.isEmpty() && ! pkgname.isEmpty())
destschema = pkgname;
else if ("public" == _schema)
; // leave it alone
else if (! _schema.isEmpty())
destschema = _schema;
XSqlQuery upsert;
int metasqlid = -1;
upsert.prepare("SELECT saveMetasql(:group, :name, :notes, :query, "
" :system, :schema) AS result;");
upsert.bindValue(":group", _group);
upsert.bindValue(":name", _name);
upsert.bindValue(":notes", _comment);
upsert.bindValue(":query", metasqlStr);
upsert.bindValue(":system",_system);
upsert.bindValue(":schema",destschema);
upsert.exec();
if (upsert.first())
{
metasqlid = upsert.value(0).toInt();
if (metasqlid < 0)
{
errMsg = TR("The %1 stored procedure failed, returning %2.")
.arg("saveMetasql").arg(metasqlid);
return -5;
}
}
else if (upsert.lastError().type() != QSqlError::NoError)
{
QSqlError err = upsert.lastError();
errMsg = _sqlerrtxt.arg(_filename).arg(err.driverText()).arg(err.databaseText());
return -6;
}
else
{
errMsg = TR("Saving the MetaSQL statement returned 0 rows. This should "
"not be possible.");
return -6;
}
if (DEBUG)
qDebug("LoadMetasql::writeToDB() executed %s and got %d in return",
qPrintable(upsert.executedQuery()), metasqlid);
return metasqlid;
}
int LoadMetasql::writeToDB(const QByteArray &pdata, const QString pkgname, QString &errMsg)
{
if (pdata.isEmpty())
{
errMsg = TR("<font color=orange>The MetaSQL statement %1 is empty.</font>")
.arg(_name);
return -2;
}
QString metasqlStr = QString(pdata);
QStringList lines = metasqlStr.split("\n");
QRegExp groupRE = QRegExp("(^\\s*--\\s*GROUP:\\s*)(.*)",Qt::CaseInsensitive);
QRegExp nameRE = QRegExp("(^\\s*--\\s*NAME:\\s*)(.*)", Qt::CaseInsensitive);
QRegExp notesRE = QRegExp("(^\\s*--\\s*NOTES:\\s*)(.*)",Qt::CaseInsensitive);
QRegExp dashdashRE = QRegExp("(^\\s*--\\s*)(.*)");
for (int i = 0; i < lines.size(); i++)
{
if (DEBUG)
qDebug("LoadMetasql::writeToDB looking at %s", qPrintable(lines.at(i)));
if (groupRE.indexIn(lines.at(i)) >= 0)
{
_group = groupRE.cap(2).trimmed();
if (DEBUG)
qDebug("LoadMetasql::writeToDB() found group %s", qPrintable(_group));
}
else if (nameRE.indexIn(lines.at(i)) >= 0)
{
_name = nameRE.cap(2).trimmed();
if (DEBUG)
qDebug("LoadMetasql::writeToDB() found name %s", qPrintable(_name));
}
else if (notesRE.indexIn(lines.at(i)) >= 0)
{
_comment = notesRE.cap(2).trimmed();
while (dashdashRE.indexIn(lines.at(++i)) >= 0)
_comment += " " + dashdashRE.cap(2).trimmed();
if (DEBUG)
qDebug("LoadMetasql::writeToDB() found notes %s", qPrintable(_comment));
}
}
if (DEBUG)
qDebug("LoadMetasql::writeToDB(): name %s group %s notes %s\n%s",
qPrintable(_name), qPrintable(_group), qPrintable(_comment),
qPrintable(metasqlStr));
QString destschema = "public";
if (_schema.isEmpty() && pkgname.isEmpty())
; // leave it alone
else if (_schema.isEmpty() && ! pkgname.isEmpty())
destschema = pkgname;
else if ("public" == _schema)
; // leave it alone
else if (! _schema.isEmpty())
destschema = _schema;
XSqlQuery gradedsavepoint("SAVEPOINT savemetasql_graded;");
MetaSQLQuery upsertm("SELECT saveMetasql(<? value('group') ?>,"
" <? value('name') ?>, <? value('notes') ?>,"
" E<? value('query') ?>,"
" CAST(<? value('system') ?> AS BOOLEAN),"
" <? value('schema') ?>"
"<? if not exists('skipgrade') ?>"
" , <? value('grade') ?>"
"<? endif ?>"
") AS result;");
ParameterList upsertp;
upsertp.append("group", _group);
upsertp.append("name", _name);
upsertp.append("notes", _comment);
upsertp.append("query", metasqlStr);
upsertp.append("system",_system);
upsertp.append("schema",destschema);
upsertp.append("grade", _grade);
int metasqlid = -1;
XSqlQuery upsert = upsertm.toQuery(upsertp);
if (upsert.first())
metasqlid = upsert.value(0).toInt();
else if (upsert.lastError().type() != QSqlError::NoError)
{
XSqlQuery gradedrollback("ROLLBACK TO SAVEPOINT savemetasql_graded;");
upsertp.append("skipgrade");
upsert = upsertm.toQuery(upsertp);
if (upsert.first())
metasqlid = upsert.value(0).toInt();
if (upsert.lastError().type() != QSqlError::NoError)
{
QSqlError err = upsert.lastError();
errMsg = _sqlerrtxt.arg(_filename).arg(err.driverText()).arg(err.databaseText());
return -6;
}
}
else
{
errMsg = TR("Saving the MetaSQL statement returned 0 rows. This should "
"not be possible.");
return -6;
}
if (metasqlid < 0)
{
errMsg = TR("The %1 stored procedure failed, returning %2.")
.arg("saveMetasql").arg(metasqlid);
return -5;
}
else
XSqlQuery gradedrelease("RELEASE SAVEPOINT savemetasql_graded;");
if (DEBUG)
qDebug("LoadMetasql::writeToDB() executed %s and got %d in return",
qPrintable(upsert.executedQuery()), metasqlid);
return metasqlid;
}
