Stored Procedure with CallableStatement

[leekiangiap]

I am trying the Stored Procedure of H2 Database, as follows:

DROP ALIAS IF EXISTS ADDUSER;
CREATE ALIAS ADDUSER AS $$
import java.util.Date;
@CODE
int addUser(String pname, Date birthday, float money, int id) {
id=15;
return id;
}
$$;


my Java code is as follows, a method using CallableStatement to get
value return from Stored Procedure:

static void sp() throws SQLException {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "{CALL ADDUSER(?,?,?,?)}";
cs = conn.prepareCall(sql);
cs.setString(1, "ps name");
cs.setDate(2, new java.sql.Date(System.currentTimeMillis()));
cs.setFloat(3, 100f);
cs.registerOutParameter(4, Types.INTEGER);

cs.executeUpdate();

int id = cs.getInt(1);

System.out.println("id=" + id);
} finally {
JdbcUtils.free(rs, cs, conn);
}
}

However, I got the following exception:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Feature not
supported: "registerOutParameter"

Notice that the source code of org.h2.jdbc.JdbcCallableStatement is
implemented by throwing Exception.

May I know anyway I can get the return value from the PROCEDURE ?

[Sergi Vladykin]

Try "select adduser(?,?,?,?)" without any out parameters

[Lee Kian Giap]

Thanks for your reply !

I wish to know whether it is possible to use CallableStatement in java to get the result ? any solution ?

2010/9/26 Sergi Vladykin <sergi.v...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Sergi Vladykin]

It seems that CallableStatements are not supported. Do you really need
them?

On 26 сен, 20:23, Lee Kian Giap <leekiang...@gmail.com> wrote:
> Thanks for your reply !
>
> I wish to know whether it is possible to use CallableStatement in java to
> get the result ? any solution ?
>

> 2010/9/26 Sergi Vladykin <sergi.vlady...@gmail.com>

> > h2-database...@googlegroups.com<h2-database%2Bunsu...@googlegroups.com>
> > .

[Kerry Sainsbury]

On Mon, Sep 27, 2010 at 10:01 AM, Sergi Vladykin <sergi.v...@gmail.com> wrote:

It seems that CallableStatements are not supported. Do you really need
them?

It looks to me like he's trying to find a way to call arbitrary bits of code from a trigger.
Currently the easiest way is to put the arbitrary bit of code on the server's classpath,
and create the object inside the trigger.

This is one of the things I'd like to make easier with the "MODULE" functionality that
allows an end user to put bits of code into H2 without having to build a jar file and
get it onto the server.

I spent most of the weekend reworking the code. Hopefully I'll have something to
show fairly soon -- including support for the Bean Scripting Framework....
(Thomas please don't hit me!)

Cheers,
Kerry (who accidentally unsubscribed from the mailing list a while ago, and didn't notice!)

 

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[leekiangiap]

Sorry, let me clarify a bit, I know normally Stored Procedure with
CallableStatment is not frequently use nowadays because we are now
having 3 layer design. It is important in the old days with where
client directly interact with database.

It is actually for learning purpose on CallableStatement. Previously,
I am using MySQL to create PROCEDURE as follows:

DELIMETER $$

DROP PROCEDURE IF EXISTS 'jdbc'.'addUser' $$
CREATE PROCEDURE 'jdbc'.'addUser' (in pname varchar(45), in birthday
date, in money float, out pid int)
BEGIN
insert into user(name, birthday, money) values (pname, birthday,
money);
END $$

DELIMETER ;


and I use java callableStatment to get the result of "out" from the
PROCEDURE, java code as follows:

String sql = "{ call addUser(?,?,?,?) }";
callableStatement = connectino.prepareCall(sql);
callableStatement.registerOutParameter(4, Types.INTEGER);
callableStatement.setString(1, "Ken");
callableStatement.setDate(2, new
java.sql.Date(System.currentTimeMillis()));
callableStatement.setFloat(3, 100f);

callableStatement.executeUpdate();

int id = cs.getInt(4); // this is the code that I can retrieve the
result from PROCEDURE


after that I decided to give H2 Database a try, but the PROCEDURE in
h2 doesn't seems like the normal Stored Procedure.

or actually the above scenario not able to achieve in H2 if I
forcefully want to use CallableStatment since it is not supported ?

On Sep 27, 6:16 am, Kerry Sainsbury <ke...@fidelma.com> wrote:
> On Mon, Sep 27, 2010 at 10:01 AM, Sergi Vladykin

> <sergi.vlady...@gmail.com>wrote:

> > <h2-database%2Bunsu...@googlegroups.com<h2-database%252Buns...@googlegroups.com>

[Sergi Vladykin]

If you have lots of inherited code which uses callable statements then
it looks like a problem to use H2. Otherwise you should just use
prepared statements instead of callables.