Skip to content

Inconsistent updates of ResultSet rows when updating one-to-many tables #2067

Closed
Closed
Inconsistent updates of ResultSet rows when updating one-to-many tables#2067
@siggemannen

Description

@siggemannen
siggemannen
opened on Feb 5, 2023

Driver version

9.1.1.jre8-preview

SQL Server version

Microsoft SQL Server 2014 (SP3-GDR) (KB4583463) - 12.0.6164.21 (X64)
Nov 1 2020 04:25:14
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Client Operating System

Windows 7

JAVA/JVM version

1.8

Table schema

drop table edit
drop table editrow
create table edit (i int, data varchar(30))
create table editrow (i int, row int, data varchar(30))

insert into edit select 1, 'testedit'
insert into editrow select 1, 1, 'test1' union select 1, 2, 'test2'

select er.*
from edit e
inner join editrow er
	ON	er.i = e.i

Problem description

I have two tables which are joined together by above SELECT. I want to update columns by selecting the resultSet by using JDBC and ResultSet.updateXXX, but according to my tests, for some reason i can only update columns in the first table, even though it's the table that gets "duplicated" in the SELECT. I can update both tables by using manual UPDATE in SQL Server, but i need to do it through java. I also can't skip the JOIN for various reasons.

I get no exceptions, but that data isn't changed either.

Sample code:

        //this doesn't update
        Connection conn = DriverManager.getConnection(getUrl());
        Statement createStatement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = createStatement.executeQuery("select er.* from edit e inner join editrow er ON  er.i = e.i");
        //Now,move to row 1...
        rs.first();
        //Update data with some string
        rs.updateString(3, "New row edit");
        rs.updateRow();
        rs.close();
        createStatement.close();
        
       //this updates
        Statement createStatement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = createStatement.executeQuery("select * from edit e inner join editrow er ON  er.i = e.i");
        //Now,move to row 1...
        rs.first();
        //Update data with some string
        rs.updateString(2, "New head edit");
        rs.updateRow();
        rs.close();
        createStatement.close();

Full code attached in the text file.

Expected behavior

I expect to be able to update all updateable columns. If some column cannot be updated, i expect an exception. But ideally everything that SQL Server itself can update should be updateable by JDBC driver.

Actual behavior

When updating column from "editrow", it silently accepts it but the data isn't changed. When updating column from "edit", it works.

Error message/stack trace

There are no errors

Any other details that can be helpful

I have attached a simple java program which demostrates the behaviour.

JDBC trace logs

feb 02, 2023 1:02:43 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:1 execute
FINER: ENTRY if object_id('edit') is not null drop table edit
create table edit (i int, data varchar(30))
if object_id('editrow') is not null drop table editrow
create table editrow (i int, row int, data varchar(30))
insert into edit select 1, 'testedit'
insert into editrow select 1, 1, 'test1' union select 1, 2, 'test2'
feb 02, 2023 1:03:56 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: SQLServerStatement:1 Executing (not server cursor) if object_id('edit') is not null drop table edit
create table edit (i int, data varchar(30))
if object_id('editrow') is not null drop table editrow
create table editrow (i int, row int, data varchar(30))
insert into edit select 1, 'testedit'
insert into editrow select 1, 1, 'test1' union select 1, 2, 'test2'
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:1 execute
FINER: RETURN false
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:1 close
FINER: ENTRY
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:1 close
FINER: RETURN
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: ENTRY 1 005 1 008
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 setResponseBuffering
FINER: ENTRY adaptive
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 setResponseBuffering
FINER: RETURN
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINER: Properties for SQLServerStatement:2: Result type:1005 (1005) Concurrency:1008 Fetchsize:128 bIsClosed:false useLastUpdateCount:false
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINE: SQLServerStatement:2 created by (ConnectionID:1 ClientConnectionId: 3715addc-0b12-4f86-a437-46f0711d8a56)
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: RETURN SQLServerStatement:2
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 executeQuery
FINER: ENTRY select er.* from edit e inner join editrow er ON er.i = e.i
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: SQLServerStatement:2 Executing server side cursor select er.* from edit e inner join editrow er ON er.i = e.i
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteCursored
FINER: SQLServerStatement:2 Execute for cursor open SQL:select er.* from edit e inner join editrow er ON er.i = e.i Scrollability:1 Concurrency:24580
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet
FINE: SQLServerResultSet:1 created by (SQLServerStatement:2)
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 executeQuery
FINER: RETURN SQLServerResultSet:1
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 first
FINER: ENTRY
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet first
FINER: SQLServerResultSet:1 currentRow:0 numFetchedRows:0 rowCount:2
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet doServerFetch
FINER: SQLServerResultSet:1 fetchType:1 startRow:0 numRows:128
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.ScrollWindow next
FINER: SQLServerResultSet:1 currentRow:0 maxRows:128
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 first
FINER: RETURN true
feb 02, 2023 1:03:57 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 updateString
FINER: ENTRY 3 New row edit
feb 02, 2023 1:04:02 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 updateString
FINER: RETURN
feb 02, 2023 1:04:02 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 updateRow
FINER: ENTRY
feb 02, 2023 1:04:02 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet updateRow
FINER: SQLServerResultSet:1 currentRow:1 numFetchedRows:1 rowCount:2
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 updateRow
FINER: RETURN
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 close
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet closeServerCursor
FINER: SQLServerResultSet:1 Closing cursor:180150003
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet closeServerCursor
FINER: SQLServerResultSet:1 Closed cursor:180150003
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 close
FINER: RETURN
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 close
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 close
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:1 close
FINER: RETURN
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 close
FINER: RETURN
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: ENTRY 1 003 1 007
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:3 setResponseBuffering
FINER: ENTRY adaptive
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:3 setResponseBuffering
FINER: RETURN
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINER: Properties for SQLServerStatement:3: Result type:1003 (2003) Concurrency:1007 Fetchsize:128 bIsClosed:false useLastUpdateCount:false
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINE: SQLServerStatement:3 created by (ConnectionID:1 ClientConnectionId: 3715addc-0b12-4f86-a437-46f0711d8a56)
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: RETURN SQLServerStatement:3
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: RETURN SQLServerStatement:3
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:3 executeQuery
FINER: ENTRY select er.data from editrow er where row = 1;
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: SQLServerStatement:3 Executing (not server cursor) select er.data from editrow er where row = 1;
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet
FINE: SQLServerResultSet:2 created by (SQLServerStatement:3)
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:3 executeQuery
FINER: RETURN SQLServerResultSet:2
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:2 next
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet next
FINER: SQLServerResultSet:2 currentRow:0 numFetchedRows:0 rowCount:-3
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:2 next
FINER: RETURN true
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:2 getString
FINER: ENTRY 1
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet getterGetColumn
FINER: SQLServerResultSet:2 Getting Column:1
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.BaseInputStream setLoggingInfo
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:1
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SimpleInputStream read
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:1 Reading 5 from stream offset 0 payload length 5
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SimpleInputStream close
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:1Enter Closing SimpleInputStream.
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SimpleInputStream skip
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:1 Skipping :0
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SimpleInputStream close
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:1Exit Closing SimpleInputStream.
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:2 getString
FINER: RETURN test1
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 close
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:2 close
FINER: RETURN
test1
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: ENTRY 1 005 1 008
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:4 setResponseBuffering
FINER: ENTRY adaptive
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:4 setResponseBuffering
FINER: RETURN
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINER: Properties for SQLServerStatement:4: Result type:1005 (1005) Concurrency:1008 Fetchsize:128 bIsClosed:false useLastUpdateCount:false
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINE: SQLServerStatement:4 created by (ConnectionID:1 ClientConnectionId: 3715addc-0b12-4f86-a437-46f0711d8a56)
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: RETURN SQLServerStatement:4
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:4 executeQuery
FINER: ENTRY select * from edit e inner join editrow er ON er.i = e.i
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: SQLServerStatement:4 Executing server side cursor select * from edit e inner join editrow er ON er.i = e.i
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteCursored
FINER: SQLServerStatement:4 Execute for cursor open SQL:select * from edit e inner join editrow er ON er.i = e.i Scrollability:1 Concurrency:24580
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet
FINE: SQLServerResultSet:3 created by (SQLServerStatement:4)
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:4 executeQuery
FINER: RETURN SQLServerResultSet:3
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 first
FINER: ENTRY
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet first
FINER: SQLServerResultSet:3 currentRow:0 numFetchedRows:0 rowCount:2
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet doServerFetch
FINER: SQLServerResultSet:3 fetchType:1 startRow:0 numRows:128
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.ScrollWindow next
FINER: SQLServerResultSet:3 currentRow:0 maxRows:128
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 first
FINER: RETURN true
feb 02, 2023 1:04:03 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 updateString
FINER: ENTRY 2 New head edit
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 updateString
FINER: RETURN
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 updateRow
FINER: ENTRY
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet updateRow
FINER: SQLServerResultSet:3 currentRow:1 numFetchedRows:1 rowCount:2
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 updateRow
FINER: RETURN
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 close
FINER: ENTRY
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet closeServerCursor
FINER: SQLServerResultSet:3 Closing cursor:180150005
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet closeServerCursor
FINER: SQLServerResultSet:3 Closed cursor:180150005
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 close
FINER: RETURN
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:4 close
FINER: ENTRY
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 close
FINER: ENTRY
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:3 close
FINER: RETURN
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:4 close
FINER: RETURN
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: ENTRY
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: ENTRY 1 003 1 007
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:5 setResponseBuffering
FINER: ENTRY adaptive
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:5 setResponseBuffering
FINER: RETURN
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINER: Properties for SQLServerStatement:5: Result type:1003 (2003) Concurrency:1007 Fetchsize:128 bIsClosed:false useLastUpdateCount:false
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement
FINE: SQLServerStatement:5 created by (ConnectionID:1 ClientConnectionId: 3715addc-0b12-4f86-a437-46f0711d8a56)
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: RETURN SQLServerStatement:5
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerConnection:1 createStatement
FINER: RETURN SQLServerStatement:5
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:5 executeQuery
FINER: ENTRY select er.data from edit er where i = 1;
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: SQLServerStatement:5 Executing (not server cursor) select er.data from edit er where i = 1;
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet
FINE: SQLServerResultSet:4 created by (SQLServerStatement:5)
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerStatement:5 executeQuery
FINER: RETURN SQLServerResultSet:4
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:4 next
FINER: ENTRY
New head edit
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet next
FINER: SQLServerResultSet:4 currentRow:0 numFetchedRows:0 rowCount:-3
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:4 next
FINER: RETURN true
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:4 getString
FINER: ENTRY 1
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet getterGetColumn
FINER: SQLServerResultSet:4 Getting Column:1
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.BaseInputStream setLoggingInfo
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:2
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SimpleInputStream read
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:2 Reading 13 from stream offset 0 payload length 13
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SimpleInputStream close
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:2Enter Closing SimpleInputStream.
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SimpleInputStream skip
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:2 Skipping :0
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SimpleInputStream close
FINER: com.microsoft.sqlserver.jdbc.SimpleInputStreamID:2Exit Closing SimpleInputStream.
feb 02, 2023 1:04:05 FM com.microsoft.sqlserver.jdbc.SQLServerResultSet:4 getString
FINER: RETURN New head edit

RSUpdate.txt

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions