*Unrecognized* procedures

[Thomas <Th...@t-online.de>]

Hello,

can't find the reason for the following issue:
(Derby 10.5.3.0 network server running on a Linux server, the java function has
been loaded into the database as part of a jar file, server running without
security policy).

When trying to call a procedure from a java program I am getting error '.. is
not recognised as a function or procedure'.

I used the exact same connection URL as used in the java program in IJ to
connect to the database and executed a 'values APPL."myFunction"("parm1",
"parm2")' and the call executed fine returning the expected result.

So I would conclude the procedure exists in that schema on the server.

In the program I used 
CallableStatement cs = conn.prepareCall("{ call APPL.\"myFunction\"(?, ?) \n}");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(1, parm1);
cs.setString(2, parm2);
which lead to 'APPL.myFunction' is not recognised as a function or procedure.

Commenting out the CallableStatement and issuing **from within the same program
(leaving driver, connectionURL untouched) ** a preparedStatement doing a "select
ALIAS FROM SYS.ALIASES \n" I can see that my Function exists.

I am passing the correct number of parameters. It can't be a classpath problem
as the java code is stored in the database. I am specifying the correct schema
name. When using eclipse data tools platform data explorer to connect to
database I can browse the system catalog and see the procedure...

So what else should I check??

Thanks

Re: *Unrecognized* procedures

[Kim Haase <Ca...@Sun.COM>]

On 01/14/10 19:20, Thomas wrote:
> The problem was the numbering of parameters in the registerOutParameter and the
> setxxx Statement:
> 
> instead of
> CallableStatement cs = conn.prepareCall("{ call APPL.\"myFunction\"(?, ?)");
> cs.registerOutParameter(1, java.sql.Types.VARCHAR);
> cs.setString(1, parm1);
> cs.setString(2, parm2);
> 
> CallableStatement cs = conn.prepareCall("{ ? = call APPL.\"myFunction\"(?, ?)");
> cs.registerOutParameter(1, java.sql.Types.VARCHAR);
> cs.setString(2, parm1);
> cs.setString(3, parm2);
> was correct/needed.
> 
> Unfortunately there was no specific hint in any documentation I read mentioning
> aspects of how the numbering needs to be done.

The API documentation for the java.sql.CallableStatement interface
(http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html 
and http://java.sun.com/javase/6/docs/api/java/sql/CallableStatement.html)
says, "Parameters are referred to sequentially, by number, with the
first parameter being 1."

The exception appears to be INOUT parameters, as described in the
Reference Manual at
http://db.apache.org/derby/docs/dev/ref/rrefjdbc75719.html, where the
same number is used for the parameter when you register it as an out
parameter and when you set its input value. If there are any errors in 
the Reference Manual topics on CallableStatement it would be helpful to 
know about them. The Reference Manual topics on JDBC generally focus on 
Derby-specific implementation details and other areas not covered by the 
API documentation.

Hope this helps --

Kim Haase

Re: *Unrecognized* procedures

[Thomas <Th...@t-online.de>]

The problem was the numbering of parameters in the registerOutParameter and the
setxxx Statement:

instead of
CallableStatement cs = conn.prepareCall("{ call APPL.\"myFunction\"(?, ?)");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(1, parm1);
cs.setString(2, parm2);

CallableStatement cs = conn.prepareCall("{ ? = call APPL.\"myFunction\"(?, ?)");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.setString(2, parm1);
cs.setString(3, parm2);
was correct/needed.

Unfortunately there was no specific hint in any documentation I read mentioning
aspects of how the numbering needs to be done.

Regards