CS

Working with database queries in scripts

Home / Documentation / Administrator guide / Script designer /

Working with database queries in scripts

CS

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

  • The “Assign value from query” command loads one column from a database query.
  • The values from all returned records are loaded into a variable – an array of values is created. For a column of a “MultiListBox” control, the elements of the array are the individual items from all returned records, without duplicates.
  • The value from the first returned record is written to a database control; if the query returns no record, the database value “null” is written.
  • For a query that logically returns exactly one value (for example a sum, a minimum or a maximum), the result is an array with one element.

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.
  • The value from the first returned record is written to a database control. If the query returns no record, the control stays unchanged.
  • A calculation can optionally be attached to each loaded value – an operator and a second operand, which is the value of a database control of the current record, the value of another column of the same record of the query, or a value written into the value field.

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, the controlling variable and the variables loaded by the same command as the controlling variable behave as single-value variables. Other variables with an array of values remain arrays of values inside the cycle as well.
  • After the cycle finishes, variables again behave as arrays of values. A value that the script assigns during an iteration to the controlling variable or to another variable loaded by the same command is stored in the corresponding element of its array when the iteration completes (by the “Next...” command), so the “Save variable back to the database” command placed after the cycle writes it to the database. This does not apply to a cycle over a variable that was not loaded from a database query, nor to an iteration that the script leaves by the “Jump to line” command – the changes from such an iteration are lost.
  • 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 by three commands that behave differently:
    • “Save variable back to the database” writes each element of the variable to the record it was loaded from. The query is not run again, and the number of elements of the variable must not change since it was loaded. Only a variable loaded from a column of the form the query is built on can be saved back; a variable from the “(ID)” column, from a column of a joined form or from a column of a “MultiListBox” control ends with an error. Records that the script deleted earlier by the “Delete record” command are skipped.
    • “Save value to the database” writes one value to all records returned by the database query of this command.
    • “Save more/multiple values to the database” writes each of its assignments (a variable, a database control or a value written into the value field) to the selected column of all records returned by the database query of this command.
    • These two commands do not split a variable with an array of values among the records: they write its first element to all records, and all its elements as its items to the column of a “MultiListBox” control. The “Save variable back to the database” command writes each element to its own record.
  • The writing script commands (“Save variable back to the database”, “Save value to the database”, “Save more/multiple values to the database”, “New record” and “Delete record”, including their conditional variants) are performed in the database only after the whole script completes successfully, in the “OnBeforeSave” and “OnBeforeDelete” scripts only in the same transaction after the record is saved or deleted. If deleting is skipped by the “SKIPDELETE()” function, or saving by the “SKIPSAVE()” function in an “OnBeforeSave” script run other than by saving the edit form (for example by a bulk change or an import from Excel), they are not performed at all.
  • A writing command writes only the columns whose new value differs from the initial state of the record. For the record the edit form works with, the initial state is the value loaded from the database at the beginning of processing the form (except in the “OnAfterSave” and “OnAfterDelete” scripts), otherwise the current state in the database; if the script has already written into the same record before, the comparison is made with this not yet committed write. If the “OnBeforeSave” script therefore writes into the record being saved a value equal to its original value in the database, the write is skipped and the value with which the record is being saved (from the edit form, a multiple change, an import and so on) is saved.
  • Until then, later script commands that work with a database query get the new values of the columns of the form the query is built on for the returned records, and they leave out the records scheduled for deletion. They do not see new records, though, and evaluate the conditions, sorting and aggregation of the query over the original values.
  • The “SQL()” server function and external functions see the state of the database before the write.
  • The writing script commands do not run the scripts or the validation of the form whose records they write. They write the changes to the change history and pass them to synchronization in the same way as saving the record in the form. Processing a record by the save scripts of its form is provided by the “OBSOAS” server function.
  • The “COMMIT()” server function makes the writes take effect immediately – a detailed description is provided in the separate guide Server functions.

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.
  • When history is not required, the optimized “DELETEDATA” function is used. 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