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.
Related topics
- Opening, reading and writing to freely definable tables
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
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
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.
- In inch programs, convert the read value prior to use
- Skip SQL commands during simulations by using conditional jumps, for example
- 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.