Extra Hot

PeopleSoft officially supports a variety of enterprise databases, the most prominent of them are Oracle, DB2 and MSSQL. When PeopleSoft applications are delivered, one could readily assume they are tested intensively against these databases.

When developing PeopleTools customizations with SQL, do you think about whether the code being written will work for other databases as well? As a developer, the importance of this issue may not be apparent in your daily work. However, for your client or organization the long-term cost is vendor lock-in — making it more difficult and costly to switch database vendors when the need arises.

PeopleTools made it easy to code cross-platform SQL in your PeopleSoft applications, so there really is not much excuse for ignoring cross-platform compatibility issues further. There are 2 features in PeopleTools that are geared towards mitigating SQL cross-platform issues:

Use Meta-SQL Extensively

Meta-SQL is a great feature that helps bridge the gap made by inconsistencies between database implementations. Now, meta-SQL actually serves multiple purposes in addition to what is described here. PeopleBooks classifies meta-SQL statements into 3 types: construct, function, and meta-variables. The statements that are relevant for cross-platform SQL are those that acts like pseudo-functions and pseudo-operators. You could use meta-SQL in most places where you could code SQL in PeopleTools (consult PeopleBooks for some limitations):

• in SQL of view records
• in PeopleCode function calls and methods, such as SQLExec(), ScrollSelect(), CreateSQL(), Rowset.Fill(), etc.
• in SQL definitions
• in Application Engine SQL steps

Before PeopleSoft send any SQL requests created by the application programmer, it first checks for meta-SQL statements. Based on the installed database platform and version, it then expands the meta-SQL statements to platform-specific SQL code. An exception to this is SQL View records’ SQL — here, the meta-SQL expansion is performed during the build process. Some examples of meta-SQL statements are: %DateAdd, %Concat, %CurrentDateIn, %FirstRows. The following examples illustrate their use.

%DateAdd(HIRE_DT,100) expands to:

DB2: HIRE_DT + 100 days
MSSQL: dateadd(day, 100, HIRE_DT)
Oracle: HIRE_DT + 100

Select TEXT1 %Concat TEXT2 From TBL1 expands to:

DB2, Oracle: Select TEXT1 || TEXT2 From TBL1
MSSQL: Select TEXT1 + TEXT2 From TBL1

%CurrentDateIn expands to:

DB2: Current Date or Current_Date
MSSQL: Getdate()
Oracle: Sysdate or Current_Date

Select %FirstRows(10) FIELD1 from TBL1 expands to:

DB2: Select FIELD1 from TBL1 Fetch first 10 rows only
MSSQL: Select Top 10 FIELD1 from TBL1
Oracle: Not sure if this is implemented in Oracle. This may be enforced by the application, i.e, SQL.Fetch() returns false after 10 rows.

If you’re not very familiar with different database-specific SQL implementations, it may seem difficult to judge how much of the SQL you code is not cross-platform. A good practice is to familiarize yourself with the available meta-SQL statements in PeopleBooks. If a certain function or operation is available as meta-SQL, it probably has varied database-specific implementations. Another thing to remind yourself is that if your query is performing date manipulations, it probably is not cross-platform. Date operations implementation is one area where the database vendors deviate the most.

Another reason why I think this is a good practice to learn is that it also offers you a good skills advantage as a developer. Once you get used to coding with meta-SQL, developing for other clients using different databases with be smooth for you.

Specify SQL Editor Platform for Platform-specific SQL

Meta-SQL is great, but it is not omnipotent. There certainly are situations where a certain query can’t be expressed using meta-SQL for cross-compatibility. A simple example is getting the correct age of a person. In DB2, the SQL for this is Year(Current_Date - BIRTHDATE); in MSSQL, it’s Datediff(year, BIRTHDATE, Getdate()); and in Oracle, Trunc(Months_between(Sysdate, BIRTHDATE)/12,0).

In these situation, it is best to mark your SQL as being specific to a platform. You do this by changing the value of [default] in the top-left dropdown of the SQL Editor. You do not need to fill up SQL implementations for the platforms you don’t currently use (well… unless you have a big budget to spare). This may not make your application cross-compatible immediately, but this should make it easier to spot SQL code that needs some recoding work.

The SQL Editor is available to views, SQL definition, and Application Engine SQL steps. If your query is used by PeopleCode functions such as SQLExec(), you should create a SQL definition (File > New > SQL) to store your query in. You then use the FetchSQL() PeopleCode function to retrieve the SQL code from the definition: SQLExec(FetchSQL(SQL.YOUR_SQLDEFN_NAME));.

Tying it together

A better way to manage this is to break your query and only store to a SQL definition those fragments that are platform-specific. This way, those platform-specific fragments could even be reused! This is akin to defining your own meta-SQL functions for use on other SQL code.

As an illustration, let’s take the age example. Create a SQL definition and name it GETAGE. Under DB2, write Year(Current_Date - %P(1)); under MSSQL, write Datediff(year, %P(1), Getdate()); and under Oracle, write Trunc(Months_between(Sysdate, %P(1))/12,0). %P(n) denotes bind variables that are substituted by the %SQL() meta-SQL function. A query that checks for the age of a person would now look like this:

Select NAME, %SQL(GETAGE,BIRTHDATE), ADDRESS1
From PS_PERSONAL_DATA
Where %SQL(GETAGE,BIRTHDATE) > 30