CODEWITHSUNDEEP
javamysqljsptomcatjdbc
Sohail
Sohail

Reputation: 221

Unable to connect MYSQL database using JSP and TOMCAT

I am unable to connect database “test” created in mySQL using Java Server Pages (JSP) & Tomcat. I am getting the error message "Unable to connect to database".

I am using following software specifications

I have configured following environment variables as follow

I have placed mysql-connector-java-5.1.13-bin jar file at following two locations:

I am currently connected to mySQL Server 5.1 using default user name ‘root’ and ‘password ‘sohail’ Currently ‘test’ database is selected

I am running following code 

<%@ page language="java" import="java.sql.*" errorPage=""%>
<%@ page import="java.util.*"%>
<html>
<head>
<title>Test DB Page</title>
</head>
<body>
<h3>Insert Data INTO MYSQL</h3>
<%
Connection con = null;
PreparedStatement stmt = null;
try 
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "sohail");
stmt = con.prepareStatement("INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, emailAddress) VALUES ('?','?','?','?','?','?','?')");
stmt.setString(1, request.getParameter("loginID"));
stmt.setString(2, request.getParameter("firstname"));
stmt.setString(3, request.getParameter("lastname"));
stmt.setString(4, request.getParameter("gender"));
stmt.setString(5, request.getParameter("Date of Birth"));
stmt.setString(6, request.getParameter("Country"));
stmt.setString(6, request.getParameter("emailAddress"));
int num = stmt.executeUpdate();
System.out.println(num + "records updated");
stmt.close();
con.close();
}
            catch (Exception ex) {
            out.println("Unable to connect to batabase.");
            }
 finally {
      try {
        if (stmt != null)
         stmt.close();
        }  
catch (SQLException e) {}
        try {
         if (con != null)
         con.close();
         } 
catch (SQLException e) {}
     }
%>
Done with new DB
</body>
</html>

Can anyone explain why I am not able to connect Database?

Update: I am getting following exception:

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

Thanks you All

The Problem has been resolved by fixing following 2 errors.

1- Dont Quote the parameters i.e. ?

2- Removed duplication i.e. Parameter no 6

Upvotes: 2

Views: 10081

Answers (3)

BalusC
BalusC

Reputation: 1108722

Parameter index out of range (1 > number of parameters, which is 0)

This means that preparedStatement.setSomething(1, something) has failed because 1 is larger than the available number of parameters which is 0.

con.prepareStatement("INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, emailAddress) VALUES ('?','?','?','?','?','?','?')"

You should remove those singlequotes. You don't need to include them. The PreparedStatement will take care about the correct way of quoting. The placeholders ? should be used unquoted. So:

con.prepareStatement("INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, emailAddress) VALUES (?,?,?,?,?,?,?)"

See also:

That said, this is not the recommended way to use JDBC nor JSP. Java code should be avoided in JSP. Also, the CLASSPATH environment variable is ignored by Tomcat.

Upvotes: 2

duffymo
duffymo

Reputation: 308763

This code is hurting my eyes.

Scriptlet code like this is the wrong way to go.

If you must put this in a JSP, you ought to be using JSTL <sql> tags.

But even that's a bad idea. You've got all your connection information in plain text in the page. You aren't using a connection pool or JNDI lookup. You say "test page" - don't let this go to production.

Upvotes: 0

nos
nos

Reputation: 229108

Don't quote the parameters. Use 

INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, 
    emailAddress)  VALUES (?,?,?,?,?,?,?)");

Your code also set parameter no. 6 twice.

Upvotes: 1

Related Questions