Using the BEGIN-SQL Paragraph
A SQL statement other than a select statement must use the BEGIN-SQL paragraph.
The following sample program loads data from an external file into the database. It demonstrates two important features of SQR: handling external files and performing database inserts. This sample program loads the tab-delimited file that is created by the program ex11a.sqr:
Program ex19a.sqr
begin-setup
begin-sql on-error=skip ! table may already exist
create table customers_ext (
cust_num int not null,
name varchar (30),
addr1 varchar (30),
addr2 varchar (30),
city varchar (16),
state varchar (2),
zip varchar (10),
phone varchar (10),
tot int
)
end-sql
end-setup
begin-program
do main
end-program
begin-procedure main
encode '<009>' into $sep
open 'ex11a.lis' as 1 for-reading record=160:vary
read 1 into $rec:160 ! skip the first record, column headings
while 1
read 1 into $rec:160
if #end-file
break
end-if
unstring $rec by $sep into $cust_num $name
$addr1 $addr2 $city $state $zip $phone $tot
move $cust_num to #cust_num
move $tot to #tot
begin-sql
insert into customers_ext (cust_num, name,
addr1, addr2, city, state, zip, phone, tot)
values
(#cust_num, $name, $addr1, $addr2, $city,
$state, $zip, $phone, #tot)
end-sql
end-while
begin-sql
commit
end-sql
close 1
end-procedure! main
The sample program begins by creating the customers_ext table. If the table already exists, you receive an error message. To ignore this error message, use the ON-ERROR=SKIP option.
The program reads the records from the file and inserts each record into the database by using an insert statement inside a BEGIN-SQL paragraph. The input file format is one record per line, with each field separated by the separator character. When the end of the file is encountered (if #end-file), the program branches out of the loop. Note that #end-file is an SQR-reserved variable.
The final step is to commit the changes to the database and close the file. You do this with a SQL COMMIT statement inside a BEGIN-SQL paragraph. Alternatively, you can use the SQR COMMIT command. For Oracle databases, use the SQR COMMIT command.
The code may be database-specific.