User Functions

Actions are written using JavaScript, but user functions are used for JavaScript content that is used repeatedly or that requires preparation for calls such as SQL or REST.
User functions are created using the GUI, and the created functions can be called from actions.

User Function Types

Two types of user functions exist.

Function	Scope
Local Function	1. Only within an action 2. Cannot be called directly from another action.
Global Function	1. App 2. Available for other actions in the app.

Note

Use $global to call global functions.

JavaScript

JavaScript processing used repeatedly in actions can be registered as functions.

How to create

1. Click the "Add" button in the left panel of the Action Editor.
2. Select "Script" from the Function Type.
3. Enter a string of alphanumeric characters starting with any letter + underscore for the function name.
4. Click the "Save" button.
5. Select the function type.
6. Displays the JavaScript function editor.
7. Write the function process in the JavaScript editor.

Note

If asynchronous processing is called within a function, the function must be set to asynchronous.

Arguments

Arguments will be the only param object.

Name	Type	Add Arguments
param	object	not allowed

To get a value from arguments param in a JavaScript function, write the following in the function editor

const input1 = param.arg1;
const input2 = param.arg2;

return input1 + input2;

On the other hand, when calling a function, call it as follows

const obj = {} ; // Create empty object
obj.arg1 = 10;
obj.arg2 = 20;

myfunc(obj);

Note

It can also be generated using object literals.
const obj = {arg1: 10, arg2: 20};

Sample

Here is a sample of using a JavaScript function that adds two numbers in an action.

Step 1

Prepare three numeric fields and buttons that will be Arguments in the UI.

Component	Component ID	Component Label
Number Field	number_field_1	Number Field 1
Number Field	number_field_2	Number Field 2
Number Field	number_ans	Result Field
Button	button_calc	Calculate button

Step 2

Open the button_calc action editor and Create a JavaScript function named my_func.

[Contents of my_func]

const input1 = param.arg1;
const input2 = param.arg2;

return input1 + input2;

Step 3

Write a call to my_func on the action board and display the result in the calculated result field.

[Action Board Contents]

const obj = {};
obj.arg1 = $ui.number_field_1.value;
obj.arg2 = $ui.number_field_2.value;
$ui.number_ans.value = my_func(obj);

Step 4

Save the action and deploy the application.

Step 5

From the application, enter numeric values in Numeric Field 1, Numeric Field 2, and click the Calculate button. Verify that the addition result is displayed in the Calculation Result field.

SQL

Issue SQL to the database and retrieve the results. The SQL editor contains only SQL statements.

Note

• It is recommended that one statement be executed.
  When multiple statements are executed, the resulting object is indeterminate depending on the result of each statement.
• It is not required to specify the database name as a prefix to the database object in SQL.
  If a database name is specified, a connection is made to the specified database.
  If you do not specify a database name, it will connect to the database specified in "Default Database" under "Application Settings" - "Application Basics".
  Note that if no database name is specified and no default database is set, an error will occur when the action is executed.
• When comparing against a string generated in SQL, a mixCollation error may occur due to a difference in collation order.
  If the above error occurs, please specify [utf8mb4_0900_ai_ci] for the collation order of the generated strings.

How to create

1. Click the "Add" button in the left panel of the Action Editor.
2. Select "SQL" from the function types.
3. Enter a string of alphanumeric characters starting with any letter + underscore for the function name.
4. Click the "Save" button.
5. Displays the SQL function editor.
6. Write SQL in the SQL editor.

Arguments

Arguments will be the only param object.

Name	Type	Add Arguments
param	Object	not allowed

Note

Arguments are the same for all user functions.

Asynchronous functions and await operators

SQL functions are processed asynchronously. To wait until the value is returned, use the await operator during SQL function execution as follows.

Return value = await SQL function

await SQL function

The return value of the SQL function will be an object containing the following properties

Property	Type	Description
data	object array	Array of ROW data in SQL results, or results of executing a DML statement
sqlState	string	SQLSTATE value
errorCode	string	Error code
errorMessage	string	Error message

Note

sqlState, errorCode, and errorMessage have values only when an SQL error occurs.

About Transaction Control

Transactions are performed on an action-by-action basis, with commit and rollback implicit.
If multiple SQL functions are called in an action, they will be rolled back if any one of them raises an exception. Transactions are action units.

Note

Exception Handling

• If an exception is caught by a try-catch statement, rollback is not performed because the calling action does not supplement the exception unless throw is performed in the catch clause.

Statements that cannot be rolled back

• Data Definition Language (DDL) statements, such as database creation or deletion, and table or stored routine creation, deletion, or modification, will not be rolled back. Please design your transactions so that they do not contain these statements.

Sample

The following is an example of issuing a SQL SELECT to a table in an action and displaying the results as a JSON string in a textarea component.

Step 1

Prepare a textarea component that displays the results in the UI and a button that executes the action.

Component	Component ID	Component Label
textarea	text_area_1	Result JSON
button	button_sql	SELECT

Step 2

Create a table named t1 in a database named mydb with the following configuration.

Column name	Data type	M	D	fsp	PK	NN	UQ	AI
id	INT				True	True
name	VARCHAR	20

Store the data for testing from the Data tab of the column.

id	name
1	a
2	b

Step 3

Open the button_sqlaction editor and create an SQL function named sql1.

[Contents of sql1]

select * from mydb.t1;

Step 4

Write SQL function calls on the action board to display the results of data acquisition in a text area.

Note

The await operator must always be used when calling SQL functions.

[Action Board Contents]

const obj = await sql1();
$ui.text_area_1.value = JSON.stringify(obj.data);

Step 5

Save the action and deploy the application.

Step 6

Click the SELECT button from the application. Verify that the SQL SELECT results are displayed in JSON format in the Results JSON text area.

[{"id":1,"name":"a"},{"id":2,"name":"b"}]

REST

REST functions are functions for calling the REST API or Amazon API Gateway.

How to create

1. Click the "Add" button in the left panel of the Action Editor.
2. Select "REST" from the function type.
3. Enter a string of alphanumeric characters starting with any letter + underscore for the function name.
4. Click the "Save" button.
5. Displays the REST function editor.
6. Configure the REST editor to call the REST API.

Basic Settings

Parameters	Description
Function name	The name set when the function was created is displayed.
Arguments	Argument is the only param object, as are other user functions.
Method	Depending on the API to be called, choose from the following 1. GET 2. POST 3. PUT 4. PATCH 5. DELETE
URL	Fill in the REST API URL. You can embed parameter values at any point using the following format. ${param.property_name}
Request headers	Set the request header. Click the "Add" button to add a header name and value entry. Typical header names are available as options, but they can also be entered arbitrarily.
Request body	For the POST/PUT/PATCH/DELETE method, request body can be specified. The input format changes depending on the Conent-Type specified in the header.
Get response as binary	The return value of the response is a string, but for services that return binaries, check this box to obtain the return value as a Buffer object.

Note

• The maximum data size of the response cannot be changed, and a maximum of 4 MB is supported.
• However, if the Get response as binary checkbox is checked, the response can be obtained regardless of the size.

Request headers

Request headers are set from the [Headers] tab. The request header is provided as an option, but can also be entered directly.

Header name	Value
Accept	1. */* 2. application/* 3. audio/* 4. image/* 5. text/*
Accept-Charset	utf-8
Accept-Encoding	1. compress 2. deflate 3. gzip
Accept-Language	1. en-US 2. ja
Authorization
Cache-Control	1. max-age=3600 2. no-cache 3. no-store
Connection	1. close 2. keep-alive
Content-Length
Content-Type	1. application/json 2. application/octet-stream 3. application/x-www-form-urlencoded 4. application/xml 5. multipart/form-data 6. text/css 7. text/html 8. text/plain
Content-Length
Cookie	1. name=value 2. name=value; name2=value2; name3=value3
Date	Wed, 21 Oct 2015 07:28:00 GMT
Expect	100-continue
From	webmaster@example.org
Host
Proxy-Authorization
Referer	https://example.com/
User-Agent
X-Amz-Content-Sha256
X-Amz-Date
X-Amz-Security-Token
X-API-Key

Note

X-API-Key is an API key for AWS API Gateway.

Request body

The request body is set from the [Body] tab. The input format changes depending on the Content-Type specification in the request header.

Content-Type of the request header	Input Format	Input details
application/json	textarea	Text
application/octet-stream	text field	File path
application/x-www-form-urlencoded	"Add" button Text field (name) Text field (value)	Parameter name Parameter value
application/xml	textarea	text
multipart/form-data	"Add Text Part" button "Add File Part" button Text field (name) Text field (value)	Parameter name Parameter value
text/css	textarea	text
text/html	textarea	text
text/plain	textarea	text

Asynchronous functions and await operators

REST functions are processed asynchronously. To wait for the value to return, use the await operator during REST function execution as follows

Return value = await REST function

Return value

The return value of the REST function will be an object containing the following properties.

Property	Type	Description
status	object	HTTP Status Object Property Type code number text string
headers	object	Response header
body	string | Buffer	Response
errorMessage	string	Eror message

Sample

The following is an example of calling a GET type REST API (one parameter) in an action and displaying the result as a JSON string in a textarea component.

Note

The REST API used in the sample returns a string consisting of the string entered as a query parameter followed by "Hello".

Parameter	JSON string
URL	https://bl2dqouo8g.execute-api.ap-northeast-1.amazonaws.com/dev/res1?name=string
API key	bcVrf52Wvs3jAKMFYOK596hlLsTtmczH5tTCjQxI

Step 1

Prepare text fields, text areas, and buttons in the UI.

Component	Component ID	Component Label
Text field	text_field_name	Name
textarea	text_area_response	Response
Button	button_rest	REST API

Step 2

Open the button_rest action editor and Create a REST function named my_rest.

[Basic settings for my_rest]

Function name	my_rest
Arguments	param
Method	GET
URL	https://bl2dqouo8g.execute-api.ap-northeast-1.amazonaws.com/dev/res1?name=${param.name}
Request header	Name- X-API-Key Value-bcVrf52Wvs3jAKMFYOK596hlLsTtmczH5tTCjQxI
Binary acquisition	Unchecked

Step 3

Write a call to the REST function on the action board and display the response in a text area.

Note

The await operator must always be used when calling REST functions.

[Action Board Contents]

const encoded_name = encodeURIComponent($ui.text_field_name.value);
const obj = { name: encoded_name };
const ret = await my_rest(obj);
$ui.text_area_response.value = ret.body;

Step 4

Save the action and deploy the application.

Step 5

From the application, enter any string in the “Name” text field and click the "REST API" button. Confirm that the "Hello input string" appears in the [Response] text area.

(Ex:) Hello World!

Sample of calling asynchronous functions with synchronous JavaScript functions

When calling asynchronous functions (SQL functions, REST functions, built-in functions such as $fn.getFile, etc.) in JavaScript functions, they must be returned as Promise objects as shown in the sample. In the following executeScript function, examples of executing REST functions, SQL functions, JavaScript functions, and built-in functions and returning the results are described.
The action board contains an example of setting the result returned from the executeScript function to the value of each table component.

Action Board

{
  const { books, tasks, plans, files } = await executeScript();
}

// Books
$ui.books.value = books.map((book) => ({
  bookTitle: book?.volumeInfo?.title,
  bookPublisher: book?.volumeInfo?.publisher,
  bookPublishedDate: book?.volumeInfo?.publishedDate,
}));

// Tasks
$ui.tasks.value = tasks;

// Plans
$ui.plans.value = plans.map((plan) => ({
  planId: plan.id,
  planName: plan.name,
}));

// Files
$ui.files.value = files.map((fileName) => ({ fileName }));

JavaScript Function: executeScript(Function type: Synchronous)

const execute = async () => {
  // REST function call
  const sendResult = await sendGetBooksRequest();
  const body = JSON.parse(sendResult.body);
  const books = body.items;
  // SQL Function Call
  const selectResult = await selectTaskRecords();
  const tasks = selectResult.data;
  // JavaScript Function Call
  const plans = getPlans();
  // Built-in function call
  const files = await $fn.getFileNames("/");
  return {
    books,
    tasks,
    plans,
    files,
  };
};
// Returns a Promise object
return execute();

JavaScript Function: getPlans(Function type: Synchronous)

return [
  {
    name: "Plan1",
    id: "p001",
  },
  {
    name: "Plan2",
    id: "p002",
  },
  {
    name: "Plan3",
    id: "p003",
  },
];