Search code examples
javastored-proceduresjdbcresultsetcallable-statement

Stored Procedure Returns null to Result Set

while working on JDBC, I got an issue I have created a table in mysql which contains the employee info Employee_Id,Name,City,Salary and Year_of_joining and i also created a stored procedure to the same database which takes two inputs and update the salary of employees base on the year of joining. Here is the Stored Procedure:

CREATE PROCEDURE proc(IN var1, IN var2)
BEGIN
  UPDATE "TABLE" SET Salary=var1+Salary WHERE Year_of_Joining= var2
END

Now i Created a database connection through java to mysql database (That connection is being established successfully)

But now, When i execute the query after creating the CallableStatement object and try to convert the current result of callable object to Result set i get null . Please help me with this. Here is the Code:

// Creates connection with java:
Connection con= DriverManager.getConnection(url,username,pswrd);

// Now Calling the Stored procedure from mysql
CallableStatement col =  con.prepareCall("{call proc(?,?)}");

// gave values to the procedure
col.setDouble(1,20000);
col.setInt(2,2012);
col.execute();

// Returning Result set=null
ResultSet rs = col.getResultSet();
Solution
    1. Firstly, you have not set the output parameter in your stored procedure.

    Like : 

    CREATE PROCEDURE proc(IN inputParam VARCHAR(255), OUT OutParam INT)...

    Then, to retrieve the values of output parameters (parameters specified as OUT or INOUT when you created the stored procedure), JDBC requires that they be specified before statement execution using the various registerOutputParameter() methods in the CallableStatement interface.

    col.registerOutParameter("OutParam", Types.INTEGER);
    1. Secondly, UPDATE statements don't return a ResultSet! Only SELECT statements return a ResultSet to operate upon.