Getting an error while calling mySql proceedure

Question

I have created a single procedure for update for different table say country & department. and in procedure i'hve mentioned an input parameter for table name along with other parameter. But unfortunately i got an error. Here is mySql Procedure:

CREATE DEFINER=`satish`@`%` PROCEDURE `p_update_Master_Name`(
IN tbl_Name VARCHAR(35),
IN tbl_column_old_value VARCHAR(35),
IN tbl_column_new_value VARCHAR(35),
IN tbl_user_id INT,
OUT msg INT 
)
BEGIN
IF EXISTS (SELECT Name from tbl_name where Name = tbl_column_new_value) then        
SET msg := '1';
-- failed case  

else

UPDATE tbl_name SET Name= tbl_column_value, Modified_Date=now(),       Modified_by=tbl_user_id where Name = tbl_column_old_value;
set msg := '0';
-- success 
END IF;
END

Im calling this procedure from java file.

CallableStatement cs = conn.prepareCall("{ call p_update_Master_Name(?,?,?,?,?)}");
            cs.setString(1, "country");
    cs.setString(2, real);
    cs.setString(3, mod);   
    cs.setInt(4, 01);
    cs.execute();
            cs.registerOutParameter(5, Types.INTEGER);
            int i=cs.getInt(5);

but it gives me a mysql.jdbc exception.

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'sims.tbl_name' doesn't 
exist

Please help me. Thanx in advance

As far as I can see the variable has been declare as tbl_Name and in your code is tbl_name — medina
– medina, Commented Jun 18, 2013 at 4:49
thnks. but somehow it doesn't work. even after changing the tbl_Name to tbl_name. im getting the same error — Subodh Bisht
– Subodh Bisht, Commented Jun 18, 2013 at 5:44

peterm · Accepted Answer · 2013-06-18 05:36:19Z

You can't use variables to define table or column (or any other db object for that matter) names in static SQL queries. They should be literals.

You have to use dynamic SQL to achieve your goal. Read more on the topic SQL Syntax for Prepared Statements

Your stored procedure might look like

DELIMITER $$
CREATE PROCEDURE p_update_Master_Name
(
    IN tbl_Name VARCHAR(35),
    IN tbl_column_old_value VARCHAR(35),
    IN tbl_column_new_value VARCHAR(35),
    IN tbl_user_id INT,
    OUT msg INT 
)
BEGIN
    SET @sql = CONCAT('SELECT (COUNT(*) > 0) INTO @result FROM ', tbl_name, ' WHERE Name = \'', tbl_column_new_value, '\'');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    SET msg = @result;

    IF @result = 0 THEN
      SET @sql = CONCAT('UPDATE ', tbl_name, 
                        ' SET Name = \'', tbl_column_new_value, 
                        '\', Modified_Date = NOW(), Modified_by = ', tbl_user_id, 
                        ' WHERE Name = \'', tbl_column_old_value, ' \'');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
    END IF;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Here is SQLFiddle demo

Chandra Shekhar · Accepted Answer · 2013-06-18 05:23:59Z

0

It seems there is some error with the procedure syntax

wrong one: 1. SELECT Name from *tbl_name* where Name = tbl_column_new_value) then
SET msg := '1'

In the above line of code you didn't set any value to tal_name;

You can refer the below syntax concentrate on INTO in the query: CREATE OR REPLACE PROCEDURE getDBUSERByUserId( p_userid IN DBUSER.USER_ID%TYPE, o_username OUT DBUSER.USERNAME%TYPE, o_createdby OUT DBUSER.CREATED_BY%TYPE, o_date OUT DBUSER.CREATED_DATE%TYPE) IS BEGIN

SELECT USERNAME , CREATED_BY, CREATED_DATE INTO o_username, o_createdby, o_date FROM DBUSER WHERE USER_ID = p_userid;

END;

answered Jun 18, 2013 at 5:23

Chandra Shekhar

2622 silver badges4 bronze badges

Collectives™ on Stack Overflow

Getting an error while calling mySql proceedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related