View Single Post
  #1 (permalink)  
Old September 7th, 2003, 11:47 PM
wslyhbb wslyhbb is offline
Registered User
 
Join Date: Aug 2003
Location: Zion, IL, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to wslyhbb Send a message via MSN to wslyhbb Send a message via Yahoo to wslyhbb
Default PreparedStatement not working properly

I have the following rows in a table:
callNumber userIDNumber dateCheckedOut dateReturned
005 Jaw 104001 2003-09-07 2003-09-07
005 Jaw 104001 2003-09-07 null
006 Lie 104001 2003-09-07 null

I then have the following method:
public void checkOut()
{
 // borrowerID is a JTextField
 String borrower = borrowerID.getText();
 try
 {
 // get connection
 Connection connection = getConnection();
 // create SQL prepared statements
 PreparedStatement getBorrowedBooks = connection.prepareStatement("SELECT callNumber, dateReturned FROM checkedOut WHERE userIDNumber = ?");
 // check to see how many books the user has currently checked out
 // where I entered 104001 into the borrowerID JTextField
 getBorrowedBooks.setString(1, borrower);
 // execute SQL query
 ResultSet rs = getBorrowedBooks.executeQuery();
 Vector values = new Vector(2);
 // create a vector to hold the results
 Vector vector = new Vector(10, 10);
 // iterate through records
 while(rs.next())
 {
  // make sure values is empty
  values.clear();
  // iterate through columns
  for(int i = 1; i <= numberOfColumns; i++)
   values.add(rs.getString(i));
  // trim vector to size
  values.trimToSize();
  // add values vector to vector
  vector.add(values);
 }
 // trim vector to size
 vector.trimToSize();
 System.out.println(vector);
 Statement stmt = connection.createStatement();
 ResultSet rs = stmt.executeQuery("SELECT callNumber, dateReturned FROM checkedOut WHERE userIDNumber = '104001'");
 Vector values = new Vector(2);
 // create a vector to hold the results
 Vector vector = new Vector(10, 10);
 // iterate through records
 while(rs.next())
 {
  // make sure values is empty
  values.clear();
  // iterate through columns
  for(int i = 1; i <= numberOfColumns; i++)
   values.add(rs.getString(i));
  // trim vector to size
  values.trimToSize();
  // add values vector to vector
  vector.add(values);
 }
 // trim vector to size
 vector.trimToSize();
 System.out.println(vector);
}

However, when I print out the results it returns three rows of the same thing, the same thing happens to be the last thing I entered.
It returns: [[006 Lie, null], [006 Lie, null], [006 Lie, null]]

The other odd thing is, if I execute the same thing with a Statement, the Statement returns the correct thing, where as the PreparedStatement does not. I think it must be some kind of cache issue, but why? and how do I go about clearing the cache?

If anyone has any ideas, please help. Thanks.

Otherwise, I guess the only alternative is to use the Statement by doing:
ResultSet rs = stmt.executeQuery("SELECT callNumber, dateReturned FROM checkedOut WHERE userIDNumber = " + borrower);