ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Givler, Eric" <egiv...@state.pa.us>
Subject RE: Oracle sequence populated via Trigger
Date Wed, 10 May 2006 18:46:18 GMT
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):
 
            Map map = new HashMap();
            // Integer numEmployeeId = 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() );
            
            sqlMap.startTransaction();
            // this does NOT call SessionScope.setCommitRequired so it doesn't 
            // know to commit in TransactionManager.commitTransaction()
            // Object o = sqlMap.queryForObject(EMPLOYEE_INSERT_SP, map);
            int results = sqlMap.update( EMPLOYEE_INSERT_SP, map );
            System.out.println("results=" + results );
            sqlMap.commitTransaction();
            sqlMap.endTransaction();   
 
            System.out.println( "employeeId=" + map.get("employeeId"));
 

-----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.

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

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


On 5/9/06, Givler, Eric < egivler@state.pa.us> wrote: 

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. 

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

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

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

I changed the EmployeeDaoIbatisImpl.java to:

    public void insert(Employee emp) {
        try
        {
            HashMap map = new HashMap();
            Integer numEmployeeId = null; 
            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 = sqlMap.queryForObject ("Employee.insertViaStoredProc", map);
            // System.out.println("employeeid=" + employeeid );

        } catch (SQLException e) {
            logger.error("Error inserting Employee", e); 
        }
    }

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 
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] 
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 <procedure> declaration to use:  {call insert_employee(#employeeId:NUMBER#,?,?,?,?)}


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. 
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:VARCHAR2#,#age:NUMBER#,#departmentId:NUMBER#)}

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. 
--- 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: 

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

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


Thanks,

Eric





Mime
View raw message