BEGIN-SQL
Syntax
BEGIN-SQL[-Cnn][-XP][-NR][-SORTnn] [-LOCK{RR|CS|RO|RL|XX}] [-DBdatabase][-DBconnectionstring] [ON-ERROR=procedure[(arg1[,argi]]...)]! In the SETUP section |[ON-ERROR={STOP|WARN|SKIP}](insetup)! Outside the SETUP section END-SQLDescription
Begins an SQL paragraph . This paragraph can reside in a BEGIN-PROCEDURE, BEGIN-SETUP, or BEGIN-PROGRAM section.
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, each statement except the last must be terminated 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 Microsoft SQL Server). Columns and variables can be referenced in the SQL statements.
Parameters
| Parameter | Description |
|---|---|
|
-Cnn |
(Oracle) Sets the context area size (buffer size for query) to larger or smaller than the default. This option is rarely needed. |
|
-DBconnectionstring |
(ODBC) Specifies the ODBC connection string for this SQL paragraph only. A connection string has the following syntax: This option enables you to combine data from multiple databases in one program. For example, a connection string for an Oracle database named ora8 might look like this: 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 can be added to the connection string with driver-defined keywords. See your ODBC driver documentation for available options. |
|
ON-ERROR |
Declares a procedure to run if an error occurs due to incorrect SQL syntax except when the statement is run in a BEGIN-SETUP section. By default, SQR 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. Optionally, you can specify arguments to be passed to the ON-ERROR procedure. Arguments can be any variable, column, or literal. If ON-ERROR is used in the SETUP section, it is a condition flag supporting the following conditions: STOP: Do not run the program. WARN: Run the program but with a warning message. SKIP: Ignore any errors and run the program. |
Example
The following example illustrates the BEGIN-SQL command:
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-sqlSee END-SQL, BEGIN-PROCEDURE, EXECUTE
See The -S command-line flag
Stored Procedures
For Oracle, stored procedures are implemented by PL/SQL in the BEGIN-SQL paragraph. For Microsoft SQL Server, SQR supports stored procedures with the EXECUTE command.
For some databases, such as Oracle, using DDL statements within a BEGIN-SQL paragraph 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 the results will be unpredictable.
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