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

  User Guides
     Sharding
     Sharding Overview

     SharePoint

   Adapters
    All Adapters
    ActiveCampaign
    Azure Bus
    Azure IoT Hub
    Azure Key Vault
    Azure Storage
    Bing Search
    ConstantContact
    Couchbase
    CSV
    DB (RDBMS)
    Dropbox
    Files
    Flight Aware
    FreshBooks
    FTP
    Geonames
    GoogleDrive
    GoogleSearch
    GoogleSheet
    GreenMile
    GpsInsight
    HttpEndpoint (REST)
    Intacct
    Insteon
    Loggly
    MailChimp
    Messaging (SMS/SMTP)
    MongoDB
    Motus
    MSAD
    MSMQ
    OneDrive
    OneDrive365 (Business)
    Pipeline
    Pressero
    RabbitMQ
    SalesForce
    Shard
    SharePoint
    Slack
    SQL Server
    SQLX
    Sudoku
    Teams
    Threading
    Twilio
    Twitter
    VellemanDMX
    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

/adapters/sqlx




SQLX


Use this adapter to access advanced operations from SQL Server such as encryption, compression, Unix Epoch conversion and more.


Configuration Settings



This adapter does not use configuration settings.



Handlers



EpochToTime
Converts epoch secs (number of seconds since 1970-01-01 at midnight GMT) to a date/time (UTC). Epoch seconds are commonly used by Java, Javascript, and *nix platforms for date/time creation and manipulation. details 

Arguments

Name Description Default
fromEpochSecs R

SQL Call

exec sqlx.EpochToTime 0


Http Call

/bsc/sqlx/epochtotime

Output Columns

String epochDatetimeThe resulting Date/time
GetNodeValue
NodeValue
Gets an element/node value from a JSON payload details 

Arguments

Name Description Default
element
json R

SQL Call

SELECT * FROM sqlx.NodeValue WHERE element='product' AND json='{"product":"pencil","price":12}'
exec sqlx.GetNodeValue 'product', '{"product":"pencil","price":12}'
EXEC sqlx.GetNodeValue '[0].songs[0].Name', '[{"product": "test", "songs": [ { "Name": "Blue Eyes" } ]}]'


Http Call

/bsc/sqlx/getnodevalue

Output Columns

String dataThe value of the element/node
GetRandomFileName
Get a random filename for the Enzo TMP directory details 

Arguments

Name Description Default
fspec R

SQL Call

declare @fspec varchar(max)
exec bsc.SqlX.GetRandomFileName @fspec output
select @fspec


Http Call

/bsc/sqlx/getrandomfilename

Output Columns

This call does not return data.

GetRandomLong
Obtains a random long value details 

Arguments


SQL Call

exec sqlx.GetRandomLong


Http Call

/bsc/sqlx/getrandomlong

Output Columns

Int64 result
GetXmlValue
XmlValue
Gets an element/attribute value from an XML payload details 

Arguments

Name Description Default
xpath
xml R

SQL Call

SELECT * FROM sqlx.XmlValue WHERE xpath='/root/product' AND xml='pencil12'
exec sqlx.GetXmlValue '/root/product', 'pencil12'


Http Call

/bsc/sqlx/getxmlvalue

Output Columns

String dataThe value of the element/attribute
JsonToXml
Converts a JSON string to an XML string details 

Arguments

Name Description Default
json R
rootElementName root

SQL Call

exec sqlx.JsonToXml '{"product":"pencil","price":12}'
exec sqlx.JsonToXml '{"product":"pencil","price":12}', 'cart'


Http Call

/bsc/sqlx/jsontoxml

Output Columns

String xmlThe resulting XML string
RegExMatch
Performs a regular expression Match operation on a string starting at a specific position details 

Arguments

Name Description Default
pattern
input
startat
options 4

SQL Call

exec sqlx.regexmatch '(\w+)\s+(car)', 'One car red car blue car', 0, 4


Http Call

/bsc/sqlx/regexmatch

Output Columns

This call does not return data.

RegExReplace
Replaces all strings that match a specific pattern with a replacement string details 

Arguments

Name Description Default
pattern R
input R
replace R
options

SQL Call

exec sqlx.regexreplace 'mycompany', '\\MyMachine.domain1.mycompany.com\C$\ThingsToDo.txt', 'test', 4


Http Call

/bsc/sqlx/regexreplace

Output Columns

String resultResult of RegEx replacement
TimeToEpoch
Converts a date/time (UTC) to epoch secs (number of seconds since 1970-01-01 at midnight GMT). Epoch seconds are commonly used by Java, Javascript, and *nix platforms for date/time creation and manipulation. details 

Arguments

Name Description Default
fromDatetime R

SQL Call

exec sqlx.TimeToEpoch '1970-01-01 00:00:00'


Http Call

/bsc/sqlx/timetoepoch

Output Columns

Int32 epochSecsThe resulting epoch seconds
UnZip
Decompresses a 64-bit encoded string value that was previously compressed using the Zip procedure details 

Arguments

Name Description Default
input R
source
column_name

SQL Call

exec sqlx.UnZip '1F8B08000000000004002BC9C82C5600A2448592D4E21285B2C49CD25485927C85AACC0200E8CE74F71B000000'
exec sqlx.unzip '', 'source2', 'name' -- requires a data source called 'source2'


Http Call

/bsc/sqlx/unzip

Output Columns

String resultUncompressed result
XmlToJson
Converts an XML string to a JSON string details 

Arguments

Name Description Default
xml R

SQL Call

exec sqlx.XmlToJson 'pencil12'


Http Call

/bsc/sqlx/xmltojson

Output Columns

String jsonThe resulting JSON string
Zip
Compresses a string value using the GZip algorithm into a 64-bit encoded string value details 

Arguments

Name Description Default
input R
source
column_name

SQL Call

exec sqlx.Zip 'a long string value to compress'
exec sqlx.zip '', 'source', 'name' -- uses a source name called 'source'


Http Call

/bsc/sqlx/zip

Output Columns

String resultCompressed result