Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 131745
Status: open
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: NERDVANA [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in:
  • 1.58
  • 1.59
  • 1.60
  • 1.61
Fixed in: (no value)



Subject: On SQL Server, DBI returns num rows affected by trigger's update
Download (untitled) / with headers
text/plain 2.6k
When using DBI on SQL Server to update a table which has a trigger which runs another update statement, DBI returns the count of rows affected by the trigger instead of the count of rows affected by the primary query. This occurs both with the FreeTDS ODBC driver and with the SQL Server Native Client 11.0. Meanwhile, the Java-based DBeaver 6.3.0 application is able to see the correct row-updated count. I have no idea if this is DBI's fault or not, but here is a repeatable test case that I used both on Linux Perl 5.26.2 32-bit with DBI 1.641 & 1.643 and DBD::ODBC 1.58 and 1.61 against SQL Server 2008 (10.0.6241) via FreeTDS driver, and on Windows 64-bit Perl 5.28.1 64-bit with DBI 1.642 and DBD::ODBC 1.60 and SQL Server Native Client 11 against the same SQL Server. my $tmp_tbl= "tmp_trig_update_example_".int(rand(1000000)); say "create table: ".$dbh->do("CREATE TABLE $tmp_tbl ( id integer identity not null, foo bit, primary key (id) )"); say "create trigger: ".$dbh->do("CREATE TRIGGER TG_${tmp_tbl}_foo ON $tmp_tbl AFTER UPDATE AS UPDATE $tmp_tbl SET foo = 0 where 1 = 0"); say "insert: ".$dbh->do("INSERT INTO $tmp_tbl (foo) VALUES (?)", {}, 1); say "update: ".$dbh->do("UPDATE $tmp_tbl SET foo = 0 WHERE id = ?", {}, 1); say "drop trigger: ".$dbh->do("DROP TRIGGER TG_${tmp_tbl}_foo"); say "update: ".$dbh->do("UPDATE $tmp_tbl SET foo = 0 WHERE id = ?", {}, 1); say "drop table: ".$dbh->do("DROP TABLE $tmp_tbl"); The output is: create table: -1 create trigger: -1 insert: 1 update: 0E0 drop trigger: -1 update: 1 drop table: -1 (the first update should also return 1, not 0E0) and the DBI_TRACE=99 output from the first update statement looks like: -> do for DBD::ODBC::db (DBI::db=HASH(0x35ca46c)~0x35ca394 'UPDATE tmp_trig_update_example_647401 SET foo = 0 WHERE id = ?' HASH(0x35ca79c) 1) thr#3f70a4 SQLPrepare UPDATE tmp_trig_update_example_647401 SET foo = 0 WHERE id = ? SQLPrepare = 0 1 <- prepare= ( DBI::st=HASH(0x35ca844) ) [1 items] at DBI.pm line 1634 +dbd_st_execute_iv(35ca9f4) dbd_st_finish(35ca9f4) -dbd_st_execute_iv(35ca9f4)=8589934592 <- execute= ( '0E0' ) [1 items] at DBI.pm line 1635 <- rows= ( 0 ) [1 items] at DBI.pm line 1636 <> DESTROY(DBI::st=HASH(0x35ca844)) ignored for outer handle (inner DBI::st=HASH(0x35ca814) has ref cnt 1) -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x35ca814)~INNER) thr#3f70a4 <- DESTROY= ( undef ) [1 items] at DBI.pm line 1637 <- do= ( '0E0' ) [1 items] at dbi-update-with-trigger.t line 16 I only mainly care about the problem because it causes DBIx::Class to die on ->update() calls because it thinks it couldn't find the row for the object.
Download (untitled) / with headers
text/plain 3.1k
On Tue Feb 11 15:25:21 2020, NERDVANA wrote: Show quoted text
> When using DBI on SQL Server to update a table which has a trigger > which runs another update statement, DBI returns the count of rows > affected by the trigger instead of the count of rows affected by the > primary query. This occurs both with the FreeTDS ODBC driver and with > the SQL Server Native Client 11.0. Meanwhile, the Java-based DBeaver > 6.3.0 application is able to see the correct row-updated count. > > I have no idea if this is DBI's fault or not, but here is a repeatable > test case that I used both on Linux Perl 5.26.2 32-bit with DBI 1.641 > & 1.643 and DBD::ODBC 1.58 and 1.61 against SQL Server 2008 > (10.0.6241) via FreeTDS driver, and on Windows 64-bit Perl 5.28.1 64- > bit with DBI 1.642 and DBD::ODBC 1.60 and SQL Server Native Client 11 > against the same SQL Server. > > my $tmp_tbl= "tmp_trig_update_example_".int(rand(1000000)); > say "create table: ".$dbh->do("CREATE TABLE $tmp_tbl ( id integer > identity not null, foo bit, primary key (id) )"); > say "create trigger: ".$dbh->do("CREATE TRIGGER TG_${tmp_tbl}_foo ON > $tmp_tbl AFTER UPDATE AS UPDATE $tmp_tbl SET foo = 0 where 1 = 0"); > say "insert: ".$dbh->do("INSERT INTO $tmp_tbl (foo) VALUES > (?)", {}, 1); > say "update: ".$dbh->do("UPDATE $tmp_tbl SET foo = 0 WHERE id > = ?", {}, 1); > say "drop trigger: ".$dbh->do("DROP TRIGGER TG_${tmp_tbl}_foo"); > say "update: ".$dbh->do("UPDATE $tmp_tbl SET foo = 0 WHERE id > = ?", {}, 1); > say "drop table: ".$dbh->do("DROP TABLE $tmp_tbl"); > > > The output is: > > create table: -1 > create trigger: -1 > insert: 1 > update: 0E0 > drop trigger: -1 > update: 1 > drop table: -1 > > (the first update should also return 1, not 0E0) > > and the DBI_TRACE=99 output from the first update statement looks > like: > > -> do for DBD::ODBC::db (DBI::db=HASH(0x35ca46c)~0x35ca394 'UPDATE > tmp_trig_update_example_647401 SET foo = 0 WHERE id = ?' > HASH(0x35ca79c) 1) thr#3f70a4 > SQLPrepare UPDATE tmp_trig_update_example_647401 SET foo = 0 WHERE id > = ? > SQLPrepare = 0 > 1 <- prepare= ( DBI::st=HASH(0x35ca844) ) [1 items] at DBI.pm line > 1634 > +dbd_st_execute_iv(35ca9f4) > dbd_st_finish(35ca9f4) > -dbd_st_execute_iv(35ca9f4)=8589934592 > <- execute= ( '0E0' ) [1 items] at DBI.pm line 1635 > <- rows= ( 0 ) [1 items] at DBI.pm line 1636 > <> DESTROY(DBI::st=HASH(0x35ca844)) ignored for outer handle (inner > DBI::st=HASH(0x35ca814) has ref cnt 1) > -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x35ca814)~INNER) > thr#3f70a4 > <- DESTROY= ( undef ) [1 items] at DBI.pm line 1637 > <- do= ( '0E0' ) [1 items] at dbi-update-with-trigger.t line 16 > > I only mainly care about the problem because it causes DBIx::Class to > die on ->update() calls because it thinks it couldn't find the row for > the object.
DBD::ODBC uses the SQLRowCount ODBC function to determine the rows affected. See https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlrowcount-function?view=sql-server-ver15 I don't know how the Java driver is seeing a different value but all that DBD::ODBC has at hand to use AFAIK is SQLRowCount. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.