Stored Procedures

Service Id: platform6.storedprocedures

1. Get List of Stored Procedures Service Items Grouped By Type¶

Header key	Description	Value
`platform6.request.action`	The action to perform (required)	`group.type`

A valid response will be:

Header key	Description
`platform6.response.status`	OK
`platform6.response.value`	Map>: 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
        }
    ]
}

2. Execute a Stored Procedures Given Its Id¶

Header 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

Note

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

A valid response will be:

Header key	Description
`platform6.response.status`	OK
`platform6.response.value`	JSON List>: The result of the execution
`columns`	JSON List: The column names

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 io.platform6.core.service.storedprocedures.StoredProceduresActions.executeStoredProcedure(StoredProceduresActions.java:126)",
        "    at io.platform6.core.service.storedprocedures.StoredProceduresService.notifyRequestMessage(StoredProceduresService.java:170)",
        "    at io.platform6.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at io.platform6.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 io.platform6.core.service.storedprocedures.StoredProceduresActions.executeStoredProcedure(StoredProceduresActions.java:126)",
        "    at io.platform6.core.service.storedprocedures.StoredProceduresService.notifyRequestMessage(StoredProceduresService.java:170)",
        "    at io.platform6.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at io.platform6.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)"
    ]
}

2. Builds or refreshes a datasource¶

Header key	Description	Value
`platform6.request.action`	The action to perform (required)	`build.datasource`
`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

Note

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

Warning

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

A valid response will be:

Header key	Description
`platform6.response.status`	OK
`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 io.platform6.core.service.storedprocedures.StoredProceduresActions.getStoredProcedure(StoredProceduresActions.java:80)",
        "    at io.platform6.core.service.storedprocedures.StoredProceduresActions.buildDatasource(StoredProceduresActions.java:211)",
        "    at io.platform6.core.service.storedprocedures.StoredProceduresService.notifyRequestMessage(StoredProceduresService.java:145)",
        "    at io.platform6.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at io.platform6.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)"
    ]
}