Reading out a table value with SQL SELECT

Application

SQL SELECT reads a single value from a table and saves the result in the defined variable.

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 show internal processes of SQL SELECT

With SQL SELECT, there is neither a transaction nor a binding between the table column and variable. The control does not consider any bindings that may exist to the specified column. The control copies the read value only into the parameter specified for the result.

Input

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.

11 SQL SELECT Q5 "SELECT Mess_X FROM Tab_Example WHERE Position_NR==3"

; Save the value of the "Position_No" column of the "Tab_Example" table in Q5

To navigate to this function:

Insert NC function All functions FN SQL table access SQL SELECT

The NC function includes the following syntax elements:

Syntax element

Meaning

SQL SELECT

Syntax initiator for the SELECT SQL command

Parameter

Variable in which the control stores the result

Name or Parameter

SQL statement or string parameter with the definition containing:

  • SELECT: Table column of the value to be transferred
  • FROM: Synonym or absolute path of the table (path in single quotation marks)
  • WHERE: Column designation, condition, and comparison value (variable after : in single quotation marks)

Text, string parameter, or format string

Notes

  • You can select multiple values or multiple columns using the SQL command SQL EXECUTE and the SELECT statement.
  • 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.
  • For the instructions within the SQL command, you can likewise use single or combined string parameters.
  • Concatenating values of string parameters

  • 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

The result of the following NC programs is identical.

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_READ_WMAT MM

1 SQL Q1800 "CREATE SYNONYM my_table FOR 'TNC:\table\WMAT.TAB'"

; Create synonym

2 SQL BIND QS1800 "my_table.WMAT"

; Bind QS parameters

3 SQL QL1 "SELECT WMAT FROM my_table WHERE NR==3"

; Define search

* - ...

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.

* - ...

3 SQL SELECT QS1800 "SELECT WMAT FROM my_table WHERE NR==3"

; Read and save value

* - ...

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.

* - ...

3 DECLARE STRING QS1 = "SELECT "

4 DECLARE STRING QS2 = "WMAT "

5 DECLARE STRING QS3 = "FROM "

6 DECLARE STRING QS4 = "my_table "

7 DECLARE STRING QS5 = "WHERE "

8 DECLARE STRING QS6 = "NR==3"

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

10 SQL SELECT QL1 QS7

* - ...