Vladimir Zabrodsky: SQL statements



	SQL STATEMENTS

We can expect SQL/DS do generate code comparable with average hand-written code in many simple cases, and superior in many complex cases.
E. F. CODD: Relational Datbase: A Practical Foundation for Productivity. CACM No. 2 1982, p. 109

IBM DB2 is the data base management system which implements the relational model of data. Data are represented in the form of tables (physical files). Each row (record) of a table consists of a set of columns (fields). All rows of a given table are of the same form.
     Operations on data are performed using Structured Query Language - SQL. SQL is a standardized language for defining and manipulating data in a relational database. Statements in this language may be specified within Rexx application programs. With SQL, you do not have to code a sequence of statements explaining how to get to the data. With a single SQL statement, you can access and manipulate the data in a database - i.e. you can retrieve, insert, update, and delete data and control access to data. This possibility overcomes all limitation of Standard Input & Output.
We can easy create an English-Czech-English dictionary:

/* COMPILE compiles English-Czech-English dictionary */ address EXECSQL EXECSQL 'SET OPTION COMMIT = *NONE' EXECSQL 'CREATE TABLE MYLIB/DICTIONARY',   ' (E_WORD CHAR(20) NOT NULL,',   ' C_WORD CHAR(20) NOT NULL)' INS = 'INSERT INTO MYLIB/DICTIONARY VALUES(?, ?)' EXECSQL 'PREPARE I1 FROM :INS' say "Insert couples of words: English-Czech" do forever   say "English-Czech"   parse linein E "-" C   if E = "" then leave   EXECSQL 'EXECUTE I1 USING :E, :C'   if RC = 0 then say "OK"; else say "Error, RC =" RC end

The SQL environment, called EXECSQL, lets you use SQL. In the program the instruction address EXECSQL changed default external environment to SQL.
In Rexx programs is the default set option COMMIT = *CHG. When the table is not journaled file, you have to use the explicit statement 'SET OPTION COMMIT = *NONE'. The statement 'CREATE TABLE MYLIB/DICTIONARY (E_WORD CHAR(20) NOT NULL, C_WORD CHAR(20) NOT NULL)' creates the database file with two columns: first for English words and second for their Czech equivalent.
The PREPARE statement 'PREPARE I1 FROM :INS' prepares the statement 'INSERT INTO MYLIB/DICTIONARY VALUES(?, ?)' in the INS variables. All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable or operational form of the statement. To issue a dynamic SQL statement, you must use the EXECUTE statement
'EXECUTE I1 USING :E, :C'
In SQL statements the names with colon prefixes are host (program) variable names. The values of E and C variables are stored as fields of one row into the DICTIONARY table.
The Rexx variable RC indicates either a successful call to the SQL interface or a failure to call the SQL interface. If the call completes successfully, RC is set to zero.
An example of a dialog with the COMPILE and man follows:

Start of terminal session. Insert couples of words: English-Czech abandon-opustit OK abandon-vzdát se OK abandon-oddávat se OK abbey-opatství OK abbreviation-zkratka OK abdicate-vzdát se OK Press ENTER to end terminal session.

The program for using our bidirectional dictionary is shown below.

/* CONSULT consults the English-Czech-English dictionary */ address EXECSQL EXECSQL 'SET OPTION COMMIT = *NONE' do until Answer = "E" | Answer = "C"   say "English->Czech or Czech->English? (E/C)"   parse upper linein Answer end Prompt.E = "Word to translate"; Prompt.C = "Napiš slovo" if Answer = "E" then   SEL = 'SELECT C_WORD FROM MYLIB/DICTIONARY WHERE E_WORD = ?'   else   SEL = 'SELECT E_WORD FROM MYLIB/DICTIONARY WHERE C_WORD = ?' EXECSQL 'PREPARE S1 FROM :SEL' EXECSQL 'DECLARE C1 CURSOR FOR S1' do forever   say Prompt.Answer; parse linein Oneword   if Oneword = "" then leave   EXECSQL 'OPEN C1 USING :ONEWORD'   do forever     EXECSQL 'FETCH C1 INTO :TRANSLATED'     if SQLCODE <> 0 then leave     say COPIES(" ", 10) Translated   end   say COPIES(" ", 10) ".."; EXECSQL 'CLOSE C1' end

In the CONSULT program we use the SQL cursor. If you want to select multiple rows of a table, a cursor must be defined. The main programming steps in using cursor are: declare the cursor, open the cursor, fetch rows from the cursor, optionally update or delete the most recently fetched row, close the cursor. When the cursor is opened, it is positioned before the first row in the result table. That row is then the current row. If host variables are specified (with the INTO clause on the FETCH statement), SQL moves the contents of the current row into your program's host variables. This sequence is repeated each time a FETCH statement is issued until the end-of-data (SQLCODE = 100) is reached. When you reach the end-of-data, close the cursor. To use the cursor again, you must first close the cursor and then re-issue the OPEN statement.
The example of CONSULT - man dialogue follows.

Start of terminal session. English->Czech or Czech->English (E/C)? E Word to translate: abandon            opustit            vzdát se            oddávat se            .. Word to translate: robot            .. Word to translate: abbreviation            zkratka            .. Word to translate: Press ENTER to end terminal session.

Did you try it? And it doesn't work?

Conclusion
A peculiarity of the Rexx language in the AS/400 environment is Input and Output. But it isn't the restriction. You can build applications that access standard input and output files, use the external data queue, the AS/400 faeries as Query/400 and also DB2/400's robust relational database capabilities.

tour from Rexx to AS/400
  [How write and run Rexx program]

  [Standard Input and Output]

  [External Data Queue]

  [SQL statements]