1. What a database query is in a script
- A database query is a standalone, configurable application object defined outside the script.
- A script only uses a database query; it does not define it.
- In a script, a database query does not specify how data is selected, but how the selected data is further processed.
- A database query in a script is not an SQL statement written inside the script.
2. Loading values from a database query
- A script can load one or more values from a database query.
- Loading values from a query creates script variables.
- The data type of a variable is automatically derived from the database column type.
- If a database query returns multiple records, the variable becomes an array of values.
- If a database query returns no records, the variable becomes an array with zero elements.
3. Loading a single value
- When loading a single value from a database query, the first record of the result set is used.
- Any additional records are ignored.
- This approach is suitable for queries that logically return exactly one value (for example sums, minimums, or maximums).
4. Loading multiple values
- When loading multiple values from a database query, all rows of the result set are loaded.
- Each loaded column is stored in a separate variable.
- All variables have the same number of elements and are synchronized with each other.
5. Database queries and cycles
- Database queries are very often used together with the script command “Cycle”.
- The cycle iterates over individual elements of variables loaded from the database query.
- In each cycle iteration, variables behave as single-value variables.
- After the cycle finishes, variables again behave as arrays of values.
- It is recommended to use the primary key “id” as the controlling variable of the cycle.
6. Saving values back to the database
- Values loaded from a database query can be saved back to the database.
- Saving is always performed based on a database query that defines the target records.
- If the database query matches multiple records, the value is written to all of them.
7. Typical usage scenarios
- Loading values from the database into variables and processing them further.
- Bulk updates of database records using a cycle.
- Creating new records based on values from other database tables.
8. Typical mistakes when working with queries
- Expecting a single value while the query returns multiple records.
- Forgetting to use a cycle when working with arrays of values.
- Using an ambiguous database query without sufficient filtering conditions.
- Unconsidered use of queries returning a large number of records.
9. Examples
9.1. Deleting a large number of records using a script
- When deleting a large number of records (roughly more than 10,000) directly from the datagrid, the operation may fail. The threshold is not fixed and depends on record size, history, synchronization, and the database server.
- A single datagrid request must load and process many records and may create a history row for each one. This increases the risk of a timeout and exhaustion of memory or database resources.
- Use the optimized “DELETEDATA” function when history is not required. If the existing history must also be removed, records and their history can be deleted iteratively one at a time; “DELETEDATA” and “DELETEHISTORY” calls execute immediately outside the deferred transaction of script commands.
9.1.1. Delete option without history records
DELETEDATA(ng_abc, id > 10)
9.1.2. Delete option including history records
A = SELECT id FROM ng_abc WHERE id > 10
Cycle (A)
DELETEDATA(ng_abc, id = #A#)
DELETEHISTORY(ng_abc, changedid = #A#)
End of cycle
