public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
var script = ScriptCode.Parse(query);
var username = "";
var password = "";
var workdir = "";
var hostName = "";
this.MParameters.TryGetValue("userid", out username);
this.MParameters.TryGetValue("password", out password);
this.MParameters.TryGetValue("workdir", out workdir);
this.MParameters.TryGetValue("host", out hostName);
username = (username ?? "").Trim();
password = (password ?? "").Trim();
workdir = (workdir ?? "").Trim();
hostName = (hostName ?? "").Trim();
var qvxTable = GetData(script, username, password, workdir, hostName);
var result = new QvxDataTable(qvxTable);
result.Select(qvxTable.Fields);
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
AppInstance.LoadMemory();
logger.Debug($"Parse query {query}");
var script = ScriptCode.Parse(query);
if (script == null)
{
throw new Exception("The sql script is not valid.");
}
var parameter = ConnectorParameter.Create(MParameters);
var qvxTable = GetData(script, parameter);
var result = new QvxDataTable(qvxTable);
result.Select(qvxTable.Fields);
AppInstance.SaveMemory();
AppInstance.Dispose();
logger.Debug($"Send result table {qvxTable.TableName}");
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
LogManager.Flush();
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
var script = ScriptCode.Parse(query);
var username = "";
var password = "";
var workdir = "";
this.MParameters.TryGetValue("userid", out username);
this.MParameters.TryGetValue("password", out password);
this.MParameters.TryGetValue("workdir", out workdir);
username = (username ?? "").Trim();
password = (password ?? "").Trim();
workdir = workdir ?? Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
internalData = GetData(script, username, password, workdir);
var fieldsl = new List <QvxField>();
foreach (DataColumn column in internalData.Columns)
{
fieldsl.Add(new QvxField(column.ColumnName, QvxFieldType.QVX_TEXT, QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII));
}
fields = fieldsl.ToArray();
var table = new QvxTable()
{
TableName = script.TableName,
Fields = fields,
GetRows = GetPowerShellResult
};
var result = new QvxDataTable(table);
result.Select(fields);
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "+ ExtractQuery()");
Dictionary <string, string> myArgs = this.args.Select(kv => kv).ToDictionary(kv => kv.Key, kv => kv.Value);
QvxDataTable retVal = null;
try {
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : " + query + " " + ((tables != null) ? String.Join("|", tables.Select(t => t.TableName)) : ""));
IQlikConnector c = this.parent.Registered(myArgs["qDriver"]);
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : driver found!");
string pattern =
"^" +
"SELECT" +
"\\s*(?<select>" +
"((\\[(?<field>[^\\[\\]]+)\\]|(?<field>[^\\[\\],\\s]+))(\\s*,\\s*))*" +
"((\\[(?<field>[^\\[\\]]+)\\]|(?<field>[^\\[\\],\\s]+)))*" +
")\\s*" +
"FROM" +
"\\s*(?<from>" +
"(\\[(?<db>[^\\[\\]]*?)\\]|(?<db>[^\\[\\]\\.\\s]*?))" +
"\\s*\\.\\s*" +
"(\\[(?<table>[^\\[\\]]*?)\\]|(?<table>[^\\[\\]\\.\\s]*?))" +
")\\s*" +
"(WHERE" +
"\\s*(?<where>" +
".*" +
")\\s*" +
")?" +
"$"
;
Match m = Regex.Match(query.Trim(), pattern, RegexOptions.IgnoreCase | RegexOptions.Singleline
);
if (m.Success)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : query matched!");
string select = m.Groups["select"].Value.Trim();
string from = m.Groups["from"].Value.Trim();
string where = (m.Groups["where"].Success) ? m.Groups["where"].Value.Trim() : null;
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}, {1}, {2}!", select, from, where));
if (m.Groups["where"].Success)
{
bool quoted = false, escaped = false, name = true;
Dictionary <string, string> currParam = new Dictionary <string, string>();
string currParamName = "";
string currParamValue = "";
#region //where clause parsing
int i = 0;
where.ToList().ForEach(chr =>
{
bool addchar = false;
if (!escaped && !quoted && chr == '\\')
{
throw new ArgumentOutOfRangeException(String.Format("unescaped \\ @{0} in where clause", i));
}
if (!escaped && quoted && chr == '\\')
{
escaped = true;
}
else if (!escaped && !quoted && chr == '=')
{
name = false;
escaped = false;
}
else if (!escaped && chr == '"')
{
if (quoted)
{
if (!name)
{
currParam.Add(currParamName, currParamValue);
currParamName = "";
currParamValue = "";
name = true;
}
else
{
throw new ArgumentOutOfRangeException(String.Format("quote in param name @{0} in where clause", i));
}
}
quoted = !quoted;
escaped = false;
}
else
{
addchar = true;
escaped = false;
}
if (addchar)
{
if (name)
{
currParamName += chr;
}
else
{
currParamValue += chr;
}
}
i++;
});
#endregion
currParam.ToList().ForEach(kv =>
{
Match mWhere = Regex.Match(kv.Key, "^(\\s*AND)?\\s*((?<param>[^\\s]+)|\\[(?<param>[^\\]]+)\\])\\s*$");
if (mWhere.Success)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}, {1}!", mWhere.Groups["param"].Value, kv.Value));
myArgs[mWhere.Groups["param"].Value.Trim().Replace("[", "").Replace("]", "")] = kv.Value.Trim();
}
else
{
throw new ArgumentOutOfRangeException();
}
});
}
List <string> fields = m.Groups["field"].Captures.Cast <Capture>().Select(cap => cap.Value).ToList();
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}!", String.Join("|", fields)));
string fromDb = m.Groups["db"].Value.Trim();
string fromTable = m.Groups["table"].Value.Trim();
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}, {1}!", fromDb, fromTable));
Database db = c.getDatabases(myArgs).Where(dbItem => dbItem.qName == fromDb).FirstOrDefault();
if (db != null)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : found db!");
QvxTable table = c.getTables(db, myArgs).Where(tableItem => tableItem.TableName == fromTable).FirstOrDefault();
if (table != null)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "ExtractQuery() : found table!");
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, String.Format("ExtractQuery() : {0}", table.GetRows().Count()));
QvxDataTable t = new QvxDataTable(table);
t.Select(table.Fields.Where(fld => fields.Contains(fld.FieldName)).ToArray());
return(t);
}
}
}
}
catch (Exception e)
{
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Error, "ExtractQuery() : " + e.Message);
throw e;
}
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Debug, "- ExtractQuery()");
return(retVal);
}