db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: How to get call a java procedure that returns a value?
Date Mon, 27 Mar 2006 18:54:01 GMT
Yan Ma wrote:

>Many thanks for your reply!         However I may not present my question clearly.   
  In derby 10.1, we usually get returned values through OUT parameters.  My question is can
we get a return value through the java methods' return value?         1. I wrote a simple
JAVA METHOD that RETURNS a int value:    public class Simple {     public static int returnint(int
id){    return id * id;   }  }         2. Generated a jar file, installed it and modified
database classpath.         3. Created a procedure named RETURNINT like the following:  CREATE
PROCEDURE RETURNINT (IN ID INTEGER) PARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL  DATA
EXTERNAL NAME 'Simple.returnint';         4. Invoked it from java code:   // invoke the procedure
RETURNINT, expected result is 2*2 = 4.   callStmt = con.prepareCall(? = CALL RETURNINT (2));
        5. Then I got the exception:  ERROR 42Y03: 'RETURNINT' is not recognized as a function
or procedure.   at
> org.apache.derby.iapi.error.StandardException.newException(Unknown Source)   at org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(Unknown
Source)   at org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(Unknown Source)
  at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown Source)   at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown
Source)   at org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(Unknown Source)
  at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)  
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)   at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown
Source)   at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)   at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)   at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)   at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
 Source)   at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)   at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
Source)   at com.ibm.wbiserver.relationshipservice.test.TestLoadRelationship.test(TestLoadRelationship.java:322)
  at com.ibm.wbiserver.relationshipservice.test.TestLoadRelationship.main(TestLoadRelationship.java:386)
    A similar exception happens if I run the code:    callStmt = con.prepareCall(? = CALL
APP.RETURNINT (2));       I have checked sys.sysaliases, sys.sysfiles, my jar file and procedure
do exist.
>
>
>
> */Rajesh Kartha <kartha02@gmail.com>/* wrote:
>
>
>     Here is a very simple example of returning an INT from a Java Stored
>     Procedure:
>
>     In this case, the SP takes in an int and adds '100' to it.
>
>     Implementation:
>     ---------------
>     public class MyProc {
>     public static void retIntegerValue(int i1[], int i3) {
>     i1[0]=i3+100;
>     }
>     }
>
>     Creating the Stored Procedure:
>     -------------------------------
>     drop procedure retIntVal;
>     CREATE PROCEDURE retIntVal(OUT a INT, IN b INT) PARAMETER STYLE JAVA
>     LANGUAGE JAVA EXTERNAL NAME 'MyProc.retIntegerValue';
>
>     Invoking it from your code:
>     ----------------------------
>     CallableStatement op1 = connCS.prepareCall("CALL retIntVal(?, ?)");
>     op1.registerOutParameter(1, Types.INTEGER); //Important
>     op1.setInt(2,24); //Provide the input value
>     op1.execute();
>     int newi=op1.getInt(1);
>     System.out.println("New Int value = "+newi);
>
>     Output:
>     --------
>     New Int value = 124
>
>
>     More info available at:
>
>     http://wiki.apache.org/db-derby/DerbyInstruction
>
>     HTH
>
>     -Rajesh
>
>     Yan Ma wrote:
>
>     > Thx!
>     >
>     > ------------------
>     > 马燕
>     >
>     > 中科院软件所
>     > 通用软件实验室
>     >
>     > __________________________________________________
>     > 赶快注册雅虎超大容量免费邮箱?
>     > http://cn.mail.yahoo.com
>     >
>
>
> __________________________________________________
> 赶快注册雅虎超大容量免费邮箱?
> http://cn.mail.yahoo.com
>
Hi -
Derby is looking for a procedure that does not return a value. Since
your method does return a value the signature does not match - your
parameter needs to be defined as INOUT. The error I get lists the
signature expected (note return type is VOID).
ij> call RETURNINT(2);
ERROR 42X50: No method was found that matched the method call
void Simple.returnint(int)

I believe you will need to set ID in your method to the new value.



Mime
View raw message