Error with simple Parameterized Query - Java/ SQL

Question

Following on from one of my previous questions to do with method design I was advised to implemented my SQL queries as a parameterized query as opposed to a simple string.

I've never used parameterized queries before so I decided to start with something simple, take the following Select statement:

String select = "SELECT * FROM ? ";

PreparedStatement ps = connection.prepareStatement(select);
ps.setString(1, "person");

This gives me the following error: "[SQLITE_ERROR] SQL error or missing database (near "?": syntax error)"

I then tried a modified version which has additional criteria;

String select = "SELECT id FROM person WHERE name = ? ";

PreparedStatement ps = connection.prepareStatement(select);
ps.setString(1, "Yui");

This version works fine, in the my first example am I missing the point of parameterized queries or am I constructing them incorrectly?

Thanks!

Andrew White · Accepted Answer · 2011-03-30 19:18:50Z

9

Simply put, SQL binds can't bind tables, only where clause values. There are some under-the-hood technical reasons for this related to "compiling" prepared SQL statements. In general, parameterized queries was designed to make SQL more secure by preventing SQL injection and it had a side benefit of making queries more "modular" as well but not to the extent of being able to dynamically set a table name (since it's assumed you already know what the table is going to be).

answered Mar 30, 2011 at 19:18

Andrew White

53.6k20 gold badges116 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jamie Keeling Over a year ago

So if I wanted to return a ResultSet containing all results from a table (SELECT * FROM person) it can't be done without implictly typing it as a string?

Andrew White Over a year ago

if your asking if you can generalize a select * from <table> with parametrized queries, the answer is no. You need to have a dedicated SQL for this or build your own generic version of select *.

Jamie Keeling Over a year ago

Yes that's what I meant :), that's a shame. I wanted to do it so I can update my JTable with the changes made since the last command was executed, do you know of a better way than refreshing the table?

Andrew White Over a year ago

sorry, but that question is way too application specific. You can probably find a library with some form of data polling built in but I think we've stayed far from the original question at that point.

ring bearer · Accepted Answer · 2011-03-30 19:35:18Z

2

If you want all rows from PERSON table, here is what you should do:

String select = "SELECT * FROM person";

PreparedStatement ps = connection.prepareStatement(select);

Variable binding does not dynamically bind table names as others mentioned above. If you have the table name coming in to your method as a variable, you may construct the whole query as below:

String select = "SELECT * FROM " + varTableName;
PreparedStatement ps = connection.prepareStatement(select);

Parameterized queries are for querying field names - not the table name!

answered Mar 30, 2011 at 19:35

ring bearer

20.9k8 gold badges62 silver badges73 bronze badges

1 Comment

Jamie Keeling Over a year ago

Thanks for the detailed example, made things a lot clearer :)

karakuricoder · Accepted Answer · 2011-03-30 19:21:47Z

1

Prepared statements are still SQL and need to be constructed with the appropriate where clause; i.e. where x = y. One of their advantages is they are parsed by the RDMS when first seen, rather than every time they are sent, which speeds up subsequent executions of the same query with different bind values.

answered Mar 30, 2011 at 19:21

karakuricoder

1,0758 silver badges8 bronze badges

Collectives™ on Stack Overflow

Error with simple Parameterized Query - Java/ SQL

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related