• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

i want to read a clob data from database and put it into another file

i want to read a clob data from database and put it into another file
0
Manikandan Thiagarajan
Asked:
Manikandan Thiagarajan
  • 8
  • 5
1 Solution
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
cold you please give me the code for that
0
 
for_yanCommented:
If you are using oracle (and I guess orher db also) one simple way is to use such query:
select to_char(cloib-field_name) from yourtable
Then you can get this value into java code as if you are reading the string.
String s = resultset.getString(1);

I'll show you alternatyive pure java way of reding clob when I reach my computer
0
 
for_yanCommented:
Another option you can find here:
read clob to string
 http://maclochlainn.wordpress.com/how-to-read-a-clob-through-jdbc/
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Manikandan ThiagarajanSenior consultantAuthor Commented:
i dont want to convert into stringin sql query
0
 
for_yanCommented:


That is how I do it in my real program.
It is still a little bit oracle specific
as it uses OracleResultSet class.
And if you defgine FileWriter instead of CharacteArrrayWrite you ca write it to file,
or write the resulting string to file

In the link which I provided above it gives more general way
   PreparedStatement pstmt = connection.prepareStatement(""select clob_filed from mytable where index = ?");
pstmt.setInt(1,1);

        ResultSet results=pstmt.executeQuery();

        if(results.next())
        {
           CLOB clob = ((OracleResultSet)results).getCLOB(1);

            if(clob == null) return null;

        Reader reader=clob.getCharacterStream();
        CharArrayWriter writer=new CharArrayWriter();
        int i=-1;

        while ( (i=reader.read())!=-1)
        {
        writer.write(i);
        }
    //    System.out.println("CLOB data read from Oracle: "+new String(writer.toCharArray()));
            String resultStringFromClob =    new String(writer.toCharArray());
        }

    }

Open in new window

0
 
for_yanCommented:

This is how they do it in that link - they have  a little bit
too many words there - but this is the essence - they use getClob(colimn) method
of the ResultSet

Statement stmt = conn.createStatement();

stmt.executeQuery("SELECT item_desc " +
"FROM   item " +
"WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring'" +
"AND    item_subtitle = 'Widescreen Edition'");


Clob clob = rset.getClob(1);
// Check that it is not null and read the character stream.
if (clob != null) {
Reader is = clob.getCharacterStream();
// Initialize local variables.
sb = new StringBuffer();
length = (int) clob.length();
// Check CLOB is not empty.
if (length > 0) {
// Initialize control structures to read stream.
buffer = new char[length];
count = 0;
// Read stream and append to StringBuffer.
try {
while ((count = is.read(buffer)) != -1)
sb.append(buffer);
}

Open in new window

0
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
           psmt = getPreparedStatementInstance(CommonpowerConstants.InwardRemittance_GET_MESSAGE_DETAIL_SQL);
            crs =psmt.executeQuery();

it would provide numberformat Exception when executing query

my query is select rawmessage rawMessage, mt_info_pk messageNumber from message_info;
0
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
raw message is clob field anything i want to change
0
 
for_yanCommented:
Please, post more of your code - what is the contents of that string you send to PrepStatement

and also poste the exception stackTrace
0
 
for_yanCommented:

add
System.out.println(
CommonpowerConstants.InwardRemittance_GET_MESSAGE_DETAIL_SQL);
before you execute query and post what it prints
0
 
Manikandan ThiagarajanSenior consultantAuthor Commented:
when i add tochar method in sql query  my program is working fine.

with out tochar method that is not working
0
 
for_yanCommented:
What databse you are using?

to_char may be Oracle specific

Rather let's understand what is the SQL statemet you use in
prepared satement - as I mentioned - printot in your
java program before you prepre statement
printout that   what you feed in to it:

add this to your cose before prearing statement and pots what it prints:
System.out.println(
CommonpowerConstants.InwardRemittance_GET_MESSAGE_DETAIL_SQL);
0
 
CEHJCommented:
You can make that a little more generic (see http://technojeeves.com/joomla/index.php/free/51-copying-streams )
Reader in = resultSet.getCharacterStream("columnName");
Writer out = new FileWriter("clob.txt");
IOUtils.copyReader(in, out);

Open in new window

0
 
for_yanCommented:


This is the full program which I now tested, which
reads CLOB form Oraclwe table and writes it to file

It does not use anything Oracle specific
should work with other databases.

import java.io.CharArrayWriter;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.*;

public class ReadFromClob {


    public static void main(String [] args){

        try{
            Class.forName ("oracle.jdbc.driver.OracleDriver");
     Connection conn =
                  DriverManager.getConnection ("jdbc:oracle:thin:@myhost.com:1521:sid","user","pswd");
            PreparedStatement pst = conn.prepareStatement("select t_clob from test_clob where int_key = ?");


    
          pst.setInt(1, 34528);


              ResultSet results=pst.executeQuery();

              if(results.next())
 
                  Clob clob = results.getClob(1);

                  if(clob == null) {
                      System.out.println("clob is null");
                  }

              Reader reader=clob.getCharacterStream();
                  FileWriter fw = new FileWriter("resultFromClob.txt");
            
              int i=-1;

              while ( (i=reader.read())!=-1)
              {
              fw.write(i);
              }
     
     
                  fw.close();
              }

          }
          catch(Exception ex){
              System.out.println("Error " + ex.toString());
              ex.printStackTrace();

          }


       
      }

    
}

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now