Mike
Mike

Reputation: 401

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

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

Upvotes: 0

Views: 3796

Answers (1)

jplc
jplc

Reputation: 362

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 = ? ";

Upvotes: 3

Related Questions