search

FirebirdSQL / NETProvider

Firebird ADO.NET Data Provider
https://www.firebirdsql.org/en/net-provider/
Other
159 stars 65 forks source link

FirebirdClient for .net 6.0 ExecuteNonQuery returns -1 for Update queries having 'returning xyz' inSQL #1044

Closed Avocet45 closed 2 years ago

Avocet45 commented 2 years ago

Hi, I'm migrating my project from .net framework 4.7 (currently using version 7.1.1.0 of NETProvider) to .net 6 (using version 9.0) and found the behaviour of ExecuteNonQuery() and ExecuteNonQueryAsync() has changed between versions creating a breaking change that appears either incorrect or undocumented, or I'm missing something. When the SQL update statement contains a returning clause to return, say, a changed sequence value, the earlier version of NETProvider returned the number of rows affected, as expected. The current version returns -1.
Looking at the code it seems that _statement.Prepare(sql); line 1376 FbCommand.cs changes the StatementType to StoredProcedure if there is a returning clause, and StatementType.StoredProcedure always returns -1 for rows affected. Explicitly setting CommandType to Text makes no difference.

cincuranet commented 2 years ago

This is expected (and known). You can read more in #852.

Avocet45 commented 2 years ago

Hi, thanks very much for your quick reply - much appreciated. In defence of my raising the issue, this is changed behaviour from version 7 to version 9 of the ado.net.provider. However, now I know this is the expected behaviour, I will amend my migrated code to inspect the return parameter value instead of the ExecuteNonQuery() return value.

cincuranet commented 2 years ago

You should always check the breaking tag when upgrading between versions. That's why I add it to issues (and #852 has it).