Using Additional SQL Statements with SQR

This chapter discusses how to:

• Use SQL statements in SQR.

• Use BEGIN-SQL.

Using SQL Statements in SQR

Although SELECT may be the most common SQL statement, you can also perform other SQL commands in SQR. Here are a few examples:

• If the program prints important documents such as checks, tickets, or invoices, you may need to update the database to indicate that the document was printed.

  You can do this in SQR with a SQL UPDATE statement.

• You can use SQR to load data into the database.

  SQR can read and write external files and construct records. SQR can also insert these records into the database by using a SQL INSERT statement.

• To hold intermediate results in a temporary database table, you can create two SQL paragraphs in the SQR program (CREATE TABLE and DROP TABLE) to create this table at the beginning of the program and drop the table at the end.

These are only a few examples. SQR can perform any SQL statement, and this feature is used often.

Using BEGIN-SQL

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-setupbegin-program
  do main
end-programbegin-procedure main#if {sqr-database} = 'Sybase'
  begin-sql
    begin transaction
  end-sql
#endif
  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
#if {sqr-database} = 'Sybase'
  begin-sql
    commit transaction
  end-sql
#else
#if {sqr-database} <> 'Informix'
  begin-sql
    commit
  end-sql
#endif
#endif
  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. If you are using Informix, for example, and your database was created with transaction logging, you must add a BEGIN WORK and a COMMIT WORK, much like the Sybase example of BEGIN TRANSACTION and COMMIT TRANSACTION.

See Using Dynamic SQL and Error Checking.

See Enterprise PeopleTools 8.50 PeopleBook: SQR Language Reference for PeopleSoft