Mailing-List: contact dev-help@commons.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Commons Developers List" <dev@commons.apache.org>
Received-SPF: pass (athena.apache.org: domain of ray@decampo.org designates
 209.85.212.171 as permitted sender)
MIME-Version: 1.0
Date: Thu, 11 Sep 2014 08:27:19 -0400
Message-ID: 
 <CAGTHBS5mtN5hi2NBQakPRthisfnz0yX0VkoYUcVcOf3XG1PXqg@mail.gmail.com>
Subject: [dbutils] DBUTILS-50; OUT parameters for stored procedures
From: Raymond DeCampo <ray@decampo.org>
To: dev@commons.apache.org
Content-Type: multipart/alternative; boundary=f46d043749cb2fe5c60502c94a50

--f46d043749cb2fe5c60502c94a50
Content-Type: text/plain; charset=UTF-8

I have attached a patch for DBUTILS-50 which requests OUT parameters for
stored procedures.  My approach was to add execute() methods to QueryRunner
which will use a CallableStatement to run the SQL.  If any of the
parameters passed are instances of OutParameter (a new class), the
parameter is registered as an OUT parameter with the CallableStatement and
the value is available from the OutParameter instance after the call has
completed.  INOUT parameters are also supported by setting a value on the
OutParameter instance.  There are flavors of execute() for procedures
returning result sets and procedures that return update counts.

For example supposed procedure my_proc takes four parameters; IN, OUT,
INOUT, IN.  The following example code would apply:

QueryRunner runner = ...
OutParameter<Integer> in = new OutParameter<>(Types.INTEGER, Integer.class);
OutParameter<Integer> inout = new OutParameter<>(Types.VARCHAR,
String.class, "input value");
runner.execute("{call my_proc(?,?,?,?)}", "value1", in, inout, 42);
Integer inReturn = in.getValue();
String inoutReturn = inout.getValue();

I thought that this approach would keep to the principle of shielding the
library users from the gory details of JDBC while keeping an interface
consistent with the current code.  Unfortunately we bend a little on the
idea of keeping the user from needing to create helper objects, but
something had to give.  This way allows the user to interleave IN, OUT and
INOUT parameters in the same set of arguments to the method keeping the
method signature sane and familiar.

I'd appreciate any feedback.

Thanks,
Ray

--f46d043749cb2fe5c60502c94a50--