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

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

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.

Conditions for WHERE entires

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.
  • Concatenating values of 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.
  • The QPARA 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.

 
Tip

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.

Example: selecting 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"

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.

 
Tip

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.

Example: selecting 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"

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.

 
Tip

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.

Example: selecting 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’"

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.

 
Tip

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.

Example: defining 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"

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.

 
Tip

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.

Example: generating a table with CREATE TABLE

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.

 
Tip

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