p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Java Databases (http://p2p.wrox.com/forumdisplay.php?f=114)
-   -   help needed with a SQL select statement problem (http://p2p.wrox.com/showthread.php?t=2434)

wslyhbb August 4th, 2003 07:46 PM

help needed with a SQL select statement problem
 
I am using Java to access information in a MySQL database, I created the following PreparedStatement:
PreparedStatement selectCurrentValue = databaseConnection.prepareStatement("SELECT ? FROM cardCatalog WHERE callNumber = ?");
Then I do (where "title" is the name of a column in the table, and callNumber.getText(), callNumber is a JTextField variable, .getText() gets the text from the text field in my GUI:
selectCurrentValue.setString(1, "title");
selectCurrentValue.setString(2, callNumber.getText());
ResultSet rs = selectCurrentValue.executeQuery();
      Vector values = new Vector(1);
      // create a vector to hold the results
      Vector vector = new Vector(1);
      // iterate through records
      while(rs.next())
      {
         // make sure values is empty
         values.clear();
         // iterate through columns
         for(int i = 1; i <= 1; i++)
            values.add(rs.getString(i));
         // trim vector to size
         values.trimToSize();
         // add vector to vector
         vector.add(values);
      }
      // trim vector to size
      vector.trimToSize();
      String currentValue = ((Vector)resultVector.elementAt(0)).elementAt(0).t oString();
After all of this executes, currentValue is "title", but that is not what should happen, it should have retrieve the information from column named title in the database. However, I can execute:
PreparedStatement selectCurrentValue = databaseConnection.prepareStatement("SELECT title FROM cardCatalog WHERE callNumber = ?");
selectCurrentValue.setString(1, callNumber.getText());
and gets the current value out of the result set as I did above I get "Java Security Handbook" which is the value stored in column title in the database for the call number I enter. Therefore, the callNumber.getText(); statement is obviously executing correctly, but why the other statement returns whatever string I pass the first question mark, I do not understand. What do I need to do to get it to work the way it should? What am I doing wrong? The goal is that I could pass it the column "title" or "author" or any column I want to get the current value in the database of. If anyone can help, it would be greatly appreciated. Thanks in advanced.

jwenting August 14th, 2003 07:30 AM

Quite simple. You are returning a fixed string with the value "title" for each row in the database where your call number is the one you entered.

You should do something like:
    PreparedStatement selectCurrentValue = databaseConnection.prepareStatement("SELECT * FROM cardCatalog WHERE callNumber = ?");
    selectCurrentValue.setString(1, callNumber.getText());
    ResultSet rs = selectCurrentValue.executeQuery();
     String title = null;
     if(rs.next())
     {
        title = rs.getString(title);
     }
     else title = "";
     String currentValue = title;
    
no need for Vectors here, your code is overly complicated as you'll only ever want a single result (and expect no more than 1).


All times are GMT -4. The time now is 04:14 AM.

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