.Setup Statement--Embed SQL Statements that Perform Set up
The .setup statement embeds SQL statements that do not involve data retrieval into Report-Writer sections. Report-Writer executes the statements before it processes the main report query.
The .setup statement has the following format:
.setup SQL_statement; {SQL_statement;}
The parameter for the .setup statement is as follows:
SQL_statement
One or more action SQL statements that do not involve data retrieval, separated by semicolons (;). (Note that the select statement cannot be used.)
For a complete explanation of all available SQL statements, see the SQL Reference Guide.
Description
Use the optional .setup statement to embed groups of SQL statements to perform tasks such as creating and qualifying temporary tables for use in the report and setting up lock modes. Report-Writer executes the .setup statement after substituting variables into the SQL statements. You can use as many lines as you need to specify the .setup. You can also include embedded declared variables within an SQL statement. The end of .setup is indicated by the start of a new Report-Writer statement. For information on embedding groups of SQL statements after the report is processed, see the .cleanup statement.
If you are running reports with an Ingres database and you are not updating the data in your report, we recommend that you set up your report to be read only. To do this, provide the following statement in the .setup section:
set lockmode session
where readlock=nolock;
Note: Keep in mind that when you set the lockmode to be read only, other users can still update the table used to produce the report. In this case, your report can not always match actual database values. If it is critical to keep the table consistent throughout the printing of the report, lock the table. For details on using lockmodes, see the Database Administrator Guide.
The following rules apply to the .setup section:
• The .setup section only supports SQL statements. The language of your .query section determines the query language of the report. If you have a QUEL .query section, the query language is QUEL; otherwise it is SQL. It is acceptable to have a QUEL query and SQL .setup and .cleanup sections.
• If the report query language is SQL, the default value of autocommit is off. If it is QUEL, the default is on. To override the default commit behavior, simply set autocommit off or on as the first statement in your .setup section.
• Only statements that are compatible with execute immediate are permitted in a .setup section. For a list of compatible statements, see the SQL Reference Guide. Neither the select statement nor any statement requiring embedded semicolons (;) or colons (:), such as create procedure, are allowed. Semicolons within quoted strings are allowed. Therefore, you can specify a table for selection by using the expression:
create table tablename as select...
• In the .setup section, Report-Writer evaluates variables only once, before running the report. Therefore, you can set the value of the variable only at report initiation time as follows:
– On the command line in a variablename=valuestring clause
– On the command line in response to a prompt
– In the with value or with prompt clause of a .declare statement associated with the variable
• In the .setup section, Report-Writer evaluates variables before sending them to the Database Management System (DBMS) and evaluates SQL statements at report runtime. It generates error messages at runtime from the DBMS. If it has detected no errors in the report or in the .cleanup and .setup sections and autocommit is off, Report-Writer executes an explicit commit at the close of report processing.
• Use the ‑d flag with the report command to run the report as if there were no errors. This flag causes Report-Writer to ignore DBMS errors in the .setup and .cleanup sections. The failure of the.setup statement, however, can affect data availability in the .query. For example, if you run the report on a temporary table created improperly in the .setup section, the report fails.
• If you specify the ‑d flag, error messages continue to display on the screen. Although it is not recommended, you can run the report with the ‑s flag if you do not want error messages to display.
• If you do not use the ‑d flag, errors and transaction handling follow these rules:
– If there is an error in the .setup section, neither the report nor the .cleanup section runs. The transaction is rolled back if autocommit is off.
– If there is an error in the .cleanup section, the .setup section (if it existed) and the report have already run. The transaction is rolled back if autocommit is off.
– If there is an error in the query or a fatal error in the report, neither the .cleanup section nor the report runs. The transaction is rolled back if autocommit is off.
Example
.NAME books2
.OUTPUT books2.out
.LONGREMARK
The BOOKS2 report demonstrates using setup
and cleanup to produce temporary tables.
.ENDREMARK
.SETUP
set lockmode session where readlock=nolock;
create view tempbooks as
select b.id, b.title, a.name, '' as subject,
1 as code
from book b, author a
where b.id = a.id
union
select b.id, b.title, '' as name, s.subject,
2 as code
from book b, subject s
where b.id = s.id;
create table subj_count as
select id, count(subject) as num_sub
from tempbooks
where code = 2
group by id;
create table auth_count as
select id, count(name) as num_auths
from tempbooks
where code = 1
group by id;
.CLEANUP
drop tempbooks;
drop subj_count;
drop auth_count;
.QUERY
select b.id, b.title, b.name, b.subject, b.code,
a.num_auths, s.num_sub
from tempbooks b,subj_count s,auth_count a
where b.id = a.id and b.id = s.id