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