回复： Re: How to get call a java procedure that returns a value?

[Yan Ma <po...@yahoo.com.cn>]

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 <ka...@gmail.com> 写道：
  
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
>



		
---------------------------------
 雅虎1G免费邮箱百分百防垃圾信
 雅虎助手-搜索、杀毒、防骚扰