private void SetQueryParameters(string sql, OracleQuery query, bool isSpecialWhereParameters)
{
// Check if sql contains any parameters
IList <string> parameters = GetParametersInText(sql, isSpecialWhereParameters);
if (parameters.Count > 0)
{
int sequence = 1;
foreach (string parameter in parameters)
{
// Insert the matched parameter
OracleQueryParameter qp = new OracleQueryParameter();
qp.Sequence = sequence;
qp.Direction = OracleQueryParameterDirection.In;
// Set the parameter name
qp.Name = parameter;
// Add property to query
query.Parameters.Add(qp);
sequence++;
}
}
}
private void GetSelectRows(CaptureCollection captures, OracleQuery query)
{
int selectRowCount = 1;
bool existing = false;
// Loop through the select rows to handle them
foreach (Capture capture in captures)
{
OracleQueryParameter qp = query.GetPropertyByTypeAndSequence(OracleQueryParameterDirection.Out, selectRowCount);
existing = qp != null;
if (!existing)
{
qp = new OracleQueryParameter();
}
if (!existing)
{
qp.Sequence = selectRowCount;
qp.Direction = OracleQueryParameterDirection.Out;
}
// Set the text to the full selectrow
qp.Text = capture.Value;
// Parse the selectrow
Match selRowMatch = RegexHelper.ParseGetFirstMatch(RegexConstants.SQL_SELECTROW, capture.Value);
if (selRowMatch != null)
{
// First check if TABLE.COLUMN was found
if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_TABLE].Value != string.Empty &&
selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value != string.Empty)
{
// Check if an alias is given, in that case use that. If not then
// use the column.
if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value != string.Empty)
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value, selectRowCount);
}
else
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value, selectRowCount);
}
// Check if we can find out the real name of the table. Either it is a table or an alias
// in the from-statement. If the real table can't be found then we can't set the table and column.
string foundTableName = GetTableName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_TABLE].Value);
if (!string.IsNullOrEmpty(foundTableName))
{
qp.OutColumnName = selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value;
qp.OutTableName = foundTableName;
}
}
// Check if selectrow starts with some kind of function
else if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_FUNCTION].Value != string.Empty &&
selRowMatch.Groups[RegexConstants.SQL_SELECTROW_FUNCTIONPARAMETERS].Value != string.Empty)
{
// Check if an alias is given, in that case use that. If not then
// we throw an error since we need unique names.
if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value != string.Empty)
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value, selectRowCount);
}
else
{
throw new Exception(string.Format("An alias is missing for the selectrow ({0}) containing:\n" +
"\t{1}\n" +
"\nAdd an unique alias and try again."
, selectRowCount.ToString()
, selRowMatch.Value));
}
}
// Unknown select row that we cannot parse more than getting the alias
else
{
// Check if we found a single column but no table and if we only have
// one table as a from table. In that case we know what table and column it is.
if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value != string.Empty &&
selRowMatch.Groups[RegexConstants.SQL_SELECTROW_TABLE].Value == string.Empty &&
fromTables.Count == 1)
{
// Check if an alias is given, in that case use that. If not then
// it has no name so set a NONAME then.
if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value != string.Empty)
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value, selectRowCount);
}
else if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value != string.Empty)
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value, selectRowCount);
}
else
{
throw new Exception(string.Format("An alias is missing for the selectrow ({0}) containing:\n" +
"\t{1}\n" +
"\nAdd an unique alias and try again."
, selectRowCount.ToString()
, selRowMatch.Value));
}
qp.OutColumnName = selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value;
qp.OutTableName = fromTables.Keys.ToList()[0];
}
// Check if an alias is given, in that case use that. If not then
// it has no name so set a NONAME then.
else if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value != string.Empty)
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_ALIAS].Value, selectRowCount);
}
else if (selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value != string.Empty)
{
qp.Name = CheckSelectUniqueName(selRowMatch.Groups[RegexConstants.SQL_SELECTROW_COLUMN].Value, selectRowCount);
}
else
{
throw new Exception(string.Format("An alias is missing for the selectrow ({0}) containing:\n" +
"\t{1}\n" +
"\nAdd an unique alias and try again."
, selectRowCount.ToString()
, capture.Value));
}
}
}
else
{
throw new Exception("Couldn't parse this SELECT row: \"" + selRowMatch.Value + "\". Please analyze!");
}
if (!existing)
{
// Add property to query
query.Parameters.Add(qp);
}
// Increase count
selectRowCount++;
}
}
private void GetWhereRows(CaptureCollection captures, OracleQuery query, bool isSpecialWhereParameters)
{
// Loop through the where rows to handle them
foreach (Capture capture in captures)
{
// Check if whererow contains any parameters
IList <string> parameters = GetParametersInText(capture.Value, isSpecialWhereParameters);
if (parameters.Count > 0)
{
// If there is only one parameter on a whererow then we try to find out
// what type it is.
if (parameters.Count == 1)
{
string regexString = isSpecialWhereParameters ? RegexConstants.SQL_WHEREROWSPECIAL : RegexConstants.SQL_WHEREROW;
Match parameterMatch = Regex.Match(capture.Value, regexString);
if (parameterMatch.Success &&
!string.IsNullOrEmpty(parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value))
{
// Get the added parameter
OracleQueryParameter qp = query.GetPropertyByTypeAndName(OracleQueryParameterDirection.In, parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value);
if (qp == null)
{
throw new Exception("Property with the name \"" + parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value + "\" doesn't exist.");
}
if (parameterMatch.Groups[RegexConstants.SQL_WHEREROW_TABLE].Value != string.Empty &&
parameterMatch.Groups[RegexConstants.SQL_WHEREROW_COLUMN].Value != string.Empty &&
parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value != string.Empty &&
string.IsNullOrEmpty(qp.OutTableName) &&
string.IsNullOrEmpty(qp.OutColumnName))
{
// Set the text to the full whererow
qp.Text = capture.Value;
// Check if we can find out the real name of the table. Either it is a table or an alias
// in the from-statement. If the real table can't be found then we can't set the table and column.
string foundTableName = GetTableName(parameterMatch.Groups[RegexConstants.SQL_WHEREROW_TABLE].Value);
if (!string.IsNullOrEmpty(foundTableName))
{
qp.OutColumnName = parameterMatch.Groups[RegexConstants.SQL_WHEREROW_COLUMN].Value;
qp.OutTableName = foundTableName;
}
}
// Special case if there is only a column without a given table but there is only one table
// in the from statement then we know what table and column it is anyway.
else if (parameterMatch.Groups[RegexConstants.SQL_WHEREROW_TABLE].Value == string.Empty &&
parameterMatch.Groups[RegexConstants.SQL_WHEREROW_COLUMN].Value != string.Empty &&
parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value != string.Empty &&
fromTables.Count == 1 &&
string.IsNullOrEmpty(qp.OutTableName) &&
string.IsNullOrEmpty(qp.OutColumnName))
{
// Set the text to the full whererow
qp.Text = capture.Value;
qp.OutColumnName = parameterMatch.Groups[RegexConstants.SQL_WHEREROW_COLUMN].Value;
qp.OutTableName = fromTables.Keys.ToList()[0];
}
else if (parameterMatch.Groups[RegexConstants.SQL_WHEREROW_FUNCTION].Value != string.Empty &&
parameterMatch.Groups[RegexConstants.SQL_WHEREROW_FUNCTIONPARAMETERS].Value != string.Empty &&
parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value != string.Empty)
{
// Set the text to the full whererow
qp.Text = capture.Value;
}
else
{
// Set the text to the full whererow
qp.Text = capture.Value;
}
}
// Can atleast set the text for the single parameter
else
{
// Get the added parameter
OracleQueryParameter qp = query.GetPropertyByTypeAndName(OracleQueryParameterDirection.In, parameters[0]);
if (qp == null)
{
throw new Exception("Property with the name \"" + parameters[0] + "\" doesn't exist.");
}
// Check if this is a row where a table.column is compared to a function where the parameter
// is somewhere within the functions parameters.
// Example: ITESTOTMP.WSID = nvl(:WSID_I,ITESTOTMP.WSID)
//if (parameterMatch.Groups[RegexConstants.SQL_WHEREROW_FUNCTION].Value != string.Empty &&
// parameterMatch.Groups[RegexConstants.SQL_WHEREROW_FUNCTIONPARAMETERS].Value != string.Empty &&
// parameterMatch.Groups[RegexConstants.SQL_WHEREROW_PARAMETER].Value != string.Empty)
// Set the text to the full whererow
qp.Text = capture.Value;
}
}
// More than one parameter on the same row.
// Check if information is found for every parameter already. In that case
// skip it. Otherwise atleast set the text for the parameter.
else if (parameters.Count > 1)
{
foreach (string parameter in parameters)
{
// Get the parameter
OracleQueryParameter qp = query.GetPropertyByTypeAndName(OracleQueryParameterDirection.In, parameter);
if (qp == null)
{
throw new Exception("Property with the name \"" + parameter + "\" doesn't exist.");
}
// Now check if information is already set.
// If not then set the text.
if (String.IsNullOrEmpty(qp.OutColumnName) &&
String.IsNullOrEmpty(qp.OutTableName) &&
String.IsNullOrEmpty(qp.Text))
{
qp.Text = capture.Value;
}
}
}
}
}
}
private void FindDataTypes(OracleQuery query, string oracleConnectionString, bool isSpecialWhereParameters)
{
if (!String.IsNullOrEmpty(oracleConnectionString))
{
// If special where parameters then there could be parameters on the format :XXXX.YYYY
// which Oracle doesn't like. So we fix these before doing the call to get
// the datatypeinfo.
string sql = TrimComments(query.SQL);
if (isSpecialWhereParameters)
{
sql = Regex.Replace(sql, @":(([A-Za-z]\w*)\.?){1,2}(?!\.)", ":param");
}
OracleDataTypeInfoList odti = OracleSchemaInfo.GetDataTypeInfo(oracleConnectionString, sql);
if (odti != null)
{
for (int i = 0; i < odti.Count; i++)
{
OracleQueryParameter qp = query.GetPropertyByTypeAndSequence(OracleQueryParameterDirection.Out, i + 1);
if (qp != null)
{
qp.DbDatatype = odti[i].DataType;
if (qp.DbDatatype == "NUMBER")
{
qp.Precision = odti[i].Precision;
qp.Scale = odti[i].Scale;
if (qp.Scale.HasValue)
{
if (qp.Precision.HasValue)
{
// Scale cannot have a higher value than the precision.
if (qp.Scale > qp.Precision)
{
qp.Scale = qp.Precision;
}
// And scale (decimals) should maximum contain 9 numbers as highest
if (qp.Scale > 9)
{
qp.Scale = 9;
}
}
else
{
qp.Scale = null;
}
}
}
else
{
qp.Precision = null;
qp.Scale = null;
}
if (qp.DbDatatype == "VARCHAR2" ||
qp.DbDatatype == "CHAR")
{
qp.Length = odti[i].ColumnSize;
}
else
{
qp.Length = null;
}
}
}
}
}
}