aspose file tools*
The moose likes JDBC and the fly likes Convert java.sql.BLOB to String Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Convert java.sql.BLOB to String" Watch "Convert java.sql.BLOB to String" New topic
Author

Convert java.sql.BLOB to String

sumitha sudhakar
Greenhorn

Joined: Aug 09, 2010
Posts: 21
Hi,

Our application is having 60000 record in a BLOB specific table.
My requirement is to export the entire table data to text files .
When I tried converting BLOB to sting and writing to file, it took almost 3 mins for 100 records, if so, will take so much of time in exporting data from BLOB specific table.
I am using the following logic,

byte[] bdata = blob.getBytes(1, (int)blob.length());
String data1 = new String(bdata);
buffer.append(data1);

Can anyone please tell me how can I speed up the operation.

Thanks,
Sumitha.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

I don't know how big the blobs are, but converting the entire blolb into a string in memory may not be the best solution.
maybe used a bufferedWriter http://docs.oracle.com/javase/6/docs/api/java/io/BufferedWriter.html and do it in chunks.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3438
    
  47

Please note that BLOB is an acronym which means Binary Large Object. That means it can contain bytes not representable in text from. Your data should probably came from a CLOB, which is character version of the above and takes care of text-related issues (encoding).

If you do have to use a BLOB and are sure it actually contains character data (which means there data model has some design flaws), you should at least specify the encoding when converting the byte array to the String. If you follow Wendy's advice and use streams instead - probably a good thing to do, you'd have to pay attention to the encoding too.

However, LOBs can contain tiny or huge amount of data. How big is the text file after exporting 100 records? Perhaps the speed is actually adequate. If not, it is possible that your JDBC driver does not provide good performance for LOBs; you might try using your database unload tools instead (if there are any).
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

Martin Vajsar wrote:Please note that BLOB is an acronym which means Binary Large Object. That means it can contain bytes not representable in text from. Your data should probably came from a CLOB, which is character version of the above and takes care of text-related issues (encoding).

If you do have to use a BLOB and are sure it actually contains character data (which means there data model has some design flaws), you should at least specify the encoding when converting the byte array to the String. If you follow Wendy's advice and use streams instead - probably a good thing to do, you'd have to pay attention to the encoding too.

However, LOBs can contain tiny or huge amount of data. How big is the text file after exporting 100 records? Perhaps the speed is actually adequate. If not, it is possible that your JDBC driver does not provide good performance for LOBs; you might try using your database unload tools instead (if there are any).


Some database had blobs before clobs were thought of, so it is possible the column is a blob, but would be clob if the column was created today
sumitha sudhakar
Greenhorn

Joined: Aug 09, 2010
Posts: 21
Thanks a lot for your responses.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Convert java.sql.BLOB to String
 
Similar Threads
how do i make a field mandatory in my WSDL using axis2
Using BLOB in java
Pattern & Matcher - how to get any combination?
How to retrieve blob data in java
How to store & retrieve a video file from database?