Stored Procedures

The platform6.storedprocedures service allows to edit and execute database specific SQL queries.

Read the user guide of the Stored Procedures service to discover its operating mode.

Group by type

Group the stored procedures by type.

Parameters

Headers of the request

Header’s key	Description	Value
platform6.request.action	The action to perform (required)	group.type
platform6.request.user	The email address of the user sending the message

Headers of the response

Header’s key	Description
platform6.response.value	The stored procedures grouped by type

Example

def cm = [ headers: [ 'platform6.request.action': 'group.type' ] ]

print p6.service.request('platform6.storedprocedures', cm).headers['platform6.response.value']

The response will be:

{
    "UserDefined": [
        {
            "name": "invoices_failed_datasource_count_amount_per_period",
            "appKey": "",
            "description": { "EN": "invoices_failed_datasource_count_amount_per_period" },
            "enabled": false,
            "contentMode": "NONE",
            "lastModifiedBy": "admin@amalto.com",
            "lastModifiedDate": 1537348332624,
            "revisionId": "c31c985f62f16da90c13fc3aee08c7ac",
            "type": "UserDefined",
            "userQueryValue": "SELECT \n creationDate,\n SUM(CASE WHEN status='Pending' THEN invoiceCount ELSE 0 END) AS pendingCount,\n SUM(CASE WHEN status='Invalid' THEN invoiceCount ELSE 0 END) AS invalidCount,\n SUM(CASE WHEN status='Rejected' THEN invoiceCount ELSE 0 END) AS rejectedCount,\n SUM(invoiceAmountUSD) AS invoiceAmount\n\nFROM ds_invoices_period_usd\n\nWHERE (status='Invalid' OR status='Rejected' OR status='Pending') \n AND creationDate >= '${minDateValue}' \n AND creationDate <= '${maxDateValue}'\n\nGROUP BY creationDate\n\nORDER BY creationDate",
            "targetDataSourceName": null,
            "definedParameters": {
                "maxDateValue": "java.lang.String",
                "minDateValue": "java.lang.String"
            },
            "publik": true,
            "cachingOverride": false,
            "appendTargetDataSource": false
        },
        {
            "name": "invoices_failed_ratio_datasource_count_per_period",
            "appKey": "",
            "description": { "EN": "invoices_failed_ratio_datasource_count_per_period" },
            "enabled": false,
            "contentMode": "NONE",
            "lastModifiedBy": "admin@amalto.com",
            "lastModifiedDate": 1537348332668,
            "revisionId": "7e6b9211dbe23a5b0575ae505c3b36fb",
            "type": "UserDefined",
            "userQueryValue": "SELECT \n created.creationDate, \n round(failed.rejectedCount / created.invoicecount * 100, 2) AS rejectedRatio,\n round(failed.invalidCount / created.invoicecount * 100, 2) AS invalidRatio\n\nFROM ds_invoices_processed_period AS created\n\nINNER JOIN ds_invoices_failed_period AS failed \n ON created.creationDate = failed.creationDate\n\nWHERE created.creationDate >= '${minDateValue}' \n AND created.creationDate <= '${maxDateValue}'\n\nORDER By created.creationDate",
            "targetDataSourceName": null,
            "definedParameters": {
                "maxDateValue": "java.lang.String",
                "minDateValue": "java.lang.String"
            },
            "publik": true,
            "cachingOverride": false,
            "appendTargetDataSource": false
        }
    ]
}

Execute a stored procedure

This resource executes a specific stored procedure.

It can also be done with the Groovy DSL sproc.execute.

Parameters

Headers of the request

Header’s key	Description	Value
platform6.request.action	The action to perform (required)	execute
id	The identifier of the stored procedure (required)
parameters	The execution arguments
platform6.request.user	The email address of the user sending the message

The author of the stored procedure’s execution will be the user who sent the common message.

Headers of the response

Header’s key	Description
platform6.response.value	The result of the execution

Example

This example uses a stored procedure, named list_service_items, with the following query:

SELECT * FROM p6core.serviceconfig WHERE id3 = '${item_name}';

Here is an example of a script executing a stored procedure:

import groovy.json.JsonOutput

def cm = [
    headers: [
        'platform6.request.action': 'execute',
        'id': 'list_service_items',
        'parameters': JsonOutput.toJson([item_name: 'frames'])
    ]
]

print p6.service.request('platform6.storedprocedures', cm).headers['platform6.response.value']

The output will be:

[
    [
        "Chevron Like Test",
        "",
        "frames",
        "{\"url\": \"https://dev.portal.amalto.com/static/applications/master/pages/template.html\", \"name\": \"Chevron Like Test\", \"appKey\": \"\", \"enabled\": false, \"revisionId\": \"3de7c0696314d164a3a2c9b08c6bc1f1\", \"contentMode\": \"NONE\", \"description\": {\"EN\": \"Chevron Like Test\"}, \"lastModifiedBy\": \"roxane.mace@amalto.com\", \"lastModifiedDate\": 1537281413549}",
        null,
        "1537281413565"
    ],
    [
        "Home page",
        "",
        "frames",
        "{\"url\": \"https://dev.portal.amalto.com/static/applications/master/pages/template.html\", \"name\": \"Home page\", \"appKey\": \"\", \"enabled\": false, \"revisionId\": \"b78cdd1f42b91f0977c3c9dcd784f917\", \"contentMode\": \"NONE\", \"description\": {\"EN\": \"Home page\", \"FR\": \"Page d'accueil\"}, \"lastModifiedBy\": \"roxane.mace@amalto.com\", \"lastModifiedDate\": 1537281413610}",
        null,
        "1537281413613"
    ]
]

If the stored procedure doesn’t exist, it will throw an error:

{
    "message" : "Unexpected error executing the stored procedure! The stored procedure 'list_service_items' is not found.",
    "stackTrace" : [
        "io.platform6.common.util.P6Exception: Unexpected error executing the stored procedure! The stored procedure 'list_service_items' is not found.",
        "    at com.amalto.service.storedprocedures.StoredProceduresActions.executeStoredProcedure(StoredProceduresActions.java:126)",
        "    at com.amalto.service.storedprocedures.StoredProceduresService.notifyRequestMessage(StoredProceduresService.java:170)",
        "    at com.amalto.b2box.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at com.amalto.b2box.core.impl.platform.messagebus.BusQueueController$ServiceQueueRunner.run(BusQueueController.java:183)",
        "    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)",
        "    at java.util.concurrent.FutureTask.run(FutureTask.java:266)",
        "    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)",
        "    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)",
        "    at java.lang.Thread.run(Thread.java:748)"
    ]
}

If there are missing arguments, it will throw an error:

{
    "message" : "Unexpected error executing the stored procedure! Call to procedure 'list_service_items' for entity 'UserDefined' missing mandatory parameter 'item_name'",
    "stackTrace" : [
        "io.platform6.common.util.P6Exception: Unexpected error executing the stored procedure! Call to procedure 'list_service_items' for entity 'UserDefined' missing mandatory parameter 'item_name'",
        "    at com.amalto.service.storedprocedures.StoredProceduresActions.executeStoredProcedure(StoredProceduresActions.java:126)",
        "    at com.amalto.service.storedprocedures.StoredProceduresService.notifyRequestMessage(StoredProceduresService.java:170)",
        "    at com.amalto.b2box.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at com.amalto.b2box.core.impl.platform.messagebus.BusQueueController$ServiceQueueRunner.run(BusQueueController.java:183)",
        "    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)",
        "    at java.util.concurrent.FutureTask.run(FutureTask.java:266)",
        "    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)",
        "    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)",
        "    at java.lang.Thread.run(Thread.java:748)"
    ]
}

Build a datasource

Execute a query which will create a new table in the database.

It can also be done with the Groovy DSL sproc.buildDataSource.

Parameters

Headers of the request

Header’s key	Description
platform6.request.action	The action to perform (required, expected value execute)
id	The identifier of the stored procedure (required)
dataSourceName	The name of the data source, spaces are not permitted and the maximum length is 32 (required)
appendTargetDataSource	Whether you add the record in the table if it already exists (default to false)
parameters	The execution arguments
platform6.request.user	The email address of the user sending the message

The author of the stored procedure’s execution will be the user who sent the common message.

Warning

Only stored procedures containing SELECT queries can be used to build a datasource!

Headers of the response

Header’s key	Description
platform6.response.value	The length of the execution result

Example

This example uses a stored procedure, named initialize_datasource_exchanges_rate, with the following query:

SELECT
    CAST('' as varchar(10)) AS currencyFrom,
    CAST('' as varchar(10)) AS currencyTo,
    CAST (0 as numeric) AS rate;

Here is an example of a script building a data source:

import groovy.json.JsonOutput

def cm = [
    headers: [
        'platform6.request.action': 'execute',
        'id': 'initialize_datasource_exchanges_rate',
        'dataSourceName': 'exchanges_rate_test'
    ]
]

print p6.service.request('platform6.storedprocedures', cm).headers['platform6.response.value']

The output will be:

[["1"]]

If the stored procedure doesn’t exist, it will throw an error:

{
    "message" : "Unexpected error getting the stored procedure! The stored procedure 'initialize_datasource_exchanges_rate' is not found.",
    "stackTrace" : [
        "io.platform6.common.util.P6Exception: Unexpected error getting the stored procedure! The stored procedure 'initialize_datasource_exchanges_rate' is not found.",
        "    at com.amalto.service.storedprocedures.StoredProceduresActions.getStoredProcedure(StoredProceduresActions.java:80)",
        "    at com.amalto.service.storedprocedures.StoredProceduresActions.buildDatasource(StoredProceduresActions.java:211)",
        "    at com.amalto.service.storedprocedures.StoredProceduresService.notifyRequestMessage(StoredProceduresService.java:145)",
        "    at com.amalto.b2box.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at com.amalto.b2box.core.impl.platform.messagebus.BusQueueController$ServiceQueueRunner.run(BusQueueController.java:183)",
        "    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)",
        "    at java.util.concurrent.FutureTask.run(FutureTask.java:266)",
        "    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)",
        "    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)",
        "    at java.lang.Thread.run(Thread.java:748)"
    ]
}