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; }