Tales of a Techie

Amazing how after all these years, there’s still nuggets of information that I keep finding out about PeopleSoft. Today for example I am working on an Application Engine for one of our customers. This particular AE is being built to replicate a dynamic list of tables from one database to another as part of an archiving strategy (more on that soon).

I began building a SQL Statement which references the sysobjects view, checking to see if a table exists in the target database, if it does exist then it must be dropped. My Code looked something like this:

&MySQLStatement = "IF EXISTS (SELECT 'X' FROM DEMO.[dbo].sysobjects WHERE TYPE = 'U' AND NAME = 'PS_MYTABLE') DROP TABLE DEMO.[dbo].PS_MYTABLE"
&MySQL = CreateSQL(&MySQLStatement);


I ran this SQL in SQL Client, ran perfectly. However the App Engine kept returning no success due to SQL Errors. Further investigation showed that PeopleSoft will Uppercase all the SQL inside &MySQLStatement, making the code passed to SQL Server look like this:

IF EXISTS (SELECT 'X' FROM DEMO.[DBO].SYSOBJECTS WHERE TYPE = 'U' AND NAME = 'PS_MYTABLE') DROP TABLE DEMO.[DBO].PS_MYTABLE

Which of course will fall over as SYSOBJECTS in the Uppercase does not exist. ([DBO] should also be referenced as [dbo]), I scratched my head for a while, coming up with all sorts of mad ways around this, none of which worked. Finally I resorted to PeopleBooks and the Chapter for the SQL Class inside the PeopleTools PeopleCode API Reference Book, the section section in this Book has a “See Also of %NoUppercase”, clicking on this took me through to the PeopleCode Language Reference Book and a description of the %NoUppercase Meta-SQL Command, which states:

%NoUppercase - When processing a SQL statement, the system automatically casts all field names and possibly record names to uppercase when processing a SQL statement. When processing records from a third party, fields that are lowercase are cast into uppercase, which can create runtime issue on case-sensitive platforms.

To prevent this, use the %NoUppercase meta-SQL statement at the beginning of the SQL statement.


Very interesting, and not a meta-SQL statement I’d come across before. Utilising this %NoUppercase statement now makes my code look like this:

&MySQLStatement = "%NoUppercase IF EXISTS (SELECT 'X' FROM DEMO.[dbo].sysobjects WHERE TYPE = 'U' AND NAME = 'PS_MYTABLE') DROP TABLE DEMO.[dbo].PS_MYTABLE"
&MySQL = CreateSQL(&MySQLStatement);


This is then passed exactly as is to SQL Server which now executes it perfectly.

In conclusion file the %NoUppercase meta-SQL statement under your “Very Useful Stuff” category and don’t forget it!