Uploading file to MySQL using DataHandler

Question

I am currently haing trouble uploading a very small file (.mp3) to a MySQL Database using a DataHandler. I am trying the code locally after which it will be translated to a webservice. The following "rough" code attempts to upload the file. Now I am not sure if the upload part messes up or the download part is not working.Because I tested the code with a very simple text file and when I downloaded it (from DB) - it was filled with junk data.Here is the code I am using for both parts

Upload Part

public void uploadFile(FileUploader Dfile)
{

        DataHandler handler = Dfile.getDfile();
        InputStream is = handler.getInputStream();

        //Move input stream to byteArray
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        byte[] tmp = new byte[4096];
        int ret = 0;

        while((ret = is.read(tmp)) > 0)
        {
            bos.write(tmp, 0, ret);
        }

        byte[] myArray = bos.toByteArray();

        MySQL s = new MySQL();

        s.InsertFile(Dfile.getName(), myArray);
        is.close();
}//End method

This is where the array gets dumped in the DB

public void InsertFile(String Fname , byte[] myArray )
{
   String sql = "INSERT INTO `uploadtest`.`File` (`Filename`,`Data`) VALUES ( \"" + Fname + "\",\"" + myArray + "\");" ;
   try 
   {
       //Insert it into the Db
       int a = stat.executeUpdate (sql);
   } 
   catch (IOException e)
   {
       e.printStackTrace();
   }
   catch (SQLException e) 
   {
    e.printStackTrace();
   }
}//end method

Download Part

This is my code for testing if the upload was successful

     String sql = "SELECT Data FROM uploadtest.File where filename=\"" + name + "\"";
    try 
       {
           ResultSet rs = stat.executeQuery(sql);
           Blob blob = null;
           InputStream inputStream = null;

           if(rs.next())
           {
            inputStream = rs.getBinaryStream("Data");
           }


            OutputStream out = new FileOutputStream(new File("d:\\somefile.mp3")); // Just to test if file retrieved from DB(blob type) was correct

            int read = 0;
            byte[] bytes = new byte[1024];

            while ((read = inputStream.read(bytes)) != -1)
            {
                out.write(bytes, 0, read);
            }

            inputStream.close();
            out.flush();
            out.close();

Any suggestions on where I might be going wrong ??

score 0 · Answer 1 · answered Apr 01 '13 at 10:56

This is where you are going wrong:

String sql = "INSERT INTO `uploadtest`.`File` (`Filename`,`Data`) VALUES ( \"" + Fname + "\",\"" + myArray + "\");" ;

Just like you are using a stream getting method in your download part, you need to insert the data into the database using a stream method. what you do now is just concatenating the String representation of the object myArray. just to get an Idea, try to log or print the value in the variable sql to see what it contains and to see what statement will be executed.

As for how to solve this, you can use a PreparedStatement and then use setBinaryStream() or setBlob() and hand in your stream from the data handler directly without the need for byte stream. here is an example: Insert file data into MySQL database using JDBC

a last note on this: the whole data will be passed in memory and you might get memory related issues from java or mysql when files get large. if you insert large files, you can consider storing the files in chunks. You can also consider not inserting the file into the database. keep the files on the filesystem and organize them into directories and subdirectories. and only store the path of the file in the database.

Uploading file to MySQL using DataHandler

1 Answers1