Tuesday, February 06, 2007

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

7 comments:

Rich said...

Nice post Jim. I agree that the Pbooks doc on this topic sucks. I'm glad you posted this.

Rich

Luis Marion said...

Hi Jim,

I have developed several file extracts from PeopleSoft H/R tables using Java, SQL and JDBC. They generate .txt files which are then FTP-ed to a local server for PGP encryption and FTPing out to health care benefit carriers. These processes run completely outside of PeopleSoft. While it is working flowlessly, it does require a MS-SQL Sever user id and password, something we would like to avoid. For now, it is using trusted connection which is enough for our security administrator, since programmers do need to have a production userid/password. I also had the issue of scheduling the job, for which I ended up using MS-SQL Server, but no log/visibility to operations.

I wonder if you could expand on this topic by providing a complete Java example including the set up on the Process Scheduler. In general, I would like to program in Java what I used to do in SQR.

We really need a book on Java-PeopleSoft integration. If you ever decide to write one, please count me in as volunteer for reviewing and testing code.

Thank you,

Luis
http://www.mariontech.com

Jim Marion said...

A PeopleSoft Java book... that is a good idea.

As for the process scheduler setup, etc... if you want to use the PeopleCode data objects to access the PeopleSoft database from Java within the process scheduler, then you will need to run your Java from an AppEngine PeopleCode step using the GetJavaClass or CreateJavaObject functions. Therefore, there is no process scheduler server setup. From the process scheduler's perspective, you are just running a regular AppEngine.

I started looking at using the PeopleCode data objects for the same reason you specify, I wanted to write my batch programs in Java, not AE, SQR, or Cobol. I wanted to use something that would be supported by fusion apps. I actually wrote a database access abstraction layer containing two implementations, a JDBC implementation and a PeopleCode implementation. Using a dependency/IoC framework like Guice, Spring, or PicoContainer, it was actually possible for me to run the same code from the app server using the PS database session and from the command line.

I should write up a complete example showing the AppEngine steps, etc. Thanks for the idea!

By the way, nice last name ;)

Linne said...

Hi Jim,
I read your book about 'Using SQL Objects', for the example 'FirstTenOperateors', the array is initialised to 10, i wonder if any possible using Arraylist (likes resultSet)

Thanks a lot!
Line

Jim Marion said...

@Linne, yes, absolutely! Replace

String[] result = new String[10];

With

ArrayList<String> result = new ArrayList<String>();

And

result[row++] = (String)descr[0];

With

result.add((String)descr[0]);

Rakesh Mohan said...

Is there a way to access non-peoplesoft Database from a PeoplSoft page? May be a SQL View connecting tables from a different DB?

Jim Marion said...

@Rakesh, absolutely. There are a couple of ways. The easiest and most common is at the database level using DB links (or whatever your database platform supports). Other options include using JDBC (as documented here), web services, etc.