ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <lmead...@apache.org>
Subject Re: Sybase selectKey problem
Date Fri, 07 Oct 2005 04:38:40 GMT
Hmm, what happens when you do the insert from a plain-jane sql client?

In this case, the exception is coming *BEFORE* the selectKey element
gets executed:

> Caused by: com.sybase.jdbc3.jdbc.SybSQLException: The column deploymentId
> in table Deployment does not allow null values.

The column does not allow null values...that tells us that the
generated key is not getting generated. :-)

This should work:

<insert id="createDeployment" parameterClass="deployment">
    INSERT INTO Deployment (
        environmentId, deploymentTypeId,
        userName, deploymentStatusId,
        deploymentTime,  threadCountOverride
    ) VALUES (
        #environmentId#, #deploymentTypeId#,
        #userName#, #deploymentStatusId#,
        #deploymentTime#, #threadCountOverride#)
    <selectKey resultClass="int" keyProperty="deploymentId">
        SELECT @@IDENTITY as value
    </selectKey>
</insert>

Someone else suggested "SELECT MAX(deploymentId)+1...", which is a
really bad idea. Threading and concurrency will kill you doing that.
Just say no.

I know with M$SQL, "select scope_identity()" is the prefered way to
get generated keys (because of potential trigger issues), and I am not
sure if that is an option on sybase..if it is, you may want to look at
that.

Larry


On 10/6/05, reubenf@sonic.net <reubenf@sonic.net> wrote:
> #1 -- I'm trying to use selectKey to create keys for me. DB is Sybase.
>
> My insert is as follows:
>
>     <insert id="createDeployment" parameterClass="deployment">
>         INSERT INTO Deployment (environmentId, deploymentTypeId, userName,
> deploymentStatusId, deploymentTime, threadCountOverride)
>         VALUES (#environmentId#, #deploymentTypeId#, #userName#,
>      #deploymentStatusId#, #deploymentTime#,
> #threadCountOverride#)       <selectKey resultClass="int"
> keyProperty="deploymentId">
>             SELECT @@IDENTITY AS deploymentId
>         </selectKey>
>     </insert>
>
> (BTW the dev guide does not mention keyProperty -- I found that in this
> thread:
> http://www.mail-archive.com/user-java@ibatis.apache.org/msg00044.html)
>
>
> I get the following error:
>
> Caused by: com.sybase.jdbc3.jdbc.SybSQLException: The column deploymentId
> in table Deployment does not allow null values.
>
>         at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:442)
>         at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:81)
>         at
> org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:319)
>         at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:181)
>         at
> org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:317)
>
>
> #2 -- Given that the insert/update statements are very much alike, is
> there a clean way to share the SQL between the two?
>
> Thanks
> Reuben
>
>

Mime
View raw message