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