JRuby fails to commit

[javornikolov]

That issue emerged while testing a change in #98.

Java::JavaSql::SQLException: Could not commit with auto-commit set on
oracle.jdbc.driver.PhysicalConnection.commit(oracle/jdbc/driver/PhysicalConnection.java:4439)
oracle.jdbc.driver.PhysicalConnection.commit(oracle/jdbc/driver/PhysicalConnection.java:4486)
java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:606)
RUBY.commit(/home/user/RubymineProjects/ruby-plsql/spec/../lib/plsql/jdbc_connection.rb:65)
RUBY.(root)(/home/user/RubymineProjects/ruby-plsql/spec/plsql/procedure_spec.rb:1848)
ruby.__dash_e__.(root)(-e:1)
ruby.__dash_e__.(root)(ruby/-e:1)

Two observations so far:

• JRuby connections are currently auto-commit by default (that's JDBC default)
• commit is failing when invoked in auto-commit mode. Maybe that's OK, yet it's good to see what's the behavior in case of MRI ruby and ruby-oci8. As per JDBC docs commit, rollback, setAutocommit are not supported in autocommit=true mode.

[javornikolov]

@jgebal, I opened a separate issue for that problem. What version of Oracle JDBC driver have you used when you faced these errors?

[jgebal]

Running with ojdbc6-11.2.0.4
I don't thing I've changed jdbc driver since the last time I've run on JRuby and seen all tests pass.
Ah, now I know I should have CI setup for that ;)
I actually might use my local Jenkins

[javornikolov]

I don't recall which version I used when tests passed for me with JRuby and unfortunately I don't have a db environment to try it out. In any case - I believe we should set autocommit to false by default for new connections (it's a common pattern in Java). commit, rollback, setSavepoint are not supported in autocommit mode (even if some version of driver/client used to tolerate them).

[jgebal]

It's a false alarm.
Something got messed up with my JRuby apparently.
Managed to get my Jenkins to run a job on JRuby and all is good.

[jgebal]

Tested now on:

• ruby-1.9.3
• ruby-2.2.1
• JRuby-1.7.24
• JRuby 9.0.5.0

All good except the tests that are failing #98 addresses this issue

[javornikolov]

Still I have some concerns - would you be able to check, are we running with autocommit true or false when a new connection is opened with JRuby? Also it's interesting what's the case with Oracle 12.1 JDBC driver.

[jgebal]

I've run out of free time today, can check the autocommit later.
I do not have a working instance of 12.1 to play with for now.

[javornikolov]

OK. It's about the client side (ojdbc6.jar, ojdbc7.jar) - the target database doesn't matter so much (can be 10g or 11g too).

Seems in 12c JDBC a change/fix has been introduced to raise an error e.g. on commit in auto-commit mode: http://asanga-pradeep.blogspot.bg/2013/08/javasqlsqlexception-could-not-commit.html

[jgebal]

I just Love those Oracle features that bring no value and force more coding.
I need to check how my IDE is configured and which JDBC driver it's using
with JRuby.
As far as I remember ruby-plsql is not having autocommit enabled by default
but there are some tests that do some validation around it.
With 12c JDBC in prospect, the code will need to capture those exceptions.
If we want to be transparent and let developers worry, just a review of
tests should do.
On 21 Feb 2016 17:06, "Yavor Nikolov" notifications@github.com wrote:

OK. It's about the client side (ojdbc6.jar, ojdbc7.jar) - the target
database doesn't matter so much (can be 10g or 11g too).

Seems in 12c JDBC a change/fix has been introduced to raise an error e.g.
on commit in auto-commit mode:
http://asanga-pradeep.blogspot.bg/2013/08/javasqlsqlexception-could-not-commit.html

—
Reply to this email directly or view it on GitHub
#100 (comment).

[javornikolov]

Actually I think might be good feature in our case since it points out some issues with our implementation:

• In terms of testing: auto-commit == true is in general a bad idea since leads to excessive side effects. (Rollback is not enough to tear down the test run, other sessions see the committed changes, etc.)
• Connection behavior is not consistent (depending on ruby flavor /java-based or not/)

Maybe we can make commit, rollback, etc. no-op in case of autocommit true. I'm not so sure about this as it hides issues like the above-mentioned ones. Yet, it's maybe useful as would allow executing same code no matter of the autocommit mode.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.