Java PreparedStatement preserve table name casing

Tags: , , , ,



I have this Java snippet, running inside a WildFly server backed by MariaDB:

var stmt = conn.prepareStatement("SELECT * FROM vehicles;");
ResultSet rs = stmt.executeQuery();

which gives me the following exception:

org.h2.jdbc.JdbcSQLException: Table "VEHICLES" not found; SQL statement:
SELECT * FROM vehicles; [42102-193]

So, apparentally, it decided to uppercase the table name, which I don’t want. How can I turn it off?

Answer

That is not possible, SQL dialects are – usually – case insensitive by default, but store the table name in uppercase (some dialects will store in lowercase). This means that if you use select * from vehicle, you’re actually selecting from a table called VEHICLE, and error messages will reflect that name, because the table vehicle is a different entity than the table VEHICLE.

If you want to have the original case reflected in the error message, you need to select from a table really called vehicle. To do that you will need to quote the object name in all your SQL statements, eg select * from "vehicle", or – in MariaDB and MySQL – select * from `vehicle`. Quoted object names retain their original case.



Source: stackoverflow