// sample code from MM.MySql documentation 
//    -- source: http://www.worldserver.com/mm.mysql/doc/mm.doc/book1.htm
//    -- modifications: slight formatting modifications
//                      + specific SQL SELECT statement
//                      + short additions from other sources
//                      + encapsulation in a separate class
//                        with separate methods

// need to set the CLASSPATH to .\;C:\...\mm.mysql.jdbc-1.2b

// Example 3-3. Using Statements and ResultSets

// Statements allow you to execute basic SQL queries and retrieve the results
// through the ResultSet class which is described later.
//
// To get a Statement object, you call the createStatement() method on the 
// Connection object you have retrieved via the DriverManager.getConnection(
// method.
//
// Once you have a Statement object, you can execute a SELECT query by calling
// the executeQuery(String SQL) method with the SQL you want to use.
//
// To update data in the database use the executeUpdate(String SQL) method. This
// method returns the number of rows affected by the update statement.
//
// If you don't know ahead of time whether the SQL statement will be a SELECT or
// an UPDATE/INSERT, then you can use the execute(String SQL) method. This
// method will return -1 if the SQL statement was a SELECT, or the number of 
// rows affected by the UPDATE/INSERT statement. If the statement was a SELECT
// statement, you can retrieve the results via the getResultSet() method.

// ResultSets represent the rows returned as an answer to a query. Once you have
// a ResultSet, you can get values for any field on the row, or move to the next
// row in the set.
//
// ResultSets are always positioned before the first row (if it exists), so you
// need to call ResultSet.next() and check if it returns true (you are on the
// next row), or false (there are no more rows left).

import java.sql.*;
import LoadDriver;
import MySqlConnect;

// Notice, do not import org.gjt.mm.mysql.*
// or you will have problems!

public class SQLStatement {

   // select owner column from table pet
   public static ResultSet sqlQuery(Connection c) {

      try {

         // Use the connection we've already created
         Statement stmt = c.createStatement();

         // run the SQL query
         ResultSet rs = stmt.executeQuery("SELECT owner FROM pet");
 
         // clean up after ourselves
         stmt.close();

         return rs;

      }
      catch (SQLException e) {
         System.out.println("SQLException: " + e.getMessage());
         System.out.println("SQLState:     " + e.getSQLState());
         System.out.println("VendorError:  " + e.getErrorCode());

         return null;

      }

   } // sqlQuery

   public static void main(String[] args) {

      ResultSet rs;
      Connection c;
      boolean isDriverLoaded = false;

      // load the MM.MySql JDBC driver
      isDriverLoaded = LoadDriver.load();
      if( !isDriverLoaded ) return;

      System.out.println("JDBC Driver Loaded");
      System.out.println();

      // establish the connection
      c = MySqlConnect.getMySqlConnection();
      if (c == null) return;

      System.out.println("Connection established");
      System.out.println();
 
      // issue the SQL query 
      rs = sqlQuery(c);
      if (rs == null) return;

      // print out the query results
      try {
        while (rs.next()) {
          System.out.println(rs.getString(1));
        }

        // Clean up after ourselves
        rs.close();
        // the statement has already been closed
        c.close();
      }
      catch (SQLException e) {
         System.out.println("SQLException: " + e.getMessage());
         System.out.println("SQLState:     " + e.getSQLState());
         System.out.println("VendorError:  " + e.getErrorCode());
      }

   } // main

} // class SQLStatement