db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Deepa Remesh (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-498) Result set holdability defined inside stored procedures is ignored by server/client
Date Fri, 07 Oct 2005 20:28:48 GMT
    [ http://issues.apache.org/jira/browse/DERBY-498?page=comments#action_12331621 ] 

Deepa Remesh commented on DERBY-498:
------------------------------------

Thanks Army for reviewing the patch. The use of "newDrdaRs.withHoldCursor" is intentional.
Reasoning is:

With this change, the result set(s) generated by a callable statement can have different holdabilities.
So the SQLCSRHLD value in OPNQRYRM reply message can be different for each result set. Hence
I cannot use the holdability of statement in general but need to get it for each result set
. For this, I am using the variable "withHoldCursor" in DRDAResultSet. This variable existed
before but was not being used anywhere. Now it is used in DRDAConnThread's writeOPNQRYRM and
set in DRDAStatement's addResultSet  method:

check in writeOPNQRYRM before:
	//pass the SQLCSRHLD codepoint only if statement has hold cursors over commit set
	if (stmt.withHoldCursor == JDBC30Translation.HOLD_CURSORS_OVER_COMMIT)
		writer.writeScalar1Byte(CodePoint.SQLCSRHLD, CodePoint.TRUE);

check in writeOPNQRYRM after:
	//pass the SQLCSRHLD codepoint only if statement producing the ResultSet has 
	//hold cursors over commit set. In case of stored procedures which use server-side
	//JDBC, the holdability of the ResultSet will be the holdability of the statement 
	//in the stored procedure, not the holdability of the calling statement.
	if (stmt.getCurrentDrdaResultSet().withHoldCursor == JDBC30Translation.HOLD_CURSORS_OVER_COMMIT)
		writer.writeScalar1Byte(CodePoint.SQLCSRHLD, CodePoint.TRUE);
		
call to addResultSet execute method in DRDAStatement:
        ...
	//For callable statement, get holdability of statement generating the result set
	if(isCallable)
		addResultSet(rs,getResultSetHoldability(rs));
	else
		addResultSet(rs,withHoldCursor);
        ...

For callable statements, I set "withHoldCursor" in DRDAResultSet with the holdability of statement
within procedure which generated the result set. For other statements, I set it to the holdability
of the DRDAStatement (same as what was being checked in writeOPNQRYRM before this change).





> 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