Hello Lukas, > > Ouch. You're completely correct: this query will not execute. > > Apparently there's a hard limit of 64KB per row, excluding BLOBs. > > The limit seems to also extends to SELECT resultset rows, which is kinda > > weird, but i suppose it is what it is.
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. I always knew about the table size limit but i didn't know that SELECTs are subject to the same limitation. 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. (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) It is nasty, indeed. Right now, I'm trying to cast all varchar bind > values to VARCHAR(4000), which is the max size supported by Oracle. > For the integration tests, that seems to be fine so far, but there > will be other corner cases, of course. But I don't think it's a show > stopper for jOOQ. As for deciding about productive databases, I don't > know... > Please CMIIW: Worst case scenario: someone with 16 varchar fields SELECTs (casted to varchar(4000) behind the scene) will inexplicably fail in jooq/firebird? 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 :) 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... 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. 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?) 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... Thanks, ts.
