package examples.jdbc.mssqlserver4;

import java.applet.*;
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.lang.*;
import java.net.*;
import java.sql.*;
import java.util.*;

/** This applet allows you to connect to a database server, enter SQL
 * queries, and see the results in a browser.  
 * 
 * <p> If the applet does not connect to your database, make sure you
 * change the 'connection' parameter in sqlBrowser.html to specify
 * your SQL Server host.  The connection URL tells the applet which
 * database on which host you're connecting to. Also make sure you
 * specify a valid login and password on your server.  <p> If you're
 * running the applet locally using AppletViewer, or similar, make
 * sure your CLASSPATH contains a path to the weblogic classes.  
 *<h3>To set up this example:</h3>
 *<ol>
 *<li>Set up your development shell as described in <a
 *href=Package-examples.jdbc.mssqlserver4.html>Package examples.jdbc.mssqlserver4</a>.
 *<p>
 *<li> In the <font face="Courier New" size=-1>sqlBrowser.html</font>
 *file, change the database URL, login, and password connection
 *parameters to correspond to your Microsoft SQL Server
 *configuration.  If you need more help, check
 *the section on connecting to a database in the Developers Guide, <a
 *href=../../../classdocs/API_jmsq4.html>Using
 *WebLogic jDriver for Microsoft SQL Server</a>.
 *
 *<p>
 *
 *<li>Compile this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ javac -d c:/weblogic/mssqlserver4/classes sqlBrowser.java</b></font>
 *<p>	
 *
 *<li>Run this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ appletviewer sqlBrowser.html</b></font>
 *
 *</ol>
 */

public class sqlBrowser extends Applet implements ActionListener
{
  private TextArea user = null;
  private TextArea server = null; 
  
  // The size of the text is limited to a few thousand characters on
  // most platforms. Therefore if you 'select * from largeTable' you
  // will only see part of the result.

  private Connection connection = null;

  /** 
   * Set up awt objects in the browser, get database connection
   * parameters from the .html file, and connect to the database.  
   */
  synchronized public void init() 
  { 
    setBackground(Color.white); 
    setLayout(new BorderLayout());
    Font courier = new Font("Courier",Font.PLAIN,12);
       
    Panel p1 = new Panel();
    Button ba, bb, bc;
    p1.add (ba = new Button("Execute Query"));
    p1.add (bb = new Button("Clear Query Text"));
    p1.add (bc = new Button("Clear Results"));
 
    ba.addActionListener(this);
    bb.addActionListener(this);
    bc.addActionListener(this);

    add("North", p1);
    
    LayoutManager p2mgr = new GridLayout(2,1);
    Panel p2 = new Panel(p2mgr);

    user = new TextArea();
    user.setEditable(true);
    user.setFont(courier);
    p2.add(user);
    
    server = new TextArea();
    server.setFont(courier);
    p2.add(server);
    add("Center", p2);

    Properties props = new Properties();
    props.put("user", "sa");
    props.put("password", "secret");
    props.put("server", "myHost:1433");
      
    Driver myDriver = null;

    try
        {
            myDriver = (Driver)Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
            connection = myDriver.connect("jdbc:weblogic:mssqlserver4", props);
        }
    catch(Exception se) 
        { 
            server.append("Connection failed because " + se + "\n"); 
        }
  }


  /** 
   * Handle button events, including executing SQL user enters and displaying results. 
   * @param iEvent, the button event.
   * @param iArgument, the button label. 
   */
  public void actionPerformed(ActionEvent e)
  {
    String command = e.getActionCommand();
    if      (command.equals("Clear Query Text"))    { user.setText(""); }
    else if (command.equals("Clear Results"))       { server.setText(""); }
    else if (command.equals("Execute Query"))       // Query button clicked
      {
        String sql = user.getText();         // read sql statement

        try
          {
            clearLog();                                             // empty server area
            Statement statement = connection.createStatement();     // create new statement
            logln("Query:\n\n" + sql);

            // execute this statement; returns true if first result is a result set
            boolean isResult = statement.execute(sql);              

            while (true)                // scan result sets and update
              {                                                       // counts from this statement
                logWarnings(connection.getWarnings());                // log warnings

                ResultSet resultSet = statement.getResultSet();     // get current result set
                int updateCount = statement.getUpdateCount();

                if (resultSet == null && updateCount == -1)
                  break;

                if(resultSet != null) {
                  logWarnings(statement.getWarnings());
                  logResultSet(resultSet);
                }
                else { 
                  logUpdateCount(updateCount);
                }
                statement.getMoreResults();          // move on to the next result set
              }
            statement.close();
          }
        catch(SQLException sqlEx)                                   // catch problems here
          {
            System.out.println("sqlBrowser.action - exception was raised " + sqlEx);
            logln("\n\nAn SQLException was raised:\n" + sqlEx);
          }
      }
  }

  /** 
   * Determine width of output column as maximum of the column data width or column name width. 
   * @param ResultSetMetaData, metadata returned with ResultSet.
   * @param columnIndex, the number of the column in the result set.
   */
  private int columnWidth(ResultSetMetaData metadata,int columnIndex) throws SQLException
  {
    String name = metadata.getColumnName(columnIndex);
    int nameLen = name != null ? name.length() : 0;

    return Math.max(metadata.getColumnDisplaySize(columnIndex),nameLen);

  }

  /** 
   * Resize a string to a specified length, truncating or padding
   * with spaces as needed.  
   * @param s, the string to resize.
   * @param len, the length of the result.
   */
  private String resizeString(String s,int len)
  {
    if(s == null) 
      s = "";
    
    int slen = s.length();
    if(slen > len)
      {
        return s.substring(0,len);
      
      }

    for(int i = len - slen ; i > 0 ; i--) 
      s += " ";

    return s;
  }

  /**
   * Process results and display in the applet panel. 
   *
   * @param resultSet, the ResultSet to display. 
   *
   */
  private void logResultSet(ResultSet resultSet) throws SQLException
  {
    logln("\n\n--------------------------------------------------------" +
          "----------------------------------------\n");

    boolean isVirgin = true;
    int columns = 0, widths [] = null;

    while(resultSet.next())
      {
        if(isVirgin)
          {
            ResultSetMetaData metadata = resultSet.getMetaData();
            columns = metadata.getColumnCount();
            String s1 = "";
            widths = new int[columns + 1];

            for(int i = 1 ; i <= columns ; i++)
              {
                widths[i] = Math.min(columnWidth(metadata,i), 40);  // wrap if column > 40
                s1 += resizeString(metadata.getColumnLabel(i),widths[i]) + " ";
              }
         
            s1 += "\n";
            for(int i = 1 ; i <= columns ; i++, s1 += " ")
              {
                for(int j = 0 ; j < widths[i] ; j++) 
                  s1 += "-";
              }

            logln(s1);

            isVirgin = false;
          }

        logWarnings(resultSet.getWarnings());

        boolean wrapping = false;
        String s2 = "";
        String remainder[] = new String[columns +1];
        for (int i = 1 ; i <= columns ; i++)
          {     
            Object obj = resultSet.getObject(i);
            String item = obj != null ? obj.toString() : "null";

            if(resultSet.wasNull()) 
              item = "null";
            if (item.length() > 40) 
              {
                int j;
                for (j = 39; j > 0 && ! Character.isWhitespace(item.charAt(j)); j--) {;}
                if (! Character.isWhitespace(item.charAt(j))) 
                  {
                    j = 39;
                  }
                remainder[i] = item.substring(j+1);
                item = item.substring(0,j);
                wrapping = true;
              }
            else
              remainder[i] = "";
         
            s2 += resizeString(item,widths[i]) + " ";
          }
        logln(s2);
        
        String item = "";
        while (wrapping) 
          {
            s2 = "";
            // Until we know otherwise, this is the last 
            // row to print for this result row
            wrapping = false;   
            for (int i = 1; i <= columns; i++)
              { 
                if (remainder[i].length() > 0)
                  {
                    item = remainder[i];
                    remainder[i] = "";
                    if (item.length() > 40)
                      {
                        int j;
                        for (j = 39; j > 0 && ! Character.isWhitespace(item.charAt(j)); j--) {;}
                        if (! Character.isWhitespace(item.charAt(j)))   // nowhere to wrap
                          j = 39;
                        remainder[i] = item.substring(j+1);
                        item = item.substring(0,j);
                        wrapping = true;
                      }
                  }
                else
                  item = "";
                s2 += resizeString(item, widths[i]) + " ";
              }
            logln(s2);
          }
      }
  }

  /**
   * Display the row count from the results. 
   *
   * @param updateCount, the update count retrieved from the ResultSet.
   */
  private void logUpdateCount(int updateCount) throws SQLException
  {

    logln("\n " + updateCount + " rows affected.");
  }

  /**
   * Process any warning messages included in the ResultSet.
   */
  private void logWarnings(SQLWarning warning) throws SQLException
  {
    if(warning != null)
      {
        logln("\n-----\n");
      }

    while(warning != null)
      {
        logln("\nSQLWarning - " + warning.getSQLState() + " " + warning.getMessage() + " " + 
              warning.getErrorCode());
        warning = warning.getNextWarning();
      }
  }

  String log = "";

  /**
   * Clear the server panel in the applet.
   */
  synchronized public void clearLog()
  {
    server.setText("");
  }

  /**
   * Append text to the server panel in the applet.
   *
   * @param s, the string to append to the server panel.
   */
  synchronized public void log(String s)
  {
    server.append(s);
  }

  /**
   * Append a string and newline to the server panel in the applet.
   *
   * @param s, the string to append to the server panel.
   */
  void logln(String s)
  {
    log(s + "\n");
  }
}



