PSQLException: ResultSet not positioned properly

Hello All,

I am getting the following error:

org.postgresql.util.PSQLException: ResultSet not positioned properly,
perhaps you need to call next.

... when I call ResultSet.next() on a set of empty rows after doing a
SELECT. The actual line that results in the exception is the call to
rs2.next() in the code below. The output I see is in the following
section.

If I whittle the code down to its bare essentials I do not see this error.
I would imagine that it has something to do with my preceding use of jdbc
connections, but since I explicitly close those connections I find this
surprising.

Thanks in advance for any help.

James

=================================================================
import java.sql.*;
import java.util.*;
import java.util.regex.*;

public class populate {
static public class UpdatePair {
public String sonumber;
public String number;
public String routingNumber;
}
public static void main(String args[]) {
String sonumber;

String location="1";
String pgTimeMarker="(now()-interval '60 days')";
UpdatePair pair;

Connection pConnection = null;
Connection mConnection = null;
try {
//postgres configs
String pDataBaseDriver ="org.postgresql.Driver";
String pUrl ="jdbc:postgresql://redacted.com:5432/redacted";
String pUsername ="redacted";
String pPassword ="redacted";

//postgres configs
String p2DataBaseDriver ="org.postgresql.Driver";
String p2Url ="jdbc:postgresql://redacted.com:5432/redacted";
String p2Username ="redacted";
String p2Password ="redacted";

//mssql configs
String mDataBaseDriver ="net.sourceforge.jtds.jdbc.Driver";
String mUrl ="jdbc:jtds:sqlserver://redacted.org:1433/redacted";
String mUsername ="redacted";
String mPassword ="redacted";

//Load the JDBC Drivers
Class.forName(pDataBaseDriver);
Class.forName(p2DataBaseDriver);
Class.forName(mDataBaseDriver);

//Get the list of prepopulated entries from the petrocan edi db
pConnection=DriverManager.getConnection(pUrl,pUsername,pPassword);
Statement pStmt = pConnection.createStatement();
Set<UpdatePair> toUpdate=new HashSet<UpdatePair>();
String q="SELECT sonumber FROM loading WHERE time IS NULL";
ResultSet pRs = pStmt.executeQuery(q);

while(pRs.next()) {
sonumber=pRs.getString("sonumber").trim();
pair = new UpdatePair();
pair.sonumber = sonumber;
toUpdate.add(pair);
}
pConnection.close();

//Find the associated record in the Navision db
mConnection=DriverManager.getConnection(mUrl,mUsername,mPassword);
Statement mStmt = mConnection.createStatement();
ResultSet mRs = null;

Iterator si = toUpdate.iterator();
while (si.hasNext()) {
pair = (UpdatePair) si.next();
sonumber = pair.sonumber;
mRs = mStmt.executeQuery("SELECT [No_],[Routing No_] FROM
[Commercial Alcohols Inc_$Sales Header] WHERE [No_] =
'"+sonumber+"'");

if (!mRs.next())
continue;
else {
pair.number=mRs.getString("No_");
pair.routingNumber=mRs.getString(2);
}
}

mConnection.close();

Connection
pConnection2=DriverManager.getConnection(p2Url,p2Username,p2Password);
Statement pStmt2 = pConnection2.createStatement();
ResultSet rs2;
si = toUpdate.iterator();
while (si.hasNext()) {
pair = (UpdatePair) si.next();
sonumber = pair.sonumber;
String arm1="null";
String arm2="null";
String timestamp=null;
StringTokenizer st;

st=new StringTokenizer(pair.routingNumber, ", ");
while (st.hasMoreTokens()) {
String token=st.nextToken();
q ="SELECT * FROM bol WHERE transaction='"+token;
q+="' AND time > "+pgTimeMarker;
rs2 = pStmt2.executeQuery(q);
System.out.println("JAMES AB");
if(rs2.next()) {
System.out.println("JAMES A");
timestamp=rs2.getString("time");
}
else
System.out.println("Error: ticket number not found
in bol: so#="+sonumber+" ticket#="+token+";
navroutno="+pRs.getString(2));

System.out.println("JAMES AB2");
}

}
}
catch(ClassNotFoundException e) {
System.out.println("Error finding database Driver");
e.printStackTrace();
}
catch(SQLException e) {
System.out.println("SQLException::"+e.getErrorCode()+"::"+e.getSQLState());
e.printStackTrace();
return;
}
}
}
=====================================================================
bash> java -cp .:./jtds-1.2.2.jar:./postgresql-8.3-603.jdbc3.jar populate
JAMES AB
SQLException::0::24000
org.postgresql.util.PSQLException: ResultSet not positioned properly,
perhaps you need to call next.
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkResultSet(AbstractJdbc2ResultSet.java:2635)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:1885)
at populate.main(populate.java:106)

james(at)dades(dot)ca wrote:

> System.out.println("Error: ticket number not found
> in bol: so#="+sonumber+" ticket#="+token+";
> navroutno="+pRs.getString(2));

You probably don't meant to use pRs.getString() here. That's the
resultset that you already iterated through completely in the previous
loop (and the connection it was generated from is closed, anyway)

-O

Oliver Jowett wrote:
> james(at)dades(dot)ca wrote:
>
>> System.out.println("Error: ticket number not
>> found
>> in bol: so#="+sonumber+" ticket#="+token+";
>> navroutno="+pRs.getString(2));
>
> You probably don't meant to use pRs.getString() here. That's the
> resultset that you already iterated through completely in the previous
> loop (and the connection it was generated from is closed, anyway)

Thank you, Oliver. That was indeed my problem.

James