I've been involved in porting an app from one database to another (not so simple, I know.) In any case, its at least working code to start from. I'm seening a behaviour I didn't see before: Hibernate seems to want to prefix all table names with the login user under informix. Thus if I have login user X and Y, all selects under X show up as X.table, such as "Select column from X.table where..." I've played around with the default_schema setting, which seems to work mostly. The rub is in when it tries to go after things like systables, it winds up with the query select foo from X.systables, which isn't correct.

Generally then, whats the correct way to configure hiberate to work with the informix database? Is there some kind of incantation or rules I need to follow either in Hibernate configuration or Informix setup that wouldn't be obvious to an old Oracle hack? :-)


Hibernate version: 3.1.3

Name and version of the database you are using: Informix 9

It's been a couple years since I worked in Informix, but I do remember that behavior. However, I never touched system tables like SYSTABLES and wonder why you would need to. Are you doing a lot of metadata stuff? Normal application logic shouldn't need to go there.

rewrite your mappings so that whenever they refer to SYSTABLES they call it system.systables or whatever the owner user id of the system tables is? That was what I used to do when I went outside of the default schema for my user. I never tried this with Hibernate, however, but it seems to me that it might work.

That would be useful, except that I never reference systables. It's something that hibernate itself is doing internally (possibly to get some kind of key value? Don't know yet...)

Can you provide a mapping file or fragment? Hard to understand what you could be doing that would cause this.

Sure, first here's the error:
2006-06-14 16:02:03,250 ERROR [org.hibernate.util.JDBCExceptionReporter] The specified table (boomer_ent01.systables) is not in the database.
2006-06-14 16:02:03,250 ERROR [org.hibernate.util.JDBCExceptionReporter] ISAM error: no record found.
2006-06-14 16:02:03,250 ERROR [org.hibernate.AssertionFailure] an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session)
org.hibernate.AssertionFailure: null id in com.supplyscape.shared.dataobjects.XMLBatchJob entry (don't flush the Session after an exception occurs)
at org.hibernate.event.def.DefaultFlushEntityEventListener.checkId(DefaultFlushEntityEventListener.java:48)
at org.hibernate.event.def.DefaultFlushEntityEventListener.getValues(DefaultFlushEntityEventListener.java:150)
.
.
.


Here's the map on the table its using:
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.supplyscape.shared.dataobjects.XMLBatchJob"
table="xml_batch_job"
proxy="com.supplyscape.shared.dataobjects.XMLBatchJob"
dynamic-update="false" dynamic-insert="false"
select-before-update="false">
<id name="id" column="id" type="java.lang.Long">
<generator class="native" />
</id>

<property name="fileName" type="java.lang.String" update="false"
insert="true" access="property" column="fileName" not-null="true" />

<list name="XMLFragments" lazy="true" inverse="false"
cascade="none">
<key column="xml_batch_job_id" />
<index column="xml_fragment_index" />
<one-to-many
class="com.supplyscape.shared.dataobjects.XMLFragment" />
</list>

<property name="handle" type="java.lang.String" update="false"
insert="true" access="property" column="handle" not-null="true" />

<property name="created" type="java.util.Date" update="false"
insert="true" access="property" column="create_stamp"
not-null="true" />

<property name="lastModified" type="java.util.Date"
update="true" insert="true" access="property" column="modify_stamp"
not-null="true" />

<property name="dataFeedFrom" type="java.lang.String"
update="true" insert="true" access="property">
<column name="data_feed_from" not-null="true" />
</property>

<property name="dataFeedTo" type="java.lang.String"
update="true" insert="true" access="property">
<column name="data_feed_to" />
</property>

<property name="operatorHandle" type="java.lang.String"
update="true" insert="true" access="property">
<column name="operator_handle" />
</property>

</class>

</hibernate-mapping>



What it looks like from here is that it's trying to get the ID of the primary key back after an insert by using this function in the dialect:
public String getSequenceNextValString(String sequenceName) {
return "select " + getSelectSequenceNextValString( sequenceName ) + " from systables where tabid=1";
}

and for some reason its prefixing the systables part with the current login name which causes it to fail...
wierd.

Hmm. From what I remember about Informix, the typical ID-generation in Informix, this was handled with a data type of SERIAL, an Informix-specific construct. I wasn't using Hibernate when I used Informix, so I don't know how you get there in Hibernate. I would have thought the Infomix dialect would have handled this.

Are you sure you've specified the informix dialect in your master hibernate.cfg.xml?

On the other hand, I would have thought the documentation of <id> would show which id type to use with Informix, but I don't see it.

but if you search these forums on the keywords informix and serial (select "All" word matching) you get a number of links to threads that may tell you something. Be careful though, some are rather old and may no longer apply.