Accessing Application Core Components

This documents describes how client applications can access application core components (Logical Units, implemented in PL/SQL) using PL/SQL Command Blocks in the COM, Java, and .Net Access Providers.

Accessing PL/SQL from COM, Java, and .Net Access Providers is, besides pure syntax, practically identical. At the end of this document there are examples for Java.

Contents

• PLSQL Command Blocks
  • PlsqlCommand
  • PlsqlSelectCommand
  • PlsqlBaseMethodCommand
• PlsqlCommandCollection
• Bind Variables
• Attribute Strings
• Example code
• More on Records and server invocation

PLSQL Command Blocks

Extended Server provides easy access to PL/SQL based components from Access Providers with a method called PLSQL Command Blocks. This feature makes it easy to run multiple commands in one call to the PL/SQL Gateway. An effect of this is that all commands in one call are executed within the same database transaction, which is quite important when updating data.

There are three different types of commands to use:

• PlsqlCommand - for executing arbitrary blocks of PL/SQL code.
• PlsqlSelectCommand - for executing SQL Select queries.
• PlsqlBaseMethodCommand - for executing standard IFS New-, Modify- & Remove-methods in Logical Units.

These types are all for executing one command at a time. To execute multiple commands in one call to the PL/SQL Gateway, commands can be combined in a PlsqlCommandCollection.

Note: Only blocks of PL/SQL code or SQL Select queries can be executed through the PL/SQL Gateway. Not SQL Insert, Update or Delete statements.

PlsqlCommand

This is the basic version of a PL/SQL command. What's said about this command type also applies to the other types. This type is used for executing blocks of PL/SQL code. The properties for this command type are:

PlsqlSelectCommand

This type of command is used for making SQL Select queries. In addition to what the PlsqlCommand type does, this type gives access to a result set. When this type is used, the SQL Select statement goes into the command text. Additional properties for this command type:

More on the result set

The result set from a query is returned as a RecordCollection with one Record per row. The records have attributes corresponding to the columns queried for, both by name and data type. For example: if two columns, Id (number) and Desc (varchar2), are queried for, then the records in the resulting RecordCollection will have two attributes. One named Id with data type FLOAT and one named Desc with data type TEXT. And their values would, of course, be the values from the database.

PlsqlBaseMethodCommand

This type of PL/SQL Command is quite different from the other two. It's used to access a Logical Unit's (LU) standard New-, Modify- or Remove-operations. The operation called doesn't actually have to be named according to the standard (see more on Type below), but it has to behave the same way. The command is executed with the Execute method (as in PlsqlCommand). These parameters are set a little differently in the COM and Java Access Providers. See below for more info.

Note: For a PlsqlBaseMethodCommand command, an application developer must not manipulate either the command text or bind variables after a command is created.

In Java Access Provider

In the Java Access Provider, the PlsqlBaseMethodCommand's parameters are supplied as arguments to the class' constructor. There are two constructors, one with a method name and one without. The Type parameter's values are defined as public fields in the PlsqlBaseMethodType class. The action parameter's values are defined as public fields in the PlsqlBaseMethodAction class.

The class' constructors are:

public PlsqlBaseMethodCommand(Server srv, PlsqlBaseMethodType baseMethodType, String packageName, 
                              Record record, PlsqlBaseMethodAction action)

public PlsqlBaseMethodCommand(Server srv, PlsqlBaseMethodType baseMethodType, String packageName, 
                              String methodName, Record record, PlsqlBaseMethodAction action)

PlsqlCommandCollection

To execute multiple commands in one call to the PL/SQL Gateway, a PlsqlCommandCollection is used. To a PlsqlCommandCollection you can add commands of any type and any number of commands (within reason). Aside from the fact that commands updating data are executed in the same database transaction when using a PlsqlCommandCollection, there is also a performance gain (less network traffic with one call). 

When using a PlsqlCommandCollection, the collection must be associated with a Server object, not the commands. If a command in a collection happen to have a Server object associated, it will not be used.

A collection of commands are executed using the Execute method of the PlsqlCommandCollection object. After execution each command can be accessed and result sets or bind variable OUT or IN_OUT values can be retrieved.

Using Bind Variables

It is possible to use bind variables when accessing PL/SQL from the Access Providers. A bind variable is defined in the command text (in a SQL statement's where clause or in a PL/SQL block) as a colon followed by the name of the variable, for example ':IDENTITY' (excluding quotation marks). More in the examples below.

Bind variables are defined as attributes in a Record (the Bind Variables property of PlsqlCommand or PlsqlSelectCommand). Each bind variable is an attribute with the same name as the variable in the command text and a value. However, only attributes of TEXT, FLOAT or TIMESTAMP data type can be used as a bind variable. A bind variable's direction must also be set (on the attribute, BindVariableDirection), IN, OUT, or IN_OUT. In the Java AP as public fields in a class, BindVariableDirection. 

What about Attribute Strings?

The Java Access Providers does not have any support for manipulating attribute strings. So does this mean that an application programmer have to deal with parsing/formatting attribute strings with the correct delimiters etc? The answer is no. 

Instead of direct support for handling attribute strings, the Java AP uses aggregate attributes in the bind variables Record instead. An AGGREGATE record attribute is an attribute whose value is another Record. 

So if the bind variables contain aggregate attributes, when the command is executed this aggregate is formatted into an attribute string before the PL/SQL Gateway is called. This attribute string contains the name-value pairs of the aggregate attribute's value (a Record). After the command is executed and if the aggregate attribute is IN_OUT or OUT parameter, the returned attribute strings is parsed and put into the aggregate attributes (Record) value. If the aggregate record contains attributes named OBJID or OBJVERSION, they are omitted from the attribute string.

To clarify things a bit with an example, suppose you are calling a PL/SQL method expecting an attribute string, named ATTR, with two values (IDENTITY and DESCRIPTION) as one of its parameters. The bind variables for the call would be created like this:

// Create the bind variables record
Record bindVars = cmd.getBindVariables();
// Create the attr aggreate and take care of the aggregate record returned.
Record attr = bindVars.addAggregate("ATTR");
// Add attributes to the aggregate record (ie the attribute string)
attr.add("IDENTITY", "someid");
attr.add("DESCRIPTION", "A description");

Code examples

To exemplify what's said in this document, here is some example code showing how to access PL/SQL based components from the Java Access Providers. The examples uses the FndUser logical unit available in all IFS Applications installations. 

Java

Download: PlsqlAccess.java

To compile and run it you need to have the files ifs-fnd-ap.jar (from <fndext_home>\access_providers\java) and ifs-fnd-common.jar (from <ifs_home>\javaruntime)  in the classpath. Here is an example on how to compile and run it. With the jar-files in the same folder as the source and class files. When running the connection string, user id and password must of course be replaced with valid values.

D:\>javac -classpath ifs-fnd-ap.jar;ifs-fnd-common.jar PlsqlAccess.java
D:\>java -classpath ifs-fnd-ap.jar;ifs-fnd-common.jar PlsqlAccess localhost:63080 domain\userid mypassword

import ifs.fnd.ap.*;

/**
 * <P>
 * Example class for accessing PL/SQL from the Java Access Provider.
 * As you can see, there is no error handling in this class. A real application
 * must always handle the errors. This is just an easy way to avoid cluttering
 * the example code (since error handling isn't the topic here).
 * </P><P>
 * As you can see in the code, <B>all</B> package, table and view names must be prefixed
 * with '&AO.'. This is in the PL/SQL Gateway replaced with the application owner.
 * </P>
 */
public class PlsqlAccess {
   
   private Server srv;
   
   /**
    * Creates a new instance of this class.
    */
   public PlsqlAccess(String connectionString, String userid, String password) {
      srv = new Server();
      
      // Set the server object's connection string (where to connect to).
      srv.setConnectionString(connectionString);
      
      // Set the server object's credentials. Must be set to something real.
      srv.setCredentials(userid, password);
      
      // Set a locale (a locale (=language)).
      // setLocale(String str) will be made deprecated in future release, so don't use that.
      srv.setLocale(new Locale("en", "US"));
   }
   
   /**
    * Gets a user's description by calling </code>FND_USER_API.GET_DESCRIPTION</code>.
    * @param   identity the user's identity.
    * @return  the user's description.
    */
   public String getDescription(String identity) throws APException {
      // Create the PL/SQL command block. A method call inside a BEGIN END block.
      PlsqlCommand cmd = new PlsqlCommand(srv, "BEGIN :DESC := &AO.FND_USER_API.GET_DESCRIPTION(:IDENTITY); END;");     
      // Set bind variables values and direction (a Record is used for bind variables)
      Record bindVars = cmd.getBindVariables();
      bindVars.add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);
      // The OUT-parameter don't need an initial value.
      bindVars.add("DESC").setBindVariableDirection(BindVariableDirection.OUT);      
      
      // Execute call
      cmd.execute();

      // Return the OUT value.
      return bindVars.find("DESC").getString();      

   }
   
   /**
    * Gets info on a user by doing a SQL query with a bind variable.
    * @param   identity the identity of the user to get the info for.
    * @return  a Record with the user info. Returns null if no info is found.
    */
   public Record getUser(String identity) throws APException {
      // The SQL query to use.
      String sql = "SELECT IDENTITY,DESCRIPTION,ORACLE_USER,WEB_USER,ACTIVE FROM &AO.FND_USER WHERE IDENTITY=:IDENTITY";     
      // Use a PlsqlSelectCommand object for queries.
      PlsqlSelectCommand selCmd = new PlsqlSelectCommand(srv, sql);
            
      // Set bind variable value and direction.
      Record bindVars = selCmd.getBindVariables();
      bindVars.add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);

      // Excute the query and get the result.
      RecordCollection result = selCmd.executeQuery();

      // See if there is a result and if any rows were returned.
      if(result != null && result.size() > 0)
         return result.get(0);   // Return the first row (and in this case the only one).

      return null;
   }
   
   /**
    * Prints out a list of all users by doing a simple SQL query.
    */
   public void listUsers() throws APException {
      // Queries are performed with a PlsqlSelectCommand object.
      PlsqlSelectCommand selCmd = new PlsqlSelectCommand(srv, "SELECT IDENTITY, DESCRIPTION FROM &AO.FND_USER");
      
      // Excute the query and get the result.
      RecordCollection result = selCmd.executeQuery();
      
      System.out.println("All users:");
      
      // Check if there are any rows returned.
      if(result != null && result.size() > 0) {
         // Iterate through the result set and print out a list of the users.
         RecordIterator itr = result.recordIterator();
         Record next;
         while(itr.hasNext()) {
            next = itr.nextRecord();
            System.out.println("  " + next.findValue("IDENTITY") + "\t" + next.findValue("DESCRIPTION"));
         }
      }
      else   // Print out a nice message if there is no result.
         System.out.println("No rows selected");
   }
   
   /**
    * Combines all the other operations and does them in one call to the 
    * PL/SQL Gateway using a PlsqlCommandCollection.
    */
   public void allInOne(String identity) throws APException {
      // A PlsqlCommandCollection is used for executing multiple commands in one call.
      PlsqlCommandCollection cmds = new PlsqlCommandCollection(srv);
    
      // Setup the commands to execute and add them to the collection.
      // Don't associate them with a Server object, since that is done on the collection instead.
      
      PlsqlCommand getDescCmd = new PlsqlCommand("BEGIN :DESC := &AO.FND_USER_API.GET_DESCRIPTION(:IDENTITY); END;");           
      getDescCmd.getBindVariables().add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);
      getDescCmd.getBindVariables().add("DESC").setBindVariableDirection(BindVariableDirection.OUT);      
      cmds.add(getDescCmd);
      
      PlsqlSelectCommand selCmd = new PlsqlSelectCommand("SELECT IDENTITY,DESCRIPTION,ORACLE_USER,WEB_USER,ACTIVE FROM &AO.FND_USER WHERE IDENTITY=:IDENTITY");
      selCmd.getBindVariables().add("IDENTITY", identity).setBindVariableDirection(BindVariableDirection.IN);      
      cmds.add(selCmd);
      
      PlsqlSelectCommand listUsersCmd = new PlsqlSelectCommand("SELECT IDENTITY, DESCRIPTION FROM &AO.FND_USER");
      cmds.add(listUsersCmd);
      
      // Execute the commands
      cmds.execute();
      
      // Handle the results
      
      System.out.println("Description for user '" + identity + "': " + getDescCmd.getBindVariables().find("DESC").getString());
      
      System.out.println("Info on user '" + identity + "': ");
      if(selCmd.getResult() != null && selCmd.getResult().size() > 0)
         System.out.println(selCmd.getResult().get(0));
      else
         System.out.println("No user '" + identity + "' found.");
      
      System.out.println();
      System.out.println("All users:");
      if(listUsersCmd.getResult() != null && listUsersCmd.getResult().size() > 0) {
         RecordIterator itr = listUsersCmd.getResult().recordIterator();
         Record next;
         while(itr.hasNext()) {
            next = itr.nextRecord();
            System.out.println("  " + next.findValue("IDENTITY") + "\t" + next.findValue("DESCRIPTION"));
         }
      }
      else
         System.out.println("No rows selected");
   }
   
   /**
    * Main method.
    * @param args the command-line arguments to this application.
    */
   public static void main(String[] args) throws APException {
      // Check parameters.
      if(args.length != 3) {
         System.err.println("Usage: PlsqlAccess <connction_string> <domain\\username> <password>");
         System.exit(1);
      }
      
      // Create an instance of this object.
      PlsqlAccess pa = new PlsqlAccess(args[0], args[1], args[2]);

      // Call the methods.
      System.out.println("Description for user 'IFSAPP': " + pa.getDescription("IFSAPP"));
      System.out.println("Info on user 'IFSAPP': ");
      System.out.println(pa.getUser("IFSAPP"));
      pa.listUsers();

      System.out.println();
      System.out.println("All in one call to PL/SQL Gateway");
      pa.allInOne("IFSAPP");
   }
}