I have a web app using Hibernate/Spring with a MySQL database. Out of curiosity I tried changing the database to Firebird 1.5. I changed the necessary URL, driver, dialect, etc., in my 'hibernate.properties' class, but when I launched the web app after the build, where it should create all the tables etc (I use 'hibernate.hbm2ddl.auto=update'), it churned out error after error, typically SQL -104 errors - 'Token Unknown...'. I'd expected a couple of glitches but not a failure of this magnitude, which leads me to suspect there is something simple I've overlooked.

Has anyone out there got Firebird/Hibernate working and can throw me any tips as to what you need to be sure you've done?

TIA

yes, i'm using firebird ...
yes, everything works just perfect ...

no, i can't help you ... why? I don't have any information what Exceptions you get etc. ...

The first problem is this:

2005-07-11 12:34:58,687 WARN [net.sf.hibernate.cfg.SettingsFactory] - Could not obtain connection metadata
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted, cause:
java.util.NoSuchElementException: Could not create a validated object
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:851)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:140)
at net.sf.hibernate.connection.DBCPConnectionProvider.getConnection(DBCPConnectionProvider.java:41)
at net.sf.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84)
at net.sf.hibernate.cfg.Configuration.buildSettings(Configuration.java:1172)...

That doesn't occur if I set hibernate.statement_cache.size=0' and comment out all the 'hibernate.dbcp.*' properties in hibernate.properties. In that case, I get lots of stuff like this:

2005-07-11 12:39:42,000 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table admin_user (id numeric(18,0) not null, name varchar(50), email varchar(255) unique, last_login timestamp, password varchar(100), system_data smallint, created_by varchar(255), last_modified_by varchar(255), created_date timestamp, modified_date timestamp, primary key (id))
2005-07-11 12:39:42,000 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 120
password
2005-07-11 12:39:42,000 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table transaction (id numeric(18,0) not null, amount double precision, auth_code varchar(255), code varchar(255) not null, message varchar(255), salesOrder numeric(18,0) not null, valid smallint not null, system_data smallint, created_by varchar(255), last_modified_by varchar(255), created_date timestamp, modified_date timestamp, order_id numeric(18,0), primary key (id))
2005-07-11 12:39:42,000 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 14
transaction
2005-07-11 12:39:42,015 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table _sale_stock_link (sale_id numeric(18,0) not null, stock_item_id numeric(18,0) not null, primary key (sale_id, stock_item_id))
2005-07-11 12:39:42,015 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 14
_sale_stock_link
2005-07-11 12:39:42,015 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table department (id numeric(18,0) not null, discriminator varchar(255) not null, image longblob, shop_id numeric(18,0), position integer, name varchar(255), description blob sub_type 1, system_data smallint, created_by varchar(255), last_modified_by varchar(255), created_date timestamp, modified_date timestamp, primary key (id))
2005-07-11 12:39:42,015 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 129
position
2005-07-11 12:39:42,015 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table attribute_definition (id numeric(18,0) not null, option_list_id numeric(18,0), relevance varchar(255) not null, module varchar(255), attribute_type varchar(255), data_type varchar(255), mandatory smallint, length integer, product_type_id numeric(18,0) not null, name varchar(255), description blob sub_type 1, system_data smallint, created_by varchar(255), last_modified_by varchar(255), created_date timestamp, modified_date timestamp, primary key (id))
2005-07-11 12:39:42,015 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 220
length
2005-07-11 12:39:42,125 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table role (id numeric(18,0) not null, name varchar(100), system_data smallint, created_by varchar(255), last_modified_by varchar(255), created_date timestamp, modified_date timestamp, primary key (id))
2005-07-11 12:39:42,125 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 14
role
2005-07-11 12:39:42,125 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table option_list_item (id numeric(18,0) not null, option_list_id numeric(18,0) not null, code varchar(255), value varchar(255), description varchar(255), system_data smallint, created_by varchar(255), last_modified_by varchar(255), created_date timestamp, modified_date timestamp, primary key (id))
2005-07-11 12:39:42,125 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 117
value

Now, it seems to me that it might be tripping up on reserved words, but what about '_sale_stock_link'?

How can I get Firebird to use these table names?

Thanks.
'

OK, I resolved many of those errors by using back-ticks (``) around table and column names in the mapping files (or rather in the XDoclet comments which generate them). This causes Firebird not to confuse the names with reserved words.

I now have another bunch of problems. This is one kind:

2005-07-11 15:28:01,406 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table "admin_user" ("id" numeric(18,0) not null, "name" varchar(50), "email" varchar(255) unique, "last_login" timestamp, "password" varchar(100), "system_data" smallint, "created_by" varchar(255), "last_modified_by" varchar(255), "created_date" timestamp, "modified_date" timestamp, primary key ("id"))
2005-07-11 15:28:01,406 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544351. unsuccessful metadata update
key size too big for index RDB$33

This is another:

2005-07-11 15:28:01,437 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: alter table "category" add constraint FK302BCFE394D9528 foreign key ("department_id") references "department"
2005-07-11 15:28:01,437 ERROR [net.sf.hibernate.tool.hbm2ddl.SchemaUpdate] - GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -607
Invalid command
"REFERENCES table" without "(column)" requires PRIMARY KEY on referenced table

I'm not sure how to proceed with these. Any suggestions?

So does your 'department' table have a primary key defined?

Yes. I don't believe it's possible for it not to have, is it? From the docs:

"5.1.4. id

Mapped classes must declare the primary key column of the database table. "

Department has an ID field defined thus:

<id
name="id"
column="`id`"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="native">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Department.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

Well it appears to be claiming that the table (created with your DDL?) never specified a PK column.

You're quite right, it didn't. More importantly, the table itself doesn't exist. I checked with DBTools - only some of the tables have been created. So I'm wondering whether the 'hibernate.hbm2ddl.auto=update' is what's causing problems, because it is obviously getting its dependencies out of order (i.e. working out a foreign key using the Department table before it's created the Department table itself).

Is it better to manually create the database, or use SchemaExport, rather than rely on this auto update method? It works fine with MySQL, though.

Well, I've made a kind of progress anyway, but run into further problems. Here's what enabled me to get the database to be buildable by Maven:

1. Ensured that all table and column names in the mapping files were enclosed within backticks (``), so that Hibernate would use double-quotes around the names in the SQL it produced. This gets around the problem of reserved words (of which there seem to be an extraordinarily large number in Firebird).

2. Ensured that only standard data types were used (Firebird was choking on a LONGBLOB column which was set that way for MySQL).

3. Ensured that all String properties which were marked as unique (such as Customer.email) had a length defined, which was shorter than the default 255 which Hibernate uses for a VARCHAR. This was causing 'key size too big for index' errors. Setting a length of 80 sorted this out.

4. I gave up on using hibernate.hbm2ddl.auto=update because I kept getting 'object in use' errors, and built the database using an Ant task (in fact called from Maven), using the SQL generated by SchemaExport.

So, at this point, I've successfully built a Firebird database. However, as soon as I try to use it, I get problems again. My login is generating this error, because I have a property in the User class of 'password':

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 324
password

Yes, it's a reserved word, but I thought I'd got around that by ensuring that I'm using quoted identifiers. I.e., my create SQL looks like this:

create table "user" ("password" varchar(100), etc

and the mapping for the column looks like this:

<property
name="password"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="`password`"
length="100"
/>

So why is Hibernate still having trouble? The actual HQL which is causing the problem is this:

"from testing.om.User where email = ? and password = ?"

Do I have to tell Hibernate to use quotes in every query?

In HB2 you must use:




Just like in EVERY SINGLE example HQL query in the HB2 documentation.

Whoops! Am I right in thinking, then, that without doing this, it assumes a column name, not a property name?

Regarding your "Token unknown" errors, as you've discovered you are using reserved words in hibernate. Whether or not you've "solved" the problem with quotes, my strong advice is don't. Instead, just change the name of the table and/or column and move on.

Here is a list of the hibernate reserved words I know of. I just added some more today that I stumbled on, so by no means is this a complete list.

reservedWords.add("DATE");
reservedWords.add("LENGTH");
reservedWords.add("USER");
reservedWords.add("PASSWORD");
reservedWords.add("PROCEDURE");
reservedWords.add("TYPE");
reservedWords.add("INDEX");
reservedWords.add("LEVEL");
reservedWords.add("MESSAGE");
reservedWords.add("ORDER");
reservedWords.add("DESC");
reservedWords.add("ROLE");
reservedWords.add("PRIMARY");
reservedWords.add("VALUE");
reservedWords.add("TIMESTAMP");
reservedWords.add("GROUP");
reservedWords.add("POSITION");
reservedWords.add("YEAR");
reservedWords.add("TRANSACTI0N");

As for your "key size too big for index" errors, this is a firebird hassle. The error is the code in the create table: "email" varchar(255) unique

You're creating a varchar col of width 255 and making it unique. Firebird 1.5 won't do this. Change it to "email" varchar(253) unique and it should work. For some reason, firebird needs a few "chars of slack" for it to create a unique index. Same goes if you try and define a varchar(255) as a primary key. And it gets worse if you combine more than one column into a unique index or primary key. Then you need even less than 253 chars. I don't know the formula, but it is a PITA. Here's hoping the new firebird release will fix this.

The index length limit has been removed in Firebird 2.0 alpha1. I think the limit is now maxed to what a single page can contain.