I have a code, which creates tables on the fly. These tables are used to store and get data for different processes.
Problem: Table names are being created with special characters like "&" and others. MySQL Allows table names with special characters. Not an issue with MySQL.
When i use DBUtils to query data from such table, i get an exception at the special character. If i were using regular jdbc, then i could have escaped those and could have processed data. But DButils not allowing to escape such characters.
I tried adding double quotes to table name and tried processing, however the problem still exists. Any advice??
java.sql.SQLException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near '-AO_I ( Datetime, L, ' at line 1
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491)
at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:404)
Removing all column names and detailed table name. The table name is "NRI-AO_I" The code adds data to hundreds of tables, but fails for few. these table names have special character in table name. Tables having "&" or "-" in their name fail.
It's a bad idea to use special characters in table names but if you have no other alternatives, Quote your ambiguous or "special" table names with a back tick. e.g:
select * from `NRI-AO_I`