ODBC Support Added To OIE

published by whitemice on Thu, 12/28/2017 - 09:43

As of OpenGroupware Coils 0.1.49r112 support for ODBC data sources has been integrated into OIE. These SQL data sources are defined in the OIESQLSources just as PostgreSQL and Informix database connections are. This feature requires the pyodbc module to be installed. The availability of this module on your workflow node can be verified using the coils-dependency-check tool.

[ogo@workflow.coils.example.com ~]# coils-dependency-check 
OK: Module markdown (Markdown rendering, required for /wiki protocol) available.
OK: Module vobject (vCard and vEvent parsing) available.
OK: Module zope.interface (ZOPE Interfaces for RML engine) available.
OK: Module xlrd (XLS<2007 read support) available.
OK: Module pycups (IPP printing support) available.
OK: Module paramiko (SSH suppport.) available.
OK: Module dateutil (Date & Time Arithmatic) available.
OK: Module lxml (SAX & DOM XML Processing) available.
OK: Module Pillow (Python Imaging Library) available.
OK: Module psycopg2 (PostgreSQL RDBMS connectivity) available.
OK: Module base64 (Encode and decode Base64 data) available.
OK: Module yaml (YAML parser & serializer) available.
OK: Module pyodbc (ODBC SQL connectivity) available.   <<<<<<<<<<
OK: Module xlwt (XLS<2007 write support) available.
OK: Module sqlalchemy (Object Relational Modeling) available.
OK: Module pytz (Python Time Zone tables) available.
OK: Module smbc (SMB/CIFS integration) available.
OK: Module argparse (Enhanced argument parsing, required for /wiki protocol) available.
OK: Module ijson (Streaming JSON parser, requires libyajl) available.
OK: Module z3c.rml (RML Generator, also requires "zope.interface") available.
OK: Module coils.foundation.api.elementflow (Streaming XML Creation) available.
OK: Module coils.foundation.api.pypdf (Simple PDF Operations) available.
OK: Module untangle (XML parsing) available.
OK: Module gnupg (GPG/PGP suppport.) available.
OK: Module informixdb (Informix RDBMS connectivity) available.

The principle use for the ODBC connection is to connect to M$-SQL database engines. In order to make ODBC connections the proper ODBC driver must be installed on the node and properly configured.

ODBC database connections are defined in the OIESQLSources configuration directive just as with PostgreSQL and Informix database connections. The driver must be "odbc" and the parameter "DSN" be the complete ODBC connection string.

coils-server-config --directive=OIESQLSources  --value='{
....
  "acumaticaMVP1": {"driver": "odbc",
                    "DSN": "DSN=AcumaticaDB;UID=oie-workflow-account;PWD=XXXXXXXXXXXXXXXXXXXXXXXXXX"}
}'

A defined connection can be tested using coils-test-sql utility. This test is best performed form the node hosting the coils.workflow.executor component.

[ogo@workflow.coils.example.com ~]# coils-test-sql --name=acumaticaMVP1 --table=ARInvoice
Store root is /var/lib/opengroupware.org
Connected to SQL "acumaticaMVP1"
  Select Table: "ARInvoice"

If the connection works then it is ready to be used from workflow actions like sqlSelectAction and sqlExecuteAction.

Tags:

pyodbc