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