Nicky Nicky - 6 months ago 23
SQL Question

Java.sql.SQLException: Parameter index out of range 2> 1

I have been getting this error and I don't know what is causing it.

This is a simple email auto reply java that I came up with. I will connect to a database which has two columns in the table. rEmail and sEmail, receiver ... sender.

So if the receiver and sender matches the check that I have done for my java code.. it will auto reply an email but I kept getting this SQL error which I have no idea what is it about.. Below is my codes..

public class TestList
extends TestMatch
{

private Collection senders;

public void init()
throws MessagingException
{

}

public Collection match(Mail mail) throws MessagingException
{
Connection conn = null;
Statement stmt = null;
try
{
Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection("jdbc:mysql://localhost/mail", "root", "password");

stmt = conn.createStatement();

String receiver = mail.getRecipients().toString();
String sender = mail.getSender().toString();

String receiverResult = receiver.substring(receiver.indexOf("[") + 1, receiver.indexOf("]"));

String sql = "SELECT senderEmail FROM TestList where rEmail='"+ rResult +"'" + " AND sEmail='"+sender+"'";
System.out.println("SQL ERROR Here -- 31");

ResultSet rs = stmt.executeQuery(sql);

this.senders = new HashSet();
while (rs.next()) {
this.senders.add(new MailAddress(rs.getString("sEmail")));
}

rs.close();
stmt.close();
conn.close();
}
catch (SQLException se)
{
se.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (stmt != null) {
stmt.close();
}
}
catch (SQLException se2) {}
try
{
if (conn != null) {
conn.close();
}
}
catch (SQLException se)
{
se.printStackTrace();
}
}
System.out.println("SQL ERROR Here -- 72);
if (this.senders.contains(mail.getSender())) {
System.out.println("SQL ERROR Here -- 74");
Properties props = new Properties();
Session session = Session.getDefaultInstance(props);
MimeMessage msg = new MimeMessage(session);

System.out.println("auto reply");
msg.setSubject ("subject");
msg.setSender (new InternetAddress("Postmaster@test.com"));
msg.setRecipient(Message.RecipientType.TO,(new InternetAddress("user1@captcha.com")));
msg.setText ("describe the mail text of","utf-8","Plain");
Transport.send(msg);
System.out.println("e-mail sent.");

return mail.getRecipients();
} else {
return null;
}
}
}


This thing will run twice, and I do not know exactly why also. At first everything will go smoothly until email sent and then it will repeat once more and stop at SQL error --72.

I have been trying to figure this out for days so can someone please help?

===== UPDATE NEW ERROR after using rs.getString(1) or rs.getString(2);

`SQL ERROR Here -- line 31
java.sql.SQLException: Column Index out of range, 2 > 1.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.checkColumnBounds(ResultSetImpl.java:818)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5651)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5570)
at org.apache.james.transport.matchers.SenderIsGreyList.match(SenderIsGreyList.java:59)
at org.apache.james.transport.LinearProcessor.service(LinearProcessor.java:357)
at org.apache.james.transport.JamesSpoolManager.process(JamesSpoolManager.java:405)
at org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.java:309)
at java.lang.Thread.run(Thread.java:745)
SQL ERROR Here -- line 72`

Answer

Looks like you're attempting to retrieve column named "sEmail" from rows in the resultset...

  rs.getString("sEmail")

But your query doesn't return a column of that name. The SELECT statement returns a column named "senderEmail".

I always use numeric. Use 1 to get the first column, e.g.

  rs.getString(1)