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 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)"
    ]
}

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 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)"
    ]
}