Only 1 record in recordset but on Update many records get updated [ODBC186]

[firebird-automations]

Submitted by: jack kay (kjack)

Hi
Firstly, thank you for a great piece of free software which works very well for me.
I think that I may have found a bug or maybe it is the planned behaviour.
I posted this as a question on stackoverflow here: http://stackoverflow.com/questions/30126619/only-1-row-in-recordset-but-all-rows-in-table-get-updated?lq=1
You may prefer to look at it there as it is better formatted than i can do here.

What it is is that I create a single row, single column recordset using this query:

sQuery = "select memo from clients where clientID = 10021 "

clientID is the primary key column.
Say the value in memo before update was 'bingo' then i edit and update it as follows:

rs.Fields("memo") = "blah"
rs.Update

Now every record in the table which had 'bingo' in its memo field gets 'blah' in there, instead.

At first I was very surprised by this but it also strikes me as logical.
So, I don't know for certain if this is a bug or not.
However I can say that when I tested this using Access and Postgresql using ADO and ODBC the behaviour
was different. Just one row was edited.

I found that I could restrict the update to a single row by including the primary key in the list
of selected fields, like so;

sQuery = "select memo, clientID from clients where clientID = 10021 "

Thank you for taking the time to look at this.
Jack

[firebird-automations]

Modified by: jack kay (kjack)

description: Hi
Firstly, thank you for a great piece of free software which works very well for me.
I think that I may have found a bug or maybe it is the planned behaviour.
I posted this as a question on stackoverflow here: http://stackoverflow.com/questions/30126619/only-1-row-in-recordset-but-all-rows-in-table-get-updated?lq=1
You may prefer to look at it there as it is better formatted than i can do here.

What it is is that I create a single row, single column recordset using this query:

sQuery = "select memo from clients where clientID = 10021 "

clientID is the primary key column.
Say the value in memo before update was 'bingo' then i edit and update it as follows:

rs.Fields("memo") = "blah"
rs.Update

Now every record in the table which had 'bingo' in its memo field gets 'blah' in there, instead.
I found that I could restrict the update to a single row by including the primary key in the list
of selected fields, like so;

sQuery = "select memo, clientID from clients where clientID = 10021 "

At first I was very surprised by this but it also strikes me as logical.
So, I don't know for certain if this is a bug or not.
However I can say that when I tested this using Access and Postgresql using ADO and ODBC the behaviour
was different. Just one row was edited.
Thank you for taking the time to look at this.
Jack

=>

Hi
Firstly, thank you for a great piece of free software which works very well for me.
I think that I may have found a bug or maybe it is the planned behaviour.
I posted this as a question on stackoverflow here: http://stackoverflow.com/questions/30126619/only-1-row-in-recordset-but-all-rows-in-table-get-updated?lq=1
You may prefer to look at it there as it is better formatted than i can do here.

What it is is that I create a single row, single column recordset using this query:

sQuery = "select memo from clients where clientID = 10021 "

clientID is the primary key column.
Say the value in memo before update was 'bingo' then i edit and update it as follows:

rs.Fields("memo") = "blah"
rs.Update

Now every record in the table which had 'bingo' in its memo field gets 'blah' in there, instead.

At first I was very surprised by this but it also strikes me as logical.
So, I don't know for certain if this is a bug or not.
However I can say that when I tested this using Access and Postgresql using ADO and ODBC the behaviour
was different. Just one row was edited.

I found that I could restrict the update to a single row by including the primary key in the list
of selected fields, like so;

sQuery = "select memo, clientID from clients where clientID = 10021 "

Thank you for taking the time to look at this.
Jack

[firebird-automations]

Commented by: @alexpotapchenko

Hi Jack,

This is "By design" in Firebird ODBC driver and related with generating update query (you can see it in ODBC trace).

You are right here:
EDIT: Having read around the web a bit this is my understanding of what is happening. It seems that the update method identifies which records to update based on the selected columns in the recordset. So if you select fields a,b,c,d and are updating field a, it will only update records in the database whose values for a,b,c,d match those in the recordset.

[firebird-automations]

Commented by: @mrotteveel

That is a pretty big gotcha. Looking at https://msdn.microsoft.com/en-us/library/windows/desktop/ms676529%28v=vs.85%29.aspx it should really only update the current row, and that is not what it's currently doing.

[firebird-automations]

Commented by: jack kay (kjack)

Hi Alexander

Thanks for looking at this so quickly.
I just need to be a little more thoughtful
with my queries and include the pk!

Jack

[firebird-automations]

Modified by: @alexpotapchenko

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

Fix Version: 2.0.6 [ 10851 ]