Jdbc

[Curtis Bridges]

I need some help. I'm trying to connect to a database I created in MS ACCESS the file name is db challenge.mdb
would somebody heip me out ? Please.
I'm getting 3 errors on lines 44,47,52 saying cannot find symbol variable rowSet
1 error on line 26 that say's cannot find symbol method JdbcRowSetImpl()
1 error on line 28 that say's cannot find symbol method setUserName(java.lang.String)

 
   import java.sql.ResultSetMetaData;
   import java.sql.SQLException;
   import javax.sql.rowset.JdbcRowSet;
   import com.sun.rowset.JdbcRowSetImpl;
   
  
  
  public class JdbcRowSetTest
  {
      //JDBC database URL, username and password
      static final String DRIVER = "com.mysql.jdbc.Driver";
      static final String DATABASE_URL = "jdbc:mysql://localhost/books";
      static final String USERNAME = "jhtp7";
      static final String PASSWORD = "jhtp7";
      
      
      public JdbcRowSetTest()
      {
      
      //connect and query database
      try
      {
      Class.forName(DRIVER);
      
      // specify properties
       JdbcRowSet rowset = JdbcRowSetImpl(); 
       rowset.setUrl (DATABASE_URL);
       rowset.setUserName(USERNAME);
       rowset.setPassword (PASSWORD);
       rowset.setCommand ("SELECT * FROM authors");
       rowset.execute();
       
       // process query results
       ResultSetMetaData metaData = rowset.getMetaData();
       int  numberOfColumns = metaData.getColumnCount();
       System.out.println(" Authors  Table of Books Database");
       
       // display rowset header
       for ( int i = 1; i <= numberOfColumns; i++)
        System.out.printf("hhh",metaData.getColumnName(i));
        System.out.println ();
        
       //display each row
       while( rowSet.next)
       {
       for ( int i = 1; i <= numberOfColumns; i++)
        System.out.printf("hhh",rowSet.getObject(i));
        System.out.println ();
       } //end while
       
       // close
       rowSet.close();
      }//end try
      catch ( SQLException sqlException )
      {
      
        sqlException.printStackTrace();
          System.exit(1);
      }//end catch
      catch(ClassNotFoundException classNotFound)
      {
       classNotFound.printStackTrace();
            System.exit(1);
      }//end catch
        } // end DisplayAuthors constructor
      
      // Launch
         public static void main( String args[] )
         {
         JdbcRowSetTest appication = new JdbcRowSetTest();
         };//end main
         }//end class JdbcRowSetTest
         
 

[Rob Spoor]

First of all, the variable is called rowset, not rowSet

Second of all, don't do it this way! Whenever using JDBC, you should do the following:

// load the class
Class.forName(DRIVER);
// get a connection object
Connection con = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
// get a statement
Statement stmt = con.createStatement();
// NOTE: also check out c.prepareStatement!
 
// execute the statement
ResultSet rs = stmt.executeQuery("SELECT * FROM authors");
// get meta data; optionally
ResultSetMetaData md = rs.getMetaData();
// iterate over the results
while (rs.next()) // with parentheses!
{
}
// clean up. ALWAYS clean up. Preferrably in a finally clause
rs.close();
stmt.close();
con.close();
// of course, don't close what you still need (e.g. the connection)

[Curtis Bridges]

Thank you, Rob,
I changed rowset as suggested. Inserted your code but am still getting errors on # 29
cannot find symbol variable DriverManager,cannot find symbol class Connection
# 31 cannot find symbol class Statement
# 35 cannot find symbol variable ResultSet
# 39 and # 42 cannot find symbol variable rs
Any ideas? New code below.

 
   import java.sql.ResultSetMetaData;
   import java.sql.SQLException;
   import javax.sql.rowset.JdbcRowSet;
   import com.sun.rowset.JdbcRowSetImpl;
   
  
  
  public class JdbcRowSetTest
  {
      //JDBC database URL, username and password
      static final String DRIVER = "com.mysql.jdbc.Driver";
      static final String DATABASE_URL = "jdbc:mysql://localhost/books";
      static final String USERNAME = "jhtp7";
      static final String PASSWORD = "jhtp7";
      
      
      public JdbcRowSetTest()
      {
      
      //connect and query database
      try
      {
      // load the class
Class.forName(DRIVER);
// get a connection object
Connection con = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
// get a statement
Statement stmt = con.createStatement();
// NOTE: also check out c.prepareStatement!
 
// execute the statement
ResultSet = stmt.executeQuery("SELECT * FROM authors");
// get meta data; optionally
ResultSetMetaData md = rs.getMetaData();
// iterate over the results
while (rs.next()) // with parentheses!
{

rs.close();
stmt.close();
}
      }//end try
      catch ( SQLException sqlException )
      {
      
        sqlException.printStackTrace();
          System.exit(1);
      }//end catch
      catch(ClassNotFoundException classNotFound)
      {
       classNotFound.printStackTrace();
            System.exit(1);
      }//end catch
        } // end DisplayAuthors constructor
      
      // Launch
         public static void main( String args[] )
         {
         JdbcRowSetTest appication = new JdbcRowSetTest();
         };//end main
         }//end class JdbcRowSetTest
         
 

[Henry Wong]

I need some help. I'm trying to connect to a database I created in MS ACCESS the file name is db challenge.mdb
would somebody heip me out ? Please.

If you are trying to connect to a access database named challenge, then why are you using the mysql driver and connecting to books?

static final String DRIVER = "com.mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/books";   

Henry

[Ulf Dittmer]

You can make it easier for yourself by using

import java.sql.*;

instead of

import java.sql.ResultSetMetaData;
import java.sql.SQLException;

That way you'll cover the ResultSet, Statement and other related classes as well.

[Rob Spoor]

The errors about DriverManager, Statement and ResultSet were because you didn't import them; Ulf's suggestion should solve that.

As for the rs part, you forgot it in your code:

ResultSet = stmt.executeQuery("SELECT * FROM authors");

[Curtis Bridges]

/*Thanks guys. We have this down to 1 error on line # 15 <identifier> expected.
I think it's something else though, but can't figure it out.
It's thowing an exception now.

java.lang.NoClassDefFoundError: JdbcRowSetTest
Exception in thread "main"

The compiler keeps wanting ";"on the last 2 lines
Please help.
Curt
*/

   import javax.sql.rowset.JdbcRowSet;
   import com.sun.rowset.JdbcRowSetImpl;
   import java.sql.*;
   
  
  public class JdbcRowSetTest
  {
      //JDBC database URL, username and password
      static final String DRIVER = "com,mysql.jdbc.Driver";
      static final String DATABASE_URL = "jdbc:mysql://localhost/db challenge.mdb";
      static final String USERNAME = "jhtp7";
      static final String PASSWORD = "jhtp7";
      
      
      public JdbcRowSetTest {
      
        
      
      //connect and query database
      try
      {
      Class.forName(ACCESS);
      
      // specify properties
         JdbcRowset rowset = JdbcRowSetImpl(); 
       rowset.setUrl (db challenge.mdb);
       rowset.setUserName(jhtp7);
       rowset.setPassword (jhtp7);
       rowset.setCommand ("SELECT * FROM authors");
       rowset.set.execute();
       
       // process query results
       ResultSetMetaData metaData = rowset.getMetaData();
       int  numberOfColumns = metaData.getColumnCount();
       System.out.println(" Authors  Table of db challenge Database:\n");
       
       // display rowset header
       for ( int i = 1; i <= numberOfColumns; i++)
        System.out.printf("db challenge.mdb",metaData.getColumnName(i)));
        System.out.println() ;
        
       //display each row
       while( rowSet.next)
       {
       for ( int i = 1; i <= numberOfColumns i++)
        System.out.printf("hhh"rowSet.getObject(i));
        System.out.println ();
       } //end while
       
       // close
       rowSet.close();
      }//end try
      catch(SQL Exception sql Exception)
      {
      sql Exception.printStackTrace();
      System.exit(1);
      }//end catch
      catch(ClassNotFoundException classNotFound)
      {
       classNotFound.printStackTrace();
            System.exit(1);
      }//end catch
        } // end DisplayAuthors constructor
      
      // Launch
         public static void main( String args[] )
         {
         JdbcRowSetTest appication = new JdbcRowSetTest();
        };//end main
        };/*end class JdbcRowSetTest*/ 
      
              

[Henry Wong]

static final String DRIVER = "com,mysql.jdbc.Driver";
static final String DATABASE_URL = "jdbc:mysql://localhost/db challenge.mdb";

This isn't going to work... The mysql driver will not work for access. You need to get an access driver from Microsoft -- or use the JDBC/ODBC bridge. As for the format of the URL, there is no standard -- you have to use the format as defined for the driver that you using. Using the mysql format isn't going to work for the access driver, once you install it.

Henry
[ October 02, 2007: Message edited by: Henry Wong ]

[Rob Spoor]

You can use the following:
Driver = "sun.jdbc.odbc.JdbcOdbcDriver"
Database URL = "jdbc: odbc: DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" followed by the filename. (Remove the spaces before odbc and DRIVER; I HATE these smileys!)
[ October 03, 2007: Message edited by: Rob Prime ]

[Joanne Neal]

Rob - there's a 'Disable smilies in this post' option on the reply page.

[Rob Spoor]

Thanks, I never saw it. Probably because it is just outside my screen