CallableStatement problem

Thread view:

ros - 02 Apr 2007 12:49 GMT

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:

// 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 $$

Reply to this Message

Tom Hawtin - 02 Apr 2007 13:15 GMT

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

Do you not want to execute the statement now you have prepared it?

> int number = genid.getInt(1);

Looking at the API docs...

The syntax for a result parameter is

"{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}"

Also note

"If used, the result parameter must be registered as an OUT parameter."

> But this generates an error. Basically the GeneratePcId() stored

You wouldn't want to quote the error, would you?

> 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 $$

Stored procedure syntax is vendor-specific. You've not told us which
product you are using.

Tom Hawtin

Reply to this Message

ros - 02 Apr 2007 13:40 GMT

> > // Create CallableStatement object
> > CallableStatement genid = conn.prepareCall("{call
[quoted text clipped - 28 lines]
>
> Tom Hawtin

Thanks for the reply Tom. 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

Reply to this Message

Lew - 02 Apr 2007 14:07 GMT

Tom Hawtin <use...@tackline.plus.com> wrote:

>> Also note
>>
>> "If used, the result parameter must be registered as an OUT parameter."

> The error that I get now is:
>
> java.sql.SQLException: No output parameters registered.
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)

Tom already answered this part of the question.

-- Lew

Reply to this Message

ros - 02 Apr 2007 14:27 GMT

> Tom Hawtin <use...@tackline.plus.com> wrote:
> >> Also note
[quoted text clipped - 8 lines]
>
> -- Lew

I have tried this again. But get the same error:

// Create CallableStatement object
CallableStatement genid = conn.prepareCall("{? = call
GeneratePcId()}");
genid.registerOutParameter(1, java.sql.Types.INTEGER);
ResultSet rs = genid.executeQuery();
//genid.execute();
int number = rs.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();

Can you see what the error is?
Cheers
ros

Reply to this Message

Tom Hawtin - 02 Apr 2007 15:04 GMT

> CallableStatement genid = conn.prepareCall("{? = call
> GeneratePcId()}");

[...]

> ResultSet rs = genid.executeQuery();

The statement does not act as a query. It returns via an integer out
parameter, not via a ResultSet.

Tom Hawtin

Reply to this Message

ros - 02 Apr 2007 13:46 GMT

> > // Create CallableStatement object
> > CallableStatement genid = conn.prepareCall("{call
[quoted text clipped - 28 lines]
>
> Tom Hawtin

And Tom, I am using MySQL for this program.
Thanks

Reply to this Message