Sequence Key not being passed to Insert

[SteveCMitchell]

When I call selectKey it either doesn't fire or the value is not passed to the id column of the insert statement.  The database complains that I cannot insert null (see bottom of post).  I had this experience at work with Oracle.  I used hqsqldb at home to recreate it (proving I could do it wrong twice).  This is the hsqldb syntax.  I chose not to use hsqldb IDENTITY so I could model the problem with Oracle.  What am I missing? 

*** THE MAPPING ***
<insert id="insertEmployee" parameterClass="com.byteworksinc.ibatis.core.Employee">
        <selectKey resultClass="int" keyProperty="id" >SELECT NEXT VALUE FOR EMPLOYEE_ID FROM EMPLOYEE</selectKey>
        INSERT INTO
        EMPLOYEE (EMPLOYEE_ID, ANNUAL_SALARY, HIRING_DATE,
        SSN, LAST_UPDATED_BY, LAST_UPDATE_DATE)
        VALUES (#id#, #annualSalary#, #hiringDate#,
        #ssn#, #lastUpdatedBy#, #lastUpdateDate#)
    </insert>

*** THE SEQUENCE ***
CREATE SEQUENCE EMPLOYEE_ID
    AS INTEGER
    START WITH 1
    INCREMENT BY 1;

*** THE ERROR ***
Cause: java.sql.SQLException: Try to insert null into a non-nullable column: column: EMPLOYEE_ID table: EMPLOYEE

[Kris Jenkins]

Hmm...I don't really know about HSQLDB, but I think the problem is the column alias.  I think you should try this (allowing for any HSQLDB syntax differences):

<selectKey resultClass="int" keyProperty="id" >SELECT NEXT VALUE AS id FOR EMPLOYEE_ID FROM EMPLOYEE</selectKey>

The difference being the 'AS id' part.

HTH,
Kris

[SteveCMitchell]

Part of the problem is solved, at least with hsqldb.  I did not have the correct hsqldb sequence statement format, and interestingly enough, the HSQLDB SEQUENCE NEXT VALUE only works if there is at least one row in the table!?  Once I changed the SQL and seeded the table with one row it worked.  Here is the corrected mapping.  Now, back to figuring out the issue with Oracle.

    <!-- Use Employee object (JavaBean) properties as parameters for insert. Each of the
    parameters in the #hash# symbols is a JavaBeans property. -->
    <insert id="insertEmployee" parameterClass="com.byteworksinc.ibatis.core.Employee">
        <selectKey resultClass="int" keyProperty="id" >SELECT NEXT VALUE FOR EMPLOYEE_ID, EMPLOYEE_ID FROM EMPLOYEE</selectKey>
        INSERT INTO
        EMPLOYEE (EMPLOYEE_ID, ANNUAL_SALARY, HIRING_DATE,
        SSN, LAST_UPDATED_BY, LAST_UPDATE_DATE)
        VALUES (#id#, #annualSalary#, #hiringDate#,
        #ssn#, #lastUpdatedBy#, #lastUpdateDate#)
    </insert>

[SteveCMitchell]

I'm looking for answers to the Oracle sequence problem.  The only difference I see between the client Oracle implementation and my working test hqsqldb implementation is the column data type.  On the Oracle implementation, iBatis makes me explicitly define the type (#id:NUMERIC#). My best guess is that iBatis is choking on the conversion, so id ends up null.

Below are three things.  1) getNextId() - for a work around I call this in the DAO insert method and manually call the setId() property on the object (SelectKey is commented out); 2) the insert mapping that I could not get to work (id was always null); and 3) the table definition. Any thoughts?

<!-- This works fine -->
<select id="getNextId" resultClass="java.lang.Integer">
        SELECT PRODUCT_REQ_CAT_ID.NEXTVAL as id FROM DUAL
    </select>

    <insert id="insertProductRequirementCategory" parameterClass="com.serff.prl.core.ProductRequirementCategory">
        <selectKey resultClass="int" keyProperty="id" >
            SELECT PRODUCT_REQ_CAT_ID.NEXTVAL as id FROM DUAL
        </selectKey>
        INSERT INTO
        PRODUCT_REQ_CAT (PRODUCT_REQ_CAT_ID, NAME, BUSINESS_LINE_ID,
        LAST_MODIFIED, USER_LAST_MODIFIED)
        VALUES (#id:NUMERIC#, #name#,
        #businessLineId:NUMERIC#, #lastModified#, #userLastModified#)
    </insert>

CREATE TABLE PRODUCT_REQ_CAT(
    PRODUCT_REQ_CAT_ID NUMBER(10) NOT NULL,
    NAME VARCHAR2(255),
    BUSINESS_LINE_ID NUMBER(10),
    LAST_MODIFIED DATE,
    USER_LAST_MODIFIED VARCHAR2(75),
    CONSTRAINT XPKPRODUCT_REQ_CAT PRIMARY KEY (PRODUCT_REQ_CAT_ID)
    CONSTRAINT XFK1PRODUCT FOREIGN KEY (BUSINESS_LINE_ID) REFERENCES BUSINESS_LINE
)
;

[Vic Cekvenich]

You might want to move this to user forum.
When I use Oracle, I call a proc I wrote that just gets the next key for the table.
hth,
.V