SQL EXECUTE can be used in conjunction with various SQL instructions.
Application
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

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 |
|
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.
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.
- 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.
Concatenation of alphanumeric values
Example
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" |
20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example WHERE Position_Nr<20" |
20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example WHERE Position_Nr==:’Q11’" |
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 |