New feature request: ODBC connection support with itoolkit_lite

[kadler]

Original report by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

I would like the following code to work:

#!python
import pyodbc
from itoolkit import *
from itoolkit.db2.idb2call import *

conn = pyodbc.connect('DRIVER={IBM i Access ODBC Driver};SYSTEM=system.mydomain.com;UID=MYUSER;PASSWORD=PASSWORD')
itransport = iDB2Call(conn)

However, the idb2call import fails with:

ModuleNotFoundError: No module named 'ibm_db'

Which makes sense. We'd depend on ibm_db is there to support calls where the toolkit fetches the connection for you, like so:

#!python
itransport = iDB2Call(user,password)

As a test, I installed ibm_db (not easy) and used this variation of iDB2Call

#!python
itransport = iDB2Call(conn)

Resulting error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python\Python36\lib\site-packages\itoolkit\itoolkit.py", line 1098, in call
    xml_out = itrans.call(self)
  File "C:\Python\Python36\lib\site-packages\itoolkit\db2\idb2call.py", line 131, in call
    stmt = ibm_db.prepare(conn, sql)
Exception: Supplied connection object Parameter is invalid

Inspecting the code, it looks like we are using the ibm_db support for prepared statements. Since we have a connection object already, I suspect everything can be accomplished with the PEP0249 spec in such a way that any database connection would work.

Of course, it would impact existing design a bit. My personal preference would be to always require the user to fetch a PEP0249 connection object as step 1.

If we had this feature, we could remotely connect to IBM i via ODBC without requiring the extra steps of XMLService setup. It would still remain generic enough for local DB2 calls to work or ibm_db calls from off-box, including IBM BlueMix. I am unconvinced that performance would improve.

[kadler]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

Closing, as this has been added with the iODBCCall class

[kadler]

Original comment by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

So, you can re-fork or talk with Aaron to get "On The Plan" so you can edit this master directly.

@ThePrez, you are now an admin.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Ops, I see you already forked. So, you can re-fork or talk with Aaron to get "On The Plan" so you can edit this master directly.

BTW -- I would add you to list of admins, but i can't recall how in bitbucket (shameful, but moving on).

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

I like the idea of a separate transport. Let's do that. You can assign ownership to me. I'd like to do this one.

ok, i started odbc driver transport in directory itoolkit/odbc (now commit in repository). Have fun.

[kadler]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

I like the idea of a separate transport. Let's do that. You can assign ownership to me. I'd like to do this one.

[kadler]

Original comment by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

Jesse, Please use new transport class iODBCCall. Do you just want me to do iODBCCall for you quickly? Do you want to do it yourself for fun???

[kadler]

Original comment by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

Forgot to include the PEP0249 spec link for convenience. https://www.python.org/dev/peps/pep-0249/

I suspect we really want to just go straight to the cursor execute() function.

I'd gladly take a stab at the code if we agree this is a suitable design change, but it would be in January sometime.