> I found a tracker entry talking about this <
> http://tracker.firebirdsql.org/browse/CORE-755 > but it hasn't been updated
> since 2009, so i don't think the limit will go away anytime soon.
Too bad...
> To be fair, though, we have several medium sized production systems using
> firebird (both in client/server and web-based environments) running for
> several years already and i have *never* ran into the limitation, even with
> large scale joins and report queries.
OK, good to know!
> (Note: after thinking about it, i suppose it's because internally we do
> impose a maximum length of 512 for VARCHARs. Anything longer than that goes
> to BLOBs subtype 1. The max length was decided arbitrarily though)
That might be a workaround, bind strings as BLOB ;-)
> Please CMIIW:
> Worst case scenario: someone with 16 varchar fields SELECTs (casted to
> varchar(4000) behind the scene) will inexplicably fail in jooq/firebird?
Right now, probably yes. Of course, if this is about bind values, jOOQ
could cast them to the actual string length. E.g.
// CAST(? AS VARCHAR(3)) for
stmt.setString("abc");
// CAST(? AS VARCHAR(10)) for
stmt.setString("1234567890");
I'll have to play around with that. If this works, then you're as
likely to break the 64k limit with jOOQ as with JDBC directly. I guess
Firebird users could live with that. I'll track this as #1727:
https://github.com/jOOQ/jOOQ/issues/1727
> This is a huge gotcha...
> For example, I have multiple reports/storedproc returning 31(days)*4(values)
> of varchar(100) that will fail right away :)
Yes, I guess there will have to be some more integration tests towards
that direction. Tracked as #1726:
https://github.com/jOOQ/jOOQ/issues/1726
Of course, in the worst case, you could always manually cast things
and write extensions to jOOQ, to work around this issue. But that
wouldn't make jOOQ a nice tool to use...
> Now i feel bad about requesting the support in the first place >_<, this
> seems to be rather a lot of effort for a workaround that might or might not
> be workable in the real world...
No no, trust me. DB2 NULLs were trickier to handle, and I had the odd
weird case to fix before... Imagine, you can only have ORDER BY along
with TOP clauses in SQL Server subselects. That means that jOOQ needs
to render TOP 100 PERCENT all the time. Another funky case was the
simulation of REPEAT() / LPAD() / RPAD() for SQLite. You can read
about it, here:
http://blog.jooq.org/2012/07/19/funky-string-function-simulation-in-sqlite/
> Just in case things don't really work out for firebird, i wonder what is
> jOOQ's lowest common denominator dialect?
> I saw something called SQLDialect.SQL99 but i think it was deprecated or not
> meant for users' code.
Yes, adding SQL99 that was quite ambitious. Today, that would
correspond to a common "average" dialect. The lowest common
denominator dialect would be stripped of so many features, you'd
immediately run away.
> What i was thinking was: for people with currently unsupported database,
> jOOQ can *still* serve (SQLDialect.NATIVE maybe?) as a thin wrapper on top
> of JDBC (without the code generation part and type safety part), but still
> offers:
> - (typesafe) binding
> - fetch into pojo (without annotation nor xml)
> - will run any sql you can throw at the database including storedproc
> http://www.jooq.org/manual/DSL/SQL/
> - (anything else i missed?)
Well, you can always run "plain SQL" through jOOQ. It would be
something like a more advanced Apache Commons DbUtils:
http://commons.apache.org/dbutils/
I mean, you could still do things like:
Result<Record> result = create.fetch("SELECT ?, ? FROM DUAL", 1, 2);
for (POJO pojo : result.into(POJO.class)) {
// ...
}
> I'm not sure if this is a direction you would like to take though; because
> it's almost like missing the point of jOOQ...
>From experience, the SQL rendering is unlikely to work nicely for
"unknown" dialects, unless you supply the SQL as plain SQL strings
yourself. The problem is that jOOQ's API has grown quite big.
Detecting which API elements work for your custom database (without
being annotated with @Support), is hard work. I personally discourage
such an approach, and I currently don't see a way to support this. The
14 currently supported databases are just too different.
