CODEWITHSUNDEEP
javamysqldatabasefileblob
joey rohan
joey rohan

Reputation: 3566

BLOb: cannot read all data,but few kb

I am using BLOb support for inserting and reading from MySQl.(JDBC) I can do that,but when it reads,its only few kb's.I dont know why. here is the working code:

 import java.sql.*;
import java.io.*;

public class InsertAndRetrieveImage {

 public static void main(String[] args) throws SQLException, FileNotFoundException, IOException {
  int id=7;
String connectionURL = "jdbc:mysql://127.0.0.1:3306/newdb";;
Connection con=null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(connectionURL, "root", "sesame");

 PreparedStatement ps = null;

 ps=con.prepareStatement("INSERT IGNORE INTO image VALUES(?,?,?)");

         File file = new File("e:/anarkali.wav");


         FileInputStream fs = new FileInputStream(file);

     try{
        System.out.println(fs.available());
         ps.setInt(1,id);
         ps.setBinaryStream(2,fs,fs.available());
         String filen=file.getName();
      ps.setString(3,filen);


      int i = ps.executeUpdate();



        String filename="filename";OutputStream os=null;byte[] content=null;
        ps= con.prepareStatement("SELECT fs from image where id=7");
        ResultSet rs = ps.executeQuery();
         System.out.println(rs);
      while(rs.next()) {


            Blob blob = rs.getBlob("fs");

        content = blob.getBytes(1, (int) blob.length());


        os = new FileOutputStream(new File("e://testanar.wav"));

    }
    os.write(content);
    os.close();
    con.close();
    ps.close();}
     catch(SQLException e)
     {System.out.println(e.getMessage());
     }

  }catch(Exception ex){}


  }

Is the problem while reading or writing?my BLOb's size is somewhat 65535.Is this is the bug?

Upvotes: 3

Views: 2657

Answers (3)

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

Your program is correct. If the file size is small then it will work fine. If you are using BLOB datatype to store that binary data,change it to LONGBLOB. Because if you tried to save a file in BLOB data field, and if the file size is more than max allowed size of BLOB datatype then the content will be truncated and you will loss some content of file(depends on file size). Even if you use LONGBLOB datatype there are some things you must check. I'll explain

In mysql website we can see that max file size of LONGBLOB is 4GB. But it depends on many things. To store large file you have to check some things. First thing is in my.ini file, there is a property called max_allowed_packet, it specifies largest possible packet that can be transmitted to or from a mysql client or server. You should set max_allowed_packet property to a higher value. And restart mysql. Some file system won't allow 4GB files. In that case you cant load that much big file.

I think in your case setting max_allowed_packet property to a higher value will fix the problem.

I think you can use following table creation script 

CREATE TABLE `image` (
    `id` INT(10) NULL DEFAULT NULL,
    `fs` LONGBLOB NULL,
    `filen` VARCHAR(50) NULL DEFAULT NULL
)

Upvotes: 3

Jubin Patel
Jubin Patel

Reputation: 2052

As you know every parameter have fix value length, here is the example.

    0 < length <=      255  -->  `TINYBLOB`
     255 < length <=    65535  -->  `BLOB`
   65535 < length <= 16777215  -->  `MEDIUMBLOB`
16777215 < length <=    2³¹-1  -->  `LONGBLOB`


TINYBLOB: maximum length of 255 bytes
BLOB: maximum length of 65,535 bytes
MEDIUMBLOB: maximum length of 16,777,215 bytes
LONGBLOB: maximum length of 4,294,967,295 bytes

this MAX size is not bug,but its length to store value

In your case Use MEDIUMBLOB or LONGBLOB.

Upvotes: 1

Luislode
Luislode

Reputation: 51

I had the same problem a few days ago and what i did is not to use the InputStream available method.

use:

ps.setBinaryStream(2, fs);

but you have to verify because there are some jdbc drivers that not support it

Upvotes: -1

Related Questions