aspose file tools*
The moose likes JDBC and the fly likes CallableStatement problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CallableStatement problem" Watch "CallableStatement problem" New topic
Author

CallableStatement problem

Ricky James
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
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:



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
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Ricky James
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
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
Ranch Hand

Joined: Mar 26, 2007
Posts: 83
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
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: CallableStatement problem