Friday, May 23, 2008

AppEngine Output Tricks, Reporting, Logging, Etc

Reporting

When I took the AppEngine course several years ago, my instructor made sure his students knew that AppEngine was a batch processing tool, not a reporting tool, unlike SQR, which could do both. At that time, PeopleSoft offered Crystal Reports, PS/nVision, PS Query, and SQR as reporting options, and he encouraged us to use those tools for reporting. AppEngine was strictly labeled a batch processing tool. While debugging some jobs containing AppEngines (dunning letters, training letters, etc), I noticed that these AppEngines created and/or read files from the process output directory. Looking at the process monitor, I knew that those same files were available from the View Log/Trace link on the process details page. This got me thinking... if I could create a Microsoft Word file from AppEngine, I could place it in that process output directory and not have to run the WINWORD process on a headless server. Now, the trick, creating a Microsoft Word file from an AppEngine... Here are a couple of options

  • Word HTML format
  • Word XML format
  • RTF

By using various methods, I can convert my mail merge source data into XML format and transform it into either of these three Microsoft Word recognized formats using XSL. Of course, as of PeopleTools 8.48, we can use XMLPublisher to generate the same result. Nevertheless, if you need to process your data prior to generating a report, then a multi-step AppEngine reporting solution might be easier for you to manage than a multi-step job.

The same options are available for creating Microsoft Excel and OpenOffice documents. If you want to create Microsoft Excel binary files from AppEngine, then you can use Apache's POI Java libraries from PeopleCode. If you are interested in creating OpenOffice documents, you can generate the appropriate XML files, and then use Java to zip them into a single file. In fact, you could use this same approach to generate OpenOffice Impress presentations or Microsoft PowerPoint 2007 presentations.

For reporting, why choose AppEngine over SQR? AppEngine components (PeopleCode, SQL, etc) are managed objects. PeopleTools managed objects participate in the change management features available in PeopleTools. SQR text files do not.

Can I create a PDF from an AppEngine? Yes. Using an XSL-FO processor, you can trasform XML into PDF using a user defined XSL template. Likewise, you can use one of the PDF Java libraries to print text to a PDF file using PeopleCode similar to the way you would print output to a PDF in SQR, but with rich text features. Other reporting options: Any reporting/output tool that has a Java API can be called from AppEngine PeopleCode. For example, JasperReports, BIRT, JFreeReport, FOP, etc.

File Output Location

Suppose I want to create a file (printable report, log file, etc), where should I create the file? If you want the file available from the View Log/Trace link, then use the following SQL to determine the process's output directory:

SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = %ProcessInstance

Logging

I've already mentioned using log4j from PeopleCode. You can read about that in my posts: Logging PeopleCode Using log4j to debug applications and log4j and PeopleCode Part II. Other options include the Peoplecode MessageBox function, the PeopleCode File object, and the Java System.out/System.err methods. I prefer the Java System output methods over the PeopleCode MessageBox function because Java gives me complete control over the output. Unfortunately, you can't call the Java System output methods directly because the PrintStream output methods are overloaded. Instead, we need to use reflection to call the print methods. Here are some functions you can place in a FUNCLIB that allow you to print to stdout and stderr from PeopleCode:

/*
* Print a line of text to stdout
*/
Function println_to_stdout(&message As string)
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jOutStream = &jSystem.out;
Local JavaObject &jCls = GetJavaClass("java.lang.Class");
Local JavaObject &jStringClass = &jCls.forName("java.lang.String");
Local JavaObject &jPrintStreamCls = &jOutStream.getClass();
Local JavaObject &jPrintlnArgTypes = CreateJavaObject("java.lang.Class[]", &jStringClass);

Local JavaObject &jPrintlnMethod = &jPrintStreamCls.getDeclaredMethod("println", &jPrintlnArgTypes);

&jPrintlnMethod.invoke(&jOutStream, CreateJavaObject("java.lang.Object[]", &message));
rem ** I didn't find flushing necessary, but here is where you would flush the buffer if desired;
rem &jOutStream.flush();
End-Function;

/*
* Print a line of text to stderr
*/
Function println_to_stderr(&message As string)
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jOutStream = &jSystem.err;
Local JavaObject &jCls = GetJavaClass("java.lang.Class");
Local JavaObject &jStringClass = &jCls.forName("java.lang.String");
Local JavaObject &jPrintStreamCls = &jOutStream.getClass();
Local JavaObject &jPrintlnArgTypes = CreateJavaObject("java.lang.Class[]", &jStringClass);

Local JavaObject &jPrintlnMethod = &jPrintStreamCls.getDeclaredMethod("println", &jPrintlnArgTypes);

&jPrintlnMethod.invoke(&jOutStream, CreateJavaObject("java.lang.Object[]", &message));
rem ** I didn't find flushing necessary, but here is where you would flush the buffer if desired;
rem &jOutStream.flush();
End-Function;

If you want to use the PrintStream.print method instead of the println method, copy the code above, rename the function, and change the &jPrintlnMethod assignment from "println" to "print".

If you've worked with Java, then you know that you can redirect stdout and stderr to another PrintStream. For example, you can redirect stdout to a file or a network socket connection. Here is some code demonstrating how to redirect stdout and stderr to a different file:

/*
* Redirect stdout to file
*/
Function redirect_stdout(&fileName as string)
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jfos_out = CreateJavaObject("java.io.FileOutputStream", &fileName, True);
Local JavaObject &jps_out = CreateJavaObject("java.io.PrintStream", &jfos_out, True);
&jSystem.setOut(&jps_out);
End-Function;

/*
* Redirect stderr to file
*/
Function redirect_stderr(&fileName as string)
Local JavaObject &jSystem = GetJavaClass("java.lang.System");
Local JavaObject &jfos_out = CreateJavaObject("java.io.FileOutputStream", &fileName, True);
Local JavaObject &jps_out = CreateJavaObject("java.io.PrintStream", &jfos_out, True);
&jSystem.setErr(&jps_out);
End-Function;

By redirecting stdout and stderr, you could actually create 3 separate output files without using the File object. The benefit of using a redirected stdout over a File object is that you can setup your stdout location in one step of your program and write to that same file from anywhere else in the program without having to open/close a File object on every step.

The App Server

Just a side note: Many of the techniques demonstrated in this post can be used online. Using System.out.println, you could print to the app server's stdout file. Likewise, the reporting solutions above could be used from an online PeopleCode event to generate reports online.

103 comments:

Achal Prabhakar said...

nice post Jim.

I wanted to mention here that POI is good but perhaps Dave's XML Library fits the bill and is light weight.

Also, I really think that being able to use Java from within PCode opens up a lot of possibilities -- logging being a very good example. Most people do not pay attention to this.

BTW: In your use - did you see any issues with using this code online in appserver? Does each instance of the app server get it's own JVM?

I wrote a bit about this post here http://rowset.organizzi.com/2008/05/24/using-app-engine-for-reporting/

Jim Marion said...

@achal, Yes, you can use this code from the App server without issues. I've done it before. I use Java regular expressions in PeopleCode all the time.

Does each instance of the app server get its own JVM... I'm going to answer that by telling you how to test this. Write a Java class using the singleton design pattern and give it 1 private instance field with a getter and a setter. Now, create an IScript for initializing the value and an IScript for reading the value. Hit the initialization IScript, then hit the reading IScript from the same session. If the reading value was the same as the setting/initializing value, then you know that the JVM exists across HTTP requests. Next, close your browser and log in as a different user. Hit the reading IScript and see if the value is still the same. If so, then you know that the JVM exists across sessions and is shared between sessions. Next, restart the app server and check the value again using the reading IScript. This time, the value should be empty.

PeopleBooks says the JavaObject PeopleCode object cannot have a global scope because the JVM is not serialized between requests. Nevertheless, depending on your findings, it might be possible to have singleton objects with a global scope through the JVM.

Yes, knowing how the app server handles the JVM can be important when considering shared objects, class loaders, etc, but, as far as performance and memory, I haven't noticed any issues.

Ernst La Haye said...

Interesting stuff. I have a relating question: for a time now I'm strugling with posting a error file directly from the app engine to the screen using the REN server functionality. I can call on the file through a request to the PSPRCSPARMS table but then getting it to the screen and closing the App engine process didn't work for me. Any thoughts?

Ciphersbak said...

Hi Jim,

Gr8 post...
I'hv already started to replace WriteToLog(%ApplicationLogFence_Level1,) with your piece of code...i guess better debugging since its all in stdout..otherwise one needs to access the APPSRV_MMDD.LOG file to check...

!Cheers!

Ciphersbak said...

Hi Jim,

Its definitely not related to the above post, but would it possible for you to through some light on reports being generated via XMLP.
The situation that I'm currently facing is that once the report is generated(using Busrting), I'm unable to retrieve the generated PDF/RTF which needs to be emailed. After having a look at the logs, this is what i feel...that the AE processing the reports can possibly be never used for emailing the Reports as the path in which they are posted (psreports) only happens when the PSXP_REPORTMGR (Inbound Async) goes to success...and this happens when the AE has already finished...
Thank you for your help...
Cheers!!

Jim Marion said...

@ernst, as delivered, when using RENS and output to window, PeopleSoft automatically opens PDF output in a window after the process finishes. If the process is an AppEngine, then, when the process finishes, a new window will open that contains links to all the documents that were generated in the AE's output directory. These files include the stdout file, trace files, and any other files you may have created in the PRCSOUTPUTDIR. You do not need to do anything special to get these files to show as links on the process completion page.

Do other processes complete correctly? Do reports run "To Window" open when they finish? Do you see a process complete page when running your AE "To Window"?

@ciphersbak, I strongly encourage the use of log4j or slf4j for logging from PeopleCode. If you are logging from an AppEngine that runs through the process scheduler, then feel free to write to stdout as well. However, if you are logging from the app server, then be careful logging to stdout. The app server log file rotation ensures that the file never gets too big. I'm not aware of a rotation for stdout. Rather, instead of stdout, if you want a log file that only contains your output, not cluttered by other app server noise, like logins and logouts, then configure log4j with a log4j.xml file in your PS_HOME/class directory and use log4j to write logging statements. log4j comes with a rolling file appender to ensure the log file doesn't get too big.

XMLP... I must confess, I have no experience with XMLP. Based on your question, I did look at some FSCM AppEngines that create XMLP output. It looks like the XMLP app classes create output in a specific directory and then publish the report to the report repository. Once the report is published, it is deleted from the location where it was generated. I did notice, however, that you can e-mail a report prior to calling publish. To do this, you will need to find the piece of PeopleCode that calls publish and change that to e-mail. You can see an example of this in App Class SCM_BI_XMLP:BI_XMLP. If the report is published to the report repository using integration broker, then you could e-mail the report by creating a subscription on the inbound message. Your subscription logic could e-mail the report if it is was generated by a specific process, etc. For example. if the message is PSRF_REPORT_CREATE, then you could use the PRCSNAME field to determine the publishing process and respond accordingly.

Ciphersbak said...

Hi Jim,

Thank you for thoughts....Infact i was also using the same App package to generate custom reports in T&E...

Thank you for the info.

!cheers!

Unknown said...

Hello,
I have an issue about representing numbers into multi-byte characters for generating an SQR report in chinese.
The multi-byte characters can be saved as such in database. But what about numbers?
Any help would be the most welcomed.

Jim Marion said...

@Indrayan, I wish I could help you with this one. I haven't done much work with multi-byte characters. Have you tried converting the numbers to strings using the appropriate character set before printing them? You should be able to use your database string conversion routines to convert numbers to strings.

Unknown said...

Hi Jim,
I was thinking of 2 possible solutions:
1) Changing the font
2) Building up an SQC file, which i can use, just like you say, to convert the numbers into string. The problem with chinese characters is that suppose 0 = A and 1 = B, we expect that 10 should = BA. But it's not the case! 10 can be = C and 100 = D. So soln 1 is no more applicable. That leaves us with soln 2, where some code needs be done for the conversion purpose. I was thinking if there were any other possible soln that I might have left out, and which might be interesting to investigate, instead of building my own library.
Anyway, thanks for the reply. Best regards

Ciphersbak said...

Hi Jim,

Hope ur doin' well.

Revisited ur blog, since i was having some issues using the functions on PSNT. The situation is such that whenever the AE is run on PSUNX it starts logging. However, the moment i change it to PSNT it does not log anything to the stdout, even though the stdout file is created.

Another issue, is regarding the stderr file. I'm using that function and writing text to the file. However, i cannot see the err file. Is it because that it will log only when the AE abends.

If that be the case then how do i use the stderr function

Thank you for your help.

Prashant

Ciphersbak said...

Hi Jim,

When we are writing to stderr, it somehow does not show up under the View Log/Trace link. Since, we wanted an individual error file for each Process (AE), we had to use the redirect function, so that it creates a separate file for each process, else, it would log all errors in the same file and that would be confusing.
Hence, to create separate error files for our AE's, we use a combination of println_to_stderr and redirect_stderr.

Please let me know your views.
Thank You

Prashant

Jim Marion said...

@Prashant, when you log to stderr, does the output go into the regular AE log file? I think AE sends both stderr and stdout to the same file. You can test this by not redirecting stderr, and then looking in the AE log file. If this is the case and you want separate stdout and stderr files, then, yes, you will need to redirect stderr.

No, you don't need an abend to see stderr. You can still get stderr output from an App Engine that runs to success.

Thank you for writing in your solution for getting stderr output.

Ciphersbak said...

Hi Jim,

Thank you for views.
The scenario is such that, when we use the println_to-stdout and stderr functions in our AE's we can see the stdout under the View Log/Trace link, however, the stderr does not show up. I guess the reason being that all processes share the same stderr, since we were able to see comments/logs in the stderr from various other custom AE's also.
Yes, we can see logging happening in both the files by using the functions. but since we would like to maintain individual log/err files for each process (AE), we went ahead and redirected the stderr to our custom File and that is working absolutely fine. The question that I've is that, will the new redirected stderr be attached under the View Log/Trace link, coz I cannot see that over there.

Thank you for your Time
Prashant

Jim Marion said...

@Prashant, did you try creating your stderr file in the file location specified by the following SQL?

SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = %ProcessInstance

Any file created in this location should show up under view log/trace.

Ciphersbak said...

Hi Jim,

If i got it correctly, i would have to create the stderr file in the directory using the File Object. I was trying to eliminate the File Object issue, as you had mentioned that we can reference that across sections without the need for checking whether the File object is accessible.

Thank You
Prashant

Jim Marion said...

@Prashant, no, you don't need to use the File class. The SQL gives you the file directory. Append "/stderr" to the end of that directory and pass that to the redirect_stderr function.

Ciphersbak said...

Hi Jim,

I've used the following PeopleCode in my AE...

SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = :1", Z_PO_P_NIBS_AET.PROCESS_INST_STG.Value, &FileDir_);
&ErrFile_ = &FileDir_ | "/stderr";
&msgLog_.redirect_stderr(&ErrFile_);

Using the above Code, I was unable to view the stderr file for my AE under View Log/Trace.

Thank You
Prashant

Jim Marion said...

@Prashant, if you didn't get an error message, then it must have created the file somewhere. Do you know where it created the file? Also, can you print the value of &ErrFile_ to stdout or using MessageBox(...)?

Ciphersbak said...

Hi Jim,

Sorry to bother you on this again and again...

I printed the ErrFile_

/psoft/app/psoft/pt849/fin9/fs90dev/appserv/prcs/FS90DEV/log_output/AE_Z_POPSTONIBS_9932/INT-PO-OUT-013_2008-10-07-23.45.50.000000.err
...It did create the file on the PSUNX server. But I was unable to view it...under View Log/Trace..

Thank you for your help
Prashant

Jim Marion said...

@Prashant, when you say, "I was unable to view it...under View Log/Trace" do you mean it didn't show up in the list of files or when you click on it in the list it won't open?

Ciphersbak said...

Hi Jim,

The stderr file for the AE does not show up in the list at all. However, while running the AE, i noticed that the stdout and custom stderr files are created in the folder AE_AE_NAME_PRCSINSTANCE, but when the files are posted (Distribution Status) the custom stderr remains in the folder and the rest of the files are moved to the report repository.

Thank You
Prashant

Jim Marion said...

@Prashant, that is interesting. I have no idea why it isn't transferring the file. To debug this, I would try changing the file name. Perhaps the file name is too long or has characters PeopleSoft doesn't like. Can you try changing it to something simple like, "mylog.txt" to see if it gets transferred? If so, then try extending it until you find the problem character or length.

I'm just curious, since the process is marked with the run date/time, why do you need to mark your .err file with the date/time? Isn't most of the information in your file name redundant to the process name and process run meta-data?

Ciphersbak said...

Hi Jim,

I made the file name as small as possible. i also changed it to match the stdout format. Something like "AE_Z_POPSTONIBS_9968.stderr". However, this file remains in the folder created for the AE under log_output folder and is not moved to the report repository and hence is not visible under View Log/Trace link. is it that when PS moves the files to the report repository, it looks for specific files only?

Thank you
Prashant

Jim Marion said...

@Prashant, did you try using a common file extension like .txt? I have no idea why it isn't transferring the file. I've created RTF, PDF and TXT files from AE's without a problem. I wonder if it uses the file extension... I've also named the files anything I wanted to name them and didn't need to register the names anywhere. I just used .rtf, .pdf, and .txt as the file extensions.

Ciphersbak said...

Hi Jim,

Thanks a lot for your help. I changed the extension of the err file to ".pdf" and it showed up under the View Log/Trace link.

cheers!!
Prashant

Jim Marion said...

@Prashant, navigate to PeopleTools > Process Scheduler > System Settings. Click on the "Process Output Format" tab. Notice all the file extensions? I'm not sure where this list is maintained. I didn't look. I'm sure you can find it with a quick ctrl-j. Also, even if you find it, I don't know if the process scheduler uses it to determine what files to transfer. Nevertheless, if you really want to use the .err extension, then it might be worth investigating. If the extension isn't as important, then go with a .txt extension.

Thanks for the "heads up!"

David Newhouse said...

Hi Jim, thanks for your interesting article.

Do you know if there is a way to simply rename the .stdout file from within App Engine PeopleCode to something more meaningful to users? I'm using MessageBox to write to the log.

TIA,
David

Jim Marion said...

@David Newhouse, when you run an AppEngine from the command line, text from MessageBox statements, etc are printed to the console window (the black box). When you run an AppEngine through the process scheduler, there is no one to witness the text in the console window. Rather than write text to a location where no one will see it, the process scheduler writes it to the stdout file. I am not sure what would happen if you tried to rename the file while the AppEngine was still running. Whether or not you could rename it would depend on whether psae had the file locked for writing. And, if you were able to rename it, would psae be able to recover by writing any remaining info to a new stdout or would it just discard it, or even worse, would it crash? I am not aware of a way to rename the file and the in memory pointer similar to the method shown above for redirecting stdout and printing from Java.

As an alternative, you might consider using the redirection and println functions above. You can place them in a FUNCLIB and call them from your AE PeopleCode.

If you prefer a PeopleCode solution, then consider dropping MessageBox in favor of the File object.

The Java stdout option mentioned above and the File object both give you the option to name your file.

When I'm debugging and investigating the inner workings of PeopleSoft, I usually use the MessageBox function because it is so easy. I don't like the verbose output, but it is pretty simple to type.

I'm sure you've already considered the tips I provided and these comments probably didn't provide you with any help.

Another alternative is to place a final step in your AE (put it in an AE library for reuse) to copy stdout into a new file. This would allow you to have a copy with a friendly name. Of course, this assumes that stdout is not locked by AE.

A very safe way to rename stdout is to put your AE in a job and run a second AE (or shell script for that matter) to rename the previous process's stdout.

So, to answer your question, "Do you know if there is a way to simply rename the .stdout file from within App Engine PeopleCode to something more meaningful to users?" As you can tell by my long rambling comments to your comment, no, I don't have any idea how to do that.

David Newhouse said...

Thanks Jim. I've decided to use GetFile to create a file named appropriately for the users and write what's needed into it. You're SQL indicating the dir path to put it in was helpful.

Cheers,
David

Jim Marion said...

Cool. Thanks David for the feedback! Your approach is what I would recommend.

Unknown said...

Jim,

I'm searching for a way to combine attachments in PS that could be in various formats into one PDF output file. An specific example of what we are trying to do is withing the recruiting module (TAM) to take all the attached resumes for a job posting and combine them into one file for the recruiter and/or manager to review. I see the mergePDFs method that is now part of the XMLP stuff delivered with tools, but the files aren't necessarily PDFs to begin with. Is there an easy way to do this? Thanks.

Jim Marion said...

@Matt, that is a good question. Unfortunately, I have no good answers. Combining content in various formats is a difficult proposition. One thing to think about is to "print" content to a file using a PDF print driver like Primo PDF. This would require you to run a process that launches the content's host app (word, excel, etc) and then prints to the PDF print driver. If you've been around PeopleSoft for a while and seen Excel or Word running headless on a server, you know how much of a pain this can be. All it takes is for one of those client applications to display a prompt or message box to a headless server and you have to reboot to clear it. It isn't a good solution, but it is the only thing I can think of.

jb said...

Jim, I have been reading this blog with much interest as I am trying to print some documents from an app engine. We would like to print documents stored in Universal Content Manager (an Oracle e-Suite product) along with Work Orders (a Crystal Report). We haven't had any success with that.
We store the URL to the documents in PeopleSoft. Is it possible to print those documents from an app engine using the URL? The documents could be Word, PDF, HTML...

Jim Marion said...

@Joseph, using App Engine to print UCM documents... I haven't tried this. If you are on Linux/Unix/HP-UX or something like that, then you can use the command line to send documents to a printer. On those systems, you can also use one of the command line http programs to download a file (wget, etc). This means you could write a shell script that downloads a file from UCM and sends it to a printer. You could then launch the shell script from an App Engine. The app engine could read your URL and pass it to the shell script. If you want more control over the shell script than what is provided by the PeopleCode Exec function, then take a look at my post Exec Processes while Controlling stdin and stdout.

This is just one option. I once wrote a WebDAV client for Java that I could call from PeopleCode. That would allow me to use UCM's WebDAV support to download files. cadaver is a webDAV command line client that can be scripted to do the same thing. I don't know if UCM supports FTP. If it does, then you could use the PeopleCode GetAttachment function to download the file, and then send it to a printer.

jb said...

Jim, thank you for the quick response. Specifically, what we have is attachments on Work Orders in Maintenance Management. Daily we will use the app engine to print all the work orders that are ready. Since we couldn't bring the attachments into the Crystal report, we were thinking of single threading the work orders and printing the attachments in between before the next work order prints. I know this could take a long time to print, but the attachments could be collated with the work order. That is why I am trying to do this within the app engine that processes the work orders. I have seen several blogs where people are looking to do this exact task...

jb said...

Since I work for the DOE and security is maximum, FTP is not allowed.

jb said...

Also, we are strictly Windows environment...

China said...

Hi Jim,

I am new to iscript. I have an XML file which I need to post to it IScript URL. In peoplesoft Finance we have Iscript_Excel_Journal funciton to import journals and I wanted to call this function from an app engine and post XML file to it.
Please let me know how I can do this.

Thanks
Niroop

Jim Marion said...

@China, You can call an IScript function just like any FUNCLIB function. You just need to declare the function. The problem you will have is the %Request object, which is likely used by the IScript. That object is not available for App Engines. I recommend extracting the core of the IScript into a function with parameters, and then refactoring the IScript to collect parameters from %Request, and then call your new function (which is, by definition, a FUNCLIB function).

CSV said...

Please help us identify the best approach. We are trying to edit the content reference values through application engine-peoplecode and it is working(when tested in App designer). But we need the App engine to get triggered when a user signs into the peoplesoft online app or when the user clicks on the folder that contains the content reference name whose attribute is being modified(In this case URL in the content reference needs to be different based on user).This content reference opens an external website(Business Intelligence system) and does not involve any component or page.

Will I be able to call an ISCRIPT that calls this APP engine to set the content reference values.

To summarize, In order to access an external system, we are trying to modify the content reference attributes dynamically(URL in this case) when no components or pages are defined.

Jim Marion said...

@CSV, I think I understand. I've done the same thing for admin purposes.

Rather than call the App Engine, can you turn the PeopleCode into a FUNCLIB so you can call it online or in App Engine? Calling App Engines online can be a little odd. This would allow you to run the FUNCLIB from signon PeopleCode, an iScript, or pretty much anywhere.

CSV said...

Thank you Jim for the prompt reply and I appreciate if you could help me on this.

The below function does two things, it sets a URL(variable path based on user) for the content reference and writes some info to the table.

Content Reference referenced in the above function has a Non-PeopleSoft URL which needs to be dynamically set at runtime and it opens
up a new window to an external site without the portal content.

Is it possible to trigger this function when the user clicks the folder where the content reference resides and how it can be done or
or when it would be appropriate to trigger this and what approach should I take.


Created this FUNCLIB fieldformula function named SetupOBIUserInterface()

Function SetupOBIUserInterface();
Local ApiObject &session, &portal;
Local ApiObject &folder;
Local boolean &open;

rem Following code sets the URL
------------------------------------------------
&session = %Session;
&portal = &session.GetPortalRegistry();
&open = &portal.Open("PORTAL_NAME");
&folder = &portal.RootFolder.Folders.ItemByName("FOLDER_NAME");
MessageBox(0, "", 0, 0, "Folder is : " | &folder);
&cref = &portal.FindCRefByName("CONTENT_REFERENCE_NAME");
MessageBox(0, "", 0, 0, "Content Ref name is: " | &cref);
&cref.URLType = "UEXT";
&cref.URL = "http://domain/analytics/saw.dll?";
&cref.Save();

&Expireddatetime = %Request.GetCookieValue("PS_TOKENEXPIRE");
&UserId = %UserId;
&BIEE_TOKEN = %AuthenticationToken;

rem Following writes info to the underlying table
---------------------------------------------------------------------------------------
SQLExec("DELETE FROM PS_BIEE_TOKEN WHERE OPRID=:1", &UserId);
SQLExec("INSERT INTO PS_BIEE_TOKEN(OPRID,EXPIRES,SESSIONCOOKIENAME) VALUES(:1,%timeAdd(%DateTimeIn(:2),0),:3)", &UserId, &Expireddatetime, &BIEE_TOKEN);
SQLExec("commit");

End-Function;

Jim Marion said...

@CSV, If I understand correctly, what you want is a dynamic menu item. You want to create a CREF that points to something, but that "something" is determined at run time, not design time. Yes, I have done that often. I do it with CREF's, Related Content, Pagelets, etc. Here is how you do this:

1. Create an iScript that determines the appropriate URL and then uses %Response.RedirectURL to send to that target

2. Register the iScript as a CREF in the menu/portal registry

I do not recommend changing CREF's just to change the target location. Changing it for one user will change it for all users.

CSV said...

thanks Jim, it worked by doing the following.

In this scenario the USERID is being passed in the URL to authenticate on the target system(BI) and also PSFT has to write authentication info on to a table.
So what I understand is that I could build the appropriate URL and update the tables and call the ReDirect method in an ISCRIPT. Then register ISCRIPT as a
CREF with a URL type "PeopleSoft Script" and also I have to set permission for the ISCRIPT WEBLIB_ record.

thank you

Jim Marion said...

@CSV, Correct. Sounds good.

Saurabh said...

Hi Jim,

We are trying to send a pdf file generated to Zebra printer but, it is not gettign printed. Though, it does successfully on a HP printer.
We want to use the zebra windows driver from peoplecode to transform the pdf into the Printer's understandable codes to accomplish printing.
Your suggestions are deeply appreciated.

Jim Marion said...

@Saurabh, I would love to help, but have no experience in this area. I suggest you post your question on the PeopleSoft OTN forum or the ITToolbox PeopleTools forum

Raj said...

Hi Jim,

I am not aware of the String Hash Table logic.I have a scenario where a special character like é after getting into the put function of the hashtable gets retrieved in the form of some garbled characters.
Kindly,could you please let me know if you have any idea on this

-Brian

Jim Marion said...

@Raj, I looked through this post and comments and didn't see any mention of hash table, so I'm going to need a bit more context.

From your comment, you are trying to render a é, which is an e with an accent. I suspect the "garbled" issue has to do with the string encoding (UTF, ASCII, etc). The easiest way to handle this type of character for HTML is to use the HTML entity rather than trust encodings. The HTML entity for this particular character is é or é (the ascii character code for é). Therefore, to properly encode these types of characters, you can convert them to their ascii # and then sandwich them between &# and ;

Vinod said...

Hi Jim,
I am creating a xmlp file(data source is Rowset) which is very big in size. I am able to convert the zip file through zipAttachment function by creating a new directory. But not able to process the zipped file through ProcessReport function. Normal xml file works fine & comes in the Email also. My requiremnet is to send zipped file as the file is big & xmlpprocess is not able to send the file.

Sending the xmlp file by function.
EmailOutput(PROCESS_INSTANCE);
Please help. How to send the zipped xmlp file output.Your early response will be highly apprecaite as I need to complete this ASAP.

Jim Marion said...

@Vinod, This sounds like a great question for the oracle peoplesoft general forum or the ittoolbox forum.

random said...

nice post Jim

I am looking to use POI to create an Excel output file using App Engine. I was wondering if someone has a working or example code that I can take a look at?

Many thanks
Pawan

Jim Marion said...

@Random, I do not have POI sample code for creating Excel spreadsheets. Any reason you don't want to use an XSL transform with the Excel XML file format?

random said...

@Jim

We have requirements from customer that require native Excel format rather than Excel XML format. Are you suggesting XSL transform with Excel XML format would be same as native Excel format?

I will look at that option, if that works.

Thanks
Pawan

Jim Marion said...

@Pawan, Excel does have an XML file format but if you want xls or xlsx, then it is probably better to stick with POI.

Ciphersbak said...

Hi Pawan,

For implementations I found it easy to make us of VBScript to change the CSV to xls/xlsx. We are currently using this successfully for most of the CSV's generated via SQR's and App Engines.

Regards,
Prashant

random said...

@Prashant

Thanks for your suggestion.

To be able to use VBScript, we would requier PSNT server installed on Windows batch server along with MS Office. If we were to us PSNT server, we could also look at the option of using COM objects to create excel spreadsheets but we are trying to use PSUNX servers only.

Many thanks
Pawan

Mathumitha said...

Hi Jim,

Not related to the above post. But i face issue with BI Publisher. Can you please suggest a solution.
We are generating invoice through XMLP. RTF template is used for the same and the output is PDF.
The data is displayed in tabular column. Total is displayed as the last row of the table. Irrespective of the number of rows in the tabular column, the total row must be displayed at the bottom of the page. I tried counting the number of lines the page can hold and that occupied by the data, inserted dummy rows, but this does not work for my requirement. Is there any way to generate pdf report with total at the end of the page.

Thanks in advance

Mathumitha

Jim Marion said...

@Mathumitha, I have very little experience with BI Publisher/XML Publisher. I suggest posting your question on the Reporting OTN Forum.

Mathumitha said...

Thank you Jim.

I have posted my issue in the Reporting OTN Forum

Mathumitha

Siddharth said...

Hi Jim,

I always look forward for your blogs for reference and study. It always confirms that everything is possible on PeopleSoft (Based on the experience I have till date.). :)

Actually, we have a requirement in which we have to read Data from Excel file (*.xls or *.xlsx) and put it into the staging table. The catch here is that the data could be multi-lingual. Thus, we could expect special characters.

I tried an approach in which I did the following:

&oWorkApp = CreateObject("COM", "Excel.Application");
ObjectSetProperty(&oWorkApp, "Visible", True);
&oWorkBook = ObjectGetProperty(&oWorkApp, "Workbooks");
....
....


Now, it was working perfectly fine in the 2-Tier mode on my Windows Workstation. But, when I tried it on PIA. It was throwing an error "COM CLASS NOT FOUND."

This was due to the reason that the AppServer is on AIX Platform. Thus, Excel cannot be installed.

Now, could you suggest some alternative to achieve this with a working example. It would be a great help. Also, could you elaborate more on the usage (How-to-guide) of the Apache POI on a PS System.

Thanks,
Siddharth

Jim Marion said...

@Siddharth, as you mentioned, the Excel approach won't work on a non-Windows app server. Also as you mentioned, I recommend POI in this situation. I don't have any examples to share. It sounds like something I should put together.

Using POI from PeopleSoft requires you to add the POI jar files to your app server (preferably using the Add to Classpath option in your psappsrv.cfg file or PS_CUST_HOME). Next you either need to write Java that combines both PeopleCode and POI classes or you write PeopleCode that makes Java calls to POI classes (potentially with reflection).

Tom Williams Jr. said...

Hi Jim.

Thanks for all of the information shared on your blog.

I'm very interested in creating report output using the Excel COM object and have nearly completed a process that distributes Audit Reports in Excel 2010 format but am looking to expand on this a bit more by adding additional Excel functionalities such as Pivot Tables, Graphs and theme's.

My current method to program Excel functionality through COM via PeopleCode is recording a macro and then try to recreate the programming steps in my COM calls but I continue to hit a wall with how to appropriately reference object methods within Excel and feel that I could do this much more efficiently/effectively.

Can you direct me to any on-line documentation that will help me to understand how to reference Excel functionality through COM.

Thanks in advance...

Jim Marion said...

@Tom, no I don't know of a good reference. The approach you are taking is good, but I think there is a better way. The approach you are taking works well when it works. The problem is when it does not work. Excel was designed for desktop, not server usage. The problem happens when Excel tries to show a dialog ad there is no one at the terminal to click "OK."

An alternative is to generate the file without Excel. I use one of two approaches:

1. Generate Excel XML
2. Use a Java library like POI

These options may be harder to code but create a more solid result.

Tom Williams Jr. said...

Thanks Jim. It looks like it may be time to brush up on my Java. But then again, I'm pretty stubborn so I'll try both :).

Cheers...

Tom Williams Jr. said...

As a followup to my previous post, I am suprised that I missed the references to Daves's XML library in previous posts in response to your blog. I think when I initially read through all the responses I my braid was consumed with thoughts of COM and blinded me.
Kudo's to everybody.

Thanks...

Gowri said...

Hi Jim,

Thanks for the interesting information.

I have a question which is exact opposite of all other's question.

I have an AE which generates .txt file and places it in path. The same is showing up in View Log/Trace too. But I don't want the file to show up in View Log/Trace as it is confidential information.

Any idea to do the same.

Jim Marion said...

@Gowri, can you just have it generate the file in a different file system location?

Tom Williams Jr. said...

@Gowri, You could change the extention to one not listed in the process scheduler system settings which will result in the distribution agent ignoring it but I imagine you will still want to email it or etherwise make it a available in some other form otherwise why writ it. Cheers.

Alex Shilman said...

Hi Jim, do you know a good way of converting a blob from oracle to base64. Ideally we want to generate a pdf document. And send it to the browser as attachment.
Any ideas?

Thanks Jim.

Jim Marion said...

@Alex, This post tells how to base64 encode binary column data so the output will be base64. If the blob contains a PDF and you want to write it to the browser, this other post tells you how to do that.

saravana said...

Hi Jim/Other Followers,
I have a requiremennt like to create the PDF from Online page without a online Process. I am in indeed your help. Please do the needful.

Thanks,
Saravana Kumar

Jim Marion said...

@Saravana, there are a couple of options. First, try BI Publisher. If that won't work for you, then try incorporating one of the Java PDF generation libraries.

ktb said...

Hi Jim, I'm trying to merge pdfs created by commgen buildattachment method into one main pdf. the individual pdfs are in unique folders distinguished by attachsysfilename. I pass an array with the filepath/filename to pdfmerger and only the first pdf is written to the merged file but blank pages are created for as many other files as are in the array. Is this a problem because the files are not in the same folder? Thanks for your help.

Jim Marion said...

@ktb, I have not merged PDF's from PeopleCode. You might ask your question on the OTN Forum.

Giri said...

Jim,

Is there any way that i can generate xls files on linux OS with OpenOffice installed on it from Appengine.

Giri

Jim Marion said...

@Giri, that is an interesting thought. It might be possible, but I don't recommend any type of headless remote control. POI and Excel XML file generation are the most common methods for generating Excel Spreadsheets from Unix.

peterparker - Servant of BHAGWAN PARASHURAMA said...

Hello Jim,

Nice post.

Keep up the good work!

Last year, I tried to generate a file in peoplesoft application engine using java. The reason for this thinking was that java has more feature, hence, we can handle lot of peculiarity in the file, as support for regular expression, strings and images are also available. I did try and was successful, here's the code which I generated, very basic though:

Local JavaObject &jFile;
Local JavaObject &msg;
&msg = CreateJavaObject("java.lang.String", "Hello World!");

Local JavaObject &buf = &msg.getBytes();
Local number &byteCount = &buf.length;
&jFile = CreateJavaObject("java.io.FileOutputStream", "C:\Temp\java.txt", True);
&jFile.write(&buf, 0, &byteCount);
&jFile.close();

I want to further extend this, like displaying images, or handling lot of inbound files. I think java can provide powerful features to PeopleSoft Application Engine, as it uses concept of threading too.

Thank & Regards,

RadhaKrishna Rao L

Unknown said...

Jim,

I am trying to use the app engine to pull all attachments out from database and put them together. These attachments are in many different file formats. Next task is to pull all of these attachments from the output folder and create a one single PDF out of it available through the report manager. Any ideas how this could be done without resolving to some outside unix tool, generator etc and staying within PeopleTools? I am stumped at putting all files in one big file and any suggestions are welcome.

Thanks,

Umya

Jim Marion said...

Umya, I don't know how you will convert all those attachments to PDF.

Unknown said...

I will convert PDF file to Microsoft word or excel manually. Convert PDF

Unknown said...

Hi Jim,

I have a below code where we have to generate Excel file through app Engine.
and we are getting below Error while executing the below code through Application Engine but it worked for 1 line printing, but when doing it in Loop it throws this error.
Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke.


Local string &xquery = "Testing Apache";
Local string &xquery1 = "Testing sheet";
Local JavaObject &XSSFWorkbook = CreateJavaObject("org.apache.poi.xssf.usermodel.XSSFWorkbook");
Local JavaObject &jClass = GetJavaClass("java.lang.Class");
Local JavaObject &jCompileArgTypes = CreateJavaObject("java.lang.Class[]", &jClass.forName("java.lang.String"));
Local JavaObject &jCompileMethod = &XSSFWorkbook.getClass().getDeclaredMethod("createSheet", &jCompileArgTypes);

Local JavaObject &sheet1 = &jCompileMethod.invoke(&XSSFWorkbook, CreateJavaObject("java.lang.Object[]", &xquery));
Local JavaObject &sheet2 = &jCompileMethod.invoke(&XSSFWorkbook, CreateJavaObject("java.lang.Object[]", &xquery1));

Local JavaObject &int = GetJavaClass("java.lang.Integer");
Local JavaObject &IntType = GetJavaClass("java.lang.Integer").TYPE;
Local JavaObject &jIntArgTypes = CreateJavaObject("java.lang.Class[]", &IntType);


Local JavaObject &jcreateRow = &sheet1.getClass().getDeclaredMethod("createRow", &jIntArgTypes);


Local number &i, &k;
&k = 1;
For &i = 1 To 40000
Local JavaObject &Row = &jcreateRow.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", &i));
Local JavaObject &jcreatecell = &Row.getClass().getDeclaredMethod("createCell", &jIntArgTypes);
Local JavaObject &cell = &jcreatecell.invoke(&Row, CreateJavaObject("java.lang.Object[]", &k));
Local JavaObject &setCellValue = &cell.getClass().getDeclaredMethod("setCellValue", &jCompileArgTypes);
Local JavaObject &cellvalue = &setCellValue.invoke(&cell, CreateJavaObject("java.lang.Object[]", "Interesting this is nice"));
End-For;
rem Local JavaObject &autoSizeColumn = &sheet1.getClass().getDeclaredMethod("autoSizeColumn", &jIntArgTypes);
rem Local JavaObject &autoSize = &autoSizeColumn.invoke(&sheet1, CreateJavaObject("java.lang.Object[]", 0));

Local JavaObject &out = CreateJavaObject("java.io.FileOutputStream", "/psoft/fs9devt/UserUpload/ap/comments12.xlsx", True);
&XSSFWorkbook.write(&out);
&out.close();

Thanks
Manish

Jim Marion said...

@Manish, I just posted a response to a similar question in the OTN forum Java Exception: java.lang.reflect.InvocationTargetException: during call of java.lang.reflect.Method .invoke

Telly said...

Hi Jim,

I saw this post and the one on OTN forum of yours. Could you help me with my code?

https://community.oracle.com/message/13624336#13624336

Thank you very much in advance.

Telly

Aravind said...

Hi Jim,

I am trying to write a million rows to a csv file using Application engine. But the AE runs for ever. If i limit the number of rows, the file is getting generated properly. Is there a constraint to generate a flat file with million plus rows?

IB Learner said...

Hi Jim,

I need your help in the the output of application engine. I am posting a bursted xmlp file to defined folder(/tmp/ for eg) using publish() method, but this method deletes the file and posts under report manager. i tried using printoutput method but it is posting without considering the bursting. please help.. i need the bursted file in the destination folder for further processing

Giri said...

Hi Jim,

I have an issue with Report Posting when the out put file size (.csv) is more than 30MB report not posting , if it is less than 30MB report getting posted.

Please help me on this.


regards,
Giri

Viswanatha Reddy said...

Hi Jim,

Hope you are doing good.

I am trying to generate the pie / bar charts using application engine at run time to embed them into HTML emails.

But I couldn't get result, where as I can generate the same using page with chart area defined.

Is there any way that we can generate pie charts in peoplesoft at runtime ?

Ankur said...

Hi Jim,

i have an question regarding Component interface .i didn't find any thread so i am posting here.

I am working in campus solution product .I have created a CI for SCC_BIO_DEMO component but address is not getting updated . then i created CI for separate address component which is ADDRESS_MAINT but still address are not getting updated.My CI is working fine but address table is not updated.
My CI is running successfully but address is not updated . Any idea what went wrong? Multiple record are involved when i wrote logic for this like ADDR_FERPA_VW ,SCC_ADDR_H,SCC_ADDR_L1_H.

i have little doubt that every record is used a subrecord (DERIVED_ADDRESS) which is causing issue.I am not sure on this. as there are secondary pages are also involved in this

Any help is appreciated

Jim Marion said...

@Ankur, interesting. I have no idea why that isn't working.

Ankur said...

@jim : is there any other way .If i miss any record to update in CI .If you remember so that i can slove this issue.I am stuck at this point to update addresses through CI

Unknown said...

@jim: is there any way I can merge 2 excel file to one in peoplesoft. I have tried many solutions provided but its not working. Some solutions worked but they are giving me file in unreadable format.

Jim Marion said...

@pranay, there are a couple of ways, some harder than others... actually, none are easy. I would think the way to do it would be to either read one and update the other or read both and create a completely new sheet or workbook. My favorite is to use Apache POI. Apache POI was delivered with PeopleTools 8.54, but wasn't really visible. It is the magic behind the new BI Publisher engine that runs on Linux/Unix. It is there for you to use. I suggest using JavaScript to interface with it through the script engines also available in the Java that comes with 8.54. If you are on 8.55, then you can use the PeopleCode Excel API. If you are on earlier versions, then you will have to copy POI into your app/process scheduler directory. If your tools version has a Java that includes the JavaScript script engine (Rhino), then you are good. If your tools version is older, then you may want to think about writing custom Java to deploy to interact with POI. POI is not easy to use from PeopleCode's Java interface.

Unknown said...

Hi Jim,
i have a query about BI publisher RTF design. I have a requirement to show 3 courses per line bullet separated (As if i have 6 subjects then 3 subjects on one line and 3 on second line) how can i do this.



Ayesha Jaffar

Jim Marion said...

I am sorry Ayesha, I have no experience with BI Publisher. You might want to ask your question on the OTN PeopleSoft Discussion Forum.

Shiva said...

Hi Jim,

Greetings for the day.
Posting this query as I do not find any related one.
I have a query related to peoplecode, Can we copy only few peoplesoft objects(ex: few fields, records..etc) from a project file using an application engine peoplecode.
Is it possible? if so could you please provide your inputs on this.

Thanks,
Shiva.

travelingwilly said...

Jim

For the longest time I created a app package that built Excel dynamically by creating XML Setup in a file an it Created a Excel document , I wrote some functions to split Arrays into Columns , Chang widths etc.. Worked Okay, It came up with an issue when opening but it worked...

So I tried using (8.58.03) CreateObject("PSSpreadsheet"); It seemed simpler.. But when I had a 25k Row Dump it took Sooooo Long...

I am loading By Array so It can be dynamic. In some cases I do not have a record or Rowset or I want to add a Additional heading info the column headings and Rows
&ss = CreateObject("PSSpreadsheet");
&FileStr = GetEnv("PSPRCSLOGDIR") | "/UDR_" | &Report_name | "_" | NU_UNIFIED_AET.PROCESS_INSTANCE.Value | ".xlsx";
&ss.Open(&FileStr, False);
&ss.SetCellString(1, 1, "Report:");
&ss.SetCellString(1, 2, &Desc);
&ss.SetCellString(2, 1, "Process Instance:");
&ss.SetCellString(2, 2, &process_instance);
&Row = 4;
For &Colm = 1 To &ColArry.Len
&ss.SetCellString(&Row, &Colm, &ColArry [&Colm]);
End-For;
Then similar for data (I may have found the above from you )

Have you seen this before...

But if it is faster By Rowset.
1 issue I have I am loading dynamic info I do not have a record to create a Rowset.

Is there a Dynamic way to create a temporary Record with the PS fields I want , Thus Add it to a rowset . Fill it and then report Off it . And remove temporary record.

Will

Jim Marion said...

@Will, I would expect it to be faster by rowset, but I don't know how much faster. Yes, it would have to be modeled as a Rowset to do that, of course.

Have you tried using POI directly? POI is the Java library that PSSpreadsheet automates. It can be challenging to use from PeopleCode because of method overloading, but certainly possible from Java running in the app server, or even from JavaScript. Sometimes I use JavaScript on the app server to act as the glue between PeopleCode and Java. Here is an example (albeit going the wrong direction) https://blog.jsmpros.com/2018/06/using-peoplecode-to-read-and-process.html

Jim Marion said...

@Will, I don't know if this project is faster or better than the PeopleCode PSSpreadsheet, but it does have some references to POI: https://github.com/cy2hq/CY2_SpreadSheet.

travelingwilly said...

Thanks...
The version I use is building a Excel file XML file that is formatted with the basic Tags so it opens up in excel .. A simple error when opening but loads.

I was hoping that PSSpreadsheet would be more efficient , but Crazy slow . less than a minute to hours loading. I will take a look at the link.

The other question about Creating a Record Dynamically thus I can create a rowset and work with it.

Jim Marion said...

I think you are right. Going through an object model is going to have a lot more memory overhead and will be a lot slower.

As you know, a Rowset requires records, and that requires fixed columns, so that may be challenging. The only example I can think of that is different is Query's RunToRowset method.

travelingwilly said...

Your example probably would work If I had a standard Record to add to rowset..

But I wish to build a temporary Derived Record , Unique name to delete when done (NoDB since it will be derived)

So based on list of Fields create a dbrecord ..
Add it to rowset. Fill and copy from andother rowset
Report on new rowset.record
And Drop

I am curious if you have done something like that

w