Thursday, July 24, 2008

Using XQuery with PeopleSoft

Like XSLT 1.0, XQuery provides a method for transforming XML. Besides the obvious differences in syntax, XQuery provides additional functionality that doesn't exist in the XSLT 1.0 specification. For example, XQuery not only provides the ability to transform a single XML document into a single output document, like XSLT 1.0, but also adds the ability to merge and/or join multiple input documents into a single result document. I'll let you look up the rest of the differences between the 2 languages. I'm not going to say either is better. They are different and each has its place. The main question I want to answer is, "How can I use XQuery with PeopleSoft?"

To use XQuery from PeopleSoft, you will need to download an XQuery library, install it, and configure your PeopleSoft app server to use it. The example code that follows uses the Saxon XQuery processor. Which version of Saxon you download and how you configure your app server to support Saxon will depend on which version of PeopleTools you are using. This difference is the result of changes made to the Java JAXP API between Java 1.4.2 (PT 8.48 and earlier) and Java 1.5 (PT 8.49). Below, you will find separate configuration sections for the Java 1.4.2 PeopleTools versions and the Java 1.5 PeopleTools version. To find out which Java version your app server uses, execute the following command:

%PS_HOME%\jre\bin\java.exe -version.

When you add the Saxon jars to your classpath, you will be adding a second implementation of the JAXP interfaces to your app server's Java runtime environment. PeopleSoft uses the Apache (Xalan/Xerces) JAXP implementation. Both of these implementations, Saxon and Apache, will register themselves as the default JAXP factory implementation. To ensure that PeopleSoft works correctly after installing the Saxon jars, you need to explicitly set the default JAXP implementation. There are 2 ways to do this: the jaxp.properties file or JVM system properties. In this example, I will give the steps for modifying your app server's JVM system properties, ignoring the jaxp.properties alternative.

To ensure that you configure our app server correctly, you need a way to determine your current JAXP settings. I wrote the following PeopleCode to assist you in configuring your JAXP settings. When run from an IScript, this code will give you the JAXP settings used by your online app server, formatted so that you can copy and paste it into your psappsrv.cfg file, as described in a later step. If you will be using XQuery in your process scheduler server, then you can replace the text %Response.WriteLine with MessageBox, and run this same PeopleCode from an AppEngine program. To run this code online, you will need to create a WEBLIB and IScript and paste this code into your record field PeopleCode:

Function IScript_GetJAXPSystemProperties()
Local string &XPathFactorySetting = "";
Local string &XPathFactoryName = "";

try
&XPathFactoryName = GetJavaClass("javax.xml.xpath.XPathFactory").newInstance().getClass().getName();
If (All(&XPathFactoryName)) Then
&XPathFactorySetting = " -Djavax.xml.xpath.XPathFactory=" | &XPathFactoryName | " -Djavax.xml.xpath.XPathFactory:http://java.sun.com/jaxp/xpath/dom=" | &XPathFactoryName;
End-If;
catch Exception &e1
end-try;

%Response.SetContentType("text/plain");
%Response.WriteLine("-Djavax.xml.transform.TransformerFactory=" | GetJavaClass("javax.xml.transform.TransformerFactory").newInstance().getClass().getName() | &XPathFactorySetting);
End-Function;

After creating your IScript, you can run this code from a URL similar to:

http://<server>:<port>/psc/<site>/EMPLOYEE/<node>/s/WEBLIB_CSS_SAXN.ISCRIPT1.FieldFormula.IScript_GetJAXPSystemProperties

Just replace the parts in < > with your site specific values.

Running this code after installing Saxon should give you the exact same result. If it doesn't, then something in the Saxon jars is overriding the PeopleSoft delivered value. Check your Java VM options to ensure that you have them set correctly. Besides your initial run prior to installing Saxon, I suggest you run this again after you install the Saxon jars and BEFORE you update the psappsrv.cfg file to see how the Factory class implementations change with the presence of the Saxon jars. Then, after you modify your psappsrv.cfg file, you can be sure that the JVM is set correctly.

PeopleTools version specific installation steps:

PT 8.48/Java 1.4.2

  1. Download Saxon version 8.9.04 from the Saxon SourceForge file repository
  2. Extract saxon8.jar, saxon8-xqj.jar, and saxon8-xpath.jar from the downloaded archive and place them in your %PS_HOME%/class directory.
  3. Open your psappsrv.cfg file and find the line that starts with JavaVM Options= and append the value given to you when you ran the function IScript_GetJAXPSystemProperties. It should look something like: -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl.
  4. Restart your app server

PT 8.49/Java 1.5

  1. Download Saxon version 9.1.0.1 from the Saxon SourceForge file repository
  2. Extract saxon9.jar and saxon9-xpath.jar from the downloaded archive and place them in your %PS_HOME%/class directory.
  3. Open your psappsrv.cfg file and find the line that starts with JavaVM Options= and append the value given to you when you ran the function IScript_GetJAXPSystemProperties. It should look something like: -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl -Djavax.xml.xpath.XPathFactory=com.sun.org.apache.xpath.internal.jaxp.XPathFactoryImpl -Djavax.xml.xpath.XPathFactory:http://java.sun.com/jaxp/xpath/dom=com.sun.org.apache.xpath.internal.jaxp.XPathFactoryImpl.
  4. Restart your app server

Downloading jars and placing them in the class path is standard practice when adding new Java libraries to a PeopleSoft implementation. Step 3 above, however, is unique. By specifically setting the JAXP system properties in the psappsrv.cfg file, we force the JVM to use the correct JAXP factories regardless of the order in which the JVM loads our Saxon/Apache jar files.

The following PeopleCode demonstrates how to execute an XQuery from PeopleCode. You will notice that the following PeopleCode references an HTML object named RSS_XQ. You can download the HTML for this HTML object (really, XQuery source, not HTML) from my online repository: rss2_html_obj.xq

Function ExecXQuery(&xquery As string) Returns string
Local JavaObject &jConfig = CreateJavaObject("net.sf.saxon.Configuration");
Local JavaObject &jClass = GetJavaClass("java.lang.Class");

Local JavaObject &jStaticContext = CreateJavaObject("net.sf.saxon.query.StaticQueryContext", &jConfig);

rem ** use Java reflection to call the compile method;
rem Local JavaObject &jExp = &jStaticContext.compileQuery(GetHTMLText(HTML.RSS_NYTIMES_BUSINESS_XQ));
Local JavaObject &jCompileArgTypes = CreateJavaObject("java.lang.Class[]", &jClass.forName("java.lang.String"));
Local JavaObject &jCompileMethod = &jStaticContext.getClass().getDeclaredMethod("compileQuery", &jCompileArgTypes);
Local JavaObject &jExp = &jCompileMethod.invoke(&jStaticContext, CreateJavaObject("java.lang.Object[]", &xquery));

Local JavaObject &jOutputProperties = CreateJavaObject("java.util.Properties");
rem ** set any output properties like encoding, method, etc;

Local JavaObject &jResultWriter = CreateJavaObject("java.io.StringWriter");
Local JavaObject &jStreamResult = CreateJavaObject("javax.xml.transform.stream.StreamResult");
Local JavaObject &jDynamicContext = CreateJavaObject("net.sf.saxon.query.DynamicQueryContext", &jConfig);

&jStreamResult.setWriter(&jResultWriter);

rem PeopleCode engine thinks &jExp is java.lang.Object, not net.sf.saxon.query.XQueryExpression so we need to continue to use reflection;
rem &jExp.run(CreateJavaObject("net.sf.saxon.query.DynamicQueryContext", &jConfig), &jStreamResult, &jOutputProperties);
Local JavaObject &jExpressionRunMethod = &jExp.getClass().getDeclaredMethod("run", CreateJavaObject("java.lang.Class[]", &jDynamicContext.getClass(), &jClass.forName("javax.xml.transform.Result"), &jOutputProperties.getClass()));
&jExpressionRunMethod.invoke(&jExp, CreateJavaObject("java.lang.Object[]", &jDynamicContext, &jStreamResult, &jOutputProperties));

Return &jResultWriter.toString();
End-Function;

Function IScript_ExecXQuery()
%Response.Write(ExecXQuery(GetHTMLText(HTML.RSS_XQ, "http://www.nytimes.com/services/xml/rss/nyt/Business.xml")));
End-Function;

As you can see, I wrapped the XQuery transformation in a function called ExecXQuery. The Saxon XQuery classes use overloads that can't be interpreted by the PeopleCode interpreter. To work around this, I had to use some Java reflection. Chris Heller did an excellent job of explaining Java reflection and PeopleCode in his post Java and PeopleCode Tips and Tricks - Part 2.

In this PeopleCode example, I've only scratched the surface of what you can do with Saxon and XQuery. Saxon includes methods for running queries to return lists, methods for dynamically setting input documents, etc. I'll let you investigate the power of Saxon. If you just want to execute xqueries as described in this post, then I suggest you add the ExecXQuery function to a FUNCLIB. Once you have the function in a FUNCLIB, you can call it from Integration Broker PeopleCode transformations. Likewise, you can use it to create a custom Pagelet Wizard data source or transformer. Using a delivered data source like HTML, you could place an XQuery in the HTML text box, and then apply an XQuery display type to that HTML data source to execute the XQuery. Creating a Pagelet Wizard XQuery transformer would allow you to execute XQueries against data sources like content management, news publications, integration broker, PeopleSoft queries, HTML, etc. Unlike an XSL transform, using XQuery, you could merge content from a news publication with other online documents.

If you plan to use other Saxon features, then you may want to create an app package with app classes to encapsulate PeopleCode interfaces to Saxon's XQuery Java API.

12 comments:

Andrew said...

Hi Jim,

I was trying this out as I thought it might be interesting to see how it works, what it does etc.

But when my iscript invokes the ExecXQuery function I get an error - ErrorReturn-> 763 - Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method.invoke. (2,763) WEBLIB_CSS_SAXN.ISCRIPT1.FieldFormula Name:ExecXQuery PCPC:3291 Statement:27

Called from:WEBLIB_CSS_SAXN.ISCRIPT1.FieldFormula Name:IScript_ExecXQuery Statement:31

I traced the PeopleCode and it errors on
&jExpressionRunMethod.invoke(&jExp, CreateJavaObject("java.lang.Object[]", &jDynamicContext, &jStreamResult, &jOutputProperties));

Can I use this just directly from an iscript CREF? Or did I just not do everything.

I appended -Djavax.xml.transform.TransformerFactory=org.apache.xalan.processor.TransformerFactoryImpl to the JVM options. I have 8.48.09 installed.

Thanks,

Andrew

Jim Marion said...

@andrew, InvocationTargetException is thrown by the java.lang.reflect.Method.invoke method when the "invoked" method throws an Exception. Basically, this is just a wrapper around a different Exception. It appears that the run method of the XQueryExpression object threw an Exception. It would be great if we could see the Java nested exception, the cause of the InvocationTargetException. Unfortunately, I don't know how to extract the nested Java exception. Did you copy and paste the code from this post directly into a Record Field FieldFormula event? If so, the first thing I would check is your app server's proxy settings. Does your app server need to use a proxy server to connect to the internet? If so, make sure you have Proxy Host and Proxy Port defined in your psappsrv.cfg file.

If you look at the Saxon documentation, you will notice you can actually run an XQuery from the command line. Try running your XQuery from the command line. You will find the Saxon docs in the sourceforge download saxon-resources8-9.zip from http://sourceforge.net/project/showfiles.php?group_id=29872&package_id=21888. To run your XQuery from your app server's command line, run

%PS_HOME%\jre\bin\java -cp %PS_HOME%\class\saxon8.jar;%PS_HOME%\class\saxon8-xqj.jar c:\temp\rss2_html_obj.xq

Where c:\temp\rss2_html_obj.xq is a copy of the XQuery from my repository. Of course, if your app server is on Unix, then use $PS_HOME/, etc. If you run this and get an error, then you should be able to get a better understanding of the error that Saxon is throwing. Another way to debug this is to write a Java wrapper that takes an XQuery file as its only parameter. Then you could catch the Java Exception and log it to a file or place it in a field that you can check after calling the method. Actually, now that I think about it, rather than posting this ugly Java reflection code embedded in PeopleCode, I should have written a Java wrapper and posted that.

Try out these options and let me know what you find.

Andrew said...

Hi Jim,

How silly of me, I should have realised it was connecting to the internet! I added the proxy info in and it worked this time.

Although the page that was displayed had html tags in it. Perhpas the page had a problem writing the html?

Anyway it worked and I like how you can bring that in.

Thanks a lot.

Andrew

Jim Marion said...

@Andrew, yes the resulting page is supposed to have HTML tags. The XQuery file converts RSS to HTML.

Thanks for the feedback! I am delighted that it worked for you.

HH said...

Jim,

My question is a little off topic but I am hoping you can spare some time.

Can you please shed some light on how to use type casting in Peoplecode.

It appears that one cannot cast objects.

For e.g. if a method call returns a type of object but contains an instance of a class say ClassA.

In java, one can easily cast it like so
( ClassA ) &ObjHndle

However, this does not work.

Another option would be to create a wrapper method in the class which casts the object handle to ClassA and return the ClassA object.

However, since we are talking about vendor delivered classes, we are looking for way simpler ways.

How can this be achieved ?

Jim Marion said...

@HH, "how to use type casting in Peoplecode... It appears that one cannot cast objects."

You are right. You can't. Notice that I used reflection with the &jExp object to execute the "run" method. That is because the prior call to "invoke" returns an Object. Once a Java object reference becomes an Object, the only way to call its methods is through reflection. Yes, this is a pain. As you already stated, you have 2 options:

1. Create wrappers/helpers
2. Use reflection

I think creating wrappers is easiest and cleanest. You don't need to modify the delivered API. You just need a class with a static method like

public static XXX objectToXXX(Object o){
if (o instanceof XXX) {
return (XXX) o;
} else {
return o;
}
}

This is basically, a static cast method.

Chris Heller just wrote a post on Casting and PeopleCode: Casting Java objects in PeopleCode

If you need to revert to Java Reflection in PeopleCode, both Chris and I have examples. Here is a list of my posts that use reflection: Java Reflection.

I am sorry for the bad news. If you are a customer, I strongly encourage you to log into Customer Connection and put in a request for a PeopleCode Java cast method.

Ciphersbak said...

Hi Jim,

Need your help to solve this one. i was gng thru ur blogs for Reflection, but I cant figure out what needs to be done here...

&jClass = GetJavaClass("java.lang.Class");
Local JavaObject &tokenPS_ = CreateJavaObject("com.peoplesoft.pt.tokengenerator.internal.PeopleSoftTokenGenerator");
Local JavaObject &jReadArgTypes = CreateJavaObject("java.lang.Class[]", GetJavaClass("com.peoplesoft.pt.tokengenerator.internal.PeopleSoftTokenGenerator"));
Local JavaObject &jReadMethod = &tokenPS_.getClass().getDeclaredMethod("generateToken", &jReadArgTypes);
Local JavaObject &jReadArgs = CreateJavaObject("java.lang.Object[]", CreateJavaObject("com.peoplesoft.pt.tokengenerator.internal.PeopleSoftTokenGenerator", &targetSTR));
Local JavaObject &jBufImage = &jReadMethod.invoke(&tokenPS_, &jReadArgs);

Thanks for your help
Prashant

Jim Marion said...

@Prashant, your code looks good to me. What errors are you getting? Take a look at Chris Heller's post Java and PeopleCode Tips and Tricks - Part 3. Chris's example is the best I've seen on PeopleCode Java Reflection.

Ciphersbak said...

Hi Jim,

Thanks for your reply. I did have a look at that post, but I'm unable to figure why it still fails with that error.
The error Text - Java Exception: java.lang.NoSuchMethodException:
com.peoplesoft.pt.tokengenerator.internal.PeopleSoftTokenGenerator.generateToken(com.peoplesoft.pt.tokengenerator.internal.PeopleSoftTokenGenerator);
during call of java.lang.getDeclaredMethod.

Thanks for helping me out with this.

Thanks!
Prashant

Jarod Merle said...

Jim, you saved the day for me again :). From your comments here regarding how to cast a Java Object to a different type in PeopleCode, I was able to successfully write code to hit a URL to retrieve employee photos and load them into the PS database. If you're interested in what I did, I posted it on stackoverflow, where I'd ask a Java question. I didn't get an answer so I answered it myself for the sake of documentation:

http://stackoverflow.com/questions/18921658/retrieve-image-from-ashx-url-using-java-in-peoplesoft-peoplecode/18988293#18988293

Jim Marion said...

Nice! Thanks for sharing!!

Jarod Merle said...

No problem. You share so much with all of us, I feel obligated to share something every couple of years at least :).