Prepared statement

[Hans Bijvoet]

In Java, I have the following code snippet:
PreparedStatement ps = conn.prepareStatement("INSERT INTO ? (price) VALUES
(?)");
ps.setString(1, tableName); // tableName = article
ps.setFloat(2, price); // price = 12.24
ps.executeUpdate();

This will raise a SQLException because the sql-string becomes: INSERT INTO
"article" (price) VALUES (12.24).
How can I avoid that the tablename is surrounded by quote's?

Greetings,
Hans

 

[Chris Smith]

In Java, I have the following code snippet:
PreparedStatement ps = conn.prepareStatement("INSERT INTO ? (price) VALUES
(?)");
ps.setString(1, tableName); // tableName = article
ps.setFloat(2, price); // price = 12.24
ps.executeUpdate();

This will raise a SQLException because the sql-string becomes: INSERT INTO
"article" (price) VALUES (12.24).
How can I avoid that the tablename is surrounded by quote's?

I'm assuming that you are actually getting 'article' in single quotes,
rather than "article" in double quotes.

In any case, you are misusing prepared statements. The values plugged
into prepared statements MUST be actual SQL values. The table name
needs to be inserted directly into the string itself. If your intent is
to actually build dynamically structured queries as you're doing, then
you should build them in String variables instead of trying to abuse
PreparedStatement for the purpose.

May I also suggest that if you have several tables that all have a
column called "price", and you're intending to insert into them
interchangably based on the value of some variable, then there is
something VERY seriously wrong with your database schema. If you're in
any position to do so, you should go Google for normalization, and then
consider re-designing the database. If you're in a position to
advocate, then advocate for someone else to do that. You are missing a
large part of the whole point of a relational database, and probably
wasting your time and putting your data at unnecessary risk.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[gimme_this_gimme_that]

Question marks are for fields only.

Do :

ps = conn.prepareStatement("INSERT INTO " + tableName + "(PRICE) VALUES
(?)");