SQL Select Changes in Version 9 of Windward Reports

In all previous versions of Windward Reports Windward performed a simple string substitution of a ${var} into a select. Windward handled special characters like ' in the variable but otherwise did a straight substitution. This has worked very well. But in our effort to improve Windward we have made the following change for SQL selects (there is no change for XPath selects).

The ${var} in a select is replaced with a parameter and we then use setParameter() to set each parameter in the select. So the select "select * from orders where date > ${req_date}" becomes "select * from orders where date > ?" and there is then a call to setParameter(0, getMapVar("req_date"));

Windward uses the date example on purpose because this was the main reason for this change. Every database vendor uses a slightly different way of setting the date in the select string. Rather than having to know that syntax and enter it correctly, this way you just set the variable. But there is a very important thing to take into account in this change.

This new approach will break your templates.

At present your selects are of the form "select * from employees where name = '${name}'". That will convert to select * from employees where name = '?'" but what you need is select * from employees where name = ?". A '?' is the literal string ?, not a parameter to replace. We therefore have 3 modes that the SQL datasources (both Java and .NET) can be run.

1. sql.parameters=substitution – This is the way we have handled this to date and your templates will all work fine with this setting. This setting is not the default – you need to set it in your properties file.
2. sql.parameters=parameters – This will change your vars in the select to a parameter, unless the var starts with an _ like ${_var}. For ${_var} it will use substitution. This mixed mode is the default.
3. sql.parameters=parameters.always – This will change vars to parameters regardless of the name.

 

Windward will not set the default to a setting that breaks existing templates lightly. Windward has found that most new customers will continue to use the default setting and we think the mixed mode is better moving forward. This is the only way to bring about this change.

You can add this setting to your properties file now, before you upgrade to version 9. In version 8 the setting will be ignored as the version 8 engine has no idea what it means.