executeUpdate return 0 rowcount for an update

Hi! I have a developer who reported the following problem
"For those of you who aren't familiar, the way we modify data in the Java
apps is through an API called JDBC. The JDBC API provides a
PreparedStatement class that allows you to assemble sql and then have it
executed. In the case of an update statement, you are supposed to call an
executeUpdate method. That method is supposed to return the number of rows
updated by the command. If it returns 0, I was assuming that no rows were
updated, hence the locking failure. I finally decided that maybe the row was
getting updated, but executeUpdate was incorrectly returning 0. I just
tested that theory and found it to be the case. The row is updated, but
executeUpdate is returning 0."
The query statement passed to executeUpdate is a simple update statement
(update ... set .. where ..)
The table developer tried to update has an update trigger which has executed
2 INSERT statements:
INSERT statement #1 adds records into transaction_touch table and
INSERT statement #2 adds records into an audit table
The transaction_touch table has an insert trigger which populates data into
another table. The test showed that
no record was added into this table. Therefore, the rowcount is 0. If I
commented out the INSERT statement inside
the transaction_touch insert trigger, a non-zero rrowcount was returned back
to Java apps.
I don't know if anybody has experienced this problem using MS JDBC driver to
access MS SQL Server 2000? If
you do and has a solution, please let me know. Thanks
-- Peter Lo
Peter:
We're going to investigate this one further and post back what we find.
Can you post the trigger repro code -- that would be helpful.
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:OLvobEkhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Hi! I have a developer who reported the following problem
> "For those of you who aren't familiar, the way we modify data in the Java
> apps is through an API called JDBC. The JDBC API provides a
> PreparedStatement class that allows you to assemble sql and then have it
> executed. In the case of an update statement, you are supposed to call an
> executeUpdate method. That method is supposed to return the number of rows
> updated by the command. If it returns 0, I was assuming that no rows were
> updated, hence the locking failure. I finally decided that maybe the row
> was
> getting updated, but executeUpdate was incorrectly returning 0. I just
> tested that theory and found it to be the case. The row is updated, but
> executeUpdate is returning 0."
> The query statement passed to executeUpdate is a simple update statement
> (update ... set .. where ..)
> The table developer tried to update has an update trigger which has
> executed
> 2 INSERT statements:
> INSERT statement #1 adds records into transaction_touch table and
> INSERT statement #2 adds records into an audit table
> The transaction_touch table has an insert trigger which populates data
> into
> another table. The test showed that
> no record was added into this table. Therefore, the rowcount is 0. If I
> commented out the INSERT statement inside
> the transaction_touch insert trigger, a non-zero rrowcount was returned
> back
> to Java apps.
> I don't know if anybody has experienced this problem using MS JDBC driver
> to
> access MS SQL Server 2000? If
> you do and has a solution, please let me know. Thanks
>
> -- Peter Lo
>
>
>
>
|||Shelby:
The update statement executed by Java apps looks like this:
update icts_user where user_init = '?' and trans_id = ?
==> In this example, only one icts_user record will be modified
Here are the triggers:
create trigger icts_user_updtrg
on icts_user
for update
as
declare @.num_rows int,
@.count_num_rows int,
@.errmsg varchar(255)
select @.num_rows = @.@.rowcount
if @.num_rows = 0
return
if update(loc_code)
begin
if exists (select 1 from inserted
where loc_code not in (select loc_code from location))
begin
if @.num_rows = 1
begin
select @.errmsg = 'The loc_code ''' + loc_code + ''' is not a valid
code defined in the location table.'
from inserted
end
else
begin
select @.errmsg = 'The loc_code is not a valid code defined in the
location table.'
end
raiserror 20002 @.errmsg
rollback tran
return
end
end
if exists (select 1 from inserted, icts_user
where icts_user.user_logon_id = rtrim(inserted.user_logon_id) and
icts_user.user_init != inserted.user_init)
begin
if @.num_rows = 1
begin
select @.errmsg = 'The new user_logon_id ''' + user_logon_id + ''' has
existed in the icts_user table. Duplicate is not allowed!'
from inserted
end
else
begin
select @.errmsg = 'The new user_logon_id(s) have existed in the
icts_user table. Duplicate is not allowed!'
end
raiserror 20003 @.errmsg
rollback tran
return
end
/* END_DATA_INTEGRITY */
if not update(trans_id)
begin
raiserror 20090 '(icts_user) The change needs to be attached with a new
trans_id'
rollback tran
return
end
if exists (select 1
from master.dbo.sysprocesses
where spid = @.@.spid and
rtrim(program_name) IN ('ISQL-32', 'OSQL-32', 'SQL Query
Analyzer') )
begin
if (select count(*) from inserted, deleted where inserted.trans_id <=
deleted.trans_id) > 0
begin
select @.errmsg = '(icts_user) New trans_id must be larger than
original trans_id.'
select @.errmsg = @.errmsg + char(10) + 'You can use the the
gen_new_transaction procedure to obtain a new trans_id.'
raiserror 20092 @.errmsg
rollback tran
return
end
end
if exists (select * from inserted i, deleted d
where i.trans_id < d.trans_id and
i.user_init = d.user_init )
begin
raiserror 20091 '(icts_user) new trans_id must not be older than current
trans_id.'
rollback tran
return
end
select @.dummy_update = 0
if update(user_init)
begin
select @.count_num_rows = (select count(*) from inserted i, deleted d
where i.user_init = d.user_init )
if (@.count_num_rows = @.num_rows)
begin
select @.dummy_update = 1
end
else
begin
raiserror 20072 '(icts_user) primary key can not be changed.'
rollback tran
return
end
end
insert transaction_touch
select 'UPDATE',
'IctsUser',
'DIRECT',
convert(varchar(40), i.user_init),
null,
null,
null,
null,
null,
null,
null,
i.trans_id,
it.sequence
from inserted i, icts_transaction it
where i.trans_id = it.trans_id and
it.type != 'E'
if @.dummy_update = 0
insert aud_icts_user
(user_init,
user_last_name,
user_first_name,
desk_code,
loc_code,
user_logon_id,
us_citizen_ind,
user_job_title,
user_status,
user_employee_num,
email_address,
defaults_information_id,
trans_id,
resp_trans_id)
select
d.user_init,
d.user_last_name,
d.user_first_name,
d.desk_code,
d.loc_code,
d.user_logon_id,
d.us_citizen_ind,
d.user_job_title,
d.user_status,
d.user_employee_num,
d.email_address,
d.defaults_information_id,
d.trans_id,
i.trans_id
from deleted d, inserted i
where d.user_init = i.user_init
return
go
/* ***************************** */
create trigger transaction_touch_instrg
on transaction_touch
for insert
as
declare @.num_rows int,
@.count_num_rows int,
@.errmsg varchar(255)
select @.num_rows = @.@.rowcount
if @.num_rows = 0
return
insert into als_run_touch
(als_module_group_id, operation, entity_name,key1,key2,
key3,key4,key5,key6,key7,key8,trans_id,sequence,to uch_key)
select a.als_module_group_id,
case when i.operation = 'INSERT' then 'I'
when i.operation = 'UPDATE' then 'U'
when i.operation = 'DELETE' then 'D'
end,
i.entity_name,
i.key1,
i.key2,
i.key3,
i.key4,
i.key5,
i.key6,
i.key7,
i.key8,
i.trans_id,
i.sequence,
i.touch_key
from als_module_entity a,
inserted i
where ( ( ((a.operation_type_mask & 1) = 1) and (i.operation =
'INSERT') ) OR
( ((a.operation_type_mask & 2) = 2) and (i.operation =
'UPDATE') ) OR
( ((a.operation_type_mask & 4) = 4) and (i.operation =
'DELETE') ) ) AND
(a.entity_name = i.entity_name)
return
go
The aud_icts_user table does not have triggers. I hope this help
-- Peter Lo
"Shelby Goerlitz [MSFT]" <shelbyg@.nospam.microsoft.com> wrote in message
news:uaP2%235whFHA.2180@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Peter:
> We're going to investigate this one further and post back what we find.
> Can you post the trigger repro code -- that would be helpful.
> -shelby
> Shelby Goerlitz
> Microsoft, SQL Server
>
> "Peter Lo" <peter.lo@.tradecapture.com> wrote in message
> news:OLvobEkhFHA.3568@.TK2MSFTNGP10.phx.gbl...
Java[vbcol=seagreen]
an[vbcol=seagreen]
rows[vbcol=seagreen]
were[vbcol=seagreen]
driver
>
|||Peter --
thx for the info -- that is very helpful. just to let you know, dev is
still looking at this issue.
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"Peter Lo" <peter.lo@.tradecapture.com> wrote in message
news:OM5tnTyhFHA.3608@.TK2MSFTNGP12.phx.gbl...
> Shelby:
> The update statement executed by Java apps looks like this:
> update icts_user where user_init = '?' and trans_id = ?
> ==> In this example, only one icts_user record will be modified
> Here are the triggers:
> create trigger icts_user_updtrg
> on icts_user
> for update
> as
> declare @.num_rows int,
> @.count_num_rows int,
> @.errmsg varchar(255)
> select @.num_rows = @.@.rowcount
> if @.num_rows = 0
> return
> if update(loc_code)
> begin
> if exists (select 1 from inserted
> where loc_code not in (select loc_code from location))
> begin
> if @.num_rows = 1
> begin
> select @.errmsg = 'The loc_code ''' + loc_code + ''' is not a valid
> code defined in the location table.'
> from inserted
> end
> else
> begin
> select @.errmsg = 'The loc_code is not a valid code defined in the
> location table.'
> end
> raiserror 20002 @.errmsg
> rollback tran
> return
> end
> end
> if exists (select 1 from inserted, icts_user
> where icts_user.user_logon_id = rtrim(inserted.user_logon_id)
> and
> icts_user.user_init != inserted.user_init)
> begin
> if @.num_rows = 1
> begin
> select @.errmsg = 'The new user_logon_id ''' + user_logon_id + ''' has
> existed in the icts_user table. Duplicate is not allowed!'
> from inserted
> end
> else
> begin
> select @.errmsg = 'The new user_logon_id(s) have existed in the
> icts_user table. Duplicate is not allowed!'
> end
> raiserror 20003 @.errmsg
> rollback tran
> return
> end
> /* END_DATA_INTEGRITY */
> if not update(trans_id)
> begin
> raiserror 20090 '(icts_user) The change needs to be attached with a new
> trans_id'
> rollback tran
> return
> end
> if exists (select 1
> from master.dbo.sysprocesses
> where spid = @.@.spid and
> rtrim(program_name) IN ('ISQL-32', 'OSQL-32', 'SQL Query
> Analyzer') )
> begin
> if (select count(*) from inserted, deleted where inserted.trans_id <=
> deleted.trans_id) > 0
> begin
> select @.errmsg = '(icts_user) New trans_id must be larger than
> original trans_id.'
> select @.errmsg = @.errmsg + char(10) + 'You can use the the
> gen_new_transaction procedure to obtain a new trans_id.'
> raiserror 20092 @.errmsg
> rollback tran
> return
> end
> end
> if exists (select * from inserted i, deleted d
> where i.trans_id < d.trans_id and
> i.user_init = d.user_init )
> begin
> raiserror 20091 '(icts_user) new trans_id must not be older than current
> trans_id.'
> rollback tran
> return
> end
> select @.dummy_update = 0
> if update(user_init)
> begin
> select @.count_num_rows = (select count(*) from inserted i, deleted d
> where i.user_init = d.user_init )
> if (@.count_num_rows = @.num_rows)
> begin
> select @.dummy_update = 1
> end
> else
> begin
> raiserror 20072 '(icts_user) primary key can not be changed.'
> rollback tran
> return
> end
> end
> insert transaction_touch
> select 'UPDATE',
> 'IctsUser',
> 'DIRECT',
> convert(varchar(40), i.user_init),
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> i.trans_id,
> it.sequence
> from inserted i, icts_transaction it
> where i.trans_id = it.trans_id and
> it.type != 'E'
> if @.dummy_update = 0
> insert aud_icts_user
> (user_init,
> user_last_name,
> user_first_name,
> desk_code,
> loc_code,
> user_logon_id,
> us_citizen_ind,
> user_job_title,
> user_status,
> user_employee_num,
> email_address,
> defaults_information_id,
> trans_id,
> resp_trans_id)
> select
> d.user_init,
> d.user_last_name,
> d.user_first_name,
> d.desk_code,
> d.loc_code,
> d.user_logon_id,
> d.us_citizen_ind,
> d.user_job_title,
> d.user_status,
> d.user_employee_num,
> d.email_address,
> d.defaults_information_id,
> d.trans_id,
> i.trans_id
> from deleted d, inserted i
> where d.user_init = i.user_init
> return
> go
> /* ***************************** */
> create trigger transaction_touch_instrg
> on transaction_touch
> for insert
> as
> declare @.num_rows int,
> @.count_num_rows int,
> @.errmsg varchar(255)
> select @.num_rows = @.@.rowcount
> if @.num_rows = 0
> return
> insert into als_run_touch
> (als_module_group_id, operation, entity_name,key1,key2,
> key3,key4,key5,key6,key7,key8,trans_id,sequence,to uch_key)
> select a.als_module_group_id,
> case when i.operation = 'INSERT' then 'I'
> when i.operation = 'UPDATE' then 'U'
> when i.operation = 'DELETE' then 'D'
> end,
> i.entity_name,
> i.key1,
> i.key2,
> i.key3,
> i.key4,
> i.key5,
> i.key6,
> i.key7,
> i.key8,
> i.trans_id,
> i.sequence,
> i.touch_key
> from als_module_entity a,
> inserted i
> where ( ( ((a.operation_type_mask & 1) = 1) and (i.operation =
> 'INSERT') ) OR
> ( ((a.operation_type_mask & 2) = 2) and (i.operation =
> 'UPDATE') ) OR
> ( ((a.operation_type_mask & 4) = 4) and (i.operation =
> 'DELETE') ) ) AND
> (a.entity_name = i.entity_name)
> return
> go
>
> The aud_icts_user table does not have triggers. I hope this help
>
> -- Peter Lo
>
> "Shelby Goerlitz [MSFT]" <shelbyg@.nospam.microsoft.com> wrote in message
> news:uaP2%235whFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Java
> an
> rows
> were
> driver
>
|||Peter,
I used the following SQL to create some DB tables and triggers for my
testing and determined that the behavior you are seeing is by design.
The MS SQL Server driver for JDBC does not have the means to return the
update count for the statement executed by the application when that
statement causes other SQL DML statements to be executed via a trigger.
create table trig1(col1 int, col2 char(5))
create table trig2(col1 int, col2 char(5))
create table trig3(col1 int, col2 char(5))
create trigger inserttrig1
on trig1
for insert
as
if update(col1)
begin
insert into trig2 values(1,'row')
end
create trigger inserttrig2
on trig2
for insert
as
if update(col1)
begin
update trig3 set col1=1 where col2='none'
end
The inserttrig2 trigger will never affect any rows as there are no rows
in the trig3 table. This is intentional. I used a simple JDBC app to
insert a row into the trig1 table so I could see what kind of update
count was returned. With the MS SQL Server driver for JDBC, an update
count of 0 was returned as I expected. This is the update count for
the execution of the inserttrig2 trigger. Due to the way SQL Server
executes triggers and the resulting TDS packet sent to the driver upon
completion, the update count returned corresponds to the most nested
SQL statement that was executed in a trigger.
For example, if my application execute an insert statement that results
in trigger1 performing and insert which results in trigger2 performing
and update, then the results returned to the application are in the
order trigger2 update count, trigger1 update count, application
statement update count.
I ran a second test using the 3.5 DataDirect SQL Server driver for JDBC
and saw an update count of 1 returned which corresponded to the update
count for the initial insert. This is the correct behavior as the
DataDirect driver has the connection option alwaysReportTriggerResults
which defaults to false. As a result, the driver parses the results
and discards update counts for trigger that are returned by the server.
This results in the driver returning the update count for the
statement executed by the app.
When I set this option to true, then an update count of zero is
returned thereby behaving the same as the MS driver as expected.
If you wish to take advantage of this functionality, then I suggest
downloading the DataDirect driver and taking it for a spin. You can
download from here:
http://www.datadirect.com/download/index.ssp
-Brian
Brian Derwart
DataDirect Technologies SupportLink