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.

Setting Description
Name The name of the stored procedure
Path Path to the results of the GraphQL query
Headers HTTP request headers
PagingStrategy Stored procedure result paging strategy settings
ErrorHandling Error processing settings
RowErrorHandling Error processing settings
ConstantParameters Parameters with constant values that are sent with every request.
Parameters The stored procedure parameter settings
Results The settings for stored procedure result

Here is an example of a stored procedure configuration. This is the GetArticleById stored procedure from the Shopify 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
39
40
41
42
43
44
45
46
"Procedures": [
      {
        "Name": "GetArticleById",
        "Path": "query.article",
        "Parameters": [
          {
            "Name": "Id",
            "Argument": "id",
            "DbType": "String",
            "BasePathPosition": 2
          }
        ],
        "Results": [
          {
            "Name": "Id",
            "DbType": "String",
            "Path": "id"
          },
          {
            "Name": "Title",
            "DbType": "String",
            "Path": "title"
          },
          {
            "Name": "CommentsCount_Count",
            "DbType": "Int32",
            "Path": "commentsCount.count"
          },
          {
            "Name": "IsPublished",
            "DbType": "Boolean",
            "Path": "isPublished"
          },
          {
            "Name": "CreatedAt",
            "DbType": "DateTime",
            "Path": "createdAt"
          },
          {
            "Name": "UpdatedAt",
            "DbType": "DateTime",
            "Path": "updatedAt"
          }
        ]
      }
    ]

General Settings

Name

Stored procedure name.

Path

Path to the results of the GraphQL query. Also serves as the base path for the GraphQL query arguments. Required parameter.

Headers

The headers of the web request for reading the object. 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

Enables paginated retrieval of records if the source supports PagingStrategy for the stored procedure. 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.

RowErrorHandling

This setting is used when additional fields must be specified in the procedure to capture errors. Settings are similar to Provider Configuration ErrorHandling. ErrorsPath and ErrorMessagePath from RowErrorHandling are added to the query. You can use semicolon in the ErrorMessagePath, to add multiple fields to the GraphQL query. For example, "message;path".

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.

Argument

Name or internal path of the GraphQL operation argument. Required parameter.

Path

Path to the field where the argument is located. When BasePathPosition = 0, this is an absolute path.

BasePathPosition

Index of the base path element (Path of the procedure) to which Path (if specified) or Argument (if Path is not specified) is added. Default value is 0. Either Path or BasePathPosition must be specified.

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. Default value is false.

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 parameter 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.

For example, the valid values for the Status parameter are: canceled, chargeable, consumed, failed.

The Columns setting for the Status field will look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
{
    "Name": "Status",
    "Path": "status",
    "DbType": "String",
    "Enum": true,
    "EnumValues": [{
            "Name": "Canceled",
            "Value": "canceled"
        },
        {
            "Name": "Chargeable",
            "Chargeable": "chargeable"
        },
        {
            "Name": "Consumed",
            "Value": "consumed"
        },
        {
            "Name": "Failed",
            "Value": "failed"
        }
    ]
}

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 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 result set.

Name

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

Path

The relative path of the field in the GraphQL query. The base path for it is always the procedure’s Path. For example: "Path": "author.id". Required setting.

If the field value is nested within other properties, the Path defines the path to the field in the format: <parent property name>.<property name>. The nesting level is unlimited.

If a dot is part of the property name rather than a hierarchy separator, quoting with {} should be used. For example: Path: "{my.amount}".

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

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.

Example 1 Procedure with Pagination

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
"Procedures": [
{
  "Name": "GetArticles",
  "Path": "query.articles.nodes",
  "PagingStrategy": {
    "Type": "NextPageToken",
    "NextPageArgument": "after",
    "NextPagePath": "query.articles",
    "NextPageBasePathPosition": 0,
    "TokenPath": "query.articles.pageInfo.endCursor",
    "TokenBasePathPosition": 0,
    "StopConditionPath": "query.articles.pageInfo.hasNextPage",
    "StopConditionBasePathPosition": 0,
    "StopConditionValue": false,
    "PageSizeArgument": "first",
    "PageSizePath": "query.articles",
    "PageSizeBasePathPosition": 0,
    "PageSize": 100
  },
  "Results": [
  {
    "Name": "Id",
    "DbType": "String",
    "Path": "id"
  },
  {
    "Name": "Title",
    "DbType": "String",
    "Path": "title"
  }]
}]

Example 2 Procedure with Parameters

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
"Procedures": [
{
  "Name": "UpdateArticle",
  "Path": "mutation.articleUpdate.article",
  "Parameters": [
  {
    "Name": "Id",
    "Path": "id",
    "DbType": "String",
    "BasePathPosition": 2
  },
  {
    "Name": "Title",
    "Path": "article.title",
    "DbType": "String",
    "BasePathPosition": 2
  }],
  "Results": [
  {
    "Name": "Id",
    "DbType": "String",
    "Path": "id"
  },
  {
    "Name": "Title",
    "DbType": "String",
    "Path": "title"
  }]
}]

On This Page