Result set holdability defined inside stored procedures is ignored by server/client

[Army <qo...@sbcglobal.net>]

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.

Does anyone know if this is a bug with the server/client, or is it just another 
difference between embedded mode and server mode that needs to be documented?

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 have HOLD_CURSORS holdability and rs2 will have 
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.  Am I correct in thinking that's a bug?

I searched through Jira but didn't see this issue mentioned anywhere (there were 
a couple that sounded sort of similar (ex. DERBY-8), but they were for XA 
connections).  If I can get confirmation that this is indeed a bug (as opposed 
to a difference between embedded and server modes that just needs to be 
documented), I will create a new Jira entry for it...

Thanks for any feedback/input,
Army

Re: Result set holdability defined inside stored procedures is ignored by server/client

[Army <qo...@sbcglobal.net>]

Kathey Marsden wrote:

> Army wrote:
> 
> 
>>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).
>>
> 
> [snip .. details]
> 
> I think this sounds like a bug.  I have not heard of it before.   
> 
> Kathey

Thanks for the reply, Kathey.  I've filed DERBY-498 and have attached a repro to 
that issue.

Army

Re: Result set holdability defined inside stored procedures is ignored by server/client

[Kathey Marsden <km...@sbcglobal.net>]

Army wrote:

> 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).
>
[snip .. details]

>
> I searched through Jira but didn't see this issue mentioned anywhere
> (there were a couple that sounded sort of similar (ex. DERBY-8), but
> they were for XA connections).  If I can get confirmation that this is
> indeed a bug (as opposed to a difference between embedded and server
> modes that just needs to be documented), I will create a new Jira
> entry for it...
>
I think this sounds like a bug.  I have not heard of it before.   

Kathey