Saturday, September 18, 2010

PeopleTools Tips at OpenWorld

I have checked in at my local airport and am en route to OpenWorld, the largest tech conference of the year. I'm pretty well finished preparing my demos for this year's session, and am using this time to finish my slides. I am very pleased with my demos this year as I think they demonstrate some very powerful ways to enhance PeopleSoft applications. Two of my primary topics for this year are Mashups and Mobile. I see Mashups as an alternative to Integration. Of course, you still need integrations, but whenever possible, I look for a Mashup alternative because Mashups general don't require modifications. In this session I will present some Mashup ideas and ways to ensure security.

Mobile... I find mobile to be one of the most fascinating ideas. I work remote (no office), and, therefore, am 100% mobile (at least in theory). PeopleTools has been relatively silent in regards to mobile. PeopleSoft Applications have built some very exciting mobile apps (see Theresa's blog post and video), but PeopleTools is silent. After reviewing a handful of mobile development strategies, I am actually quite pleased with the mobile development solutions available to PeopleSoft customers. I'm finding that even though PeopleTools is silent in regards to mobile, the PeopleTools architecture lends itself very well to mobile development. In my PeopleTools Tips session on Monday I will demonstrate two separate mobile applications. The first is a mobile employee directory built using ADF and the web service data control. The second application uses plain old JavaScript and HTML to display a mobile worklist. This second application excites me the most because it shows that mobile development can be simple - No SOAP, no WSDL, no frameworks, no data bindings... just plain JavaScript, CSS, HTML, and PeopleCode.

Besides mobile and mashups, I also included:

  • Monkeypatching - what is it and how can I/why would I use it?
  • Debugging integrations - tools that facilitate debugging.
  • Pagelet Wizard - what is it, how can I use it, how can I extend it?

See you Monday at 5:00 PM in the Marriott, Golden Gate A (session id S317016). You won't be disappointed!


Dan said...

Hi Jim - I just added your session to my agenda. I've been doing some browser-side totaling and validations with jQuery and I am very interested in your approach to mobile. See you Monday.

Jim Marion said...

Hi Dan, it will be great to see you on Monday. You will have to let me know what you think of my mobile approach after you see it :). I am interested in feedback.

BShreyas said...

Hi Jim

I have a query. I will be grateful if its answered as it is obstructing my work.

I need to create hovertextsfor all the fields/ Edit boxed/labels for all the pages of our HRMS 8.9 system. I am told that hovertext functionality is inbuilt in 9.1 but no in 8.9 but we need to bring it in 8.9. Idea of usual HTML area addition and IQuery will not work here. Hence I think we need to modify the program which creates the HTML of the peoplesoft pages, to set the hovertext attribute to the value fetched from some tables.(Hovertext for RECORD-Field combinations will be stored in some tables). So the page HTML will have the hovertext for all the fields. Kindly let me know which process generated this HTML code. Also is there any other approach to tackle the same?

Jim Marion said...

@BShreyas, I believe page assembly happens in the C++ app server code. The actual HTML is generated in the C++ code. There are some "hooks" into the page generation process. For example, PT_PAGESCRIPT, PT_COPYURL, and many more HTML definitions make their way into the page assembly process. I don't see any way for you to globally modify page HTML at design time. At run time, yes, but not design time.

An alternative that I describe in my book is to add JavaScript to PT_COPYURL. This will get inserted into every page generated by PeopleSoft. How I might approach this is to use $(document).ready to iterate over all "input[type='text'], select" elements, collect the ID's, and then send them to an iScript. The iScript would determine the record, field for the ID using the technique described in my post Parsing the HTML Field name into Record and Field Names. It would return the HTML field ID/hover text in JSON and you could use these results to update your page. Be sure the Ajax request is async so you aren't blocking the page.

This would require you to modify one object, but would give you global impact. I think that one modification is worth the benefit.

Note: This will require you to insert jQuery globally. My book shows how to use PT_COPYURL to do this as well.

Why PT_COPYURL? You are on PT 8.49. Prior to 8.50, HTML definitions had a maximum size. PT_COPYURL is small enough that it has room for you to add more JavaScript.

BShreyas said...

Hi Jim

It was really a nice reply. But the problem is that I am a bit novice in Java Script, Ajax etc. I have the following queries.

When I went through the chapters 5,6,7 of your book I observed that the examples involve page-comonent specific codes, in each of which there is an inclusion of an HTML area. But as in my case I have to put a common customization for every page I can not use the HTML area. Hence can you please provide me the snippets to send the list of all the elements on a page to IScript, using jquery, and dispatch the JSON from IScript to jQuery and executing JQuery to assign the hovertext again.

Thank you.

Jim Marion said...

@BShreyas, in the book, you will see that I started with a custom HTML Area to give the reader an idea of what we are doing. In chapter 6, page 245, however, we leave HTML Area's behind and go for "Global User Interface Changes." Pages 245 through 254 show how to make a global change to the "active" text field. In chapter 7 on page 275, the text shows how to create a configurable global change. This allows you to either change global functionality, or only change the functionality of a particular page, but it does it all with JavaScript, and doesn't use HTML Area's at all. I think the chapter 7 concept is very significant and would help you implement what you are trying to implement. Chapter 8 actually has the final version of the code, so if you implement the concept, go with the code from chapter 8.

jb said...

Jim, I am developing an interface to a SQL Server 2008 database using PeopleSoft Integration Broker. I am seeing a lot of information that SOAP and web services will not be supported in future versions of SQL Server. Can you tell me 1) what is the alternative, and 2) I am having a difficult time finding any info on calling SQL Server Packages via XML/PeopleCode, do you have any sample code?

Jim Marion said...

@JB, chapter 12 of my book contains all the code necessary to create a custom JDBC target connector for Integration Broker. What this allows you to do is execute SQL statements using the Integration Broker message data as bind variables - Inserts, Updates, Deletes, exec procedures, etc.

I assume that MS SQL will still support some type of HTTP integration even if not XML/SOAP. PeopleSoft doesn't have to produce and consume SOAP. It can be XML, JSON, plain text, etc. It is really unlimited.

jb said...

Jim, I purchased your book and read chapter 12 - Creating Real-Time Integrations. How much would have to change to select data from a remote SQL Server database and bring the resultset back into our PeopleSoft database (also on SQL Server)?

Jim Marion said...

@JB, Let me make sure I understand correctly, You want to send SQL Server data into PeopleSoft. Chapter 12 was written with the idea of sending data from PeopleSoft into a database -- any database, not just Oracle. I believe you are asking for the exact opposite.

I've done this before. What you are referring to is a Listening Connector. Rather than create a separate listening connector, I would just use the HttpListeningConnector and post data into PeopleSoft as an HTTP(S) post. Most database applications contain functions/procedures for making HTTP requests. PeopleBooks has examples of POSTing to the HttpListeningConnector. This is the approach I take.

jb said...

Not exactly, I want to initiate a call from PeopleSoft to select data in the remote SQL server database, bring those results back to PeopleSoft and load them into the PeopleSoft database. I don't want to do any development on the remote database (it is owned by separate division within the company). I want to trigger the event from within PeopleSoft and process the results there.

Jim Marion said...

@jb, I see. That is different. That would be a synchronous request. The code I wrote was more of a "send and forget" asynchronous type. That is why the response at the end of the "send" method is pretty much hard coded XML. I think what you would want to change is the result sent back in that XML. The SQL part would work exactly the same, except the sql.execute part. I ignore the result. If I were thinking of sync requests as well, I would have rewritten this connector to check that return value. If sql.execute returned "true" I should have called getResultSet and returned that in the XML response. I think that is all you need to change.

jb said...

Jim, thanks for the information. Is there any support for .net communication via Integration Broker for the type of communication I need to do? We currently don't have the JDBC installed on any of our databases... (Just looking for the path of least resistance)...

Jim Marion said...

@jb, understood. No, no .net. The integration broker connectors run in a JRE. You can use .Net dll, but that would require you to create a Java wrapper.

About SQL Server access from JDBC... I don't think this requires any changes to the database. If I understand correctly, you just have to copy the JDBC driver jar into your PeopleSoft PSIGW web app. I believe you can download the jar file directly.

jb said...

Jim, could you provide some code to call the getResultSet and process the rows?

Jim Marion said...

@jb, I don't have any off hand. I suggest a Google search for getResultSet. Here is one of the results that shows how to get a result set. You would replace the sql.execute with this code: How to Use Methods getResultSet or getUpdateCount to Retrieve the Results? Next, you will want to move the ResultSet contents into XML. You can either iterate over the rows and create an XML document (simple data structures) or use an existing library to convert the ResultSet into XML (for more complex data structures).

jb said...

Jim, using the JDBCTargetConnector, I want to be able to build my SQL 'message' in PeopleCode and call the synchronous message to get the results. I anticipate several different 'Select' statements being used. It looks like the code iterates over the sql statements and bind variables. If I build the XML containing the SQL in PeopleCode will I need to build out each one in the sql.xml file used in your example?

Jim Marion said...

@jb, the key is that your message contain SQL statements. You don't need bind variables. You just need SQL statements to execute and one bindings node for each SQL statement (with an sql-ref attribute). You don't need any bind values, just the bindings node to say which SQL statements to execute.

Jim Marion said...

@JB, yes, I should have mentioned that you can build your SQL statements with PeopleCode (since that was the question). You can build them in the initial message or you can build them in a transform. The key is just that you have:

1. /message/statements nodes
2. /message/data/bindings nodes

Put your SQL in the statements node and create bindings nodes for each SQL statement with an sql-ref that points to the statement. You don't have to have bind variables, just bindings nodes.

jb said...

Jim, your book has been very useful in creating the JDBC Target Connector. I have it working to the point of creating a resultset and placing that resultset data in an XML format. I have installed in PeopleSoft and am testing via a page and PeopleCode. I think I am missing something though. I get an error that no response was sent back from the gateway. I am using a synchronous message. Do I need to add some code for IBResponse?

Jim Marion said...

@jb, yes, you need to set the IBResponse to the XML contents you want to return from your connector.

jb said...

Jim, can you help? I set up the transform program, as you outlined in your book, just to make sure your solution works. I still get the message: PublicationContractManager::ProcessError/RetryResponse(): 'Integration Gateway: No response received from Gateway (158,10829)'.
I did some research in the knowledge base and found a couple cases that fit this situation (ID 616609.1). We are on NT, no proxy, no firewall. We synchronized the appserver.cfg jolt port and files for machine name using IP Address.

The connector works fine outside of PeopleSoft and 'pings' successfully from within PeopleSoft.

Also, I added my resultset XML to the returned 'response' in the connector and verified the contents. I get no errors in the errorlog.html file and no 'response' message in the msglog.html file. Log Level = 5...

Any ideas?

Jim Marion said...

@jb, I'm not sure what could cause that. I suggest you open a case with support. Even though this is a custom connector, you are using the supported Integration Broker SDK to create it.

jb said...

Jim, there is one thing I came across. The file StatementCache.class is not addressed in the Deploying the Connector section of Chapter 12. Does this need to be installed on the webserever, as it is referenced in the JDBCTargetConnector.class?

Jim Marion said...

@jb, Hmmm... I must have forgotten that step. Yes, absolutely that needs to be deployed. Package the class into a jar and add it to the PSIGW web app's WEB-INF/lib directory, then restart your web server. Sorry about that.

jb said...

Jim, I am still having a difficult time getting the JDBCTargetConnector to work within PeopleSoft. At this point, I have went to your exact code from your book, using the USER_PROFILE service with the App Engine program transform. My msglog.html file logs the PeopleSoftListeningConnector: Request entry and the 'Caching Statement' entry from the custom 'StatementCache' class. Your code also has a display to show the bind variables, but it appears it is not getting this far. No errors are showing anywhere.
The connector 'pings' ok, but seems to fail or just stop when trying to build the sql statement. I put in a couple more messages to check the number of values in the bindList (2), and to list those values as they are being processed in the for... loop. The program seems to stop right at the for... loop. Could there be something missing for the sql.setObject that may be causing an error that is not being caught (and therefore displayed somewhere)?

Jim Marion said...

@jb, If I remember correctly, you were rewriting the connector to allow for selecting in synchronous mode. If I understand your last question, however, it sounds like you are just trying to get the async send working.

Now... about the for-loop. It sounds like you have a statement cache and are getting statements. I think where you might have problems is in the binds. If you have no /message/data/bindings/bind collections in your XML after the transform, then the for loop won't do anything.

Do you know the shape of the XML being sent to the connector? In the service operations monitor, this would be the XML after the transform (if you are using a transform to change from the standard PeopleSoft format to the one required by the connector).

You might also try printing the XML that is coming in so you can see what it looks like. You can also print the number of nodes the for loop is supposed to be iterating over.

Be sure to restart your web server after changing your code.

jb said...

Jim, it appears I am not even getting to the transform program.

The XSLT used is the same as in your book. I did put in a statement to tell how many variables are in the bindList. The msglog.html shows 4 variables and the 'Caching Statement' shows:
"INSERT INTO PS_H_USERS VALUES (?, ?, ?, ?) using id: user-insert".

But that is where it stops. I did install your code exactly, without the resultset processing I need. I am just trying to get your code to work.

Jim Marion said...

@jb, The transform happens before the connector code fires, so when you say you are getting information in message log from the connector, and it is happening before the XSL transform, then that is a problem. Er, it is a problem if you are using the USER_PROFILE message. On the USER_PROFILE service operation, did you set up a routing for your custom connector that uses the transform program specified in the chapter?

When you say "it shows 4 variables", that is from this, right?

int bindCount = bindList.size()

What about this? Are you seeing output from this?

Logger.logMessage("Bind values: " + bindMessage, null,

In a comment earlier you expressed concern with this statement, saying you think the program exits when it hits this line, with no error or anything:

sql.setObject(bindIdx + 1, bindValue);

What happens when you comment out that line and comment out the sql.execute just below it? Do you then see the bind values printed to the message log?

Since you are seeing stuff in msgLog.html, I assume you have logging turned all the way up?

I assume you put a print statement like "before setObject: " + bindIdx before setObject, and then the same thing after just to make sure it hits the other side of that statement?

I'm just typing out all the things I would do to test this and see what is going on. I've installed the code on several servers from PT 8.49 through 8.51 and have not experienced any issues. I think what you need to do is print the values of those bind variables from bindList. Commenting out setObject should do that, and only that.

jb said...

Jim, I apologize for having so much trouble with this. I have a lot of, what appear as errors, although the code works in jDeveloper. The run log from jDeveloper displays this:

WARNING: Found Oracle Apps MBeanServer but the getMBeanServer method threw an exception.

Caused by: java.lang.IllegalStateException: java.lang.reflect.InvocationTargetException

Caused by: java.lang.reflect.InvocationTargetException

Caused by: java.lang.IllegalStateException: java.lang.InstantiationException

Caused by: java.lang.RuntimeException: java.lang.InstantiationException

Caused by: java.lang.InstantiationException

Caused by: java.lang.IllegalArgumentException

GatewayProperties not found at the expected location, looking for it at the JAVA_HOME directory
Process exited with exit code 0.

Could these be causing my problem?
(I removed all the 'at' messages)...

jb said...

Jim, I appreciate your time to help us resolve this. We did a lot of testing today running this from both jDeveloper and PeopleSoft. I put a java sleep timer in the program so the DBA could verify a connection is being made.

Our Findings:
From JDeveloper:
The ping did open a connection and closed it after two minutes. DBA could see the login.
Testing with SQL again kept the connection open for two minutes and executed two insert commands on two different tables. DBA could see the login and the commands processed.

From PeopleSoft:
The ping opened the connection and closed it after two minutes. DBA could see the login.
Tested with USER_PROFILE service with at transform. DBA ran a trace while this executed. The connection was opened but no command was executed.
IB Monitor shows the following XML, post transform:


Joseph Belanger

This corresponds to my xslt in the transform app engine.

Again, no other messages appeared in msglog.html (my local msglog.html has several more).

I am working to put more messages in.
Is there a way to catch any exceptions that may not be specifically caught?

Jim Marion said...

@JB, Your XML post transform has the SQL, which I could tell from your other debugging. But what about the /message/data/bindings/bind? That is what the for loops process. You won't get any commands executed if you don't have /message/data/bindings/bind nodes. Can you escape the XML and post it here? Obfuscate the data, of course.

As for the Warnings/Errors from JDeveloper, that is because the Integration Broker framework classes expect a specific environment: running in a web server, with a configuration file in a certain place, etc.

jb said...

Jim, in my last post, it looks like the xml code did not show up.

It looks like well-formed xml. See if this shows up with comment code:



Joseph Belanger


You asked about the messages from
the code:
Logger.logMessage("Bind values: " + bindMessage, null,

It is not hitting this code. I am going to comment out the sql.setobject code, as you suggest, to see if it does hit that code.

jb said...

Jim, it does appear sql.setObject may be the problem. I put a message right before this and commented that statement out along with the sql.execute statement.
Now the program logged my message then the Bind Values message and finished with the PeopleSoftListeningConnector: Response message.

Your thoughts? We are on 1.5 jdk and that is what I used to compile, although I am using 1.6 jdk with jDeveloper.

jb said...

The problem definately appears to be with the sql.setObject(bindIdx +1, bindValue); statement. I put a logger.logmessage right before and right after. It hits the first message but not the second.

Is there some specific syntax for SQLSERVER JDBC that might be different from Oracle?

I also coded a 'catch' (Exception e) to try and catch any error, but none are showing up.

Jim Marion said...

@jb, setObject is database independent. The SQL Server JDBC driver might handle it differently, but... I like setObject because you don't have to know the data type. PreparedStatement has several other setXXX methods, such as setBytes, setClob, setDate, setTime, etc.

I'm wondering if the data type going into setObject matches the content of the bind node... Perhaps the SQL server JDBC driver is failing based on a data type mismatch?

jb said...

Jim, I got past the problem with setObject by switching to use the SQL Server classes for connection, PreparedStatement, and others.

My problem now is that I cannot get the webserver to recognize the XMLNode class. I keep getting java.lang.NoClassDefFoundError: com/peoplesoft/pt/common/XmlNode

There were a few others that I did resolve, but this one seems to be persistent. I have added xmlparserv2.jar into the web server and added the explicit path to the PSCLASSPATH entry in setenv.cmd.

Any ideas?

Jim Marion said...

@jb, you are running this connector inside the PSIGW web app, right? You should not need to change the classpath. How interesting.

jb said...

Jim, yes I am running inside PSIGW. I have tried several things on the PSCLASSPATH, however, I cannot get it to find this class...

Jim Marion said...

@jb, it sounds like there is something wrong with your PIA deployment. You should find it in your classes directory. I've deployed PIA several times and have NEVER made changes to the class path.

Here is the path where I found mine, starting from the PSIGW web app:


I did have another thought... Once I had a PIA that had a bunch of corrupt .class files in it. I had to redeploy to resolve it. If you have the file, but it says it can't find it, then the file may be corrupt.

Ganesh said...

Hi Jim,

I have created CI Based webservices for the 3rd party usage.

Security section is that is optional. It works fine without that as well. (below xml part in the SOAP request).

I have 2 issues with it.

1) If security section is optional , then it is not secure, what we need to do to mandatory?

2) We don't want to send password in clear text, how CI Webservices can have secured XML SOAP message.?

Please thrown us more light on these.


Jim Marion said...

@Ganesh, I thought service operations had a checkbox to require login. I'm not very familiar with web services in PeopleSoft. You may want to ask your question on the PeopleSoft OTN Forum. I also suggest you ask the question through Oracle support.

Samir said...

Hi Jim

I think you will be able to provide some insights on this .

I need to emulate a form post through APP engine Pcode .See form below .When the form submits it
makes call to an API that returns an XML response based on the form parameters.

form action="" method="post"
Function: input type="text" name="API_FUNCTION" value="getAffiliateList"
Key: input type="text" name="API_KEY" value="asgasdgsdgsdgsdgsdgsdg"
Empl Id: input type="text" name="empl_id" value="OD4134"
email: input type="text" name="email" value=""
input type="submit" value="Submit"

Using Java we would create a request . (Set the headers , define the post parameters ) . Send a post request to , read the retrieved xml and process the data .

How can we do this through AE Pcode without using any Java classes . I am planning to try this out using the ConnectorRequest method
but was not sure of how to set the Form POST Parameters such as API_KEY , API_FUNCTION etc to the request .

Let me know if you know of a different approach and if the ConnectorRequest way is the only way to go ,then please do guide me a bit on setting the
post parameters .


Jim Marion said...

@Samir, you can make a Sync form post through Integration Broker, but I'm not sure it is that easy. I would prefer to use Java. Since the app and process scheduler server both have full access to the Java API without adding any jar files or classes, why not just make the calls through the Java API? You can use CreateJavaObject and GetJavaClass to perform the full communication through PeopleCode without writing any Java.