Publishing APIs That Return All When the Parameter Value Is Empty

You want to publish an API that ignores the query condition corresponding to a parameter if you do not pass a specified value through the parameter. This means that to query all data, do not pass the corresponding values through the parameter; to query other non-empty data, pass the values through the parameter.

This document uses the SORDER table as an example to illustrate how to publish the table data as an API that enables fetching all data when no value is passed through the parameter area and fetching other non-empty data when a specified value is passed through the parameter area.

Creating an API

Click Data Service, choose New > API Service, enter the name of the API service, and click OK, as shown in the following figure.

Configuring API Information

Service Content (Data Query)

The following figure shows the setting page.

SELECT *FROM `demotest`.`SORDER` WHERE 1 = 1 AND CASE WHEN '$area' != '' THEN OWNER_REGION = '$area' ELSE 1 = 1 END

The following table describes the setting items.

For details about each setting item, see Overview of API Release.

Setting Item	Description
SQL Statement	1. Set the parameter area in the SQL statement. After publishing this API, you can use the parameter to query data in the SORDER table. 2. This example uses MySQL syntax. Adjust the statements accordingly if you use other databases. 3. If the parameter in the SQL statement is enclosed in single quotes ('') (as shown in this example), the parameter value does not need to be enclosed in single quotes ('') when you call the API. Note: In this case, if you call the API, the SQL statement that queries all data will substitute the default empty value: SELECT * FROM demotest.SORDER WHERE 1 = 1 AND CASE WHEN '' != '' THEN OWNER_REGION = '' ELSE 1 = 1 END If the default value is set to NULL, the SQL statement will substitute the default value NULL: SELECT * FROM demotest.SORDER WHERE 1 = 1 AND CASE WHEN 'NULL' != '' THEN OWNER_REGION = 'NULL' ELSE 1 = 1 END The execution result depends on the reading behavior of the database. If the parameter in SQL statements is not enclosed in single quotes (''), that is:  SELECT * FROM demotest. SORDER WHERE 1=1 AND CASE WHEN ${area} != '' THEN OWNER_REGION=${area} ELSE 1=1 END Then the parameter value should be enclosed in single quotes ('') when you call the API. 4. For FineDataLink of versions before V4.0.28: To address SQL injection prevention issues, FineDataLink adds single quotes ('') in escape characters in SQL Injection Prevention by default. This means that parameter values are not allowed to be enclosed in single quotes ('') in SQL statements by default. You need to manually enclose default parameter values in single quotes ('') during query preview. Therefore, if you need to enclose parameters in single quotes ('') in the SQL statement, you need to disable Escape Character in SQL Injection Prevention.
Pagination Query	Disable Pagination Query. The API to be generated will return data without pagination after being called.
Mandatory	In this example, disable Mandatory for the parameter area since you want to call the API without referencing parameters and fetch all data by default.

API Configuration

1. Select POST as Request Method and set the path to Doc_489. Custom path is supported.

Add an App. If no App has been created, you can skip this step and bind the API to an App in App List after completing the API creation. For details, see Binding an API to an Application. The binding step is mandatory; otherwise, the API cannot be called later.

Set Request Body Format to application/json, as shown in the following figure.

For details about each setting item, see Overview of API Release.

2. Click Test Call in the lower right corner to debug the API. The parameter value of area is empty, and all data is returned, as shown in the following figure.

Click Save and Activate.

Subsequent Operations

1. If you do not bind the API to a specified App when publishing the API, you can still manage the published API by binding it to an App in App List to ensure that the API can be called.

2. For details about using published APIs, see Using APIs Released in Data Service (GET Request).

The parameter value is empty, that is, the corresponding value of the parameter area is not passed. All data is fetched, as shown in the following figure.

For details about managing published APIs, see Data Service O&M.