CallableStatement problem

[ros]

Hi,

I am trying to insert a record into my database using 2 stored
procedures. These are pasted below. The code that I am using in the
java program is as follows:

code:

// Create CallableStatement object
CallableStatement genid = conn.prepareCall("{call
GeneratePcId()}");
int number = genid.getInt(1);
CallableStatement cstmt = conn.prepareCall("{call
InsertPrice (?, ?, ?, ?, ?)}");

// Bind values to the parameters
cstmt.setInt(1, number );
cstmt.setInt(2, 8);
cstmt.setInt(3, 600);
cstmt.setDate(4,
Date.valueOf("2008-01-01"));
cstmt.setDate(5, Date.valueOf("2008-02-02"));

But This generates an error. Basically the GeneratePcId() stored
procedure returns the value which is an integer and I want to use this
as the input of InsertPrice.

I am getting an error in this line: int number = genid.getInt(1);

Can anybody advise what the problem is?

Thanks
ros

PROCEDURE `InsertPrice`(pc_id INT, pc_level INT, price INT, from_date
DATE, to_date DATE)
BEGIN
INSERT INTO price VALUES (pc_id, pc_level, price, from_date, to_date);
END $$

PROCEDURE `GeneratePcId`()
BEGIN
select max(pc_id)+1 as newpcid from price;
END $$

[Robert Klemme]

On 02.04.2007 13:47, ros wrote:
> I am trying to insert a record into my database using 2 stored
> procedures. These are pasted below. The code that I am using in the
> java program is as follows:
>
> code:
>
> // Create CallableStatement object
> CallableStatement genid = conn.prepareCall("{call
> GeneratePcId()}");
> int number = genid.getInt(1);
> CallableStatement cstmt = conn.prepareCall("{call
> InsertPrice (?, ?, ?, ?, ?)}");
>
> // Bind values to the parameters
> cstmt.setInt(1, number );
> cstmt.setInt(2, 8);
> cstmt.setInt(3, 600);
> cstmt.setDate(4,
> Date.valueOf("2008-01-01"));
> cstmt.setDate(5, Date.valueOf("2008-02-02"));
>
> But This generates an error. Basically the GeneratePcId() stored
> procedure returns the value which is an integer and I want to use this
> as the input of InsertPrice.
>
> I am getting an error in this line: int number = genid.getInt(1);

You don't post the error (which btw, is not a good idea) but it is most
likely caused by the fact that you never *execute* the call - hence
there's no value to retrieve.

robert

[ros]

Thanks for the reply Robert. And thanks for pointing out that I forgot
the execute statement. I was thinking that you gave me the solution
until I tried running the code.

The error that I get now is:

init:
deps-jar:
compile-single:
run-single:
Connecting to the database...
java.sql.SQLException: No output parameters registered.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
910)
at
com.mysql.jdbc.CallableStatement.getOutputParameters(CallableStatement.java:
1426)
at
com.mysql.jdbc.CallableStatement.getInt(CallableStatement.java:1247)
at
CallableStatementExercise.query(CallableStatementExercise.java:34)
at
CallableStatementExercise.main(CallableStatementExercise.java:12)
BUILD SUCCESSFUL (total time: 0 seconds)

And it points to "int number = genid.getInt(1);".

The code is here:

// Create CallableStatement object
CallableStatement genid = conn.prepareCall("{call
GeneratePcId()}");

genid.execute();

int number = genid.getInt(1);
CallableStatement cstmt = conn.prepareCall("{call
InsertPrice (?, ?, ?, ?, ?)}");

// Bind values to the parameters
cstmt.setInt(1, number );
cstmt.setInt(2, 8);
cstmt.setInt(3, 600);
cstmt.setDate(4, Date.valueOf("2008-01-01"));
cstmt.setDate(5, Date.valueOf("2008-02-02"));

// Execute the query
cstmt.execute();

Thanks again.
ros

[ros]

And btw, I am using MySQL. Sorry forgot to mention it.
ros

[Bart Cremers]

As the SQLException states, you need to register the outputparameter
for the first statement as you try to retrieve a value from it. So
use:

genid.registerOutParameter(1, java.sql.Types.INTEGER) before executing
the statement.

Regards,

Bart