Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sql query ignores variable

 
Lila Fowler
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.



P.S: Im using mySql
 
Stephan Ort
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to use variables inside a sql statement you will have to use PreparedStatement s.

One example:



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
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In this case I think you would use a CallableStatement, not a PreparedStatement.
 
Stephan Ort
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At Paul: Yes you are right.

It is then possible to make use of the set methods to accomplish the variable replacement.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34378
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic