I am using Hibernate 2 against Gupta SQLBase. The SQLBase driver has a strange quirk: column names passed to ResultSet.getString(columnName) (or any of the get functions) must be upper case.

In my hibernate mapping document, I specify all database identifiers (table, column etc) in upper case. Simple selects and joins work just fine.

I am now running a slightly more complicated query involving aggregate functions and group by. Hibernate is generating select list aliases like:

select traceinven0_.PART_ID as x0_0_, traceinven0.TRACE_ID as x1_0_, ...

This query works fine if run against the database directly and if column values are retrieved by index (ie: ResultSet.getString(1)) instead of by name. Unfortunately, Hibernate uses the aliases that it has generated and the driver breaks (throws SQLException with message "column not found!").

Is there a way to change the generated aliases so that they are uppercase? Can I make this change via a config file, or do I have to build my own flavour of Hibernate after patching code somewhere? Can I change the alias generation strategy via a Dialect?

Aaron.

Hi
Can you post the entire query and Sql Trace

thanks

The query [hql] is:

select tr.id.partId, tr.id.traceId, tr.inventoryTransaction.location.id.warehouseId, sum(tr.qty) from TraceInventoryTransaction tr where tr.id.partId='770588' group by tr.id.partId, tr.id.traceId, tr.inventoryTransaction.location.id.warehouseId

The generated SQL is pretty much what you would expect, and I don't have it handy (sorry). A snippet is included in the original post. The only problem that I'm having is with the generated 'column' aliases (ie: the x0_0_ and x1_0_ strings).

The SQLBase driver is broken. If you ask for result set data by 'column' name (ie: in plain JDBC), you must ask for it in upper case. Hibernate must be retrieving data from the JDBC result set by column name/alias (rather than ordinal) -- which is sane. If the aliases generated were X0_0_ etc, then I would have no problems (ie: the broken driver would work). As it is, Hibernate asks for column values using the aliases from the query (eg: x0_0_) and the driver denies that such a column exists (for those who are interested, the driver does respond with meta data that matches the input SQL -- you just HAVE to ask for columns by uppercase name).

For simpler queries I have managed to get Hibernate to work with SQLBase. In my hibernate mapping document I specify all of my database identifiers in upper case (eg: TraceInventoryTransaction has property id.partId (composite primary key) with column="PART_ID".

So ... short of getting the driver fixed (which would be nice but doesn't seem likely) how do I convince Hibernate to use upper case aliases (eg X0_0_ instead of x0_0_).?

update: This doesn't seem to be an issue with newer versions of SQLBase.

Aaron.

Hi,

I'm looking at implementing NHibernate with SQLBase. Is there any code you can post that might help me out?

Thanks.

Mike.