Fundamentals

Application

If you would like to access content in a table or manipulate the tables (e.g., rename columns or rows), then use the available SQL commands.

The syntax of the SQL commands available on the control is strongly influenced by the SQL programming language but does not conform with it entirely. In addition, the control does not support the full scope of the SQL language.

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

In the NC software, table accesses occur through an SQL server. This server is controlled via the available SQL commands. The SQL commands can be defined directly in an NC program.

The server is based on a transaction model. A transaction consists of multiple steps that are executed together, thereby ensuring that the table entries are processed in an orderly and well-defined manner.

The SQL commands take effect in the Program Run operating mode and the MDI application.

Example of transaction:

  • Assign variables to table columns for read- or write-access using SQL BIND
  • Select data using SQL EXECUTE with the instruction SELECT
  • Read, change, or add data using SQL FETCH, SQL UPDATE, or SQL INSERT
  • Confirm or discard interaction using SQL COMMIT or SQL ROLLBACK
  • Approve bindings between table columns and variables using SQL BIND
 
Tip

You must conclude all transactions that have been started—even exclusively reading accesses. Concluding the transaction is the only way to ensure that changes and additions are transferred, that locks are removed, and that used resources are released.

The result set contains a subset of a table file. It results from a SELECT query performed on the table.

The result set is created when a query is executed in the SQL server, thereby occupying resources there.

This query has the same effect as applying a filter to the table, so that only part of the data records become visible. To perform this query, the table file must be read at this point.

The SQL server assigns a handle to the result set, which enables you to identify the result set for reading or editing data and completing the transaction. The handle is the result of the query, which is visible in the NC program. The value 0 indicates an invalid handle, i.e. it was not possible to create a result set for that query. If no rows are found that satisfy the specified condition, an empty result set is created and assigned a valid handle.

Overview of SQL commands

The control provides the following SQL commands:

Syntax

Function

Further information

SQL BIND

SQL BIND establishes or removes connections between table columns and variables

SQL SELECT

SQL SELECT reads out a single value from a table and does not open any transaction

SQL EXECUTE

SQL EXECUTE opens a transaction for selected table columns and table rows or enables the use of other SQL instructions (miscellaneous functions).

SQL FETCH

SQL FETCH transfers the values to the bound variables

SQL ROLLBACK

SQL ROLLBACK discards all changes and concludes the transaction

SQL COMMIT

SQL COMMIT saves all changes and concludes the transaction

SQL UPDATE

SQL UPDATE expands the transaction to include the change of an existing row

SQL INSERT

SQL INSERT creates a new table row

Notes

 
Notice
Danger of collision!
Read and write accesses performed with the help of SQL commands always occur in metric units, regardless of the unit of measure selected for the table or the NC program.
If, for example, you save a length from a table to a Q parameter, then the value is thereafter always in metric units. If this value is then used for the purpose of positioning in an inch program (L X+Q1800), then an incorrect position will result.
  1. In inch programs, convert the read value prior to use
 
Notice
Danger of collision!
Even during simulation of an NC program, the control executes all SQL commands. Here, SQL commands may overwrite table values, for example, that also take effect in the Program Run operating mode. The overwritten values can lead to unexpected behavior or incorrect positioning during subsequent machining operations. There is a danger of collision.
  1. Skip SQL commands during simulations by using conditional jumps, for example
  2. Use FN18: SYSREAD ID992 NR16 to check whether the NC program is active in a different operating mode or in Simulation
  • HEIDENHAIN recommends that you use SQL functions instead of FN 26, FN 27, or FN 28 in order to achieve maximum HDR hard-disk speeds for table applications and to reduce the amount of computing power used.