Introduction

Enzo Server
  Installation
  Quick Start

  Core Features
    Async Calls
    ConnectionStrings
    Edge Cache
    HTTP Access
    Scheduling
    Views

  Advanced Capabilities
    Change Data Capture

  Administration
    Configuration Settings
    Logins & ACL
    Auditing
    Current Executions
    Linked Server
    SSL

  Adapters
    Azure Bus
    Azure IoT Hub
    Azure Key Vault
    Azure Storage
    Couchbase
    CSV
    DB (RDBMS)
    Files
    Flight Aware
    FTP
    Geonames
    Insteon
    Loggly
    Messaging (SMS/SMTP)
    MongoDB
    Motus
    MSAD
    MSMQ
    Pipeline
    Pressero
    RabbitMQ
    SalesForce
    Shard
    SharePoint
    SQL Server
    Threading
    Twilio
    Twitter
    VellemanDMX
    Weather (Underground)
    Whiparound
    WMI
    Xls (Excel)
    ZIP


  SDK
    Overview
    Installation
    DevHost
    Create A Simple Adapter
    Handler Columns

    Advanced SDK Concepts
      Handler Decorators
      Dynamic Columns
      Table & Table Enumerators
      Virtual Tables

/sdk/handlercolumns




Handler Columns


A handler contains two sets of columns: input and output. Input columns are also called arguments.

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