Advanced Backend Features

5 Advanced Backend Features

The DDS backend service supports many SQL query commands. Some of these commands can be added to a query using the components available in the DDS UI, while others must be added by manually typing them into the query itself. Once that is done, the query can then be run through the DDS UI, or a browser, or a cURL commands to get the results.

Follow the instructions in the "Query Builder" section within the "Designer Tab Guide" section of the "User Interface" chapter for instructions on setting up a basic query in the DDS UI. Once those steps have been followed, a query is generated in the GET box. The query can be edited to add the following statements individually or in a combination as required.

Note:

Please maintain the formatting of the GET request, making sure all the special characters (like the separating ampersand &) are in place, a lack of which may cause unexpected errors.

Note:

However, before any of the following commands can be run we need an access_token, which is mandatory when using applications using OAuth2.0 authentication. The process to create an OAuth2.0 application and getting the access token is discussed in the chapter Security Setup.

LIKE Statement

The LIKE statement can be added separately to the query request to view only the results that are similar to the LIKE value. A simple GET request and result with LIKE statement can be like the following:

http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=<COULMN_NAME> LIKE '<data_to_search>'&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=<COULMN_NAME>%20LIKE%20'<data_to_search>'&offset=0&pageLimit=25"

IN Statement

The IN statement can be added separately to the query request to only view results containing one of a set of possible values. A simple GET request and result with IN statement can be like the following:

http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=<COULMN_NAME> LIKE '<data_to_search>'&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=<COULMN_NAME>%20IN%20('<value1>','<value2>','<value3>', …)&offset=0&pageLimit=25" -o filename.txt

Note:

The IN Statement supports searching using comma-separated values in parentheses. Sub-queries are NOT supported by the DDS design.

Comparative Operators

Comparative operators (like < and >) can be used to filter data for a particular range. A GET request and result with these operators can be like the following:

http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=COULMN_NAME<'data_to_search'&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=COULMN_NAME<'data_to_search'&offset=0&pageLimit=25" -o filename.txt

Filtering DATE Column

Date columns can be filtered using the 'DD-MMM-YYYY' date format. Additionally, SYSDATE±N can also be used to query date columns.

Example with the SYSDATE keyword:

http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=CREATION_DATE<SYSDATE-1&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=CREATION_DATE<SYSDATE-1&offset=0&pageLimit=25" -o file-name.txt

Example with 'DD-MMM-YYYY' date format:

http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=CREATION_DATE>'01-JAN-1976'&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=CREATION_DATE>'01-JAN-1976'&offset=0&pageLimit=25" -o file-name.txt

Combinations of OR, AND & BETWEEN Statements

The conditional statements OR, AND & BETWEEN can be used in combination with each other using parentheses to further filter results based on required conditions. The Filter DDS row in the UI can be used to add a single conditional statement, upon which more conditions can be added manually by typing them in the GET box. A simple GET request with result can look like this:

http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=(DATA_TYPE LIKE 'INVOICE_HEADER' or DATA_TYPE LIKE 'INVOICE_DETAIL') and FILE_ID='184' and CREATION_DATE BETWEEN '03-MAY-2021' AND '04-MAY-2021'&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&rowFilter=(DATA_TYPE%20LIKE%20'INVOICE_HEADER'%20or%20DATA_TYPE%20LIKE%20'INVOICE_DETAIL')%20and%20FILE_ID='184'%20and%20CREATION_DATE%20BETWEEN%20'03-MAY-2021'%20AND%20'04-MAY-2021'&offset=0&pageLimit=25" -o filename.txt

Viewing Clob Data

Sometimes the data in a table field can of the type CLOB and have large data in it. DDS supports these fields and can display the data in the UI. However, these fields can also be viewed through a cURL command, and saving the output to a file.

Note:

Ensure that the required DDS application username/password is supplied.

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<server_host>:<server_port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&columnFilter=MESSAGE_DATA&offset=0&pageLimit=25" -o file_name.txt

The above queries can also be combined with the functions listed above in this chapter to get more filtered results.

Viewing Query Response as JSON Data

The DDS backend supports sending data as either JSON or XML.

To view the data as XML, run the query in a curl command by adding the -H switch set like the following:

curl "http://<dds_host>:<dds_port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&offset=0&pageLimit=25" -H "Accept:application/xml" -u <dds_user>:<dds_pass> -o file_name.txt

JSON is the default response format. To return a JSON object, do not include the -H switch in the cURL command.

Or, you can set the cURL command with the -H switch setting Accept to application/json in the header. The cURL with output is as follows:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<dds_port>/dynamic-data-service-web/resources/dds/<selected_schema>/data?fromTables=<selected_table>&offset=0&pageLimit=25" -H "Accept:application/json" -o file_name.txt

Note:

Ensure that the required DDS application username/password is supplied.

JOINing Two or More Tables

DDS has the ability to join two or more tables. This is done using the JOIN element in the Query Builder Tab under the Designer section.

Use the check box to enable table joins. Once enabled, click inside the visible text field to view a list of tables. Select one or more tables as required.

Note:

The row filter must be added to this, which can either be done by using the Row Filter in the UI or adding your own rowFilter by typing the required conditions in the GET query box. You must also ensure that all required columns are selected in the column filter, otherwise an error will display when you try to run the query.

A sample GET query with result is as follows:

http://<dds_host>:<dds_port>/dynamic-data-service-web/resources/dds/<SELECTED_TABLE>/data?fromTables=TABLE1 t0,TABLE2 t1,TABLE3 t2&columnFilter=t0.COLUMN1,t1.COLUMN1,t2.COLUMN1,t0.COLUMN2,t1.COLUMN2,t2.COLUMN2,t0.COLUMN3,t1.COLUMN3,t2.COLUMN3&rowFilter=t0.COLUMN1=t1.COLUMN1 and t1.COLUMN1=t2.COLUMN1 and t1.COLUMN2=t2.COLUMN3 and t1.COLUMN3 LIKE '1'&offset=0&pageLimit=25

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET  "http://<dds_host>:<dds_port>/dynamic-data-service-web/resources/dds/<SELECTED_TABLE>/data?fromTables=TABLE1%20t0,TABLE2%20t1,TABLE3%20t2&columnFilter=t0.COLUMN1,t1.COLUMN1,t2.COLUMN1,t0.COLUMN2,t1.COLUMN2,t2.COLUMN2,t0.COLUMN3,t1.COLUMN3,t2.COLUMN3&rowFilter=t0.COLUMN1=t1.COLUMN1%20and%20t1.COLUMN1=t2.COLUMN1%20and%20t1.COLUMN2=t2.COLUMN3%20and%20t1.COLUMN3%20LIKE%20'1'&offset=0&pageLimit=25" -o filename.txt

Count of Records

DDS has the ability to fetch the count of records for a particular query. This can be used to fetch count of all records or a limited set of records based on conditions and parameters as required.

A sample GET query with result is as follows:

http://<dds_host>:<dds_port>/dynamic-data-service-web/resources/dds/<SELECTED_SCHEMA>/data?fromTables=TABLE1&offset=0&pageLimit=25

Note:

The count query that is actually being run is slightly different to the one displayed in GET box. The correct one is given below in the curl command equivalent.

A curl equivalent of the above example:

Curl -i -H "Authorization: Bearer $AccessToken" -H "Content-Type: application/json;charset=UTF-8" --request GET "http://<dds_host>:<dds_port>/dynamic-data-service-web/resources/dds/<SELECTED_SCHEMA>/data/count?fromTables=TABLE1&rowFilter=<CONDITIONS_AS_REQUIRED>" -o filename.txt