Sql query ignores variable

[Lila Fowler]

Heya =)

I cant get the column to create with the variable name that's being sent to it, instead it creates a column names: columnName

Any ideas how i could fix this?

Thanks.

   
CREATE PROCEDURE `CSSEDIANDPRINTCHARGE_ADD_COLUMN`(IN columnName VARCHAR(50))
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
ALTER TABLE CSSEDIANDPRINTCHARGE ADD columnName INTEGER(10) NOT NULL DEFAULT '0'; //Creates a column named columnName instead of the variable
COMMIT;
END; 

P.S: Im using mySql

[Stephan Ort]

If you want to use variables inside a sql statement you will have to use PreparedStatement s.

One example:

PreparedStatement updatePstat = connection.prepareStatement(
          "UPDATE highscore SET victories=?, score=? WHERE player=?");

updatePstat.setInt(1, /*victoryVariable*/);
updatePstat.setInt(2, /*scoreVariable*/);
updatePstat.setString(3, /*playerVariable*/);

updatePstat.executeUpdate();

The ? can't be placed whereever you want. For example you can't create a table using a variable where its value should be the name of the new table.

[Paul Clapham]

In this case I think you would use a CallableStatement, not a PreparedStatement.

[Stephan Ort]

At Paul: Yes you are right.

It is then possible to make use of the set methods to accomplish the variable replacement.

[Jeanne Boyarsky]

Stored procedures are a little different. You can pass a variable for a column name and concatenate it with SQL in the stored proc. Beware of SQL injection attacks though. By using this technique, you lose the safety of prepared statements.

[Lila Fowler]

Thanks, I ended up using the prepare statement and it works great:

CREATE PROCEDURE `CSSEDIANDPRINTCHARGE_ADD_COLUMN`(IN columnName VARCHAR(50))
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET @add_column = CONCAT("ALTER TABLE CSSEDIANDPRINTCHARGE ADD COLUMN ", columnName, " INTEGER(10) NOT NULL DEFAULT '0'");
PREPARE add_column FROM @add_column;
EXECUTE add_column;
DEALLOCATE PREPARE add_column;
END;

thanks guys