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:

Local Record &rec = CreateRecord(Record.xxx);
Local string &exists;

REM set &rec key field values, copy from component buffer, 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):

Function Merge(&rec As Record)
Local string &exists;

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

If (All(&exists)) Then
SQLExec("%Update(:1)", &rec);
Else
SQLExec("%Insert(:1)", &rec);
End-If;
End-Function;

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...

4 comments:

kdrymer said...

Hi Jim - I realize this post is quite old but being new to programming in App Engine and I found the information very helpful. One question, I'm trying to trace through an AppEngine program that utilizes PeopleCode and there is a declared variable within an OnExecute step with this usage "&SQL1 = CreateSQL("%Insert(:1)");" I'm trying to determine what is being populated (so that I can modify it hopefully) inside the (:1) variable. I've tried searching Record definitions for FUNCLIB but did not find what I felt was relevant. Any ideas how I can dig deeper to find this? Thanks again!

Jim Marion said...

@Kevin, look for &SQL1.Execute();

Mohit said...

Hi Jim -

I am trying to write insert script in file through peoplecode, how we can do that
or how we can create .dat file through peoplecode

like
&SQL = CreateSQL("%SelectAll(:1) where", &Recname)
while &sql.fetch(&recname)
---- write into file all the fielvalues
end-while

Jim Marion said...

@Mohit, what file format are you trying to create? A data mover output file is not easy, but a CSV or XML is trivial. Yes, you can write to a file using your exact code. Just use &file.WriteLine("string to write"); assuming &file represents your open file.