SQL Server + Zato

sqlserver.txt

  I have solved the problem by modifying these lines in zato. I do not know if this has been the best method , but it has worked to give ZATO connectivity with SqlServer.

  First of all , I installed and configured FreeTDS and unixODBC (http://blog.tryolabs.com/2012/06/25/connecting-sql-server-database-python-under-ubuntu/)
  Then:

In /opt/zato/2.0.0/local/zato-common/src/zato/common/init.py,
  add 'mssql+pyodbc': 'SELECT 1',
  to var: ping_queries

In /opt/zato/2.0.0/zato-web-admin/src/zato/admin/zato_settings.py,
  add 'mssql+pyodbc': 'MS SQL Server',
  to var: engine_friendly_name

  add 'mssql+pyodbc': 'MS SQL Server',
  to var: engine_friendly_name

  add 'mssql':'mssql+pyodbc',
  to var: django_sqlalchemy_engine

So I had to correct errors in web_admin interface because zato not expect a null db_name:

In  /opt/zato/2.0.0/zato-server/src/zato/server/service/internal/outgoing/sql.py
  modify:
    output_required = ('id', 'name', 'is_active', 'cluster_id', 'engine', 'host', Integer('port'), 'username', Integer('pool_size'))
    output_optional = ('db_name', 'extra',)

In /opt/zato/2.0.0/local/zato-web-admin/src/zato/admin/static/js/outgoing/sql.js
  modify this line in ready function:
    $.fn.zato.data_table.setup_forms(['name', 'username', 'engine', 'host', 'port', 'pool_size']);

And finally:
In /opt/zato/2.0.0/zato-web-admin/src/zato/admin/web/forms/outgoing/sql.py
  Modify in CreateForm Class:
    db_name = forms.CharField(required=False, widget=forms.TextInput())
  And add in Editform Class:
    db_name = forms.CharField(required=False, widget=forms.TextInput())

Thanks for all !!
Cheers..

It looks by the content that the intention is to enable outgoing connection possibility for MSSQL and not to use MSSQL as a database for the cluster configuration, right?

To avoid the error message :

DBAPIError: (Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

I did this modification In file

/opt/zato/2.0.8/zato-server/src/zato/server/connection/sql.py

in import section add :

import urllib
import re

in function SQLConnectionPool change :

self.engine = create_engine(engine_url, **_extra)
with :

        if 'mssql+pyodbc' in engine_url:   #engine_url is of the form : mssql+pyodbc://uid:pwd@server:port/db
            [empty, notused, uid, pwd, server, port, db, empty2]= re.split("(.*)://(.*):(.*)@(.*):(.*)/(.*)", engine_url)

            quoted = urllib.quote_plus("DRIVER=FreeTDS;Server={0};Port={1};UID={2};PWD={3};Database={4};TDS_Version=8.0;".format(server, port, uid, pwd, db))
            self.engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
        else:
            self.engine = create_engine(engine_url, **_extra)