This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Sql query ignores variable Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Sql query ignores variable" Watch "Sql query ignores variable" New topic
Author

Sql query ignores variable

Lila Fowler
Ranch Hand

Joined: Jul 31, 2009
Posts: 84
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


Courage is not the absence of fear but rather the judgment that something is more important then fear. ~ Ambrose Redmond
Stephan Ort
Greenhorn

Joined: Jun 08, 2010
Posts: 5
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Jun 08, 2010
Posts: 5
At Paul: Yes you are right.

It is then possible to make use of the set methods to accomplish the variable replacement.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Lila Fowler
Ranch Hand

Joined: Jul 31, 2009
Posts: 84
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Sql query ignores variable
 
Similar Threads
How to find the column names..???
design problem
CallableStatement resultset returns only columns and no data, where data exists.
Need a little help with the map container
import data from large csv file. Help.