How to convert Blob Type back to File

[Darren Alexandria]

Good Day!

I have a database table which has a blob type column. My task is to retrieve it and save it in a directory. How can I convert those blob type entries to File (text files) type? Please give me some idea and advice.


Thanks.

[Freddy Wong]

You can use the getBinaryStream() from the ResultSet, then you can create a file from InputStream returned by the getBinaryStream().

Hope this helps.

[Darren Alexandria]

Thanks for your reply.

From what you said, After the InputStream, I will make a new file?

Is this something like File file = new File (); ??

Thanks again.

[Darren Alexandria]

Does it look like this:

ResultSet rs = stmt.executeQuery("SELECT textFile FROM blobFiles");
Rs.next();
InputStream is = rs.getBinaryStream(2);

What does the "2" in the .getBinaryStream(2) stand for? Does it mean that the column textFile is the 2nd column in the table blobFiles?

Please correct me if I am wrong.

Thanks so much.

Darren

[Freddy Wong]

The column is the column position of your SQL query, e.g.
SELECT a, b FROM MyTable.
Column 1 is a
Column 2 is b

After you can get the InputStream, what you need to do is to read it and then output the bytes that you've read into the OutputStream (in your case it can be something like FileOutputStream).

Hope this gives you some idea

[Darren Alexandria]

Thanks so much for your replies.

Here's the code that I have written just now:
Can you take a look at it and give some comments?

 
try {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT blobFile FROM 
 
tblBlobFiles");
    
        if (rs.next()) {
 
            Blob blob = rs.getBlob("blobFile");
            long blobLength = blob.length();
 
            int pos = 1;   // position is 1-based
            int len = 10;
            byte[] bytes = blob.getBytes(pos, len);
    
            InputStream is = blob.getBinaryStream();
            int b = is.read();
 
FileOutputStream os = new FileOutputStream("C:/UploadedFiles");
os.write(b);
 
File destFolder = new File("C:/UploadedFiles");
File fNew2 = new File(destFolder,is);
 

        }
    } catch (IOException e) {
    } catch (SQLException e) {
    }
 

Which should I use?
a.

FileOutputStream os = new FileOutputStream("C:/UploadedFiles");
os.write(b);

or this one (I used this in my Uploadfiles code)

File destFolder = new File("C:/UploadedFiles");
File fNew2 = new File(destFolder,is);

Thanks so much.
=)

[Freddy Wong]

Normally you will need to iterate the InputStream and then write each byte that you've read into the OutputStream.
For example:

int b = 0;
while ((b = is.read()) != -1)
{
    fos.write(b); // fos is the instance of FileOutputStream
}

For good performance, you may want to add some buffering techniques.

Sorry, I can't give you any good sample code because I don't have any Java installed in the computer that I'm currently using right now

[Darren Alexandria]

No problem. Just by mere replying, you are really helping me a lot. =)
Anyway, Okay I'll insert the code snippet you just gave and add buffers. I executed the program with the code that I have. It has no errors but when I check the specified directory, it is still empty. What have I missed?

Thanks so much.
=)

[Darren Alexandria]

Ooops! I got this error from the Stack Trace:

[Microsoft][SQLServer 2000 Driver for JDBC]Unsupported data conversion

What should I do?

[Freddy Wong]

Don't use the JDBC driver from Microsoft. Use the jTDS (open source JDBC driver).

[Darren Alexandria]

I haven't heard jTDS. Is it for free? Can it be used without license because the application that I am developing is for commercial use.

What do I have to do? Just download it's JAR and its okay?

[Darren Alexandria]

Hi!

I read the things about jtds and downloaded it already. Sounds good. The problem is, how do I know what property to choose from? Can you give me a sample URL?

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]


Thanks.

[Darren Alexandria]

I got the sample for the URL and its Driver, the error "Unsupported Data Conversion" is gone, but it has been replaced by: "Access Denied".
What should I do now?

[Freddy Wong]

jTDS can be used for commercial use.

Regarding the Access Denied exception, have you enabled the TCP/IP in your SQL Server? One more thing, it's good to post the stack trace, so that it's much easier to figure out what the problem is.

[Darren Alexandria]

here's the Stack Trace:

java.io.FileNotFoundException: C:\DownloadedFiles (Access is denied)
at java.io.FileOutputStream.open(Native Method)
at java.io.FileOutputStream.<init>(FileOutputStream.java:179)
at java.io.FileOutputStream.<init>(FileOutputStream.java:70)
at org.apache.jsp.viewBlob_jsp._jspService(viewBlob_jsp.java:104)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Thread.java:595)
java.io.FileNotFoundException: C:\DownloadedFiles (Access is denied)


Thanks so much.

[Freddy Wong]

From the stack trace, it doesn't look like the JDBC problem anymore. Maybe you can check whether the C:\DownloadedFiles has the right permission (writable).

Can you show us your code, so that it's easier to tell what went wrong?

[Darren Alexandria]

Mr. Freddy Wong,

This is my revised code:

 
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
 
try {
        
        Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
        connection = DriverManager.getConnection(connectionURL, "sa", "sa");
        statement = connection.createStatement();
        rs = statement.executeQuery("SELECT fileName,blobFile FROM tblBlobFiles");
    
    
        if (rs.next()) {
 
String filename = rs.getString(1);
           Blob blob = rs.getBlob(2);
          InputStream is = blob.getBinaryStream();
              FileOutputStream fos = new FileOutputStream("C:\\DownloadedFiles"+ "\\" + filename);

int b = 0;
while ((b = is.read()) != -1)
{
    fos.write(b); 
}
        }
    } catch (IOException e) 
    {
    e.getMessage (); e.printStackTrace(); 
System.out.println(e); 
    } 
    catch (SQLException e) 
    {
    e.getMessage (); e.printStackTrace(); 
System.out.println(e); 
    }

The improvement here is that there are no more "Access Denied" issues. I credit that to the wonderful people here in the ranch. Now, a file has been created in the C:\DownloadedFiles folder with the correct filename but the problem is that when I open the files, it only copies the first 4 lines from the original file. Why is that so? Also, there are 10 blob files in the database and I am supposed to download them all but there is only one file being downloaded.

Thanks so much.

Darren

[Freddy Wong]

If you want to get all the blob, you need to iterate the ResultSet, i.e.

    
while (rs.next())
{
    // do something
}

And don't forget to close the OutputStream after you finish using it. It's a good habit for doing so

[Darren Alexandria]

Okay thanks so much.

I have changed:

if (rs.next())
{
}

to:

rs.next();

the output is still the same. Only one file. And the contents are not complete. Still the first 4 lines are being copied.

[Freddy Wong]

No no... you must use while (rs.next()). If you only use rs.next(), it only only gets one record. In your case, if you have 10 records, iterating the ResultSet until it returns false will get the whole 10 records.

And for your another question on why you only get 4 lines, I'm not too sure why. Did you insert the file into the database correctly?

[Darren Alexandria]

Thanks. I have already changed the rs.next() to while (rs.next()).
The weird thing is that I retrieved 16 files and only file # 4,5,6,7 have the correct contents.

thanks again.

Darren

[ahmed elgrouney]

Hi ....

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class JDBCEx {

public static void main(String args[]) throws SQLException, FileNotFoundException, IOException{  
   



   try (Connection con = DriverManager.getConnection(  
           "jdbc:oracle:thin:@10.0.10.47:1521/intgtest","alexandria","alexandria")) {
       Statement stmt=con.createStatement();
     
       ResultSet rs=stmt.executeQuery("select CONTENTS from TRACK_MESSAGE_CONTENTS");
     while(rs.next())
       {
           

Blob blob;
           blob = rs.getBlob("CONTENTS");
byte[] bdata = blob.getBytes(1, (int) blob.length());
String text = new String(bdata);
           

           System.out.println(text);
       }
        con.close();


...... when i run this code, this error message appear "Invalid column type: getBLOB not implemented for class oracle.jdbc.driver.T4CNamedTypeAccessor"
anyone help me please ... thanks