Re: Invalid character???

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 20 Aug 2008 22:48:45 -0700 (PDT)
Message-ID: <f1a68271-cf77-42d5-b535-3ac46ba79bd8@34g2000hsh.googlegroups.com>

On Aug 20, 9:51 pm, Tim Slattery <Slatter..._at_bls.gov> wrote:
> Tim Slattery <Slatter..._at_bls.gov> wrote:
> >Laurenz Albe <inv..._at_spam.to.invalid> wrote:
>
> >>They have two samples:
>
> >>// SQL-92 syntax
> >>conn.prepareCall("{? = call func (?,?)}");
>
> >>// PL/SQL anonymous block
> >>conn.prepareCall("begin ? := func(?,?); end;");
>
> >I've tried both, I get illegal character from the first and something
> >about illegal type from the second. Neither works.
>
> GOT IT!!!
>
>         ResultSet result = null;
>         CallableStatement sprocStmt = null;
>         String spName;
>         try
>         {
>             getDBConnection();         /* sets dbConnection */
>
>             spName = "{ ? = call getReporterData(?) }";
>
>             sprocStmt = dbConnection.prepareCall(spName);
>             sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);
> sprocStmt.setString(2, key);
>
>             result = sprocStmt.executeQuery();
>
> I called the executeQuery method of CallableStatement instead of the
> execute method. executeQuery returns a ResultSet, which contains the
> good stuff. Calling the execute and getObject methods of
> CallableStatement, which my examples showed, didn't work. Also, my
> examples showed "{call ? := call getReporterData(?) }", which didn't
> work. Remove the colon, move "call" between the equal sign and the
> function name.
>
> --
> Tim Slattery
> Slatter...@bls.govhttp://members.cox.net/slatteryt

I could've written it like this (Oracle-specific, so kinda nonportable,  but we're working with Oracle here anyway, don't we?)

try
{
  getDBConnection();
  // instantiate OracleCallableStatement by explicitly casting it   // this can require casting the Connection to OracleConnection first   OracleCallableStatement sprocStmt =
   (OracleCallableStatement)(dbConnection.prepareCall("{ ? = call getReporterData(?) }"));
  sprocStmt.registerOutParameter(1, OracleTypes.CURSOR);   sprocStmt.setString(2,key);
  try
  {
    sprocStmt.execute();
    ResultSet rs = sprocStmt.getCursor(1); // this method is Oracle- specific

    //
    // alternatively (more "platform-neutral" way):
    //
    // ResultSet rs;
    // if (sprocStmt.execute())
    //   rs = sprocStmt.getResultSet();
    //

    processResult(rs); // assuming there's dedicated method for that   }
  finally
  {
    sprocStmt.close();
  }
}
catch ...

Anyway, glad you were able to resolve the issue.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Aug 21 2008 - 00:48:45 CDT