You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by re...@sonic.net on 2005/10/07 03:00:18 UTC
Sybase selectKey problem
#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
Re: Sybase selectKey problem
Posted by Larry Meadors <lm...@apache.org>.
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 <re...@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
>
>
Re: Sybase selectKey problem
Posted by re...@sonic.net.
BTW, this *does* work:
<insert id="createDeployment" parameterClass="deployment">
<selectKey resultClass="int" keyProperty="deploymentId">
SELECT MAX(deploymentId) + 1 FROM Deployment
</selectKey>
INSERT INTO Deployment (deploymentId, environmentId,
deploymentTypeId, userName, deploymentStatusId, deploymentTime,
threadCountOverride)
VALUES (#deploymentId#, #environmentId#, #deploymentTypeId#,
#userName#,
#deploymentStatusId#, #deploymentTime#,
#threadCountOverride#)
</insert>
BUT it directly contradicts the thread here:
http://www.mail-archive.com/user-java@ibatis.apache.org/msg00044.html
...in which the consensus that the selectKey operation for sybase is
identical to MSSQL in a) being after the insert and b) using @@IDENTITY.
Also it would seem to be counter to the examples in the docs, which don't
require that "deploymentId" be explicity passed to the insert.
What am I missing? Are these differences due to which JDBC driver I'm
using? (Jconn3)
#2 (below) still stands.
Thanks
> #2 -- Given that the insert/update statements are very much alike, is
> there a clean way to share the SQL between the two?
Re: Sybase selectKey problem
Posted by Zarar Siddiqi <za...@utoronto.ca>.
Try this as the <selectKey> query. I think you have to select the column as
"value". I'm assuming your "deployment" class has a "deploymentId"
property.
<selectKey resultClass="int" keyProperty="deploymentId">
SELECT @@IDENTITY AS value
</selectKey>
Zarar
----- Original Message -----
From: <re...@sonic.net>
To: <us...@ibatis.apache.org>
Sent: Thursday, October 06, 2005 9:00 PM
Subject: Sybase selectKey problem
> #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
>