Ingres Database Reference
Document #: US-13233,EN
------------------------------------------------------------------------------
Major subject: analysis Minor subjects: tech_notes
Keywords: call_report, code_example
Abstract:
Running Reports From 4GL: 4GL/RW: How to pass complex parameter
to Report Writer through 4GL. Examples to pass entire WHERE
clause for both UNIX and VMS in SQL and QUEL. Equivalent
to Release 6 Technical Note #10 or Release 5 note #58.
Expert note:
Running Reports From 4GL
========================
Overview
--------
This document discusses how to run a report from an ABF application and pass a
parameter to the report at runtime. One way to accomplish this is to define a
report frame, where the use of a form to pass parameters to the report is
optional. If no parameter form exists, the INGRES Report Writer will prompt
the user for the needed values at runtime. The application described in this
document, however, uses a user-specified frame and the CALL REPORT command.
Included in this document is an example of passing a complex parameter to a
report, where the entire WHERE clause is passed. Refer to the INGRES ABF/4GL
Manual for examples of how to pass less complex parameters.
o Debugging Tools
o The Form
o SQL Version
o QUEL Version
o Summary
Debugging Tools
---------------
There are three useful commands, that can be defined at the operating system
level before entering the application, to allow you to see the commands that
are executed in the application. They are, the VMS command "set verify", and
the INGRES trace flag "printqry" and II_UTEXE_DEBUG. The "printqry" and
II_UTEXE_DEBUG tools are available for both UNIX and VMS. The "set verify" is
valid only on VMS. If you believe that an incorrect query is being passed to
the dbms because you are getting incorrect number of rows or no rows at all,
you should use the "printqry" trace flag to find out what query is being passed
to the DBMS.
The output of "printqry" can either be sent to the screen or to a file. In
order to send it to the screen, use:
VMS: define ING_SET "set printqry"
UNIX: C Shell: setenv ING_SET "set printqry"
Bourne Shell: ING_SET="set printqry"; export ING_SET
To place the output of "printqry" to a file, use:
VMS: define II_EMBED_SET "printqry"
UNIX: C Shell: setenv II_EMBED_SET "printqry"
Bourne Shell: II_EMBED_SET="printqry"; export II_EMBED_SET
This will create a file named "iiprtqry.log" in the current working directory.
When there is a suspicion that incorrect parameters are being passed or that
none of your parameters are being passed because you are getting the incorrect
data or no data, use II_UTEXE_DEBUG to find the exact report command being
passed to the Operating System.
VMS: define II_UTEXE_DEBUG "true"
UNIX: C Shell: setenv II_UTEXE_DEBUG "true"
Bourne Shell: II_UTEXE_DEBUG="true"; export II_UTEXE_DEBUG
You will need to stop the screen either by pressing the "Hold Screen" key or
using "^S" to stop the screen and "^Q" to restart the screen.
Please note that UNIX is case sensitive so be sure to define the environment
variables as shown above.
The Form
--------
A blank form, on which 2 fields are created, is used. This form allows you to
enter selection criteria for running a preformatted report. The names of the
fields on the form correspond to the names of the fields that are being
selected in the report, where name is defined as a character field and hours
is defined as an integer field. Following is the form with the menuitems
added:
--------------------------------------------------------------
| |
| Enter Name: ____________________ |
| |
| Enter Hours: ________ |
| |
| |
| Run SQL Report End |
--------------------------------------------------------------
SQL Version
-----------
The Report
- - - - - -
/*
** Report: sqlrep - This report prompts for a qualification string at runtime
*/
.NAME sqlrep
.QUERY SELECT name, hours
FROM tasks
WHERE $qual
.HEADER REPORT
.NL 10
.CE .PR "Report on Name and Hours using Parameters Passed from ABF"
.DETAIL
.PR name, hours
The 4GL Code
- - - - - - -
This 4GL example shows a call to a report from ABF, passing the parameters for
report name, output file name, and a paramstring. The paramstring for the
WHERE clause is built by concatenating literal text and values entered onto
the application form. Note that it is illegal to pass null values in a call to
a subsystem, so if param is nullable, a warning message will be issued.
********************************************************************************
/* VMS SQL Example of passing an entire where clause to a report from 4GL */
initialize (paramstring = varchar(100) not null with default) =
{
}
'Run SQL Report' =
{
paramstring = 'qual = "name = ''' + :name
+ ''' and hours = ' + varchar(:hours) + '"';
/* The paramstring value built in the above assignment looks like */
/* qual = "name = 'John Smith' and hours = 40" */
call report (report='sqlrep', file='sqlrep.out', param=:paramstring);
}
'End', key frskey3 =
{
exit;
}
********************************************************************************
/* UNIX SQL Example of passing an entire where clause to a report */
initialize (paramstring = varchar(100) not null with default) =
{
}
'Run SQL Report' =
{
paramstring = 'qual = "name = ''"''' + :name
+ '''"'' and hours = ' + varchar(:hours) + '"';
/* The paramstring value built in the above assignment looks like */
/* qual = "name = '"'John Smith'"' and hours = 40" */
call report (report='sqlrep', file='sqlrep.out', param=:paramstring);
}
'End' =
{
exit;
}
********************************************************************************
The resulting query submitted by the Report Writer for the report is:
select name, hours from tasks where name = 'John Smith' and hours = 40
To understand the pieces of the paramstring, section off pieces of the
paramstring where literal text begins and ends and where the parameter values
fit in. For example:
VMS: | qual = "name = ' | John Smith | ' and hours = | 40 | " |
UNIX: | qual = "name = '"' | John Smith | '"' and hours = | 40 | " |
1 2 3 4 5
Sections 1, 3, and 5 represent literal text and sections 2 and 4 represent
parameter values that were entered on the form.
The following flowcharts will explain the function of each single or double
quote in the paramstring:
********************************************************************************
/* VMS SQL Flowchart Example */
paramstring =
(Begin -----> 'qual =
|
piece of | "name = (Begin value for report parameter 'qual') <----------
literal | |
text in | ' (tell 4GL the next quote is a literal character) |
4GL | |
| ' (a literal quote that will be passed through VMS to |
| the RW; Begin value of name in RW query) <------- |
| | |
End) -----> ' | |
| |
+ :name + | |
| |
(Begin -----> ' | |
| | |
piece of | ' (tell 4GL the next quote is a literal character) | |
literal | | |
text in | ' (a literal quote that will be passed through | |
4GL | VMS to the RW; End value of name in RW query) <-- |
| |
| and hours = |
| |
End) -----> ' |
|
+ varchar(:hours) + |
|
(Begin -----> ' |
| |
piece of | " (End value for report parameter 'qual') <-------------
literal |
text in |
4GL |
|
End) -----> '
********************************************************************************
/* UNIX SQL Flowchart Example */
paramstring =
(Begin -----> 'qual =
|
piece of | "name = (Begin value for report parameter 'qual') <----------
literal | |
text in | ' (tell 4GL the next quote is a literal character) |
4GL | |
| ' (a literal quote that tells UNIX that the next |
| quote it sees will be literal) |
| |
| " (a literal quote that tells UNIX that the next |
| quote it sees will be literal) |
| |
| ' (a literal quote that will close off the quote |
| around the above double quote) |
| |
| ' (a literal quote that will be passed through UNIX |
| to the RW; Begin value of name in RW query) <---- |
| | |
End) -----> ' | |
| |
+ :name + | |
| |
(Begin -----> ' | |
| | |
piece of | ' (tell 4GL the next quote is a literal character) | |
literal | | |
text in | ' (a literal quote that tells UNIX that the next | |
4GL | quote it sees will be literal) | |
| | |
| " (a literal quote that tells UNIX that the next | |
| quote it sees will be literal) | |
| | |
| ' (a literal quote that will close off the quote | |
| around the above double quote) | |
| | |
| ' (a literal quote that will be passed through UNIX| |
| to the RW; End value of name in RW query) <------ |
| |
| and hours = |
| |
End) -----> ' |
|
+ varchar(:hours) + |
|
(Begin -----> ' |
| |
piece of | " (End value for report parameter 'qual') <-------------
literal |
text in |
4GL |
|
End) -----> '
********************************************************************************
QUEL Version
------------
The Report
- - - - - -
/*
** Report: quelrep - This report prompts for a qualification string at runtime
*/
.NAME quelrep
.QUERY RANGE OF t IS tasks
RETRIEVE (t.name, t.hours)
WHERE $qual
.HEADER REPORT
.NL 10
.CE .PR "Report on Name and Hours using Parameters Passed from ABF"
.DETAIL
.PR name, hours
The 4GL Code
- - - - - - -
This 4GL example shows a call to a report from ABF, passing the parameters for
report name, output file name, and a paramstring. The paramstring for the
WHERE clause is built by concatenating literal text and values entered onto the
application form. Note that it is illegal to pass null values in a call to a
subsystem, so if param is nullable, a warning message will be issued.
********************************************************************************
/* VMS QUEL Example of passing an entire where clause to a report */
initialize (paramstring = varchar(100) not null with default) =
{
}
"Run QUEL Report" =
{
paramstring = "qual = \"t.name = \"\"" + :name
+ "\"\" and t.hours = " + c(:hours) + "\"";
/* The paramstring value built in the above assignment looks like */
/* qual = "t.name = ""John Smith"" and t.hours = 40" */
call report (report="quelrep", file="quelrep.out", param=:paramstring);
}
"End" =
{
exit;
}
********************************************************************************
/* UNIX QUEL Example of passing an entire where clause to a report */
initialize(paramstring = varchar(100) not null with default) =
{
}
"Run QUEL Report" =
{
paramstring = "qual = \"t.name = \\\"" + :name
+ "\\\" and t.hours = " + c(:hours) + "\"";
/* The paramstring value built in the above assignment looks like */
/* qual = "name = \"John Smith\" and hours = 40" */
call report (report = "quelrep", file = "quelrep.out",
param=:paramstring);
}
"End", key frskey3 =
{
exit;
}
********************************************************************************
The resulting query submitted by the Report Writer for the report is:
retrieve (t.name, t.hours) where t.name = "John Smith" and t.hours = 40
To understand the pieces of the paramstring, section off pieces of the
paramstring where literal text begins and ends and where the parameter values
fit in. For example:
| qual = "name = "" | John Smith | "" and hours = | 40 | " |
| qual = "name = \" | John Smith | \" and hours = | 40 | " |
1 2 3 4 5
Sections 1, 3, and 5 represent literal text and sections 2 and 4 represent
parameter values that were entered on the form.
The following flowcharts will explain the function of each single or double
quote in the paramstring:
********************************************************************************
/* VMS QUEL Flowchart Example */
paramstring =
(Begin -----> "qual =
|
| \ (tell 4GL the next quote is a literal character)
|
piece of | "t.name = (Begin value for report parameter 'qual') <----------
literal | |
text in | \ (tell 4GL the next quote is a literal character |
4GL | |
| " (a literal quote that tells VMS that the next quote |
| it sees will be literal) |
| |
| \ (tell 4GL the next quote is a literal character |
| |
| " (a literal quote that will be passed through VMS to |
| the RW; Begin value of name in RW query) <------- |
| | |
End) -----> " | |
| |
+ :name + | |
| |
(Begin -----> " | |
| | |
piece of | \ (tell 4GL the next quote is a literal character) | |
literal | | |
text in | " (a literal quote that tells VMS that the next | |
4GL | quote it sees will be literal) | |
| | |
| \ (tell 4GL the next quote is a literal character) | |
| | |
| " (a literal quote that will be passed through | |
| VMS to the RW; End value of name in RW query) <-- |
| |
| and t.hours = |
| |
End) -----> " |
|
+ c(:hours) + |
|
(Begin -----> " |
| |
piece of | \ (tell 4GL the next quote is a literal character) |
literal | |
text in | " (End value for report parameter 'qual') <------------
4GL |
|
End) -----> "
********************************************************************************
/* UNIX QUEL Flowchart Example */
paramstring =
(Begin -----> "qual =
|
| \ (tell 4GL the next quote is a literal character)
|
piece of | "t.name = (Begin value for report parameter 'qual') <----------
literal | |
text in | \ (tell 4GL the next backslash is a literal character |
4GL | |
| \ (a literal backslash that tells UNIX that the next |
| backslash it sees will be literal) |
| |
| \ (a backslash that tells UNIX that the next quote it |
| sees will be a literal) |
| |
| " (a literal quote that will be passed through UNIX |
| to the RW; Begin value of name in RW query) <---- |
| | |
End) -----> " | |
| |
+ :name + | |
| |
(Begin -----> " | |
| | |
piece of | \ (tell 4GL the next backslash is a literal | |
literal | character) | |
text in | | |
4GL | \ (a literal backslash that tells UNIX that the | |
| next backslash it sees will be literal) | |
| | |
| \ (a literal backslash that tells UNIX that the | |
| next quote it sees will be literal) | |
| | |
| " (a literal quote that will be passed through | |
| UNIX to the RW; End value of name in RW query) <- |
| |
| and t.hours = |
| |
End) -----> " |
|
+ c(:hours) + |
|
(Begin -----> " |
| |
piece of | \ (tell 4GL the next quote is a literal character) |
literal | |
text in | " (End value for report parameter 'qual') <------------
4gl |
|
End) -----> "
********************************************************************************
Summary
-------
This document provided both an SQL and QUEL example of how to run a report from
ABF and pass parameters to the report at runtime using CALL REPORT. The
necessary syntax for creating the paramstring is illustrated and explained
through the use of flowcharts.
Releases affected: all(all.all) - Releases not affected:
Errors:
Bugs/SIRS:
------------------------------------------------------------------------------
Ingres Database Reference
To William's Home Page
© William Yuan 2000
Email William