Selecting Data from the Database

This chapter provides an overview of the sample program for listing and printing data and describes how to create SQR select paragraphs.

Understanding the Sample Program for Listing and Printing Data

Here is a sample program that selects data from the database and prints it in columns:

Program ex3a.sqr
begin-program
   do list_customers
end-program
begin-heading 4
   print 'Customer Listing' (1) center
   print 'Name' (3,1)
   print 'City' (,32)
   print 'State' (,49)
   print 'Phone' (,55)
end-heading
begin-footing 1
   ! Print "Page n of m" in the footing
   page-number (1,1) 'Page '
   last-page   () ' of '
end-footing
begin-procedure list_customers
begin-select
name (,1)
city (,32)
state (,49)
phone (,55)
   position (+1)  ! Advance to the next line
from customers
end-select
end-procedure ! list_customers

The output for the ex3a.sqr program is:

                       Customer Listing

Name                           City            State  Phone
Gregory Stonehaven             Everretsville    OH    2165553109
John Conway                    New York         NY    2125552311
Eliot Richards                 Queens           NY    2125554285
Isaiah J Schwartz and Company  Zanesville       OH    5185559813
Harold Alexander Fink          Davenport        IN    3015553645
Harriet Bailey                 Mamaroneck       NY    9145550144
Clair Butterfield              Teaneck          NJ    2015559901
Quentin Fields                 Cleveland        OH    2165553341
Jerry's Junkyard Specialties   Frogline         NH    6125552877
Kate's Out of Date Dress Shop  New York         NY    2125559000
Sam Johnson                    Bell Harbor      MI    3135556732
Joe Smith and Company          Big Falls        NM    8085552124
Corks and Bottles, Inc.        New York         NY    2125550021
Harry's Landmark Diner         Miningville      IN    3175550948
                
Page 1 of 1

The PROGRAM section contains a single DO command, which invokes the list_customers procedure.

In SQR language, a procedure is a group of commands that are performed one after the other, like a procedure (or subroutine) in other programming languages. A DO command invokes a procedure.

Break your program logic into procedures and keep the PROGRAM section small. It should normally contain a few DO commands for the main components of your report.

The HEADING section creates headings for the report columns. In this example, four lines are reserved for the heading:

begin-heading 4
   print 'Customer Listing' (1) center
   print 'Name' (3,1)
   print 'City' (,32)
   print 'State' (,49)
   print 'Phone' (,55)
end-heading

The Customer Listing title is printed on line 1. Line 2 is left blank. The first column heading, Name, is positioned at line 3 of the heading, in character position 1. The rest of the column heading commands omit the line numbers in their positions and are set by default to the current line. Line 4 of the heading is left blank.

In this sample program, the footing is the same as the one in the previous sample program.

Creating SQR Select Paragraphs

The BEGIN-SELECT command is the principal method of retrieving data from the database and printing it in a report. Look again at the sample program for listing and printing data, in which the list_customers procedure starts with BEGIN-PROCEDURE and ends with END-PROCEDURE.

Note the comment following the END-PROCEDURE command. It indicates that the procedure is being ended, which is helpful when you have a program with many procedures. (You can also omit the exclamation point, for example, END-PROCEDURE main.)

The procedure itself contains a select paragraph, which starts with BEGIN-SELECT and ends with END-SELECT.

The select paragraph is unique. It combines an SQL SELECT statement with SQR processing in a seamless way. The actual SQL statement is:

SELECT NAME, CITY, STATE, PHONE
FROM CUSTOMERS

Syntax of the Select Paragraph

In an SQR select paragraph, the SQL statement SELECT is omitted, and no commas are between the column names. Instead, each column is on its own line. You can also place SQR commands between the column names, and these commands are run for every record that the select fetches.

Note. You must name each individual column in a table—the SQL SELECT * FROM statement is not allowed in SQR.

SQR distinguishes column names from SQR commands in a select paragraph by their indentation. Column names must be placed at the beginning of a line. SQR commands must be indented at least one space. In the following example, the POSITION command is indented to prevent it from being taken as a column name. The word From must be the first word in a line. The rest of the SQR select paragraph is then written freely, after SQL syntax.

Think of the select paragraph as a loop. The SQR commands, including printing of columns, are run in a loop, once for each record that Select returns. The loop ends after the last record is returned.

Positioning Data

In a select paragraph, you see positioning after each column name. This positioning implies a PRINT command for that column. Omitting the line number in the position causes it to be set by default to the current line.

begin-select
name (,1)
city (,32)
state (,49)
phone (,55)
   position (+1)  ! Advance to the next line
from customers
end-select

The implied PRINT command is a special SQR feature that is designed to save you coding time. It works only inside a select paragraph.

After the last column is a POSITION command: POSITION(+1). The plus sign (or minus sign) indicates relative positioning in SQR. A plus sign moves the print position forward from the current position, and a minus sign moves it back. The +1 in the sample program specifies one line down from the current line. This command advances the current print position to the next line.

Note. When you indicate print positions by using plus or minus signs, be sure that your numbers do not specify a position outside of the page boundaries.