Category Archives: PeopleCode

A Look at the Standalone Rowset’s Fill Method

Posted on by
Reply

A standalone rowset can be populated with rows directly from the database by using the Fill method. PeopleBooks describes the syntax of the Fill method as follows:

Fill([wherestring [, bindvalue] . . .]);

Data is read from the database table corresponding to the primary database record of the rowset. It’s also stated in PeopleBooks that:

The Fill method uses a correlation ID of FILL for the table it reads. You must use the correlation ID if you want to refer to the rowset table name as part of the wherestring. You receive a runtime error if you use the table name as a column prefix instead of the correlation ID.

It’s quite common to see wherestrings in the Fill method to reference the FILL correlation ID inside subqueries to limit the resultset. For example, something like the following:


Local Rowset &rs = CreateRowset(Record.RF_INST_PROD);

&rs.Fill("WHERE RBTACCTID = :1 AND EXISTS (SELECT 1 FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);

or


&rs.Fill("WHERE RBTACCTID = :1 AND INST_PROD_ID IN (SELECT INST_PROD_ID FROM PS_RF_INST_PROD_ST WHERE SETID = FILL.SETID AND INST_PROD_ID = FILL.INST_PROD_ID AND INST_PROD_STATUS = 'INS')", &acctid);

However, if you observe it closely (ignoring related-language processing), the Fill method would basically just generate a SQL statement in the following manner:

SELECT {list of fields from primary record} FROM %TABLE({primary record}) FILL {wherestring}

and push the resultset to the target rowset.

With this in mind, it’s possible to pull data from a join query instead. For example, the previous Fill call can be rewritten as:


&rs.Fill("INNER JOIN PS_RF_INST_PROD_ST ST ON ST.SETID = FILL.SETID AND ST.INST_PROD_ID = FILL.INST_PROD_ID WHERE FILL.RBTACCTID = :1 AND ST.INST_PROD_STATUS = 'INS')", &acctid);

Interestingly, it also means you could fill a rowset with data not coming from the primary record by using a UNION. For example (notice the fields doesn’t even have to match the primary record):


Local Rowset &rs = CreateRowset(Record.PSOPRCLS); /* Fields: OPRID, OPRCLASS */

&rs.Fill("WHERE 1=0 UNION SELECT OPRID, ROWSECCLASS FROM PS_ABS_DEPT2_VW WHERE EMPLID = :1", &emplid);

This latter example is more for academic discussion, as I can’t vouch for the database efficiency in using this technique. Also, when the fields of the primary record is updated, there is a need to manually adjust the code to match the primary record definition. However, it might be handy for quick prototypes and filling message rowsets with data directly without the need for new SQL Views and CopyTo operation.

Postscript: A quick googling shows that this concept has already been presented by Todd Kummer here.

Using MsgGetText to Concatenate Strings

Posted on by
3

Lately, I’ve been using the MsgGetText() built-in function to concatenate adhoc strings in PeopleCode. For example, instead of the following:


Local string &TEXTLINE = "Value of " | &fld1name | " is " | &fld1value | ".";

Using MsgGetText, it can be re-written as follows:


Local string &TEXTLINE = MsgGetText(0, 0, "Value of %1 is %2.", &fld1name, &fld1value);

I find that the latter is easier to read and modify compared to the first, especially if there are a lot of values being concatenated.

What about performance? Wouldn’t the MsgGetText code needlessly query the database for message (0, 0)? Fortunately, I think not. Some tests with SQL trace turned on — and the cache freshly cleared — show that a query to the message catalog for message (0, 0) is never performed by the application server. A wonderful optimization.

PeopleCode Array class’ generic sort?

Posted on by
3

I’ve come across the following statement on PeopleBooks (PeopleCode API Reference > Application Classes > When Would You Use Application Classes?). This statement can be found in PeopleBooks for PeopleTools versions 8.45 to 8.49:

… suppose you want to provide a more generic sort, with comparison function at the end of it. You want to use the array class Sort method, but your process has to be generic: you aren’t certain if you’re comparing two records or two strings. You could define a class that had as its main method a comparison, and return a -1, 0, or 1. Then write your personalized sort, extending the array class method Sort.

I find this statement very vague and lacking in details, that I am doubtful about its accuracy.

The statement seems to somewhat describe how one would define the ordering of objects in Java’s array and collection classes by creating an implementation of java.util.Comparator interface. However, according to PeopleBooks, the Sort method of the Array class does not have any optional parameters where such a comparator object may be provided.

I couldn’t find anything else in PeopleBooks where it describes the implementation of array sorting in the manner described in the statement above. Can anyone shed some light into this, and if possible, provide additional details? Does the PeopleCode Array class Sort method have an undocumented feature similar Java Arrays’ sort method?

Access to Instance Variables within the same Class

Posted on by
4

In PeopleSoft application classes, instance variables are analogous to private variables in most object-oriented languages. This means that instance variables are inaccessible from PeopleCode outside the class where it is declared. I used to assume that a specific object (instance of a class) would only have access to its own instance variables. This appears not to be the case, as the following paragraphs in PeopleBooks (PeopleCode API Reference > Application Class > Self-Reference) states:

If you declare an instance variable as private you can still access it as a private property in another instance of the same class. For example, given the following declaration:

class Example
  private
     instance number &Num;
end-class;

A method of Example could reference another Example instance’s &Num instance variable as follows:

   &X = &SomeOtherExample.Num;

Continue reading →

Inserting a New Scroll Row from within the Same Scroll

Posted on by
9

In PeopleCode, when inserting or deleting a row on a scroll, it is required that you perform the action on a parent row of the rowset being inserted/deleted to. PeopleTools doesn’t allow PeopleCode (using the built-in functions/methods InsertRow and DeleteRow) to insert or delete a row on the same scroll within which it is currently running. If you attempt to do this, PeopleTools will give an error complaining about changing the current program context.

However, there are some cases where inserting within the same scroll might be desirable. For example, based on the data entered on a row of a scroll, a new matching row must be inserted within the same scroll.

Yes, you can always make concessions, like forcing the user to click a button on a higher scroll level. Yet if you are looking for a way to insert within the same scroll level, read on. This tutorial illustrates a technique for doing a row insert from PeopleCode to the same scroll where the PeopleCode program is running.
Continue reading →

Rich emails using SendMail()

Posted on by
28

PeopleTools 8.4x added an option in the PeopleCode function SendMail() for specifying the content-type. In PeopleBooks, what is immediately highlighted is the ability to format your emails using HTML by setting the content-type to text/html. What probably isn’t well known is that it is also possible to set the content-type to other MIME types such as multipart/related.

text/html alone will allow you to format the text of your message. But if you need to display an image such as a logo, <img> tags inside your html will need to link to images stored in a web server somewhere. Sometimes, this is not ideal. With multipart/related, you can embed images in your email messages.

Continue reading →