package examples.jdbc.odbc;

import java.util.*;
import java.sql.*;
import weblogic.common.*;

/**
 * This simple example shows how to select data and result set metadata
 * using WebLogic JDBC and the JDBC-ODBC bridge. To use the example, you 
 * must have an ODBC data source defined in the ODBC driver manager. On 
 * Windows, use the ODBC control panel to access the driver manager. On 
 * other platforms, check the documentation provided with your ODBC 
 * driver manager. 
 * <p>
 *<h3>To set up this example:</h3>
 *<ol>
 *<li>Set up your development shell as described in <a
 *href=../../../techstart/environment.html>Setting up your environment</a>.
 *
 *<p>
 *<li>Change the <font face="Courier New" size=-1><b>ODBC_DSN</b></font>
 *portion of the URL to match an ODBC Data Source Name (DSN) in your
 *ODBC environment.
 *
 *<p>
 *<li>Compile the Java file with the following command:
 *
 *<p>
 *<font face="Courier New"><b>$ javac -d %CLIENT_CLASSES% simpleselect.java</b></font>
 *
 *<p>
 *
 *<li>Start the WebLogic Server.  
 *
 *<p>
 *<li>Run this program from a command line, with:
 *
 *<p>
 *<font face="Courier New"> $ <b>java examples.jdbc.odbc.simpleselect</b></font>
 *</ol>
 *
 *
 * @author       Copyright (c) 1997-2000 by BEA Systems, Inc. All Rights Reserved.
 */

public class simpleselect {

  /**
   * Runs this example from the command line.
   */
  public static void main (String args[]) {

    // Change this URL to point to your ODBC data source  
    String url   = "jdbc:odbc:ODBC_DSN";
    String query = "SELECT * FROM emp"; 
    
    T3Client   t3   = null;
    Connection conn = null;
    try {

      // Change this property if necessary to the URL of your WebLogic
      // Server
      t3 = new T3Client("t3://localhost:7001");
      t3.connect();

      // Change these properties to login to your own database
      Properties dbprops = new Properties();
      dbprops.put("user",                  "scott");
      dbprops.put("password",              "tiger");
      
      Properties t3props = new Properties();
      t3props.put("weblogic.t3",           t3);
      t3props.put("weblogic.t3.dbprops",   dbprops);
      t3props.put("weblogic.t3.driver",    "sun.jdbc.odbc.JdbcOdbcDriver");
      t3props.put("weblogic.t3.url",       url);
      t3props.put("weblogic.t3.cacheRows", "10");
      
      Class.forName("weblogic.jdbc.t3.Driver");
      conn = DriverManager.getConnection("jdbc:weblogic:t3", t3props);
      
      checkForWarning(conn.getWarnings());
      
      DatabaseMetaData dma = conn.getMetaData();
      
      System.out.println("Connected to " + dma.getURL());
      System.out.println("Driver       " + dma.getDriverName());
      System.out.println("Version      " + dma.getDriverVersion());
      System.out.println("");
      
      // Create a Statement object so we can submit
      // SQL statements to the driver
      Statement stmt = conn.createStatement();
      
      // Submit a query, creating a ResultSet object
      ResultSet rs = stmt.executeQuery(query);
      
      // Display all columns and rows from the result set
      dispResultSet (rs);
      
      // Always close ResultSets and Statements when you 
      // have finished using them
      rs.close();
      stmt.close();
      
    }
    catch (SQLException ex) {
      
      // A SQLException was generated. Catch it and display the error
      // information. Note that there could be multiple error objects
      // chained together.
      System.out.println ("\n*** SQLException caught ***\n");
      
      ex.printStackTrace();
      
      while (ex != null) {
	System.out.println ("SQLState: " + ex.getSQLState ());
	System.out.println ("Message:  " + ex.getMessage ());
	System.out.println ("Vendor:   " + ex.getErrorCode ());
	ex = ex.getNextException ();
	System.out.println ("");
      }
    }
    catch (java.lang.Exception ex) {
      
      // Got some other type of exception. Dump it.
      ex.printStackTrace ();
    }
    finally {
      try {conn.close();}    catch (Exception e) {;}
      try {t3.disconnect();} catch (Exception e) {;}
    }
  }
  
  private static boolean checkForWarning (SQLWarning warn)
       throws SQLException
  {
    boolean rc = false;
    
    // If a SQLWarning object was given, display the warning
    // messages. Note that there could be multiple warnings chained
    // together.
    if (warn != null) {
      System.out.println ("\n *** Warning ***\n");
      rc = true;
      while (warn != null) {
	System.out.println ("SQLState: " + warn.getSQLState ());
	System.out.println ("Message:  " + warn.getMessage ());
	System.out.println ("Vendor:   " + warn.getErrorCode ());
	System.out.println ("");
	warn = warn.getNextWarning ();
      }
    }
    return rc;
  }
  
  private static void dispResultSet (ResultSet rs) throws SQLException {
    int i;
    ResultSetMetaData rsmd = rs.getMetaData ();
    int numCols = rsmd.getColumnCount();
    
    for (i=1; i<=numCols; i++) {
      if (i > 1) System.out.print(",");
      System.out.print(rsmd.getColumnLabel(i));
    }
    
    System.out.println("");
    
    boolean more = rs.next ();
    while (more) {
      for (i=1; i<=numCols; i++) {
	if (i > 1) System.out.print(",");
	System.out.print(rs.getString(i));
      }
      System.out.println("");
      more = rs.next ();
    }
  }
}
