In Omnis Studio 10.1 we introduced a great new feature to make it a lot easier and faster to handle SQL data contained in a list. By using a SQL Worker Object you can now perform operations on large lists of SQL data asynchronously, on a background thread – the Worker Object must be the same DAM type as the SQL session object, and there are some differences in the way that you can use a table class from which the list or row is defined. So there is less scope to override SQL methods using the table class because of the need to execute the worker in a separate self-contained thread.
Using a Worker in a SQL List or Row
If you want to use a worker object with your SQL list or row, you need to assign a new SQL session property, $useworker to kTrue, but after assigning $sessionobject (after which you can no longer assign $sessionobject, or access $statementobject). There is also a new property $synchronous, which defaults to false meaning use another thread. If set to true (and $useworker is true), the worker object executes synchronously in the current thread.
Selecting & Fetching Data
When using SQL Worker based lists the result set is generated by the worker in a separate thread, therefore the $select() and $fetch() methods cannot be used. In this case, you can use a new method $selectfetch that performs both the select and the fetch of the data ($selectfetch() cannot be used with a row variable and it cannot be overridden in a table class). It has the following definition:
- $selectfetch()
$selectfetch([bDistinct=kFalse, iMaxRows=1, bAppend=kTrue, cText,…])
bDistinct
Pass this as kTrue to make the worker use a SELECT DISTINCT query rather than SELECT.
iMaxRows
The maximum number of rows to fetch. Must be between 1 and 10000000 inclusive.
bAppend
Pass this as kTrue to append the fetched data to the list, kFalse to replace the list contents with the fetched
cText,…
Any further parameters are treated as SQL text and appended to the generated SELECT or SELECT DISTINCT
After fetching the data, the worker generates a notification to $completed in the table instance. When using a worker, you cannot override $insert, $update or $delete in a table class. When you execute these methods via a worker, the table instance copies the current values of the affected row (rows for $update) into the parameter list for the worker, and then starts the worker.
Smart List Methods
When using a worker, you cannot override $do… methods, or call $doinsert, $doupdate or $dodelete. When you call $dowork, $doinserts, $doupdates or $dodeletes, the table instance generates a single query for each of the relevant operations insert, update and delete.
Completion Row
The table instance properties $rowsaffected and $rowsfetched are not relevant when using a worker. $completed in the table instance is passed a row variable parameter with columns: errorcode, errortext, work, errors, and rowsFetched.
For more information about using SQL Worker Objects, refer to the online docs.