Execute stored procedures with output parameter

[DavDavid]

Is it possible execute a stored procedure that has output parameters? I have tried:

String result = new JdbcSession(datasource)
  .sql("call procedure('paramIn', 'paramOut')")
  .update(new SingleOutcome<>(String.class)
;

but doesn't work. It returns a java.sql.SQLException: ORA-06577.

---

• 51-55acd5db/JdbcSession.java:141-143: Refactor this class to avoid... #56 (by )

[dmarkov]

@yegor256 please do something about it, see par.21

[yegor256]

@DavDavid we don't support that now. we should create an instance of CallableStatement and then do this:

stmt.registerOutParameter(1, Types.VARCHAR);

We don't do this now. I don't know how exactly to extend our library to support that... Let's think

[yegor256]

@DavDavid maybe you have a suggestion?

[dmarkov]

@amihaiemil please help, the task is yours

[amihaiemil]

@DavDavid Can you try to see if this works?

String result = new JdbcSession(datasource)
  .sql("call procedure('paramIn', ?)")
  .prepare(
      new Preparation() {
          @Override
          public void prepare(PreparedStatement stmt) throws SQLException {
              CallableStatement cstmt = (CallableStatement) stmt;
              cstmt.registerOutParameter(1, Types.VARCHAR);                 
          }
      }
  )
  .update(new SingleOutcome<>(String.class);

[DavDavid]

It gives me a ClassCastException in CallableStatement line:
java.lang.ClassCastException: oracle.jdbc.driver.OraclePreparedStatementWrapper cannot be cast to java.sql.CallableStatement
at com.simula.Simulation$1.prepare(Simulation.java:167)
at com.jcabi.jdbc.JdbcSession.configure(JdbcSession.java:440)
at com.jcabi.jdbc.JdbcSession.run(JdbcSession.java:377)
at com.jcabi.jdbc.JdbcSession.update_aroundBody14(JdbcSession.java:304)
at com.jcabi.jdbc.JdbcSession$AjcClosure15.run(JdbcSession.java:1)
at org.aspectj.runtime.reflect.JoinPointImpl.proceed(JoinPointImpl.java:149)
at com.jcabi.aspects.aj.MethodLogger.wrap(MethodLogger.java:212)
at com.jcabi.aspects.aj.MethodLogger.ajc$inlineAccessMethod$com_jcabi_aspects_aj_MethodLogger$com_jcabi_aspects_aj_MethodLogger$wrap(MethodLogger.java:1)
at com.jcabi.aspects.aj.MethodLogger.wrapClass(MethodLogger.java:140)
at com.jcabi.jdbc.JdbcSession.update(JdbcSession.java:304)
at com.simula.Simulation.main(Simulation.java:161)

[amihaiemil]

@DavDavid right, i'm going to make an implementation for this requirement, this weekend :D

[amihaiemil]

@dmarkov I created PR #53 for this issue.

[amihaiemil]

@DavDavid I created a PR for this. It will take a couple of days until it is reviewed and merged. Please don't switch off notifications from this issue, as you will have to close it in the end (that's how we work here). Many thanks!

[dmarkov]

@dmarkov I created PR #53 for this issue.

@amihaiemil thanks for that

[amihaiemil]

@dmarkov I need more time

[dmarkov]

@dmarkov I need more time

@amihaiemil yes, take your time, thanks for letting me know

[amihaiemil]

@DavDavid the pr was finally merged. See details on how to use the new functionality in its description.

Can you close this ticket?

[DavDavid]

Ok, thanks very much

[dmarkov]

@DavDavid the code made here contains a puzzle 51-55acd5db/#56, which will be resolved soon

[dmarkov]

@amihaiemil thanks, I just added 30 mins to your account, payment AP-2R818475C7709490E, 2547 hours and 5 mins spent. +30 added to your rating, at the moment it is: +1039

[dmarkov]

@DavDavid the last puzzle 51-55acd5db/#56 originated from here solved

[0pdd]

@DavDavid the puzzle #unknown is still not solved.