db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: 回复: Re: How to get call a java procedure that returns a value?
Date Sun, 26 Mar 2006 06:38:19 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=GB2312" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<br>
Yan Ma wrote:
<blockquote
 cite="mid20060326030219.86045.qmail@web15806.mail.cnb.yahoo.com"
 type="cite">
  <div>Many thanks for your reply!</div>
  <div>&nbsp;</div>
  <div>However I may not present my question clearly.</div>
  <div><br>
In derby 10.1, we usually get returned values through OUT parameters.<br>
My question is can we get a return value through the java methods'
return value?</div>
  <div>&nbsp;</div>
</blockquote>
Don't think Derby PROCEDURE can return a value, but could you use
FUNCTION? Check Derby docs for syntax... it is pretty similar to
PROCEDURE.<br>
<br>
Satheesh<br>
<blockquote
 cite="mid20060326030219.86045.qmail@web15806.mail.cnb.yahoo.com"
 type="cite">
  <div>1. I wrote a simple JAVA METHOD that RETURNS a int value:</div>
  <div>public class Simple {</div>
  <div>&nbsp;public static int returnint(int id){<br>
&nbsp;&nbsp;return id * id;<br>
&nbsp;}<br>
}</div>
  <div>&nbsp;</div>
  <div>2. Generated a jar file, installed it and modified database
classpath.</div>
  <div>&nbsp;</div>
  <div>3. Created a procedure named RETURNINT like the following:<br>
CREATE PROCEDURE RETURNINT (IN ID INTEGER) PARAMETER STYLE JAVA
LANGUAGE JAVA MODIFIES SQL DATA EXTERNAL NAME 'Simple.returnint';</div>
  <div>&nbsp;</div>
  <div>4. Invoked it from java code:<br>
&nbsp;// invoke the procedure RETURNINT, expected result is 2*2 = 4.<br>
&nbsp;callStmt = con.prepareCall(? = CALL RETURNINT (2));</div>
  <div>&nbsp;</div>
  <div>5. Then I got the exception:<br>
ERROR 42Y03: 'RETURNINT' is not recognized as a function or procedure.<br>
&nbsp;at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)<br>
&nbsp;at
org.apache.derby.impl.sql.compile.StaticMethodCallNode.bindExpression(Unknown
Source)<br>
&nbsp;at
org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(Unknown
Source)<br>
&nbsp;at
org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown
Source)<br>
&nbsp;at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown
Source)<br>
&nbsp;at
org.apache.derby.impl.sql.compile.RowResultSetNode.bindExpressions(Unknown
Source)<br>
&nbsp;at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)<br>
&nbsp;at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)<br>
&nbsp;at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown
Source)<br>
&nbsp;at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)<br>
&nbsp;at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)<br>
&nbsp;at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)<br>
&nbsp;at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)<br>
&nbsp;at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)<br>
&nbsp;at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)<br>
&nbsp;at
com.ibm.wbiserver.relationshipservice.test.TestLoadRelationship.test(TestLoadRelationship.java:322)<br>
&nbsp;at
com.ibm.wbiserver.relationshipservice.test.TestLoadRelationship.main(TestLoadRelationship.java:386)<br>
&nbsp;<br>
A similar&nbsp;exception happens if I run the code:</div>
  <div>callStmt = con.prepareCall(? = CALL APP.RETURNINT (2));</div>
  <div>&nbsp;</div>
  <div>I have checked sys.sysaliases, sys.sysfiles, my jar file and
procedure do exist.<br>
  </div>
  <div>&nbsp;</div>
  <div><b><i>Rajesh Kartha <a class="moz-txt-link-rfc2396E" href="mailto:kartha02@gmail.com">&lt;kartha02@gmail.com&gt;</a></i></b>
写道:</div>
  <blockquote class="replbq"
 style="border-left: 2px solid rgb(16, 16, 255); padding-left: 5px; margin-left: 5px;"><br>
Here is a very simple example of returning an INT from a Java Stored<br>
Procedure:<br>
    <br>
In this case, the SP takes in an int and adds '100' to it.<br>
    <br>
Implementation:<br>
---------------<br>
public class MyProc {<br>
public static void retIntegerValue(int i1[], int i3) {<br>
i1[0]=i3+100;<br>
}<br>
}<br>
    <br>
Creating the Stored Procedure:<br>
-------------------------------<br>
drop procedure retIntVal;<br>
CREATE PROCEDURE retIntVal(OUT a INT, IN b INT) PARAMETER STYLE JAVA<br>
LANGUAGE JAVA EXTERNAL NAME 'MyProc.retIntegerValue';<br>
    <br>
Invoking it from your code:<br>
----------------------------<br>
CallableStatement op1 = connCS.prepareCall("CALL retIntVal(?, ?)");<br>
op1.registerOutParameter(1, Types.INTEGER); //Important<br>
op1.setInt(2,24); //Provide the input value<br>
op1.execute();<br>
int newi=op1.getInt(1);<br>
System.out.println("New Int value = "+newi);<br>
    <br>
Output:<br>
--------<br>
New Int value = 124<br>
    <br>
    <br>
More info available at:<br>
    <br>
<a class="moz-txt-link-freetext" href="http://wiki.apache.org/db-derby/DerbyInstruction">http://wiki.apache.org/db-derby/DerbyInstruction</a><br>
    <br>
HTH<br>
    <br>
-Rajesh<br>
    <br>
Yan Ma wrote:<br>
    <br>
&gt; Thx!<br>
&gt;<br>
&gt; ------------------<br>
&gt; 马燕<br>
&gt;<br>
&gt; 中科院软件所<br>
&gt; 通用软件实验室<br>
&gt;<br>
&gt; __________________________________________________<br>
&gt; 赶快注册雅虎超大容量免费邮箱?<br>
&gt; <a class="moz-txt-link-freetext" href="http://cn.mail.yahoo.com">http://cn.mail.yahoo.com</a><br>
&gt;<br>
    <br>
  </blockquote>
  <br>
  <p> </p>
  <hr size="1"><a href="http://cn.mail.yahoo.com" target="blank">
雅虎1G免费邮箱百分百防垃圾信</a><br>
  <a href="http://cn.zs.yahoo.com" target="blank">雅虎助手-搜索、杀毒、防骚扰</a>
</blockquote>
</body>
</html>


Mime
View raw message