How to resolve java.sql.SQLException: Parameter index out of range?

Question

I'm newbie developer.In my code html page is properly displying in browser but after submiting data it is not calling post method and hence not storing data in my mysql workbence database. Schema name in databse is registration and table name is reg_details.I am using apache tomcat server.

HTML file:

<!DOCTYPE html>
    <html>
    <head>
    <meta charset="ISO-8859-1">
    <title>Insert title here</title>
    </head>
    <body>
    <form action="Registration" method="post" autocomplete="on">
    <fieldset>
    <legend>Registration Details:</legend>
    First Name &nbsp <input type="text" name="firstname" autofocus> &nbsp Middle name &nbsp <input type="text" name="middlename"> &nbsp Last Name &nbsp <input type="text" name="lastname"><br><br>
    Address &nbsp <br> <textarea name="address" rows="2" cols="25"></textarea>
    <br><br>
    Gender &nbsp <input type="radio" name="gender" value="male"> Male <input type="radio" name="gender" value="female"> Female<br><br>
    Mobile No. &nbsp <input type="text" name="mobileno"> <br><br>
    Email Id &nbsp <input type="email" name="emailid"> <br><br> 
    Birth Date &nbsp <input type="date" name="birthday"><br><br>
    Username &nbsp <input type="text" name="username"><br><br>
    Password &nbsp <input type="password" name="password"><br><br>
    <input type="submit" value="Submit">
    </fieldset>
    </form>
    </body>
    </html>

registration.java file

    import java.io.IOException;
        import java.io.PrintWriter;
        import java.sql.*;
        import javax.servlet.ServletException;
        import javax.servlet.annotation.WebServlet;
        import javax.servlet.http.HttpServlet;
        import javax.servlet.http.HttpServletRequest;
        import javax.servlet.http.HttpServletResponse;

        /**
         * Servlet implementation class Registration
         */
        @WebServlet("/Registration")
        public class Registration extends HttpServlet {

            private static final long serialVersionUID = 1L;
            /**
             * @see HttpServlet#HttpServlet()
             */
            public Registration() {
                super();
                // TODO Auto-generated constructor stub
            }

            /**
             * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
             */
            public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                // TODO Auto-generated method stub

                //http://localhost:12093
                try {
                    String firstname=request.getParameter("firstname");
                    PrintWriter print = response.getWriter();
                    print.println(firstname);
                    Class.forName("com.mysql.jdbc.Driver");
                    java.sql.Connection connection;

                    connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/registration","root","rohan");
                    PreparedStatement ps=connection.prepareStatement("INSERT INTO reg_details (name) values ('rk');");
                    //Statement stmt = connection.createStatement();
                //  ResultSet result = stmt.executeQuery("INSERT INTO reg_details (name) values ('rohan');");
                    ps.setString(1, firstname);
                    ps.executeUpdate();
        //          while(result.next())
        //          {
        //              PrintWriter print = response.getWriter();
        //              print.println(result.getString("name"));
        //          }
connection.close();
            ps.close();
                } catch (SQLException | ClassNotFoundException e) {
                    // TODO Auto-generated catch block
                e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

For writing connection con = DriverManager.getconnection(); its telling me to write like this java.sql.Connection connection; or connection con = (connection)DriverManager.getconnection();

I updated the with your suggestions. I closed the connection at end and removed the service method but after executing same error exits. I am attaching my console so that it will help you. I used mysql-connector-java-5.1.44-bin.jar for database connection.

Here is stacktrace from server console :

org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:Hospital Queue Management' did not find a matching property. org.apache.catalina.startup.VersionLoggerListener log
INFO: Server built:          
org.apache.catalina.startup.VersionLoggerListener log
INFO: Server number:         8.0.48.0
org.apache.catalina.startup.VersionLoggerListener log
INFO: Architecture:          amd64
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
 org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["http-nio-8085"]
 org.apache.coyote.AbstractProtocol start
INFO: Starting ProtocolHandler ["ajp-nio-8019"]
 org.apache.catalina.startup.Catalina start
INFO: Server startup in 1512 ms
 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3327)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3312)
    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4027)
    at Registration.doPost(Registration.java:44)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Welcome to Stack Overflow! Can you elaborate on how your code "doesn't work"? What were you expecting, and what actually happened? If you got an exception/error, post the line it occurred on and the exception/error details. Please edit these details in or we may not be able to help. — Blue
– Blue, Commented Jan 25, 2018 at 13:43
Since you have this: e.printStackTrace() please check the logs of the server you're using (tomcat, jetty, w/e) to run the application and see if there is an exception printed there. That's vital information to find issues and solve them. Besides, pay attention to the basic concepts. MySQL is the database engine, MySQL Workbench is a client tool that allows you to "see" a human representation of the databases and tables inside your MySQL engine. — Luiggi Mendoza
– Luiggi Mendoza, Commented Jan 25, 2018 at 13:45
I don't like the look of your 'service' method - what happens if you remove that. I think that that is effectively intercepting all your calls to your servlet — DaveH
– DaveH, Commented Jan 25, 2018 at 13:59
@DaiveH thanks for reply i removed the service method but the code is still not storing data in mysql database. — thetopcoder
– thetopcoder, Commented Jan 25, 2018 at 15:01

Ravi · Accepted Answer · 2018-01-25 15:01:39Z

2

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

You don't have any placeholder for preparedstatement (number of parameter are 0)

INSERT INTO reg_details (name) values ('rk')

But, you are trying to set

ps.setString(1, firstname);

And that is the reason, you are getting exception. You need to make change to SQL to accept one parameter

INSERT INTO reg_details (name) values (?)

answered Jan 25, 2018 at 15:01

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

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

Comments

Atul · Accepted Answer · 2020-10-11 11:00:50Z

0

If you have this exception while calling a stored procedure:

java.sql.SQLException: Parameter index X of is out of range (1, Z)

And if you are using

spring data jpa
Returning a value. E.g.- id field

Then you have to take care these things.

@Repository
public interface UserDao extends JpaRepository<User, Long>{

    @Procedure(procedureName = "PersistUserInfo" , outputParameterName = "id")
    long saveUserInfo(String userName, String firstName, String lastName);

}

Need to define the OUT parameter in @Proceddure syntax in the property name outputParameterName = "id"
The OUT parameter is the last parameter in the stored procedure. (This is important).

So your stored procedure should be like this:

CREATE PROCEDURE `PersistUserInfo`(IN userName varchar(20), IN firstName varchar(100), IN lastName varchar(100), OUT id BIGINT)

In the hibernate logs you will find the call of SP like this:

{call PersistUserInfo(?,?,?,?)}

With these changes I am able to solve the issue and able to call the stored procedure.

answered Oct 11, 2020 at 11:00

Atul

3,45734 silver badges47 bronze badges

1 Comment

Andreas Over a year ago

Look at the stacktrace. This is obviously a plain HttpServlet subclass named Registration, with a override of the doPost() method that does straight JDBC call to setString() of a PreparedStatement. No Spring framework, no JPA, so answer is not useful.

Collectives™ on Stack Overflow

How to resolve java.sql.SQLException: Parameter index out of range?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related