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, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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/[email protected]/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
>
>
