Getting “java.sql.SQLException: Values not bound to statement” exception

Question

I recently upgraded from sqlite-jdbc-3.7.2.jar to sqlite-jdbc-3.26.0.jar. I observed that the insert query in my code are failing with “java.sql.SQLException: Values not bound to statement” exception.

Below is the piece of code which works fine for previous version of sqlite :

String sqlStatement = "INSERT INTO table_name(id,name,type,author,size) VALUES (?,?,?,?,?)";
try
{
    Connection conn = this.connect(<name>); 
    PreparedStatement pstmt = conn.prepareStatement(sqlStatement))
    {
        pstmt.setInt(1, rs.getInt(<value>));
        pstmt.setString(2, rs.getString(<value>));
        pstmt.setInt(3, rs.getInt(<value>));

        if (somecondition)
        {
            pstmt.setString(4, rs.getString(<Value>));
            pstmt.setInt(5, rs.getInt(<value>));

        }
        pstmt.executeUpdate();
    }
}
catch(Exception e)
{
    //handling of exception goes here
}

I have read the release notes https://www.sqlite.org/changes.html and checked that as a part of Query planner enhancements, few things were changed (The query planner examines the values of bound parameters to help determine if a partial index is usable. ). But I am still not clear what was the enhancement and how it is affecting my code. Also can someone guide me on how to fix the above code?

Thanks, Ketaki

You have 5 placeholders/parameters in your SQL query string, but when somecondition is false, you only supply 3. This is what SQLite complains about. — Corion
– Corion, Commented Jan 15, 2019 at 10:09
Is this is your actual code? Here PreparedStatement pstmt = conn.prepareStatement(sqlStatement)) you have an extra right parenthesis and right below you have a block surrounded with curly brackets, why? Is there an if statement that is missing and not allowing the parameters to get values? Anyway, you do have later such an if statement. — forpas
– forpas, Commented Jan 15, 2019 at 10:11
No, the extra bracket is there by mistake. The statement is conn.prepareStatement(sqlStatement), since I cannot paste the actual code here, I modified it a bit and then pasted here. Please ignore — Ketaki Fadnavis
– Ketaki Fadnavis, Commented Jan 15, 2019 at 12:06
Thanks Corion. The issue is resolved after I passed the values for all the columns. The modified code part is : if (somecondition) { pstmt.setString(4, rs.getString(<Value>)); pstmt.setInt(5, rs.getInt(<value>)); } else { pstmt.setString(4, <default value>); pstmt.setInt(5, <default value>); } I ensured that everytime all the columns are populated. Thanks for your help ! — Ketaki Fadnavis
– Ketaki Fadnavis, Commented Jan 15, 2019 at 12:07
@Ketaki - If this is indeed a change in behaviour from previous versions of SQLite then please write up a short answer explaining what you needed to do to fix the problem. That will help future users facing the same issue. — Gord Thompson
– Gord Thompson, Commented Jan 15, 2019 at 13:48

Ketaki Fadnavis · Accepted Answer · 2019-01-18 10:16:06Z

2

In the above program, the insert query was expecting 5 values to be populated by user. However, if(somecondition) is not satisfied then values for 4th and 5th were not getting inserted in query. This was the issue since sqlite-jdbc-3.26.0 expects all values to be populated before executing the query. In the fix, I ensured that all values are populated even if "somecondition" is false by inserting else block.

Fix :

 if (somecondition)
  {
            pstmt.setString(4, rs.getString(<Value>));
            pstmt.setInt(5, rs.getInt(<value>));

  }
  else
  {
            pstmt.setString(4, rs.getString(<default-value>));
            pstmt.setInt(5, rs.getInt(<defaul`enter code here`t-value>));
  }

answered Jan 18, 2019 at 10:16

Ketaki Fadnavis

416 bronze badges

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

Collectives™ on Stack Overflow

Getting “java.sql.SQLException: Values not bound to statement” exception

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related