Reputation: 47

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

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)

Upvotes: 1

Answers (2)

Atul

Reputation: 3377

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.

Upvotes: 0

Ravi

Reputation: 31437

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 (?)

Upvotes: 2

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

Answers (2)

Related Questions