Custom Type Handler --Done

Hi....
Can you tell me where i will put this tag

<typeHandler javaType="boolean"jdbcType="VARCHAR" callback="eg.ClassName" />

because i am confuse...and i try your method but it's still not working...can you give me a clue how to registeroutparamter (ref cursor oracle) because i always got error message "invalid column type"

and how i implement typehandlercallback...because i have read javadoc and there is a example how to use it...but it's different when i want to change it become ref cursor, and i so frustated...:(

Hi,

I'm trying to register a custom type handler to add support for the XMLTYPE and CLOB data types in Oracle 9i. However, I'm not sure what javaType and jdbcType I should be specifying in the config to support this.

Has anyone had any experience in doing this? Are there any examples of this?

Thanks in advance for you help!

For creating a Oracle CLOB handler, the actual connection object is needed (possibly through the Statement object) but the interface doesn't provide access to it right now... or am I off track here?

Thanks!

p.s It seems like a lot of people will be building their own CLOB, BLOB, and XMLTYPE custom handlers for Oracle (at least I am). Maybe reflection can be used to include these custom handlers with a future iBatis release. I would be willing to contribute :)

If you have ideas about CTH enhancements, please feel free to post them here.

Could you also post what you think an Oracle CLOB/BLOB CTH would look like? (even if it wouldn't currently compile).

Cheers,
Clinton

This is what I use to create an oracle CLOB. As far as I know, the createTemporary() only works for 9i and above oracle JDBC drivers and the expected object is an oracle.jdbc.OracleConnection even though the method API expects the plain JDBC Connection interface. For some reason, connection.getMetadata().getConnection() returns the actual physical connection object instead of the pooled interface (if you are using app server pooling).

oracle.sql.CLOB tempClob = null;
        try
        {
            // If the temporary CLOB has not yet been created, create new
            tempClob = oracle.sql.CLOB.createTemporary(conn.getMetaData().getConnection(), true, oracle.sql.CLOB.DURATION_SESSION);

            // Open the temporary CLOB in readwrite mode to enable writing
            tempClob.open(oracle.sql.CLOB.MODE_READWRITE);

            // Get the output stream to write
            Writer tempClobWriter = tempClob.getCharacterOutputStream();
            // Write the data into the temporary CLOB
            tempClobWriter.write(myClobString);

            // Flush and close the stream
            tempClobWriter.flush();
            tempClobWriter.close();

            // Close the temporary CLOB
            tempClob.close();
        }
        catch (Exception exp)
        {
            try
            {
                if (tempClob != null)
                    tempClob.freeTemporary();
            }
            catch (SQLException sqle)
            {
                log.error("Failed to free temporary clob.", sqle);
            }
            throw new DaoException("Failed to create a clob out of a string.", exp);
        }
        return tempClob;

Essentially, the DaoUtils.createClob()  method contains the code I posted above. At this point, I don't know what should be in valueOf() method yet:

public void setParameter(ParameterSetter parameterSetter, Object parameter) throws SQLException
    {
       
        Clob clob = DaoUtils.createClob(parameterSetter.getConnection(), (String) parameter);
        parameterSetter.setClob(clob);
    }

    public Object getResult(ResultGetter resultGetter) throws SQLException
    {
        log.info("Using custom Clob");
        Clob clob = resultGetter.getClob();
        return  (clob != null ? clob.getSubString(1, (int) clob.length()) : "");
    }

this is what I have in my sql map file

  <resultMap id="imageResult" class="image">
    <result property="id"            column="f_id" javaType="java.lang.Integer" jdbcType="NUMERIC"/>
    <result property="altText"       column="f_alt_text" javaType="java.lang.String" jdbcType="VARCHAR"/>
    <result property="contentType"   column="f_content_type" javaType="java.lang.String" jdbcType="VARCHAR"/>
    <result property="binaryData"    column="f_image" jdbcType="BLOB" callback="com.garbuz.jsite.db.OracleBlobTypeHandlerCallback" />
  </resultMap>

When I do that I get this error message

--------------------------------------------
Error while configuring DaoManager.  Cause: com.ibatis.sqlmap.client.SqlMapException: There was an error while building the SqlMap instance. 
--- The error occurred in config/sqlmap/Image.xml. 
--- The error occurred while loading the SQL Map resource. 
--- Cause: com.ibatis.sqlmap.client.SqlMapException: XML Parser Error.  Cause: org.xml.sax.SAXException: Error: URI=null Line=24: Attribute "callback" must be declared for element type "result".
Caused by: org.xml.sax.SAXException: Error: URI=null Line=24: Attribute "callback" must be declared for element type "result".
Caused by: com.ibatis.sqlmap.client.SqlMapException: XML Parser Error.  Cause: org.xml.sax.SAXException: Error: URI=null Line=24: Attribute "callback" must be declared for element type "result".
Caused by: org.xml.sax.SAXException: Error: URI=null Line=24: Attribute "callback" must be declared for element type "result".
--------------------------------------------

Is there something I am doing wrong?

Finally I was able to make it work. Here is the sql map file:

-------------------------------------------------
  <resultMap id="imageResult" class="image">
    <result property="id"            column="f_id" javaType="java.lang.Integer" jdbcType="NUMERIC"/>
    <result property="altText"       column="f_alt_text" javaType="java.lang.String" jdbcType="VARCHAR"/>
    <result property="contentType"   column="f_content_type" javaType="java.lang.String" jdbcType="VARCHAR"/>
    <result property="binaryData"    column="f_image" jdbcType="BLOB" typeHandler="com.garbuz.jsite.db.OracleBlobTypeHandlerCallback" />
  </resultMap>
-------------------------------------------------

This is the type hadler:

-------------------------------------------------
package com.garbuz.jsite.db;

import java.sql.*;
import  com.ibatis.sqlmap.client.extensions.*;

public class OracleBlobTypeHandlerCallback implements TypeHandlerCallback {

    public Object getResult(ResultGetter getter)
    throws SQLException {
        Blob blob = getter.getBlob();
        int size = (int)blob.length();
        return blob.getBytes(1,size);
    }
    
    public void setParameter(ParameterSetter setter, Object parameter)
    throws SQLException {
        byte[] bytes = (byte[])parameter;
        setter.setBytes(bytes);
    }
    
    public Object valueOf(String s){
        return s;
    }
}
-------------------------------------------------

It works for me just fine. I can upload and modify images.

Hi all,

Thanks for your help re. implementing a customTypeHandlerCallback that can handle Oracle CLOB's. I'm currently using the following config + implementation:

<typeHandler javaType="java.lang.String" jdbcType="CLOB" allback="com.emap.ibatis.CLOBTypeHandlerCallback"/>

public class CLOBTypeHandlerCallback implements TypeHandlerCallback {

    public Object getResult(ResultGetter getter) throws SQLException {
       
        CLOB clob = (CLOB) getter.getClob();
       
        Reader reader = null;
        StringBuffer result = new StringBuffer(1000);
       
        try {
           
            reader = clob.getCharacterStream();

            // Get optimal size to read/write data and initialze buffer
            char[] buffer = new char[clob.getBufferSize()];
            int read = 0;
            int buffLen = buffer.length;

            // Read from CLOB and return contents
            while((read = reader.read(buffer, 0, buffLen)) > 0 ) {
                result.append(buffer, 0, read);
            }

        } catch (IOException e1) {
           
            throw new SQLException("Failed to read CLOB: " + e1.getMessage());
             
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException ignore) {}
            }   
        }
       
        return result.toString();
       
    }

    public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
       
        String str = parameter.toString();
        if (str != null) {
            setter.setString(str);
        } else {
            setter.setNull(Types.CLOB);
        }
       
        System.out.println("CLOBTypeHandlerCallback: " + (parameter != null ? parameter.getClass() : null));
       
    }

    public Object valueOf(String s) {
       
        return s;

    }
   
}

Although I think this makes sense for reading Oracle CLOB's , it *may* not represent the most efficient method for writing clobs back to the DB. I've read Aye Thu's post but don't see where he gets his connection from... "conn.getMetaData().getConnection()"... Where is conn initialised?

I guess this isn't a performance issue when we are writing smaller fields, but if we end up writing very large documents back to the DB then we might run into problems.

Any ideas?

In addition to previous post:

My current concern is that we'll run into problems when setting large strings (greater than 32765 bytes), but I've yet to test this.

I'm looking into using setCharacterStream(...) as an alternative to setString(...). This should work with Oracle 9i according to the documentation.

...object and the index value. That way, support for Oracle NCHAR and NVARCHAR Unicode character values can be accomplished with a custom type handler.

// because it is necessary to add:
((OraclePreparedStatement) ps).setFormOfUse(i, OraclePreparedStatement.FORM_NCHAR);

//ahead of doing a:
ps.setString(...)

Is there a good way to do that?

I want to creat Clob,but i cann't get the connection(oracle.sql.CLOB.createTemporary(Connection connection, boolean b, int i),and the preparedStatement is private also(        org.springframework.jdbc.support.lob.OracleLobHandler.getLobCreator().setClobAsString(PreparedStatement ps, int parameterIndex, String content)),because the PreparedStatement.setCharacterStream(int parameterIndex,Reader reader,int length) if length > 4000 is error.

hello Aye Thu :
parameterSetter.getConnection(),?????Connection() Where , How get