Accessing the PeopleSoft Database in Java

Because PeopleSoft databases are SQL compliant, it is possible to access a PeopleSoft database in Java using JDBC. At times, this may be preferable. Most of the time, however, we, and our DBA's, would prefer that we accessed the PeopleSoft database through the PeopleSoft framework, not through an external direct database connection. Fortunately for those of us that like to write Java code, PeopleSoft created native interfaces to the common data access PeopleCode objects. You will find these classes and their Java source code in PS_HOME/class/peoplecode.jar.

NOTE: Because this library requires a PeopleSoft session, your Java code must be called from a PeopleTools application (PeopleCode's GetJavaClass() or CreateJavaObject(...)).

PeopleSoft's documentation of the Java PeopleCode objects is very sparse. Even though the PeopleBooks documentation for calling PeopleCode objects from Java provides a few good examples, the Javadoc class and method documentation doesn't provide much more than a method signature. Likewise, the Java source code just contains method signatures for native calls. Looking at the source code and Javadocs, you will notice that each Object's methods takes an Object and/or an Object[] parameter. Since Object is the base for all non-primitive types, it is difficult to know what PeopleSoft intended for these methods' arguments. Overloaded methods with strong types would have been better.

Let's examine a few common PeopleCode data access methods...

SQLExec

PeopleSoft provides access to many of the common PeopleCode functions through the object PeopleSoft.PeopleCode.Func. The following code snippet is the Java declaration for the SQLExec method of that object:

public static native boolean SQLExec(Object Par1, Object[] Par2);

This call spec tells us very little about the parameters required to execute the SQLExec method. From our PeopleCode experience, we can guess that the first parameter identifies the SQL statement and the second parameter, an array, contains the in/out parameters. Looking at the PeopleBooks for the function SQLExec, we can tell that this function accepts either a SQL statement or a named SQL object as its first argument. What about when called from Java? Does the Java version (really a Java wrapper around the native implementation) accept the same identifiers for the SQL statement? If so, how should those identifiers be specified? I tried several options. Here are a few that did NOT work:

String outCol = null;
Object[] parms = {new Integer(123), "abc", outCol};

// The following throws a PeopleCode exception
Func.SQLExec(new Name("SQL", "MYSQLOBJECT"), parms);

// Oracle doesn't like the following statement because PeopleSoft sends
// the Oracle database the statement: "SQL"
Func.SQLExec(Func.GetSQL(new Name("SQL", "MYSQLOBJECT"), null), parms);

// Oracle doesn't like this statement either because PeopleSoft sends
// the Oracle database the statement: "SQL.MYSQLOBJECT"
Func.SQLExec("SQL.MYSQLOBJECT", parms);

Here are a couple that I tried that actually did work:

// Use meta-sql %SQL to turn MYSQLOBJECT into an SQL statement
Func.SQLExec("%SQL(MYSQLOBJECT)", parms);

// Load the SQL object, then execute it's statement.
Func.SQLExec(Func.GetSQL(new Name("SQL", "MYSQLOBJECT), null).getValue(), params);

Interstingly enough, notice that the two versions that work return a String containing the SQL statement. Perhaps the following declaration would have been more helpful:

public static native boolean SQLExec(String sqlStatement, Object[] inOutParms);

SQL Cursors

PeopleSoft provides access to SQL cursors through the SQL object. As you would expect, the SQL PeopleCode object has a Java complement appropriately called PeopleSoft.PeopleCode.SQL. Just like PeopleCode, you create a Java SQL object using the CreateSQL function. Here is an example of creating an SQL object in Java:

Object[] parms = {"parm1", new Integer(20)};
Func.CreateSQL("SELECT...", parms);

The PeopleBooks for the PeopleCode CreateSQL function explain that the CreateSQL function will open the cursor for fetching if the SQL statement starts with the word SELECT. While working with the SQL object in Java, I found that SQL SELECT statements used with the SQL object HAVE to start with SELECT. I was not able to open cursors for SQL statements created from SQL definitions when the SQL statements started with a "%" (percent). I was, however, able to call the Execute method of SQL objects regardless of the Meta-SQL used in the SQL definition.

Conclusion

If you need access to a PeopleSoft database from Java, you have multiple options. The 2 main reasons I continue to use the PeopleCode objects for data access are:

• Meta-SQL
• Shared database session