(Migrated) Outgoing Sql Connection to Microsoft SqlServer

(This message has been automatically imported from the retired mailing list)

Dear Zato’s Team,

I want to establish an SQL Outgoing Connection to a Microsoft SqlServer.
How can I do it?

Where do I create that connection? Directly into the service handler?

Thank you in advance

Un saludo,
–=20

Jorge Herrera Pi=C3=B1ero
Jefe del =C3=81rea de Servicios TIC
Correo: jhpinero@ull.es Web: http://www.stic.ull.es
Servicio de Tecnolog=C3=ADas de la Informaci=C3=B3n y la Comunicaci=C3=B3n =
(STIC)
Universidad de La Laguna (ULL)

=C2=BFNecesita ayuda? Acceda al portal de soporte <http://soporte.ull.es/st=
ic>

[image: Certificado EFQM de Excelencia 300+]
<http://support.webs.ull.es/calidad/CertificadoCEG_UniversidadLaLaguna_STIC=
.pdf>

Le agradecer=C3=ADamos que valorase el servicio que presta el STIC
http://www.ull.es/encuestas/index.php/53427/lang-es

Este mensaje puede contener informaci=C3=B3n confidencial y/o privilegiada.
Si usted no es el destinatario o lo ha recibido por error debe borrarlo
inmediatamente. Est=C3=A1 estrictamente prohibido por la legislaci=C3=B3n v=
igente
realizar
cualquier copia, revelaci=C3=B3n o distribuci=C3=B3n del contenido de este =
mensaje
sin autorizaci=C3=B3n expresa.

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient or have received this e-mail in
error you must destroy it. Any unauthorised copying, disclosure or
distribution
of the material in this e-mail is strictly forbidden by current legislation=
.

On 05/02/15 16:03, Jorge Herrera Piñero wrote:

I want to establish an SQL Outgoing Connection to a Microsoft
SqlServer. How can I do it?

Where do I create that connection? Directly into the service handler?

Hi Jorge,

this is not a built in functionality - you’d need to establish the
connections yourself.

Doing it directly in the handle method means each invocation of a
service would open a new connection.

Most of the time this is to be avoided, Zato or not. But on the other
hand, if such a service is invoked 6 times a day to, say, process
reports in batches - it would not hurt.

Alternatively, you can use a feature new in 2.0 - server-wide user
context. Basically, it’s a dictionary of arbitrary data that can be
shared across all services on a given server.

It can be used to keep configuration or connections to technologies for
which there is no adapter as of yet, such as MS SQL. Everything that
needs to be shared can be put in there and connections to SQL Server
sound just fine.

You’d then have something like:

def handle(self):
conn = self.server.user_ctx[‘My Connection’]

As it happens, I am working on the feature’s documentation exactly as we
speak. I have just added a few introductory words. The chapter will be
done today or tomorrow.

I don’t have any SQL Server handy but if you contact me off-list and can
share access to a DB instance I can take it as an opportunity to
document the feature using your use-case. That would let you have
working code serving exactly what you need.

Which reminds me… I asked about this earlier and Dariusz pointed me in a
direction on how to add at least parts of the MSSQL functionality.
Never got any further as I figured there is so much more I need to learn
about Zato before MSSQL becomes a must-have for me.

I did add pyodbc to Zato 1.1 and it worked. Open/close connection on each
service invocation is a bummer, though…

Details here:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-July/00055=
0.html

Adding msql in the same way mysql was added to 1.1:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-November/0=
00672.html
Also:

Maybe something is of use for you among those links. Good luck!

-Bad

On Thu, Feb 5, 2015 at 4:57 PM, Dariusz Suchojad dsuch@zato.io wrote:

On 05/02/15 16:03, Jorge Herrera Pi=C3=B1ero wrote:

I want to establish an SQL Outgoing Connection to a Microsoft
SqlServer. How can I do it?

Where do I create that connection? Directly into the service handler?

Hi Jorge,

this is not a built in functionality - you’d need to establish the
connections yourself.

Doing it directly in the handle method means each invocation of a
service would open a new connection.

Most of the time this is to be avoided, Zato or not. But on the other
hand, if such a service is invoked 6 times a day to, say, process
reports in batches - it would not hurt.

Alternatively, you can use a feature new in 2.0 - server-wide user
context. Basically, it’s a dictionary of arbitrary data that can be
shared across all services on a given server.

It can be used to keep configuration or connections to technologies for
which there is no adapter as of yet, such as MS SQL. Everything that
needs to be shared can be put in there and connections to SQL Server
sound just fine.

You’d then have something like:

def handle(self):
conn =3D self.server.user_ctx[‘My Connection’]

As it happens, I am working on the feature’s documentation exactly as we
speak. I have just added a few introductory words. The chapter will be
done today or tomorrow.

I don’t have any SQL Server handy but if you contact me off-list and can
share access to a DB instance I can take it as an opportunity to
document the feature using your use-case. That would let you have
working code serving exactly what you need.

I am using MS SQL server. Calling a stored procedure a few times day.

So I am creating a connection each time. Later I will probably build a
fulltime connection.

I am storing connection strings in the kvdb - the
first insphire:connect:default points the key of the actual connection
string which allows me to swap them easily without redeploying code.

from zato.server.service import AsIs, Boolean, Integer, Unicode, Service
import pyodbcimport urllib

class UpdateEngineHours(Service):

class SimpleIO:
    input_required =3D (Unicode('assetno'), Integer('hours'),
        Unicode('datetime'))

@property
def odbc_connection(self):
    source =3D self.kvdb.conn.get('insphire:connect:default')
    conn_string =3D urllib.unquote(self.kvdb.conn.get(source))
    self.logger.info('connection strng: {}'.format(conn_string))
    connection =3D pyodbc.connect(conn_string, autocommit=3DTrue)
    self.logger.info('connection made: {}'.format(connection))
    return connection

def handle(self):
    self.logger.info('Request: {}'.format(self.request.payload))
    self.logger.info('Request type: {}'.format(type(self.request.payloa=

d)))
with self.odbc_connection as con:
assetno =3D self.request.input.assetno
hours =3D self.request.input.hours
tstamp =3D self.request.input.datetime
con.execute(“exec [rel].add_meter_reading ?,?,?”, assetno,
hours, tstamp) # lint:ok
self.logger.info(‘Request written’)

On 6 February 2015 at 03:32, Baad Sequel badsequel@gmail.com wrote:

Which reminds me… I asked about this earlier and Dariusz pointed me in a
direction on how to add at least parts of the MSSQL functionality.
Never got any further as I figured there is so much more I need to learn
about Zato before MSSQL becomes a must-have for me.

I did add pyodbc to Zato 1.1 and it worked. Open/close connection on each
service invocation is a bummer, though…

Details here:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-July/000=
550.html

Adding msql in the same way mysql was added to 1.1:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-November=
/000672.html
Also:
https://github.com/zatosource/zato/issues/86

Maybe something is of use for you among those links. Good luck!

-Bad

On Thu, Feb 5, 2015 at 4:57 PM, Dariusz Suchojad dsuch@zato.io wrote:

On 05/02/15 16:03, Jorge Herrera Pi=C3=B1ero wrote:

I want to establish an SQL Outgoing Connection to a Microsoft
SqlServer. How can I do it?

Where do I create that connection? Directly into the service handler?

Hi Jorge,

this is not a built in functionality - you’d need to establish the
connections yourself.

Doing it directly in the handle method means each invocation of a
service would open a new connection.

Most of the time this is to be avoided, Zato or not. But on the other
hand, if such a service is invoked 6 times a day to, say, process
reports in batches - it would not hurt.

Alternatively, you can use a feature new in 2.0 - server-wide user
context. Basically, it’s a dictionary of arbitrary data that can be
shared across all services on a given server.

It can be used to keep configuration or connections to technologies for
which there is no adapter as of yet, such as MS SQL. Everything that
needs to be shared can be put in there and connections to SQL Server
sound just fine.

You’d then have something like:

def handle(self):
conn =3D self.server.user_ctx[‘My Connection’]

As it happens, I am working on the feature’s documentation exactly as we
speak. I have just added a few introductory words. The chapter will be
done today or tomorrow.

I don’t have any SQL Server handy but if you contact me off-list and can
share access to a DB instance I can take it as an opportunity to
document the feature using your use-case. That would let you have
working code serving exactly what you need.

Also the connection string is important.

I found that I need to use autocommit=3DTrue - otherwise you needs commits
all over the place. And you need to enable MARS

DRIVER=3D{ODBC Driver 11 for SQL
Server};SERVER=3D192.168.4.107,14330;DATABASE=3Dtest;UID=3Dtest;PWD=3Dtest1=
23;MARS_Connection=3Dyes

With this in place it works reliably for me.

I have also setup SQL Server Broker Queues to asynchronously call out to
zato.

Zato is it be our integration point between our primary application (which
has no api ;-( we have to talk to the underlying database and multiple
external services inbound and outbound.

Cheers

Tim

On 6 February 2015 at 07:34, Tim Hoffmn thoffman.rel@gmail.com wrote:

I am using MS SQL server. Calling a stored procedure a few times day.

So I am creating a connection each time. Later I will probably build a
fulltime connection.

I am storing connection strings in the kvdb - the
first insphire:connect:default points the key of the actual connection
string which allows me to swap them easily without redeploying code.

from zato.server.service import AsIs, Boolean, Integer, Unicode, Service
import pyodbcimport urllib

class UpdateEngineHours(Service):

class SimpleIO:
    input_required =3D (Unicode('assetno'), Integer('hours'),
        Unicode('datetime'))

@property
def odbc_connection(self):
    source =3D self.kvdb.conn.get('insphire:connect:default')
    conn_string =3D urllib.unquote(self.kvdb.conn.get(source))
    self.logger.info('connection strng: {}'.format(conn_string))
    connection =3D pyodbc.connect(conn_string, autocommit=3DTrue)
    self.logger.info('connection made: {}'.format(connection))
    return connection

def handle(self):
    self.logger.info('Request: {}'.format(self.request.payload))
    self.logger.info('Request type: {}'.format(type(self.request.payl=

oad)))

    with self.odbc_connection as con:
        assetno =3D self.request.input.assetno
        hours =3D self.request.input.hours
        tstamp =3D self.request.input.datetime
        con.execute("exec [rel].add_meter_reading ?,?,?", assetno, ho=

urs, tstamp) # lint:ok

    self.logger.info('Request written')

On 6 February 2015 at 03:32, Baad Sequel badsequel@gmail.com wrote:

Which reminds me… I asked about this earlier and Dariusz pointed me in =
a

direction on how to add at least parts of the MSSQL functionality.
Never got any further as I figured there is so much more I need to learn
about Zato before MSSQL becomes a must-have for me.

I did add pyodbc to Zato 1.1 and it worked. Open/close connection on eac=
h

service invocation is a bummer, though…

Details here:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-July/00=
0550.html

Adding msql in the same way mysql was added to 1.1:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-Novembe=
r/000672.html

Also:
https://github.com/zatosource/zato/issues/86

Maybe something is of use for you among those links. Good luck!

-Bad

On Thu, Feb 5, 2015 at 4:57 PM, Dariusz Suchojad dsuch@zato.io wrote:

On 05/02/15 16:03, Jorge Herrera Pi=C3=B1ero wrote:

I want to establish an SQL Outgoing Connection to a Microsoft
SqlServer. How can I do it?

Where do I create that connection? Directly into the service handler?

Hi Jorge,

this is not a built in functionality - you’d need to establish the
connections yourself.

Doing it directly in the handle method means each invocation of a
service would open a new connection.

Most of the time this is to be avoided, Zato or not. But on the other
hand, if such a service is invoked 6 times a day to, say, process
reports in batches - it would not hurt.

Alternatively, you can use a feature new in 2.0 - server-wide user
context. Basically, it’s a dictionary of arbitrary data that can be
shared across all services on a given server.

It can be used to keep configuration or connections to technologies for
which there is no adapter as of yet, such as MS SQL. Everything that
needs to be shared can be put in there and connections to SQL Server
sound just fine.

You’d then have something like:

def handle(self):
conn =3D self.server.user_ctx[‘My Connection’]

As it happens, I am working on the feature’s documentation exactly as w=
e

speak. I have just added a few introductory words. The chapter will be
done today or tomorrow.

I don’t have any SQL Server handy but if you contact me off-list and ca=
n

share access to a DB instance I can take it as an opportunity to
document the feature using your use-case. That would let you have
working code serving exactly what you need.

Hi there,
Thanks for all your answers

Regards

Un saludo,
–=20

Jorge Herrera Pi=C3=B1ero
Jefe del =C3=81rea de Servicios TIC
Correo: jhpinero@ull.es Web: http://www.stic.ull.es
Servicio de Tecnolog=C3=ADas de la Informaci=C3=B3n y la Comunicaci=C3=B3n =
(STIC)
Universidad de La Laguna (ULL)

=C2=BFNecesita ayuda? Acceda al portal de soporte <http://soporte.ull.es/st=
ic>

[image: Certificado EFQM de Excelencia 300+]
<http://support.webs.ull.es/calidad/CertificadoCEG_UniversidadLaLaguna_STIC=
.pdf>

Le agradecer=C3=ADamos que valorase el servicio que presta el STIC
http://www.ull.es/encuestas/index.php/53427/lang-es

Este mensaje puede contener informaci=C3=B3n confidencial y/o privilegiada.
Si usted no es el destinatario o lo ha recibido por error debe borrarlo
inmediatamente. Est=C3=A1 estrictamente prohibido por la legislaci=C3=B3n v=
igente
realizar
cualquier copia, revelaci=C3=B3n o distribuci=C3=B3n del contenido de este =
mensaje
sin autorizaci=C3=B3n expresa.

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient or have received this e-mail in
error you must destroy it. Any unauthorised copying, disclosure or
distribution
of the material in this e-mail is strictly forbidden by current legislation=
.

2015-02-05 23:40 GMT+00:00 Tim Hoffmn thoffman.rel@gmail.com:

Also the connection string is important.

I found that I need to use autocommit=3DTrue - otherwise you needs commit=
s
all over the place. And you need to enable MARS

DRIVER=3D{ODBC Driver 11 for SQL
Server};SERVER=3D192.168.4.107,14330;DATABASE=3Dtest;UID=3Dtest;PWD=3Dtes=
t123;MARS_Connection=3Dyes

With this in place it works reliably for me.

I have also setup SQL Server Broker Queues to asynchronously call out to
zato.

Zato is it be our integration point between our primary application (whic=
h
has no api ;-( we have to talk to the underlying database and multiple
external services inbound and outbound.

Cheers

Tim

On 6 February 2015 at 07:34, Tim Hoffmn thoffman.rel@gmail.com wrote:

I am using MS SQL server. Calling a stored procedure a few times day.

So I am creating a connection each time. Later I will probably build a
fulltime connection.

I am storing connection strings in the kvdb - the
first insphire:connect:default points the key of the actual connection
string which allows me to swap them easily without redeploying code.

from zato.server.service import AsIs, Boolean, Integer, Unicode, Service
import pyodbcimport urllib

class UpdateEngineHours(Service):

class SimpleIO:
    input_required =3D (Unicode('assetno'), Integer('hours'),
        Unicode('datetime'))

@property
def odbc_connection(self):
    source =3D self.kvdb.conn.get('insphire:connect:default')
    conn_string =3D urllib.unquote(self.kvdb.conn.get(source))
    self.logger.info('connection strng: {}'.format(conn_string))
    connection =3D pyodbc.connect(conn_string, autocommit=3DTrue)
    self.logger.info('connection made: {}'.format(connection))
    return connection

def handle(self):
    self.logger.info('Request: {}'.format(self.request.payload))
    self.logger.info('Request type: {}'.format(type(self.request.pay=

load)))

    with self.odbc_connection as con:
        assetno =3D self.request.input.assetno
        hours =3D self.request.input.hours
        tstamp =3D self.request.input.datetime
        con.execute("exec [rel].add_meter_reading ?,?,?", assetno, h=

ours, tstamp) # lint:ok

    self.logger.info('Request written')

On 6 February 2015 at 03:32, Baad Sequel badsequel@gmail.com wrote:

Which reminds me… I asked about this earlier and Dariusz pointed me in
a direction on how to add at least parts of the MSSQL functionality.
Never got any further as I figured there is so much more I need to lear=
n

about Zato before MSSQL becomes a must-have for me.

I did add pyodbc to Zato 1.1 and it worked. Open/close connection on
each service invocation is a bummer, though…

Details here:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-July/0=
00550.html

Adding msql in the same way mysql was added to 1.1:

https://mailman-mail5.webfaction.com/pipermail/zato-discuss/2014-Novemb=
er/000672.html

Also:
https://github.com/zatosource/zato/issues/86

Maybe something is of use for you among those links. Good luck!

-Bad

On Thu, Feb 5, 2015 at 4:57 PM, Dariusz Suchojad dsuch@zato.io wrote:

On 05/02/15 16:03, Jorge Herrera Pi=C3=B1ero wrote:

I want to establish an SQL Outgoing Connection to a Microsoft
SqlServer. How can I do it?

Where do I create that connection? Directly into the service handler=
?

Hi Jorge,

this is not a built in functionality - you’d need to establish the
connections yourself.

Doing it directly in the handle method means each invocation of a
service would open a new connection.

Most of the time this is to be avoided, Zato or not. But on the other
hand, if such a service is invoked 6 times a day to, say, process
reports in batches - it would not hurt.

Alternatively, you can use a feature new in 2.0 - server-wide user
context. Basically, it’s a dictionary of arbitrary data that can be
shared across all services on a given server.

It can be used to keep configuration or connections to technologies fo=
r

which there is no adapter as of yet, such as MS SQL. Everything that
needs to be shared can be put in there and connections to SQL Server
sound just fine.

You’d then have something like:

def handle(self):
conn =3D self.server.user_ctx[‘My Connection’]

As it happens, I am working on the feature’s documentation exactly as =
we

speak. I have just added a few introductory words. The chapter will be
done today or tomorrow.

I don’t have any SQL Server handy but if you contact me off-list and c=
an

share access to a DB instance I can take it as an opportunity to
document the feature using your use-case. That would let you have
working code serving exactly what you need.

Hi Dariuz,

Is there any further documentation on server-wide user context? We are considering using it to create outgoing sql connection to MSsql.

Thanks.

Keith

Hi @keith,

have a look at this pull request, it’s not merged in yet, but it adds MS SQL connectivity to Zato: