Writing your first adapter for Enzo Unified

This blog post shows you how to write your first adapter using the Enzo Unified SDK, so that you can extend the capabilities of SQL Server and/or create a light-weight HTTP proxy for applications, IoT devices or mobile applications. This post assumes you have a high level understanding of Enzo Unified (http://www.enzounified.com) and that you have installed and configured the Enzo Unified SDK (download details below).

In this post, you will learn how to create an adapter to get the current UCT time using the official US NIST time servers. You will write this logic in a new adapter called TimeServers and expose a method called GetUSTime().  Once coded, you will be able to access this method through SQL Server as a stored procedure, or using an HTTP call from any client. Because Enzo Unified exposes these methods as HTTP calls, any client running on any operating system capable of making HTTP calls will be able to run this command.

Pre-Requisites

To successfully build this adapter, you will need the following:

  • Visual Studio 2012 or higher
  • SQL Server 2014 Express Edition or higher
  • Enzo Unified SDK (see download links below)

 

Create an Empty Adapter Project

After downloading and installing the SDK, and registering the Enzo Unified project template per installation instructions, you will be able to create a new Adapter project.

  • Start Visual Studio 2012, and select New Project
  • Select Enzo Unified DataAdapter (Annotated) under Templates –> Visual C# –> Enzo Unified
  • Enter the adapter name:  TimeServers
  • Verify the location of your adapter (it should be under the namespace you specified during installation) and click OK

image

You will need to change two settings:

  • Open TimeServers.cs
  • On line 5, replace the namespace by your namespace (MY is the namespace I entered)
  • Change the the project configuration from Any CPU to x64

image

  • Make sure the project compiles successfully
  • Press F5 to start the adapter (at this point, the adapter does nothing yet)
  • The Enzo Unified Development Host will start and a user interface will be displayed allowing you to test your adapter
  • Type this command, and press F5 to execute it: 

exec my..help

image

Stop the project. We are ready to add the method to this adapter.

Note that while you can use the Development Host for simple testing, it is recommended to use SQL Server Management Studio for better experience. We will review how to connect to Enzo Unified in more detail later.

Create the GetUSTime Method

Let’s replace the entire content of the TimeServers.cs file; we will review the implement next.

using BSC.Enzo.Unified;
using BSC.Enzo.Unified.Logging;
using System;
using System.Collections.Generic;
using System.IO;


namespace MY
{
    public class TimeServers : DataAdapter
    {
        // this is the main call to register your functionality with the Enzo instance
        public override void RegisterAdapter()
        {
            #region Descriptive details
            Title = "NIST Time Server";            // a summary of the adapter's operations
            Description = "Access the NIST time servers";    // more detailed info what your adapter does
            Author = "Me";                            // your or your company name
            AuthorCopyright =                             // your copyright details
                "Copyright (c) 2017 MyCo. All Rights Reserved.";
            AuthorUrl = "www.myco.com";                    // Link to your web site (optional)
            AuthorEmail = "me@myco.com";                // your contact email address
            #endregion


            LogSeverityThreshold = Severity.Debug;        // level of logging detail


            #region Register any configuration variables that can be referenced by your DataAdapter (optional)
           
            // This adapter will require a configuration setting, which can be different per Login/user
            RegisterConfig(
                    "int defaultTimeout|Default timeout in milliseconds||r",
                    "nistServer|The NIST main URI (time.nist.gov)||r"
                );
            #endregion


            #region Optionally register session variables to be automatically created upon client
            // We will keep the list of valid IP Addresses for the time servers as provided by NIST
            RegisterSessionVar("validIPAddress", "");
            #endregion

            #region Handler: GetUTCTime
            RegisterHandler(
                "GetUTCTime,UTCTime|Time",             // name info for your handler
                HandlerOptions.Select,                        // table operations, etc.
                "Obtains the current UTC Time using the NIST servers.",                    // a brief description of functionality
                new[] { "exec TimeServers.GetUTCTime", "exec TimeServers.GetUTCTime 5000" },    // one or more examples
                GetUTCTime,                                    // method used to process request
                new[]                                        // zero or more arguments method expects
                {
                    "int timeout|The timeout for this request"
                },
                new[]                                        // zero or more output columns of rows returned
                {
                    "datetime currentUTCTime|The NIST provided UTC time",
                    "lastIPAddress|The last IP Address used to get the time"
                });
            #endregion
        }

       
        #region Private helper methods

        internal static DateTime GetCurrentUTCDate(ExecEventArgs e, EventResult retval, int timeout, string nistServer, out string ipAddress)
        {
            // Get the last valid IP Address from this session
            string validIPAddress = e.GetVar("validIPAddress", null); // Did we already resolve the list of IP Addresses?
            ipAddress = null;

            int maxAttempts = (validIPAddress == null) ? 5 : 1; // Allow repeating only the first time we make this call in a session

            List<string> addresses = new List<string>();
            DateTime utcDate = DateTime.MinValue;

            string actualTime = "";
            string time = "";
            string lastIP = "";

            while(maxAttempts-- >= 0)
            {
           
                string NISTIP = ipAddress = validIPAddress ?? nistServer;   // Use the last valid IP Address, or the generic NIST endpoint

                retval.AddMessage("NIST Server/IP: " + NISTIP); // Add the server IP Address used to return this date

                try
                {
                    System.Net.Sockets.TcpClient tcp = new System.Net.Sockets.TcpClient(NISTIP, 13);    // Make sure your firewall allows this port through...
                    tcp.Client.ReceiveTimeout = timeout;
                    tcp.Client.SendTimeout = timeout;

                    using (StreamReader reader = new StreamReader(tcp.GetStream()))
                    {
                        time = reader.ReadToEnd();

                        lastIP = tcp.Client.RemoteEndPoint.ToString();
                        if (lastIP != "")
                            lastIP = lastIP.Substring(0, lastIP.IndexOf(':'));

                        reader.Close();

                        if ((time ?? "") != "")
                            break;
                    }
                }
                catch (Exception ex)
                {
                    retval.AddMessage("NIST Error: " + ex.Message); // Add an output to the message window
                }
                System.Threading.Thread.Sleep(timeout); // This is needed or we could get an error from NIST servers
            }

            try
            {
                actualTime = time.Substring(time.IndexOf(' ') + 1);
                actualTime = "20" + actualTime.Substring(0, actualTime.LastIndexOf(':') + 3);
                utcDate = Convert.ToDateTime(actualTime);
            }
            catch
            {
                string err = "Error with date received: " + time;
                throw new Exception(err);
            }

            if (actualTime == "")
                throw new System.Net.WebException("Could not connect to the NIST time server");

            if (validIPAddress == null && lastIP != "")
            {
                // Save the valid IP address in the session variable
                e.SetVar("validIPAddress", lastIP);
            }

            return utcDate;

        }


        #endregion
       

        #region Handler Function: MyHandler
        private EventResult GetUTCTime(object sender, ExecEventArgs e)
        {
            EventResult retval = new EventResult(e);

            int timeout = e.GetArg("timeout", e.GetSetting<int>("defaultTimeout")); // Timeout to use (the value provided as input parameter, or the default setting)
            string nistServer = e.GetSetting("nistServer");

            // Implement validation for the timeout value
            if (timeout == 0)
                return retval.SetResultCode("Timeout value of '{0}' is not accepted.", timeout);

            if (e.IsArgValidation)
                return retval;

            string lastIPAddress = null;
            DateTime now = GetCurrentUTCDate(e, retval, timeout, nistServer, out lastIPAddress);

            retval.AddResultRow(now, lastIPAddress);

            return retval;
        }
        #endregion
    }
}

This code implements the following functionality: Configuration Settings (RegisterConfig, e.GetSetting), Session Variables (RegisterSessionVar, e.GetVar, e.SetVar), a handler (i.e. the method: RegisterHandler) called GetUTCTime, response messages (AddMessage), result rows (AddResultRow) and method arguments (e.GetArg).

RegisterAdapter()

The RegisterAdapter() method is called automatically by Enzo Unified and setups the adapter configuration and methods. Once this method executes, the adapter is up and running.

Configuration Settings store configuration information for a given adapter; these settings are loaded per login. Each login can have one or more configuration settings, but only one can be set as the default configuration. We will create configuration settings soon using an SQL command. In our code, we are keeping two settings: a timeout value and the URL of the NIST server load balancer. The syntax for declaring configuration settings is documented in the help file; in our example we require that both values be set, but no default value is provided. We access these values by calling the GetSetting method on the ExecEventArg variable.

Next we define a session variable called validIPAddress. Session variables are created when a user logs in, and destroyed when logging off. This is typically used for performance reasons so that when a SQL login takes place variables can be stored and retrieved later. However session variables do not provide performance improvements for HTTP calls or over Linked Server because a new session is established every time. We get and set session variables using the GetVar and SetVar methods on the ExecEventArg variable.

Then the RegisterHandler method is called; this method creates a new handler that can listen for native SQL commands as well as HTTP requests. The method takes several parameters, including the actual method to call, the list of input parameters and output columns. In this case, we are creating a method called GetUTCTime and can be called with HTTP from code, and EXEC or SELECT operators with SQL Server.

GetUTCTime()

This method performs the actual operation for the GetUTCTime handler. Of importance, this method provides an ExecEventArg variable that gives detailed information about the incoming request. Feel free to inspect this object; it contains information about the caller, the operation being sent and the input parameters.

This method expects a EventResult object back. The EventResult object allows you to specify the return rows and optional messages to be returned to the caller. To add rows to the output, simply call the AddResultRow method and a list of values to the expected columns being returned. In our case, the RegisterHandler dictates that two columns need to be returned: a datetime and a string (currentUTCTime and lastIPAddress). Although not shown in this example, you can also dynamically define a list of columns to be returned if needed.

This method also calls GetCurrentUTCDate(), which implements the logic for fetching the current time from NIST servers. The logic itself isn’t very important; however you will notice calls to GetVar and SetVar to get/set the session variable created earlier.

First Time Setup

Now that the adapter is ready to go, run it by pressing F5; the Enzo Unified Development Host will start automatically.  It is time to configure our default settings; execute the following commands:

exec my.TimeServers._configCreate 'nistsettings', 1, 1000, 'time.nist.gov'
exec my.TimeServers._configUse 'nistsettings'

The first command creates a setting called ‘nistSettings’, sets it as the default configuration, with 1 second timeout and time.nist.gov as the initial NIST address to use.  Once these calls are made, you will no longer need to execute them.

Testing with SQL Commands

We will test our SQL commands using SQL Server Management Studio (SSMS). Start SSMS and connect to Enzo Unified (warning: you may be tempted to connect to SQL Server; you should instead connect to Enzo Unified); enter the following information:

  • Server Name:  localhost,9550
  • Authentication: SQL Server Authentication
  • Login: sa
  • Password: password

image

Let’s first examine the built-in help of our command:

exec  my.TimeServers.getutctime help

You will see a built-in help provided listing the input parameters and output columns:

image

Let’s execute the procedure called GetUTCTime using a SQL Stored Procedure call; we not passing the timeout value, so the default of 1 second will be used:

exec  my.TimeServers.getutctime

image

You can also try the SELECT command (note that the table name is a bit different, as specified in the RegisterHandler call):

SELECT * FROM my.TimeServers.utctime

Testing with REST

Before being able to run a command through REST, we first need to find out the AuthToken for the REST request for the ‘sa’ account.  The AuthToken is unique for each account in Enzo Unified; when a command is executed, Enzo Unified looks up the AuthToken provided and executes the request in the context of the associated user. To find the AuthToken, execute this SQL command from SQL Server; lookup the authToken column.

SELECT * FROM instance.security.accounts

To test our method from a REST client, let’s use Fiddler; we simply need to issue a GET request against the my/timeserver/getutctime method. Here is the full URL for the request; the port information was specified during installation of Enzo Unified.

http://localhost:19550/my/timeservers/getutctime

And the body of the request is as follows:

authToken: 701bc09f-8bed-4087-9c88-364f45fa83c0

If we want to specify a timeout value other than the default we would add this line to the payload:

timeout: 1000

image

Once executed, you will receive a JSON response with the current UTC time as expected.

 

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Enzo Unified (http://www.enzounified.com/). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.

Print | posted @ Tuesday, March 14, 2017 8:57 AM

Comments on this entry:

Gravatar # re: Writing your first adapter for Enzo Unified
by Khan Saab at 3/19/2017 10:42 PM

This is wonderful article.You can checkout my Blog Lineage OS
Gravatar # re: Writing your first adapter for Enzo Unified
by Lara at 3/21/2017 5:03 AM

That's a great article for students for their Assignment Writing
Gravatar # re: Writing your first adapter for Enzo Unified
by mawar at 4/5/2017 9:49 PM

soon a new page two successful page one to one Obat Herbal Asam Urat
Gravatar # re: Writing your first adapter for Enzo Unified
by Lyrics Bright at 4/8/2017 2:34 AM

We Are Working With Worlds Largest Song Lyrics Site
Comments have been closed on this topic.