Try OpenEdge Now
skip to main content
Java Open Clients
Passing Temp-tables as SQL ResultSet Parameters : Example Java client passing an SQL ResultSet parameter
 

Example Java client passing an SQL ResultSet parameter

The following procedure supports a small portion of an accounting system. The AccountInfo.p procedure is expected to run persistently and has an internal procedure called getPaymentsInfo:
Note: These samples are not available on the OpenEdge product ESD or the PSDN Web site.
AccountInfo.p
/* AccountInfo.p Procedure */
DEFINE INPUT PARAM account-num AS INTEGER.
DEFINE TEMP-TABLE payee-list                FIELD name            AS CHAR
                                            FIELD id              AS INTEGER.

DEFINE TEMP-TABLE payments-info         FIELD payment-date    AS DATE
                                          FIELD payee-id        AS INTEGER
                                            FIELD payee-name      AS CHAR
                                            FIELD amount          AS DECIMAL
                                            FIELD cleared         AS LOGICAL.

/* ... AccountInfo.p code ... */

/* The Internal Procedure getPaymentsInfo returns payment records.
   This is information about payments done after a specific date to payees
   who belong to the payeeList set
*/

PROCEDURE getPaymentsInfo:
        DEFINE INPUT   PARAM from-date AS DATE.
        DEFINE INPUT   PARAM TABLE FOR payee-list.
        DEFINE OUTPUT PARAM payments-num AS INTEGER.
        DEFINE OUTPUT PARAM TABLE FOR payments-info.

        /* ... getPaymentsInfo Code ... */

END PROCEDURE.
The code in the following example demonstrates how to create and use AppObjects and ProcObjects from a Java client. In this example, the AppObject was defined with ProxyGen as Account.
    // Creates and connects to the AppObject.
    Account account = new Account("AppServer://myhost:2290",
                                   "user", "password", null);

    // Creates a Persistent Procedure instance for account 777
    AccountInfo info = account.createPO_AccountInfo(777);

   // Gets information about payments done after a specific date to
    // payees who belong to the payeeList set
    java.util.GregorianCalendar fromDate;               // Starting date.
    java.sql.ResultSet payeeList;                       // List of payees.
    com.progress.open4gl.IntHolder paymentsNum; // # of returned payment records.
    com.progress.open4gl.ResultSetHolder payHolder; // Holder for record set.

    fromDate = new java.util.GregorianCalendar(1982, 6, 31);
    payeeList = new PayeeList();    // See Example 4-4 for PayeeList class.
    paymentsNum = new com.progress.open4gl.IntHolder();
    payHolder = new com.progress.open4gl.ResultSetHolder();

   // Makes the call
    info.getPaymentsInfo(fromDate, payeeList, paymentsNum, payHolder);

    // Prints the # of payment records.
    System.out.println("The number of records is: " +
                                         paymentsNum.getIntValue());
    // Extracts payment records.
    java.sql.ResultSet paymentsInfo = payHolder.getResultSetValue();

// Prints all the payment records.
   while (paymentsInfo.next())
    {
   // Gets the next record.  
     int colNum = paymentsInfo.getMetaData().getColumnCount();

// Prints the columns of the current record.
        for (int i = 1; i <= colNum; i++)
            System.out.println (paymentsInfo.getObject(i));
    }
     
// Releases the ProcObject.
    info._release();

   // Releases the AppObject. Since this is the last object to
    // share the connection this disconnects from the AppServer.
     account._release();
The following code example shows the PayeeList class for the Java client using proxy objects example. This class implements the passing of a temp-table parameter as an SQL ResultSet.
import java.sql.*;
import java.util.Vector;

// An example of a simple implementation of payeeList INPUT ResultSet.
public class PayeeList extends com.progress.open4gl.InputResultSet
{   private Vector rows;
    private int rowNum;
    private Vector currentRow;

   // Create a payee ResultSet with two payees.
    // Each has an Id and a name.
    PayeeList ()
    {
        rows = new Vector();
        Vector row;
        // {431, "Bay Plumbing"} payee.
        row = new Vector();
        row.addElement(new Integer(431));
        row.addElement(new String("Bay Plumbing"));
        rows.addElement(row);
        // {711, "Laura's Gifts"} payee.
        row = new Vector();
        row.addElement(new Integer(711));
        row.addElement(new String("Laura's Gifts"));
        rows.addElement(row);
        currentRow = null;
        rowNum = 0;
    }
    // Position the cursor on the next row.
    public boolean next() throws SQLException
    {
        try
        {
            currentRow = (Vector)rows.elementAt(rowNum++);
        }
        catch (Exception e) {return false;}
        return true;
    }

// Return the nth object of the row when n = pos. In this example, pos can be 1 or 2.
    public Object getObject(int pos) throws SQLException
    {
        return currentRow.elementAt(pos-1);
    }
}