Java/JDBC/SQLite - Issue with Updating BLOB

Question

I am having an issue with updating the Blob, the issue is that the pst.executeUpdate with not execute, however, if I take out the everything that relates the the Blob/Tryinng to update the Blob everything else will update i.e. ID, Name, Address etc. Everything functions as it should, the issue is just with the Blob.

 updateEmployee.addActionListener(new ActionListener() {

        @Override
        public void actionPerformed(ActionEvent e) {
            // TODO Auto-generated method stub
            Connection connection = null;
            PreparedStatement pst = null;

            try {

                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection("jdbc:sqlite:employeeDatabase.sqlite");
                connection.setAutoCommit(false);



                int idVal = Integer.parseInt(idTextField.getText());
                String nameVal= nameTextField.getText();
                String genderVal = genderTextField.getText();
                String dobVal = dobTextField.getText();
                String addressVal = addressTextField.getText();
                String postcodeVal =  postcodeTextField.getText();
                String ninVal =  ninTextField.getText();
                String jobVal =  jobtitleTextField.getText();
                String startDateVal =  startdateTextField.getText();
                String salaryVal = salaryTextField.getText();
                String emailVal =  emailTextField.getText();
                //Icon photoBlob = photoLabel.getIcon();
                InputStream img = new FileInputStream(s);
                String sql = "UPDATE employees set ID= '"+ idVal+"', Name = '"+ nameVal +"', Gender ='"+ genderVal+"', DOB='"+ dobVal+"', Address ='"+ addressVal+"', Postcode ='"+ postcodeVal+"', NIN ='"+ ninVal+"', JobTitle='"+ jobVal+"', StartDate ='"+ startDateVal+"', Salary ='"+ salaryVal+"', Email='"+ emailVal+"', Images='"+ img+" WHERE ID= '"+ idVal+"'";

                pst = connection.prepareStatement(sql);
                pst.setInt(1,Integer.parseInt(idTextField.getText()));
                pst.setString(2, nameTextField.getText());
                pst.setString(3, genderTextField.getText());
                pst.setString(4, dobTextField.getText());
                pst.setString(5, addressTextField.getText());
                pst.setString(6, postcodeTextField.getText());
                pst.setString(7, ninTextField.getText());
                pst.setString(9, startdateTextField.getText());
                pst.setString(10, salaryTextField.getText());
                pst.setString(11, emailTextField.getText());
                pst.setBytes(12, readFile(s));

                pst.executeUpdate();




                System.out.println("Employee Updated");
                JOptionPane.showMessageDialog(null, "Employee has successfully been updated");              


                 connection.commit();
                pst.close();
                connection.close();
            }
            catch ( Exception e1 ) {

                if(idTextField.getText().equals("")){
                    JOptionPane.showMessageDialog(null, "Please Ensure An Employee Has Been Selected");
                }
            }
        }});

Edit -

I can however, insert and delete blob files as well as retrieve. Just this updating is giving me an issue.

Do not, again, do not under any circumstance concatenate parameter values into an SQL statement, that leads to SQL injection attacks. Please read up on it and use preparedStatement and setParameter, so that one day, you will not be responsible for a hack that compromises my credit card or password. — Tony BenBrahim
– Tony BenBrahim, Commented Dec 23, 2016 at 1:50

Gord Thompson · Accepted Answer · 2016-12-21 16:34:28Z

1

Your SQL command text is not valid for a parameterized query. Instead of creating a dynamic SQL command string with imbedded values ...

String sql = "UPDATE employees set ID= '"+ idVal+"', Name = '"+ nameVal +"', Gender ='"+ genderVal+"', DOB='"+ dobVal+"', Address ='"+ addressVal+"', Postcode ='"+ postcodeVal+"', NIN ='"+ ninVal+"', JobTitle='"+ jobVal+"', StartDate ='"+ startDateVal+"', Salary ='"+ salaryVal+"', Email='"+ emailVal+"', Images='"+ img+" WHERE ID= '"+ idVal+"'";

... you should be using a command string with question marks as parameter placeholders ...

String sql = "UPDATE employees set ID = ?, Name = ?, Gender = ?, DOB = ?, Address = ?, Postcode = ?, NIN = ?, JobTitle = ?, StartDate = ?, Salary = ?, Email = ?, Images = ? WHERE ID = ?;

... and then using .setInt, .setString, .setBytes et al to set the parameter values.

(Note also that it is actually redundant to SET the "ID" value when you are using ... WHERE ID = ?.)

edited Dec 21, 2016 at 16:34

answered Dec 21, 2016 at 16:26

Gord Thompson

125k39 gold badges251 silver badges458 bronze badges

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

3 Comments

TheNotoriousCoder Over a year ago

Thanks for the answer. It does work in terms of executing but doesn't update the database.

Gord Thompson Over a year ago

executeUpdate should return an int value indicating the number of rows affected, so verify that it is not zero. Also ensure that you have specified the correct number of parameters, in the correct order, and that they are of the correct type.

TheNotoriousCoder Over a year ago

Still have a problem with the BLOB, however, it's fine. I'm not going to be fussed about updating it.

Collectives™ on Stack Overflow

Java/JDBC/SQLite - Issue with Updating BLOB

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related