MySQL :: HeatWave User Guide :: 10.1.4.2 execute_prepared_stmt_from_app

HeatWave User Guide / ... / execute_prepared_stmt_from_app_async

10.1.4.2 execute_prepared_stmt_from_app_async

The execute_prepared_stmt_from_app_async routine creates an asynchronous task associated with a specific application user. Application-level asynchronous task lets you filter and track tasks for individual application users using an application-managed user ID. This routine includes options that define the structure and format used for storing task status and logs data.

This topic contains the following sections:

Syntax

mysql> CALL mysql_tasks.execute_prepared_stmt_from_app_async (
IN 'sql_statements' TEXT,
IN 'app_user_id' VARCHAR(255),
IN 'schema_name' VARCHAR(255),
IN 'task_type' VARCHAR(80),
IN 'task_name' VARCHAR(255),
IN 'task_data' JSON,
IN 'data_json_schema' JSON,
IN 'log_data_json_schema' JSON,
IN 'progress_monitor_sql_statements' TEXT,
IN progress_monitor_refresh_period DECIMAL(5,2),
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 (;).
app_user_id (VARCHAR(255)): specifies the unique ID of the application user initiating the task.
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_type (VARCHAR(80)): specifies a task type that you can later use to filter the tasks based on the specified task type. If the value is NULL, tasks of all types are included.
task_name (VARCHAR(255)): specifies a name for the asynchronous task. If set to NULL, execute_prepared_stmt_from_app_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.
data_json_schema (JSON): specifies the JSON schema that defines the format and data types used for the task_data field included in the asynchronous task status. You can set this to NULL if no specific structure definition is required for the task data.
log_data_json_schema (JSON): specifies the JSON schema that defines the format and data types used for the data field included in the task logs. You can set this parameter to NULL if no specific structure definition is required for the task log data.
progress_monitor_sql_statements (TEXT): specifies an optional SQL statement to monitor the task progress. Set this parameter to NULL if progress monitoring is not required.
progress_monitor_refresh_period (DECIMAL(5,2)): specifies the refresh interval in seconds. If set to NULL, the refresh period is set to 5 seconds by default.
@task_id (VARCHAR(36)): output parameter that returns the unique identifier of the created task. Use this ID is to monitor the task status or manage the task.

Syntax Example

mysql> CALL mysql_tasks.execute_prepared_stmt_from_app_async(
'DO SLEEP(5); SELECT JSON_OBJECT("Task result", "Finished...") into @task_result',
'app_user_01',
'demo_db',
'demo task type',
'demo task name',
JSON_OBJECT('sleep', 5, 'refresh_period', 1),
'{
  "type": "object",
  "properties": {
      "sleep": {
          "type": "integer"
      },
      "refresh_period": {
          "type": "integer"
      }
  }
}',
'{
  "type": "object",
  "properties": {
    "iteration": {
      "type": "integer"
    },
    "Task result": {
      "type": "string"
    },
    "connection_id": {
      "type": "integer"
    }
  },
  "additionalProperties": false,
  "oneOf": [
    { "required": ["iteration"] },
    { "required": ["Task result"] },
    { "required": ["connection_id"] }
  ]
}',
@progress_monitor_sql_statements,
1,
@task_id);

Where:

mysql> SET @progress_monitor_sql_statements = '
  SET @my_counter = coalesce(@my_counter, 0) + 1;
  SET @my_progress = @my_counter * 100 / 5;
  CALL mysql_tasks.add_task_log(@task_id, "Progress update", JSON_OBJECT("iteration", @my_counter), @my_progress, "RUNNING")';

In this example:

The first SQL statement DO SLEEP(5) simulates a time-consuming task. The second statement SELECT JSON_OBJECT("Task result", "Finished...") into @task_result 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. You can view the task status using the app_task_status routine.
The next few parameter values specify the application-managed user ID app_user_01, database to use demo_db, task type demo task type, and task name demo task name.
The JSON value JSON_OBJECT('sleep', 5, 'refresh_period', 1) specifies the additional metadata to include in the task_data field in the task status. This example stores different parameter values that were used during asynchronous task creation as additional task metadata, so that these values can be referred to at a later time. You can view the task status using the app_task_status routine.

The following JSON schema defines the structure of the additional metadata to include in task_data field in the task status:

{
  "type": "object",
  "properties": {
      "sleep": {
          "type": "integer"
      },
      "refresh_period": {
          "type": "integer"
      }
  }
}

The JSON following JSON schema defines the structure of the additional data to include in data field in the task logs:
```
{
  "type": "object",
  "properties": {
    "iteration": {
      "type": "integer"
    },
    "Task result": {
      "type": "string"
    },
    "connection_id": {
      "type": "integer"
    }
  },
  "additionalProperties": false,
  "oneOf": [
    { "required": ["iteration"] },
    { "required": ["Task result"] },
    { "required": ["connection_id"] }
  ]
}
```
This example specifies a strict schema for logging data. Any attempt to log a property that is not included in the specified schema would cause the asynchronous task to fail. Also, in this example, all specified properties are added to the task logs, one property per log. You can view the task logs using the app_task_logs routine.
The @progress_monitor_sql_statements session variable specifies the SQL statements to use for monitoring the task progress. The following progress monitoring statements are used in this example:
```
  SET @my_counter = coalesce(@my_counter, 0) + 1;
  SET @my_progress = @my_counter * 100 / 5;
  CALL mysql_tasks.add_task_log(@task_id, "Progress update", JSON_OBJECT("iteration", @my_counter), @my_progress, "RUNNING");
```
- The first two statements increase the value of a counter stored in the session variable @my_counter which is then used to calculate the progress percentage stored in the @my_progress session variable every second considering that the task takes 5 seconds to complete.
- The last statements in this example logs the progress of the task using the add_task_log routine.
  
  In this example wherein the task runs for 5 seconds, progress monitoring statements are used to track and log the task progress after every 1 second. However, progress tracking, data logging, and refresh period would vary depending on the duration and complexity of task you are running.
The refresh period is set to 1. Therefore, the specified progress monitoring statements run after every 1 second.
Finally, the task ID generated for the asynchronous task that is returned by the routine is stored in the session variable @task_id.

10.1.4.2 execute_prepared_stmt_from_app_async

Syntax

Syntax Example

See Also