I'm trying to use prepared statements to set a table name to select data from, but I keep getting an error when I execute the query.
The error and sample code is displayed below.
[Microsoft][ODBC Microsoft Access Driver] Parameter 'Pa_RaM000' specified where a table name is required. private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [?]"; //?=date public Execute(String reportDate){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection(Display.DB_MERC); PreparedStatement st = conn.prepareStatement(query1); st.setString(1, reportDate); ResultSet rs = st.executeQuery();
Any thoughts on what might be causing this?
A JDBC PreparedStatement example to create a table in the database. A table 'employee' is created.
No, a parameterised query doesn't just drop the parameter values in to the query string, it supplies the RDBMS with the parameterised query and the parameters separately. But such a query can't have a table name or field name as a parameter.
Overview of Prepared StatementsIf you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead. The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created.
A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements basically work like this: Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?").
A table name can't be used as a parameter. It must be hard coded. So you can do something like:
private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [" + reportDate + "?]";
This is technically possible with a workaround, but very bad practice.
String sql = "IF ? = 99\n"; sql += "SELECT * FROM first_table\n"; sql += "ELSE\n"; sql += "SELECT * FROM second_table"; PreparedStatement ps = con.prepareStatement(sql);
And then when you want to select from first_table you set the parameter with
ps.setInt(1, 99);
Or if not, you set it to something else.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With