package examples.jdbc.mssqlserver4;

import java.io.*;
import java.util.*;
import java.net.*;
import java.sql.*;

/**
 * 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 provided by any DBMS, 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.
 * <p>
 * Please note that this example uses a simple method for obtaining
 * result sets, update counts, and/or the output parameter values it may
 * generate. This is always possible with simple, known queries.
 * When executing complex or unknown possibly multi-statement SQL
 * or stored procedures, you should use the example in <tt>complexproc.java</tt>
 * to ensure that all results become available.
 *<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> 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 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.mssqlserver4.exsql</b></font>
 *
 *</ol>
 *
 * @author         Copyright (c) 1997-2000 by BEA Systems, Inc. All Rights Reserved.
 */

public class exsql
{
  public static void main(String argv[])
       throws Exception
  {
      Connection conn  = null;
      Driver myDriver = null;

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

    try
      {
	myDriver = (Driver)Class.forName("weblogic.jdbc.mssqlserver4.Driver").newInstance();
	conn = myDriver.connect("jdbc:weblogic:mssqlserver4", props );
	
        Statement stmt = conn.createStatement();
	
        // This is an example of SQL including a few extended SQL escapes.
        // 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 they can be nested.
	
        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" +
	  "--  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:\n " + 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) {;}
      }
  }
}

