Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Java > Other Java > Java Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Java Databases Discussion specific to working with Java Databases. For other Java topics, please see related Java forums. For database discussions not specific to Java, please see the Database category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Java Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 7th, 2003, 11:47 PM
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);

  #2 (permalink)  
Old September 8th, 2003, 10:41 PM
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

To everyone that read and will read this post. I posted this both here and on java.sun.com's forum, and I got the following reply on java.sun.com's forum, which was the problem and fixed the problem, so I thought I would post the answer here to for those it may help. The following is the reply I received:

"The problem is not in JDBC, it's your handling of Vectors. Your are creating a Vector values, adding the reference to it to Vector vector, changing the content of Vector values and then again adding a reference to it to Vector vector.

Now you have two references to (the same) Vector values in Vector vector.

The solution is creating a new Vector before adding it. In other words, change:
values.clear();
to
values = new Vector();
Hope that helps!"
  #3 (permalink)  
Old September 9th, 2003, 10:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , .
Posts: 175
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Damn vectors!

Still, I'm a little confused...

You said that the code for the second select statement was producing the correct output. How can this be if you were using the same code (other than the prepared statement) to process each loop?

I've obviously missed something obvious, so I just thought I'd ask.

Thanks in advance

Martyn
  #4 (permalink)  
Old September 9th, 2003, 01:08 PM
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

Er, well, I'm sorry, when I was testing the Statement I was outputting the results directly out to the screen instead of putting it into vectors first. So, actually that second while loop shouldn't be in my example. I guess if I had outputted the PreparedStatement to the screen before putting it in the vectors I may have realized that my vectors were the issue. My processing the ResultSet is actually a seperate method in my code but I was just trying to simplify things. I have used the method in other places without any problems, but I guess that was because I never ran across the issue, or at least never tried to do the same type of thing I was trying to do when I ran across the problem. Therefore, I thought that section of code was correct, and did not think there was a problem there, in which case there really was.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Web service not working properly in IIS Abhinavnaresh ASP.NET 2.0 Professional 1 April 10th, 2008 08:53 AM
Form's calculated field not working properly PaulJH Access 11 March 5th, 2007 09:52 AM
ch 8 code is not working properly Dev_gh78 C# 1 August 4th, 2006 12:16 AM
Banner ad values not working properly. malhyp Dreamweaver (all versions) 3 January 17th, 2006 03:42 AM



All times are GMT -4. The time now is 05:53 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.