BEGIN-SQL

Function

Begins an SQL paragraph, which can reside in BEGIN-PROCEDURE, BEGIN‑SETUP, or BEGIN-PROGRAM.

Syntax

BEGIN-SQL[-Cnn][-XP][-NR][-SORTnn]
[-LOCK{RR|CS|RO|RL|XX}]
[‑DBdatabase][-DBconnectionstring]
[ON‑ERROR=procedure[(arg1[,argi]...)](non‑setup)
|[ON‑ERROR={STOP|WARN|SKIP}](SETUP)
END-SQL

Arguments

-Cnn

(Oracle) Sets the context area size (buffer size for query) to larger or smaller than the default.

-XP

(Sybase) Prevents the creation of stored procedures for SQL paragraphs. When specified, Production Reporting generates a new SQL statement using the current value of the bind variables each time BEGIN-SQL is executed. This disables the performance optimization created by stored procedures.

Use -XP if you change variables frequently during execution and do not want Production Reporting to automatically create stored procedures. You can also use -XP if users do not have permission to create stored procedures. If you do not change variables frequently during execution, stored procedures may optimize program performance. In this case, do not use -XP.

-XP improves performance when using bind variables and dynamic query variables in the same query. Each time the dynamic query variable changes in value, a new stored procedure is created. If the dynamic query variable changes often and the query contains bind variables, you create many stored procedures if you do not use -XP.

-DBconnectionstring

(ODBC) The ODBC connection string for this SQL paragraph only. A connection string has the following syntax:

DSN=data_source_name[;keyword=value[;keyword=value[;...]]]

Combines data from multiple databases in one program. For example, a connection string for an Oracle named “ora8” might appear as:

'DSN=ora8;UID=scott;PWD=tiger'

where DSN, UID, and PWD are keywords common to all drivers (representing name, user ID, and password, respectively). Connection string options are always separated by a semicolon (;). Other driver-specific options may be added to the connection string using driver-defined keywords. See your ODBC driver documentation for available options.

Connection=connstr

Used with Production Reporting DDO. The name of a data source previously declared using DECLARE-CONNECTION. If not specified, the default connection is used. (See BEGIN-EXECUTE for the behavior of the default connection.)

ON-ERROR

Procedure to execute if an error occurs due to incorrect SQL syntax except when executed in a BEGIN-SETUP section. By default, Production Reporting reports any error and then halts. If an error procedure is declared, you can trap errors, report or log them, and continue processing. The procedure is invoked when an error occurs in any SQL statement in the paragraph. After the error procedure ends, control returns to the next SQL statement, if any.

You can optionally specify arguments to pass to ON-ERROR. Arguments can be any variable, column, or literal.

If ON-ERROR is used in SETUP, it is a condition flag supporting the following conditions:

Description

BEGIN-SQL starts all SQL statements except SELECT, which has its own BEGIN-SELECT paragraph. If a single paragraph contains more than one SQL statement, terminate each statement (except the last) by a semicolon (;).

If a single paragraph contains more than one SQL statement, and the -C flag is used, all are assigned the same context area size or logical connection number.

Only non-SELECT statements can be used (except SELECT INTO for Sybase and Microsoft SQL Server backends). Reference columns and variables in SQL statements.

Examples

begin-sql
  update orders set invoice_num = #next_invoice_num
  where order_num = &order_num
end-sql

begin sql
  delete orders
  where order_num = &order_num;
  insert into orders values ($customer_name, #order_num,...)
end-sql

Stored Procedures

For Sybase, and Microsoft SQL Server, Production Reporting supports stored procedures with EXECUTE. For Oracle, stored procedures are implemented using PL/SQL in the BEGIN‑SQL paragraph.

For some databases such as ORACLE, using DDL statements in BEGIN‑SQL causes a commit of outstanding inserts, updates, and deletes and releases cursors. For this reason, ensure that these are done in the proper order or unpredictable results may occur.

Oracle PL/SQL

For Oracle, PL/SQL is supported in a BEGIN-SQL paragraph. This requires an additional semicolon at the end of each PL/SQL statement.

For Oracle PL/SQL:

begin-sql
  declare
    varpl varchar2 (25);;
    var2 number (8,2);;
  begin
    varpl :='abcdefg';;
    $v1 :=varpl;;
    $v2 :='1230894asd';; 
    var2 :=1234.56;; 
    #v :=var2;;
  end;;
end-sql

For Oracle stored procedures:

begin-sql
  begin
  #dept_number :=get_dept_no($dept_name);;
  end;;
end-sql

See Also