/sdk/handlercolumns

Handler Columns

Defining columns improperly may prevent your handler from being used from REST calls or from Linked Server connections (and as a result from SQL Server directly)

Guidelines

You should name your columns using the following guidelines:

• Column names should not be a known HTTP Header (avoid these column names: connection, keep-alive, authorization, age, warning...)
• Input and output columns should be identical to participate in Linked Server operations using SELECT, DELETE, INSERT and UPDATE operations; if you are only using the EXEC operation, input and output columns can be different.
• Column names should be unique within a handler
• Certain column names will be automatically shown with password characters (*): password, pwd, key, authtoken, authenticationtoken, accesstokensecret, apikey, secret
• The connectionString input column name will be treated as a Central Connection String, and a dropdown will display in the Enzo Manager application to select from a list of connection strings

Generally speaking you specify the data type of a column by using a C# syntax. If no data type is provided, the column is assumed to be a string data type.

In this version the XML, date, and time data types are not supported; you can however use a string and datetime data type instead respectivelly.

Each column is defined at a minimum by its name. Additional options are available by adding a '|' character. Up to three pipes can be added to add a description, a default value and additional options for the column:

datatype colName|description|default|options

The description can be anything that doesn't contain a '|' character.
The default is optional, but if provided should match the data type specified for the column.
The options are a set of characters with specific a meaning; they are only used by input arguments. For example 'r' means required.

• r: column is required
• s: column is sensitive data and should only be displayed to Enzo Administrators (useful for Configuration Settings)
• t: column represents a Top N argument
• u: argument is automatically extracted as part of a REST call (ex: .../handler/1)
• x: column supports extended operators (ex: supports LIKE operation)
• z: column is an argument that should not be counted as a column (useful for Linked Server compliance)
• @: column holds a subTable name (see the Handler Decorators section)
• {choice:A,B}: column requires a specific value from the list provided
• {length:val[:scale]}: column expects a specific length value and optionally a scale

Example

This handler defines a Stored Procedure / REST endpoint called ListPipelines, also accessible using the Pipelines table name. The config and the sourceTable parameters are required. The mode parameter is optional but has a default value (CDC). The enabled parameter is defined a boolean data type.

RegisterHandler("ListPipelines,Pipelines",
    HandlerOptions.Select | HandlerOptions.HttpGET,
    "Gets list of pipelines.",
    new[] { @"exec Pipeline.ListPipelines" },
    ListPipelines,
    new[] { "name|The name of the pipeline (starts with)" },
    new[] { 
        "name|The name of the pipeline",
		"identifier|The internal unique id for this pipeline",
        "owner|The owner of the pipeline",
        "bool enabled|true when the pipeline is enabled",
        "datetime createdOn|The date the pipeline was created",
        "adapter|The name of the source adapter",
        "config|The configuration name of the source adapter||r", 
        "sourceTable|The source table to read from||r",    
        "interval|The polling interval to the source system as a cron expression",  
        "mode|The pipeline mode (CDC,Sync,CDCSync)|CDC",
        "filter|Adapter-specific filter if required (ex: Twitter)",
        "columnList|List of columns available from the source system",
        "IDs|The Primary Key of the source system (if any)"

    }
    );

The above handler is accessible using the following SQL commands, assuming the name of the adapter is MyAdapter:

EXEC MyAdapter.ListPipelines 'pipelinename'

or

SELECT * FROM MyAdapter.Pipelines WHERE name = 'pipelinename'
SELECT * FROM MyAdapter.Pipelines WHERE name = 'pipelinename' AND mode='CDC'


The handler is also accessible using a REST command (note: an authentication token is needed for a successful call):

HTTP GET 127.0.0.1/bsc/myadapter/getpipelines?name=pipelinename