Executing SQL statements with SQL EXECUTE

Application

SQL EXECUTE can be used in conjunction with various SQL instructions.

Requirements

  • Code number 555343
  • Table exists
  • Appropriate table name
  • The names of tables and table columns must start with a letter and must not contain an arithmetic operator (e.g., +). Due to SQL commands, these characters can cause problems when data are input or read.

Description of function

SQL EXECUTE_01
Black arrows and associated syntax indicate internal processes of SQL EXECUTE. The gray arrows and associated syntax do not directly belong to the SQL EXECUTE command.

The control provides the following SQL statements in the SQL EXECUTE command:

Instruction

Function

SELECT

Select data

CREATE SYNONYM

Create synonym (replace long path names with short names)

DROP SYNONYM

Delete synonym

CREATE TABLE

Generate a table

COPY TABLE

Copy table

RENAME TABLE

Rename table

DROP TABLE

Delete a table

INSERT

Insert table rows

UPDATE

Update table rows

DELETE

Delete table rows

ALTER TABLE

  • Add table columns using ADD
  • Delete table columns using DROP

RENAME COLUMN

Rename table columns

SQL EXECUTE with the SQL instruction SELECT

The SQL server places the data in the result set row-by-row. The rows are numbered in ascending order, starting with 0. The SQL commands FETCH and UPDATE use these row numbers (the INDEX).

SQL EXECUTE, in conjunction with the SQL instruction SELECT, selects the table values, transfers them to the result set, and always opens a transaction in the process. Unlike the SQL command SQL SELECT, the combination of SQL EXECUTE and the SELECT instruction allows multiple columns and rows to be selected at the same time.

In the function SQL ... “SELECT...WHERE...", you can enter the search criteria. You thereby restrict the number of rows to be transferred. If you do not use this option, then all of the rows in the table are loaded.

In the function SQL ... “SELECT...ORDER BY...", you can enter the ordering criterion. This entry consists of the column designation and the keyword ASC for ascending or DESC for descending order. If you do not use this option, then rows will be stored in a random order.

With the function SQL ... "SELECT...FOR UPDATE", you can lock the selected rows for other applications. Other applications can continue to read these rows but are unable to change them. If you make changes to the table entries, then it is absolutely necessary to use this option.

Empty result set: If no rows meet the search criterion, then the SQL server returns a valid HANDLE without table entries.

Conditions for WHERE entries

Condition

Programming

Equals

= ==

Not equal to

!= <>

Less than

<

Less than or equal to

<=

Greater than

>

Greater than or equal to

>=

Empty

IS NULL

Not empty

IS NOT NULL

Linking multiple conditions:

Logical AND

AND

Logical OR

OR

Notes

  • You can also define synonyms for tables that have not yet been generated.
  • The sequence of the columns in the created file corresponds to the sequence within the AS SELECT instruction.
  • For the instructions within the SQL command, you can likewise use single or combined QS parameters.
  • Concatenation of alphanumeric values

  • If you check the content of a QS parameter in the additional status indicator (QPARA tab), then you will see only the first 30 characters and therefore not the entire content.
  • QPARA tab

Example

Example: selection of table rows

11 SQL BIND Q881 "Tab_Example.Position_Nr"

12 SQL BIND Q882 "Tab_Example.Measure_X"

13 SQL BIND Q883 "Tab_Example.Measure_Y"

14 SQL BIND Q884 "Tab_Example.Measure_Z"

. . .

20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example"

Example: Select table rows with the WHERE function

20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example WHERE Position_Nr<20"

Example: Select table rows with the WHERE function and Q parameter

20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example WHERE Position_Nr==:’Q11’"

Example: Define the table name with absolute path information

20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM ’V:­\table­\Tab_Example’ WHERE Position_Nr<20"

0 BEGIN PGM SQL_CREATE_TAB MM

1 SQL Q10 "CREATE SYNONYM NEW FOR 'TNC:\table\NewTab.TAB'"

; Create synonym

2 SQL Q10 "CREATE TABLE NEW AS SELECT X,Y,Z FROM 'TNC:\prototype_for_NewTab.tab'"

; Create table

3 END PGM SQL_CREATE_TAB MM

0 BEGIN PGM SQL_CREATE_TABLE_QS MM

1 DECLARE STRING QS1 = "CREATE TABLE "

2 DECLARE STRING QS2 = "'TNC:\nc_prog\demo\Doku\NewTab.t' "

3 DECLARE STRING QS3 = "AS SELECT "

4 DECLARE STRING QS4 = "DL,R,DR,L "

5 DECLARE STRING QS5 = "FROM "

6 DECLARE STRING QS6 = "'TNC:\table\tool.t'"

7 QS7 = QS1 || QS2 || QS3 || QS4 || QS5 || QS6

8 SQL Q1800 QS7

9 END PGM SQL_CREATE_TABLE_QS MM