Executing Java External Procedures

An application is typically developed by creating a class file in Java and packaging the Java application. Another way of building application programs is to create a Java object in a database, which does not need any additional compilation or packaging processes.

This chapter explains how to create a Java application program using the created Java external procedure.

Basic Java Application Program

Development Method

Users can develop application programs in the same way as they develop a Java application program.

The following is an example.

SQL> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaExtproc" AS
     public class SimpleMath {
         public static int findMax(int x, int y) { 
             if (x >= y) return x;
             else return y;
         }
     }
     /

With development of Java application programs, the following points must be noted.

• Static variables cannot be accessed. This can lead to unexpected behavior.

• To map a user Java class to a PSM function, users must define static class variables in the class.

Displaying Java Output

Users can check the output of Java application programs using the on screen DBMS_JAVA package. The following is an example.

SQL> CREATE OR REPLACE AND RESOLVE Java SOURCE NAMED "JavaExtproc" AS
     public class SimplePrint {
         public static void printMax(int x, int y) { 
         if (x >= y) System.out.println("Max="+x); 
         else System.out.println("Max="+y);
         }
     }
     /
Java Source 'JavaExtproc' created.

SQL> CREATE OR REPLACE PROCEDURE print_max(x pls_integer, y pls_integer) IS 
     LANGUAGE JAVA NAME 'SimplePrint.printMax(int, int)';
     /
Procedure 'PRINT_MAX' created.

SQL> set serveroutput on
SQL> call dbms_java.set_output(2000); 

PSM called.

SQL> BEGIN print_max(1, 2); END;
     / 
Max=2

PSM completed.

SQL>

For more information about the DBMS_JAVA package, refer to "Tibero tbPSM Reference Guide".

---

Internal JDBC Driver

Users can develop application programs using Internal JDBC Driver which resides in the Java object, as well as the JDBC interface. A Java external procedure can access the internal JDBC driver which uses the current transaction instead of using a general type of JDBC interface that makes a new connection with a database.

Connecting with a Database Server

The following example connects to a general JDBC interface.

Connection conn = DriverManager.getConnection("jdbc:tibero:localhost:9999:tibero",
                                              "tibero", "tibero");

The following example uses an internal JDBC driver.

Connection conn = DriverManager.getConnection("jdbc:default:connection:");

When an existing JDBC interface is used, the Java external procedure is not included in the current transaction, but initiated as a new session. However, when an internal JDBC driver is used, the Java external program is included in the current transaction, to commit or roll back any uncompleted transactions.

After connecting the database using the internal JDBC driver, users can access the database in the same way as they do through JDBC interface.

Example

The following example creates a Java application program using an internal JDBC driver.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "TestInternal" as
import java.sql.*; 
import java.io.*;

public class POManager
{
   public static void addCustomer (int custNo, String custName, String city) 
   throws SQLException
   {
       String sql = "INSERT INTO Customers VALUES (?,?,?)"; 
       try
       {
         Connection conn = DriverManager.getConnection("jdbc:default:connection:");
       
         PreparedStatement pstmt = conn.prepareStatement(sql); 
         pstmt.setInt(1, custNo);
         pstmt.setString(2, custName); 
         pstmt.setString(3, city); 
         pstmt.executeUpdate(); 
         pstmt.close();
       }
       catch (SQLException e) { 
          System.err.println(e.getMessage());
       }
   }
}