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.
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.
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).
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.
Database queries and loops
- Database queries are very often used together with the script command “Loop”.
- The loop iterates over individual elements of variables loaded from the database query.
- In each loop iteration, variables behave as single-value variables.
- After the loop finishes, variables again behave as arrays of values.
- It is recommended to use the primary key “id” as the controlling variable of the loop.
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.
Typical usage scenarios
- Loading values from the database into variables and processing them further.
- Bulk updates of database records using a loop.
- Creating new records based on values from other database tables.
Typical mistakes when working with queries
- Expecting a single value while the query returns multiple records.
- Forgetting to use a loop 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.