ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Eric T. Blue" <ericblu...@gmail.com>
Subject Re: Oracle sequence populated via Trigger
Date Wed, 10 May 2006 00:40:30 GMT
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.EmployeeActioninsertOrUpdate-
insertOrUpdate
> DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeActioninsertOrUpdate-
insert
> DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSourcedebug- 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.SimpleDataSourcedebug- 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.SimpleDataSourcedebug- Returned
connection 538 to pool.
> DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeActioninsertOrUpdate-
insertOrUpdate
> DEBUG [ApplicationServerThread-0] net.reumann.demo.action.EmployeeActioninsertOrUpdate-
insert
> DEBUG [ApplicationServerThread-0] com.ibatis.common.jdbc.SimpleDataSourcedebug- 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.SimpleDataSourcedebug- 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