International Text in SQR for PeopleSoft Programs

System Variables to Check Encoding Settings

You can reference the values set for the ENCODING parameters in pssqr.ini/unx from your SQR for PeopleSoft programs using the following system variables:

$sqr-encoding

Stores value set to the ENCODING parameter of pssqr.ini/unx. If no value is set for this parameter, SQR automatically determines the appropriate encoding for input and output based on the operating system locale and stores values in this variable.
$sqr-encoding-database-api

Stores value set to ENCODING-DATABASE-API parameter of pssqr.ini/unx file. Except for DB2 UDB for Linux, Unix, and Microsoft Windows, the value for this parameter is automatically determined by the system and cannot be overridden by editing pssqr.ini/unx. In this case, this variable stores the encoding value automatically determined and used by system. For example, on an Oracle platform, this variable value is always UTF-8.
$sqr-encoding-file-input

Stores value set to ENCODING-FILE-INPUT parameter in pssqr.ini/unx file. If no value is set for this parameter, the input files are considered to be encoded in the encoding specified in the ENCODING parameter and this variable stores the same value as $sqr-encoding.
$sqr-encoding-file-output

Stores value set to ENCODING-FILE-OUTPUT parameter in pssqr.ini/unx file. If no value is set for this parameter, the same encoding set in the ENCODING parameter is used for file output encoding and this variable stores the same value as $sqr-encoding.
$sqr-encoding-report-output

Stores value set to ENCODING-REPORT-OUTPUT parameter in pssqr.ini/unx file. If no value is set for this parameter, the same encoding set in the ENCODING parameter is used for report output encoding and this variable stores the same value as $sqr-encoding.
$sqr-encoding-source

Stores value set to ENCODING-SQR-SOURCE parameter in pssqr.ini/unx file. If no value is set for this parameter, SQR source files are considered to be encoded in the character set encodings specified in the ENCODING parameter and this variable stores the same value as $sqr-encoding.
$sqr-encoding-console

Stores value set to ENCODING-CONSOLE parameter in pssqr.ini/unx file. If no value is set for this parameter, the same encoding set in the ENCODING parameter is used for console output encoding and this variable stores the same value as $sqr-encoding.

Note:

The system variables are read-only and cannot be overwritten at runtime. They can be used with functions and commands that take encodings as parameters, such as substrt(), lengtht(), and open.

String Length

When you work with strings in SQR, you must consider three different ways of measuring string length:

The number of characters in the string.
The number of print columns occupied by these characters.

For example, characters from the Latin alphabet normally require one print column; characters in Japanese often require two.
The number of bytes used to store the character.

SQR uses the Unicode UCS-2 method of encoding characters, which means that every character occupies two bytes; however, your database may use a different encoding that requires a different number of bytes for each character.

SQR provides the following functions to help you manage string length according to each of these criteria.

Note:

Although SQR uses Unicode internally, it can still read/write with non-Unicode (ANSI) databases and files. Refer to the OPEN command in the SQR documentation for details.

Length() and Substr().

Length() and Substr() deal with the number of characters in a string. As the PeopleSoft system field lengths (as defined in Application Designer) are character-based, the Length( ) and Substr( ) functions are useful for calculating the length of the string as it is stored in the database.

For example, the following code determines whether string &abc will fit into a database field that is 10 characters long. If the string won’t fit into the field, the code truncates the string to use only the first 10 characters.
```
If length(&abc) > 10 then
&abc = substr(&abc,1,10)
End-if
```
Lengthp() and Substrp().

Lengthp() and Substrp() deal with the number of print columns required to print the character using a monospace (nonproportional) font.

For example, the following code determines whether string &abc will fit into a print area that is 10 columns wide. If the string won’t fit into the print area, the code truncates the string to use only the first 10 columns of characters.
```
If lengthp(&abc) > 10 then
&abc = substrp(&abc,1,10)
End-if
```
Lengtht() and Substrt().

Lengtht() and Substrt() deal with the number of bytes that the string occupies in a specified character set. Typically, you would use lengtht( ) and substrt( ) if you are writing to a file in a specific character set, and you need to check or limit the byte length of the string in the output file, as would be required by most interface files.

For example, the following code determines whether string &abc will require more than 10 bytes in an output file. If the string is more than 10 bytes, the code truncates the string to use only the first 10 bytes worth of characters. The SQR system variable $sqr-encoding-file-output is used to reference the SQR.INI ENCODING-FILE-OUTPUT variable, which determines the default character set of any file that is written to by the SQR OPEN command. You can substitute any valid PeopleSoft system encoding for the $sqr-encoding-file-output variable.

If lengtht(&abc, $sqr-encoding-file-output) >10 then
&abc = substrt(&abc, $sqr-encoding-file-output,1,10)
End-if

OPEN Command

The SQR OPEN command, used to read and write files from within SQR programs, enables the report designer to specify the character set of the file being opened. You can specify a character set explicitly in the OPEN command. If you do not specify a character set, the SQR program uses the character set specified in the pssqr.ini/unx parameter ENCODING-FILE-OUTPUT or ENCODING-FILE-INPUT, depending on whether you are opening the file for reading or writing.

To integrate with a third-party system, specify the character set for SQR programs to match the target data. For example, a mainframe-based payroll system may expect an EBCDIC format file. Specifying the character set directly in the OPEN command enables the SQR program to be independent of the pssqr.ini/unx settings and enables the SQR program to create the file directly in the character set expected by the target system (without the need to convert the output in a separate step).

As another example, if you are generating text output for a mail merge in Microsoft Word, you can specify UCS-2 or Unicode-Little-Endian to the ENCODING parameter of the OPEN statement, so that Microsoft Word can import the Unicode file including international text.