Re: JDBC and stored procedures

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Thu, 08 Apr 99 19:56:00 GMT
Message-ID: <7ej1bn$h8m$2@news.doit.wisc.edu>

In article <370B83BA.E993C5E9_at_odaxys.fr>, christophe maligorne <cmaligorne_at_odaxys.fr> wrote:
>
>I am trying to call a stored procedure via JDBC.
>
>My code is :
>
>|| CallableStatement cs = conn.prepareCall("{? = call
>essai.CHERCH_PROD(?)}");
>|| cs.setString(1, code);
>|| cs.executeQuery() ;
>
>in fact, my procedure is a fonction and it runs without problem.
>I am expecting to retrieve the exit value of the function with the
>method cs.getString(1).
>
>When, i'm runnig this application i have the error : ORA-01008: not all
>variables bound.

The problem is... you haven't bound all your variables (big surprise, eh?) The first "?" is bind variable 1, the second "?" is bind variable 2, etc. Output bind variables/parameters (i.e. variables whose values can be set by the stored procedure/function and accessible to the calling program) must be registered as such:

        CallableStatement cs = conn.prepareCall("{? := call essai.CHERCH_PROD(?)}");

        cs.registerOutParameter(1, TYPES.VARCHAR);      <- added
        cs.setString(2, code);                          <- changed
        cs.executeUpdate();                             <- changed

With the result now available using:

        String result = cs.getString(2);

Hope this helps.
Gerard

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Thu Apr 08 1999 - 14:56:00 CDT