java.sql.SQLException: Parameter number 2 is not an OUT parameter

Question

I am getting an Error while running this:

1. cs = getCon1().prepareCall("{CALL SaveLabourWageDetails(?,?)}");

2. cs.setString(1, user.getUserId());

3. cs.registerOutParameter(2, java.sql.Types.INTEGER); //<--- ERROR at this line

4. cs.execute();

5. String lastIsertId=cs.getString(2);

The Stored Procedure is :

CREATE

    PROCEDURE `cheque_alert`.`SaveLabourDetailsHead`(IN wage_entered_by VARCHAR(10),OUT LastInsertId INT)

    BEGIN
    INSERT INTO `cheque_alert`.`labour_wage_head`
            (
             `wage_entered_by`,
             `entered_date_time`)
    VALUES (wage_entered_by,
         NOW());

          SELECT LAST_INSERT_ID() INTO LastInsertId;

    END$$

DELIMITER ;

Please point out the problem in this code..

The procedure is named SaveLabourDetailsHead and you call SaveLabourWageDetails, that might be the problem. — user180100
– user180100, Commented Oct 25, 2013 at 5:54
I made a simple mistake.. and spoiled lot of time.. thanks alot — Abdul Razak
– Abdul Razak, Commented Oct 25, 2013 at 6:01

Aniket Kulkarni · Accepted Answer · 2013-10-25 06:09:25Z

2

You are calling wrong procedure. You have procedure SaveLabourDetailsHead and you are calling

1. cs = getCon1().prepareCall("{CALL SaveLabourWageDetails(?,?)}");  
                                         ↑

Change to,

1. cs = getCon1().prepareCall("{CALL SaveLabourDetailsHead(?)}");

Set String parameter wage_entered_by.

answered Oct 25, 2013 at 6:09

Aniket Kulkarni

13k10 gold badges70 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dark Knight · Accepted Answer · 2013-10-25 05:52:29Z

0

Your out parameter is of type String, but it should be int. Try this out.

int lastIsertId=cs.getInt(2);

answered Oct 25, 2013 at 5:52

Dark Knight

8,3574 gold badges41 silver badges58 bronze badges

Comments

Miss Chanandler Bong · Accepted Answer · 2020-02-27 11:51:06Z

0

I had the same problem but the output exception is misleading as the root cause was the procedure name.

I typed incorrect procedure which did not exist in database.

Instead of providing a SQL exception something like "routine does not exist", it gave:

java.sql.SQLException: Parameter number 2 is not an OUT parameter.

edited Feb 27, 2020 at 11:51

Miss Chanandler Bong

4,27811 gold badges30 silver badges40 bronze badges

answered Feb 27, 2020 at 11:19

Sahil Garg

1676 bronze badges

Comments

hertg · Accepted Answer · 2020-06-25 12:34:41Z

0

Just had the same problem.

It was a permission issue in my case. The MySQL user my application uses lacked the EXECUTE permission on the schema I'm working on.

GRANT EXECUTE ON <your_schema>.* TO '<your_user>'@'localhost';
FLUSH PRIVILEGES;

answered Jun 25, 2020 at 12:34

hertg

1732 silver badges11 bronze badges

Comments

Yowser · Accepted Answer · 2023-10-23 10:52:50Z

0

I was chasing this for ages, then found out I'd missed and underscore from the name of the stored procedure! I didn't even have a procedure with the incorrect name.

What a stupid error message!

"The parameter is wrong - ooh, by the way, that procedure doesn't exist"

answered Oct 23, 2023 at 10:52

Yowser

461 silver badge4 bronze badges

Comments

Jericho · Accepted Answer · 2024-05-11 13:22:37Z

0

I was getting this error because procedure was not creatd at all in database. I created procedure after that it started to work. Point is sometimes error is misleading not showing the actual error.

answered May 11, 2024 at 13:22

Jericho

11.1k39 gold badges126 silver badges207 bronze badges

Collectives™ on Stack Overflow

java.sql.SQLException: Parameter number 2 is not an OUT parameter

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related