A blog containing development tips I have learned through the years as a PeopleSoft developer.

Sunday, May 03, 2009

Productivity gains through Meta-SQL

I hope the designer of PeopleSoft's Meta-SQL is extremely wealthy. I believe Meta-SQL usage is one of the most under utilized PeopleTools development practices. Do you want to improve your productivity as a PeopleSoft developer? Learn Meta-SQL. Consider the %InsertSelect Meta-SQL statement... A PeopleSoft SQL insert into/select from statement may contain 100 or more fields. Maintaining the mappings between the insert clause and the select clause can be quite daunting. %InsertSelect eliminates this mapping nightmare through convention: source fields with the same name are automatically mapped to destinations with the same name. If your scenario deviates from this convention, then a minor configuration in the Meta-SQL statement allows you to override the default behavior.

Likewise, using Meta-SQL, it is possible to write generic SQL that works regardless of the target table. Consider the following PeopleCode:

LocalRecord &rec =CreateRecord(Record.xxx);Localstring &exists;

REMset&reckeyfieldvalues,copyfromcomponentbuffer,etc;

SQLExec("SELECT'X'FROM%Table(:1)WHERE%KeyEqual(:1)", &rec, &exists);

No matter what table I specify, this same SQL statement will tell me if a matching row exists in that table. PeopleSoft includes many of these Meta-SQL shortcuts. By combining a couple of Meta-SQL statements into a FUNCLIB, I can create a generic PeopleCode compliment to the Oracle merge statement (some call it UPSERT):

Where would I use a merge function like this? Let's say you need to clone data in the current buffer, but change a key field (EFFDT perhaps?). Using the Copy methods of stand-alone Rowsets and Records, I can copy the component buffer into stand-alone rowsets and records, change the key fields, and then, with a generic loop, iterate over the rows and records, inserting or updating database values using the Merge function above.

There are several Meta-SQL routines. Some exist to provide database independence, but many exist to provide meta-data driven shortcuts for repetitive tasks. I encourage you to take some time to review the PeopleBooks Meta-SQL documentation. I trust you will be pleasantly surprised with the productivity gains you can achieve through Meta-SQL. Now, if I could just get someone to create an open source port of PeopleSoft's Meta-SQL that I can use with JDBC...

The views expressed on this blog are my own and definitely reflect the views of JSMPros, my employer. The views and opinions expressed by visitors to this blog, however, are theirs and do not necessarily reflect my opinions or the opinions of JSMPros.

Please be advised: this site contains code snippets and examples that may require modifications to delivered PeopleSoft objects. Before modifying delivered code, make sure your development team approves of your modification. Likewise, ensure that you have properly documented your modification according to your organization's best practices. You and your organization will be responsible for maintaining your modifications through patches and upgrades.

The author provides development tips and modification ideas for informational purposes only. Tips and techniques presented on this site should be considered proof of concepts only. Neither the author or his employer assume any liability for problems resulting from the implementation of these ideas.