5 Replies Latest reply: Aug 7, 2007 12:22 PM by masijade RSS

    please help me solve PreparedStatement problem

    807600
      hello.
      i'm having a problem running a PreparedStatement program (shown below). when i enter the name of a product in the text field and click on the check box, i get the following SQLExceptions. there is data in the VideoLibrary database. how can i get rid of these SQLExceptions?

      ----jGRASP exec: java ViewProductByName

      java.sql.SQLException: No data found
      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.SQLGetDataInteger(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcResultSet.getObject(Unknown Source)
      at ViewProductByName.selectProductByName(ViewProductByName.java:115)
      at ViewProductByName$1.actionPerformed(ViewProductByName.java:54)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.JToggleButton$ToggleButtonModel.setPressed(Unknown Source)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at javax.swing.JComponent.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)

      ----jGRASP: process aborted by user.
      import java.awt.*;
      import java.awt.event.*;
      import javax.swing.*;
      import java.sql.*;
       
      public class ViewProductByName extends JFrame{
         JPanel pnlBox = new JPanel();
         JPanel pnlBody = new JPanel();
         JPanel pnlFooter = new JPanel();
         JTextField vp = new JTextField(25);
         JCheckBox cvp = new JCheckBox("View Product(s)");  
         JButton rvp = new JButton("Return to View Product Details Menu");
         JLabel lbl = new JLabel("Enter Query:");
         JTextArea jta = new JTextArea();
         Container contentpane;
         Connection db;
         PreparedStatement ps;
           
         public ViewProductByName(){
            super("View Product By Name");
            contentpane = getContentPane();
            contentpane.setLayout(new BorderLayout());
            pnlBox.add(lbl);
            pnlBox.add(vp);
            pnlBox.add(cvp);
            jta.setFont(new Font("Serif", Font.PLAIN, 12));
            jta.setLineWrap(true);
            jta.setWrapStyleWord(true);
            jta.setEditable(false);
            JScrollPane jsp = new JScrollPane(jta);
            Dimension d = new Dimension();
            d.setSize(500, 500);
            jsp.setPreferredSize(d);
            pnlBody.add(jsp, BorderLayout.CENTER);
            pnlFooter.add(rvp);
            contentpane.add(pnlBox,BorderLayout.NORTH);
            contentpane.add(pnlBody,BorderLayout.CENTER);
            contentpane.add(pnlFooter,BorderLayout.SOUTH);
            pack();
            setLocationRelativeTo(null);
            setVisible(true);
                
            cvp.addActionListener(new ActionListener(){
               public void actionPerformed(ActionEvent e){
                  makeConnection();
                  setHostURL();
                  selectProductByName();
                  closeDB();
               }
            });
                
            rvp.addActionListener(new ActionListener(){
               public void actionPerformed(ActionEvent e){
                  setVisible(false);
                  //ViewProductDetails vpd = new ViewProductDetails();
                  //vpd.setVisible(true); 
               }
            });
         } 
           
         public void makeConnection(){ 
            try{ 
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
            } 
            catch(Exception e){ 
               System.out.println("Problem loading the driver"); 
            } 
         }
           
         public void setHostURL(){ 
            String url = "jdbc:odbc:VideoLibrary";
                
            try{ 
               db = DriverManager.getConnection(url,"",""); 
            } 
            catch(Exception e){ 
               System.out.println("Could not initialise the database"); 
               e.printStackTrace(); 
            } 
         }
           
         public void selectProductByName(){
            String n = vp.getText();
            
            try{
               String q = "SELECT ProductNumber, Name, RentalPrice, AgeCategory, Type, Year FROM Product WHERE Name = ?";
               ps = db.prepareStatement(q);
               DatabaseMetaData dbmd = db.getMetaData(); 
               ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
               ps.setString(1, n);
               rs = ps.executeQuery();
               ResultSetMetaData rsmd = rs.getMetaData();
            
               for(int i = 1; i <= rsmd.getColumnCount(); i++){
                  jta.append(rsmd.getColumnName(i) + "    ");
               }
               jta.append("\n");
       
               while(rs.next()){
                  int productNumber = rs.getInt(1);
                  String name = rs.getString(2);
                  float rentalPrice = rs.getFloat(3);
                  String ageCategory = rs.getString(4);
                  String type = rs.getString(5);
                  int year = rs.getInt(6);
                     
                  for(int i = 1; i <= rsmd.getColumnCount(); i++){
                     jta.append(rs.getObject(i) + "     ");
                  }
                  jta.append("\n");
               }
            }
            catch(SQLException ea){
               ea.printStackTrace();
            }
         }
           
         public void closeDB(){ 
            try{  
               if(ps != null){ 
                  ps.close(); 
               } 
               if(db != null){ 
                  db.close(); 
               } 
            } 
            catch(Exception e){ 
               System.out.println("Could not close the current connection"); 
               e.printStackTrace(); 
            } 
         }
            
         public static void main(String[] args){
            new ViewProductByName();
         }
      }
        • 1. Re: please help me solve PreparedStatement problem
          807600
          should i use JButton instead of JCheckBox?
          • 2. Re: please help me solve PreparedStatement problem
            807600
            Hi,

            In selectProductByName() I can see that you are using same result set
            for executing the query and databasemetadata.
            ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
                     ps.setString(1, n);
                     rs = ps.executeQuery();
                     ResultSetMetaData rsmd = rs.getMetaData();
            After executing the prepared statement you are using the result set to get the metadata. But the result set now will contain the result of the SQL query.

            Hope it helps

            Thanks
            • 3. Re: please help me solve PreparedStatement problem
              807600
              Hi,

              In selectProductByName() I can see that you are using
              same result set
              for executing the query and databasemetadata.
              ResultSet rs = dbmd.getTables(null,
              null, null, new String[]{"TABLE"});
              ps.setString(1, n);
              rs = ps.executeQuery();
              ResultSetMetaData rsmd =
              rs.getMetaData();
              After executing the prepared statement you are using
              the result set to get the metadata. But the result
              set now will contain the result of the SQL query.

              Hope it helps

              Thanks
              There is nothing wrong with this; in fact it's the way you would ordinarilly do it to capture the column names, etc.
              • 4. Re: please help me solve PreparedStatement problem
                807600
                This code here:
                try{
                  String q = "SELECT ProductNumber, Name, RentalPrice, AgeCategory, Type, Year FROM Product WHERE Name = ?";
                  ps = db.prepareStatement(q);
                  DatabaseMetaData dbmd = db.getMetaData(); 
                // Add this:
                  System.out.println("Got the dbmd");
                  ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
                // And this:
                  System.out.println("Got the rs from the 'getTables' method");
                  ps.setString(1, n);
                  rs = ps.executeQuery();
                // And this:
                  System.out.println("rs is null? ___"+ (rs == null));
                  ResultSetMetaData rsmd = rs.getMetaData();
                // And this:
                  System.out.println("Got the rsmd");
                • 5. Re: please help me solve PreparedStatement problem
                  masijade
                  Please do not cross post. Stick with the thread in the JDBC forum. Also, when you read the resultset, you read it once, all the way through, assigning the values to variables, then read it again to insert it into the JTextArea. I don't believe you can read the same field twice when using the JDBC-ODBC Bridge (at least not when using MS Access).