Putting files in mySQL databases (using Java)

Use set/getBinary stream with a PreparedStatement

Here's an insert example. Do the reverse for a select (i.e. to extract):

// Get the file
File f = new File("x.exe");
// Prepare the insert
String query = "INSERT INTO tableWithBlob (fileField) VALUES (?)";
PreparedStatement pstmt = connection.prepareStatement(query);

pstmt.setBinaryStream(1, new FileInputStream(f), (int)f.length());
// Insert
pstmt.executeUpdate();

I have tried the code given and it works when attempting to insert .zip files and .png files into the database but i can't get .doc files or .rar files in yet.

.doc file is 102kb. CEHJ's code for extracting it makes all files come out at 2.81Mb. My Version of what i think the code should be:

Statement standardStat = conn.createStatement();
ResultSet rs = standardStat.executeQuery("SELECT * FROM tbl2 where ref='67'");
                              
rs.next();
                              
InputStream in = rs.getBinaryStream(2);
FileOutputStream out = new FileOutputStream("E:\\x-file");
int temp = 0;
                              
      while ((temp = in.read()) != -1)
      {
                 out.write(temp);
      }
                              
out.close();


---Brings out a file that is 63Kb. So neither versions seem to be working.

Plus when i attempt to put a 1.21Mb RAR file in, i get the sql exception of:
[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]Got a bigger packet than 'Max Allowed Packets' Byte

Can any one help me with this? The full code is below:


----------FULL CODE-------------
               //i already have a file input stream at this point called fis.                

      //force the loading of the jdbc - obdc driver
      Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                              
      //get a connection through this driver
               Connection conn = DriverManager.getConnection("jdbc:odbc:SEContacts");
                              
      //create a statement space
      String query = "INSERT into tbl2 values (?,?);";
      PreparedStatement stat = conn.prepareStatement(query);
      stat.setInt(1,67);
      stat.setBinaryStream(2,fis,(int)selectedFile.length());
                                          
      //insert the file into the database
      int ok = stat.executeUpdate();
                              
                 JOptionPane.showMessageDialog(null,"File Inserted into database");
                        

                 //This is the code to get the file out again.
      
      //use a not prepared statement                  
      Statement standardStat = conn.createStatement();
      ResultSet rs = standardStat.executeQuery("SELECT * FROM tbl2 where ref='67'");
                              
      rs.next();
                              
      InputStream in = rs.getBinaryStream(2);
      FileOutputStream out = new FileOutputStream("E:\\x-file");
      int temp = 0;
                              
      while ((temp = in.read()) != -1)
      {
            out.write(temp);
      }
                              
      out.close();

----------------END OF CODE--------------------

So something i not quite right. The database was configured as this:

create table tbl2 (ref INT, data BLOB); //sql

All help is much appreciated.
Thanks for all contributions so far

Phill

For files as large as the ones you're using, you should be using LONGBLOB

While you're checking this out, better to use the same file, whose length you know exactly

I solved the problem and for the benefit of others have posted the code below.

I have actually decided to ditch the database in my application basically rendering the code i've finally written obsolete but never the less it was a fantastic effort and thank you to all the posters involved for your efforts.

-------------THE DATABASE FORMAT------------------

The code below is based on the table named with the following structure

create table Files (ref INT, data VARBINARY(100000));

10Mb File limit although often crashes on big files. Works for files up to 2Mb all the time.

-------------CODE TO DUMP FILES IN A MY SQL DATBASE--------------

String result = JOptionPane.showInputDialog(null,"Please select a valid filename to be loaded into a database or hit cancel to cancel.","Select file to go into the database");

if (result == null)
JOptionPane.showMessageDialog(null,"No file inserted");
else
{
         //check if file exists
         File selectedFile = new File(result);
            
      if (selectedFile.exists())
      {
      //attempting to open file...
      try {
            FileInputStream fis = new FileInputStream(selectedFile);
            //fis.mark(0);
                  
      int result2 = JOptionPane.showConfirmDialog(null, "All Database Connection Information is hard-coded so unless this application is run on \nPhillip Taylor's home network, it will not be successfull. Would you like to continue?","Choices",JOptionPane.YES_NO_OPTION);
                        
      if (result2 == JOptionPane.YES_OPTION)
      {
      //Connect to database.
            try {
      
            //force the loading of the jdbc - obdc driver
                  Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                              
            //get a connection through this driver
                  Connection conn = DriverManager.getConnection("jdbc:odbc:SEContacts");
                              
                int document_id = (int)(Math.random() * 1000);
                    System.out.println("Generated Random ID of " + document_id);
                          String sql = "INSERT INTO Files VALUES("+ document_id +",?)";
                    PreparedStatement pstmt = conn.prepareStatement(sql);
                      
                    // Create some binary data
                    byte[] buffer = new byte[(int)selectedFile.length()];
                          
                    System.out.println("File Size: " + selectedFile.length());
                    System.out.println("Byte Array Size:" + buffer.length);
                          
                    for (int i = 0; i < buffer.length; i++)
                    {
                          buffer[i] = (byte)fis.read();;
                          
                    }
                    System.out.println("read complete");
                    if (fis.read() != -1)
                          System.out.println("ERR: Wrong file size");
                          
                    System.out.println("**************");
                          
                    for (int z = 0; z < buffer.length; z++)
                    {
                          System.out.print((char)buffer[z]);
                                
                    }
                    System.out.print('\n');
                          
                    // Set value for the prepared statement
                    pstmt.setBytes(1, buffer);
                      
                System.out.println("Now for the execute");
                    // Insert the data
                    pstmt.executeUpdate();
                          
                    System.out.println("Now for the close");
                    pstmt.close();

                              
            System.out.println("File Inserted into database");
                              //This is the code to get the file out again.
                              
                  Statement standardStat = conn.createStatement();
                  ResultSet rs = standardStat.executeQuery("SELECT * FROM Files where ref=" + document_id+ ";");
                              
            rs.next();
                              
            byte[] new_data;
                              
      //IMPORTANT - COLUMN NUMBER IS NOT THE SAME FOR INSERT AS DELETE!
      //Column MUST be found.
      new_data = rs.getBytes(rs.findColumn("data"));
      System.out.println("File Extracted From Database");
                              
      System.out.println("File Size at Extract: " + new_data.length);
                              FileOutputStream out = new FileOutputStream("E:\\from_db_("+ document_id + ") " + selectedFile.getName());
                              int temp = 0;
                              
      for (int f = 0; f < new_data.length; f++)
      {
            out.write(((Byte)new_data[f]).intValue());
      }
                              
      out.close();

      System.out.println("File Written back to Disk");
                              
                                                            
      } catch (SQLException e)
      {
                                    JOptionPane.showMessageDialog(null,"SQL Exception\n" + e.getMessage());
                                    e.printStackTrace();
      } catch (ClassNotFoundException f)
      {
                                    JOptionPane.showMessageDialog(null,"Class not found exception\n" + f.getMessage());
                                    f.printStackTrace();
                              } catch (Exception g)
                              {
                                    JOptionPane.showMessageDialog(null,"Random message\n" + g.getMessage());
                                    g.printStackTrace();
                              }
                        }
                        else
                        {
                              JOptionPane.showMessageDialog(null,"The prototype demonstration will now end");
                              fis.close();
                              System.exit(0);
                        }
                        
                        fis.close();
                  } catch (IOException e)
                  {
                        JOptionPane.showMessageDialog(null,"Error reading file");
                  }
            }
            else //file doesn't exist
                  JOptionPane.showMessageDialog(null,"File don't exist!");
      }

    //end the prototype.
    System.exit(0);


---------------------------------

MANY MANY APPOLOGISES ABOUT THE FORMATTING IF IT DOESN'T APPEAR CORRECT.

Phillip Taylor

>>10Mb File limit although often crashes on big files. Works for files up to 2Mb all the time.

That may well be because you buffer the file in memory first:

>>byte[] buffer = new byte[(int)selectedFile.length()];

I would rather perform the operation as per my earlier example

8-)

(: