Java error: The index out of range when update sql

Question

I have an error when I try to update the record in MSSQL. My code:

    PreparedStatement ps = con.prepareStatement("UPDATE tblUsers SET firstName='?', lastName = '?', phone = ?,"
            + "[group] = ?, picture = ?, address = ? Where id=?");
    ps.setString(1, firstName);
    ps.setString(2, lastName);
    //update phone has two conditions
    if (!phone.isEmpty()) {
        ps.setString(3, "'" + phone + "'");
    } else {
        ps.setString(3, null);
    }
    ps.setInt(4, group);
    //update img has two conditions
    if (pathImg != null) {
        ps.setBytes(5, MyUtils.getImgbinary(pathImg));
    } else {
        ps.setBytes(5, MyUtils.getImgbinary(defaultPathImg));
    }
    //update address has two conditions
    if (!address.isEmpty()) {
        ps.setNString(6, "'" + address +"'");
    } else {
        ps.setNString(6, null);
    }
    ps.setInt(7, userID);

When I tried to executeUpdate(), 'The index 6 is out of range' error throws. I don't understand why? In here, address can be null.

You shouldn't need to "quote" the parameters, it's kind of the point of using `PreparedStatement` — MadProgrammer, Aug 24 '19 at 08:04
That's nice, but you don't need to use `'?'` or "quote" the values, that's the point of using `PreparedStatement`. I thinking that your issue is with `setBytes` — MadProgrammer, Aug 24 '19 at 08:13
You might want to have a look at [this example](https://stackoverflow.com/questions/15127100/store-and-retrieve-images-in-postgresql-using-java) and I'd look at [Storing Binary Data](https://jdbc.postgresql.org/documentation/head/binary-data.html) of the PostgresSQL JDBC documentation for examples — MadProgrammer, Aug 24 '19 at 08:16
I tried running the code again without setBytes and fixing the update query but it still got the 'The index 5 is out of range' error. I don't think setBytes() has problems. — Ngọc Hữu, Aug 24 '19 at 09:01
I used another way: setting a String sql variable. But I want to better understand the above error. — Ngọc Hữu, Aug 24 '19 at 09:11
I'm staring at `ps.setNString(6, "'" + address +"'");` and `firstName='?'` scratching my head over why you insist on quoting these parameters, when it's not required — MadProgrammer, Aug 24 '19 at 09:49

Java error: The index out of range when update sql

0 Answers0