help with Callable Statements
Hi,
I am working on a problem which requires me to insert record into the database (mySQL) using Callable Statements.
For this I have written 2 Stored Procedures (SP) which are as follows:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GeneratePcId`()
BEGIN
select max(pc_id)+1 from price;
END $$
CREATE DEFINER=`root`@`localhost` 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 $$
The frist SP returns a number that adds 1 to the max of pc_id column which is the PK and max means the last row value.
This value I want to use as a value for inserting the new row into the table. I have worked on this and the program chunk is here:
// Create CallableStatement object
CallableStatement genid = conn.prepareCall("{? = call GeneratePcId()}");
genid.registerOutParameter(1, Types.INTEGER);
//ResultSet rs = (ResultSet)genid.executeQuery();
//rs.next();
genid.executeUpdate();
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 when I run this program I get the following error:
java.lang.NullPointerException at com.mysql.jdbc.CallableStatement$CallableStatementParamInfo.iterator(CallableStatement.java:324)
at com.mysql.jdbc.CallableStatement.setInOutParamsOnServer(CallableStatement.java:1938)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:749)
at com.mysql.jdbc.CallableStatement.executeUpdate(CallableStatement.java:808)
at CallableStatementExercise.query(CallableStatementExercise.java:36)
at CallableStatementExercise.main(CallableStatementExercise.java:13)
Line 13 points togenid.executeUpdate();
Can anybody please advise me what the problem is?
Shall be really grateful.
Templar_Knight
Message was edited by:
templar_knight
