Integration

Sorry. I'm using Toplink Essentials v2b58.

Sequence objects supported only for Oracle and (as of the end of last year) PostgreSQL. For other platforms GenerationType.SEQUENCE results in Identity being used.

To workaround define the tables with Identity.

To fix the problem implement sequence objects support for the database platform you are using: it should be easy to do following the example of PostgreSQLPlatform.
The methods that should be implemented are (copy from PostgreSQLPlatform):

    /**
     *  INTERNAL:
     *  Indicates whether the platform supports sequence objects.
     *  This method is to be used *ONLY* by sequencing classes
     */
    public boolean supportsSequenceObjects() {
        return true;
    }

    /**
     * INTERNAL:
     * Returns query used to read value generated by sequence object (like Oracle sequence).
     * This method is called when sequence object NativeSequence is connected,
     * the returned query used until the sequence is disconnected.
     * If the platform supportsSequenceObjects then (at least) one of buildSelectQueryForSequenceObject
     * methods should return non-null query.
     */
    public ValueReadQuery buildSelectQueryForSequenceObject(String seqName, Integer size) {
        return new ValueReadQuery("select nextval(\'"  + getQualifiedSequenceName(seqName) + "\')");
    }

    /**
     * INTERNAL:
     *  Prepend sequence name with table qualifier (if any)
     */
    protected String getQualifiedSequenceName(String seqName) {
        if (getTableQualifier().equals("")) {
            return seqName;
        } else {
            return getTableQualifier() + "." + seqName;
        }
    }

    public Writer buildSequenceObjectCreationWriter(Writer writer, String fullSeqName, int increment, int start) throws IOException {
        writer.write("CREATE SEQUENCE ");
        writer.write(fullSeqName);
        if (increment != 1) {
            writer.write(" INCREMENT BY " + increment);
        }
        writer.write(" START WITH " + start);
        return writer;
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects.
     * Returns sql used to delete sequence object from the database.
     */
    public Writer buildSequenceObjectDeletionWriter(Writer writer, String fullSeqName) throws IOException {
        writer.write("DROP SEQUENCE ");
        writer.write(fullSeqName);
        return writer;
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects
     * and isAlterSequenceObjectSupported returns true.
     * Returns sql used to alter sequence object's increment in the database.
     */
    public Writer buildSequenceObjectAlterIncrementWriter(Writer writer, String fullSeqName, int increment) throws IOException {
        writer.write("ALTER SEQUENCE ");
        writer.write(fullSeqName);
        writer.write(" INCREMENT BY " + increment);
        return writer;
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects
     * and it's possible to alter sequence object's increment in the database.
     */
    public boolean isAlterSequenceObjectSupported() {
        return true;
    }

I can do this for Informix, no problem. But I'm not too pleased to have a fix applied for myself on a source level checkout, so only if it is accepted back into the code base. I can live the the table version for a while.

At the moment I'm using the EclipseLink version of Toplink, because Oracle's Toplink is not compiled as much. On what codebase should I make the change?

Eclipselink.

Ohhhhkay... How do I get to the sources of EclipseLink? Or do you want me to cold-code it based on the provided example?

The source is included in each of the milestone and nightly builds available from the download page at http://www.eclipse.org/eclipselink/.

The source is managed in the Eclipse Foundation's SVN repository (http://dev.eclipse.org/svnroot/technology/org.eclipse.persistence/trunk/).

To contribute any enhancements to EclipseLink you should open a bug and attach any proposed changes. We'll then review it and either add it to the source base or sommunicate back through the bug any issues or concerns with the contribution.

Doug

One thing I should add is that TopLink/EclipseLink was designed to be extensible. One aspect of this is allowing end users to provide their own platform implementation to customize how we interact with their database. Writing a new platform or modifying one was intended.

We do greatly appreciate any contributions that make EclipseLink better for all users.

Doug

1. Get Eclipselink source: either download it from http://www.eclipse.org/projects/project_summary.php?projectid=technology.eclipselink
or access the subversion repository: http://dev.eclipse.org/svnroot/technology/org.eclipse.persistence/trunk/

2. Report a new bug at https://bugs.eclipse.org/bugs
This will require to create bugzilla account
Then select Technology - EPS (EclipseLink)

3. Attach the patch to the bug - or just paste the added methods and notify that the bug fix is ready for review either eclipselink-dev@eclipse.org or just post into this thread.

4. The fix will be reviewed and (if it's all good) will be checked in by one of the Eclipselink committers.

I created a custom class that extends InformixPlatform.

Examining:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls264.htm
It turned out that the syntax is 100% identical to the Postgres version, so I copied the Postgres code (also provided in this post) into the custom class.

Then PersistenceUnitProperties.TARGET_DATABASE was used to specify this custom class (full class name) and I altered the BM to use sequences.

But alas...

Internal Exception: java.sql.SQLException: Cannot insert a null into column (relation.relationnr)

Am I missing something?

If you provide such a great persistance library, then who am I not to contribute.

I found the culprit (don't ask). Anyhow, my current challenge:

[EPS Warning]: 2008.04.23 16:50:10.453--UnitOfWork(3900654)--java.lang.NullPointerException
at org.eclipse.persistence.sequencing.QuerySequence.createArguments(QuerySequence.java:340)
at org.eclipse.persistence.sequencing.QuerySequence.select(QuerySequence.java:296)
at org.eclipse.persistence.sequencing.QuerySequence.updateAndSelectSequence(QuerySequence.java:246)
at org.eclipse.persistence.sequencing.StandardSequence.getGeneratedVector(StandardSequence.java:72)
at org.eclipse.persistence.sequencing.Sequence.getGeneratedVector(Sequence.java:267)
at org.eclipse.persistence.internal.sequencing.SequencingManager$Preallocation_NoTransaction_State.getNextValue(SequencingManager.java:596)
at org.eclipse.persistence.internal.sequencing.SequencingManager.getNextValue(SequencingManager.java:884)
at org.eclipse.persistence.internal.sequencing.ClientSessionSequencing.getNextValue(ClientSessionSequencing.java:86)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.assignSequenceNumber(ObjectBuilder.java:244)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.assignSequenceNumber(UnitOfWorkImpl.java:376)
at org.eclipse.persistence.internal.jpa.RepeatableWriteUnitOfWork.mergeCloneWithReferences(RepeatableWriteUnitOfWork.java:250)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.mergeCloneWithReferences(UnitOfWorkImpl.java:3174)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.mergeInternal(EntityManagerImpl.java:235)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.merge(EntityManagerImpl.java:211)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Looks like

    public ValueReadQuery buildSelectQueryForSequenceObject(String seqName, Integer size) 

is not implemented in your platform?

No it was not and after that it works, so here it is for your scrutany.

    /**
     *  INTERNAL:
     *  Indicates whether the platform supports sequence objects.
     *  This method is to be used *ONLY* by sequencing classes
     */
    public boolean supportsSequenceObjects() {
        return true;
    }
    
    /**
     * INTERNAL:
     * Returns query used to read value generated by sequence object (like Oracle sequence).
     * This method is called when sequence object NativeSequence is connected,
     * the returned query used until the sequence is disconnected.
     * If the platform supportsSequenceObjects then (at least) one of buildSelectQueryForSequenceObject
     * methods should return non-null query.
     */
    public ValueReadQuery buildSelectQueryForSequenceObject(String seqName, Integer size) {
        return new ValueReadQuery("select " + seqName + ".nextval from systables where tabid = 1");
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects.
     * Returns sql used to create sequence object in the database.
     */
    public Writer buildSequenceObjectCreationWriter(Writer writer, String fullSeqName, int increment, int start) throws IOException {
        writer.write("CREATE SEQUENCE ");
        writer.write(fullSeqName);
        if (increment != 1) {
            writer.write(" INCREMENT BY " + increment);
        }
        writer.write(" START WITH " + start);
        return writer;
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects.
     * Returns sql used to delete sequence object from the database.
     */
    public Writer buildSequenceObjectDeletionWriter(Writer writer, String fullSeqName) throws IOException {
        writer.write("DROP SEQUENCE ");
        writer.write(fullSeqName);
        return writer;
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects
     * and it's possible to alter sequence object's increment in the database.
     */
    public boolean isAlterSequenceObjectSupported() {
        return true;
    }

    /**
     * INTERNAL:
     * Override this method if the platform supports sequence objects
     * and isAlterSequenceObjectSupported returns true.
     * Returns sql used to alter sequence object's increment in the database.
     */
    public Writer buildSequenceObjectAlterIncrementWriter(Writer writer, String fullSeqName, int increment) throws IOException {
        writer.write("ALTER SEQUENCE ");
        writer.write(fullSeqName);
        writer.write(" INCREMENT BY " + increment);
        return writer;
    }

There was one minor problem: the method which selects the next value of a sequence is not identical to Postgres; Informix requires the "nextval" inside a select, insert or update statement, but a select always needs a "from" and a table.
Now, there is no oracle alike "dual" table where to run the select against, so I resorted to run it against the always present system table. Permissions on this table cannot be revoked, so it should always run. And it should even run quickly, because it is not a real table, but an in memory thing.

One question: the int parameter in buildSelectQueryForSequenceObject is not documented. Neither the Oracle implementation nor the Postgres seem to bother with it. What is it for?

One suggestion: since the whole sequence-related methods have one gate (the supportsSequenceObjects method being true), wouldn't it be better to have the dummy methods in org.eclipse.persistence.internal.databaseaccess.DatabasePlatform throw an exception telling the coder the method is not implemented? It sure beats the NPE I got.

Tom

Eclipse has strict rules for accepting the code: the person contributing the code should open a bug and put the code there (see my prev. prev. posting).

The second parameter is sequence preallocation size. Usually it's ignored: sequence object already has it's own increment size. The parameter is there for exotic cases - such as calling a stored procedure that uses increment value provided by this parameter.

I agree about the exception, could you please log an enhancement request for that.

Thank you very much for spending your time and effort making eclipselink a better product.

Will do, however note the end of your point 3.

My bad, what I was trying to say was:
Attach the patch to the bug - or just paste the added methods into the bug.
Notify that the bug fix is ready for review either eclipselink-dev@eclipse.org or just post the notification into this thread.

Patch: Bug 228681

Enhancement: Bug 228682

Checked in the fix for Bug 228681 into Eclipselink. Thanks a lot for your contribution.

One last thing. I've working with Toplink for about 10 years ago in a banking project. Back then TL was already a feature complete product with weaving, a workbench and all the requiredmapping and extentions.

I assumed that all the derivates that we're seeing now (TLE, JPA-RI, EL) basically are wrappers on top of the core TL. But since the Informix platform is not implemented complety, that is not the case?

Many things were added for JPA, weaving being one of them. I doubt that 10 years ago either weaving or Informix sequence objects were around.

Now that you mention it. Indeed, IFX did not support sequences and weaving was only available in the static postcompile approach.