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 Oracle sequence populated via Trigger
Date Tue, 09 May 2006 16:10:20 GMT
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