package examples.jdbc.oracle;

import java.io.*;
import java.util.*;
import java.net.*;
import java.sql.*;
import java.util.Properties;
import weblogic.common.*;

/**
 * This simple example shows how JDBC Extended SQL can be embedded
 * in standard SQL sent to the DBMS.  Extended SQL is part of the
 * JDBC spec, and provides a DBMS-neutral way of using the many
 * secondary functions that all DBMSes provide, but in a proprietary
 * syntax. The WebLogic JDBC drivers will convert extended SQL syntax
 * to the DBMS-specific syntax for the relevant function. Please see
 * our documentation for links to the complete Extended SQL specification.
 * <p>
 * This example is run on the command line and is output to
 * System.out.
 *
 *<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 connection parameters to correspond to your Oracle configuration.
 *If you need more help, check the section on connecting
 *to a database in the Developers Guide, <a
 *href=../../../classdocs/API_joci.html>Using WebLogic jDriver for Oracle</a>.
 *
 *<p>
 *
 *<li>Compile this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ javac -d %CLIENT_CLASSES% exsql.java</b></font>
 *<p>	
 *
 *<li>Run this example by executing the following command in your development shell:
 *
 *<p>
 *
 *<font face="Courier New"><b>$ java examples.jdbc.oracle.exsql</b></font>
 *
 *</ol>
 *
 * @author Copyright (c) 1996-2000 by BEA Systems, Inc.  All Rights Reserved.
 */
public class exsql {

  /**
   * In the main, we set properties for the connection, acquire a
   * Connection object, and then create the Extended SQL and
   * execute it in the Statement. The Extended SQL we use here
   * is an example of SQL that includes a few Extended SQL
   * escapes. Note that an Extended SQL element is enclosed by
   * {}. This example uses the function dayname, the function
   * now(), and a date, time, and timestamp escape. Note that
   * Extended SQL escapes can be nested.
   */
  public static void main(String argv[]) throws Exception {
    
    Connection conn  = null;
    
    try {
      java.util.Properties props = new java.util.Properties();
      props.put("user",     "scott");
      props.put("password", "tiger");
      props.put("server",   "DEMO");
      
      Driver myDriver = (Driver) Class.forName("weblogic.jdbc.oci.Driver").newInstance();
      conn = myDriver.connect("jdbc:weblogic:oracle", props);

      Statement stmt = conn.createStatement();
      
      String exsql =
        "select {fn dayname ({fn now()})},   -- Today               \n" +
        "           {d '1997-05-24'},  -- a date                    \n" +
        "           {t '10:30:29' },  -- a time                     \n" +
        "           {ts '1997-05-24 10:30:29.123'}, -- a timestamp  \n" +
        "          '{string data with { or } will not be altered'   \n" +
        "           from dual                                       \n" +
        "--{ Also note that you can safely include { and } in comments";
      
      System.out.println(" The Extended SQL:\n" + exsql + "\n\n becomes:\n");
      System.out.println(conn.nativeSQL(exsql));
      
      ResultSet rs = stmt.executeQuery(exsql);
      
      while (rs.next())
        System.out.println("Got: " + rs.getString(1) +
                           "\n "   + rs.getString(2) +
                           "\n "   + rs.getString(3) +
                           "\n "   + rs.getString(4) +
                           "\n "   + rs.getString(5) );
      
    }
    
    catch (Exception e) {
      e.printStackTrace();
    }
    
    finally {
      try {conn.close();}    catch (Exception e) {;}
    }
  }
}
