Support localization in CLOB file in DB through Java

[Samir Banerjee]

I am facing problems with setting Latin-1 Supplement characters and Double byte characters in CLOB field in DB through Java code.
The part of the code I am using to set data in the DB with encoding is:

public void writeLOB(CallableStatement cstmt, int placeHolderIndex) throws SQLException {  
        CLOB clob = (CLOB)cstmt.getClob(placeHolderIndex);  
        writeCLOB(clob);  
    }  
  
private void writeCLOB(CLOB clob) throws SQLException {  
        Writer writer = null;  
        String encoding = super.getEncoding();  
  
        try {  
            if(encoding != null)  
              writer = new OutputStreamWriter(clob.setAsciiStream(0L), encoding);  
            else  
              writer = clob.setCharacterStream(0L);  
  
            writer.write(getJDBCObject().toString());  
            writer.flush();  
            writer.close();  
        }  
        catch(IOException ex) {  
            Logger.getInstance().logError(ex);  
            throw new SQLException("Error writing to LOB");  
        }  
}  

Here the callablestatement executes the following:
begin update test_clob tc set tc.id = " + "empty_clob() where tc.key1 = 1 returning tc.id into ? ; end;

Later the callablestatement and the placeholder position is passed to the upper method and they update the DB.


Code for retrieving the value :

public static String _getStringFromClob(Clob clob, String encoding) throws SQLException, DataObjectException  
    {  
     InputStream inputStream = null;  
     ByteArrayOutputStream outputStream = null;  
     try  
     {  
        int length = (int)clob.length();  
        byte[] array = new byte[length];  
        int inputStreamOffset = 0;  
        inputStream = clob.getAsciiStream();  
        outputStream = new ByteArrayOutputStream();  
  
        // we have to loop because length is in characters not bytes  
        while(true)  
        {  
           inputStreamOffset = inputStream.read(array, 0, length);  
           if(inputStreamOffset < 1)  
              break;   
           outputStream.write(array, 0, inputStreamOffset);  
        }  
        array = outputStream.toByteArray();       
        return new String(array, encoding);  
          
     }  
     catch(UnsupportedEncodingException e)  
     {  
        Logger.getInstance().logError(e);  
        throw new DataObjectException("Unsupported encoding");  
     }  
     catch(IOException e)  
     {  
        Logger.getInstance().logError(e);  
        throw new DataObjectException("Error reading from clob");  
     }  
     finally  
     {  
        try  
        {  
             if(inputStream != null)  
                inputStream.close();  
             if(outputStream != null)  
                outputStream.close();  
        }  
        catch(IOException e)  
        {  
            Logger.getInstance().logError(e);  
        }  
     }  
  }  

Here the select statement is fired and the returned CLOB filed is passed to the above function and it returns a String.

This is not working when I am writing "ä" into the CLOB filed with encoding "Cp1252".

Another problem is that the data in the CLOB is an xml and the name of the node are in English and the value of the node is to be localized (<AltText>Test ä</AltText> or <AltText>せんせい</AltText>).

Please can anyone help me with why this is not working or if there is any other way to do it.

Kindly help me out of this. Thanks in advance.

[Tom Reilly]

This is not working when I am writing "ä" into the CLOB filed with encoding "Cp1252".

Another problem is that the data in the CLOB is an xml and the name of the node are in English and the value of the node is to be localized (<AltText>Test ä</AltText> or <AltText>せんせい</AltText>).

What does "not working" mean? Please explain the "another problem".

[Samir Banerjee]

I am using the below Java code to set Latin-1 supplement character in the DB (NLS_CHARACTERSET = WE8ISO8859P1) but due to the DB not supporting these characters it is converting it into '?' or inverted '?'.
Is there a way to keep the the DB's NLS_CHARACTERSET as WE8ISO8859P1 and still support various languages (Single or Double Byte Unicode characters)?

   StringBuffer query = new StringBuffer("begin update test_clob tc set " );  
          query.append(" tc.id = empty_clob() where tc.key1 = 1");  
          query.append(" returning tc.id into ? ; end;") ;  
  
          Connection con = null ;  
          CallableStatement cstm = null ;  
          try{  
              Class.forName("oracle.jdbc.driver.OracleDriver");  
              con = DriverManager.getConnection("Connection URL","user","pass");  
              con.setAutoCommit(false);  
              cstm = con.prepareCall(query.toString());  
              cstm.registerOutParameter(1,Types.CLOB);  
              cstm.execute();  
              Clob clob = (Clob)cstm.getClob(1);  
              Writer writer = null;  
              String encoding = "Cp1252";  
              if(encoding != null)  
                  writer = new OutputStreamWriter(clob.setAsciiStream(0L), encoding);  
              else  
                  writer = clob.setCharacterStream(0L);  
                
              writer.write("<AltText>Test ä</AltText>");  
              writer.flush();  
              writer.close();  
              con.commit();  
                
              PreparedStatement pstmt = null;  
              ResultSet rs = null;  
              try {  
                  pstmt = con.prepareStatement("select id from test_clob where key1 = 1");  
                  rs = pstmt.executeQuery();  
                  if(rs.next()) {  
                      Clob tempC =  rs.getClob("id");  
                      _getStringFromClob(tempC, "Cp1253");  
                  }  
              }catch(Exception e){  
                  e.printStackTrace();  
              }  
              cstm.close();  
              con.close();  
          }catch(Exception e){  
              e.printStackTrace();  
          }  
    }  
      
      
    public void _getStringFromClob(Clob clob, String encoding){  
     InputStream inputStream = null;  
     ByteArrayOutputStream outputStream = null;  
     try  
     {  
        int length = (int)clob.length();  
        byte[] array = new byte[length];  
        int inputStreamOffset = 0;  
        inputStream = clob.getAsciiStream();  
        outputStream = new ByteArrayOutputStream();  
  
        // we have to loop because length is in characters not bytes  
        while(true)  
        {  
           inputStreamOffset = inputStream.read(array, 0, length);  
           if(inputStreamOffset < 1)  
              break;   
           outputStream.write(array, 0, inputStreamOffset);  
        }  
        array = outputStream.toByteArray();  
        System.out.println(new String(array, encoding));  
     }  
     catch(Exception e)  
     {  
        e.printStackTrace();  
     }  
     finally  
     {  
        try  
        {  
             if(inputStream != null)  
                inputStream.close();  
             if(outputStream != null)  
                outputStream.close();  
        }  
        catch(IOException e)  
        {  
           e.printStackTrace();  
        }  
     }  
  }  
 

Please let me know if there is an solution to this. Thanks in advance.