db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Deepa Remesh <drem...@gmail.com>
Subject Re: [jira] Commented: (DERBY-498) Result set holdability defined inside stored procedures is ignored by server/client
Date Fri, 14 Oct 2005 00:39:51 GMT
I have added procedure tests to xaSimplePositive.sql and attached it
with the mail. This patch is only for review. If it is okay, I can
combine it with my previous patch for derby-498 and attach it to the
JIRA issue.

I ran xaSimplePositive.sql before and after applying my patch for
derby-498. In both cases, I did'nt get any error when the holdability
of a statement inside a procedure was set to HOLD_CURSORS_OVER_COMMIT.
Is this okay?

Here is behaviour before and after the patch (It is the same for
global and local transactions)
-------------------------------------
before the patch:
-------------------------------------
Holdability of statement inside procedure | Result set holdability
default | HOLD_CURSORS_OVER_COMMIT
HOLD_CURSORS_OVER_COMMIT | HOLD_CURSORS_OVER_COMMIT
CLOSE_CURSORS_AT_COMMIT | HOLD_CURSORS_OVER_COMMIT

-------------------------------------
after the patch:
-------------------------------------
Holdability of statement inside procedure | Result set holdability
default | CLOSE_CURSORS_AT_COMMIT
HOLD_CURSORS_OVER_COMMIT | HOLD_CURSORS_OVER_COMMIT
CLOSE_CURSORS_AT_COMMIT | CLOSE_CURSORS_AT_COMMIT

Does this look okay?

Thanks,
Deepa

On 10/13/05, Kathey Marsden (JIRA) <derby-dev@db.apache.org> wrote:
>     [ http://issues.apache.org/jira/browse/DERBY-498?page=comments#action_12332026 ]
>
> Kathey Marsden commented on DERBY-498:
> --------------------------------------
>
> Well all that XA trouble sounds like it was due to a not so good tip that I put in this
bug (sorry about that).  Your fix looks fine except that I wonder about the  reflection to
get the holdability  with xa connections and jdk131 and whether the method will be available.
>
> If we don't have one already, I think it would be good to add a  procedure call  that
returns statements, both inside and outside of an xa transaction to xaSimplePositive.sql.
  You can't specify  HOLD_CURSORS_OVER_COMMIT within an xa transaction (it should throw an
error), but even statements in procedures with  CLOSE_CURSORS_AT_COMMIT  should go through
the new code path.
>
> There are probably existing procedure definitions in functionTests/util/ProcedureTest
that could be called from   one of the xa sql tests.
>
> > Result set holdability defined inside stored procedures is ignored by server/client
> > -----------------------------------------------------------------------------------
> >
> >          Key: DERBY-498
> >          URL: http://issues.apache.org/jira/browse/DERBY-498
> >      Project: Derby
> >         Type: Bug
> >   Components: Network Client, Network Server
> >     Versions: 10.1.2.0, 10.2.0.0
> >     Reporter: A B
> >     Assignee: Deepa Remesh
> >  Attachments: d498.java, derby-498.diff, derby-498.status
> >
> > Assume I have a Java stored procedure that returns one or more result sets, and
the holdability of those result sets is specified as part of the createStatement() method
within the procedure definition (see below for an example).
> > If I execute this procedure against Derby embedded, the holdability of each result
set matches that of the statement-specific holdability that is defined within the stored procedure.
 However, if I run the procedure against the Network Server using the Derby client, the holdability
of _all_ result sets is the same, and it is based on the holdability of the statement that
_executed_ the procedure--i.e. the statement-specific holdability that is defined within the
procedure is ignored.
> > Ex: If I create a stored procedure that corresponds to the following method:
> > public static void p2(ResultSet[] rs1, ResultSet[] rs2,
> >     ResultSet[] rs3) throws Exception
> > {
> >     Connection conn = DriverManager.getConnection(
> >         "jdbc:default:connection");
> >     Statement st1 = conn.createStatement(
> >         ResultSet.TYPE_FORWARD_ONLY,
> >         ResultSet.CONCUR_READ_ONLY,
> >         ResultSet.HOLD_CURSORS_OVER_COMMIT);
> >     rs1[0] = st1.executeQuery("select * from testtable1");
> >     Statement st2 = conn.createStatement(
> >         ResultSet.TYPE_FORWARD_ONLY,
> >         ResultSet.CONCUR_READ_ONLY,
> >         ResultSet.CLOSE_CURSORS_AT_COMMIT);
> >     rs2[0] = st2.executeQuery("select * from testtable2");
> >     Statement st3 = conn.createStatement(
> >         ResultSet.TYPE_FORWARD_ONLY,
> >         ResultSet.CONCUR_READ_ONLY,
> >         ResultSet.HOLD_CURSORS_OVER_COMMIT);
> >     rs3[0] = st3.executeQuery("select * from testtable3");
> >     return;
> >     }
> > }
> > Then with Derby embedded, if I have a JDBC Statement that executes a call to this
procedure, rs1 and rs3 will behave with HOLD_CURSORS holdability and rs2 will behave with
CLOSE_CURSORS holdability--and that will be the case regardless of the holdability on the
Statement that executed the call.  That seems correct to me.
> > But if I do the same thing with Network Server, all of the result sets (rs1, rs2,
and rs3) will have the same holdability as the JDBC Statement that executed the call.  It
doesn't matter what the holdabilities used within the procedure definition are: they will
all be over-ridden by the holdability of the Statement that made the call.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
>    http://issues.apache.org/jira/secure/Administrators.jspa
> -
> For more information on JIRA, see:
>    http://www.atlassian.com/software/jira
>
>

Mime
View raw message