One of the projects that we have hosted on Code.GreySparling is a very cool way to work with PeopleSoft Component Interfaces. If you do any sort of testing or data loading or Component Interface development with PeopleSoft applications, then this is for you.

Labels: 2010, CodeGreySparling, ComponentInterface, jython, python, Testing

Dave Kurtz has a nice writeup over at OakTable.net on using App Engine meta-sql to add hints to dynamically generated SQL.

Labels: 2010, AppEngine, Performance, sql

This is an update to our Editing Enhancements for PeopleCode and SQR blog entry, and was covered in one of our recent webinars for learning to be an expert in PeopleSoft.

One of the things you'll notice if you go through a PeopleCode trace is that there's way too much detail if you're trying to learn what's going on under the covers. We've found that if you can navigate to the page you want to learn about, turn on tracing, perform a few actions, and then review the trace, you'll pick it up a lot faster. The real question is how to do it.

Well, there's really two pieces:

1. Providing a way to turn tracing on and off right at the point in which you want to record your SQL
2. Provide a way to more easily review the SQL trace generated by PeopleSoft

So, we've put together an iScript that has the tracing in it for SQL as well as PeopleCode tracing as well as extending the user defined language file that already has PeopleCode and SQR syntax highlighting in it for SQL traces.

How to get it

Well, the PeopleCode can be accessed here.

The syntax highlighting file can be found here. To apply the syntax highlighting to Notepad++, merely go to %APPDATA%\Notepad++ and overlay the userDefineLang.xml file that's there.

Limitations and Enhancements

The current set of limitations and enhancement requests for this example can be found here.

Labels: PeopleCode, sql, Trace

One of the biggest challenges of end-users is to figure out where the data they want to query against resides. This is yet another tip from our "Advanced Query Tips and Techniques" webinar. By combining the PeopleCode Query Classes with knowledge of the PeopleTools tables underlying the component definition, you can provide a page that allows queries to be easily created.

Getting the Records to put in the Query

Probably the most important part of this is to figure out what records are in a component (and then subsequently weeding out the extraneous records from the list). This information is stored in the PSPNLFIELD and PSPNLGROUP records (the PSPNLGROUP record tells you the list of pages you need to look at, and the PSPNLFIELD record tells you what records and fields are on those pages.

Here is sample peoplecode that will log the list of records from a component.

Now, let's get to the good stuff

Instead of extending our code to get the records to include in the query, we will start with some code to create a simple query definition, and then extend it accordingly. Here is the code to create a "Hello World" query against the GL_ACCOUNT_TBL record.

Add multiple fields to Query

The next step is to extend our code to add multiple fields to the query definition. For the purposes of this step, we are hard-coding the record alias to be "A", and passing in the field name for both the query fielname and query field heading.

Here are the modifications to accomplish this.

Determine what fields to add the query

The next step is to add the logic to determine which fields we should add to the query from the current record. As the goal is to identify the records for a starting point, we are taking the approach of adding the key fields of the current record to the query.

This information is stored in the PSRECFIELD table, in the USEEDIT field. This field is a bitmap integer where each base-2 position is a switch for a different attribute.

Here are the modifications to accomplish this.

Dynamically add records to Query

Now that we've got a single record and its key fields being added to our query, the next step is to add more than one record by selecting against the PSPNLFIELD and PSPNLGROUP records.

In addition to putting in the appropriate selection and looping logic, it is important to keep track of the alias by which the record will be referred (this ensures that logic for adding the selected fields puts the fields in the proper place). To accomplish this, we are using the Char peopletools function and adding 64 to the index value (65 is the ascii value of "A").

Here are the modifications to accomplish this.

Expanding to handle more records

Because most components contain a lot of records and fields that do not match the key structure of the component, we limited our logic in the prior step to only bring in the GL_ACCOUNT_TBL. At this point, we will expand it to determine what keys are required from the search record, and then only include records that match at least one field from the search record.

To accomplish this, we will be looking at the PSPNLGRPDEFN record, in the SEARCHRECNAME field. The code creates an array of fields, and then a separate function will compare this array to an array that contains the list of fields of the current record to be added to the query.

Here are the modifications to accomplish this.

Expand to support more complex components

The last step is to address some complexities that come from more complex component definitions. These issues include the following:

• Components with search records that don't have any keys (e.g. INSTALLATION table)
• Related Displays

The related display information is also stored in the FIELDUSE field on the PSPNLFIELD record, but in a different position in the bitmap. For the search records that don't have any keys, we took the shortcut of taking the first record in the component and using its keys to drive what gets included in the query.

Here are the modifications to accomplish this.

Last Step - Create a Page

The last step is to create a page to allow users to create the queries by picking a component name.

You can go to the project home page to pull down the whole project for your own use. When doing this, you will want to open up the component definition in application designer and use the wizard to add it to the portal registry and add it to one of your permission lists for testing.

Potential Enhancements

The code discussed here does have a number of places where it should be enhanced as part of deploying it widely. Those can be viewed here.

Labels: PeopleCode, Query

One limitation of PeopleSoft Query is that when you use "In Tree" criteria, the effective date of the tree is forever stored in that criteria. When reading through the release value proposition of PeopleTools 8.5, I was thinking that feature to dynamicall prompt tree-nodes at run-time was going to also address this limitation. Alas, that was not the case.

Fortunately, through a little bit of PeopleCode, this can be rectified relatively easily.

Cool. Show me the stuff

I decided to implement this using application engine, since most organizations would want to update the effective dates of the queries periodically (most likely on a nightly basis). To simplify the content of this project, I wrote it to process any queries that have GS in the first two characters. You can look at the contents here.

Step 1

The first step is to create the app engine program and put in some PeopleCode to prove you can open up a query with it. Here is that code.

Step 2 - Find the Criterion

The next step is to walk the query object to find its criterion. Here are the modifications to step 1 to log the fieldname of the first piece of criterion.

Step 3 - Loop through all criterion

Now that we know how to access the criteria in a query, we will want to loop through it to do our work.

Here are the modifications to loop through all the criteria and log the field names.

Step 4 - Evaluate Criterion

Now that we can see all the criteria, we need to process only the in-tree criteria (since that's all we care about for updating the effective date).

Here are the changes to process the criterion and determine whether it's in-tree criteria. Note that this code has been put into a new function to simplify readability.

Step 5 - Get the Criteria String

On of the things that isn't documented well, is exactly where the criteria is stored. I spent a little time walking through the debugger in the application designer to determine that it's in the expr2constant1 property as a comma separated value string.

Here are the modifications to log that value.

Step 6 - Decompose the criteria string

The next step is to take the contents of the exprconstant1 attribute and break it apart into its component parts. This is done using the split function to create an array of values.

Here are the modifications to accomplish this.

Step 7 - Find the new tree effective date

Now that we've decomposed the criteria string and know what tree we're using, we have the ability to determine the current effective dated version. This is done by selecting against the PSTREEDEFN table using the keys of the tree.

Here are the modifications to find the most recent effective date and apply it to the appropriate place in the criteria string and log it.

Step 8 - Save the updated query

Now that we've determined how to update the effective date in the criteria, it's time to save that change back into the system.

Here are the modifications to accomplish this.

Final Step - Loop through the queries

So far, we've only been processing a single, sample query. Now it's time to expand this code to look at all queries that start with our prefix of GS.

Here are the modifications to accomplish this. And, without further ado, the culmination of our work without showing the diffs.

Limitations / Opportunity for Enhancements

There are several areas where this code should be enhanced as part of putting it into place in your environment. You can find a list of them here.

Labels: query code tree effdt

How often has your DBA wanted to be able to quickly get a set of files containing the SQL of commonly run queries? Perhaps you'd like to make sure you version that SQL to make sure that you can see how it changes over time. With a little PeopleCode, you can do this pretty easily.

Take a look at what you can get when you version your query SQL. Pretty cool, right?

Yeah... How'd you do it?

Well, let me walk you through it one step at a time. Otherwise, the PeopleCode can get a little daunting. In order to simplify things, this example creates a simple app engine program with one section in it containing PeopleCode. This way, it can be run anytime you want (and even scheduled).

Step 1

The first step is to open a query and prove you did it successfully. Here's a link to the code that does this.

Step 2

Step 2 is to extend the PeopleCode to get the metasql property of the query object. Here are the changes to the original code (you may want to play with the settings of the difference viewer to see more of the code).

Step 3

Step 3 is to create a file from the query we've opened and gotten the SQL from. Here are the modifications for that.

Step 4

Step 4 is to loop through all the queries that beging with "GS" and create a filename with the queryname in it. Here are the changes for this.

So, here's the final Peoplecode

Check it out. Most likely, you've also seen your first set of code in Code.GreySparling.com.

Potential Enhancements

The code discussed here does have a number of places where it should be enhanced as part of deploying it in your environment. Those can be viewed here.

Labels: query sql code

I was having some beers at Pleasaton's (in)famous Hopyard last night and the topic of IBM and PeopleSoft came up. Specifically in the context of the announcement at the last PeopleSoft Connect conference about how we (PeopleSoft) were going to start heavily using the IBM WebSphere technology stack. Since I also had a regular blog reader come up and harass me at the table about a lack of blog entries this year, I figured that this would be a good one to write-up. Besides, it's been awhile since we've shared any good behind-the-scenes dirt :-)

Labels: 2010, History, IBM, Oracle, PeopleSoft