How can I call a DB2 Stored Procedure with OUT params using ActiveRecord?

Question

I need to call some legacy stored procedures using ActiveRecord.

I can call procedures using ActiveRecords when they have no parameters or only IN params.

e.g.

ActiveRecord::Base.connection.execute('call legacy_proc')

or

ActiveRecord::Base.connection.execute("call legacy_proc('param1', 'param2')")

The above works perfectly fine. Now, the problem arises when I need to call a procedure with OUT params. I tried different ways of calling the SP but I can't get it to work.

 ActiveRecord::Base.connection.execute("call legacy_proc_with_out_params('param1', 'param2', ?, ?)")

 ActiveRecord::Base.connection.execute("call legacy_proc_with_out_params('param1', 'param2', null, null)")

 ActiveRecord::Base.connection.execute("call legacy_proc_with_out_params('param1', 'param2', '', '')")

None of the above work (the last two params are OUT params). As a last option, I could even skip reading the OUT params if that's really not possible, as long as I can at least execute the procedure.

EDIT: adding the error details in order to clarify "not working".

 ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-313, SQLSTATE=07004, SQLERRMC=null, DRIVER=4.15.113: call legacy_proc_with_out_params('param1', 'param2', ?, ?)
    from arjdbc/jdbc/RubyJdbcConnection.java:547:in `execute'
    from /home/devusr.gem/gems/activerecord-jdbc-adapter-1.3.16/lib/arjdbc/jdbc/adapter.rb:581:in `_execute'
    from /home/devusr.gem/gems/activerecord-jdbc-adapter-1.3.16/lib/arjdbc/jdbc/adapter.rb:557:in `execute'
    from /home/devusr.gem/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `log'
    from /home/devusr.gem/gems/activesupport-4.1.8/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /home/devusr.gem/gems/activerecord-4.1.8/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
    from /home/devusr.gem/gems/activerecord-jdbc-adapter-1.3.16/lib/arjdbc/jdbc/adapter.rb:557:in `execute'
    from (irb):30:in `evaluate'
    from org/jruby/RubyKernel.java:1119:in `eval'
    from org/jruby/RubyKernel.java:1519:in `loop'
    from org/jruby/RubyKernel.java:1282:in `catch'
    from org/jruby/RubyKernel.java:1282:in `catch'
    from /home/devusr.gem/gems/railties-4.1.8/lib/rails/commands/console.rb:90:in `start'
    from /home/devusr.gem/gems/railties-4.1.8/lib/rails/commands/console.rb:9:in `start'
    from /home/devusr.gem/gems/railties-4.1.8/lib/rails/commands/commands_tasks.rb:69:in `console'
    from /home/devusr.gem/gems/railties-4.1.8/lib/rails/commands/commands_tasks.rb:40:in `run_command!'
    from /home/devusr.gem/gems/railties-4.1.8/lib/rails/commands.rb:17:in `(root)'
    from org/jruby/RubyKernel.java:1083:in `require'

EDIT 2: tried @mustaccio's suggestion (i.e. use the prepare method through raw_connection, but the DB2 adapter does not implement that method. If there's an alternative path to an implementation of a similar method in DB2, this would definitely resolve the issue.

I also forgot to mention that I'm using jRuby. Therefore, I'm using the jdbc adapter for DB2.

 irb(main):049:0> ActiveRecord::ConnectionAdapters.constants.grep /DB2/
 => [:DB2Column, :DB2JdbcConnection]

 irb(main):050:0> ActiveRecord::Base.connection.raw_connection.methods.sort.grep /prep/
 => []

 irb(main):051:0>  ActiveRecord::Base.connection.raw_connection.prepare
 NoMethodError: undefined method `prepare' for #<ActiveRecord::ConnectionAdapters::DB2JdbcConnection:0xe6bc1cd0>
    from (irb):51:in `evaluate'
    from org/jruby/RubyKernel.java:1119:in `eval'
    from org/jruby/RubyKernel.java:1519:in `loop'
    from org/jruby/RubyKernel.java:1282:in `catch'
    from org/jruby/RubyKernel.java:1282:in `catch'
    from /home/srpec/.gem/gems/railties-4.1.8/lib/rails/commands/console.rb:90:in `start'
    from /home/srpec/.gem/gems/railties-4.1.8/lib/rails/commands/console.rb:9:in `start'
    from /home/srpec/.gem/gems/railties-4.1.8/lib/rails/commands/commands_tasks.rb:69:in `console'
    from /home/srpec/.gem/gems/railties-4.1.8/lib/rails/commands/commands_tasks.rb:40:in `run_command!'
    from /home/srpec/.gem/gems/railties-4.1.8/lib/rails/commands.rb:17:in `(root)'
    from org/jruby/RubyKernel.java:1083:in `require'
    from bin/rails:4:in `(root)'

Answer 1

The solution I found is to use a BEGIN-END block and declare dummy variables to bind to the OUT params.

Given this example:

ActiveRecord::Base.connection.execute("call legacy_proc_with_out_params('param1', 'param2', ?, ?)")

Where the first OUT param is INTEGER and the second is a SMALLINT, the complete call looks like this:

 beginend_stmt = "BEGIN
                      DECLARE out_Param1 INTEGER;
                      DECLARE out_Param2 SMALLINT;

                      call legacy_proc_with_out_params('param1', 'param2', out_Param1, out_Param2);
                  END"

  ActiveRecord::Base.connection.execute beginend_stmt