This article will show you how to invoke MS SQL stored procedures from Zato services - a feature new in the just released version 3.1 of the Python-based integration platform.

In web-admin

Start off by installing the latest updates.

Next, the first thing needed is creation of a new outgoing SQL connection - make sure to choose the MS SQL (Direct) type, as below.

It is considered a direct one because, even though it is based on SQLAlchemy, it does not make use of the most of SQLAlchemy's functionality and lets one invoke stored procedures alone, i.e. it is not possible to use this type of connections with ORM or anything else - only stored procedures are supported.

Make sure to change the password after creating a connection - the default one is a randomly generated string.

Python code

In most cases, to invoke a stored procedure, use the code below:

# -*- coding: utf-8 -*-


# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Connection to use
        name = 'My MS SQL Connection'

        conn = self.outgoing.sql.get(name)
        session = conn.session()

        # Procedure to invoke
        proc_name = 'get_current_user'

        # Arguments it has on input
        args = ['my.user.id']

        data = session.callproc(proc_name, args)

        # Data is a list of dictionaries, each of which
        # represents a single row of data returned by the procedure.
        for row in data:
            ...

Lazy evaluation

The usage example above will work in many cases but, supposing a procedure returns many thousands of rows, it may not be efficient to read them in all in a single call.

This would potentially create a big list of row elements - if all them are indeed required in a single place then this is not a concern. But if they should be processed one by one then it may be better to explicitly fetch and process a single row at a time.

To achieve it, use_yield=True can be applied, as in the code below. Now, each iteration of the for loop will return a new row, without ever accumulating all of them in RAM.

# -*- coding: utf-8 -*-


# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Connection to use
        name = 'My MS SQL Connection'

        conn = self.outgoing.sql.get(name)
        session = conn.session()

        # Procedure to invoke
        proc_name = 'get_current_user'

        # Arguments it has on input
        args = ['my.user.id']

        data = session.callproc(proc_name, args, use_yield=True)

        # Data is a Python generator now and each iteration
        # of the loop returns a new row from the stored procedure.
        for row in data:
            ...

Wrapping up

Ability to use MS SQL is a feature new in Zato 3.1 - it works in a way similar to other SQL connection types with the notable exception that only stored procedures can be invoked from Python code.

There are two ways to invoke stored procedures - either by reading the whole output into a service or processing rows one by one. The latter is recommended if a large number of rows is to be processed by the service.

The newest version of Zato, the open-source Python-based enterprise API integrations platform and backend application server, is out with a lot of interesting features, changes and additions

The full changelog is here and below is a summary of what is new in 3.1:

  • Greatly enhanced support for Docker, including Quickstart, Swarm and Kubernetes
  • Python 3 is now fully supported in addition to Python 2.7
  • New connectors and adapters: MongoDB, LDAP (Active Directory), Apache Kafka, SFTP, Slack, Telegram and JSON-RPC
  • Extensions to Single Sign-On: two-factor authentication and multi-credentials accounts
  • Rate-limiting and IP white-listing, including hierarchical definitions
  • Extensions to WebSockets: outgoing connections and broadcasts
  • A range of security enhancements, including TOTP two-factor authentication in web-admin
  • General performance boosts - both run-time and server startup

What is Zato?

Zato is an open-source API integrations platform and backend application server composed of several major blocks of functionality:

  • Online request/response integrations using a wide range of protocols, including SAP, Odoo, IBM MQ, REST, AMQP, Search, Email and many more

  • Publish/subscribe message topics with queues and guaranteed delivery

  • Single Sign-On for REST and Python applications

Its HA architecture is highly-scalable and everything comes with a web-based GUI along with a command-line interface and admin APIs.

If you are looking for a highly productive Python-based open-source platform designed specifically to integrate systems or expose APIs in many protocols or data formats, to be used by other servers, frontends or mobile, Zato is the choice.

Quick links:

Invoking individual WebSocket connections has been supported since Zato 3.0 and Zato 3.1 adds new functionality on top of it - message broadcasting - which lets one notify all the clients connected to a particular channel. Here is how to use it.

Web-admin

Let's say that there is a WebSocket channel such as the one here:

In the context of broadcast messages, the most important part of this definition is its name - below, we will be sending messages to all clients connected to that particular channel by its name.

Python code

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals


# Zato
from zato.server.service import Service

class WSXBroadcast(Service):

    def handle(self):

        # Channel to invoke
        name = 'My WSX API Channel'

        # Get a handle to the channel object
        channel = self.wsx.channel[name]

        # Message to send
        data = 'Hello from Zato'

        # Broadcast this message to all clients connected to that channel
        channel.broadcast(data)

And this is literally it - you have just broadcast a message to all WebSocket connections of that channel. It does not matter if clients are in JavaScript, Python, if they are other Zato servers or browsers - all of them receive the notification.

Note that the messages are always sent in background - they are treated as asynchronouous messages and Zato does not wait for any potential response from the clients.

Employing JSON Schema is an easy and straightforward way to validate input to your APIs - learn below how it can be enabled in Zato 3.1+ services in one line of code.

Preparing a schema

Supposing that our service needs two parameters:

  • name (string)
  • pub_year (integer)

This is how a schema representing them may look like - let's save it in a file called myschema.json.

{
  "type": "object",
  "required": ["name", "pub_year"],
  "properties": {
    "name":  {"type" : "string"},
    "pub_year": {"type" : "number"}
  }
}

This is as simple as it can get - we list our parameters and for each one, we also say of what datatype they are.

Note that a JSON Schema may be of arbitrary complexity to express any kind of business needs. It is also possible to split bigger schemas into reusable pieces, ready for inclusion in more than one place.

Uploading the schema

Each server has a directory called config/repo/schema/json - this is where the schema needs to be uploaded.

For instance, if path to a server is /home/zato/env/server1 then the full path will be /home/zato/env/server1/config/repo/schema/json.

Save myschema.json to this directory and restart your server before continuing to the next step.

Configuring a service

To make use of the prepared schema, a service uses an attribute appropriately called schema, as below.

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals

# Zato
from zato.server.service import Service

class MyService(Service):

    # Require JSON Schema validation
    schema = 'myschema.json'

    def handle(self):

        # If we are here, it means that the input schema validation succeeded
        self.logger.info("My request: %s", self.request.payload)

Note that the value of this attribute can be either relative or absolute. If it is relative, it is in relation to the main directory (config/repo/schema/json). If it is an absolute one, it must be a full path to the schema file in the filesystem.

Schema validation in runtime

You do not need to do anything else for schema validation to work, let's observe it.

As expected, on invalid input we get an error:

$ curl localhost:17010/schema/check -d '{"user_id":123}' ; echo
{"zato_env":
  {"result": "ZATO_ERROR",
   "cid": "0ee199609d5020d7cb8ca924",
   "details":
     {"is_ok": false,
      "cid": "0ee199609d5020d7cb8ca924",
      "message": "Invalid request"
  }}}
$

Whereas with correct data on input a message is stored in a log file:

INFO - My request: {'name':'The Garden of Cyrus', 'pub_year':1658}

Web-admin options

Sometimes it is handy to be able to disable input validation on demand, without redeployment of code. On the other hand, it is at times convenient to return more details than "Invalid request" alone.

This is exactly what can be done in web-admin, for each service separately:

Summary

That is all - you have just created a schema and enabled it for your service. Zato picked it up, started to enforce its definitions in runtime, and the whole of it took just a single line of code. Moreover, you can dynamically change the way it is used without making any updates to the source code.

This is a quick guide on how to turn SSH commands into a REST API service. The use-case may be remote administration of devices or equipment that does not offer a REST interface or making sure that access to SSH commands is restricted to selected external REST-based API clients only.

Python

The first thing needed is code of the service that will connect to SSH servers. Below is a service doing just that - it receives name of the command to execute and host to run in on, translating stdout and stderr of SSH commands into response documents which Zato in turn serializes to JSON.

It uses the sh library for SSH connections which means that each new one is started in a subprocess.

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals

# stdlib
from traceback import format_exc

# sh
from sh import ssh

# Zato
from zato.server.service import Service

class SSHInvoker(Service):
    """ Accepts an SSH command to run on a remote host and returns its output to caller.
    """
    class SimpleIO:
        input_required = 'host', 'command'
        output_required = 'is_ok', 'cid'
        output_optional = 'stdout', 'stderr'
        skip_empty_keys = True
        response_elem = None

    def handle(self):

        # Local aliases
        host = self.request.input.host
        command = self.request.input.command

        # Correlation ID is always to be returned
        self.response.payload.cid = self.cid

        try:
            # Run the command and collect output
            output = ssh(host, command)

            # Assign both stdout and stderr to response
            self.response.payload.stdout = output.stdout
            self.response.payload.stderr = output.stderr

        except Exception:
            # Catch any exception and log it
            self.logger.warn('Exception caught (%s), e:`%s', self.cid, format_exc())
            self.response.payload.is_ok = False

        else:
            # Everything went fine
            self.response.payload.is_ok = True

Web-admin

In web-admin, let's go ahead and create an HTTP Basic Auth definition that a remote API client will authenticate against:

Now, the SSH service can be mounted on a newly created REST channel - note the security definition used and that data format is set to JSON.

Usage

At this point, everything is ready to use. For testing purposes, let's invoke the service from command line:

$ curl "api:8MbrHs2GHHQjw@localhost:11223/api/ssh" -d \
    '{"host":"localhost", "command":"uptime"}'
{
    "is_ok": true,
    "cid": "27406f29c66c2ab6296bc0c0",
    "stdout": " 09:45:42 up 37 min,  1 user,  load average: 0.14, 0.27, 0.18\n"}
$

This completes it - the service is deployed and made accessible via a REST channel that can be invoked using JSON. Any command can be sent to any host and, assuming that SSH commands can be executed at all, their output will be returned to API callers in JSON responses.