CallableStatement problem

[Ricky James]

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
Ricky

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


[ April 02, 2007: Message edited by: Ricky James ]
[ April 02, 2007: Message edited by: Ricky James ]

[Jan Cumps]

Ricky, please post the error.

Your stored procedure GeneratePcId should return the value.

You did not mention your database brand, but you could do this as a return value, or as an out parameter.
Your jdbc call should fetch that new id. ("{? = call GeneratePcId()}")
You need to bind that out parameter to a java variable.

Oracle's tutors tell you how to do that:
Calling PL/SQL from Java.

Regards, Jan

[Ricky James]

Originally posted by Jan Cumps:
Ricky, please post the error.

Your stored procedure GeneratePcId should return the value.

You did not mention your database brand, but you could do this as a return value, or as an out parameter.
Your jdbc call should fetch that new id. ("{? = call GeneratePcId()}")
You need to bind that out parameter to a java variable.

Oracle's tutors tell you how to do that:
Calling PL/SQL from Java.

Regards, Jan

Hi Jan,

Thanks for the reply.

I am using MySQL.

I get the following error when I try to run the program, although it compiles:

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:33)
at CallableStatementExercise.main(CallableStatementExercise.java:12)
BUILD SUCCESSFUL (total time: 0 seconds)


And Jan, I didn't really understand where I could use the statement that you suggested. Can you please elaborate?

Thanks
Ricky

[Ricky James]

Hi,

I forgot to execute the statement. I have rectified that but I still get the same error:

The error that I get now is pasted below. Please let me know if it makes sense to you.

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

[Jan Cumps]

The error exactly tells what you have to do:
java.sql.SQLException: No output parameters registered.

This link 18.4.5.1.3. Using CallableStatements to Execute Stored Procedures shows how you pass an out parameter from a MySQL procedure, and how to bind the parameter.

By reading this link, and the previous link I provided you to Oracle's documentation, you'll get it working.

Regards, Jan
[ April 02, 2007: Message edited by: Jan Cumps ]