Developers

Omnis Technical Note TNSQ0035 June 2016

Running Asynchronous Tasks Using SQL Workers

for Omnis Studio 8.x or above
by Omnis Engineering

The Omnis DAMs allow the creation of “SQL Workers” which are instantiated from a SQL Object variable type available in the Oracle, ODBC, JDBC, MySQL, PostgreSQL, DB2, Sybase, and SQLite DAMs. SQL workers were introduced in Omnis Studio 6.0 and have been enhanced in Studio 8.0 (with support for Push connections). Using a number of SQL workers you can execute a number of long-running tasks, such as multiple SELECT statements, on different background threads that report back to the main thread as each task completes. Special completion methods within each worker thread allow you to populate different forms asynchronously, making applications potentially faster or more responsive and user-friendly.

Worker objects are created by sub-classing an Omnis Object class with the appropriate SQL Worker Object type. You initialize the object by supplying a SQL statement along with any bind variables that the SQL statement may need. Logon details or optionally the name of a session pool are also passed during initialization. A SQL Worker thread is dispatched by calling the $start() method. Upon completion, the worker thread calls back into the worker object's $completed() method, or $cancelled(), with the result set or error information.

There is an example library to accompany this tech note which shows multiple threads running queries against a SQLite database. The code samples below are from the example library.

Download the sample library from Github: https://github.com/OmnisStudio/Omnis-SQLWorker (requires Studio 8.1.2 or above)
(or the archive: sqlworker.zip requires Studio 8.x or above).

Creating SQL Worker Objects

Worker objects are created by sub-classing an Omnis object class as a Worker Object. In the example library there is an object class called oSqliteWorker, which has its $superclass property set to .SQLITEDAM.Worker Objects\SQLiteWorker, the SQLite worker object. To create this object, you would need to create an object class, open the Property Manager and select the $superclass property. Click on the dropdown menu next to the property and in the Select Object dialog drill down into the External Objects group, then the Worker Objects group, select the SQLiteWorker object and click on OK (this object already exists in the example library).

The oSqliteWorker object is instantiated in the main wSQLWorker window as the instance variable iWorkerObj which has type set to Object and its subtype set to the oSqliteWorker object class.

How do Worker Objects work?

The iWorkerObj object variable can be used inside the window to call the worker object methods: these are $init() to initialize or reset the worker object ready to perform its task, $start() to start the worker task on a background thread (which is non-blocking), or $cancel() to abort a worker task if required. You should examine the ‘runQuery’ method in the wSQLWorker window class methods since it contains the code to prepare and run the queries against the worker object. The database connection details, the queries themselves, and the bind variables are set up in the $construct() method in the window. When the run button in the window is clicked the runQuery method is executed setting up the connection types (in the paramsrow variable), queries and bind variables (in the work variable), and kicking off the worker object task.

; paramsrow row variable is populated with the SQL connection details ; work list variable contains the queries or tasks to be completed Do iWorkerObj.$cancelifrunning.$assign(kTrue) Do iWorkerObj.$waitforcomplete.$assign(kFalse) Do iWorkerObj.$init(paramsrow) Returns flag If flag     Do iWorkerObj.$setCallingInst($cinst)     Do iWorkerObj.$start() Returns #F     Do $cinst.$objs.Start.$enabled.$assign(kFalse)     Do $cinst.$objs.Cancel.$enabled.$assign(kTrue) Else     OK message SQL Worker {[iWorkerObj.$errorcode], [iWorkerObj.$errortext].} End If

When you run the queries set up in the SQL Worker window the results are returned after a short delay. You can open multiple copies of the window from the Worker menu and click Run in all of them simultaneously to see the queries running in parallel. You can right-click on the queries list to add further queries or delete existing ones. You could adapt the connection details and queries to execute the worker tasks against your own database, and to test the capabilities of the worker objects to run queries or perform other tasks asynchronously.