I was developing a java application and all our business logics were in sprocs. Data access and manipulation were done by sprocs and functions. We were using Microsoft JDBC driver and everything was fine until we realized the clunkiness of passing parameters by position restriction.
In java to call a sproc, you need to use CallableStatement stmt = connection.prepareCall("{call sp_name(?,?,..)}")
There is no way to pass the name of the parameter to the sproc. I borrowed a technique from aspnet's DeriveParameter implementation to fix this issue. DeriveParameters calls sp_sproc_columns and then dynamically build the parameters with proper data type and data size
1. Define a SqlParam object with Name, Value, Type and IsOutput properties.
2. Create a method to get the sproc's parameters by calling sp_sproc_columns system sproc. Get the resultset, loop through the resultset and then build an arraylist of SqlParam object. (you can persist this in an application scoped object to improve performance)
3. In the calling function, you build the SqlParam arraylist with the same parameter name as the proc and the parameter values.
4. Loop through the derived param list and build the callable statement like {call sproc (?,?..)}.
Loop through the derived param list then match the parameter name to the calling param list and then build the actual parameter collection. This ensure that the order in which the parameters are added to the parameter collection matches the sproc parameter position.
5.Loop through the derived parameters and build the output parameters' value into your calling param list (so that you can access this from the calling method)
6. Build parameter name and value to debug/error log in the catch section
To Call a Function
1. You need to exclude "@RETURN_VALUE" and "@TABLE_RETURN_VALUE" in the buildParameter function
2. If the function is a table valued function, then use "select * from
3. If the function is a scalar valued function, the use "select
download code
That's it!
Paul
