Greetings Andy.
On Sun, 2023-01-15 at 22:15 -0800, AndyGo wrote:
> I'm going to always use unquoted values for table/view names.
This is not RDBMS agnostic and will get you into big trouble when
switching from Oracle to Sybase or MS SQL Server or vice versa.
> I've used H2 successfully in different projects for over a decade.
> It's an awesome database with incredible breadth of use cases.
I full agree on that, however: who runs H2 in production should know
exactly what he is doing.
Your issue is a rather simple one: at least you got an error!
At the same time, between releases there is a residual risk that
perfect SQL:2016 compliant queries return wrong/different results
("WITH statement with parameters" since 210) -- and this is really
dangerous because you will suddenly get wrong information without a
chance of noticing.
Don't run software in production under scenarios, which you have not
well tested and understood in the lab before.
> 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
I do not understand your example: if your table was created as
"myFirstTable" (with explicit quotes), then of course myFirstTable ==
MYFIRSTTABLE == "MYFIRSTTABLE" won't match. Same for Oracle and MS SQL
Server or any other DB I know.
I do not see any H2 issue here, just the usual challenge of writing
platform agnostic SQL.
My advice: stick to the SQL:2016 standard as much as possible and
document well founded deviations (e.g. Oracle .nextval) when forced to
use it.
Cheers
Andreas
--
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 h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/b4fedab64616f1e2067534c3b2a2c12db278a828.camel%40manticore-projects.com.
