Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Prepared Statements to set Table Name

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?

like image 258
Brandon Avatar asked Jul 30 '09 18:07

Brandon


People also ask

Can we create a table with PreparedStatement?

A JDBC PreparedStatement example to create a table in the database. A table 'employee' is created.

Can table name be parameterized?

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.

What is the use of PreparedStatement in JDBC?

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.

What is a PreparedStatement used for?

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 "?").


2 Answers

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 + "?]"; 
like image 152
camickr Avatar answered Oct 09 '22 00:10

camickr


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.

like image 40
mypetlion Avatar answered Oct 08 '22 22:10

mypetlion