• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Sql query ignores variable

 
Ranch Hand
Posts: 90
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 90
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic