MySQL HeatWave User Guide
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:
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.
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.