SqlAlchemy hosted on the IBMi
MarioAquino-MAQU opened this issue · 10 comments
Hello,
@naveen-ram introduced me to this package, I'm a fan of this initiative. Composing querys with an ORM and a builder pattern is a great, clean way to write code. And there aren't that many packages out there which support the IBM DB2 database.
On my local machine (Windows) this package worked like a charm from the first time. I have changed the database of my application from mysql to DB2 and the only thing I had to change was my "SQLALCHEMY_DATABASE_URI" in my config-file and install the package Sqlalchemy-IBMI.
Now I'm trying to host this application on the IBM i as well. But I get a connection error, this seems weird to me since I do use the "IBM i Access ODBC driver" in both environments.
Connection-string:
SQLALCHEMY_DATABASE_URI = "ibmi://<user>:<password>@<host>/<database>?current_schema=<schema>"
Error I get on the server (works perfectly on my local host)
sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('28000', '[28000] CWBNL0202 - cwbodmsg.dllCWBNL0202 - cwbodmsg.dllCWBNL0202 - cwbodmsg.dll comm rc=8015 - , Password length = 0, Prompt Mode = Never, System IP Address = 127.0.0.1 (8015) (SQLDriverConnect)')
To me it seems like the username and password are not passed to the database. I enabled tracing in odbcinst.ini and the logged connection-string seems ok:
[ODBC][34083][1638524004.593711][SQLDriverConnectW.c][290]
Entry:
Connection = 188c66cd0
Window Hdl = 0
Str In = [Driver={IBM i Access ODBC Driver}; UNICODESQL=1; TRUEAUTOCOMMIT=1;;database=<database>;uid=<user>;pwd=<password>;Naming=0;System=<host>...][length = 164 (SQL_NTS)]
Str Out = 0
Str Out Max = 0
Str Out Ptr = 0
Completion = 0
Any idea how I can solve this? Or suggestions what I could try to get more information concerning this problem?
Thank you in advance!
There's a bug in the most recent driver version which causes the connection string arguments to be case-sensitive. I'm guessing that's the problem here :(
Yikes... Which version of the IBM i Access ODBC driver do you recommend using? The one installed on my IBMi is 1.1.0.15.
That's the affected one. You could try downgrading to 1.1.0.14 or wait until next version is out (or open a ticket to get a test fix).
I'm not able to acquire an older version of the driver here: https://www.ibm.com/resources/mrs/assets/DownloadList?source=swg-ia&lang=en_US
So I raised a support ticket. I will keep you updated.
Thank you for the support this far @kadler
@kadler I was able to test with an older ODBC driver (1.1.0.13) and it worked. Are you sure IBM will remove the case-sensitivity in the next releases?
For anyone else reading this and IBM i Access ODBC Driver version 1.1.0.15 is still the most recent version of the ODBC driver. Use an older one!
IBM i Access ODBC Driver version 1.1.0.15 is still the most recent version of the ODBC driver.
There is, as far as I can tell, still no official place (archive) to download an older version.
As @MarioAquino-MAQU notes, https://www.ibm.com/support/pages/ibm-i-access-client-solutions only has .15...
And is really annoying on top, as it requires a login which prevents easy use in a dockerfile.
@kadler : I am weary about Liam's public access downloadable driver, are we allowed to do that?
If so, I could solve my docker issue as I have all versions locally, and could easilly host those for use in docker, same as some IBM people do right here on github with openshift and Db2 for i example code... But when I asked them point blank at common eu last year, if that was legal, they said no and stated they would delete the driver from their repo, but so far havn't...
Still, getting a .16 release that works, maybe even a .17 that works with a recent version of sqlalchemy ( #133 ), would be nice.
Is this something for @markdirish to look at?
There are definitely licensing issues with the ODBC driver, which will hopefully get straightened out soon.
In addition, the changes are already queued for the next service pack which will be sometime in Q2 (as much as I can say right now). Unfortunately, the service pack schedules have been moved around so there was no fall service pack last year, which would have included this fix.
If you need a fix sooner than the next service pack, please open a ticket with service and we can provide an interim fix.
FYI, I have published 0.9.2 which includes code to map the SQLAlchemy keywords to the keywords that work with the buggy driver: https://pypi.org/project/sqlalchemy-ibmi/0.9.2/
Please let me know if this works for you.
@kadler Thanks, I'll have a look and report back.
Assuming this is fixed and closing. If there's still issues, please re-open.