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
- 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 table |
COPY TABLE | Copy table |
RENAME TABLE | Rename table |
DROP TABLE | Delete 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 SELECT instruction
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.
Enter the search criteria in the SQL ... "SELECT...WHERE..." function. 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.
Enter the ordering criteria in the SQL ... "SELECT...ORDER BY..." function. 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
- If you use the SQL EXECUTE NC function, the control will insert the SQL syntax element into the NC program only.
- 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 string parameters.
- After the WHERE syntax element, you can define the comparison value as a variable as well. If you use numerical parameters for the comparison, the control rounds the defined value to an integer. If you use string parameters, the control uses the defined value.
- If you check the content of a string parameter on the QPARA tab of the Status workspace, you possibly do not see the complete content.
Example
NC programs contained in this User's Manual are suggestions for solutions. The NC programs or individual NC blocks must be adapted before being used on a machine.
Change the following contents as needed:
- Tools
- Cutting parameters
- Feed rates
- Clearance height or safe position
- Machine-specific positions (e.g., with M91)
- Paths of program calls
Some NC programs depend on the machine kinematics. Adapt these NC programs to your machine kinematics before the first test run.
In addition, test the NC programs using the simulation before the actual program run.
With a program test you determine whether the NC program can be used with the available software options, the active machine kinematics and the current machine configuration.
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" |
NC programs contained in this User's Manual are suggestions for solutions. The NC programs or individual NC blocks must be adapted before being used on a machine.
Change the following contents as needed:
- Tools
- Cutting parameters
- Feed rates
- Clearance height or safe position
- Machine-specific positions (e.g., with M91)
- Paths of program calls
Some NC programs depend on the machine kinematics. Adapt these NC programs to your machine kinematics before the first test run.
In addition, test the NC programs using the simulation before the actual program run.
With a program test you determine whether the NC program can be used with the available software options, the active machine kinematics and the current machine configuration.
20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example WHERE Position_Nr<20" |
NC programs contained in this User's Manual are suggestions for solutions. The NC programs or individual NC blocks must be adapted before being used on a machine.
Change the following contents as needed:
- Tools
- Cutting parameters
- Feed rates
- Clearance height or safe position
- Machine-specific positions (e.g., with M91)
- Paths of program calls
Some NC programs depend on the machine kinematics. Adapt these NC programs to your machine kinematics before the first test run.
In addition, test the NC programs using the simulation before the actual program run.
With a program test you determine whether the NC program can be used with the available software options, the active machine kinematics and the current machine configuration.
20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example WHERE Position_Nr==:’Q11’" |
NC programs contained in this User's Manual are suggestions for solutions. The NC programs or individual NC blocks must be adapted before being used on a machine.
Change the following contents as needed:
- Tools
- Cutting parameters
- Feed rates
- Clearance height or safe position
- Machine-specific positions (e.g., with M91)
- Paths of program calls
Some NC programs depend on the machine kinematics. Adapt these NC programs to your machine kinematics before the first test run.
In addition, test the NC programs using the simulation before the actual program run.
With a program test you determine whether the NC program can be used with the available software options, the active machine kinematics and the current machine configuration.
20 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM ’V:\table\Tab_Example’ WHERE Position_Nr<20" |
NC programs contained in this User's Manual are suggestions for solutions. The NC programs or individual NC blocks must be adapted before being used on a machine.
Change the following contents as needed:
- Tools
- Cutting parameters
- Feed rates
- Clearance height or safe position
- Machine-specific positions (e.g., with M91)
- Paths of program calls
Some NC programs depend on the machine kinematics. Adapt these NC programs to your machine kinematics before the first test run.
In addition, test the NC programs using the simulation before the actual program run.
With a program test you determine whether the NC program can be used with the available software options, the active machine kinematics and the current machine configuration.
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 columns X, Y and Z of an existing table as a new table |
3 END PGM SQL_CREATE_TAB MM |
NC programs contained in this User's Manual are suggestions for solutions. The NC programs or individual NC blocks must be adapted before being used on a machine.
Change the following contents as needed:
- Tools
- Cutting parameters
- Feed rates
- Clearance height or safe position
- Machine-specific positions (e.g., with M91)
- Paths of program calls
Some NC programs depend on the machine kinematics. Adapt these NC programs to your machine kinematics before the first test run.
In addition, test the NC programs using the simulation before the actual program run.
With a program test you determine whether the NC program can be used with the available software options, the active machine kinematics and the current machine configuration.
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 |