PeopleCode | Using CreateArrayAny to retrieve data from unknown tables
A. A Refresher on Arrays
We’ve already seen from Tip 013 that an array is defined as follows:
Local array of string &gradeCodes = CreateArray(""); Local array of number &gradeValues = CreateArray(0);
The ‘CreateArray’ statement sets up an initial empty array of the type specified by the first parameter. So above we have defined an array of strings, followed by an array of numbers.
While ‘CreateArray’ works for most situations, it does have a couple of limitations:
• The array type must be defined in advance. We must know that we are receiving strings or numbers or dates.
• The array can only contain values of the specified data type. We can’t ‘mix and match’ values in the array, having a string, number and date field all stored together in the same array.
This is where ‘CreateArrayAny’ comes into the picture. This allows us to define an array consisting of any number of objects types. Better still, the array can contain a combination of values. Strings, numbers, booleans, and dates can all be stored in the same array.
B. Selecting Data from an Unknown Table
To see why ‘CreateArrayAny’ is useful, let’s consider the example of having to select data from an unknown SQL table.
This type of example often comes up when doing data conversion. We are faced with the task of having to create conversion routines that must select data from any number of external tables. Since we don’t know the table’s name or definition at the point of creating the program, we can’t make any assumptions as to the number of fields or the field types.
From Tip 021, we could use the ‘%SelectAll’ meta-SQL construct to allow us to directly populate the equivalent record object, but this assumes that the table has been created in App Designer and a record definition already exists. If the table has been created outside of PeopleSoft – but is still accessible via the database – we cannot rely on record objects.
The CreateArrayAny function allows us to select all fields from the unknown table at run-time. Here is an example of the function being used:
Local integer &i; Local string &sql_text = "select * from external_table"; Local SQL &Select_SQL = CreateSQL(&sql_text); Local array &FieldArray = CreateArrayAny(); While &Select_SQL.Fetch(&FieldArray) For &i = 1 To &FieldArray.Len &logFile.WriteLine("Field Number " | &i | " contains value " | &FieldArray [&i]); End-For; End-While;