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 Q parameter.
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
With SQL SELECT, there is neither a transaction nor a binding between the table column and Q parameter. 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
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 SQL SELECT
The NC function includes the following syntax elements:
Syntax element | Meaning |
---|---|
SQL SELECT | Syntax initiator for the SELECT SQL command |
Q, QL, QR, QS, or Q REF | Variable in which the control stores the result |
Name or QS | SQL statement or QS parameter with the definition containing:
Fixed or variable name |
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, which can also be a variable. If you use Q, QL, or QR parameters for the comparison, the control will round the defined value to the next integer. If you use a QS parameter, the control will use the exact value you specified.
- For the instructions within the SQL command, you can likewise use single or combined QS parameters.
- If you check the content of a QS parameter in the additional status indicator (QPARA tab), then you will see only the first 30 characters and therefore not the entire content.
Example
The result of the following NC programs is identical.
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 |
* - ... |
* - ... | |
3 SQL SELECT QS1800 "SELECT WMAT FROM my_table WHERE NR==3" | ; Read and save value |
* - ... |
* - ... | |
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 | |
* - ... |