You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Yan Ma <po...@yahoo.com.cn> on 2006/03/25 17:47:17 UTC
How to get call a java procedure that returns a value?
Thx!
－－－－－－－－－－－－－－－－－－
马燕
中科院软件所
通用软件实验室
__________________________________________________
赶快注册雅虎超大容量免费邮箱?
http://cn.mail.yahoo.com
回复： Re: How to get call a java procedure that returns a value?
Posted by 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免费邮箱百分百防垃圾信
雅虎助手-搜索、杀毒、防骚扰
Re: How to get call a java procedure that returns a value?
Posted by Stanley Bradbury <St...@gmail.com>.
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 <ka...@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.
Re: Re: How to get call a java procedure that returns a value?
Posted by 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> 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
Re: How to get call a java procedure that returns a value?
Posted by 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
>