Handling large objects - IBM DB2

JDBC provides different classes to support BLOB, CLOB, and XML values.

BLOB
A BLOB value can be used to store the binary large value. A BLOB object can be created and populated using the way shown in below

Creating a BLOB value

BLOB implementation in DB2 JDBC universal driver does not support any constructor function to create the BLOB object. So, it is not possible to create a BLOB object using a new operator.

The value of the BLOB type parameter can be set using the following PreparedStatement setter methods:

  • setBytes
  • SetBinaryStream

The BLOB value from the ResultSet object can be retrieved using the following ResultSet getter methods:

  • getBytes
  • getBinaryStream

CLOB and DBCLOB
A CLOB value can be used to store the character large value. A CLOB object can be created using a way similar to the way we created a BLOB object. Example 5-15 shows how to create a CLOB value.

Creating a CLOB value

The parameter value of type CLOB can be set using the following setter methods of PreparedStatement object:

  • setString
  • setAsciiStream
  • setUnicodeStream
  • setCharacterStream

The CLOB value can be retrieved from the result set using the following getter methods of ResultSet object:

  • getAsciiStream
  • getCharacterString
  • getString
  • getUnicodeStream

DBCLOB values are handled as CLOB objects in JDBC.

GRAPHICS and DBGRAPHICS
GRAPHICS and DBGRAPHICS are treated as String values in JDBC.

XML
XML is a new data type introduced in DB2 9. XML is different from the current data types supported in DB2, because XML is self-defined and stored in an tree structure manner in the database. For more details about the XML data type, refer to Chapter , “Application development with DB2 pureXML”. Currently, JDBC does not have any XML class to support the XML data type. XML data or columns can be mapped to any of the following:

  • String
  • byte
  • Stream
  • com.ibm.db2.DB2Xml class

com.ibm.db2.DB2Xml is a newly introduced class in JDBC universal driver. This class is a native class for the universal driver for handling the XML values. The following setXXX method can be used to set the value of the XML parameter in a PreparedStatement object:

  • setBytes()
  • setString()
  • setAsciiStream()
  • setBinaryStream()
  • setCharacterStream()
  • setClob()
  • setBlob()
  • setObject()

Supports DB2Xml, String, byte[], inputStream, Reader, CLOB, and BLOB as parameters The encoding conversion of the XML data depends on which setXXX method is used to bind the parameter value. The setAsciiStream, setCharacterStream, and setString methods convert the encoding to UTF-8 and send the XML value as an explicit external encoding; setByte and setBinaryStream assume that the internal encoding is correct and send the XML value with internal encoding.

An XML value can be retrieved from the ResultSet using the following getXXX methods:

  • getBytes()
  • getString()
  • getAsciiStream()
  • getBinaryStream()
  • getCharacterStream()
  • getObject()

The getObject method will retrieve the XML value as an DB2Xml class object. The XML values retrieved by these methods are externally encoded data and without explicit XML declaration. These methods cannot be called on the same XML row again. Trying to do that will throw an SQLException.

com.ibm.db2.jcc.DB2Xml Class
DB2 Universal JDBC driver introduces the DB2Xml class. The object of this class is returned to the application whenever the getObject method is used to retrieve the XML column value from the ResultSet object. This class define methods to convert the XML data to other Java data types, such as String, byte[], BinaryStream, and AsciiStream. These methods have the getDB2XXX format where XXX indicate the data type. This class also defines the methods which convert the XML data to the specified data and add the XML declaration tag. These methods have a format such as getDB2XmlXXX. Below shows how to retrieve an XML value as a DB2Xml object.

Retrieving XML value as a DB2Xml object

The metadata information regarding the XML data type column gives the data type of the column as java.sql.Types.OTHER because there is no XML data type defined in the JDBC standard.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics