I am currently using ODBC to access an IBM as400 machine through Rails -> (small as400 odbc adapter) -> odbc_adapter (gem 4.2.4) -> ruby-odbc (gem 0.999991) -> unixODBC (2.3.4, ubuntu 18.04) -> IBMiAccess (latest). By some miracle, this all works pretty well, except for recently we were having problems with strings containing specific characters causing an error to be raised in ruby-odbc.
Retrieving a record with the special character '?' fails with:
ActiveRecord::StatementInvalid (ArgumentError: negative string size (or size too big): SELECT * from data WHERE id = 4220130.0)
Seems the special character ends up taking up 2 bytes and whatever conversions are happening don't handle this correctly.
Strings without special characters are being returned with encoding Encoding:ASCII-8BIT.
There is a utf8 version of ruby-odbc, which I was able to load by requiring it in our iSeries adapter, and then requiring odbc_adapter:
require 'odbc_utf8' # force odbc_adapter to use the utf8 version
require 'odbc_adapter'
This allows the utf8 version of odbc-ruby to occupy the ODBC module name, which the odbc_adapter will just use. Though there was a problem:
odbc_adapter calls .get_info for a number of fields on raw_connection (odbc-ruby), and these strings come back wrong, for example the string "DB2/400 SQL" which is from ODBC::SQL_DBMS_NAME looks like: "Dx00Bx002x00/x004x000x000x00 x00Sx00Qx00Lx00", with an encoding of Encoding:ASCII-8BIT. odbc_adapter uses a regex to map dbms to our adapter, which doesn't match: /DB2/400 SQL/ =~ (this_string) => null.
I'm not super familiar with string encodings, but was able to hack in a .gsub("", "") here to fix this detection. After this, I can return records with special characters in their strings. They are returned without error, with visible special characters in Encoding:UTF-8.
Of course, now querying on special characters fails:
ActiveRecord::StatementInvalid (ODBC::Error: HY001 (30027) [IBM][System i Access ODBC Driver]Memory allocation error.: SELECT * from data WHERE (mystring like '%?%'))
but I'm not too concerned with that. The problem now is that it seems the UTF8 version of ruby-odbc sets the ODBC version to 3, where on the non-utf8 version it was 2:
Base.connection.raw_connection.odbc_version => 3
And this seems to prevent autocommit from working (works on version 2):
Base.connection.raw_connection.autocommit => true
Base.connection.raw_connection.autocommit = false
ODBC::Error (IM001 (0) [unixODBC][Driver Manager]Driver does not support this function)
This function is used to start/end transactions in the odbc_adapter, and seems to be a standard feature of odbc:
https://github.com/localytics/odbc_adapter/blob/master/lib/odbc_adapter/database_statements.rb#L51
I poked around in the IBMiAccess documentation, and found something about transaction levels and a new "trueautocommit" option, but I can't seem to figure out if this trueautocommit replaces autocommit, or even if autocommit is no longer supported in this way.
https://www.ibm.com/support/pages/ibm-i-access-odbc-commit-mode-data-source-setting-isolation-level-and-autocommit
Of course I have no idea of how to set this new 'trueautocommit' connection setting via the ruby-odbc gem. It does support .set_option on the raw_connection, so I can call something like .set_option(ODBC::SQL_AUTOCOMMIT, false), which fails in exactly the same way. ODBC::SQL_AUTOCOMMIT is just a constant for 102, which I've found referenced in a few places regarding ODBC, so I figure if I could figure out the constant for TRUEAUTOCOMMIT, I might be able to set it in this way, but can't find any documentation for this.
Any advice for getting .autocommit working in this configuration?
Edit: Apparently you can use a DSN for odbc, so I've also tried creating one in /etc/odbc.ini, along with the option for "TrueAutoCommit = 1" but this hasn't changed anything as far as getting .autocommit to work.
question from:
https://stackoverflow.com/questions/66050908/autocommit-odbc-api-not-working-through-ibm-iaccess-to-unixodbc-to-ruby-odbc 
