APL2 and SQL: A Tutorial
`
`Nancy Wheeler
`IBM
`
`APL Development
`Santa Teresa Laboratory
`San Jose, California
`
`August, 1 989
`
`Part 1: SQL
`
`Before beginning the discussion of 501... it is necessary to define some terms.
`
`3 $01, (Structured Query Language) is a language defined to access relational databases. The SQL lan—
`guagc has been implemented by various vendors in support of their relational databases, and there is an
`ANSI] standard for the SQL language.
`
`0 SOLIDS (SOL! Data System) is IBM’s relational database system in the VM environment.
`
`0 DBZ (IBM Database 2) is IBM’s relational database system in the MVS environment.
`
`'l'hroughout this tutorial. the term SQL will be used to refer to the $01. language. Except where explicitly
`stated, the uses and examples of 501.. seen here are supported both by SOLIDS and [)132.
`
`Permission to cop}r without lee all or part of this material is
`granted provided that the capital are not made or distributed
`[or direct commercial advantage, the ACM copyright notice
`and the title of the publication and its date appear, and notice
`in given that copying is by permission of the Association for
`Computing Machinery. To copy otherwise, or to republish,
`requires a tee audio:- apecific permission.
`© 1989 ACM
`
`53
`
`EMNOS USA CORP.
`
`EXHIBIT 1017
`
`

`

`Relational lJata
`
`Simply stated, relational data is data that is arranged in a two-dimensional tabular format. The columns of
`the matrix have attributes that define the type of the data, and the roWs contain the data. The intersection of
`a row and column is called a field or value. Figure 1 shows an example of a relational table.
`
` 1’03 LEVEL SALARY DEPT
`
`PROJ
`
`ADAMS
`BANKS
`CROW
`DEAN
`
`EATON
`FARR
`
`12
`15
`6
`12
`
`18
`25
`
`3
`3
`2
`3
`
`H
`5
`
`35000 ADMIN PAl
`35000 SALES P52
`2UO00 PROD
`PPl
`38000 PROD
`PP2
`
`P31
`”0000 RES
`50000 ADMIN PAl
`
`CALVIN
`5
`3
`27000 SALES PS1
`HARVEY
`23
`5
`”5000 SALES PS1
`INGRAM
`2
`1
`18000 ADMIN PA2
`JACKSON
`1
`1
`16000 PROD
`KAHAN
`6
`3
`PR2
`32000 RES
`LAMAR
`21
`5
`45000 PROD PPi
`MULVEY
`3
`2
`21000 SALES PS2
`
`
`Figure l. Relational Table
`
`One of the main advantages of using relational databases is that SQ], users need not know how relational
`data is stored in the computer in order to use it. Using SQL statements, the user tells the database system
`what is to be done in a conceptual manner, and the database system then accesses the data necessary for the
`specified task. Thus, even though the underlying operating systems may he completely different in different
`relational databases (as with SQLIDS and DB2), the user can state the problem in a Well-defined, familiar
`manner.
`
`SOL Statements
`
`For the purposes of this tutorial, we will group the major SQL statements into categories. Complete syntax
`for all of these, and for additional SQL statements, can be found in the database reference manuals.
`
`Data Detinitlen
`
`Data definition statements are those that allow you to define your database configuration. The CREATE
`statement has several forms for the creation of tables, indiccs, views (logical tables), and synonyms (alias
`names). The ALTER statement allows, with some restrictions, the changing of table definitions, and the
`DROP statement is for deletion of entire tables, indices, views and synonyms. Figure 2
`shows the
`CREATE TABLE statement used to create the table shown in Figure I.
`
`54
`
`

`

`
`
`CREATE TABLE ABC
`
`
`(NAME VARCHAR(20) NOT NULL,
`
`r03 SMALLINT,
`
`LEVEL SMALLINT,
`
`SALARY INTEGER,
`
`DEPT VARCHAR(B).
`
`PROJ CHAR(3))
`
`IN MYSPACE
`
`
`Figure 2. CREA'I'E TABLE statement
`
`Each column must be given a name and datatype attribute. Table l is a summary of the datatypes' currently
`supported by SOLIDS and D82. The NOT NULL attribute is optional, and indicates to 30].. that an error
`should be given if an attempt is made to add a row to the table with data missing for the indicated column.
`
`
`SQL Dalatype
`Description
`
`IN'J'EGER
`
`Fullword (31-bit) signed integer
`
`
`SMALLINT
`Ilalfword (15-bit) signed integer
`
`Double Precision (ti-byte) floating point. May also be indicated as
`
`DOUBLE PRECISION or FLOA'I'(n) where 22 s n S 53.
`
`Single Precision (4-byte) floating point. May also be indicated as
`l7]..OA'I‘(n) where l S n S 21.
`
`
`
`Decimal number, where 1) indicates precision (I to 15) and 5 indicates
`I)ECIMAI_.(p,s)
`
`scale (0 to p)
`
`CIIAR(n)
`
`Fixed-length character string of length n, where l S n S 254. Data is
`padded with blanks if smaller than the defined size.
`
`VARCI-lAR(n)
`
`Varying-length character string, with maximum length n, where l s n
`S 32767.
`
`LONG VARCIIAR
`
`Varying-length character string, with maximum length 32767.
`
`7ixed~length graphic (double-byte) string of length n, where l S n S
`GRAPI‘IIC(n)
`127. Data is padded with blanks if smaller than the defined size.
`
`VARGRAPIIICUI)
`
`Varying—length graphic string, with maximum length n, where l S n
`5 16383.
`
`LUNG
`VARGRAI‘IIIC
`
`Varying-length graphic string, with maximum length [6383.
`
`Table I (Part 1 of 2). SQL Dalatypes
`
`55
`
`

`

`Description
`
`
` SQL Datatype
`
`
`
`A time in one of four standard forms (ISO, USA, .118, EUR).
`A date and time combination in ISO form. TIMESTAMI’
`
`
`A date in one Of four standard forms (ISO, USA, “3, EUR).
`
`Table 1 (Par120f2). SQL Datatypes
`
`If you want to attach additional information to tables, views or columns, the LABEL 0N and COMMENT
`ON statements may be used after the table or view has been created. Column labels are useful if you find
`the [ES-character length limit on column names too restrictive, and they can be retrieved along with or instead
`of the names when you retrieve the data. Comments are simply placed in the system catalog tables for infor-
`mational purposes.
`
`The final clause on the CREATE TABLE statement indicates where in the database to place the table. An
`assumption is made that the entity MYSPA CE exists and that the creator of the table is authorized to use it.
`This is one area where the individual database implementations are not the same. The allocation of space in
`the. database for the table is handled differently in 13132 and SQL/DS and requires some system-dependent
`information. The process of acquiring the space is usually done once, when a new application is designed,
`with the help of a databasc administrator, and will not be discussed further here.
`
`Note that this example shows only the simplest form of CREATE TABLE. Later releases of SOLIDS and
`D132 have added support for referential integrity. which allows association between tables and requires a
`more detailed form.
`
`Data Manipulation
`
`The three data manipulation SQL statements are INSERT, UPDA'I‘I'J and DELETE. They operate on indi-
`vidual rowx of a table, and their functions are indicated by their names.
`
`The UPDATE and DELETE statements choose the row operated on in one of two ways. The first is to use
`a search condition (WHERE clause). The number of rows changed or deleted depends on the number of
`rows that meet the search criteria. The second method is to position a data retrieval cursor at the row of the
`table to be operated on and use the CURRENT 0F eursorname clause.
`In this case, the number of rows
`changed or deleted is always one. Cursors will be discussed in more detail later in this tutorial.
`
`shows some examples of data manipulation statements. Note the use of the keyword
`Figure 3
`NULL to represent the absence of data.
`
`56
`
`

`

`INSERT INTO ABC VALUES( 'KAHAN' ,6,7,32000, ‘RES'
`
`,
`
`'Pfi’2')
`
`INSERT INTO ABC VALUES( 'JACKSON' ,1.1,16000, 'PROD' ,NULL)
`
`UPDATE ABC SET SALARY=35000 WHERE NAME=‘KAHAN'
`
`UPDATE ABC SET SALARY=35000 WHERE CURRENT 0F MXCUBSOR
`
`
`
`
`
`
`
`
`
`DELETE FROM ABC WHERE DEPT='RES'
`
`Figure 3. Data Manipulation Statements
`
`Data Retrieval
`
`The data retrieval SQL statement is SELECT- The search conditions are specified in the WHERE clause of
`the statement. The WHERE clause can range in complexity from being non-existent to containing an entire
`other SELECT statement. You can select all or any subset of columns from one or multiple tables.
`
`Another feature of the WHERE clause is that some values may be left as variables. to be filled in at exe-
`cution time. This is handled dilTerently in different languages, but most use the colon (:) to identify the vari-
`able.
`
`The search capabilities built into the SQL language are very powerful. It would be impossible to cover them
`in depth in this short tutorial. Some basic examples are given in Figure 4, and the reader is referred to the
`database documentation for the more advanced forms.
`
`
`SELECT * FROM ABC
`
`SELECT * FROM ABC ORDER BY DEPT
`
`SELECT NAME,DEPT,PROJ FROM ABC
`
`SELECT NAME,SALARY FROM ABC WHERE DEPT :
`
`
`
`SELECT NAME,SALARY FROM ABC WHERE DEPT
`:myvariable
`
`
`
`‘SALES'
`
`Figure 4. Data Retrieval Statements
`
`When a SELECT statement is issued, the result is a new relational table. (This explains the ability to select
`within a select).
`
`Associated with the SELECT statement is the concept of a cursor. A cursor is an imaginary pointer that
`indicates which row of the result table is the current row. The cursor is set to point to the top of the table,
`and as each row is retrieved it is moved to point to that row. Once the cursor is pointing to a row, the
`
`57
`
`

`

`CURRENT 0F clause may be used on an UPDATE or DELE'I'E to modify or delete that row. When the
`cursor moves beyond the end of the table, the program will be signalled that there is no more data in the
`table. More details on how the cursor is created and used will be given in Part 2.
`
`Authorization Statements
`
`Once a user or application has created some relational tables, it is usual that some other users or applications
`will want to use ,that data. The authorization statements GRANT and REVORE allow specification of which
`users can perform which operations on data created by another user. See Figure 5 for some examples of
`authorization statements.
`
`
`GRANT ALL ON ABC TO USERl
`
`
`
`GRANT INSERT. UPDATE (PROJ.DEPT} 0N ABC T0 USERE
`
`
`REVOKE INSERT 01? ABC FROM USERS!
`
`
`GRANT SELECT 03'? ABC TO PUBLIC
`
`Figure 5. Authorization Statements
`
`Control Statements
`
`Control statements do not operate on specific data, but rather specify the boundaries of a unit of work or a
`lock held on data.
`
`Units of work in 801. are sequences of operations which are performed together. Only when all the oper-
`ations in the sequence are successfully completed is the database to be permanently changed. A unit of work
`is begun when the first SQL statement is issued to the database, and is terminated by either a COMMIT
`statement, which makes the changes permanent, or a ROLLBACK statement, which cancels them. After the
`COMMIT or ROLLBACK, a new unit of work is begun.
`ln SOLIDS, the additional RELEASE option
`causes the database connection to be completely severed.
`
`When a user begins a new unit of work, a user id is assigned by the database. The default id is normally the
`operating system user id, but each database system provides a means of overriding that default. In SOLIDS,
`the CONNECT statement allows specification of an alternative id.
`If the correct password is given, the user
`may become another user or connect to a special id created just for a particular application.
`In DBZ, the
`SET CURRENT SQLII) statement allows the user to change the value of the authorization [D established
`by the DB2 authorization exits. More information on these exits is available in the BBQ! documentation.
`
`Locks on data can be controlled in various ways, one of which is to use the LOCK statement. This state-
`ment causes the database to override the implicit locking parameters, which may be useful when updates are
`to be made which depend on the status of the entire table remaining consistent. The lock override stays in
`effect until the end of the unit of work.
`
`58
`
`

`

`Figure 6
`
`shows examples of control statements.
`
`SOLIDS and D132:
`
`ROLLBACK WORK
`
`COMMIT WORK
`
`LOCK TABLE ABC IN SHARE MODE
`
`LOCK TABLE ABC IN EXCLUSIVE MODE
`
`SET CURRENT SQLID MYUSER
`
`SQL/DS:
`
`COMMIT WORK RELEASE
`
`ROLLBACK WORK RELEASE
`
`CONNECT MYUSER IDENTIFIED BY MYPASSWORD
`
`D32:
`
`Figure 6. Control Statements
`
`Analysis
`
`The EXPLAIN statement is a special SQL statement that does not do' any operations on data, but rather
`analyzes those operations. It takes an SQL statement as an argument, and places information about path
`selection into a special SQL table. This can be Very useful in tuning an application to take the best possible
`advantage of indices. For more information on EXPLAIN, see the database documentation.
`
`Using SQL
`
`59
`
`

`

`Using 80!. from Programs
`
`SOL is not a stand-alone language, in that it does not compile directly into executable machine language
`modules. It is either embedded in programs written in other compiled languages (such as assembler,
`FORTRAN, Put, COBOL and C), called interactively by interpretive languages (such as APLZ, RIEXX,
`and BASIC) or supported internally by end-user tools such as QMF. When using it in conjunction with
`compiled languages, the source code is passed through a pre-processor before the compile which translates
`the SQL statements into statements in the compiled language. With interpreted languages and end-user touls.
`there is an special step when the language or tool is installed to initialize the database support, but no pre—
`process is required when issuing SQL statements.
`
`Static SQL vs. Dynamic SQL
`
`Static SQL is a form of SQL where the SQL statements are known in advance and coded directly into a
`compiled program. Any variable data is passed in program variables whose names are also coded into the
`program. This type of SQL is supported only from compiled languages.
`
`With Dynamic 801., the SQL statements are not known until execution time. They are built into a buffer,
`and the buffer and any variable data is passed at execution time. This type of SQI. is supported both from
`compiled and interpreted languages, and is the type of SQI... we will be discussing in Parts 2 and 3 of this
`tutorial.
`
`Each of the two types of SQL has advantages and disadvantages- Static SQ! . is usually faster at execution
`time. as the path to the data can be determined at compile time. However. if the database status changes
`(new indices are added etc) and the program is not recompiled this adxantage can turn into a disadvan-
`tage Dynamic SQL, when issued from an interpreted language, requires less coding time andIS easily modi-
`fied if the problem changes, with no pre— process or compile required.
`
`Whether to use Static or Dynamic 80].. is a choice akin to that of whether to use a compiled or interpreted
`language. Each has its strengths. and many times a combination of the two provides the optimum solution.
`
`
`
`Es "HAPL2
`
`An auxiliary processor, Al’ 12?, allows APLZ programmers to imbed Structured Query Language (801...)
`statements in their AI’LZ functions.
`
`Aiong with AP 127, a workspace called SQL is distributed with the A912 product- The SQI. workspace
`contains APL2 functions for using AP 127. It contains a simple function for each Al’ 12? command. and
`some additional higher-level functions. In these examples we will use the simple command functions.
`
`60
`
`

`

`Relational Data in APL2
`
`showed an example relational table. We could represent that table in an Al‘LZ-matrix
`Figure 1
`with a row for each row of the table and a column for each column.
`If we did, it Would look like Figure 7.
`
`
`
`ADAMS
`
`BANKS
`CROW
`DEAN
`EATON
`
`ABCM
`12 3 36000 ADMIN PA1
`
`15 3 35000 SALES P32
`B
`2 2H000 PROD
`PP1
`12 3 38000 PROD
`PP2
`13 fl #0000 RES
`P31
`
`FARR
`CALVIN
`
`25 5 50000 ADMIN PA1
`5
`3 27000 SALES P31
`
`HARVEY
`INGRAM
`JACKSON
`KAHAN
`LAMAR
`
`23 5 #5000 SALES P31
`2
`1 18000 ADMIN PA2
`1
`1 16000 PROD
`6
`3 32000 RES
`21 5 05000 PROD
`
`PR2
`PP1
`
`MULVEY
`
`3
`
`2 21000 SALES PS2
`
`DISPLAY IH [ 1 JABC’M
`
`.+____
`
`.+——--
`
`IADAMSI 12 3 35000 lADMINl
`I _____ I
`
`IPAil
`
`+———-
`
`.+———--
`
`lBANKSI 15 3 35000 ISALESI
`l _____ l
`
`IPS2l
`
`Icsowl
`
`0
`
`2 2u000 lPRODl
`
`IPPII
`
`
`
`Figure 7. Relational data in an APLZ matrix
`
`The DlSl’l.AY workspace, included with APLZ. allows a pictorial representation of data, making it easier to
`see the type and structure of an object. Here, we display the first four rows of the ABCM variable for dem-
`onstration. Note that the numbers are scalars, so they have no boxes around them. The character items are
`vectors, and the entire object is a matrix.
`
`Another possible way to represent a relational table is as a vector of matrices. one for each column. Figure 8
`shows that form.
`
`61
`
`

`

`
`
` ABGV
`36000
`12
`3
`ADAMS
`
`35000
`BANKS
`15
`3
`20000
`CROW
`6
`2
`38000
`BEAM
`12
`3
`00000
`EATON
`18
`u
`
`
`
`FARR
`CALVIN
`HARVEY
`INGRAM
`JACKSON
`KAHAN
`LAMAR
`
`MULVEY
`
`25
`5
`23
`2
`1
`6
`21
`
`3
`
`5
`3
`5
`1
`1
`3
`5
`
`2
`
`50000
`27000
`05000
`18000
`16000
`32000
`#5000
`21000
`
`
`
`
`
`
`
`
`
`
`
`
`
`PROD
`RES
`
`ADMIN
`SALES
`
`SALES
`ADMIN
`
`PROD
`RES
`PROD
`SALES
`
`PP2
`PR1
`
`PA1
`PS1
`
`Ps1
`PA2
`
`PR2
`PP1
`P32
`
`DISPLAY u+[1]“ABcv
`+ _________________________________________
`
`
`
`I
`.+—~.
`.+-——u.
`.+————.
`.+.
`.+—.
`I
`I
`+12! +3I +36000| +ADMINI +PA1I
`I
`I
`I15|
`|3|
`IasoooI
`ISALESI
`IPS2|
`I
`I
`I 6|
`I2I
`l2uoool
`IPROD |
`|PP1I
`I
`|
`I12|
`|3|
`Iasoool
`IPROD I
`IPP2|
`I
`I
`'~..
`l~l
`I~_..__l
`l _____ I
`!___l
`I
`t: _________________________________________ l
`
`.+ ——————
`IADAMS
`
`
`
`Figure 8. Relational data in an APLZ vector
`
`In this alternative form, note that the variable-length character fields must be padded with blanks to the
`width of the widest value to create a uniform matrix. Null items must also be replaced with their prototype
`values, which are 0 for numeric items and blank for character items.
`
`APLZ allows retrieval of SQL data in both of these forms.
`
`SOL Statements in APL2
`
`In Part 1, we showed examples of SQL statements. We will now see how to execute those statements from
`within APL2.
`
`A?
`
`

`

`Data Definition, Authorization
`
`Data definition and authorization statements are executed directly, as is, by the database system. No variable
`substitutions are allowed. For this type of statement, we use the AP 127 command EXEC, which expects
`only one argument, the SQL statement. In Figure 9 ‘
`. we will use the EXEC function to execute
`the CREATE statement from our example, and to grant authority to all users to retrieve data from iut. We
`have placed the CREATE in an APLZIcharacter matrix called ABCC, since it is a longer statement.
`
`ABCC
`CREATE TABLE ABC
`
`(NAME VARCHAR(20).
`yos
`SMALLINT.
`
`LEVEL SMALLINT,
`SALARY INTEGER,
`DEPT VARCHAR(8).
`PROJ 0343(3))
`IN MYSPACE
`
`EXEC A3011?
`0 O D 0
`
`0
`
`0 0
`
`0
`
`0 0
`
`EXEC 'LABE’L 0N COLUMN ABC.NAME IS "THIS IS A LABEL'“
`0
`0
`'
`
`EXEC 'G’HANT SELECT 015' ABC TO PUBLIC'
`
`0 0 0
`
`Figure 9. Data Definition Statements from APLZ
`
`For those familiar with Dynamic SOL, the EXEC command issues an EXECUTE IMMEDIATE to SQL.
`
`AP I27 always returns a 5-item numeric return code, followed by any result data. For these statements, there
`is no result data, so the second item of the result is null. We will explain the meaning of non-zero return
`codes in a later section. Five zero codes, of course, means that operation completed successfully.
`
`Data Manipulation
`
`Data manipulation statements can be processed in two different ways. If there are no variable substitutions,
`they can be processed like data definition statements, with the EXEC command.
`if you wish to execute an
`SQL statement more than once, however, it can be much more efficient to use Variable substitution.
`
`With variable substitution in APLZ, the places for the values are indicated in the SQL statement by a colon
`(:) followed by a number which represents an index into an APL2 variable. The statement in that form is
`passed to AP 12'}r with a PREP command, and a name is attached to the statement to identify it for later
`
`63
`
`

`

`execution. AP 12'? will remember the indices and replace them with the Dynamic SQL placeholder “.7” before
`issuing an SQL PREPARE.
`
`To execute the statement, an AP 12'? CALL command is issued against the statement name, and the data, or
`value-list, is passed as an argument to that command. The value-list can contain more items than are actu-
`ally indexed, and the indices can select items in any order. The CALL command issues a Dynamic SQL
`EXECUTE.
`It may be repeated as many times as necessary, with different data passed each time.
`
`Figure 10 shows examples of data manipulation statements in APLZ.
`
`Without variable substitution:
`
`EXEC 'UPDATE' ABC SET SALARY=35000 WHERE NAME='
`0 0 0
`0
`
`'KAHAN'
`
`'
`
`"
`
`EXEC 'DELETE FROM ABC WHERE DEPT='
`0 O 0
`0
`
`'RES'
`
`'
`
`'
`
`0
`
`0
`
`With variable substitution:
`
`=3
`
`PREP 'NAME‘
`0 O 0
`0
`
`'INSERT INTO ABC VALUES(:1,:2,:3,:|4,:5,:6)'
`
`CALL 'NAME"
`0 0 0
`0
`
`CALL 'NAME"
`0 O 0
`
`('KAHAN'
`
`6
`
`'7 32000 'RES'
`
`'PR2')
`
`('JACKSON'
`
`1
`
`1 16000 'PHOD' ")
`
`0
`
`0
`
`0
`
`Figure 10. Data Manipulation Statements in APLZ
`
`Data Retrieval
`
`There are five required steps to perform a SELECT in Dynamic SQL. They are PREPARE, DESCRIBE,
`OPEN, FETCH and CLOSE. The AP 127 commands correSponding to these steps are PREP, OPEN,
`DESCRIBE, FETCH and CLOSE.
`
`The PREP command is the same for data retrieval as for data manipulation, except that it is required,
`whether or not there are variables in the statement.
`It sends the statement to SQL and assigns a name to it.
`
`The DESCRIBE command returns information about the result table names, datatypes, null status and
`labels. This AP 127 command may be iSsued at any time after the PREP, or may be omitted if not needed
`by the application.
`If it is omitted, AP 127 will issue the SQL DESCRIBE automatically before the OPEN
`and save the information for use later on. There are four different forms of describe which return the
`column names and labels in different combinations.
`
`64
`
`

`

`The OPEN command receives the value-list, if any, and sets the cursor to the beginning of the table (it does
`not yet point to any rows). The FETCH command causes the data to be passed from SQL. The CLOSE
`command tells SQL that we are done with the cursor.
`
`As with data manipulation statements, the PREP command may be issued once... with variable indices, and
`the OPEN, FETCH, CLOSE sequence may be repeated, with different data passed on each OPEN.
`
`Figure 11, Figure 12
`
`and Figure 13
`
`show some simple examples of this sequence.
`
`
`PREP 'NAME'
`D O 0
`
`0 O
`
`'SELECT 1' FROM ABC"
`
`0 0 D
`
`.
`
`3 3 2
`
`3 a 5 3 5 1 1 3 5 2
`
`OPEN 'NAME'
`0 0
`O
`
`0 0
`
`FETCH 'NAME'
`0 0 0 0
`0
`ADAMS
`BANKS
`CROW
`DEAN
`
`EATON
`FARR
`
`CALVIN
`HARVEY
`INGRAM
`
`JACKSON
`KAHAN
`LAMAR
`
`MULVEY
`
`CLOSE 'HAME'
`
`0 O
`
`Figure 11. Simple Data Retrieval
`
`65
`
`

`

`'NAME'
`
`NAME
`V 20
`NOT NULL
`
`LEVEL SALARY DEPT
`YOS
`S
`I
`V B
`S
`NULL NULL
`NULL
`NULL
`
`V 20
`NOT NULL
`
`I
`S
`S
`NULL NULL NULL
`
`V 8
`NULL
`
`C 3
`NULL
`
`
` 'NAME' 'SELECT * FROM ABC'
`
`
`
`
`PROJ
`
`
`C 3
`
`
`NULL
`
`
`
`
`
`'NAME'
`'LABELS'
`THIS IS A LABEL
`
`
`
`
`
`
`
`
`
`
`
`
`
`PROJ
`LEVEL SALARY DEPT
`YOS
`
`
`C 3
`S
`I
`V 8
`S
`
`
`
`NULL NULL
`NULL
`NULL
`NULL
`
`
`
`
`
`YOS
`
`LEVEL SALARY DEPT
`
`PROJ
`
`S
`S
`NULL NULL
`
`I
`NULL
`
`V 8
`NULL
`
`C 3
`NULL
`
`'ANY'
`'NAME'
`THIS IS A LABEL
`
`V 20
`NOT NULL
`
`‘BOTH'
`
`'NAME'
`NAME
`V 20
`NOT NULL
`THIS IS A LABEL
`
`Figure 12. DESCRIBE
`
`66
`
`

`

`PREP 'NAME'
`0 0 0
`
`'SELECT NAME FROM ABC WHERE DEPT =
`
`OPEN 'NAME'
`0 0 D
`
`(C'ADMIN')
`
`FETCH 'NAME'
`0 0 0
`ADAMS
`
`FARR
`
`INGRAM
`
`CLOSE 'NAME'
`0 O 0
`
`0 O 0 0 O
`
`OPEN ‘NAME'
`O 0
`0
`
`(C'SALES')
`
`FETCH 'NAME'
`0 0 O
`BANKS
`CALVIN
`HARVEY
`
`MULVEY
`
`CLOSE 'NAME'
`
`Figure l3. Data Retrieval with variable substitution
`
`You will notice that each FETCH request made to AP 127 returns multiple rows of the SQL table, even
`though the SQL FETCH only can receive one row at a time. The number of rows returned is controlled by
`an AP 12‘? Option. AP 12‘? will return the number of rows you request, or the whole table, whichever is
`smaller. If the entire table is not retrieved on the first call. the third item of the return code is set to one. You
`can repeat the FETCH step as many times as necessary to retrieve the entire table.
`
`A second AP 12? option allows you to choose the data format. As shown previously, there are two different
`formats. The default is the first. or mtrfi: format. The alternate format, which actually consumes less spaoe
`and so can be more efficient, is called vector format.
`
`If you choose to use the vector format, you may also request from AP 127 an additional data item called the
`length mrn'x which will tell you the lengths of the data before padding and null replacement. You can use
`this information to recreate the matrix form of the data. or perhaps to substitute alternate values for the SQL
`null.
`
`All of these options can be set permanently with the AP 12'? SE TOP T command, or temporarily during
`execution of the query by passing them as arguments to the FETCH command. Figure 14
`
`£7
`
`

`

`shows the use of the SE TOPT command, which sets options pennanently. Any or all of the options may
`be set on each call. Note that the SETOPT command does not cause any communication with SQL, it
`simply tells AP 12? how you would like to receive your data.
`
`0
`
`SETOPT 500
`0 0 0
`
`0 0
`
`
` ' VECTOR '
`
`SETIQPT
`0 O U
`U
`
`
`
`
`
`
`
`
`
`
`
`
`
`SETOPT 'LENGTH'
`0 O 0 0 0
`
`SETOPT 'MATRIX'
`D O 0 t)
`
`'NOLENGTH'
`
`20
`
`Figure 14. SETOI’I‘ command
`
`Figure 15
`
`shows the result when the vector format and length matrix Options are in effect.
`
`68
`
`

`

`'SELECT NAME,PROJ FROM 3136'
`
`PREP ‘NAME'
`D 0 0
`0
`
`OPEN 'NAME'
`0 O 0
`0
`
`0
`
`0
`
`’LE'NG‘TH '
`' VECTOR ’
`DISPLAY FETCH ‘NAME'
`+ —————————————————————————————————————— .
`
`.+——.
`
`+PA1|
`IP32|
`IPP1|
`IPP2|
`IPRiI
`lPAlI
`IPSlI
`Ips1l
`IPA2|
`|
`|
`lPR2|
`IPP1|
`
`I l
`
`+
`
`u———--——-—-——-——-—-—-——-—O
`
`Iwmmowmwwmmwww
`
`IHARVEY
`IINGRAM
`IJACKSON
`IKAHAN
`ILAMAR
`IMULVEY
`I _______
`
`. I l | I l l l l | I l I I I | I l' F
`
`igure 15. Vector Format with Length Matrix
`
`shows the retrieval of a table in multiple steps, controlling the number of rows with
`Figure 16
`In the case of such a small table, of course, one would not normally fetch the result in so
`the FETCH.
`many steps. The technique shown, however. may be used when space considerations prohibit fetching the
`entire table in one pass, or when the number of rows in the result cannot be predicted. The goal should be
`to minimize the number of calls to AP 12?, given the current space constraints.
`
`69
`
`

`

`PREP
`0 D 0 0
`0
`
`'NAME'
`
`'SELECT * FROM ABC'
`
`OPEN
`0
`0
`
`0
`
`'NAME'
`
`FETCH
`100
`
`FETCH
`0
`0
`
`1
`
`5
`
`'NAME'
`ADAMS
`BANKS
`CROW
`DEAN
`EATON
`
`'NAME'
`
`5
`
`FARR
`CALVIN
`
`HARVEY
`INGRAM
`JACKSON
`
`12
`15
`
`12
`18
`
`25
`
`23
`
`FETCH
`000
`
`5
`
`'HAME'
`KAHAN
`
`LAMAR
`
`MULVEY
`
`21
`3
`
`36000
`35000
`2u000
`38000
`”0000
`
`ADMIN
`
`SALES
`PROD
`PROD
`RES
`
`PAl
`PS2
`PPl
`PP2
`P31
`
`50000
`27000
`
`”5000
`18000
`
`15000
`
`ADMIN
`
`SALES
`SALES
`ADMIN
`PROD
`
`PA1
`P31
`
`P51
`PA2
`
`32000
`
`H5000
`21000
`
`RES
`PROD
`SALES
`
`PR2
`
`PP1
`
`PS2
`
`43(0wa
`
`HHU‘IDJU'I
`
`MU‘IW
`
`CLOSE
`0 0 0 0 0
`
`'NAME'
`
`
`
`
`
`Figure l6. Fetching a Table in Pieces
`
`Note: After each FETCH, the cursor is left pointing at the last row fetched. AP 12? cannot tell that the end
`of the table is reached until it tries to fetch beyond the end. Therefore, if you fetch exactly the number of
`rows in the table1 it is possible to get the ( 0
`0
`1
`0
`0 } return code when the table is actually completely
`fetched. In that case, the next FETCH call will produce the return code ( 0
`1
`O
`2
`1 O 0 ), indicating
`there are no rows in the result.
`
`If a result table has no rows, the data returned is an APL null prototype of the result. This lessens the need
`for special code to handle the null case, as the data will be the correct shape regardless of the number of
`show what the null results look like.
`rows fetched. Figure 17
`
`70
`
`

`

`0 O
`
`PREP 'NULL'
`0
`0
`
`O
`
`‘SELECT * FROM ABC WHERE NAME="WHEELER"'
`
`OPEN 'NULL'
`0 O O
`
`0 0
`
`DISPLAY FETCH 'NULL'
`
`'MATRIX'
`
`
`
`DISPLAY FETCH 'NULL'
`
`Figure 17. Null Result Tables
`
`Control Statements
`
`LOCK. SET CURRENT SOLID: LOCK and SET CURRENT SQLID are both processed with the EXEC
`command, like data definition and authorization statements. No variables are allowed in these statements.
`
`COMMIT. ROLLBACK: The COMMIT and ROLLBACK statements are processed by AP 127 commands
`of the same name. They met be processed with the EXEC command. The AP 127 commands allow an
`optional parameter, RELEASE, which disconnects the user from the database.
`In SOLIDS. this corresponds
`to the RELEASE option. In DB2, it causes a disconnect from the Call Attach Facility, which AP 127 uses
`to communicate with DB2.
`
`It is very important to he a responsible SQL citizen and issue COMM [Ts and ROLLBACKs as often as
`possible in your program. Not issuing them can cause data locks to be held, thus causing other users to wait
`for access to data. It is especially important to remember this in APLZ, because implicit COMMITs are
`never issued, and implicit ROLLBACKs are issued only at shared variable retraction and )OFF. In com-
`piled programming languages, the database connection is terminated when the program ends. In APLZ, AP
`12’? is the program. so the connection stays active unless something explicit is done to terminate it.
`
`Figure 18
`
`shows examples of COMMIT and ROLLBA CK from APLZ.
`
`71
`
`

`

`ROLLBAC'K
`O 0 D
`0 O
`
`COMMIT
`O 0 0
`
`0 0
`
`
`
`
`
`
`ROJJLBACK 'RELE'ASE'
`0
`0
`0 0 0
`
`
`Figure 18. ROLLBACK, COMMIT
`
`CONNECT: The CONNECT command is available in SQL/DS only‘ This command allows you to specify
`the User ID that will be used in making the database connection, and optionally, the name of the database
`as well. The facility is useful, for example, when you want only one User ID to have certain authority or
`access to certain tables. An application can connect to that ID to do work in SQL, and individual users of
`the application need not have authority to use the data directly.
`
`Use of CONNECT can also save keystrokes. When connected as another user. that user’s ID is automat-
`ically prefixed to all table names instead of your own ID. They then do not have to be explicitly typed.
`
`Figure 19
`
`shows an example of the use of the AP 127 CONNECT command.
`
`Y2
`
`

`

`
`
`CONNECT " " 'MYDATABASE'
`O 0 0
`
`'SELECT * FROM INVENTORY'
`
`'SELE'OT * FROM SQLDBA.IHVEN?ORY'
`
`PREP ‘NAME‘
`2 _201t
`
`0
`
`PREP 'NAME'
`O 0 0
`
`ROLLBA CK
`0 O
`
`0
`
`the User ID already active.
`
`CONNECT 'SQLDBA'
`0 0
`0
`
`'SQLDBAPW'
`
`’MYDATABASE'
`
`PREP 'NAME'
`0 0 O 0 0
`
`'SELECT * FROM INVENTORY'
`
`Note: The ROLLBACK is necessary before the CONNECT to disconnect
`
`Figure 19. CONNECT command
`
`EXPLAIN: The EXPLAIN command, when issued from Dynamic SQL. is also processed with the EXEC
`command. Note that the statement is not placed in quotes. After the EXPLAIN is processed, you must
`look in the special SQL tables created for EXPLAIN to find the information. The structure and naming
`conventions for those tables are dilferent in SQL/DS and DB2.
`
`00000
`
`EXEC ‘EXPLAIN ALL FOR SELECT * FROM ABC”
`
`Figure 20. EXPLAIN command
`
`Error Handling
`
`We have seen that the third item in the return code vector is used to flag the condition that there may be
`more rows in the result table.
`
`If the first is set to 1. there has been an error. If
`The first and second items flag error or warning conditions.
`the second is set, there has been a warning. If both are set, an error has occurred which has caused a trans-
`action backout. This means that the current unit of work has been rolled back by the system.
`
`73
`
`

`

`The fourth and filth items of the return code vector tell you the source of error or warning, and the error
`code.
`.
`
`It accepts as a param-
`The MESSA 08' function may be used to retrieve more information about an error.
`eter the return code vector from the AP 127 operation. The result from execution of the MESSA GE function
`depends on the type of error and the environment.
`
`AP 127‘ Errors: Sometimes an error or warning is detected in AP 127 before the request. is passed to SQL.
`When that happens. the fourth item in the return code vector is l, and the fifth is the AP 12'? message
`number.
`In Figure 21, we have typed an incorrect option setting. MESSAGE returns the actual text of the
`AP 12'? message that corresponds to the return code.
`
`SETOPT ‘VE'C'TOE‘
`1 127
`
`O
`
`1
`
`0
`
`VECTOE IS AN UNKNOWN OPTION VALUE
`
`MESSAGE 1 O O
`
`1 127
`
`ERROR MESSAGE.
`
`Figure 21. An AP 127 error
`
`SQL Errors: Sometimes, although AP 127 detects no error, SQL cannot process the command. When an
`error or warning is discovered by SQL, the fourth return code is 2. and the fifth return code is the
`SQLCODE.
`
`It will return the contents of the SQLCA control
`The MESSA GE function can also be used for SQL errors.
`block, which contains information about the status of the error in SQL. This information can be used in
`conjunction with the database message manual to debug the problem.
`
`Note: When executing in DB2. the MESSA GE function will also return the text of the error message as
`formatted by DBZ. In SQL/DS, message and help text is available in SQL tables installed with the system.
`You can code another SQL query to retrieve the message text if you wish to.
`
`Figure 22
`
`shows what happens when we execute an SQL statement against a non-existent table.
`
`7'4
`
`

`

`+ ___________________
`
`[ERROR MESSAGE.
`i ____________________
`
`+ ______________
`
`