Convert BLOB to CLOB
This Java code helps convert BLOB data in a column into CLOB.
During a recent upgrade to a client's application, I had to update the database while keeping the data intact in...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
the tables. In the new version, I changed the datatype of a column from BLOB to CLOB, so I had to write a program that does the conversion. I didn't think that this could be done at the database level, so I wrote the following code. It has been tested on DB2 version 7.1, fixpack 5.
import java.io.* ; import java.sql.* ; class sampletocnb { public static void main ( String[] args ) throws Exception { BufferedReader br = new BufferedReader ( new InputStreamReader ( System.in ) ) ; Statement st = null ; PreparedStatement insertSampleC = null ; ResultSet rs = null ; ResultSetMetaData metaData = null; Connection con ; System.out.print ( "n Enter Data Source Name: " ) ; String str = br.readLine ( ) ; int val = 0 ; //InputStream is = null ; try{ Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ; str="jdbc:odbc:"+str ; con = DriverManager.getConnection(str, "CDSWEB", "CDSWEB" ) ; String selectSample = "SELECT OFFICEID,SMARTPADID,NOTE FROM SMARTPAD_1" ; st = con.createStatement ( ) ; insertSampleC = con.prepareStatement ( "UPDATE CDSWEB.SMARTPAD SET NOTE=? WHERE OFFICEID=? AND SMARTPADID=?" ) ; rs = st.executeQuery ( selectSample ) ; metaData = rs.getMetaData ( ) ; int count = 0; while ( rs.next ( ) ){ ++count; System.out.println("Record no "+count); int officeid = rs.getInt("OFFICEID"); int letterid = rs.getInt("SMARTPADID"); InputStream is = rs.getBinaryStream("NOTE") ;//BLOB if(is == null) System.out.println("InputStream Bytes is null"); InputStreamReader isReader = new InputStreamReader(is); BufferedReader blobData = new BufferedReader(isReader); StringBuffer buf = new StringBuffer(); while((str = blobData.readLine( )) != null) buf.append(str); int size = buf.length(); char charArr[] = new char[size]; for(int len = 0;len<charArr.length;len++) charArr[len] = buf.charAt(len); CharArrayReader chreader = new CharArrayReader(charArr); insertSampleC.setCharacterStream(1,chreader,charArr.length ) ;//CLOB insertSampleC.setInt(2,officeid); insertSampleC.setInt(3,letterid); System.out.println("Executing..."); int result = insertSampleC.executeUpdate() ; System.out.println("Result "+result); System.out.println("Record updated "+count); } } catch ( Exception e ){ System.out.println ( e.getMessage ( ) ) ; e.printStackTrace ( ) ; } finally{ if ( st != null ) st.close ( ) ; if ( insertSampleC != null ) insertSampleC.close ( ) ; } } }
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free DB2 tips and scripts.
- Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
- Best Web Links: DB2 tips, tutorials, and scripts from around the Web.