Thanks for the reply andrei. It's not a matter of trying to shoot myself in the foot. If I'm writing all SQL by hand then personally, I'm going to always use unquoted values for table/view names. However, various ORMs and query tools/frameworks have all kinds of different behaviors.
I've used H2 successfully in different projects for over a decade. It's an awesome database with incredible breadth of use cases. And yet, just the other day when creating a client demo, I received a series of error messages that didn't appear to make sense -- even accounting for case-sensitivity, the table name of the SQL statement matched the table name, yet H2 couldn't find the table? Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "MYFIRSTTABLE" not found (candidates are: "myFirstTable"); SQL statement: SELECT id, name FROM myFirstTable Eventually, the culprit was an ORM tool that quotes all of its object names, and then running queries with standard JDBC Prepared Statements that don't. However it was an embarrassing half-hour with the client trying to sort it all out with a database that I endorse but which the client was unfamiliar with. Obviously, this experience did not leave them with a great impression of H2. On Sunday, January 15, 2023 at 11:08:37 AM UTC-8 [email protected] wrote: > Hi Andy, > > I wonder, what are your expectations in such scenario? > If you are looking for a way to shoot yourself in the foot, I am sure H2 > will present multiple opportunities for that. > Does it really matter if it end up with "table not found" or will select > from one or the other table, at random? > Of course, you are always welcome to try... > > On Saturday, January 14, 2023 at 10:38:13 PM UTC-5 AndyGo wrote: > >> Thanks. Appreciate the quick and comprehensive response. Can you explain >> this a bit more: "It is possible to specify them in the default Regular >> compatibility mode " >> >> Just curious, since the settings can't persist... what would happen if, >> in default mode, I created MyTable and also "mytable" and then later, >> connected with CASE_INSENSITIVE_IDENTIFIERS and queried SELECT * FROM >> MYTABLE ? >> >> On Friday, January 13, 2023 at 2:12:31 AM UTC-8 Evgenij Ryazanov wrote: >> >>> Hello! >>> >>> These settings aren't related to each other. >>> >>> IGNORE_CASE is about *data types* and their *values*. It replaces CHARACTER >>> VARYING (VARCHAR) columns in DDL commands with VARCHAR_IGNORECASE data >>> type. This legacy setting should never be used, it is much better to >>> specify some case-insensitive database collation if you need it. >>> >>> DATABASE_TO_UPPER, DATABASE_TO_LOWER, and CASE_INSENSITIVE_IDENTIFIERS >>> settings change treatment of *identifiers*. H2 by default is fully >>> compliant with the SQL Standard here. It means all unquoted identifiers are >>> converted to upper case. id, ID, Id, "ID" (and also non-standard >>> MySQL-style `ID` and `id`) are equal to each other, but "id" is an >>> another identifier. Some other database systems historically process >>> identifiers in their own special way and sometimes this way also depends on >>> their environment. That's why these settings can be set separately from >>> compatibility modes. Values of these settings, however, aren't persisted >>> and you must always specify them with the same values in JDBC URL. >>> >>> It is possible to specify them in the default Regular compatibility mode >>> too, but, again, the default behavior is already correct and compliant with >>> the Standard. >>> >> -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ff1895b1-bb1c-46db-903f-51ca1fc0a2aan%40googlegroups.com.
