[jira] Commented: (DERBY-3427) setting transaction isolation level to read committed raise ERROR X0X03: Invalid transaction state - held cursor requires same isolation level

[Dyre Tjeldvoll (JIRA)]

    [ 
https://issues.apache.org/jira/browse/DERBY-3427?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12574998#action_12574998
 ] 

Dyre Tjeldvoll commented on DERBY-3427:
---------------------------------------

A couple of observations about this.
1) The setting of the isolation level fails because the connection has an 
activation with an open holdable resultset. The statement which created the 
resultset is '? = CALL SYSIBM.BLOBGETBYTES(?, ?, ?)'

2) There appears to be something wrong with the transmission of holdability for 
CallableStatements in the ClientDriver, because 
'? = CALL SYSIBM.BLOBGETBYTES(?, ?, ?)' is clearly prepared with 
ResultSet.CLOSE_CURSORS_AT_COMMIT:
            blobGetBytesCall = connection.prepareCallX
                ("? = CALL SYSIBM.BLOBGETBYTES(?, ?, ?)",
                 java.sql.ResultSet.TYPE_FORWARD_ONLY, 
                 java.sql.ResultSet.CONCUR_READ_ONLY, 
                 java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT);

but when I instrument EmbedCallableStatement I see that on the server the 
holdability is different:
                        /* KLUDGE - ? = CALL ... returns a ResultSet().  We
                         * need executeUpdate to be false in that case.
                         */
                        
org.apache.derby.iapi.services.monitor.Monitor.logMessage("EmbedCallableStatement="+getSQLText()

+", 
closeAtCommit:"+(getResultSetHoldability()==java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT));

prints the following to derby.log:
EmbedCallableStatement=? = CALL SYSIBM.BLOBGETLENGTH(?), closeAtCommit:false

and even
EmbedCallableStatement=call SYSIBM.SQLCAMESSAGE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
, closeAtCommit:false

Seems a bit strange that sSQLCAMESSAGE should need to use a holdable cursor.


3) The repro runs fine if I modify EmbedCallableStatement.executeStatement(...) 
so that it closes the resultset it uses to get the value of the out-parameter, 
rather than simply setting the variable to null:

                                try
                                {
                                        DataValueDescriptor returnValue = 
pvs.getReturnValueForSet();
                                        
returnValue.setValueFromResultSet(results, 1, true);
                                } catch (StandardException e)
                                {
                                        throw 
EmbedResultSet.noStateChangeException(e);
                                }
                                finally {
                                        
org.apache.derby.iapi.services.monitor.Monitor.
                                                logMessage("result:"+results);
                                        results.close();  // <--- ADDED THIS 
LINE
                                        results = null;
                                }

                                // This is a form of ? = CALL which current is 
not a procedure call.
                                // Thus there cannot be any user result sets, 
so return false. execResult
                                // is set to true since a result set was 
returned, for the return parameter.
                                execResult = false;

I have not run any other tests with this change, so I don't know if it has any 
negative side-effects.

> setting transaction isolation level to read committed raise ERROR X0X03: 
> Invalid transaction state - held cursor requires same isolation level
> ----------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3427
>                 URL: https://issues.apache.org/jira/browse/DERBY-3427
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions: 10.3.1.4, 10.3.2.1
>         Environment: windows XP Java version 1.6.0_03
>            Reporter: Rene Levantinh
>            Priority: Critical
>         Attachments: SimpleNetworkClientSample1.java, 
> SimpleNetworkServerSample.java
>
>
> setting transaction isolation level to read committed after reading a BLOG 
> record raise ERROR X0X03: Invalid transaction state - held cursor requires 
> same isolation level .
>   Using Derby client 10.2  either with Derby  server 10.3  or  10.2   works 
> fine .
>   Using Derby Client 10.3  with Derby Server 10.2  also work fine.  

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.