ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Problem with SCOPE_IDENTITY() with SQL Server 2005 Express
Date Tue, 24 Apr 2007 20:03:45 GMT
Looks like it's using the same connection, but I wonder if there's a commit
between the two statements.  Just for fun, try writing some explicit
transaction control statements around the insert to see if that fixes it.

Jeff Butler


On 4/24/07, Fearghal O Maolcatha <fearghal.omaolcatha@gmail.com> wrote:
>
> I've run the query from SQL Server Studio Express query editor and it
> returns the id:
>
> insert
> into uidisk_group (free_capacity ) values ('100')
>
> select SCOPE_IDENTITY()
>
> However if I run the 'insert' and 'select SCOPE_IDENTITY' as separate
> queries then the 'select SCOPE_IDENTITY()' returns NULL
> I was looking through the log file containing the Ibatis debug output,
> I've noticed that the 'insert' and 'select SCOPE_IDENTITY' are issued as two
> separate Prepared Statements to the database. I'm just wondering whether
> these two statements are executed in a transaction? I've included an extract
> from the log file below:
>
> [main] DEBUG java.sql.Connection - {conn-100003} Preparing
> Statement:           insert into uidisk_group (free_capacity,
> logical_capacity, name)     values (?, ?, ?)
> [main] DEBUG java.sql.PreparedStatement - {pstm-100004} Executing
> Statement:           insert into uidisk_group ( free_capacity,
> logical_capacity, name)     values (?, ?, ?)
> [main] DEBUG java.sql.PreparedStatement - {pstm-100004} Parameters:
> ['100', null, null]
> [main] DEBUG java.sql.PreparedStatement - {pstm-100004} Types: [
> java.lang.String, null, null]
> [main] DEBUG java.sql.Connection - {conn-100003} Preparing
> Statement:        SELECT SCOPE_IDENTITY()
> [main] DEBUG java.sql.PreparedStatement - {pstm-100005} Executing
> Statement:        SELECT SCOPE_IDENTITY()
> [main] DEBUG java.sql.PreparedStatement - {pstm-100005} Parameters: []
> [main] DEBUG java.sql.PreparedStatement - {pstm-100005} Types: []
> [main] DEBUG java.sql.ResultSet - {rset-100006} ResultSet
> [main] DEBUG java.sql.ResultSet - {rset-100006} Header: []
> [main] DEBUG java.sql.ResultSet - {rset-100006} Result: [0]
> [main] DEBUG com.ibatis.common.jdbc.SimpleDataSource - Returned connection
> 18751079 to pool.
>
>
>  On 24/04/07, Larry Meadors <lmeadors@apache.org> wrote:
> >
> > Hm, that should work - have you run the queries from another
> > jdbc-based sql tool to see what you get?
> >
> > Larry
> >
> >
> > On 4/24/07, Fearghal O Maolcatha <fearghal.omaolcatha@gmail.com> wrote:
> > > I'm using <insert/> elements that have been generated by Abator. I'm
> > also
> > > using Abator to generate the DAOs. So basically I'm calling
> > > someDAO.insert(<objectToInsert>);
> > > I've included the insert element from the Ibatis SQL mapping file
> > below:
> > >
> > >   <insert id="abatorgenerated_insert" parameterClass="MyClass">
> > >     <!--
> > >       WARNING - This element is automatically generated by Abator for
> > > iBATIS, do not modify.
> > >       This element was generated on Tue Apr 24 11:36:48 BST 2007.
> > >     -->
> > >     insert into uidisk_group (free_capacity, logical_capacity, name)
> > >     values (#freeCapacity:NUMERIC#, #logicalCapacity:NUMERIC#,
> > >       #name:VARCHAR#)
> > >     <selectKey keyProperty="diskgrpId" resultClass=" java.lang.Integer
> > ">
> > >       SELECT  SCOPE_IDENTITY()
> > >     </selectKey>
> > >   </insert>
> > >
> > >
> > >
> > >
> > > On 24/04/07, Larry Meadors < lmeadors@apache.org> wrote:
> > > > Are you using <insert/ > elements and calling insert() to execute
> > them?
> > > >
> > > > Larry
> > > >
> > > >
> > > > On 4/24/07, Fearghal O Maolcatha < fearghal.omaolcatha@gmail.com>
> > wrote:
> > > > > I tried it with the latest driver from JTDS but still having the
> > same
> > > > > problem. I've included the beginning of my SqlMapConfig.xml file
> > below.
> > > Is
> > > > > there anything extra I need to set to get this to work?
> > > > >
> > > > >
> > > > >
> > > > > <settings useStatementNamespaces="true" />
> > > > >
> > > > >
> > > > >
> > > > > <!-- Configure a built-in transaction manager. If you're using
an
> > > > >
> > > > > app server, you probably want to use its transaction manager
> > > > >
> > > > > and a managed datasource -->
> > > > >
> > > > > <transactionManager type="JDBC" commitRequired="false">
> > > > >
> > > > > <dataSource type="SIMPLE">
> > > > >
> > > > > <property name="JDBC.Driver"
> > > > > value="net.sourceforge.jtds.jdbc.Driver"/>
> > > > >
> > > > > <property name=" JDBC.ConnectionURL "
> > > > > value="jdbc:jtds:sqlserver://localhost:3470/NSD_DB;"/>
> > > > >
> > > > > <property name="JDBC.Username" value="sa"/>
> > > > >
> > > > > <property name=" JDBC.Password" value="********"/>
> > > > >
> > > > > </dataSource>
> > > > >
> > > > > </transactionManager>
> > > > >
> > > > >
> > > > >
> > > > > On 24/04/07, Larry Meadors < lmeadors@apache.org> wrote:
> > > > > > Is that the M$ driver? I'd try JTDS and see if it works any
> > better.
> > > > > >
> > > > > > http://jtds.sourceforge.net/
> > > > > >
> > > > > > Larry
> > > > > >
> > > > > >
> > > > > > On 4/24/07, Fearghal O Maolcatha < fearghal.omaolcatha@gmail.com
> > >
> > > wrote:
> > > > > > >
> > > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I'm currently using Abator to generate the model, sql maps
and
> > DAOs
> > > for
> > > > > my
> > > > > > > SQL Server 2005 Express database. I have a number of tables
> > that
> > > have
> > > > > > > autogenerated keys and abator is adding the following xml
for
> > each
> > > of
> > > > > these
> > > > > > > keys for the inserts in the sql mapping files:
> > > > > > >
> > > > > > > <selectKey keyProperty="<beanId>" resultClass="
> > java.lang.Integer">
> > > > > > >
> > > > > > > SELECT SCOPE_IDENTITY()
> > > > > > >
> > > > > > > </selectKey>
> > > > > > >
> > > > > > > However when I try to use the autogenerated 'insert()'
method
> > of the
> > > > > DAO, it
> > > > > > > does not return the generated key and the java bean passed
as
> > > argument
> > > > > > > doesn't have it's id property updated. I've verified that
a
> > row is
> > > being
> > > > > > > inserted in the database the the 'insert()' method of the
DAO
> > is
> > > called
> > > > > but
> > > > > > > the <selectKey> part of the insert doesn't seem to
be working.
> > I had
> > > a
> > > > > look
> > > > > > > at previous threads and some had said that they were using
the
> > jtds
> > > jdbc
> > > > > > > driver so I had swiched to jtds-1.2 (I'm currently using
the
> > MS SQL
> > > > > Server
> > > > > > > 2005 JDBC driver, sqljdbc_1.1) but I still encountered
the
> > same
> > > problem.
> > > > > I
> > > > > > > can get the <selectKey> to work by replacing
> > 'SCOPE_IDENTITY()' with
> > > > > > > '@@IDENTITY AS' but would prefer not to have to alter the
> > generated
> > > xml
> > > > > and
> > > > > > > I think it is recommended not to use this as there can
be
> > problems
> > > if DB
> > > > > > > triggers are in use.
> > > > > > >
> > > > > > > I'm using JDK6, sqljdbc_1.1 as the JDBC driver,
> > Ibatis-2.3.0.677 and
> > > SQL
> > > > > > > Server Express 2005.
> > > > > > >
> > > > > > > Any help or suggestions on how to rectify the problem would
be
> > > > > appreciated.
> > > > > > >
> > > > > > > Regards,
> > > > > > >
> > > > > > > Fearghal.
> > > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
>

Mime
View raw message