Sample Applications

Applet Sample

This section contains a generic sample applet that does not use Oracle-specific features. Both the SQLJ source code and the HTML page are included here, but Java source code for the user interface is not included. The demo in this section is located in the following directory:

[Oracle Home]/sqlj/demo/applets

This directory also includes source for the user interface and a version of the applet that uses Oracle-specific types.

For information about running the generic applet, see the Applet.readme file in the directory noted above. For information about running the Oracle-specific applet, refer to the AppletOracle.readme file.

For general discussion of SQLJ in applets, see "Running SQLJ in Applets".

Generic Applet HTML Page--Applet.html

This section contains the HTML page for the generic applet.

<html>
<head>
<title>SQLJ Applet</title>
</head>
<body>

<h1>SQLJ Applet</h1>

This page contains an example of an applet that uses SQLJ and
Oracle's Thin JDBC driver.<p>

<b>Note:</b>
This applet requires Netscape 4.0X with the JDK1.1 patch, or Netscape 4.5
or later, or Microsoft Internet Explorer 4.0 or later. 
<p>

The source code for the applet is in
<a href="AppletMain.sqlj">AppletMain.sqlj</a>

The source code for the applet's user interface is in
<a href="AppletUI.java">AppletUI.java</a>


<hr>
<!-- Properties of the APPLET tag:
      codebase="<the location of your classfiles or archives>"
      archive="<name of archive file>"

      Below we assume that you have a directory "dist" off of
      the root of your HTML server, and that you have jar-ed up
      the SQLJ runtime, JDBC thin driver, and Applet classes into
      the archive Applet.jar in this directory.

     Applet PARAMeters: adjust these to reflect your settings
      sqlj.url      - the JDBC URL
                      for example "jdbc:oracle:thin:@localhost:1521:orcl"
      sqlj.user     - the user name
      sqlj.password - the user password
 -->
<APPLET code="AppletMain.class"
        codebase="dist"
        archive="Applet.jar"
        width=640 height=480>
<PARAM name="sqlj.url"      value="jdbc:oracle:thin:@<hostname>:<port>:<oracle_
sid>">
<PARAM name="sqlj.user"     value="scott">
<PARAM name="sqlj.password" value="tiger">
</APPLET>

Generic Applet SQLJ Source--AppletMain.sqlj

This section contains the SQLJ source code for the generic applet. If you have access to the demo/applets directory and compare the Oracle-specific source (AppletOracle.sqlj) to this generic source, you will note that the only significant differences are as follows:

The generic applet declares an iterator, EmpIter, that uses only the Java String type. The Oracle-specific applet declares an iterator, EmpOraIter, that uses oracle.sql.* types.
The Oracle-specific applet must use the stringValue() method of the oracle.sql.NUMBER class to display numeric data.

/*
 * This applet extends the AppletInterface class that contains the
 * user interface component of the applet.
 *
 * This applet connects to a database to select and display
 * employee information.  It will also delete a select employee
 * from the database.
 */

// SQLJ-specific classes
import java.sql.SQLException;
import java.sql.DriverManager;
import sqlj.runtime.ExecutionContext;
import sqlj.runtime.ref.DefaultContext;
import oracle.jdbc.driver.OracleDriver;
import oracle.sqlj.runtime.Oracle;

// Event handling classes
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;


public class AppletMain extends AppletUI
                        implements ActionListener
{ 

  // Declare a named iterator with several columns from the EMP table

  #sql public static iterator
       EmpIter(String empno, String ename, String job, String sal, String comm);


  // Applet initialization

  private DefaultContext m_ctx = null;

  public void init ()
  { 

    // Create the User Interface

    super.init();


    // Activate the buttons

    Query_button.addActionListener(this);
    Query_button.setActionCommand("query");

    Delete_button.addActionListener(this);
    Delete_button.setActionCommand("delete");


    // Open a connection to the database

    if (m_ctx == null)
    {
      // Connect to the database
      String url      = null;
      String user     = null;
      String password = null;
      try
      {
        url      = getParameter("sqlj.url");
        user     = getParameter("sqlj.user");
        password = getParameter("sqlj.password");
      }
      catch (NullPointerException exn) { }; // permit to call as an application

      try
      {
         if (   url==null || url.equals("") 
             || user==null || user.equals("")
             || password==null || password.equals(""))
         {
           // If the connect properties are not passed as parameters from the
           // HTML file, we pull them out of the connnect.properties resource.
           output.append("Connecting using the connect.properties resource\n");
           m_ctx = Oracle.getConnection(getClass(),"connect.properties");
         }
         else
         {
           output.append("Connecting using the PARAMeters in the HTML file\n");
           output.append("User " + user + " to " + url + "\n");

           DriverManager.registerDriver(new OracleDriver());
           m_ctx = Oracle.getConnection(url, user, password);
         }
         output.append("Connected\n");
      }
      catch (SQLException exn)
      {
         output.append("A SQL exception occurred: "+exn.getMessage()+"\n");
      }
    }
    else
    {
      output.append("Re-using connection.\n");
    }
  }


  // Perform the work

  public void actionPerformed(ActionEvent ev)
  {
    String command = ev.getActionCommand();

    try
    {

      if (command.equals("query"))
      { 
         int numRecords = 0;

         EmpIter ecur;

         // Clear the output area
         output.setText("");

         String x = query_name_field.getText();
         if (x==null || x.equals("") || x.equals("%"))
         {
            // Execute the query
            output.append("Executing: SELECT * FROM EMP\n");
            #sql [m_ctx] ecur = { SELECT * FROM EMP };
            while (ecur.next ())
            {
               output.append(ecur.empno() + "     " + ecur.ename() + "     "
                             + ecur.job() + "     $" + ecur.sal() + "\n");
               numRecords++;
            }
         }
         else
         {
            output.append("Executing: SELECT * FROM EMP WHERE ENAME = '"+
                                query_name_field.getText() + "'\n\n");
            #sql [m_ctx] ecur = { SELECT * FROM EMP 
                         WHERE ENAME = :(query_name_field.getText()) };
            while (ecur.next())
            {
               output.append("Employee's Number:   " + ecur.empno() + "\n");
               output.append("Employee's Name:     " + ecur.ename() + "\n");
               output.append("Employee's Job:      " + ecur.job() + "\n");
               output.append("Employee's Salary:   " + ecur.sal() + "\n");
               output.append("Employee's Commison: " + ecur.comm() + "\n");
               numRecords++;
            }
         }

         // we're done
         output.append(numRecords + " record"+( (numRecords==1)?"":"s" )+
                       " retrieved.\n");
         query_name_field.setText("");

         // ensure that iterator is closed
         ecur.close();
      } 
      else if (command.equals("delete"))
      {
         output.setText("");


         // Use an execution context to get an update count
         ExecutionContext ectx = new ExecutionContext();
         #sql [m_ctx, ectx]
             { DELETE FROM EMP WHERE ENAME = :(delete_name_field.getText()) };
         int numDeleted = ectx.getUpdateCount();

         if (numDeleted==1)
         {
           output.append("Deleted employee "+delete_name_field.getText()+ ".");
         }
         else if (numDeleted==ExecutionContext.EXCEPTION_COUNT)
         {
           output.append("An exception occurred during deletion.");
         }
         else
         {
           output.append("Deleted "+numDeleted+" employees.");
         }
         
         delete_name_field.setText("");
      }
    }
    catch (SQLException e)
    {
        // Report the error
        output.append("A SQL exception occurred:\n"+e.getMessage () + "\n");
    }
  }

  // it is important to rollback (or commit) at the end of the day, and
  // not leave the connection dangling

  public void stop()
  {
    if (m_ctx != null)
    {
      try
      {
         System.out.println("Closing the applet.");
         #sql [m_ctx] { ROLLBACK };
         // or, if you prefer: #sql [m_ctx] { COMMIT }; 

         m_ctx.close(); 
      }
      catch (SQLException exn) { }
      finally { m_ctx = null; }
    }
  };

  // Provide a main entry point so this works both, as an applet, and as
  // an application.
  public static void main(String[] args)
  {
    AppletFrame f = new AppletFrame(new AppletMain(), 600, 350);
  }

}