Convert BLOB to CLOB

• Prashant Dahalkar
• Published: 20 Nov 2002

During a recent upgrade to a client's application, I had to update the database while keeping the data intact in...

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.