-1

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Bence Csókás
  • 41
  • 7
  • Are you sure that this is not just logging it as upper case? – Scary Wombat Apr 13 '20 at 00:17
  • 1
    Have you tried putting the table name [in quotes](http://h2database.com/html/grammar.html#quoted_name)? So, in Java that would be `"SELECT * FROM \"vehicles\";"`. Also, you tag mariadb, but the error is h2 - is that relevant? – andrewJames Apr 13 '20 at 00:38
  • SQL is not case-sensitive. There is no problem here to solve. – user207421 Apr 13 '20 at 00:59
  • @user207421 - is that not implementation-specific? Keywords, I agree - it does not matter. But for [object names](https://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive) - doesn't that depend on the RDBMS and maybe also how it is configured? – andrewJames Apr 13 '20 at 01:04
  • @andrewjames True. What I'm really wondering is why the OP objects to an error message that only developers should ever see. – user207421 Apr 13 '20 at 01:11
  • @andrewjames You're right about H2, I never noticed that... Weird, I'll have to look into that. As for the others, see my comment on Mark's answer. MariaDB on *nix IS case-sensitive for object names. – Bence Csókás Apr 13 '20 at 12:01

1 Answers1

1

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • While this may be true for some DBMSes (like Oracle), I run a MariaDB instance on Linux, where keywords are case insensitive but object names - tables, databases, views etc. - are not. I defined them as lowercase, so they must be accessed as lowercase. I'll try the quoting though. ``` MariaDB [dbname]> select * from vehicles; Empty set (0.00 sec) MariaDB [dbname]> select * from Vehicles; ERROR 1146 (42S02): Table 'dbname.Vehicles' doesn't exist ``` – Bence Csókás Apr 13 '20 at 11:55
  • 1
    @BenceCsókás The error in your question is from H2, not from MySQL or MariaDB. – Mark Rotteveel Apr 13 '20 at 16:07