Procedures

The Procedures section contains stored procedure configurations. Their settings define the connector stored procedures - auxiliary connector objects that allow you to implement specific operations and execute endpoints that cannot be represented as classic connector table objects. For example, the Ping procedure checks whether the connection is valid, or the SendMessage procedure, which sends a message, etc.

You can call a stored procedure, for example, as a text of the command:

CALL <Procedure Name> (:<Parameter1>, :<Parameter2>, :<Parameter3>, ..., :<ParameterN>)

in the ExecuteCommand action in a Target component of a Data Flow or in Query. Connector stored procedures cannot participate in SELECT or CRUD requests.

Stored procedure configuration includes the following settings.

Here is an example of a stored procedure configuration. This is the InitiateUserInvite stored procedure from the TeamworkCRM connector. It initiates an invitation for a user.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
{
    "Name": "InitiateUserInvite",
    "Url": "/users/actions/initiateInvite.json",
    "Method": "POST",
    "BodyPattern": "{ \"invite\" : <$body$> }",
    "BodyType": "Json",
    "Parameters": [
        {
            "Name": "email",
            "APIPath": "email",
            "DbType": "String",
            "Required": true,
            "ParameterType": "BodyParameter"
        },
        {
            "Name": "firstName",
            "APIPath": "firstName",
            "DbType": "String",
            "Required": true,
            "ParameterType": "BodyParameter"
        },
        {
            "Name": "lastName",
            "APIPath": "lastName",
            "DbType": "String",
            "Required": true,
            "ParameterType": "BodyParameter"
        },
        {
            "Name": "title",
            "APIPath": "title",
            "DbType": "String",
            "Required": true,
            "ParameterType": "BodyParameter"
        }
    ],
    "ResultPath": ""
}

General Settings

Name

Stored procedure name.

Url

The stored procedure endpoint URL. It can be an absolute or relative URL. If the URL value starts with the ‘/’ character, Skyvia treats it as a relative URL and appends it to the BaseUrl. Otherwise, it treats it as an absolute URL.

Method

A web request method, for example, GET, POST, PUT. This setting is required.

BodyType

The type of web request body.

Valid values are:

ResultPath

A path to the response field containing the result of the procedure endpoint execution. See ProviderConfiguration for more information.

BodyPattern

Request body format. See ProviderConfiguration for more information.

Headers

Additional HTTP request headers that are sent every time you call the procedure. Specify Headers as a JSON array of objects with the Key and Value properties. These headers are merged with the respective constant headers, specified in ProviderConfiguration Headers section.

PagingStrategy

Allows getting records by pages, if the source supports PagingStrategy for stored procedure endpoint. Add this section if paging for this stored procedure differs from the paging, configured in the ProviderConfiguration section. See ProviderConfiguration for more information.

ErrorHandling

Allows you to determine the procedure behavior in case of errors. Add this section if this behavior for this stored procedure must differ from the behavior, configured in the ProviderConfiguration section. See ProviderConfiguration for more information.

ConstantParameters

Allows you to set constant web request parameters, sent with every procedure call. These constant parameters are merged with the respective constant parameters, specified in ProviderConfiguration ConstantParameters section.

Parameters

This section contains stored procedure parameter configurations for every stored procedure parameter. These configurations have the following settings:

Name

Stored procedure parameter name.

ParameterType

Specifies how the parameter value is passed in the web request. The default value is UrlParameter. Valid values:

APIPath

The name of the parameter in the data source API. If the parameter is nested in the API, the APIPath should have the following format: parent object name.parameter name. Nesting level is not limited.

If the dot character is a part of the name in JSON, and not the separator in the hierarchy, then you need to use quotation with the {} characters.

DbType

Specifies the parameter data type. The default value is String. You can find the supported types in the Objects topic.

Special JsonArray and JsonObject types mean that the parameter value is an array or a JSON object correspondingly.

SubType

Specifies the data type for complex JSON object or JSON array parameters (having DbType set to JsonArray or JsonObject).

For JsonObject parameters, it can be a name or complex type. For JsonArray parameters, it can be the name of a complex type or the name of a simple data type, and it specifies the type of array elements.

If the SubType is not specified, the JSON parameter content is passed as is without further processing and mapping to a specific sub type fields structure.

Length

Number of characters for text type fields. Default values are: 38 for Guid, int.Maxvalue for JSON Object or JSON Array, 255 for other text types.

Precision

Max number of significant digits for a numeric field.

Scale

Number of digits after comma for numeric fields.

TimeStampStoreMode

This settings specifies the timestamp format for the procedure. If you omit it, the corresponding global setting from the ProviderConfiguration section is used. See ProviderConfiguration for more information.

DateTimeFormat

This setting specifies how to process the source DateTime data for the procedure. If you omit it, the corresponding global setting from the ProviderConfiguration section is used. See ProviderConfiguration for more information.

DateFormat

This setting specifies how to process the source Date data for the procedure. If you omit it, the corresponding global setting from the ProviderConfiguration section is used. See ProviderConfiguration for more information.

TimeFormat

This setting specifies how to process the source Time data for the procedure. If you omit it, the corresponding global setting from the ProviderConfiguration section is used. See ProviderConfiguration for more information.

Nullable

Defines if the field can accept null values. The default value is true.

Optional

Use this setting to define whether the parameter is optional or required. Accepts the true and false values.

DefaultValue

This setting sets the default value for the parameter. It is useful when Optional = true.

NullAs

This setting specifies a value the source uses as an empty value. For example, some source uses "name": "" instead of "name": null. In this case, you specify "NullAs": "".

Enum

This setting specifies if the parameter has a set of specific valid values. Enum accepts true of false values.

Use this setting always together with the EnumValues setting.

MultiEnum

This setting specifies if the field is an array of specific valid values. MultiEnum accepts true of false values.

Use this setting always together with the EnumValues setting.

EnumValues

This setting defines the set of valid values for the field if Enum or MultiEnum is true.
It is represented as key-value pairs: [{Name1:value1}, {Name2:value2}]. Name, in this case, is a list item display name available to the connector end user. Value is an internal API value of the valid enum list item.

StrictEnumValues

Determines whether you can assign only values from the EnumValues list to this field, and custom values are not allowed. The default value is False.

Results

This section contains column configurations with settings defining the fields of the result set returned by the stored procedure. This section is not needed if the stored procedure does not need to return a resultset.

Name

A field name displayed to user after the procedure execution, for example {"Name":"Amount",...}.

APIPath

The JSON path to the result field in the web response. If the result field is nested, then you specify the parent and child fields in the format: parent setting name.setting name. Nesting level is unlimited.

JPath

JSON path to a property with the field value in the response. For fields with special characters in their names, you need to use escaping. For example, JSON path for a field with a dot in the name LocationName.ID, looks like ['LocationName.ID'].

DbType

Specifies the field data type. Default data type is String.

{"Name":"FirstName", APIPath: "first_name", "DbType":"String" ...}

You can find the supported types in the Objects topic.

SubType{: #subtype2}

Specifies the data type for complex JSON object or JSON array result fields (having DbType set to JsonArray or JsonObject).

For JsonObject result fields, it can be a name or complex type. For JsonArray result fields, it can be the name of a complex type or the name of a simple data type, and it specifies the type of array elements.

If the SubType is not specified, the JSON field content is passed as is without further processing and mapping to a specific sub type fields structure.

Length

Number of characters for text type fields. Default values are: 38 for Guid, int.Maxvalue for JSON Object or JSON Array, 255 for other text types.

Precision

Max number of significant digits for a numeric field.

Scale

Number of digits after comma for numeric fields.

TimeStampStoreMode

Use this setting to redefine the TimeStampStoreMode from the ProviderConfiguration section.

DateTimeFormat

This setting specifies how to process the source DateTime data. Default value is yyyy-MM-ddTHH:mm:ssZ. See ProviderConfiguration for more information.

DateFormat

This setting specifies how to process the source Date data. Default value is yyyy-MM-dd. See ProviderConfiguration for more information.

TimeFormat

This setting specifies how to process the source Time data. Default value is HH:mm:ssZ. See ProviderConfiguration for more information.

Nullable

Boolean setting which specifies if the field can accept null values. Default is false.

Enum

This setting specifies if the parameter has a set of specific valid values. Enum is boolean and accepts true of false values. Use this setting always together with EnumValues setting.

MultiEnum

This setting specifies if the field is an array of specific valid values. MultiEnum is boolean and accepts True of False values. Use this setting always together with the EnumValues setting.

EnumValues

This setting defines the set of specific valid values for the field if Enum or MultiEnum is true.
It is represented as key-value pairs: [{Name1:value1}, {Name2:value2}]. Name, in this case, is a list item display name available to the connector end user. Value is an internal API value of the valid enum list item.

Expression

This setting allows creating virtual calculated fields in the data set, returned by a stored procedure. When building an expression, use the API names of the response fields.

You must specify API names without parentheses or quotes. If the expression uses the name of the nested field, separate the names with the dot (for example, shipping.country).

You can find the Expression syntax and the list of supported functions and operators in our Expression Syntax documentation.