ibm pyodbc connection to mssql

[Jeff Goltz]

Anyone have any framework/simple example of using pyodbc to connect from the IBM i to a MSSQL database?

Seeing alot of examples of windows to ibmi, nothing the other direction. Guessing we probably need to create a DSN file/entry somewhere…

‌

import pyodbc
connString = "Server=MYSERVER; Database=MYRDB; User Id=MYUSERID; Password=MYPWD";
connObj = pyodbc.connect( connString )

‌

pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (
0) (SQLDriverConnect)')

‌

[Jesse G]

As far as I know, there is no way to connect to an MS SQL database from Python on IBM i. You’d need to have an ODBC driver for MS SQL built on IBM i, or build the pymssql (which probably also needs an ODBC driver).

‌

If you’re willing to explore Node.js, the mssql module builds “out of the box.” While I don’t know of anyone who has explicitly validated it from IBM i, it should work.

‌

[Jesse G]

There’s also the JDBC path, which should work. Just not with Python, of course.

[Jesse G]

Thanks for opening this inquiry, but unfortunately the exact requirements suggested cannot be feasibly implemented.

[Kevin Adler]

I’d also add that generally, this issue tracker is not the right place for questions of “how do I do this?” but rather for issues specifically with RPMs delivered via the IBM yum repository. For general open source on IBM i questions, a better avenue would be our Ryver forums where there is a dedicated Python channel. There is also the Midrange Open Source mailing list.

[Kevin Adler]

FYI, here’s the Python Wiki about various connection options from Python to MS SQL Server: https://wiki.python.org/moin/SQL Server

Basically the options boil down to pymssql or pyodbc, both of which require FreeTDS http://www.freetds.org/. pymssql seems discontinued, but they also mention ctds as an alternative (still needs FreeTDS, though). You can try building FreeTDS yourself and getting it to work or collaborate with the community to provide as well. If this is something you’d like to see IBM deliver, please open an RFE.

[Kevin Adler]

FYI, we’ve now shipped FreeTDS as an rpm. You can install the ODBC driver via yum install freetds-odbc.