Enterprise PeopleTools 8.51 PeopleBook: SQR for PeopleSoft Developers

Introducing the Sample SQR Program

This chapter discusses how to:

Use this guide..
Set up the sample database.
Understand the sample program for printing a text string.
Create and run a sample SQR (Structured Query Report) program.
View SQR output.

Using with This Guide

Initial sections of this guide teach the basic uses of SQR. You learn how to:

Create a variety of reports, such as tabular, cross-tabular, and master and detail reports.
Produce mailing labels, form letters, and envelopes.
Enhance your reports with typeset-quality fonts and graphics.
Produce graphs and charts that help you present data and trends visually.

Subsequent sections describe the advanced features and uses of SQR. You learn how to:

Create HTML output and publish reports on the internet, an intranet, or an extranet.
Create reports that can be easily ported between different systems and databases and that support different printer and display types.
Create reports that format dates, numbers, and money according to local preferences.
Integrate SQR with other software packages, such as front-end user interface tools and spreadsheets.
Extend SQR with procedures and functions that are written in C.
Test and debug programs.
Tune programs for optimum performance.

The code examples demonstrate standard SQR programming style. Use this standard style to make your code easier for other SQR programmers to understand.

You can run the program examples in this guide without modification against the Oracle, Sybase, and Informix databases and run against other databases with minor modifications.

Audience

This guide was written for programmers who develop reports for relational databases. To use this guide effectively, you need a working knowledge of SQL and experience writing software programs. You also must be familiar with your particular database and operating system.

How to Use SQR for PeopleSoft Developers

You can just read this book and study the sample programs. However, Oracle encourages you to try these programs for yourself and to experiment with them. Make some changes to the sample programs and see how they run.

To use the sample programs, you must first install SQR for PeopleSoft. SQR for PeopleSoft is installed automatically when you install PeopleTools.

If you installed all of the program components, the sample programs are located in the TUTORIAL directory underneath <PS_HOME>\bin\sqr\<database_platform>.

You can run the sample programs on any hardware platform, but you may find it somewhat easier to review SQR program results from the Microsoft Windows platform by using the SQR Viewer or a web browser to verify your results.

Note. You can set up the sample database, as described in a moment, and run the sample programs with any username and password, although you may want to use an account that does not hold important data.

Convention	Description
{ }	Braces enclose required items.
[ ]	Square brackets enclose optional items.
...	Ellipses indicate that the preceding parameter can be repeated.
\|	A vertical bar separates alternatives within brackets, braces, or parentheses.
'	A single quote starts and ends a literal text constant or any argument that has more than one word. Important! If you are copying code directly from the examples in the PDF file, make sure that you change the slanted quotes to regular quotes; otherwise, you will receive an error message.
,	A comma separates multiple arguments.
( )	Parentheses must enclose an argument or element.
UPPERCASE	SQR commands and arguments are uppercase within the text, but lowercase in the code examples. (Note that these commands are case-insensitive.)
Variable	Information and values that you must supply appear in variable style.
hyphen versus underscore	Many SQR commands, such as BEGIN-PROGRAM, use a hyphen, whereas procedure and variable names use an underscore. Procedure and variable names can contain either a hyphen or underscores, but using underscores in procedure and variable names to distinguish them from SQR commands is best. It also prevents confusion when you mix variable names and numbers in an expression, where hyphens could be mistaken for minus signs.

Setting Up the Sample Database

To run the sample programs in this guide, you must create a sample database. To do so, run the loadall.sqr program.

Change to the SAMPLE (or SAMPLEW, for Microsoft Windows) directory under <PS_HOME>\bin\sqr\<database_platform>.
At the command line, enter:
```
sqr loadall username/password
```

If SQR is installed on Microsoft Windows, you can run loadall.sqr by double-clicking the Loadall icon. If your system does not display this icon, run loadall.sqr from the SAMPLEW directory of SQR for PeopleSoft.

If an individual table already exists, you are prompted to enter:

A: Abort the load.
S: Skip the specified table.
R: Reload the specified table.
C: Reload all tables.

You can also run this as a batch program by entering the preferred option (A, S, R, or C) at the command-line. For example:

sqr loadall username/password a

Considerations for DBX

The following considerations apply for DB2 on AIX and DB2 on ZOS.

DB2 on AIX

The DB2CLI.INI file (on Windows with the DB2 ODBC connection) should have the following entry.

This file is typically located in C:\Apps\DB\Db2 directory

[common]PATCH2=6DISABLEKEYSETCURSOR=1

DB2 on ZOS

PSSQR.UNX or PSSQR.INI file should have the following line.

FORCESPACEAFTERCOMMA=TRUE

Understanding the Sample Program for Printing a Text String

The first sample program is the simplest SQR program. It prints a text string:

Program ex1a.sqr
begin-program
   print 'Hello, World.' (1,1)
end-program

Note. For your convenience, all of the program examples and their output files are included with the installation. As mentioned, these samples are in the SQR for PeopleSoft directory <PS_HOME>\bin\sqr\<database_platform>\SAMPLE (or SAMPLEW, for Microsoft Windows).

Take another look at the sample program. This program contains three lines of code, starting with BEGIN-PROGRAM and ending with END-PROGRAM. These two commands and the code between them make up the PROGRAM section, which is used to control the order of processing. The PROGRAM section is required, and you can have only one. It typically goes at or near the top of the program.

The PROGRAM section contains a PRINT command, which in this case prints the text Hello, World. This text is enclosed in single quotation marks ('), which are used in SQR to distinguish literal text from other program elements.

The last element of the PRINT command indicates the position on the output page. An output page can be thought of as a grid of lines and columns. The (1,1) indicates line 1, column 1, which is the upper-left corner of the page.

Note. In SQR, you must place each command on a new line. You can indent SQR commands.

Creating and Running a Sample SQR Program

This section discusses how to:

Create an SQR program.
Run an SQR program.

Creating an SQR Program

To create an SQR program:

Open a text editor and enter the code in the sample program exactly as shown, or open the ex1a.sqr file from the TUTORIAL directory.
If you are writing the sample program, save your code with the name ex1a.sqr.

SQR programs usually have a file extension of .sqr.

Running an SQR Program

To run the sample program:

Change to the directory in which you saved the program using the command that is appropriate to your operating system.
Enter the appropriate SQR program command at the system command prompt (UNIX/Linux or Microsoft Windows) or from within the SQR application’s graphical user interface (GUI), where available (Microsoft Windows only).

If you are using the command line, use SQR (UNIX/Linux) or SQRW (Microsoft Windows) to invoke SQR. Enter sqr or sqrw, the SQR program name, and the connectivity string, all on one line, by using this syntax:
```
[sqr or sqrw] [program] [connectivity] [flags ...] [args ...] [@file ...]
```

In a common configuration, you may be running SQR on Microsoft Windows against an Oracle database that is located on another machine in the network. Use this command format:

sqrw ex1a username/password@servername -KEEP

If you correctly replace username, password, and servername with the appropriate information, you should have a command line like this:

sqrw ex1a sammy/baker@rome -KEEP

To produce the output file for this exercise, the example uses the -KEEP flag, which is defined later in this guide.

See Enterprise PeopleTools 8.51 PeopleBook: SQR Language Reference for PeopleSoft.

See Specifying Output File Types by Using SQR Command-Line Flags.

Command Line Examples

Here are some examples for running SQR from the command line for different databases and platforms.

DB2 on Microsoft Windows

%PS_HOME%\bin\sqr\DB2\BINW\sqrw %PS_HOME%\sqr\xrfwin.sqr T846U10/testdb2/t3stdb20 -oc:\sqr_out\xrfwin.out -i%PS_HOME%\sqr\; -zif%PS_HOME%\sqr\pssqr.ini -fc:\sqr_out\ T846U10 T846U10 952 VP1 testEnglish

Sybase on Microsoft Windows

%PS_HOME%\bin\sqr\syb\binw\sqrw %PS_HOME%\sqr\sysaudit.sqr sa/sybase -vPTSUN15_ANSI_12503 -dbT846A60 -tb -xp -oc:\sqr_out\sysaudit.log -i%PS_HOME%\sqr\ -ZIF%PS_HOME%\sqr\pssqr.ini -fc:\sqr_out\sysaudit.pdf -PRINTER:PD T846A60 254 VP1 PSTEST

Sybase on Unix

$PS_HOME/bin/sqr/SYB/bin/sqr $PS_HOME/sqr/xrfwin t847a60/t847a60 -dbT847A60 -o/tmp/x1.out -xb -xi -i$PS_HOME/sqr -zif$PS_HOME/sqr/pssqr.unx -f/tmp/x1.htm -PRINTER:HT T847A60 1 VP1 TEST

Informix

%PS_HOME%\BIN\SQR\INF\BINW\sqrw %PS_HOME%\sqr\xrfwin.sqr H890R33B/h890r33b⇒ /h890r33b -PB -oc:\sqr_out\xrfwinx.out -i%PS_HOME%\sqr_ifx\ -ZIF%PS_HOME%\sqr_ifx\pssqr.ini "-fc:\sqr_out\xrfwinx.pdf" -PRINTER:PD

Oracle on Unix

$PS_HOME/bin/sqr/ORA/bin/sqr $PS_HOME/sqr/xrfwin.sqr T846U22/T846U22@T846U22 -o$PS_HOME/xrfwin_689.out -i$PS_HOME/sqr/ -ZIF$PS_HOME/sqr/pssqr.unx "-f$PS_HOME/xrfwin_689.pdf" -printer:pd T846U22 689 VP1 PJS

Microsoft SQL Server on Microsoft Windows

%PS_HOME%\bin\sqr\MSS\BINW\sqrw %PS_HOME%\sqr\xrfwin.sqr T846U10/testdb2/t3stdb20 -oc:\sqr_out\xrfwin.out -i%PS_HOME%\sqr\; -zif%PS_HOME%\sqr\pssqr.ini -fc:\sqr_out\ T846U10 T846U10 952 VP1 testEnglish

Viewing SQR Output

SQR normally places the SQR program output files in the directory from which you run the program. The output file has the same file name as the SQR file that created it, but the file extension is different.

The output files should appear as soon as your program has finished running. If you specified the -KEEP argument, one output file is in SQR Portable Format (recognizable by its .spf extension). SQR Portable Format is discussed later in this guide, but for now, you can view the sample program’s .spf file output, <filename>.spf, on Microsoft Windows platforms with the SQR Viewer GUI (sometimes referred to as an SPF Viewer). Invoke the SQR Viewer by entering sqrw at the command line.

On Microsoft Windows and UNIX/Linux systems, the program also produces an output file with an .lis extension. You can view this output file type from the command line with such commands as TYPE on Microsoft Windows systems or CAT, MORE, and VI on UNIX/Linux systems. Use the command that is appropriate to your system to view or print the .lis file.

The output for the example program looks like this for all platforms:

Hello, World.

You may also see a character such as ^L or <FF> at the end of this output file. It is the form-feed character that ejects the last page. This guide does not show the form-feed characters.