[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-414) Informix dialect creates syntax errors in IDS 9.4

[<leg...@at...>]

The following comment has been added to this issue:

     Author: Finn McCann
    Created: Fri, 24 Oct 2003 4:57 AM
       Body:
In net.sf.hibernate.mapping.Table, you need to modify the following method:

public String sqlCreateString(Dialect dialect, Mapping p) throws HibernateException {

You will need to change the following:

			buf.append( col.getQuotedName(dialect) )
				.append(' ')
				.append( col.getSqlType(dialect, p) );
			
			if ( identityColumn && col.getQuotedName(dialect).equals(pkname) ) {
				buf.append(' ').append( dialect.getIdentityColumnString() );
			}

To something like:

			buf.append( col.getQuotedName(dialect) )
				.append(' ');

			// to support dialects that have their own identity data type
                  if ( !identityColumn || dialect.hasDataTypeInIdentityColumn() ) {
				buf.append( col.getSqlType(dialect, p) );
			}
			
			if ( identityColumn && col.getQuotedName(dialect).equals(pkname) ) {
				buf.append(' ').append( dialect.getIdentityColumnString() );
			}

and the net.sf.hibernate.dialect.Dialect should have a new method:

	/**
	 * Does this dialect have an Identity clause added to the data type or a 
       * completely seperate identity data type?
	 * @return boolean
	 */
	public boolean hasDataTypeInIdentityColumn() {
		return true;
	}

This means you can create a net.sf.hibernate.dialect.Informix9Dialect

package net.sf.hibernate.dialect;

/**
 * Informix9 dialect. This class is extends the Informix 7 dialect
 * <br>
 * Seems to work with Informix Dynamic Server Version 9.4, 
 * Informix JDBC driver version 2.21JC5.
 * @author Finn McCann
 */
public class Informix9Dialect extends InformixDialect {
	
	public Informix9Dialect() {
		super();
	}	

	/**
	 * @see net.sf.hibernate.dialect.Dialect#getIdentityColumnString()
	 */
	public String getIdentityColumnString() throws MappingException {
		return "SERIAL8 NOT NULL";
	}

	/**
	 * @see net.sf.hibernate.dialect.Dialect#hasDataTypeInIdentityColumn()
	 * Informix9 has it's own SERIAL8 datatype.
	 * @return boolean
	 */
	public boolean hasDataTypeInIdentityColumn() {
		return false;
	}

	/**
	 * The syntax used to add a foreign key constraint to a table. 
       * Informix constraint name must be at the end.
	 * @return String
	 */
	public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable, String[] primaryKey) {
		return new StringBuffer(30)
		.append(" add constraint ")
		.append(" foreign key (")
		.append( StringHelper.join(StringHelper.COMMA_SPACE, foreignKey) )
		.append(") references ")
		.append(referencedTable)
		.append(" constraint ")
		.append(constraintName)
		.toString();
	}
	
	/**
	 * The syntax used to add a primary key constraint to a table.
       * Informix constraint name must be at the end.
	 * @return String
	 */
	public String getAddPrimaryKeyConstraintString(String constraintName) {
		return " add constraint primary key constraint " + constraintName + " ";
	}
}

---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-414


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-414
    Summary: Informix dialect creates syntax errors in IDS 9.4
       Type: Bug

     Status: Unassigned
   Priority: Major

    Project: Hibernate2
   Versions:
             2.0.2

   Assignee: 
   Reporter: Finn McCann

    Created: Tue, 21 Oct 2003 11:01 AM
    Updated: Tue, 21 Oct 2003 11:01 AM
Environment: Hibernate2 on JDK1.4 on Win2000, talking via Informix JDBC 2.21.JC5 driver to Informix IDS 9.4 on another Windows 2000 box.

Description:
Having tested Informix dialect in Hibernate2 against Informix IDS v9.4, we found a couple of issues: 

1.  Serial ids:

<id name="id" column="ID" length="32" unsaved-value="null">
   <generator class="uuid.hex"/>
</id>

creates a data type:

INT8 SERIAL   

instead of:

SERIAL8

The "INT8 SERIAL" causes a syntax error in IDS 9.4.

2. Foreign Key constraint names:

<set name="upliftRates" lazy="true" cascade="all" order-by="EFFECTIVE_DATE desc">
        <key column="NETWORK_ID" length="32"/>
        <one-to-many class="com.capetechnologies.multinet.domain.UpliftRate"/>
</set>

creates this ALTER TABLE statement:

alter table MULTINET_TAX_RATES add constraint FKE1700D046924EC foreign key (NETWORK_ID) references MULTINET_NETWORK

instead of:

alter table MULTINET_TAX_RATES add constraint foreign key (NETWORK_ID) references MULTINET_NETWORK constraint FKE1700D046924EC

Having "CONSTRAINT <constraint name>" within the statement instead of at the end causes a syntax error.

This also applies to adding UNIQUE CONSTRAINTs.



---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira