private static SqlStatement NormalizeParameters(SqlStatement statement, List <ParameterAccessor> accessors)
{
// remember accessor indexes
new QueryVisitor().VisitAll(statement, e =>
{
if (e.ElementType == QueryElementType.SqlParameter)
{
var parameter = (SqlParameter)e;
if (parameter.IsQueryParameter)
{
var idx = accessors.FindIndex(a => object.ReferenceEquals(a.SqlParameter, parameter));
parameter.AccessorId = idx >= 0 ? (int?)idx : null;
}
}
});
// correct expressions, we have to put expressions in correct order and duplicate them if they are reused
statement = NormalizeExpressions(statement);
var found = new HashSet <ISqlExpression>();
var columnExpressions = new HashSet <ISqlExpression>();
var parameterDuplicateVisitor = new QueryVisitor();
statement = parameterDuplicateVisitor.Convert(statement, e =>
{
if (e.ElementType == QueryElementType.SqlParameter)
{
var parameter = (SqlParameter)e;
if (parameter.IsQueryParameter)
{
var parentElement = parameterDuplicateVisitor.ParentElement;
if (parentElement is SqlColumn)
{
columnExpressions.Add(parameter);
}
else if (parentElement !.ElementType == QueryElementType.SetExpression)
{
// consider that expression is already processed by SelectQuery and we do not need duplication.
// It is specific how InsertStatement is built
if (columnExpressions.Contains(parameter))
{
return(parameter);
}
}
if (!found.Add(parameter))
{
var newParameter =
(SqlParameter)parameter.Clone(new Dictionary <ICloneableElement, ICloneableElement>(),
c => true);
return(newParameter);
}
// notify visitor to process this parameter always
parameterDuplicateVisitor.VisitedElements.Add(parameter, null);
}
private static T NormalizeExpressions <T>(T expression)
where T : class, IQueryElement
{
var queryVisitor = new QueryVisitor();
var result = queryVisitor.Convert(expression, e =>
{
if (e.ElementType == QueryElementType.SqlExpression)
{
var expr = (SqlExpression)e;
// we interested in modifying only expressions which have parameters
if (HasQueryParameters(expr))
{
if (expr.Expr.IsNullOrEmpty() || expr.Parameters.Length == 0)
{
return(expr);
}
var newExpressions = new List <ISqlExpression>();
var newExpr = QueryHelper.TransformExpressionIndexes(expr.Expr,
idx =>
{
if (idx >= 0 && idx < expr.Parameters.Length)
{
var paramExpr = expr.Parameters[idx];
var normalized = paramExpr;
var newIndex = newExpressions.Count;
if (newExpressions.Contains(normalized) && HasQueryParameters(normalized))
{
normalized = (ISqlExpression)normalized.Clone(
new Dictionary <ICloneableElement, ICloneableElement>(),
c => true);
}
newExpressions.Add(normalized);
return(newIndex);
}
return(idx);
});
// always create copy
var newExpression = new SqlExpression(expr.SystemType, newExpr, expr.Precedence, expr.IsAggregate, newExpressions.ToArray());
// force re-entrance
queryVisitor.VisitedElements.Remove(expr);
queryVisitor.VisitedElements.Add(expr, null);
return(newExpression);
}
}
return(e);
});
return(result);
}
private SqlStatement WrapParameters(SqlStatement statement)
{
// ODBC cannot properly type result column, if it produced by parameter
// To fix it we will wrap all parameters, used as select columns into type-cast
// we use CVar, as other cast functions generate error for null-values parameters and CVar works
// we are interested only in selects, because error generated by data reader, not by database itself
if (statement.QueryType != QueryType.Select)
{
return(statement);
}
var visitor = new QueryVisitor();
statement = visitor.Convert(statement, e =>
{
if (e is SqlParameter p && p.IsQueryParameter && visitor.ParentElement is SqlColumn)
{
return(new SqlExpression(p.Type.SystemType, "CVar({0})", Precedence.Primary, p));
}
return(e);
});
private SqlStatement WrapParameters(SqlStatement statement)
{
// for some reason Firebird doesn't use parameter type information (not supported?) is some places, so
// we need to wrap parameter into CAST() to add type information explicitly
// As it is not clear when type CAST needed, below we should document observations on current behavior.
//
// When CAST is not needed:
// - parameter already in CAST from original query
// - parameter used as direct inserted/updated value in insert/update queries (including merge)
//
// When CAST is needed:
// - in select column expression at any position (except nested subquery): select, subquery, merge source
// - in composite expression in insert or update setter: insert, update, merge (not always, in some cases it works)
var visitor = new QueryVisitor();
statement = visitor.Convert(statement, e =>
{
if (e is SqlParameter p && p.IsQueryParameter)
{
// Don't cast in cast
if (visitor.ParentElement is SqlExpression expr && expr.Expr == CASTEXPR)
{
return(e);
}
if (p.Type.SystemType == typeof(bool) && visitor.ParentElement is SqlFunction func && func.Name == "CASE")
{
return(e);
}
var replace = false;
for (var i = visitor.Stack.Count - 1; i >= 0; i--)
{
// went outside of subquery, mission abort
if (visitor.Stack[i] is SelectQuery)
{
return(e);
}
// part of select column
if (visitor.Stack[i] is SqlColumn)
{
replace = true;
break;
}
// insert or update keys used in merge source select query
if (visitor.Stack[i] is SqlSetExpression set &&
i == 2 &&
visitor.Stack[i - 1] is SqlInsertClause &&
visitor.Stack[i - 2] is SqlInsertOrUpdateStatement insertOrUpdate &&
insertOrUpdate.Update.Keys.Any(k => k.Expression == set.Expression))
{
replace = true;
break;
}
// enumerable merge source
if (visitor.Stack[i] is SqlValuesTable)
{
replace = true;
break;
}
// complex insert/update statement, including merge
if (visitor.Stack[i] is SqlSetExpression &&
i >= 2 &&
i < visitor.Stack.Count - 1 && // not just parameter setter
(visitor.Stack[i - 1] is SqlUpdateClause ||
visitor.Stack[i - 1] is SqlInsertClause ||
visitor.Stack[i - 1] is SqlMergeOperationClause))
{
replace = true;
break;
}
}
if (!replace)
{
return(e);
}
return(new SqlExpression(p.Type.SystemType, CASTEXPR, Precedence.Primary, p, new SqlDataType(p.Type)));
}
return(e);
});