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!

score 9 · Accepted Answer · answered Mar 30 '11 at 19:18

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 '11 at 19:18

Andrew White

52,720
19
113
137

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? – Jamie Keeling Mar 30 '11 at 19:24
if your asking if you can generalize a select * from with parametrized queries, the answer is no. You need to have a dedicated SQL for this or build your own generic version of select *.
– Andrew White Mar 30 '11 at 19:31
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? – Jamie Keeling Mar 30 '11 at 19:33
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. – Andrew White Mar 30 '11 at 19:40

score 2 · Answer 2 · answered Mar 30 '11 at 19:35

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!

Thanks for the detailed example, made things a lot clearer :) — Jamie Keeling, Mar 30 '11 at 19:39

score 1 · Answer 3 · answered Mar 30 '11 at 19:21

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.

Error with simple Parameterized Query - Java/ SQL

3 Answers3

Linked