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 12:23:02 GMT
The trigger in the db is what is populating the PK attribute.  I don't need to issue the <selecttKey>
for the insert or I'm using two sequences instead of one.
If the database is creating it (Oracle), I'd like to get it, and place it back into the bean
and forward to the next page.
 
I do appreciate the response though!
 
I tried the route of a stored proc call but that is not working either.  I was unable to debug
this with Jdeveloper 10.1.3 as it crashes with an error, which I'm assuming is due to an invalid
entry in the sqlMapConfig.xml file, HOWEVER, this same workspace works fine if I'm not debugging:
 
Target URL -- http://127.0.0.1:8989/StrutsIbatis/index.jsp
06/05/10 08:04:06 Oracle Containers for J2EE 10g (10.1.3.0.0)  initialized
DEBUG [ApplicationServerThread-0] net.reumann.demo.persistence.BaseIbatisDao <clinit>-
Attempting to initialize SqlMap
ERROR [ApplicationServerThread-0] net.reumann.demo.persistence.BaseIbatisDao <clinit>-
Error intializing BaseIbatisDao 
com.ibatis.common.exception.NestedRuntimeException: Error occurred.  Cause: com.ibatis.common.xml.NodeletException:
Error parsing XML.  Cause: com.ibatis.common.exception.NestedRuntimeException: Error parsing
XPath '/sqlMapConfig/settings'.  Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
Caused by: com.ibatis.common.exception.NestedRuntimeException: Error parsing XPath '/sqlMapConfig/settings'.
 Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
Caused by: com.ibatis.common.xml.NodeletException: Error parsing XML.  Cause: com.ibatis.common.exception.NestedRuntimeException:
Error parsing XPath '/sqlMapConfig/settings'.  Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
Caused by: com.ibatis.common.exception.NestedRuntimeException: Error parsing XPath '/sqlMapConfig/settings'.
 Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
 at com.ibatis.sqlmap.engine.builder.xml.SqlMapConfigParser.parse(SqlMapConfigParser.java:81)
 at com.ibatis.sqlmap.client.SqlMapClientBuilder.buildSqlMapClient(SqlMapClientBuilder.java:62)
 at net.reumann.demo.persistence.BaseIbatisDao.<clinit>(BaseIbatisDao.java:20)
 at net.reumann.demo.service.EmployeeDaoService.<init>(EmployeeDaoService.java:12)
 at net.reumann.demo.action.EmployeeAction.<clinit>(EmployeeAction.java:22)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:494)
 at java.lang.Class.newInstance0(Class.java:350)
 at java.lang.Class.newInstance(Class.java:303)
 at org.apache.struts.util.RequestUtils.applicationInstance(RequestUtils.java:143)
 at org.apache.struts.action.RequestProcessor.processActionCreate(RequestProcessor.java:280)
 at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:218)
 at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
 at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:719)
 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:376)
 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:870)
 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:451)
 at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:218)
 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:119)
 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
 at java.lang.Thread.run(Thread.java:595)
Caused by: com.ibatis.common.xml.NodeletException: Error parsing XML.  Cause: com.ibatis.common.exception.NestedRuntimeException:
Error parsing XPath '/sqlMapConfig/settings'.  Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
Caused by: com.ibatis.common.exception.NestedRuntimeException: Error parsing XPath '/sqlMapConfig/settings'.
 Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
 at com.ibatis.common.xml.NodeletParser.parse(NodeletParser.java:53)
 at com.ibatis.sqlmap.engine.builder.xml.SqlMapConfigParser.parse(SqlMapConfigParser.java:78)
 ... 27 more
Caused by: com.ibatis.common.exception.NestedRuntimeException: Error parsing XPath '/sqlMapConfig/settings'.
 Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
 at com.ibatis.common.xml.NodeletParser.processNodelet(NodeletParser.java:114)
 at com.ibatis.common.xml.NodeletParser.process(NodeletParser.java:75)
 at com.ibatis.common.xml.NodeletParser.process(NodeletParser.java:93)
 at com.ibatis.common.xml.NodeletParser.parse(NodeletParser.java:63)
 at com.ibatis.common.xml.NodeletParser.parse(NodeletParser.java:51)
 ... 28 more
Caused by: java.lang.NullPointerException
 at java.lang.Class.forName0(Native Method)
 at java.lang.Class.forName(Class.java:164)
 at com.ibatis.sqlmap.engine.builder.xml.SqlMapConfigParser$3.process(SqlMapConfigParser.java:160)
 at com.ibatis.common.xml.NodeletParser.processNodelet(NodeletParser.java:112)
 ... 32 more

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