24.8. Advanced EnterpriseDB JDBC Connector Functionality

24.8.1. Getting results based on a cursor

By default the EnterpriseDB JDBC Connector driver collects all the results for the query at once. This can be inconvenient for large data sets so the JDBC driver provides a means of basing a ResultSet on a database cursor and only fetching a small number of rows.

This ensures that a small number of rows are cached on the client side of the connection and when exhausted the next block of rows is retrieved by repositioning the cursor.

24.8.2. Example

Changing code to cursor mode is as simple as setting the fetch size of the Statement to the appropriate size. Setting the fetch size back to 0 will cause all rows to be cached (the default behaviour). The following code listing shows just how to do this:

	// make sure autocommit is off
	db.setAutoCommit(false);
	Statement st = db.createStatement();

	// Turn use of the cursor on.
	st.setFetchSize(50);
	ResultSet rs = st.executeQuery("SELECT * FROM emp");
	while (rs.next()) {
	   System.out.print("a row was returned.");
	}
	rs.close();

	// Turn the cursor off.
	st.setFetchSize(0);
	rs = st.executeQuery("SELECT * FROM emp");
	while (rs.next()) {
	   System.out.print("many rows were returned.");
	}
	rs.close();

	// Close the statement.
	st.close();
	      

24.8.3. Using PreparedStatements

Sometimes, it is more convenient and/or efficient to use a PreparedStatement object for sending SQL statements to the DBMS. The main advantage of using a PreparedStatement over a simple Statement is that if you need to use the same, or similar query with different parameters multiple times, the statement can be compiled and optimized by the DBMS just once. Whereas if we use the same SQL statement with a normal Statement it is compiled each time we use the same SQL statement.

PreparedStatement are also created with a Connection method. The following snippet shows how to create a parameterized SQL statement with two input parameters:

     PreparedStatement prepareInsertEmp = con.prepareStatement
     ("INSERT INTO emp(empno,ename) VALUES(?, ?)");
    

Before we can execute a PreparedStatement, we need to supply values for the parameters. You can set these values before each execution of the prepared statement.

Continuing the above example, we would write:

 prepareInsertEmp.setObject(1,new Integer(c.readLine()));         
 prepareInsertEmp.setObject(2,c.readLine().toString());    

The following code shows the complete source code for invoking a PreparedStatement that accepts an employee number and employee name and inserts that employee information as a new row in the emp table.

import java.sql.*;
public class PreparedStatementTest
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
    String message = "/********************************************************/ \r\n";
          message += "*                                                        * \r\n";
          message += "*  The sample program will take employee number and    *\r \n";
          message += "*  employee name and insert a new record in EMP table    * \r\n";
          message += "*                                                        * \r\n";
          message += "/********************************************************/ ";
    try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      PreparedStatement prepareInsertEmp = con.prepareStatement(
      "INSERT INTO emp(empno,ename) VALUES(?, ?)");
  
      System.out.println(message);
      System.out.print("\r\nEmployee Number : ");
      prepareInsertEmp.setObject(1,new Integer(c.readLine()));
           
      System.out.print("Employee Name : ");
      prepareInsertEmp.setObject(2,c.readLine().toString());
      prepareInsertEmp.execute();
      System.out.println("\r\nThe procedure successfully executed.");
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");                                                                                 
      System.out.println("See full details below.\r\n");
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
 }

	      

24.8.4. Executing Stored Procedures

A stored procedure is simply a module that is stored in the database and is written in EnterpriseDB's SPL (Superset Procedural language) programming language. A stored procedure can optionally return a value, and it may have input and/or output parameters.

A stored procedure is tightly coupled with EnterpriseDB and consists of database access commands (SQL), plus control statements and data structures to manipulate the data obtained from the database. This is especially useful when extensive manipulation of the data is required either before it is sent to the database or before it is sent to the client or when there is no client involved such as in a batch program.

24.8.5. Invoking Stored Procedures

A CallableStatement object provides a way to call stored procedures in a standard way for all RDBMS's. We just created a sample stored procedure in EnterpriseDB in the above example. We actually call the stored procedure using a CallableStatement object. This call is written in an escape syntax that may take one of two forms: one form with a result parameter, and the other without one. A result parameter, a kind of OUT parameter, is the return value for the stored procedure. Both forms may have a variable number of parameters used for input (IN parameters), output (OUT parameters), or both (IN OUT parameters). A question mark serves as a placeholder for a parameter.

The syntax for invoking a stored procedure in JDBC is shown below. Note that the square brackets indicate that what is between them is optional; they are not themselves part of the syntax.

{call procedure_name([?, ?, ...])}
	

The syntax for a procedure that returns a result parameter is:

{? = call procedure_name([?, ?, ...])}
        

To elaborate the concepts of stored procedures and their invocation from Java code, let us proceed with looking at 2 examples. The first example concentrates on a simple stored procedure without any arguments and result parameters, whereas the second one takes in two arguments.

24.8.6. Example 1: Executing a Simple Stored Procedure

Before we jump into the Java code for invoking our stored procedure (called "list_dept10") for this example, let us first create the stored procedure in our EnterpriseDB database. The following code listing shows the SPL for the "list_dept10" stored procedure:

CREATE OR REPLACE PROCEDURE list_dept10
IS
v_deptname VARCHAR2(30);
BEGIN
	DBMS_OUTPUT.PUT_LINE('Dept No: 10');
	SELECT dname INTO v_deptname FROM dept WHERE deptno = 10;
	DBMS_OUTPUT.PUT_LINE('Dept Name: ' || v_deptname);
END;

You can also create the stored procedure via EnterpriseDB PSQL as shown below:

edb=# CREATE OR REPLACE PROCEDURE LIST_DEPT10
edb-# IS
edb$# v_deptname VARCHAR2(30);
edb$# BEGIN
edb$# DBMS_OUTPUT.PUT_LINE('Dept No: 10');
edb$# SELECT dname INTO v_deptname FROM dept WHERE deptno = 10;
edb$# DBMS_OUTPUT.PUT_LINE('Dept Name: ' || v_deptname);
edb$# END;
CREATE PROCEDURE

To use the "LIST_DEPT10" procedure from our Java code we must prepare the callable statement via the prepareCall() method of the Connection object. The example below creates an instance of CallableStatement that contains a call to the "LIST_DEPT10" stored procedure.

import java.sql.*;
public class SelectDept10
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call LIST_DEPT10()}");      
      System.out.print("Executing LIST_DEPT10 Stored Procedure");
      st.execute();
	  System.out.print("Stored Procedure executed successfully");
	  
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

When you execute this stored procedure successfully via JDBC you should see the message "Stored Procedure executed successfully" on the client end. The message printed via DBMS_OUTPUT.PUT_LINE will only be visible on the server side since this message is shown on the Console window of the server.

24.8.7. Example 2:Executing Stored Procedures with IN parameters

For our next example we will first create and then invoke another EnterpriseDB stored procedure called "JDBC_DML" which accepts 2 IN parameters (employee number, and employee name) and then inserts that respective information into the "emp" table.

The following code listing shows the SPL code for the "EMP_INSERT" stored procedure:

CREATE OR REPLACE PROCEDURE EMP_INSERT(pENAME IN VARCHAR,pJOB IN VARCHAR,
pSAL IN FLOAT4, pCOMM IN FLOAT4, pDEPTNO IN INTEGER,pMgr IN INTEGER) AS
DECLARE
  CURSOR TESTCUR IS SELECT MAX(EMPNO) FROM EMP;
  MAX_EMPNO INTEGER := 10;
BEGIN
  
  OPEN TESTCUR;
  FETCH TESTCUR INTO MAX_EMPNO;   
  INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,COMM,DEPTNO,MGR) VALUES(MAX_EMPNO+1,pENAME,
  pJOB,pSAL,pCOMM,pDEPTNO,pMgr);
  close testcur;
END;

Create the above stored procedure using "EnterpriseDB PSQL". Once you have created the stored procedure, use the following code for invoking that stored procedure from your Java code and passing it 6 IN arguments via command line.

import java.sql.*;
public class InsertEmp
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call EMP_INSERT(?,?,?,?,?,?)}");      

      System.out.print("Employee Name : ");
      st.setObject(1,new String(c.readLine()));      
	  System.out.print("Employee Job : ");
	  st.setObject(2,new String(c.readLine()));	  
	  System.out.print("Salary : ");
	  st.setObject(3,new Float(c.readLine()));	  
	  System.out.print("Commission : ");
	  st.setObject(4,new Float(c.readLine()));
	  System.out.print("Department No : ");
	  st.setObject(5,new Integer(c.readLine()));
	  System.out.print("Manager : ");
	  st.setObject(6,new Integer(c.readLine()));

      st.execute();

    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

Whether the ? placeholders are IN, OUT, or IN OUT parameters depends on the stored procedure "EMP_INSERT".

Passing in any IN parameter values to a CallableStatement object is done using the setObject method whereas if the stored procedure returns OUT parameters, the SQL type of each OUT parameter must be registered before the CallableStatement object can be executed. (This is necessary because some DBMSs require the SQL type.) Registering the SQL type is done with the method registerOutParameter. Then after the statement has been executed, CallableStatement's getObject method retrieves the parameter value.

24.8.8. Example 3: Stored Procedure with OUT parameters

For our next example we will first create and then invoke another SPL stored procedure called "DEPT_SELECT" which accepts 1 IN parameter (department number) and returns 2 OUT parameters (the department name and location) corresponding against that department number.

The following code listing shows the SPL code for the "DEPT_SELECT" stored procedure:

CREATE OR REPLACE PROCEDURE DEPT_SELECT(pDEPTNO IN INTEGER,pDNAME OUT VARCHAR,
pLOC OUT VARCHAR) AS
DECLARE
  CURSOR TESTCUR IS SELECT DNAME,LOC FROM DEPT WHERE deptno = pDEPTNO;
   REC RECORD;
BEGIN
  
  OPEN TESTCUR;
  FETCH TESTCUR INTO REC;
  pDNAME:=REC.DNAME;  
  pLOC:=REC.LOC;  
  close testcur;
END;

You can create the above stored procedure either via Query Tool or EnterpriseDB PSQL as shown in the other examples.

Now that you are done creating the stored procedure let's invoke the stored procedure from Java. The following code sample illustrates the passing of one IN and the retrieval of two OUT parameters.

import java.sql.*;
public class SelectDept 
{
  /**
   * Main method to test the Stored procedure calls from JDBC
   * @param args
   */
  public static void main(String[] args)
  {
     try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call DEPT_SELECT(?,?,?)}");      

      System.out.print("DEPT  No : ");
      st.setObject(1,new Integer(c.readLine()));      

      st.registerOutParameter(2,Types.VARCHAR);
      st.registerOutParameter(3,Types.VARCHAR);

      st.execute();

      System.out.println("Dept Name: "+st.getString(2));
      System.out.println("Location : "+st.getString(3));
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}
    

Whether the ? placeholders are IN, OUT, or IN OUT parameters depends on the stored procedure "DEPT_SELECT".

As this stored procedure returns OUT parameters, the JDBC type of each OUT parameter must be registered before the CallableStatement object can be executed. This is necessary because some DBMSs require the SQL type. Registering the JDBC type is done with the method registerOutParameter. Then after the statement has been executed, the CallableStatement's getXXX methods can be used to retrieve OUT parameter values. The correct CallableStatement.getXXX method to use is the type in the Java programming language that corresponds to the JDBC type registered for that parameter.

With reference to our current example the following code registers 2 OUT parameters and after executing the stored procedure retrieves the values returned in the OUT parameters. Since both OUT parameters represent VARCHAR datatypes we retrieve them using the getString() method.

24.8.9. Example 4:Executing Stored Procedures with IN OUT parameters

For our next example we will first create and then invoke an SPL stored procedure called "emp_query" which accepts one IN parameter (department number), two IN OUT parameters (employee number and employee name) and then returns the respective information for the employee within the two IN OUT parameters and three OUT parameters.

The following code listing shows the SPL code for the "emp_query" stored procedure which is included as part of the sample database installed with EnterpriseDB. See Section 1.2.

CREATE OR REPLACE PROCEDURE emp_query (
    p_deptno        IN    NUMBER,
    p_empno        IN OUT NUMBER,
    p_ename        IN OUT VARCHAR2,
    p_job          OUT    VARCHAR2,
    p_hiredate      OUT    DATE,
    p_sal          OUT    NUMBER
)
IS
BEGIN
    SELECT empno, ename, job, hiredate, sal
        INTO p_empno, p_ename, p_job, p_hiredate, p_sal
        FROM emp
        WHERE deptno = p_deptno
          AND (empno = p_empno
          OR  ename = UPPER(p_ename));
END;

The following program invokes the stored procedure and sets the IN OUT parameters accordingly.

import java.sql.*;
public class SelectEmp 
{
  /**
  * Main method to test the Stored procedure calls from JDBC
  * @param args
  */
  public static void main(String[] args)
  {
    try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      CallableStatement st = con.prepareCall("{call emp_query(?,?,?,?,?,?)}");
      System.out.print("Department No : ");
      st.setInt(1,new Integer(c.readLine()));
      System.out.print("Employee No : ");
      st.setInt(2,new Integer(c.readLine()));
      System.out.print("Employee Name : ");
      st.setString(3,new String(c.readLine()));

      st.registerOutParameter(2,Types.INTEGER);
      st.registerOutParameter(3,Types.VARCHAR);
      st.registerOutParameter(4,Types.VARCHAR);
      st.registerOutParameter(5,Types.TIMESTAMP);
      st.registerOutParameter(6,Types.NUMERIC);

      st.execute();

      System.out.println("Employee No: "+st.getInt(2));
      System.out.println("Employee Name: "+st.getString(3));
      System.out.println("Job : "+st.getString(4));
      System.out.println("Hiredate : "+st.getTimestamp(5));
      System.out.println("Salary : "+st.getBigDecimal(6));
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");
      System.out.println("See full details below.\r\n");
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

The value of each IN OUT parameter is set with the CallableStatement's setXXX methods. The JDBC type of each IN OUT parameter is registered before the CallableStatement object is executed via the CallableStatement's getXXX methods.

24.8.10. Using REF CURSORS with Java

A REF CURSOR is a cursor variable that contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable. A cursor variable is not tied to a particular query like a static cursor. The same cursor variable may be opened a number of times with the OPEN FOR statement containing different queries and each time, a new result set will be created for that query and made available via the cursor variable.

EnterpriseDB supports the declaration of a cursor variable using both the SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR. Following is the declaration of a weakly typed SYS_REFCURSOR:

name SYS_REFCURSOR;

Following is the example of a strongly-typed REF CURSOR:

TYPE cursor_type_name IS REF CURSOR RETURN return_type;

Read more about REF CURSORS and what you can use them for in REF CURSORS and Cursor Variables.

Let us proceed with learning about using REF CURSORS in Java with an example.

24.8.11. Example Using REF CURSOR in Java

The first step of using REF CURSORS in java, we need to create the following procedure. This procedure takes a SYS_REFCURSOR as an IN parameter. The following SQL script shows the declaration of this stored procedure:

CREATE OR REPLACE PROCEDURE refcur_inout_callee(p_refcur IN OUT SYS_REFCURSOR)
IS
BEGIN
   OPEN p_refcur FOR SELECT ename FROM emp;
END;

The above stored procedure can be created using EnterpriseDB Developer Studio. To use the above defined procedure from our Java code, we must specify the datatype of the REF CURSOR being passed as an IN parameter.

Following is the Java code which will use the above declared stored procedure to retrieve employee names from the table emp:

import java.sql.*;
public class ListEmp
{
  /**
  * Main method to test the Stored procedure calls from JDBC
  * @param args
  */
  public static void main(String[] args)
  {
    try
    {
      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
      System.out.println("Start...");
      con.setAutoCommit(false);
      CallableStatement st = con.prepareCall("{call refcur_inout_callee(?)}");
      st.setNull(1,Types.REF);
      st.registerOutParameter(1,Types.REF);
      st.execute();
      ResultSet rs = (ResultSet)st.getObject(1);
      while(rs.next()) {
        System.out.println(rs.getString(1));
      }
      System.out.println("\r\n Result ended.");
    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");
      System.out.println("See full details below.\r\n");
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

The above Java script will first create a connection with the database, this is done by using the ConnectionUtils.java file. It will then use the procedure refcur_inout_callee(p_refcur IN OUT SYS_REFCURSOR) to retrieve employee names from the database and will finally display them on the console.

Following is the result when the above java script is executed.

24.8.12. Using BYTEA Datatype

For some operations we need to store data in binary format, like storing digital images. EnterpriseDB also offers the option to store and retrieve binary data via the BYTEA data type which allows storage of binary strings in a sequence of bytes.

To see such usage of BYTEA in action, we will look at 2 examples. The first example talks about inserting binary data into the database, whereas the second example talks about retrieving the binary data inserted via the first example. Before proceeding any further we need to fulfill the following dependencies:

1. First of all we need to create an EMP_DETAIL table (if not created already) which will be used to store the BYTEA data. We can do this using the following script:

         
CREATE TABLE EMP_DETAIL(
                                EMPNO INT4 PRIMARY KEY, PIC BYTEA
);	 

The above sql script will create a table EMP_DETAIL which contains 2 columns. The first one stores an employee's id hence its datatype is an integer. It is also a primary key. The second column will store a picture corresponding to an employee id in BYTEA format.

2. Create a BYTE_FUNC which has declared x as a BYTEA. We will need this function for data retrieval and insertion in our examples. The script for creating this function is as follows:

create or replace function BYTE_FUNC(x bytea) return bytea IS
BEGIN
 return x;
END;

3. Creating a procedure and function which will insert (ADD_PIC) and retrieve (GET_PIC) the BYTEA data from the table EMP_DETAIL. The script for both the GET_PIC function and ADD_PIC stored procedure is as follows:

	
CREATE OR REPLACE PROCEDURE ADD_PIC(e IN int4,x IN bytea) AS
BEGIN
 INSERT INTO EMP_DETAIL VALUES(e,x);
END;

CREATE OR REPLACE FUNCTION GET_PIC(e IN int4) RETURN BYTEA IS
DECLARE
 x BYTEA;
BEGIN
 SELECT PIC INTO x from EMP_DETAIL WHERE EMPNO=e;
 RETURN x;
END;	

Now that we are done with the dependencies let us move onto writing the Java code for inserting and retrieving binary data from an EnterpriseDB database.

24.8.13. Example 5: Inserting a picture

For the execution of the following sample code you will need JDK 1.4 installed on your machine.

import java.sql.*;
import java.io.*;
public class InsertPicTest
{
  public static void main(String[] args)
  {
    try
    {

      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
	if(con==null){
		System.out.println("Unable to connect to database.");
		return;
	}
	System.out.print("\r\nEnter Employee No. : ");
	int empno = Integer.parseInt(c.readLine());
	System.out.print("\r\nEnter Image path for the Employee \""+empno+"\" : ");
	String fileName = c.readLine();
	File f = new File(fileName);
	if(!f.exists()){
		System.out.println("The specified file does not exist. Terminating...");
		return;
	}
	
      CallableStatement st = con.prepareCall("{call ADD_PIC(?,?)}");            

      st.setInt(1,empno);
      st.setBinaryStream(2,new FileInputStream(f),(int)f.length());

      st.execute();

      System.out.println("Added image for Employee \""+empno);
      con.close();
    }
      
      catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

This sample script will store an image in the database. First of all a connection is established with the database. This is done by the following piece of java code:

Connection con = ConnectionUtils.getConnection();

If connection fails to be established than an error message is generated.

Once this has been done the user will be then be prompted to provide the user id against which the image is to be stored and the physical path to that image on your system.

The program will then check if the file path is correct and an image with the name specified exists, and if it does not the system will display an error message as shown below:

If no error is generated than the CallableStatement instance 'st' will call the ADD_PIC procedure. We will use the java class library function fileinputstream(f) to transfer the file string. Fileinputstream is meant for reading streams of raw bytes such as image data. The image will be stored in BYTEA format. Following confirmation message will appear to the user.

24.8.14. Example 6: Retrieving a picture

Once we have stored the image in the database in BYTEA format we will then need to retrieve it. The following java code will be used to retrieve a picture from the database.

import java.sql.*;
import java.io.*;
public class GetPicTest
{
  public static void main(String[] args)
  {
    try
    {

      Console c = new Console();
      Connection con = ConnectionUtils.getConnection();
	if(con==null){
		System.out.println("Unable to connect to database.");
		return;
	}
	System.out.print("\r\nEnter Employee No. : ");
	int empno = Integer.parseInt(c.readLine());

      CallableStatement st = con.prepareCall("{?=call GET_PIC(?)}");            

      st.setInt(2,empno);
      st.registerOutParameter(1,Types.BINARY);      

      st.execute();
      byte[] b = st.getBytes(1);

	System.out.print("\r\nEnter destination path to store the image : ");
	String fileName = c.readLine();
	FileOutputStream fos = new FileOutputStream(new File(fileName));
      fos.write(b);
      fos.close();
      System.out.println("File saved at \""+fileName+"\"");
      con.close();	

    }catch(Exception exp){
      System.out.println("\r\nAn Error occured while executing the procedure.");      
      System.out.println("See full details below.\r\n");      
      exp.printStackTrace();
    }
    ConnectionUtils.closeConnection();
  }
}

First of all a connection "con" will be established to the EnterpriseDB server. If the connection fails then an error message will be displayed to the user.

Once a sconnection has been established, user will be prompted to enter user id, against which the image is to be retrieved from the database and path to the location where the image is to be stored. If the employee id is not present in the database than the following error message will appear.

If however the image is saved successfully then a confirmation message will appear telling the user that the image has been saved successfully. The binary data is dumped in a byte array and then the byte array is transferred to the database via a FileOutputStream object.

24.8.15. Notification Handler

	 import java.sql.*;
         import com.edb.NoticeListener;
         import com.edb.core.BaseStatement;
         public class Test {
         public static void main(String[] args) throws Exception{   
        

	 Class.forName("com.edb.Driver");
	

	Connection con =
        DriverManager.getConnection("jdbc:edb://localhost:5444/edb","enterprisedb","enterprisedb");
        

	
        CallableStatement cst = con.prepareCall("{call list_emp()}");
        

	MyNoticeListener ml = new MyNoticeListener("Listener1");
        ((BaseStatement)cst).addNoticeListener(ml);
        cst.execute();
        System.out.println("Finished");
         }
        }
        

	class MyNoticeListener implements NoticeListener
	{
          String name = "Default";
          public MyNoticeListener(String name)
	  {
          this.name = name;
          }
         public void noticeReceived(SQLWarning warn)
	 {
           System.out.println("["+name+"] "+"Invoked Procedure: list_emp    "+ warn.getMessage());
         }
        }