10.1.4.1 execute_prepared_stmt_async

The execute_prepared_stmt_async routine creates a task that executes SQL statements asynchronously within an event stored in the schema_name schema.

The application-level variant for this routine is execute_prepared_stmt_from_app_async.

This topic contains the following sections:

• Syntax

• Syntax Example

• See Also

Syntax

mysql> CALL mysql_tasks.execute_prepared_stmt_async (
IN 'sql_statements' TEXT,
IN 'schema_name' VARCHAR(255),
IN 'task_name' VARCHAR(255),
IN 'task_data' JSON,
OUT @task_id VARCHAR(36));

Following are execute_prepared_stmt_async parameters:

• sql_statements (TEXT): specifies the SQL statements that you want to run asynchronously represented as a string value separated by a semi-colon (;).

• schema_name (VARCHAR(255)): specifies the schema to run the given SQL statements. If the schema is already set for the current session, you can set this parameter to NULL to use the current session schema.

• task_name (VARCHAR(255)): specifies a name for the asynchronous task. If set to NULL, execute_prepared_stmt_async is used as the default task name.

• task_data (JSON): specifies additional metadata related to the task, represented in JSON format. You can use this parameter to store metadata that you might find relevant for the task, such as parameter values that you passed to the task. It is optional to add a value for this parameter. If there is no metadata, set this parameter to NULL.

• @task_id (VARCHAR(36)): output parameter that returns the unique identifier (UUID) of the created task. Use this ID to monitor the task status or manage the task.

Syntax Example

mysql> CALL mysql_tasks.execute_prepared_stmt_async('DO SLEEP(5); 
SELECT JSON_OBJECT("Async Task completed at", now()) into @task_result', NULL, 'DemoAsyncTask', NULL, @task_id);

In this example, the first SQL statement DO SLEEP(5) simulates a time-consuming task. This can be any long-running task.

The second statement generates an output that is stored in the reserved session variable @task_result. You can optionally use the reserved session variable @task_result to add a custom task status message for the asynchronous task, or to store an output generated by an SQL statement that you are running asynchronously. The value that you want to store in @task_result must be in JSON format. Any value stored in @task_result is included in the asynchronous task status data field.

The task ID generated for the asynchronous task that is returned by the routine is stored in the session variable @task_id.

See Also

Running tasks Asynchronously.