Upload PDF file to mysql BLOB by using java.sql.PreparedStatement without corruption

Question

I tried to uplad a pdf file using java.sql.PreparedStatement to mysql Blob field using the following code.

        File inFile = new File("Path+BLOCK.pdf");
        byte[] b = new byte[(int)inFile.length()];

        PreparedStatement psmnt = (PreparedStatement) 
        con.prepareStatement("INSERT  INTO 
                        2012DOC (SRNO,DOCUMENT) 
                       VALUES  (?,?)"
                      );   //con is java.sql.Connection object
        psmnt.setString(1, "1200021");
        psmnt.setBytes(2, b);
        psmnt.executeUpdate();

This code executes without error and database shows blob content, but when I try to retrieve the file using the below code it gives a corrupt file which doesn't open.

ResultSet rs=con.Execute("SELECT DOCUMENT FROM 2012DOC");
rs.next();
response.setContentType("application/pdf");
response.setHeader("Content-Disposition", "attachment; filename=kjsahkjd.pdf");

java.sql.Blob blob = rs.getBlob("DOCUMENT");
ServletOutputStream servletOutputStream = response.getOutputStream();
InputStream in = blob.getBinaryStream();
int length = (int) blob.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1) {

servletOutputStream.write(buffer, 0, length);
}
in.close();
servletOutputStream.flush();
servletOutputStream.close();

It outputs the file with same size as the original,but the file doesn't open. The pdf reader is fired but cannot open the file and gives an error 'the file was either damaged or not supported file type'

I also had this kind of problems. Can you specify doesn't open? The PDF reader is fired but it cannot display the file? Or the browser doesn't end the download? — Raffaele
– Raffaele, Commented Jun 28, 2012 at 8:25
The pdf reader is fired but it cannot open the file. The reader gives an error 'the file was either damaged or not supported file type' — Sangeet Menon
– Sangeet Menon, Commented Jun 28, 2012 at 8:53
Without a reproducible test case, we can only try to guess to trim the problem down. I think your first attempt should be comparing the original file and the database content, to see if they really are binary equal. The driver is the official MySQL Connector/J, isn't it? — Raffaele
– Raffaele, Commented Jun 28, 2012 at 9:11
Also, instead of reading the content of the file in memory, you'd better use PreparedStatement.setBlob(int, InputStream). According to the docs, setBytes uses (LONG)VARBINARY — Raffaele
– Raffaele, Commented Jun 28, 2012 at 9:18
@Raffaele, I found a way for that, using setBinaryStream... Posted the answer for other's reference. — Sangeet Menon
– Sangeet Menon, Commented Jun 28, 2012 at 9:25

Sangeet Menon · Accepted Answer · 2012-06-28 09:23:55Z

3

Ahhh...After a little debugging I found the code that uploads is troublesome, and finally got the right way to do it.

Here is what I did...I'm posting it so that others with same problem can solve it

After Converting the java.io.File to java.io.FileInputStream

FileInputStream io = new FileInputStream(inFile);

Set the BLOB field using psmnt.setBinaryStream()

psmnt.setBinaryStream(3,  (InputStream)io,(int)inFile.length());

answered Jun 28, 2012 at 9:23

Sangeet Menon

9,9238 gold badges44 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shivam · Accepted Answer · 2013-07-11 07:17:22Z

0

remove " java.sql.Blob blob = rs.getBlob("DOCUMENT"); "

and don't initialize length i.e instead of

int length = (int) blob.length();

just write

int length;

then it downloads file successfully .. enjoy :)

edited Jul 11, 2013 at 7:17

user907729

answered Jul 11, 2013 at 6:57

shivam

31 silver badge6 bronze badges

Collectives™ on Stack Overflow

Upload PDF file to mysql BLOB by using java.sql.PreparedStatement without corruption

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related