Using the PSSQR.INI File and the PSSQR Command Line

This chapter discusses the:

• pssqr.ini installation process.

• Default settings section.

• Processing-Limits section.

• Environment sections.

• Locale section.

• Fonts section.

• HTML-Images section.

• PDF Fonts section.

• PDF Fonts: Exclusion Ranges section.

• TrueType Fonts section.

• Enhanced-HTML section.

• Colors section.

• The use of pssqr.exe command-line options.

Installing PSSQR.INI

The pssqr.ini file is the initialization file for SQR for PeopleSoft . SQR uses the settings and parameters in this file during the compile and execution phases.

The installation process installs a default initialization file called pssqr.ini, which is located in the SQR directory under the <PS_CFG_HOME> directory. The installation process also installs files named pssqr<language_cd>.ini, which are used to create language-specific configurations.

Microsoft Windows Platforms

In Microsoft Windows, SQR looks for the initialization file in the following locations in this order:

1. The file name specified by the -ZIF{file} command-line flag.

2. The directory in which the executable image resides.

3. The Microsoft Windows system directory.

z/OS

In z/OS, SQRINI is required during initialization. You must specify the dataset member &PSHLQ..SQRSRC(PSSQRINI) in the JCL to start the SQR process.

All Other Platforms

In all other platforms, SQR looks for the initialization file in the following locations in this order:

1. The file name specified by the -ZIF{file} command-line flag.

2. The current working directory.

3. The directory specified using the SQRDIR environment variable.

   SQR automatically sets up SQRDIR.

You can make changes or additions to the pssqr.ini file.

This example shows the format of the pssqr.ini file:

; Comments are lines that start with a semicolon.  The semicolon
; must be the first character of the line and therefore cannot be
; part of another line.
;
; Leading and trailing space characters are ignored.  To preserve
; the space characters you must surround the value with either
; single (') or double (") quote characters.  SQR will remove
; them when the entry is processed.
;
[Section_Name]
Entry = Value
     .
     .

[Another_Section_Name]
Entry = Value
     .
     .

Default Settings Section

This table describes the SQR default settings:

ForceSpaceAfterComma=⇒
{TRUE|FALSE}

ShowDBWarnings=⇒
{TRUE|FALSE}

AllowDateAsChar=⇒
{TRUE|FALSE}

Begin-Select  
  [$Col1] &col1=Text  
  [$Col2] &col2=Date  
  [$Col3] &col3=Number  
  from MyTable
End-Select

OUTPUT-FILE-MODE

OUTPUT-FILE-MODE

OUTPUT-FILE-MODE

OUTPUT-FILE-MODE

OUTPUT-FILE-MODE

LOCALE

DEFAULT-NUMERIC

OutputFormFeedWithDashD=⇒
{TRUE|FALSE}

OutputTwoDigitYearWarningMsg=⇒
{TRUE|FALSE}

UseY2kCenturyAlgorithm=⇒
{TRUE|FALSE}

Note. Use the setting V30 to process numbers in the same manner as in previous (before V4.0) releases. Specifically, all numeric variables and literals are declared as FLOAT, including integer literals.

Processing-Limits Section

Use the Processing-Limits section to define the sizes and limitations of some of the internal structures used by SQR; these definitions directly affect memory requirements. The entries are the same as those used in the file specified with the -MFILE command-line flag. If the -MFILE command-line flag is used, the Processing-Limits section of the file is not processed.

This table describes some of the internal structures used by SQR:

BREAKS

DYNAMICARGS

EXPRESSIONSP⇒
ACE

FORWARDREFS

LONGSPACE

ONBREAKS

POSITIONS

PROGLINEPARS

PROGLINES

QUERIES

QUERYARGS 

SQLSIZE

STRINGSPACE

SUBVARS

VARIABLES

WHENS

The maximum value refers to the number of entries allowed, as shown in the previous table; however, limits are lower for 32-bit machines. In either case, SQR indicates the limit if you exceed it.

In addition to increasing the sizes, you may also decrease them to decrease the amount of memory used. Decreasing them might be advantageous, for example, for certain applications running in the PC environment, where memory is limited.

Environment Sections

The Environment sections { Common | Oracle | Informix | ODBC | DB2 | RDB | Sybase ] } define the environment variables used by SQR. You can define an environment variable in multiple environment sections; however, a definition in a database-specific environment section takes precedence over an assignment in the Environment:Common section.

You can set these environment variables: SQRDIR, SQRFLAGS, and DSQUERY. In Microsoft Windows systems, SQRDIR is required and is automatically defined in the appropriate database-specific environment section during the SQR installation. The other environment variables are optional. SQRFLAGS specifies the default command-line flags for all invocations of SQR. DSQUERY (Sybase only) identifies the default Sybase server to use.

In Microsoft Windows systems only, the SQR Extension section defines DLLs containing new user functions (UFUNC) and user calls (UCALL). UFUNC and UCALL reside inside SQREXT.DLL and other DLLs, or in one or the other.

When SQRW.DLL and SQRWT.DLL are being loaded, they look for SQREXT.DLL in the same directory and for any DLLs specified in the SQR Extension section in sqr.ini, such as:

[SQR Extension]
c:\sqrexts\sqrext1.dll=
c:\sqrexts\sqrext2.dll=
c:\sqrexts\sqrext3.dll=

Any new extension DLLs containing new user functions must be listed in the SQR Extension section in sqr.ini.

See Using Interoperability Features.

For Oracle on Windows, SQR uses dynamic binding of Oracle routines. When SQR attempts to access an Oracle database, it searches for the Oracle DLL as follows:

• The file described by the value of ORACLE_DLL entry in the Environment:Oracle section of the sqr.ini file.

• OCIW32.DLL (Oracle supplied).

• ORANT71.DLL (Oracle supplied).

This table describes SQRParseSQLEnable:

Locale Section

This section specifies the default settings for the locale identified by locale-name (which can consist of letters from A to Z, numbers from 0 to 9, a hyphen, and an underscore). A number of locales are predefined in the pssqr.ini file. Depending on your application, you may have to alter the settings for these locales or add new locales. You can reference or alter a locale at runtime using the ALTER-LOCALE command. The following table describes the entries for a locale section.

Note. The SYSTEM locale is provided for your reference but is commented out. The settings for the SYSTEM locale, if set, are ignored. Use the ALTER-LOCALE command to change the SYSTEM locale settings at runtime.

See Avoiding Older SQR Commands.

NUMBER-EDIT-MASK

MONEY-EDIT-MASK

DATE-EDIT-MASK

INPUT-DATE-EDIT-MASK

MONEY-SIGN

MONEY-SIGN-LOCATION

THOUSAND-SEPARATOR

DECIMAL-SEPARATOR

DATE-SEPARATOR

TIME-SEPARATOR

EDIT-OPTION-NA

EDIT-OPTION-AM

EDIT-OPTION-PM

EDIT-OPTION-AD

EDIT-OPTION-BC

DAY-OF-WEEK-CASE

DAY-OF-WEEK-FULL 

DAY-OF-WEEK-SHORT

MONTHS-CASE

MONTHS-FULL

MONTHS-SHORT

Fonts Section

The Fonts section lists the fonts available to SQR when printing on Microsoft Windows printer devices (using the -PRINTER:WP command-line flag), viewing SPF output using SPF Viewer on Microsoft Windows (SQRWV), and creating style sheet for Enhanced HTML output. This section does not apply to PostScript or HP LaserJet printer types.

This section provides an overview of fonts available to SQR and discusses how to:

• Add font entries.

• Specify character sets in Windows.

See DECLARE-PRINTER, Avoiding Older SQR Commands.

Adding Fonts Entries

The Fonts section contains a number of predefined font entries. You can add entries by using the font numbers 900 through 999. Each entry consists of a font name, a font style (fixed or proportional), and a bold indicator, all of which are associated with a font number, for example:

   4=Arial,proportional
     or
   300=Courier New,fixed,bold

Note. A proportional font style is assumed if the second parameter starts with a P. Bold is assumed if a third parameter is supplied.

Using the font number, commands such as ALTER-PRINTER and DECLARE-PRINTER can reference a particular font style.

Specifying Character Sets in Windows

In Microsoft Windows, you can use the CharacterSet entry either to determine the Microsoft Windows default character set or to specify a character set. The CharacterSet entry enables you to print any standard character set to a Windows printer (-PRINTER:WP) or to view an SPF file that displays the appropriate character set.

The syntax is:

CharacterSet=DEFAULT|AUTO|character_set

The arguments are:

• DEFAULT reflects current SQR functionality.

• AUTO automatically determines the default character set of the Microsoft Windows installation and uses the default set when generating reports.

  CharacterSet specifies one of these keywords: ANSI, ARABIC, BALTIC, CHINESEBIG5, EASTEUROPE, GB2312, GREEK, HANGUL, HEBREW, JOHAB, MAC, OEM, RUSSIAN, SHIFTJIS, SYMBOL, THAI, TURKISH, VIETNAMESE.

HTML-Images Section

The HTML-Images section defines the parameters that SQR uses when generating HTML report output files. This table describes the parameters SQR uses when generating HTML report output files:

Note. SQR does not validate any of the graphic image file names provided. The user is responsible for ensuring that the graphic image files are in a location that the browser can access.

PDF Fonts Section

The PDF Fonts section lists the available fonts for SQR when printing using the -PRINTER:PD command-line flag. Fonts specified are case sensitive.

Unlike the Fonts section, the PDF Fonts section uses a list of fonts mapped to a single font number. You can specify up to 10 fonts for a single font number. Depending on the character to print, SQR determines the font to use from the list. The following is the syntax of this font list:

Font Number=Font1, Font2, Font3 ...

The list is ordered by priority from the left. If Font1 has a glyph (an image of a character) for the character you want to print, then t is used; but if Font1 does not have a glyph, then Font2 is checked and used if it has a glyph for that character.

Font Number is a decimal number that specifies fonts in the SQR program using statements like DEFINE-PRINTER or ALTER-PRINTER.

Font numbers that are multiples of 100 (300, 3200, and so on) are recognized as a bold version of the base font. For example, font 300 is the bold version of Font3.

Font numbers 30 to 39 are recognized as italic fonts, and multiples of 30 to 39 (3000, 3100, and so on) are bold italic versions of the font. If you assign nonbold fonts for a font number recognized as bold, such as 600, the text is printed in bold.

Font1, Font2, and Font3 represent the font name. You can use Adobe Reader core fonts, Adobe Reader Asian Font Pack fonts, or TrueType fonts.

Adobe Reader core fonts are the fonts that Adobe Reader natively supports. The core fonts are:

• Courier

• Courier-Bold

• Courier-Oblique

• Courier-BoldOblique

• Helvetica

• Helvetica-Bold

• Helvetica-Oblique

• Helvetica-BoldOblique

• Times-Roman

• Times-Bold

• Times-Italic

• Times-BoldItalic

• Symbol

• ZapfDingbats

Adobe Reader Asian Font Pack fonts are the font packages Adobe provides as an add-on to Adobe Reader for the purpose of viewing Asian text. You can download these fonts from the Adobe website or, if you use Adobe Reader 6, the Asian Font Pack automatically downloads when you open a PDF file that contains a font from the Asian Font Pack. Alternatively, you can use the Adobe Reader localized to any of the Japanese, Chinese, or Korean languages. These localized versions come with the same fonts that are included in the Asian Font Pack. These Asian Font Pack fonts are used with SQR:

• HeiseiKakuGo-W5 (Japanese)

• HeiseiMin-W3 (Japanese)

• MHei-Medium (Traditional Chinese)

• MSung-Light (Traditional Chinese)

• STSong-Light (Simplified Chinese)

• HYGoThic-Medium (Korean)

• HYSMyeongJo-Medium (Korean)

In addition, you can use TrueType/OpenType fonts that exist on the machine where you run SQR. If you use TrueType fonts for PDF output, they will be always embedded as subset of the original font. To use TrueType fonts for PDF output, you need to specify the mapping between the font name and the font file path in the [TrueType Fonts] section, and then include the font name in the font list under the [PDF Fonts] section. More information about TrueType fonts is included in a later section in this chapter.

See TrueType Font Section.

PDF Fonts: Exclusion Ranges Section

The PDF Fonts: Exclusion Ranges section defines character ranges you want to exclude from the range a font covers. Without an exclusion range, SQR uses all of the character ranges a font covers, and then checks the font listed in the next priority only when it does not cover a character. By specifying an exclusion range, you can use a font in a lower priority for a specific character or a range of characters. This mechanism allows users to control which font to use down to a single character level.

The syntax in this section is as follows:

Font Number=Font1 Exclusion Range, Font2 Exclusion Range, ...

Exclusion ranges apply to the list of fonts defined in the PDF Fonts section. For example, if you have a PDF Fonts section like the following:

3=Courier,Cumberland,HeiseiKakuGo-W5,...

In addition, if you define the PDF Fonts: Exclusion Ranges section like this

 3=0x20AC,0x3070-0x30FF,,

then you are defining that Euro currency symbol (0x20AC in Unicode) is rendered using the Cumberland font, even though Courier has it. In addition, Greek characters (from 0x3070 to 0x30FF in Unicode) are rendered using HeiseiKakuGo-W5 font, even though Cumberland has them.

Each Exclusion range is specified using Unicode (UCS-2) codepoint, in decimal or in hexadecimal, in the following ways:

0x20AC (single character)

160-255 (range)

0x20AC|0x00A0-0x00FF (multiple characters and ranges)

Hexadecimal numbers need to be preceded by 0x; otherwise, they are recognized as decimal.

TrueType Font Section

The TrueType Font section defines the mapping of font name, which is an alias SQR uses internally to look up a font and physical font file name. You need to specify the font in the full path, unless you have placed fonts in a directory specified in the Font Path parameter or in a Windows font folder if you are running SQR on Windows.

The syntax is:

font name=fontfile path

For example, if you have the font courier.ttf in the c:\user\fonts directory, you set the following in this section:

CourierNew=c:\user\fonts\courier.ttf  

Font name can be any string that is convenient for you to identify the font, and it does not need to correspond to the internal name of the font. You should not use the same font name that is used for Adobe core fonts or for Asian Font Pack fonts. If you set the same name for any of Adobe core fonts or Asian Font Pack fonts, then the TrueType font is used.

Note. TrueType font embedding is now supported by SQR running on z/OS. However, the Font Path parameter under the TrueType Font section is not supported for z/OS. If you are on z/OS, you need to specify the full path to each entry.

If you have a TrueType Collection (TTC) file, you will also need to specify the font number to access the specific font included in the collection. For example, if MS P Mincho is included in the TrueType Collection file msmincho.ttc, then you will need to specify:

MSPMincho=c:\winnt\fonts\msmincho.ttc,1

The font number within the TrueType collection starts at 0. msmincho.ttc contains MS Mincho and MS P Mincho in this order; thus the number 0 represents MS Mincho and 1 represents MS P Mincho. If you do not specify a font number, SQR uses font with font number 0 from the TrueType Collection file.

Note. You can use only Microsoft type of Unicode-based TrueType/OpenType fonts with SQR. SQR requires TrueType/OpenType font to have CMAP table with Platform ID 3 (Microsoft), Encoding ID 0 (Symbol), 4 (UCS-2), or 10 (UCS-4) and table format 4 or 12. SQR does not support OpenType fonts with CFF (Postscript) outline. You can use OpenType fonts with TrueType outlines, but SQR does not make use of advanced layout features provided with OpenType font.

Enhanced-HTML Section

The Enhanced-HTML section is used to define various default actions that SQR takes when generating HTML output using the -EH command-line flag. This table describes the default actions that SQR takes when generating HTML output using the -EH command-line flag:

Colors Section

The Colors section defines the default colors that you can use in your SQRs. Enter the default colors in this format:

[Colors]
color_name = ({rgb])
color_name = ({rgb])
...
color_name = ({rgb])

The default colors implicitly installed are:

• black=(0,0,0)

• white=(255,255,255)

• gray=(128,128,128)

• silver=(192,192,192)

• red=(255,0,0)

• green=(0,255,0)

• blue=(0,0,255)

• yellow=(255,255,0)

• purple=(128,0,128)

• olive=(128,128,0)

• navy=(0,0,128)

• aqua=(0,255,255)

• lime=(0,128,0)

• maroon=(128,0,0)

• teal=(0,128,128)

• fuchsia=(255,0,255)

Using PSSQR.EXE Command-Line Options

The PeopleSoft system provides a shell called pssqr.exe that extends SQR to handle the submission of SQR programs under Microsoft Windows and UNIX/Linux operating systems. Pssqr.exe implements a process that ensures that output is sent to the appropriate destination in a way that is consistent across platforms.

Note. PeopleSoft does not support running pssqr.exe directly from a DOS or Unix/Linux command line. Pssqr.exe is a wrapper program used by PeopleSoft Process Scheduler to run SQR reports; it is not designed to run manually outside of Process Scheduler.

Pssqr.exe does not run on OS/390, however, all output formats are supported on all platforms.

Pssqr.exe provides the following features:

• Expanded output formats: SQR Viewer, HTML, PDF, CSV (Spreadsheet Standard), HP, Postscript, Line Printer.

• Expanded printer format: Microsoft Windows Default Printer (Win32 only), HP, Postscript, Line Printer.

• Enhanced delivery of reports to printers: PSSQR sends reports to the printer instead of SQR, which resolves issues that can be encountered on non-Microsoft networks.

• File output and logs with unique names. If the process instance is sent, the file names will be <SQR Program>_<Instance>.xxx.

  If not, then the log names will be <SQR Program>_<timestamp>.xxx.

• Common command line interface for both Microsoft Windows and UNIX/Linux.

• Capability to read Configuration Manager settings to determine the flags (SQRFLAGS) to use.

• Support for multiple report output.

  Default values come from Configuration Manager if run from the command line.

This table describes the command-line parameters:

Note. Avoid using a hyphen (-) or @ sign in the PSSQR.EXE command line, for instance, as part of the run control ID. The hyphen (-) and @ sign characters are SQR-reserved characters.

See Also

Using Reserved Characters