Need help with a DA class.

[ddmeightball]

Ok, I am trying to get this to work. I have cut out everything in my servlet except for the searching for the appointment.

My problem is that everytime I access my DA class, it sends back a null appointment. In my servlet, i have code that if it returns a null appointment, it forwards to the Error.jsp. I think my problem lies with my SQL code in the ApptDA.java class. I checked my log.txt I am creating with my LogUtil.java class and it shows an error of


[Microsoft][ODBC Driver Manager] Invalid cursor state


Here is my ApptDA.java class method that I am using to try to find the certain appointment. I have been putting serveral statements using my LogUtil.logToFile() methods to write a file to help track down my problem. I have included it in my zip file and I will include it below the code for my searchApptsDB() method.


public Appointment searchApptsDB(int searchID)throws SQLException
{
LogUtil.logToFile("Now in searchApptsDB, before Trycatch statement.");
//make the connection
try
{
//find the info
//sql = "Select * from appts where PatID="+"'"+searchID+"'";
LogUtil.logToFile("Now in try statement");
sql = "Select * from appts";
appt=null;

LogUtil.logToFile("SQL Statement.=");
LogUtil.logToFile(sql);

//make a connection, need the driver string and the dsn for access
LogUtil.logToFile("Value of Connection con.=");
con = ConnectionManager.getInstance().getConnection("schedule");
LogUtil.logToFile(con);

//create a Statement in order to execute an sql command
LogUtil.logToFile("Value of Statement stmt =");
stmt = con.createStatement();
LogUtil.logToFile(stmt);

//get the list of appointments
LogUtil.logToFile("Value of ResultSet rsPats =");
rsPats = stmt.executeQuery(sql);
LogUtil.logToFile(rsPats);

LogUtil.logToFile("Value of ResultSetMetaData rsmPats =");
rsmPats = rsPats.getMetaData();
LogUtil.logToFile(rsmPats);

//gets the for each row
LogUtil.logToFile("pid in ApptDA.=");
pid = rsPats.getInt(1);
LogUtil.logToFile(pid);

LogUtil.logToFile("patName in ApptDA.=");
patName = rsPats.getString(2);
LogUtil.logToFile(patName);

LogUtil.logToFile("paReason in ApptDA.=");
patReason = rsPats.getString(3);
LogUtil.logToFile(patReason);

LogUtil.logToFile("month in ApptDA.=");
month = rsPats.getInt(4);
LogUtil.logToFile(month);

LogUtil.logToFile("day in ApptDA.=");
day = rsPats.getInt(5);
LogUtil.logToFile(day);

LogUtil.logToFile("year in ApptDA.=");
year = rsPats.getInt(6);
LogUtil.logToFile(year);

LogUtil.logToFile("hour in ApptDA.=");
hour = rsPats.getInt(7);
LogUtil.logToFile(hour);

LogUtil.logToFile("minute in ApptDA.=");
minute = rsPats.getInt(8);
LogUtil.logToFile(minute);

//create a appointment object here
appt = new Appointment(patName, pid, patReason, year,month,day,hour,minute);
LogUtil.logToFile("Value of appt in try statement.=");
LogUtil.logToFile(appt);

//return appt;
LogUtil.logToFile("Now Leaving try statement.");
}//end try

catch (ClassNotFoundException nf){
LogUtil.logToFile(nf.getMessage());
System.out.println(nf.getMessage());
nf.printStackTrace();
}

catch(SQLException s){
LogUtil.logToFile(s.getMessage());
System.out.println(s.getMessage());
s.printStackTrace();
}

catch(Exception e){
LogUtil.logToFile(e.getMessage());
System.out.println(e.getMessage());
e.printStackTrace();
}
LogUtil.logToFile("Sending appt back.");
LogUtil.logToFile("Now leaving searchApptsDB, after Trycatch statement.");

return appt;

}//end Appointment search()

Now for the contents of my log file. My logUtil, prints out the value of each of the variables in the searchApptsDB() method. I noticed that it doesnt print out the values of the variables from the database.


This is the value of searchID before accessing the database.
1
SearchAppt before DA
business.Appointment@b03be0
Now in searchApptsDB, before Trycatch statement.
Now in try statement
SQL Statement.=
Select * from appts
Value of Connection con.=
sun.jdbc.odbc.JdbcOdbcConnection@113a53d
Value of Statement stmt =
sun.jdbc.odbc.JdbcOdbcStatement@c5495e
Value of ResultSet rsPats =
sun.jdbc.odbc.JdbcOdbcResultSet@53fb57
Value of ResultSetMetaData rsmPats =
sun.jdbc.odbc.JdbcOdbcResultSetMetaData@19a32e0
pid in ApptDA.=
[Microsoft][ODBC Driver Manager] Invalid cursor state
Sending appt back.
Now leaving searchApptsDB, after Trycatch statement.
The try statement, searchAppt after returning from DA.
null
search appt null if statement
null



For those of you that would rather run the code yourself, I have included an updated version of my code in this post.

[ddmeightball]

Ok, i think I know where that Invalid Cursor state error is coming from, but I don tknow why I am getting it.

According to the log.txt file, its gets to the LogUtil.logToFile("pid in ApptDA.="); in my method and it just skips getting hte information from the database, therefor thats why I keep getting redirected to my Error.jsp assignment. But my question is though, why is this happening


pid = rsPats.getInt(1);
patName = rsPats.getString(2);
patReason = rsPats.getString(3);
month = rsPats.getInt(4);
day = rsPats.getInt(5);
year = rsPats.getInt(6);
hour = rsPats.getInt(7);
minute = rsPats.getInt(8);

[ddmeightball]

Ok, I figured it out.

I got the error pinned down to my ResultSet object when it trying to get the information from the database.

I was looking at

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#next()

and I read


Moves the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row; the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.


I then figured out that I was always before the first row of data in my database. I added rsPats.next() before I started to get information out of the ResultSet and it worked.

Its really nice to figure that out.

Below is a finished version of my code.

[ComputerJy]

glad you could help you ;)