Update SQL image in java

Question

What I need to do (And I know it's bad practice, but I'm kind of forced to) is upload an image to an SQL DB from Java. Currently, I'm using a prepared statement and trying to use this to upload the bytes from the image.

public void insertImage(Connection conn,String img,String strItemNum)
{

        String query;
        PreparedStatement pstmt;

        try
        {
                File file = new File(img);
                FileInputStream fis = new FileInputStream(file);
                byte[] image = new byte[(int) file.length()];
                fis.read(image);
                System.out.println("image as sent " + image.length);
                query = ("SELECT [Item Picture] from [Inventory] where [Item Number] = '" + strItemNum + "'");
                pstmt = conn.prepareStatement(query);
                System.out.println(pstmt.getMetaData().getColumnName(1) + " of type: " + pstmt.getMetaData().getColumnTypeName(1));
                pstmt.setBytes(1,image);
                pstmt.executeUpdate();
                pstmt.close();


        }
        catch (IOException | SQLException e)
        {
            System.err.println(";"+e.getMessage());
            e.printStackTrace();

        }

}

But this is yielding an SQLException: Invalid parameter index 1. The parameter in my table is of type "image" and I can't get anything to stick. I've tried using .setBlob but from my google research, it appears that Blobs were never implemented very well.

EDIT: Solved by using AVD's answer

changed to

query = ("Update  [Inventory] set [Item Picture] = ? where [Item Number] = ?");
pstmt = conn.prepareStatement(query);
pstmt.setBytes(1,image);
pstmt.setString(2, strItemNum);
pstmt.executeUpdate();
pstmt.close();

are you sure that your query is well-formed? These brackets seems to be illegal... — Alex Stybaev
– Alex Stybaev, Commented Jul 2, 2012 at 13:03
@AlexStybaev: the brackets are Microsoft's deviation from the SQL standard. — user330315
– user330315, Commented Jan 4, 2014 at 10:30

KV Prajapati · Accepted Answer · 2012-07-02 13:14:21Z

3

You should have to use INSERT or UPDATE sql statement to add a new record or update an existing row.

String sql="INSERT INTO TableName (Col1,Col2) VALUES (?,?)";

Use ? (question mark) to specify the placeholder.

EDIT:

Connection connection=null;
PreparedStatement statement=null;
String sql="UPDATE TableName set ImageCol1=? WHERE ID=?";
try{
  //Obtain connection 
  statement=connection.prepareStatement(sql);
  statement.setBytes(1,byteArray);
  statement.setInt(2,10);
  ...
}catch(SQLException ex){
  //
}finally{
  if(ps!=null) {
    try{
      ps.close();
    }catch(Exception ex){
      //
    }
  }
 if(cn!=null) {
    try{
      cn.close();
    }catch(Exception ex){
      //
    }
  }
}

edited Jul 2, 2012 at 13:14

answered Jul 2, 2012 at 13:03

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

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

2 Comments

damian Over a year ago

Okay so how would I use the UPDATE statement for the bytes? Sorry, I'm VERY new to SQL

damian Over a year ago

Thankyou! This was amazingly helpful.

jhenrique · Accepted Answer · 2018-08-15 20:03:52Z

2

You should do something like this:

Connection con = getConnection();
PreparedStatement ps = null;
con = getConnection();
ps = con.prepareStatement("insert into tableName (column, blobColumn...) values (?, ?)");
ps.setString(1, "ID");
File image = new File("C:/yourPath.../...");
inputStream = new FileInputStream(image);
ps.setBinaryStream(2, (InputStream) inputStream, (int) (image.length()));
ps.executeUpdate();

That's it, now you inserted your img to DB.

edited Aug 15, 2018 at 20:03

answered Jul 22, 2014 at 17:31

jhenrique

8781 gold badge11 silver badges21 bronze badges

Collectives™ on Stack Overflow

Update SQL image in java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related