GeeCON Prague 2014*
The moose likes JDBC and the fly likes How to convert Blob Type back to File Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to convert Blob Type back to File" Watch "How to convert Blob Type back to File" New topic
Author

How to convert Blob Type back to File

Darren Alexandria
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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

Hope this helps.


SCJP 5.0, SCWCD 1.4, SCBCD 1.3, SCDJWS 1.4
My Blog
Darren Alexandria
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
Does it look like this:



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
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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?



Which should I use?
a.



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


Thanks so much.
=)
Freddy Wong
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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

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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
Ooops! I got this error from the Stack Trace:

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

What should I do?
Freddy Wong
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
Mr. Freddy Wong,

This is my revised code:



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
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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

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

Joined: Aug 17, 2007
Posts: 185
Okay thanks so much.

I have changed:



to:



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
Ranch Hand

Joined: Sep 11, 2006
Posts: 959

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
Ranch Hand

Joined: Aug 17, 2007
Posts: 185
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to convert Blob Type back to File