title: Using java to upload an image as a blob to an sql database
body: <p>I have a database that is set up like this</p>
  
  <pre><code>  CREATE TABLE item (             
                ItemID int(11) NOT NULL auto_increment,  
                ItemName char(25),      
                YearManufactured int(11),   
                CopiesSold int(11),
                AmountInStock int(11),
                image blob,                         
                Description char(30),     
                PRIMARY KEY  (`ItemID`)                   
       );
  </code></pre>
  
  <p>The table works fine, and I have been able to insert and delete from it just fine. I want to organize my code a little different and use Java as opposed to writing scriplets to update the table. The initial reason I wanted to switch to do this was because I wanted to be able to upload an image easier. This is my code:</p>
  
  <p>Here is the scriplet to open up initially. The URL, UserID and Password are definitely correct.</p>
  
  <pre><code>&lt;%  
      // Instantiating the connection to the database
      String mysJDBCDriver = "com.mysql.jdbc.Driver"; 
      String mysURL = "MY_ADDRESS"; 
      String mysUserID = "USERNAME"; 
      String mysPassword = "PASSWORD";
  
      java.sql.Connection conn=null; // a new connection
      try 
      {
          DB db = new DB();
          conn=db.dbConnect(mysJDBCDriver, mysURL,mysUserID,mysPassword);
          db.insertImage(conn,"c:/Users/DPiganell/image_launch_a5.jpg");
          db.getImageData(conn);
          System.out.println("Connected successfully to database using JConnect");
          session.setAttribute("connection", conn);
  
       }
      catch(Exception e)
      {
          e.printStackTrace();
          out.print(e.toString());
      }
  
  %&gt;
  </code></pre>
  
  <p>Here is my DB Object</p>
  
  <pre><code>package DBPackage;
  
  import java.io.*;
  import java.sql.*;
  
  public class DB
  {
          public DB() {}
  
          public Connection dbConnect(String db_driver, String db_connect_string,
             String db_userid, String db_password)
          {
                  try
                  {
                          Class.forName(db_driver);
                          Connection conn = DriverManager.getConnection(
                            db_connect_string, db_userid, db_password);
  
                          System.out.println("connected");
                          return conn;
  
                  }
                  catch (Exception e)
                  {
                          e.printStackTrace();
                          return null;
                  }
          }
  
          public void insertImage(Connection conn,String img)
          {
                  int len;
                  String query;
                  PreparedStatement pstmt;
  
                  try
                  {
                          File file = new File(img);
                          FileInputStream fis = new FileInputStream(file);
                          len = (int)file.length();
  
  //                        query = ("UPDATE item SET image = ? WHERE ItemID = ?");
                          query = ("INSERT INTO ITEM (ItemType, ItemName, YearManufactured, CopiesSold, AmountInStock, image, description) "
                      + "VALUES(?,?,?,?,?,?,?");
                          pstmt = conn.prepareStatement(query);
                          pstmt.setString(1, "DVD");
                          pstmt.setString(2, "fsd");
                          pstmt.setInt(3, 1991);
                          pstmt.setInt(4, 6);
                          pstmt.setInt(5, 7);
                          pstmt.setString(7, "works");
  
                          // Method used to insert a stream of bytes
                          pstmt.setBinaryStream(6, (InputStream)fis, len);
                          pstmt.executeUpdate();
  
                  }
                  catch (SQLException e)
                  {
                          e.printStackTrace();
                  }
                  catch (Exception e)
                  {
                          e.printStackTrace();
                  }
          }
  
          public void getImageData(Connection conn)
          {
  
                   byte[] fileBytes;
                   String query;
                   try
                   {
                           query = "select image from item where ItemID=3";
                           Statement state = conn.createStatement();
                           ResultSet rs = state.executeQuery(query);
                           if (rs.next())
                          {
                                    fileBytes = rs.getBytes(1);
                                    OutputStream targetFile=  
                                    new FileOutputStream(
                                         "C:/Users/DPiganell/newImage.jpg");
  
                                    targetFile.write(fileBytes);
                                    targetFile.close();
                          }        
  
                   }
                   catch (Exception e)
                   {
                           e.printStackTrace();
                   }
          }
  };
  </code></pre>
  
  <p>Once the program reaches executeUpdate, and exception is thrown. Any help is appreciated. I am willing to take a different approach if it is easier.</p>
  
  <p>SQLException Stacktrack:</p>
  
  <pre><code>SEVERE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.Util.getInstance(Util.java:386)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
      at DBPackage.DB.insertImage(DB.java:55)
      at org.apache.jsp.header_jsp._jspService(header_jsp.java:61)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:403)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:492)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:378)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
      at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
      at org.apache.catalina.core.ApplicationDispatcher.doInvoke(ApplicationDispatcher.java:787)
      at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:649)
      at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:604)
      at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:534)
      at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:1015)
      at org.apache.jsp.index_jsp._jspService(index_jsp.java:60)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:403)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:492)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:378)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
      at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
      at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
      at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)
      at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)
      at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:330)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
      at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:174)
      at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:828)
      at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:725)
      at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1019)
      at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)
      at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
      at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
      at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
      at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
      at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
      at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
      at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
      at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
      at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
      at java.lang.Thread.run(Thread.java:722)
  
  SEVERE:     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  SEVERE:     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  SEVERE:     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  SEVERE:     at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  SEVERE:     at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  SEVERE:     at com.mysql.jdbc.Util.getInstance(Util.java:386)
  SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
  SEVERE:     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
  SEVERE:     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
  SEVERE:     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
  SEVERE:     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
  SEVERE:     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
  SEVERE:     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
  SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
  SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
  SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
  SEVERE:     at DBPackage.DB.insertImage(DB.java:55)
  SEVERE:     at org.apache.jsp.header_jsp._jspService(header_jsp.java:61)
  SEVERE:     at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
  SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
  SEVERE:     at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:403)
  SEVERE:     at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:492)
  SEVERE:     at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:378)
  SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
  SEVERE:     at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
  SEVERE:     at org.apache.catalina.core.ApplicationDispatcher.doInvoke(ApplicationDispatcher.java:787)
  SEVERE:     at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:649)
  SEVERE:     at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:604)
  SEVERE:     at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:534)
  SEVERE:     at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:1015)
  SEVERE:     at org.apache.jsp.index_jsp._jspService(index_jsp.java:60)
  SEVERE:     at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
  SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
  SEVERE:     at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:403)
  SEVERE:     at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:492)
  SEVERE:     at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:378)
  SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
  SEVERE:     at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
  SEVERE:     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
  SEVERE:     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
  SEVERE:     at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
  SEVERE:     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
  SEVERE:     at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)
  SEVERE:     at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)
  SEVERE:     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)
  SEVERE:     at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:330)
  SEVERE:     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
  SEVERE:     at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:174)
  SEVERE:     at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:828)
  SEVERE:     at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:725)
  SEVERE:     at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1019)
  SEVERE:     at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)
  SEVERE:     at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
  SEVERE:     at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
  SEVERE:     at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
  SEVERE:     at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
  SEVERE:     at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
  SEVERE:     at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
  SEVERE:     at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
  SEVERE:     at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
  SEVERE:     at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
  SEVERE:     at java.lang.Thread.run(Thread.java:722)
  </code></pre>