> This is exactly what i meant. For this particular type of use, how do you
> actually create the factory and which dialect to use?
You can choose a "similar" dialect.
> Please CMIIW, but i thought we only need this when we're doing stuff like
> "SELECT ?, ? from DUAL".
> Are you saying that we need this too for "normal" binding like "SELECT *
> from myTable where id = ?" ?
What's the definition of "normal"? Why is it more "normal" for a
database to infer / coerce types in one case, but not in another? I
can't take chances on assumptions without spending great effort in
investigation. Hence, if a database has weak inferral / coercion
capabilities, bind values are always cast no matter where they appear.
I'm open to solutions that are more precise, but only if they don't
overly complexify jOOQ's internals.
Over the time, I've found the incapability to infer / coerce types in
some corner cases a mere lack of functionality (or just plain bugs),
not an actual feature of a database. I have tried to get some
knowledge out of Fred Toussi from HSQLDB, or Thomas Müller from H2,
explaining when casting is needed and when it isn't, without success.
There are no general rules, only ad-hoc rules explaining the need or
non-need in specific cases. Across all databases, unfortunately, the
need for casting can be assumed to be quite arbitrary.
Now, you may argue that you'll never write SQL that is a corner case
and you wouldn't need bind value casting as you hadn't needed it so
far. That's probably true if you're in full control of the rendered
SQL. Yet, stuff like "SELECT ?, ? from DUAL" is not so uncommon. With
multi-record inserts (simulated by "INSERT INTO .. SELECT .. UNION ALL
SELECT ..") or with MERGE statements, it can be quite frequent. Other
corner cases appear when using functions, operators, etc.
> Because in the second case, the sql will render to something like "SELECT *
> from myTable where id = cast('ABCDE' as varchar(5))" instead of just "SELECT
> * from myTable where id = ?".
Yes, "SELECT * FROM myTable where id = cast(? as varchar(5))"
