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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/b4fedab64616f1e2067534c3b2a2c12db278a828.camel%40manticore-projects.com.
