Java java.sql.SQLException: Invalid column index on preparing statement

java oracle jdbc sqlexception
user1851487 picture user1851487 · Jan 23, 2013 · Viewed 100.6k times · Source

my code below query the database for a set of rows based on user input. I have tried and tested the query inside of SQL Developer and it works perfectly fine returning the correct rows. And example of an input is : 2013-01-22

But for some reason inside of java i am getting this error:

java.sql.SQLException: Invalid column index

The console indicates it is firing at this line here:

preparedStatement.setString(1, to);

The full connection code:

ResultSet rs = null;
        PreparedStatement preparedStatement = null;
        try {
            String strQuery =
                    "SELECT homes.home_id, homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft,"
                    + " listagg(features.feature_name, '\n') WITHIN GROUP(ORDER BY features.feature_name) features, home_type.type_name"
                    + " FROM homes"
                    + " INNER JOIN bookings ON bookings.home_id <> homes.home_id"
                    + " INNER JOIN home_feature ON homes.home_id = home_feature.home_id"
                    + " INNER JOIN home_type ON home_type.type_code = homes.type_code"
                    + " INNER JOIN features ON home_feature.feature_id = features.feature_id"
                    + " WHERE bookings.booking_end < date '?'"
                    + " OR bookings.booking_start > date '?'"
                    + " GROUP BY homes.home_id, homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft, home_type.type_name";

            preparedStatement = conn.prepareStatement(strQuery);//prepare the statement
            preparedStatement.setString(1, to);//insert
            preparedStatement.setString(2, from);//insert
            rs = preparedStatement.executeQuery();//execute query

The full stack trace looks like this:

SEVERE: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5317)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5305)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:248)
at DB.DatabaseConnector.getPropertiesSearch(DatabaseConnector.java:258)
at DB.SearchServlet.doPost(SearchServlet.java:47)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Thread.java:722)

SEVERE:     at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5317)
SEVERE:     at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5305)
SEVERE:     at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:248)
SEVERE:     at DB.DatabaseConnector.getPropertiesSearch(DatabaseConnector.java:258)
SEVERE:     at DB.SearchServlet.doPost(SearchServlet.java:47)
SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:688)
SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
SEVERE:     at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
SEVERE:     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
SEVERE:     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
SEVERE:     at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
SEVERE:     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
SEVERE:     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
SEVERE:     at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
SEVERE:     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
SEVERE:     at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
SEVERE:     at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
SEVERE:     at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
SEVERE:     at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
SEVERE:     at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
SEVERE:     at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
SEVERE:     at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
SEVERE:     at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
SEVERE:     at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
SEVERE:     at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
SEVERE:     at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
SEVERE:     at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
SEVERE:     at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
SEVERE:     at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
SEVERE:     at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
SEVERE:     at java.lang.Thread.run(Thread.java:722)

Im not quite sure why the error would be thrown as the query works fine is SQL developer. I am using Oracle

EDIT:

The result set is read into this within the same method. The homes object is then passed back:

HomeList homes = new HomeList();
homes = new HomeList(rs);

The constructor looks like this:

public HomeList(ResultSet rs) {
    this();
    try {
        while (rs.next()) {
               list.add(new Home(rs.getInt(1),   
                                 rs.getString(2),
                                 rs.getString(3),   
                                 rs.getInt(4),
                                 rs.getInt(5),
                                 rs.getInt(6),
                                 rs.getInt(7),
                                 rs.getInt(8),
                                 rs.getString(9),
                                 rs.getString(10)
                                     ));
        }//end while
    } catch (SQLException e) {
        e.printStackTrace();
    }//end try
}

Answer

TechSpellBound picture TechSpellBound · Jan 23, 2013

Everywhere inside the query string, the wildcard should be ? instead of '?'. That should solve the problem.

EDIT :

To add to that, you need to change date '?' to to_date(?, 'yyyy-mm-dd'). Please try that and let me know.

Related questions

How to set the timezone region for JDBC Connection and avoid the SqlException timezone region not found?

I have the following problem trying to create a Connection object to handle the connection from a command line Java application and an Oracle database. So I have a Main class that contains the main() method, this one: import java.…

Read More
Java JDBC - How to connect to Oracle using Service Name instead of SID

I have a Java application that uses JDBC (via JPA) that was connecting to a development database using hostname, port and Oracle SID, like this: jdbc:oracle:thin:@oracle.hostserver1.mydomain.ca:1521:XYZ XYZ was the Oracle SID. Now I …

Read More
java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

I am getting an ORA-01000 SQL exception. So I have some queries related to it. Are maximum open cursors exactly related to number of JDBC connections, or are they also related to the statement and resultset objects we have created …

Read More