Interacting with PeopleSoft through Java - Part II

Part I of the series compiled the Java APIs that were generated for the component Interface. Now, open the component Interface and generate a Java template for that. When you take the Java file and compile it, PeopleSoft will prompt for the following values:
Enter Application Server Machine Name:
Enter Application Server Port Number:
Enter PeopleSoft UserID:
Enter PeopleSoft UserID Password:
Note that the Java template can be edited and these values can be hardcoded in the class files. Basically, this class file will use all the four class files that we have built as a part of Part I for setting the values. The four class files will inturn use the PSJOA.jar file to connect to the PeopleSoft database.
Now, when I tried executing the Java template given by PeopleSoft, I ended up with the following error:-
Unable to Connect to Application Server.
(0,0) : PeopleTools release (8.46) for web server '' is not the same as Application Server PeopleTools release (8.46.08). Access denied.
:)…tricky. I was using 8.46 in my desktop and the server was in 8.46.08..To resolve this problem, take the PSJOA.jar from the application server machine and recompile the class files. That would resolve this issue.
Once this error was fixed, I got one more error
No rows exist for the specified keys.
Failed to get the Component Interface.
(91,91) : Cannot find record. {TESTER_CI.CS_FLD_CAPTID} (91,30)
This is a straight forward one. I had modified the key values of the component interface and did not compile the four class files (generated in part 1). ..Setting the proper field names resolved this value.
After this, I tried setting the value to the field and saving the component interface. It worked like a miracle!..We have now successfully connected PeopleSoft to Java program externally. This opens up a whole lot of possibilities!..It can even substitute the integration broker component of PeopleSoft and help in seamless logging..
Keep watching this space for more..

Interacting with PeopleSoft through Java - Part I

PeopleSoft has some stuff which is really good to explore and learn. But they are kept far from the reach of normal developers, and it is often very tough to appreciate the importance of it.


One such feature is using PSJOA.jar to connect to PeopleSoft through Java.

In this first of the series of posts, we will see how this can be accomplished as a step by step tutorial. The usage of this feature is simply countless. It can be used for any purposes. It can even replace the integration broker stuff that is managed by PeopleSoft.

Before we start, we need to locate some files and keep them ready.

PSJOA.jar -> This is the interface jar file which would help Java to interact with PeopleSoft through a component interface. It will be available in %PS_HOME%\class directory.


Let us create a simple component to illustrate the usability of this feature. Then, we will see the errors thrown by PeopleSoft at each and every stage and see how we can address them.

Create a record with a single field and put this on a page. Create a component and move it to a component interface. The component interface should have the following methods :- Cancel, Create, Find, Get and Save.Now, open the component interface. Click on Build -> PeopleSoft APIs. This process would validate all the component interface and you will get some errors in this process. You can just skip the errors and continue the process. Select the "java" class option and specify the target location where the java files needs to be placed. For my component interface, PeopleSoft created the following four files

1) ITesterCi.java
2) ITesterCiCollection.java
3) TesterCi.java
4) TesterCiCollection.java


The next step would be to compile these four java files and construct class files. Believe me, this is one of the toughest step anybody can encounter and it needs to be carefully resolved. My version of PeopleSoft PeopleTools is 8.46.08. The errors and resolution might vary depending on the version of PeopleTools that you are using, however, the base approach would still remain the same.

I got a beautiful error when I compiled the java files.

javac -classpath D:\PT8.46\class\psjoa.jar *.java
ITesterCi.java:35: cannot resolve symbol
symbol : class ICompIntfcPropertyInfoCollection
location: interface PeopleSoft.Generated.CompIntfc.ITesterCi
public ICompIntfcPropertyInfoCollection getPropertyInfoCollection() throws JOA
Exception;
^
ITesterCi.java:36: cannot resolve symbol
symbol : class ICompIntfcPropertyInfoCollection
location: interface PeopleSoft.Generated.CompIntfc.ITesterCi
public ICompIntfcPropertyInfoCollection getCreateKeyInfoCollection() throws JO
AException

When I checked the iTesterCI.java, the declaration elements were

import java.math.*;
import psft.pt8.joa.*;
Some more errors like this were also present:
TesterCi.java:86: cannot resolve symbol
symbol : class ICompIntfcPropertyInfoCollection
location: class PeopleSoft.Generated.CompIntfc.TesterCi
return (ICompIntfcPropertyInfoCollection)m_oThis.getProperty("PropertyInfoCollection");
^
TesterCi.java:90: cannot resolve symbol
symbol : class ICompIntfcPropertyInfoCollection
location: class PeopleSoft.Generated.CompIntfc.TesterCi
return (ICompIntfcPropertyInfoCollection)m_oThis.getProperty("CreateKeyInfoCollection");
^
TesterCi.java:94: cannot resolve symbol
symbol : class ICompIntfcPropertyInfoCollection
location: class PeopleSoft.Generated.CompIntfc.TesterCi
return (ICompIntfcPropertyInfoCollection)m_oThis.getProperty("GetKeyInfoCollection");
^
TesterCi.java:98: cannot resolve symbol
symbol : class ICompIntfcPropertyInfoCollection
location: class PeopleSoft.Generated.CompIntfc.TesterCi
return (ICompIntfcPropertyInfoCollection)m_oThis.getProperty("FindKeyInfoCollection");
^
TesterCi.java:142: cannot resolve symbol
symbol : class ICompIntfcPropertyInfo
location: class PeopleSoft.Generated.CompIntfc.TesterCi
return (ICompIntfcPropertyInfo)m_oThis.invokeMethod("GetPropertyInfoByName", args);

This means, there is some problem in the java files generated by the Application Designer. The class files that are pointed out in the source files, are not the same as what this is contained in PSJOA.jar.

We will have to edit the class files and replace all such occurences with suitable class file names. This will ensure that the class files compiles successfully.

For my version of PeopleTools, that is what I did

Change the java code to
public CIPropertyInfoCollection getPropertyInfoCollection() throws JOAException {
return (CIPropertyInfoCollection)m_oThis.getProperty("PropertyInfoCollection");
}
public CIPropertyInfoCollection getCreateKeyInfoCollection() throws JOAException {
return (CIPropertyInfoCollection)m_oThis.getProperty("CreateKeyInfoCollection");
}
public CIPropertyInfoCollection getGetKeyInfoCollection() throws JOAException {
return (CIPropertyInfoCollection)m_oThis.getProperty("GetKeyInfoCollection");
}
public CIPropertyInfoCollection getFindKeyInfoCollection() throws JOAException {
return (CIPropertyInfoCollection)m_oThis.getProperty("FindKeyInfoCollection");
}

Note that this class is available in PSJOA.jar file. I also commented the following piece of code
// public CIPropertyInfo getPropertyInfoByName(String Name) throws JOAException {
// Object[] args = new Object[1];
//args[0] = Name;
//return (CIPropertyInfo)m_oThis.invokeMethod("GetPropertyInfoByName", args);
//}
as this is not required for my R & D. Further, this file implements ITesterCi which is evident from
public class TesterCi implements ITesterCi

So, we will have to edit the other Java file as well. Following lines were changed in that class file..
public CIPropertyInfoCollection getPropertyInfoCollection() throws JOAException;
public CIPropertyInfoCollection getCreateKeyInfoCollection() throws JOAException;
public CIPropertyInfoCollection getGetKeyInfoCollection() throws JOAException;
public CIPropertyInfoCollection getFindKeyInfoCollection() throws JOAException;

As before, I commented out this line
//public ICompIntfcPropertyInfo getPropertyInfoByName(String Name) throws JOAException;

Now, when I compiled the jar files, they got compiled successfully without any errors. One milestone gone. Part II of this blog will explain how to create a java template from the CI and make it interact with the class files generated out of this blog. We will create a key and update a table with a value in the end.!

PeopleSoft - Create Table -Build Script File

I was little curious today to find the series of SQL statements that gets executed from the Application Designer when you try to create a table in PeopleSoft. To setup, I created a record definition TEST_RECORD with three fields in it
1) DESCR100
2) DESCR20
3) DESCR50
I selected the "Build Script File" option and clicked on "Build". Note that the table does not exist in PeopleSoft. In subsequent blog posts, we will try to see some variations. Here we go..
Behind the scenes:-
1) SELECT VERSION, DESCR FROM PSMSGSETDEFN WHERE MESSAGE_SET_NBR = :1
The value passed to this :1 is 76. When queried in the database the value for Version came as "1" and DESCR came as "SQL Build Process". So, basically the system is trying to retrieve the messages that needs to be fired during the build process. These messages should be appearing in the "Build" tab of the application designer.
2) SELECT MESSAGE_NBR, MESSAGE_TEXT, MSG_SEVERITY, DESCRLONG,TO_CHAR(LAST_UPDATE_DTTM,'YYYY-MM-DD-HH24.MI.SS."000000"')
FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = :1 ORDER BY MESSAGE_NBR
The value passed to this statement is 76 again. This is the complete set of message catalogue entries that is stored in PeopleSoft, and that get displayed during the build process. Some of them are dynamic as well.
3) SELECT COUNT(*) FROM PSDDLMODEL WHERE PLATFORMID = 2
Not sure why this is executed. May be it is trying to get a count to ensure that there are suffcient rows in the table to construct the SQL statement. This is evident from the next SQL.
4) SELECT STATEMENT_TYPE, PLATFORMID, SIZING_SET, PARMCOUNT, MODEL_STATEMENT FROM PSDDLMODEL WHERE PLATFORMID = 2 ORDER BY STATEMENT_TYPE, PLATFORMID, SIZING_SET
Now, this MODEL_STATEMENT field is holding values like
CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;
These three parameters are dynamic and depends on the record
[TBNAME] => substituted from Application designer
[TBCOLLIST] => dynamic substitution
[TBSPCNAME] => dynamic substitution
CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE**; <I have not defined any index on my table >
CREATE TABLESPACE [TBSPCNAME] DATAFILE '**DIR**[TBSPCNAME].DBF' SIZE **SIZE** DEFAULT STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**);
ANALYZE TABLE [TBNAME] ESTIMATE STATISTICS;
ANALYZE TABLE [TBNAME] COMPUTE STATISTICS;
Each of the **<value>** gets dynamically substituted during the build process. This is also evident from the next SQL
SELECT STATEMENT_TYPE, PLATFORMID, SIZING_SET, PARMNAME, PARMVALUE FROM PSDDLDEFPARMS WHERE PLATFORMID = 2 ORDER BY STATEMENT_TYPE, PLATFORMID, SIZING_SET.
The result of this statement in my DB is
1 2 0 INIT 40000
1 2 0 MAXEXT UNLIMITED
1 2 0 NEXT 100000
1 2 0 PCT 0
1 2 0 PCTFREE 10
1 2 0 PCTUSED 80
2 2 0 BITMAP
2 2 0 INDEXSPC PSINDEX
2 2 0 INIT 40000
2 2 0 MAXEXT UNLIMITED
2 2 0 NEXT 100000
2 2 0 PCT 0
2 2 0 PCTFREE 10
3 2 0 DIR /dir/
3 2 0 INIT 40000
3 2 0 MAXEXT UNLIMITED
3 2 0 NEXT 100000
3 2 0 PCT 0
3 2 0 SIZE 30000000
So, the final script statement came out to be
CREATE TABLE PS_TEST_RECORD (DESCR100 VARCHAR2(300) NOT NULL
CHECK(LENGTH(DESCR100)<=100),
DESCR20 VARCHAR2(60) NOT NULL CHECK(LENGTH(DESCR20)<=20),
DESCR50 VARCHAR2(150) NOT NULL CHECK(LENGTH(DESCR50)<=50))
TABLESPACE AMAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS
UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/
COMMIT < I guess PeopleSoft Automatically adds this >
/
The values highlighted in red are directly picked up from the table. The Application designer program "implicitly" substitutes these values as obtained from the table.
5) SELECT 'PS_DOES_TABLE_EXIST' FROM PS_TEST_RECORD
Oracle error occurred: 942 (ORA-00942: table or view does not exist)
This was the last statement in my trace file. Here, it tries to query the database to really see if the table is existing in the first place. In subsequent blog posts, we will see a variation of what would happen, if the table really existed!