Getting SQL Exception while using Java's PreparedStatement for select query

Question

I have entered the query manually and by copy pasting, and it runs fine. When I make the query into a PreparedStatement Object I get an error:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10145][10844][4.22.29] Invalid parameter 1: Parameter index is out of range. ERRORCODE=-4461, SQLSTATE=42815

I have read the JavaDoc for PreparedStatement objects but am fresh out of ideas. My other queries that are identical (but use int instead of String) work fine. Any thoughts?

A snippet of the code:

      String queryText = "";
      PreparedStatement querySt = null; 
      ResultSet answers = null; 


      queryText = "SELECT title, year, language, weight FROM yrb_book WHERE title = '?' AND cat = '?' ";

      try 
      {
          querySt = DbConn.prepareStatement(queryText);
      } 
      catch(SQLException e) 
      {
          System.out.println(e);
          System.exit(0);
      }

      // Execute the query.
      try 
      {
          querySt.setString(1, title);
          querySt.setString(2, category);
          answers = querySt.executeQuery();
      }

Below is the table I am working with: create table yrb_book ( title varchar(25) not null, year smallint not null, language varchar(10), cat varchar(10) not null, weight smallint not null, constraint yrb_book_pk primary key (title, year), constraint yrb_book_fk_cat foreign key (cat) references yrb_category, constraint yrb_book_weight check (weight > 0) );

I studied this answer for 30 minutes, but cannot see how it can apply in my case. Getting SQL Exception while using prepared statement for select query

jplc · Accepted Answer · 2017-03-26 04:13:12Z

3

Don't use quotes (single and double) for token "?". Instead of:

queryText = "SELECT title, year, language, weight FROM yrb_book WHERE title = '?' AND cat = '?' ";

use:

queryText = "SELECT title, year, language, weight FROM yrb_book WHERE title = ? AND cat = ? ";

answered Mar 26, 2017 at 4:13

jplc

3621 silver badge12 bronze badges

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

3 Comments

Mike Over a year ago

@ jplc you need the single quotes or it becomes a bad db2 query. I am trying to go more this way: string temp = "'"+title+"'";

jplc Over a year ago

@Mike did you execute the new query suggested. Java SQL API (PreparedStatement) do the job for you. If you get a new error then post it.

Mike Over a year ago

you are correct and I was wrong. Your suggestion fixed my problem. Thanks for your suggestion, I really appreciate it! Java indeed does add the ' to strings when replacing the ? on its own. Thanks again.

Collectives™ on Stack Overflow

Getting SQL Exception while using Java's PreparedStatement for select query

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