> Just to add a bit of use case info here:
>
> Basically we have two approaches to generator/sequence use for this purpose;
> namely "one-generator-per-table" approach vs
> "one-generator-to-rule-them-all" approach. In case of surrogate keys,
> usually the preferred field type is BIGINT (a.k.a. int64). There are some
> interesting database->java mapping issues later but i suppose we'll catch
> this later during the tests.
So far, I haven't seen many issues related to BIGINTs. Around 70% of
the tests pass by now (using SQLite-generated classes)
> In most of master-detail CRUD scenarios involving surrogate primary keys, i
> prefer to get the generator value beforehand and assign them to the fields
> (both master and details) manually.
OK
>> > 2. SEQUENCEs are supported in firebird 2.0 onwards, but i'm not certain
>> Yes, they are. The latest version on GitHub uses them already
> I'm sorry, that sentence was cut-off >_<
> I was meaning to say "but i'm not certain they behave identically to SQLite
> or even Oracle's SEQUENCE since this is a syntactic sugar on top of
> GENERATORs".
They appear to work the same way, from a jOOQ perspective.
> I'm going to have to check the jaybird jdbc implementation for a definitive
> answer, but at the server level i think firebird is somewhere in between the
> two extremes.
> [...]
> From my limited testing at the jdbc level, the same thing holds true
> ...
> PreparedStatement ps = conn.prepareStatement("select * from mytable
> where id = ?");
> ps.setInt(1, 1);
> ps.setString(1, "1");
> ...
> Either of the parameter setting statement will work. Do you have a
> particular use case in mind?
Yes. Anything as simple as this might fail:
SELECT ? FROM RDB$DATABASE
SELECT ? + ? FROM RDB$DATABASE
Many JDBC drivers / databases can infer types at bind time. This seems
not to work with Firebird. jOOQ's usual workaround as mentioned in
that blog post is to execute something like this (as jOOQ usually has
the type information):
SELECT CAST(? AS INTEGER) FROM RDB$DATABASE
SELECT CAST(? AS INTEGER) + CAST(? AS INTEGER) FROM RDB$DATABASE
> That FAQ entry was talking about statement text size (the actual SQL string
> sent to the server) and the internal BLR size so i don't think we'll hit
> that limit anytime soon.
> Can you show me where you get "total of all involved varchar objects"
> reference from? I have never seen or run into this limit before. There *is*
> a 32K limit for varchar length for a single column, though.
Similar example as above. Try executing something like
SELECT
CAST(? AS VARCHAR(30000)),
CAST(? AS VARCHAR(30000)),
CAST(? AS VARCHAR(30000))
FROM
RDB$DATABASE;
This will fail with the same exception as mentioned before:
Caused by: org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -204
Implementation limit exceeded
block size exceeds implementation restriction
at
org.firebirdsql.gds.impl.jni.JniGDSImpl.native_isc_dsql_prepare(Native
Method)
at
org.firebirdsql.gds.impl.jni.BaseGDSImpl.iscDsqlPrepare(BaseGDSImpl.java:580)
at
org.firebirdsql.gds.impl.GDSHelper.prepareStatement(GDSHelper.java:190)
at
org.firebirdsql.jdbc.AbstractStatement.prepareFixedStatement(AbstractStatement.java:1440)
at
org.firebirdsql.jdbc.AbstractPreparedStatement.prepareFixedStatement(AbstractPreparedStatement.java:1279)
at
org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:135)
