Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 16147 invoked from network); 11 May 2006 06:00:07 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 11 May 2006 06:00:07 -0000 Received: (qmail 25058 invoked by uid 500); 10 May 2006 18:46:43 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 25044 invoked by uid 500); 10 May 2006 18:46:43 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 25017 invoked by uid 99); 10 May 2006 18:46:43 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 May 2006 11:46:43 -0700 X-ASF-Spam-Status: No, hits=0.8 required=10.0 tests=DNS_FROM_RFC_ABUSE,HTML_MESSAGE,MAILTO_TO_SPAM_ADDR X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [206.224.21.48] (HELO enhbgsmtp02.state.pa.us) (206.224.21.48) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 10 May 2006 11:46:41 -0700 Received: from [206.224.21.25] ([206.224.21.25]) by enhbgsmtp02.state.pa.us with Microsoft SMTPSVC(5.0.2195.6713); Wed, 10 May 2006 14:46:19 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C67462.064AAE94" Subject: RE: Oracle sequence populated via Trigger Date: Wed, 10 May 2006 14:46:18 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Oracle sequence populated via Trigger Thread-Index: AcZzymfjXD6w7zj8Q7aLxdURt3VeNAAl2eBg From: "Givler, Eric" To: X-OriginalArrivalTime: 10 May 2006 18:46:19.0069 (UTC) FILETIME=[067696D0:01C67462] X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. ------_=_NextPart_001_01C67462.064AAE94 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable It turns out that the stored procedure worked fine, but was NEVER = committed. I was calling queryForObject instead of update, which I = guess must be called if your stored proc is doing the DML (insert): =20 Map map =3D new HashMap(); // Integer numEmployeeId =3D null; // map.put("employeeId", numEmployeeId ); map.put("employeeId", new Integer(-1) ); map.put("firstName", emp.getFirstName() ); map.put("lastName", emp.getLastName() ); map.put("age", emp.getAge() ); map.put("departmentId", = emp.getDepartment().getDepartmentId() ); =20 sqlMap.startTransaction(); // this does NOT call SessionScope.setCommitRequired so it = doesn't=20 // know to commit in TransactionManager.commitTransaction() // Object o =3D sqlMap.queryForObject(EMPLOYEE_INSERT_SP, = map); int results =3D sqlMap.update( EMPLOYEE_INSERT_SP, map ); System.out.println("results=3D" + results ); sqlMap.commitTransaction(); sqlMap.endTransaction(); =20 =20 System.out.println( "employeeId=3D" + = map.get("employeeId")); =20 -----Original Message----- From: Eric T. Blue [mailto:ericblue76@gmail.com] Sent: Tuesday, May 09, 2006 8:41 PM To: user-java@ibatis.apache.org Subject: Re: Oracle sequence populated via Trigger Here is a sample snippet of a resultMap I used with both Oracle 8i and = 9i. =20 select object_id.nextval as id from dual INSERT INTO table (id, value1, value2))=20 VALUES (#id#,#value1#,#value2#) =20 On 5/9/06, Givler, Eric < egivler@state.pa.us> wrote:=20 Does anyone have a working example of interacting with a table that = populates the PK value via a trigger (selects the sequence value from = dual in before insert trigger). The examples that I've seen read the = sequence via selectKey, which does work, but our trigger populates it.=20 If I was doing this in SQL, the statement would be: insert into emp( ename, department) values ( 'eric', 'research') returning empno into :empnum I'd even opt for being able to call a stored proc to do the insert, but = I'm having problems passing a null into it. I started with the = Struts-Ibatis demo from Rick Reumann's site. I added this to = employee.xml =20 {call insert_employee(?,?,?,?,?)}=20 I changed the EmployeeDaoIbatisImpl.java to: public void insert(Employee emp) { try { HashMap map =3D new HashMap(); Integer numEmployeeId =3D null;=20 map.put("employeeId", numEmployeeId ); map.put("firstName", emp.getFirstName() ); map.put("lastName", emp.getLastName() ); map.put ("age", emp.getAge() ); map.put("departmentId", = emp.getDepartment().getDepartmentId() ); //how do I get the resulting employeeId? Object o =3D sqlMap.queryForObject = ("Employee.insertViaStoredProc", map); // System.out.println("employeeid=3D" + employeeid ); } catch (SQLException e) { logger.error("Error inserting Employee", e);=20 } } an iBatis debug/trace shows: DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction = insertOrUpdate- insertOrUpdate DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction = insertOrUpdate- insert DEBUG [ApplicationServerThread-0] = com.ibatis.common.jdbc.SimpleDataSource debug- Checked out connection = 538 from pool. DEBUG [ApplicationServerThread-0] java.sql.Connection debug- = {conn-100009} Connection=20 DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010} PreparedStatement: {call insert_employee(?,?,?,?,?)} DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010} Parameters: [null, Eric, Givler, 40, 10]=20 DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010} Types: [null, java.lang.String, java.lang.String, = java.lang.Integer, java.lang.Integer] DEBUG [ApplicationServerThread-0] = com.ibatis.common.jdbc.SimpleDataSource debug- Returned connection 538 = to pool. The row does not get into the database. I'm assuming it is a problem = with the way the parameters are setup. If I switch the declaration to use: {call = insert_employee(#employeeId:NUMBER#,?,?,?,?)}=20 I get another error: DEBUG [ApplicationServerThread-0] = com.ibatis.common.jdbc.SimpleDataSource debug- Returned connection 538 = to pool. DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction = insertOrUpdate- insertOrUpdate DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeAction = insertOrUpdate- insert DEBUG [ApplicationServerThread-0] = com.ibatis.common.jdbc.SimpleDataSource debug- Checked out connection = 538 from pool.=20 DEBUG [ApplicationServerThread-0] java.sql.Connection debug- = {conn-100009} Connection DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010} PreparedStatement: {call = insert_employee(#employeeId:NUMBER#,#firstName:VARCHAR#,#lastName:VARCHAR= 2#,#age:NUMBER#,#departmentId:NUMBER#)}=20 DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010} Parameters: [null, eric, givler, 40, 10] DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010} Types: [null, java.lang.String, java.lang.String, = java.lang.Integer, java.lang.Integer] DEBUG [ApplicationServerThread-0] = com.ibatis.common.jdbc.SimpleDataSource debug- Returned connection 538 = to pool. ERROR [ApplicationServerThread-0] = net.reumann.demo.persistence.EmployeeDaoIbatisImpl insert- Error = inserting Employee com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in net/reumann/demo/persistence/Employee.xml. --- The error occurred while applying a parameter map.=20 --- Check the Employee.EmployeeMap. --- Check the statement (update procedure failed). --- Cause: java.sql.SQLException: ORA-06550: line 1, column 23: PLS-00103: Encountered the symbol "#" when expecting one of the = following:=20 ( ) - + case mod new not null others table avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall=20 merge year month DAY_ hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date I'd appreciate any tips on this one. Luckily, my hair is short, so it = is hard to pull out.=20 Thanks, Eric ------_=_NextPart_001_01C67462.064AAE94 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
It=20 turns out that the stored procedure worked fine, but was NEVER = committed. =20 I was calling queryForObject instead of update, which I guess must be = called if=20 your stored proc is doing the DML (insert):
 
       &nbs= p;   =20 Map map =3D new=20 HashMap();
          = ; =20 // Integer numEmployeeId =3D=20 null;
          &nbs= p; //=20 map.put("employeeId", numEmployeeId=20 );
            = map.put("employeeId", new Integer(-1)=20 );
            = map.put("firstName", emp.getFirstName()=20 );
            = map.put("lastName", emp.getLastName()=20 );
            = map.put("age", emp.getAge()=20 );
            = map.put("departmentId", emp.getDepartment().getDepartmentId()=20 );
            =
           =20 sqlMap.startTransaction();
       &= nbsp;   =20 // this does NOT call SessionScope.setCommitRequired so it doesn't=20
            = // know=20 to commit in=20 TransactionManager.commitTransaction()
     &= nbsp;     =20 // Object o =3D sqlMap.queryForObject(EMPLOYEE_INSERT_SP,=20 map);
          &nbs= p; int=20 results =3D sqlMap.update( EMPLOYEE_INSERT_SP, map=20 );
            = System.out.println("results=3D" + results=20 );
            = sqlMap.commitTransaction();
       =     =20 sqlMap.endTransaction();  
 
       &nbs= p;   =20 System.out.println( "employeeId=3D" + = map.get("employeeId"));
 
-----Original Message-----
From: Eric T. Blue=20 [mailto:ericblue76@gmail.com]
Sent: Tuesday, May 09, 2006 = 8:41=20 PM
To: user-java@ibatis.apache.org
Subject: Re: = Oracle=20 sequence populated via Trigger

Here is a sample = snippet of=20 a resultMap I used with both Oracle 8i and 9i.

<insert=20 id=3D"insertObject" = parameterClass=3D"com.yourcompany.Object">
<selectKey=20 keyProperty=3D"id" resultClass=3D"int">
   =20     select object_id.nextval as id from=20 dual
    = </selectKey>

    INSERT=20 INTO table
        (id, value1, = value2))=20
    = VALUES
       =20 (#id#,#value1#,#value2#)
    =
</insert>

On 5/9/06, Givler,=20 Eric <egivler@state.pa.us>=20 wrote:
Does=20 anyone have a working example of interacting with a table that = populates the=20 PK value via a trigger (selects the sequence value from dual in = before=20 insert trigger).  The examples that I've seen read the = sequence=20 via selectKey, which does work, but our trigger populates it. =

If I=20 was doing this in SQL, the statement would be:

insert into = emp(=20 ename, department) values ( 'eric', 'research')
returning empno = into=20 :empnum

I'd even opt for being able to call a stored proc to = do the=20 insert, but I'm having problems passing a null into it.  I = started=20 with the Struts-Ibatis demo from Rick Reumann's site.  I = added=20 this to employee.xml

    <parameterMap = id=3D"EmployeeMap" = class=3D"map">
      =20 <parameter property=3D"employeeId" jdbcType=3D"INTEGER"=20 javaType=3D"java.lang.Integer" mode=3D"INOUT" />=20
       <parameter = property=3D"firstName"=20 jdbcType=3D"VARCHAR" javaType=3D"java.lang.String" mode=3D"IN"=20 />
       <parameter=20 property=3D"lastName" jdbcType=3D"VARCHAR" javaType=3D" = java.lang.String"=20 mode=3D"IN" />
       = <parameter=20 property=3D"age" jdbcType=3D"INTEGER" javaType=3D"java.lang.Integer" = mode=3D"IN"=20 />
       <parameter=20 property=3D"departmentId" jdbcType=3D"INTEGER" javaType=3D" = java.lang.Integer"=20 mode=3D"IN"=20 = />
    </parameterMap>

  = ;  =20 <procedure id=3D"insertViaStoredProc" = parameterMap=3D"EmployeeMap"=20 >
        {call=20 insert_employee(?,?,?,?,?)}
    =20 </procedure>

I changed the EmployeeDaoIbatisImpl.java=20 to:

    public void insert(Employee emp)=20 = {
        try
  &= nbsp;     {
     &nb= sp;      HashMap=20 map =3D new=20 = HashMap();
          = ;  Integer=20 numEmployeeId =3D null;=20 =
           &nb= sp;map.put("employeeId",=20 numEmployeeId=20 = );
           &= nbsp;map.put("firstName",=20 emp.getFirstName()=20 = );
           &= nbsp;map.put("lastName",=20 emp.getLastName()=20 = );
           &= nbsp;map.put=20 ("age", emp.getAge()=20 = );
           &= nbsp;map.put("departmentId",=20 emp.getDepartment().getDepartmentId()=20 = );
           &= nbsp;//how=20 do I get the resulting=20 = employeeId?
         &nbs= p;  Object=20 o =3D sqlMap.queryForObject ("Employee.insertViaStoredProc",=20 = map);
          &nbs= p; //=20 System.out.println("employeeid=3D" + employeeid=20 );

        } catch=20 (SQLException e)=20 = {
           &n= bsp;logger.error("Error=20 inserting Employee", e);=20 =
        }
  &nbs= p; }

an=20 iBatis debug/trace shows:

DEBUG [ApplicationServerThread-0]=20 net.reumann.demo.action.EmployeeAction insertOrUpdate-=20 insertOrUpdate
DEBUG [ApplicationServerThread-0]=20 net.reumann.demo.action.EmployeeAction insertOrUpdate- = insert
DEBUG=20 [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource = debug-=20 Checked out connection 538 from pool.
DEBUG = [ApplicationServerThread-0]=20 java.sql.Connection debug- {conn-100009} Connection
DEBUG=20 [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010}=20 PreparedStatement: {call insert_employee(?,?,?,?,?)}
DEBUG=20 [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010}=20 Parameters: [null, Eric, Givler, 40, 10]
DEBUG=20 [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010}=20 Types: [null, java.lang.String, java.lang.String, java.lang.Integer, = java.lang.Integer]
DEBUG [ApplicationServerThread-0]=20 com.ibatis.common.jdbc.SimpleDataSource debug- Returned connection = 538 to=20 pool.

The row does not get into the database.  I'm = assuming=20 it is a problem with the way the parameters are setup.

If I = switch=20 the <procedure> declaration to use:  {call=20 insert_employee(#employeeId:NUMBER#,?,?,?,?)}

I get another=20 error:

DEBUG [ApplicationServerThread-0]=20 com.ibatis.common.jdbc.SimpleDataSource debug- Returned connection = 538 to=20 pool.
DEBUG [ApplicationServerThread-0]=20 net.reumann.demo.action.EmployeeAction insertOrUpdate-=20 insertOrUpdate
DEBUG [ApplicationServerThread-0]=20 net.reumann.demo.action.EmployeeAction insertOrUpdate- = insert
DEBUG=20 [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSource = debug-=20 Checked out connection 538 from pool.
DEBUG = [ApplicationServerThread-0]=20 java.sql.Connection debug- {conn-100009} Connection
DEBUG=20 [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010}=20 PreparedStatement: {call=20 = insert_employee(#employeeId:NUMBER#,#firstName:VARCHAR#,#lastName:VARCHAR= 2#,#age:NUMBER#,#departmentId:NUMBER#)}=20
DEBUG [ApplicationServerThread-0] java.sql.PreparedStatement = debug-=20 {pstm-100010} Parameters: [null, eric, givler, 40, 10]
DEBUG=20 [ApplicationServerThread-0] java.sql.PreparedStatement debug- = {pstm-100010}=20 Types: [null, java.lang.String, java.lang.String, java.lang.Integer, = java.lang.Integer]
DEBUG [ApplicationServerThread-0]=20 com.ibatis.common.jdbc.SimpleDataSource debug- Returned connection = 538 to=20 pool.
ERROR [ApplicationServerThread-0]=20 net.reumann.demo.persistence.EmployeeDaoIbatisImpl insert- Error = inserting=20 = Employee
com.ibatis.common.jdbc.exception.NestedSQLException:
--- = The=20 error occurred in net/reumann/demo/persistence/Employee.xml.
--- = The=20 error occurred while applying a parameter map.
--- Check the=20 Employee.EmployeeMap.
--- Check the statement (update procedure=20 failed).
--- Cause: java.sql.SQLException: ORA-06550: line 1, = column=20 23:
PLS-00103: Encountered the symbol "#" when expecting one of = the=20 following:

   ( ) - + case mod new not null others = <an=20 identifier>
   <a double-quoted = delimited-identifier>=20 <a bind variable>
   table avg count current = exists max=20 min prior sql stddev sum
   variance execute multiset = the both=20 leading trailing forall
   merge year month DAY_ hour = minute=20 second timezone_hour
   timezone_minute timezone_region = timezone_abbr time timestamp
   interval = date
  =20 <a string literal with character set specification>

I'd = appreciate any tips on this one.  Luckily, my hair is = short, so it=20 is hard to pull out.=20

Thanks,

Eric


------_=_NextPart_001_01C67462.064AAE94--