Stored procedures can also be used as a mechanism to return complex query results. Using a named query to invoke a stored procedure is very similar to a direct SQL named query, with one minor addition. The <sql> tag for a stored procedure uses an attribute named stored-procedure. When using a stored-procedure, this attribute must be set to true. Here is an example of how you might define a stored procedure in a repository definition file. Note that the values used in the <returns> and <dependencies> tags in this example are dependent upon what the stored procedure returns and defines constraints for.
<item-descriptor name=...>
...
<named-query>
<sql-query>
<query-name>myQuery</query-name>
<sql stored-procedure="true">
{ call myStoredProcedure (?, ?) }
</sql>
<returns>id,firstName,lastName</returns>
<input-parameter-types>java.lang.String,java.lang.String
</input-parameter-types>
<dependencies>login</dependencies>
</sql-query>
</named-query>
</item-descriptor>The syntax for calling a stored procedure is different from executing a SQL statement. The body of the <sql> tag for a stored procedure must use the syntax required by java.sql.CallableStatement. There are two formats typically used in a CallableStatement, one for procedures, which don’t have an explicit return value, and one for functions, which do. Unless you are using Oracle, stored procedures in the SQL repository must use the procedure form, as in the above example. The enclosing curly braces are required. The question marks indicate parameters. In stored procedures, parameters can be IN, OUT and INOUT, indicating that the values go in the database, come out, or both go in and come out. The SQL repository supports only IN parameters. Any OUT parameters or INOUT parameters will not function correctly, except in the case of Oracle stored procedures.
Any stored procedure used must return a java.sql.ResultSet. Most JDBC drivers will do so by default, but there are exceptions. Oracle, for example, requires some special tuning, as described in the next section.
