Integrating with Microsoft SQL Server via stored procedures

2019-07-01, by Dariusz Suchojad

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 Dashboard

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 it 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.