Description
Currently dynamic sql is managed in two phases, one for scripting and one for sql parameterization. This leads to two different languages in a dynamic sql at the same time and is a common source of mistakes.
For example, using velocity scripting we can do something like:
SELECT * from mytable Where mycolumn = '$_parameter.mymethod($_parameter.value)'
but we can't do:
SELECT * from mytable Where mycolumn = @{$_parameter.mymethod($_parameter.value)}
Because SQL parameter binding language is not velocity, but a propietary binding language.
The problem goes worst in loops, because the scope of the scripting variables and sql parameter are not the same. So <bind /> will not work in loops as expected.
My proposal is to extract any sql parameter in the scripting phase and cache it in the BoundSql object as proposed some time ago by Eduardo, then the sql parameterization will use the already extracted values directly by index, so we can use arbitrary scripting expressions.
For example:
SELECT * from mytable Where mycolumn = $sql.bind($_parameter.mymethod($_parameter.value))
the hypothetical $sql.bind method should support more parameters like type (IN,OUT,INOUT), jsdbType, etc...
Also in the case of velocity, the method can be replaced by a more friendly syntax (macro):
#set ($p = $_parameter)
SELECT * from mytable Where mycolumn = #param( $p.mymethod( $p.value ) )