-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Oracle XMLTYPE for data > 4000
PostPosted: Thu Aug 19, 2004 10:06 am 
Newbie

Joined: Thu Aug 19, 2004 9:48 am
Posts: 4
I succesfully used the code described in the forum to create a custom UserType for the Oracle XMLType column. However this code seems to only work for data less than 4000.

I did some single stepping and found that net.sf.sql.Insert is used to generate the prepared statement for the SQL INSERT. The issue with the XMLTYPE is that the prepared statement must use xmltype(?) not '?'.

Code from net.sf.sql.Insert
Code:
public Insert addColumn(String columnName) {
  return addColumn(columnName, "?");
}


Code:
<hibernate-mapping>

    <class name="com.xerox.isrve.parm.bus.ReportRequest" table="REQPARM" dynamic-insert="true">
        <id name="id" type="long" column="REQUEST_ID">
            <generator class="sequence">
               <param name="sequence">GUIPARMS.REQ_SEQ</param>
           </generator>
        </id>
       
        <property name="reportId" column="REPORT_ID"/>
      <property name="xmlText" type="com.xerox.isrve.tools.util.HibernateXmlType" column="PARAMETERS"/>
      <property name="parentId" column="PARENT_REQUEST_ID"/>
      <property name="savedRequestFlag" column="SAVED_REQUEST_FLAG"/>
      <property name="savedRequestName" column="SAVED_REQUEST_NAME"/>
      <property name="scheduleId" column="SCHEDULE_ID"/>
      <property name="status" column="STATUS"/>
      <property name="createDate" column="CREATE_DATE"/>
      <property name="createdBy" column="CREATED_BY"/>
    </class>
   
</hibernate-mapping>

In my example the PARAMTERS column is an XMLTYPE. Hibernate generates the following prepared statement:

insert into REQPARM (REPORT_ID, PARAMETERS, SAVED_REQUEST_FLAG, STATUS, CREATE_DATE, CREATED_BY, REQUEST_ID) values (?, ?, ?, ?, ?, ?, ?)

The statement must be:
insert into REQPARM (REPORT_ID, PARAMETERS, SAVED_REQUEST_FLAG, STATUS, CREATE_DATE, CREATED_BY, REQUEST_ID) values (?, xmltype(?), ?, ?, ?, ?, ?)

My question is how can I configure hibernate to change the default prepared statement placeholder?

Thanks.


Top
 Profile  
 
 Post subject:
PostPosted: Fri May 19, 2006 11:30 am 
Newbie

Joined: Tue Apr 25, 2006 12:04 pm
Posts: 10
Location: Buenos Aires
could you solve this issue?

i have a similar problem!

thanks


Top
 Profile  
 
 Post subject: This is solved in Hibernate3
PostPosted: Fri May 19, 2006 12:10 pm 
Newbie

Joined: Thu Aug 19, 2004 9:48 am
Posts: 4
I could not solve this issue using Hibernate2. In Hiberante2 I actually used CLOB access techniques without defining the column in the *.hbm.xml file. I implemented an onSave() and onLoad() to manage this column.

Code:
   /**
     * called to save the report parameters into an Oracle XMLTYPE. This is
     * required since Hibernate does not support this yet.
     * 
     * @param obj saved request.
     */
    public synchronized void onSave(PersistentObject obj)
    {
        ReportRequest req = (ReportRequest) obj;
        JdbcTemplate jt = new JdbcTemplate(getDataSource());
        LobHandler lobHandler = new OracleLobHandler();

        // Must be set to the proper pool provider.
        jt.setNativeJdbcExtractor( new IsrveNativeJdbcExtractor() );
       
        try
        {
            String sql   = "UPDATE reqparm SET parameters = xmltype(?) " +
                           "WHERE request_id = " + req.getId().toString();
           
            SqlLobValue lob = new SqlLobValue( req.getXmlText(), lobHandler);
       
            log.debug("Saving XML parameters: " + sql);
           
            // Execute the update.
            jt.update(sql, new Object[] {lob}, new int[] {Types.CLOB} );
        }
        catch (DataAccessException ex)
        {
            req.addError("Unable to save Parameters", ex);
        }
    }
   
    /**
     * called after object loaded. Load the XML parameter list.
     */
    public synchronized void onLoad(PersistentObject obj)
    {
       if ( obj.getId() != null )
       {
         ReportRequest req = (ReportRequest) obj;
          JdbcTemplate jt = new JdbcTemplate(getDataSource());
                 
          try
          {
              String sql   = "SELECT parameters FROM reqparm WHERE request_id = " + req.getId().toString();
             
              log.debug("Loading XML Parameters: " + sql);
             
              oracle.sql.OPAQUE o = (oracle.sql.OPAQUE) jt.queryForObject(sql, oracle.sql.OPAQUE.class);
               
              XMLType poxml = XMLType.createXML(o);
             
              req.setXmlText(poxml.getStringVal());
          }
          catch (Exception ex)
          {
              req.addError("Unable to load Parameters", ex);
          }
       }
    }   


I would recommend using Hibernate3 since you can specify the SQL right in the *.hbm file. I have not tried this yet as the previous method meets my needs.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.