Insert into View with Insert-Trigger

[andfx]

Hi,

I'm on a legacy Oracle database and need to insert into views which have an insert trigger to handle it. This doesn't seem to be possible since 1.3.x due to the fact that the RubyJdbcConnection forces the insert-statements to return the generated keys:

    @JRubyMethod(name = "execute_insert", required = 1)
    public IRubyObject execute_insert(final ThreadContext context, final IRubyObject sql)        throws SQLException {
        final String query = sql.convertToString().getUnicodeValue();
        return executeUpdate(context, query, true);
    }

The third parameter in #executeUpdate sets returnGeneratedKeys to true. Thus the statement is executed with Statement.RETURN_GENERATED_KEYS, see https://github.com/jruby/activerecord-jdbc-adapter/blob/master/src/java/arjdbc/jdbc/RubyJdbcConnection.java#L665.

When trying to insert into the view, a ORA-22816 is thrown.

Can we handle the requirement of a returned generated key in a better way?

Regards.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/1915544-insert-into-view-with-insert-trigger?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github).

[kares]

yes, we will need to work on refactoring that part (it affects all not just Oracle) ... feel free to go for it!

[kares]

tried reproducing but failed - all seems fine ;( ... would be great if ORA issues got reproducable samples. anyway, I think it's possible to work-around the issue if a primary_key= is correctly set for the view record class by using insert_returning: true in the database configuration. hope it helps!

[kares]

insert_returning: true actually was not working ... but should be now 7b6432546bcb9136a3 there's also an option to turn generated keys off ... but it's preferable to try insert_returning: true

[andfx]

Hi kares,

thanks for your work. I just tried your changes an the error still occurs. In my opinion the error is happening because the JDBC Adapter is using the RETURNING-Clause even if insert_returning is false.

Here is the stacktrace:

activerecord-jdbc-adapter-7b6432546bcb/lib/arjdbc/jdbc/RubyJdbcConnection.java:597:in `execute_insert`
activerecord-jdbc-adapter-7b6432546bcb/lib/arjdbc/jdbc/adapter.rb:448:in `exec_insert`
activerecord (3.2.16) lib/active_record/connection_adapters/abstract_adapter.rb:280:in `log`
activesupport (3.2.16) lib/active_support/notifications/instrumenter.rb:20:in `instrument`
activerecord (3.2.16) lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log`
activerecord-jdbc-adapter-7b6432546bcb/lib/arjdbc/jdbc/adapter.rb:448:in `exec_insert`
activerecord-jdbc-adapter-7b6432546bcb/lib/arjdbc/oracle/adapter.rb:584:in `exec_insert`
activerecord-jdbc-adapter-7b6432546bcb/lib/arjdbc/oracle/adapter.rb:574:in `insert`
activerecord (3.2.16) lib/active_record/relation.rb:66:in `insert`

As described in the initial comment the #execute_insert methods of the RubyJdbcConnection always set returnGeneratedKeys of #executeUpdate to true. Therefore every Insert Statement is called with Statement.RETURN_GENERATED_KEYS which produces the ORA-issue.

So there is no way to work-around this at the moment...

[kares]

@andfx first of all thanks, second maybe you really need to look into it than. the problem in resolving this is I was unable to reproduce so it must be something about Oracle/Driver version (or configuration) you're using. which in case of the DB version, I'm not that into doing an Oracle install (again) unless I'm paid by the hour :) to do so ... if the driver does not support generated keys so far what I've seen (including Oracle's thin JDBC4 driver 11.1.07.0) simply return nothing or non-sense while trying to extract the keys result set.

as for the insert_returning: false part ... seems working just fine at my end as well ;( ... so I'm really not sure why that would behave differently ... maybe share your configuration with us and what adapter.use_insert_returning? is giving you - thanks again and I'm sorry I could not help it this time.

[andfx]

@kares Thanks for your feedback!

I'll have a look at this myself in the next days and post an update.

Keep up the good work!

Regards