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 <[email protected]> wrote:
> On Mon, Sep 27, 2010 at 10:01 AM, Sergi Vladykin
> <[email protected]>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!)
>
>
>
> > On 26 сен, 20:23, Lee Kian Giap <[email protected]> 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 <[email protected]>
>
> > > > Try "select adduser(?,?,?,?)" without any out parameters
>
> > > > On 26 сен, 15:45, leekiangiap <[email protected]> wrote:
> > > > > 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 ?
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > Groups
> > > > "H2 Database" group.
> > > > To post to this group, send email to [email protected].
> > > > To unsubscribe from this group, send email to
> > > > [email protected]<h2-database%[email protected]>
> > <h2-database%[email protected]<h2-database%[email protected]>
>
> > > > .
> > > > For more options, visit this group at
> > > >http://groups.google.com/group/h2-database?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "H2 Database" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected]<h2-database%[email protected]>
> > .
> > For more options, visit this group at
> >http://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
