Hello, > Yes, I've noticed that. I'll have to think about how to handle > identities in Firebird. I guess some inspiration can be taken from > Oracle, which doesn't support identity columns either. > Just to add a bit of use case info here:
1. 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. 2. 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. > 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". In fact, there is one issue that's causing me some headaches: Firebird > is one of those databases that requires about as much bind variable > casting as Derby and DB2 (see this post for details: > http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness/). > 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. It *is* rather strongly typed though. Basically if the server can convert the data safely, it will pass. Examples: - INSERT INTO T_LANGUAGE (ID, CD, DESCRIPTION, DESCRIPTION_ENGLISH) VALUES (1, 'EN', 'ENGLISH', 'ENGLISH');-> OK - INSERT INTO T_LANGUAGE (ID, CD, DESCRIPTION, DESCRIPTION_ENGLISH) VALUES ('2', 'FR', 'FRENCH', 'FRENCH'); -> OK (varchar casted to int) - INSERT INTO T_LANGUAGE (ID, CD, DESCRIPTION, DESCRIPTION_ENGLISH) VALUES (3, 'JP', 'JAPANESE', 12345); -> OK (int casted to varchar) - INSERT INTO T_LANGUAGE (ID, CD, DESCRIPTION, DESCRIPTION_ENGLISH) VALUES (4, 4, 'DE', 'DE'); -> OK (int casted to char(2)) - INSERT INTO T_LANGUAGE (ID, CD, DESCRIPTION, DESCRIPTION_ENGLISH) VALUES ('LOL', 'LOL', 'LOL', 'LOL'); -> FAIL (cannot cast this string to int) >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? At the same time, Firebird has a weird limit of around 64kb for > various block sizes, including the total of all involved varchar > objects: http://www.firebirdfaq.org/faq299/ > 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. Thanks, ts.
