Call a stored procedure from a JSP with JSTL

Keywords:
Call execute SQL stored procedure JSP JSTL tag library

Problem:
The JSTL SQL tag library is a useful way of getting a rapid prototype going - it's all in a plain text file and will get compile on the fly. Examples I've seen show SELECT, UPDATE, INSERT and DELETE(s) ... can a stored procedure be run?

Solution:
The short answer is yes, the key thing is to know if the stored procedure is returning a result set or not as you have two tags available:

• <sql:query> expects a ResultSet


• <sql:update> does not expect a ResultSet - will throw an error if gets one. You do have access to an Integer result - eg rows updated.

For example, calling a stored procedure that returns a result set:

<sql:setDataSource var="myDataSource" dataSource="jdbc/myjndiref"/>
<sql:query var="examples" dataSource="${myDataSource}">
    exec ExampleProcLoadExample ?
    <sql:param value="${exampleId}"/>
</sql:query>
<c:choose>
    <c:when test="${fn:length(examples.rows) == 0}">
        <%-- no rows returned ! --%>
    </c:when>
    <c:otherwise>
        <c:set var="example" value="${examples.rows[0]}"/>
        <%-- got your object, can access columns with '.' notation --%>
    </c:otherwise>
</c:choose>

For example, calling a stored procedure that performs an 'update' (or insert/delete) returning number of rows updated:

<sql:setDataSource var="myDataSource" dataSource="jdbc/myjndiref"/>
<sql:update var="updateCount" dataSource="${myDataSource}">
    exec ExampleProcRemoveExample ?
    <sql:param value="${exampleId}"/>
</sql:update>
<c:choose>
    <c:when test="${updateCount le 0}">
        <%-- no rows updated ! --%>
    </c:when>
    <c:otherwise>
        <%-- some row(s) has been updated --%>
        <c:out value="${updateCount} row(s) have been updated"/>
    </c:otherwise>
</c:choose>

Notes:
When calling stored procedures you're getting into RDBMS vendor specific territory. Using the recommended JDBC drivers from the DB vendor for the DB version in use may make some of this work more smoothly.