This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft 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 Java 8 in Action this week in the Java 8 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: 18150
    
    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: 29274
    
140

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
Need a little help with the map container
design problem
CallableStatement resultset returns only columns and no data, where data exists.
How to find the column names..???
import data from large csv file. Help.