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:

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

 

[Tom Hawtin]

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

 

[ros]

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

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

 

[ros]

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

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

 

[Lew]

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

 

[ros]

Tom already answered this part of the question.

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

 

[Tom Hawtin]

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