SyntaxFix

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

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
}

This question is related to java oracle jdbc sqlexception

The answer is

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.

As @TechSpellBound suggested remove the quotes around the ? signs. Then add a space character at the end of each row in your concatenated string. Otherwise the entire query will be sent as (using only part of it as an example) : .... WHERE bookings.booking_end < date ?OR bookings.booking_start > date ?GROUP BY ....

The ? and the OR needs to be seperated by a space character. Do it wherever needed in the query string.

In date '?', the '?' is a literal string with value ?, not a parameter placeholder, so your query does not have any parameters. The date is a shorthand cast from (literal) string to date. You need to replace date '?' with ? to actually have a parameter.

Also if you know it is a date, then use setDate(..) and not setString(..) to set the parameter.

Source: Stackoverflow.com

Examples related to java

• Under what circumstances can I call findViewById with an Options Menu / Action Bar item? • How much should a function trust another function • How to implement a simple scenario the OO way • Two constructors • How do I get some variable from another class in Java? • this in equals method • How to split a string in two and store it in a field • How to do perspective fixing? • String index out of range: 4 • My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to oracle

• concat yesterdays date with a specific time • ORA-28001: The password has expired • how to modify the size of a column • How to create a blank/empty column with SELECT query in oracle? • Find the number of employees in each department - SQL Oracle • Query to display all tablespaces in a database and datafiles • When or Why to use a "SET DEFINE OFF" in Oracle Database • How to insert date values into table • error: ORA-65096: invalid common user or role name in oracle • In Oracle SQL: How do you insert the current date + time into a table?

Examples related to jdbc

• Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' • Hibernate Error executing DDL via JDBC Statement • Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] • MySQL JDBC Driver 5.1.33 - Time Zone Issue • Spring-Boot: How do I set JDBC pool properties like maximum number of connections? • Where can I download mysql jdbc jar from? • Print the data in ResultSet along with column names • How to set up datasource with Spring for HikariCP? • java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Exception occurring. Why? • java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname

Examples related to sqlexception

• Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0 • When does SQLiteOpenHelper onCreate() / onUpgrade() run? • Java java.sql.SQLException: Invalid column index on preparing statement • java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...' • What does "if (rs.next())" mean? • How to catch a specific SqlException error? • java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver • conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Tags

Indexed Badimageformatexception Approximate Commitanimations Correlated Serviceknowntype Distributed-apps Valuechangelistener Scalable Unsafe Makefile-project .net-1.0 Carmen Super Caf Optimistic Metadata Saf Basic-msi Io-buffering Enscript Count Spanned Ivalueconverter Blast Openvz Pylons Umbraco Orgchart Decision-tree Session-state-provider Visual-inheritance Uploadstring Icommand Repost Width Msdtc Adb Absolute-path Dynamic-text Settingslogic Sqlclr Ms-access Avrcp No-data Typeset Expando Email-threading Relaxng Root-node Usebean Default-constraint Punctuation Epic Google-datatable Sharepointfoundation2010 Spml Html-safe Runtimemodification In-addr Imagemagick Sql-injection Reset Parallel-port Neural-network Scripting-languages Subscript Interopbitmapimage Project-files Basic-authentication Fact-table Dbconnection Url-interception Array-initialization Wss4j Console-output Progress-reports Pygobject Email-processing Boost-filesystem Username .net-1.1 Stuarray Self-updating M2eclipse Sunstudio Multiple-matches Mechanicalturk Flex-builder-3 Win64 M4a Purify Hex Linechart Bluetooth Cfengine Standardized Space-tree Ado.net Factors