Consultor Eletrônico

Apptivity: Running Oracle Stored Procedures

This K-base explains how to run an Oracle Stored Procedure using
Apptivity.

First of all, the execution of the Stored Procedure is pure Java
related. By this I mean that there is no automation in Apptivity to
accomplish the execution of an Oracle Stored procedure. I'll also
give an example but for more info, I advice to get a book on JDBC.
In that book you will find the explanation on interface classes
like: Interface java.sql.PreparedStatement and Interface
java.sql.CallableStatement. These are the standard Java
implementations to run stored Procedures Although Oracle makes the
following remark:

Oracle JDBC drivers support execution of PL/SQL stole from any
Oracle JDBC driver:
SQL92 Syntax
CallableStatement cs1 = conn.prepareCall
( "{call proc (?,?)}" ) ;
CallableStatement cs2 = conn.prepareCall
( "{? = call func (?,?)}");

Oracle Syntax
CallableStatement cs3 = conn.prepareCall
( "begin proc (:1, :2);
end;" ) ;
CallableStatement cs4 = conn.prepareCall
( "begin :1 :=
func(:2,:3); end;" ) ;

The example uses an Oracle database V7.3 on HP-UX. On the account
SCOTT/TIGER I created a stored procedure to return the number of
records in the EMP table and a string with "error" in it. To create
this you need to execute the following on the Oracle side.

/*********************************************************/
CREATE OR REPLACE PROCEDURE ret_type
( counter_test out int,
out_param OUT varchar2 )
IS
BEGIN
SELECT count(*),'error' into counter_test,out_param
FROM emp;
END ret_type;
/
/*********************************************************/

When the Stored Procedure is created, the job on the Oracle side is
finished. Looking at it from the Apptivity point of view, the
Stored Procedure needs to be run on the Server, due to restrictions
when running in an Applet. In Apptivity you will have to create a
server-event. The ServerRequest routine could be something like
this:

/*********************************************************/
/*
* Ora_conServerEvents.java: An Event Handler class created by the
* Apptivity Developer.
*
* This class is instantiated by the server through references in
* the .qry file.
*
* You can add your own logic using other editors as well.
*
* @(#)Ora_conServerEvents.java 1.00 February 03, 1998,
* , 14:43:16 tbo
*/
import java.applet.*;
import java.net.*;
import java.math.*;
import java.sql.*;
import progress.apptivity.client.*;
import progress.apptivity.server.*;
import com.indius.base.*;
import netscape.application.*;
/**
* Java project generated by Apptivity Developer
*
* @version 1.00 February 03, 1998, 14:43:16
* @author tbo
*/
public class Ora_conServerEvents extends abServerEvents
{
public abVector Run_sto_pro(abVector arg) throws
SQLException, ClassNotFoundException
{
abVector returnValue = new abVector();
// Add your code here
try{
// This will get the Server context
abServer thisServer = getServer();
// This is for preparing a Connection Handle
abConnection myconn = thisServer.getConnection("Myora");
// Get a connection handle to the Oracle instance
Connection conn=myconn.getConnection(abConnection.UPDATE);

// Representation of the Stored Procedure Call
String stopro = "begin RET_TYPE (:1, :2); end;";

int titi = 0;
String toto = "";
try
{
// Prepare statement
CallableStatement callstat = conn.prepareCall(stopro);
callstat.setInt(1,titi);
callstat.setString(2,toto);
// Setting the Output Parameters to be recognised.
callstat.registerOutParameter(1,Types.INTEGER);
callstat.registerOutParameter(2,Types.VARCHAR);
// Executing the stored procedure
callstat.execute();
//Retrieving the values from Oracle stored proc.
titi = callstat.getInt(1);
toto = callstat.getString(2);
}
catch(SQLException e)
{
e.printStackTrace();
// This is just an example
returnValue.addElement(new abString("FAILED"));
return (returnValue);
}
// Pack the variables you want to pass back into returnValue
returnValue.addElement(new abInteger(titi));
returnValue.addElement(new abString(toto));
}
catch(Exception e){};

return(returnValue);
}
// END_CUSTOM_HANDLER

public abVector serverRequest (abServer server, int reqType,
String id,abQuery query, abVector arg)
throws abException
{
abVector returnValue = new abVector();
try{
switch (reqType)
{
case 1:
returnValue = Run_sto_pro(arg);
break;
}
//TODO: Add your handler code here
}
catch (Exception e)
{};
return (returnValue);
}
}
/*********************************************************/

When this routine is called, it will return an abVector that
contains the results. The client side will have to check if it was
successful according to the number of elements in the vector.

The call from the client could be something like the following
(imagine 1 button and 2 textfields).

public void OnActionbutton1 (Object arg)
{
//TODO: Add your handler code here
//
abVector toto = new abVector();
abVector returnval = new abVector();
try{
returnval = theApp().serverRequest(1,"Test",toto);
}
catch(Exception e){};
if (returnval.size() > 1){
abInteger tbo1 = (abInteger) returnval.elementAt(0);
Integer tbo2 = new Integer(tbo1.intValue());
textField1.setText( tbo2.toString());
textField2.setText( returnval.elementAt(1).toString());
}

return;
}

This should run the Stored Procedure from the Server and return
the values in the designated textFields.

Progress Software Technical Support Note # 17653