Errors using JayDeBeApi

[mikrom-work]

• itoolkit version: 1.7.0
• Python version:
• OS Name and Version: Windows 10
• IBM i version: V7R3
• XMLSERVICE version:

Describe the bug

I'm using JayDeBeApi 1.2.3 and jt400.jar with itoolkit 1.7.0 and tried executing some commands on IBM i using database transport.
I got following errors:

  File "C:\Python35\lib\site-packages\itoolkit\itoolkit.py", line 1173, in call
    xml_out = itrans.call(self)
  File "C:\Python35\lib\site-packages\itoolkit\transport\base.py", line 47, in call
    return self._call(tk)
  File "C:\Python35\lib\site-packages\itoolkit\transport\database.py", line 79, in _call
    return "".join(row[0] for row in cursor).rstrip('\0')
TypeError: 'Cursor' object is not iterable
Exception ignored in: >
Traceback (most recent call last):
  File "C:\Python35\lib\site-packages\itoolkit\transport\base.py", line 21, in __del__
  File "C:\Python35\lib\site-packages\itoolkit\transport\base.py", line 69, in close
  File "C:\Python35\lib\site-packages\itoolkit\transport\database.py", line 82, in _close
  File "C:\Python35\lib\site-packages\jaydebeapi\__init__.py", line 437, in close
jpype._core.JVMNotRunning: Java Virtual Machine is not running

To eliminate the errors I changed the lines from the source file itoolkit\transport\database.py as follows
For the error 'Cursor' object is not iterable I changed in the _call method the line 79
from

        return "".join(row[0] for row in cursor).rstrip('\0')

to

        return "".join(row[0] for row in cursor.fetchall()).rstrip('\0')

For the other error I changed in the _close method the line 82
from

        self.conn.close()

to

        try:
          self.conn.close()
        except:
          pass

Now JayDeBeApi works with itoolkit without errors.
I tested my changes with pyodbc too and it works as well as before (without changes).
Is it possible to include these changes in itoolkit so that the database transport also works with JayDeBeApi?

Attached is the original file and the file with proposed changes.
database_transport_changes.zip

[kadler]

Is there a specific reason you're bridging to Java and using jt400 instead of using the IBM i Access ODBC driver? Seems like a lot of extra complications.

[mikrom-work]

Some time ago ibm_db driver worked for me only when running python program locally on IBM i. It didn't work when running python script from Windows and Linux. To make the python scripts multi platform I used several drivers: on windows I used pyodbc and on linux I had problems installing ODBC and so I used JayDeBeApi with jt400.jar. The function for getting connection to iSeries I'm using everywhere looks like this:

def get_db_connection(csebk, userid, pwd):
  ### Using ibm_db_dbi on iSeries or pyodbc on PC
  # both implement Python Database API Specification v2.0
  # https://www.python.org/dev/peps/pep-0249/

  import platform
  running_platform = platform.system()
  if running_platform == "OS400":
    import ibm_db_dbi
    connection = \
      ibm_db_dbi.connect("DATABASE=*LOCAL")
    #connection = \
    #  ibm_db_dbi.connect(database="*LOCAL",user=userid,password=pwd)  
  elif running_platform == "Windows":
    import pyodbc
    connection = pyodbc.connect(driver='{iSeries Access ODBC Driver}',
    system=csebk,uid=userid,pwd=pwd)
  elif running_platform == "Linux":
    import jaydebeapi
    connection = jaydebeapi.connect(
      "com.ibm.as400.access.AS400JDBCDriver", 
      "jdbc:as400://%s" % csebk, [userid , pwd], "jt400.jar")
  else:
    print("Error: Cannot run on platform \"%s\" !" % running_platform)
    import sys
    sys.exit()
  # return connection
  return running_platform, connection

The fact is that the installation of JayDeBeApi in linux is very simple and it works with jt400.jar as well on linux as on windows.

I thought that the two proposed changes would not cause any complications, at least that didn't happen to me when I tested it.

[kadler]

Yeah, the changes look fine. I was just curious.

FYI, it's recommended to use the "IBM i Access ODBC Driver" name instead of the "iSeries Access ODBC Driver", since the latter is deprecated.

[mikrom-work]

OK, I will try the "IBM i Access ODBC Driver"

[jkyeung]

Minor comments on the proposed changes:

Re: explicitly fetching using .fetchall()

Sounds good to me. I'm presuming the amount being fetched is always, or nearly always, something reasonable to fetch all at once.

Re: surrounding close in try..except

I think we can follow the usual recommendation to avoid using bare except. In this case, I would use except RuntimeError since that is what jpype._core.JVMNotRunning derives from. I imagine that would also capture most sane errors during closing. At worst, we could do except Exception; but I would go with RuntimeError until someone demonstrates that this isn't broad enough.

[kadler]

Re: explicitly fetching using .fetchall()

Sounds good to me. I'm presuming the amount being fetched is always, or nearly always, something reasonable to fetch all at once.

We already loop over every record in the cursor using a list comprehension, so calling fetchall() doesn't change the end result.

The differences is that now we're fetching them all at the start of the comprehension instead of during the comprehension. This could affect some queries, where you only care about a single field in the result set and throw the rest away - in that case you'd be buffering all fields for all rows instead of just one row at a time. However, for XMLSERVICE it only returns one field anyway, so that doesn't matter. In addition, the number of rows it returns is limited by the size of the array result set embedded in the RPG program, which is of course limited by the size of a space pointer (so ~16MiB).

[mikrom-work]

Re: explicitly fetching using .fetchall()

of course it could be done without fetchall() too, i.e. with fetchone(), but it won't look so nice - for example:

        rows_0 = []
        while True:
            row = cursor.fetchone()
            if row == None:
                break
            else:
                rows_0.append(row[0])
        # join rows[0] list elements
        return "".join(rows_0).rstrip('\0')

[kadler]

I've created 1.7.1-rc if you want to test https://pypi.org/project/itoolkit/1.7.1rc0/