by Ursego » 29 Jul 2019, 10:07
After each SQL, check the variable which stores the number of rows affected by the most recent SQL statement - to verify that the action completed properly.
That variable exist in each RDBMS and usually contains "rowcount" in its name. Just a few examples: @@rowcount in MS SQL Server and Sybase, SQL%ROWCOUNT in Oracle. Maybe, in your DB it's a function rather than a variable. If you are unsure, google the following: "<your RDBMS name> check how many rows affected by last sql".
The only exception from this rule is when the number of affected rows can by any (zero, one and more than one). For example, if you are updating a non-mandatory entity when it's OK to find many entities which satisfy the WHERE clause.
Assumptions, assumptions...
Perhaps the most prevalent and damaging mistake made during the development of SQL code is a failure to define or recognize the assumptions on which the implementation relies. The result is code that is brittle, and liable to behave unpredictably when these assumptions are invalidated by changes to the underlying database objects.
Changes to the keys in your tables should, hopefully, be rare, but they can cause trouble to the unwary when they happen. The examples, provided below, demonstrate what can happen to perfectly correct code when changes are made to the underlying unique or primary keys, thus invalidating assumptions in the code regarding the uniqueness of the column data. We'll then discuss how use of the rowcount variable can detect if such assumptions are still true.
We usually omit the check of the number of affected records if the WHERE clause is more than straightforward and compares only the Primary Key(s). But if it contains a more complicated logic (especially when many tables are involved), it's better to check what exactly has been done. Even though your SQL statement works now, requirements or circumstances can change in the future. For example, some field becomes nullable while your code is based on the fact that it's mandatory, the UNIQUE constraint is removed one day, or another field is added to (or removed from) a primary key (and the corresponding foreign keys), or whatever else happens. If such a change will be done in the future to the DB schema, somebody can incorrectly change your WHERE clause. Or it will be forgotten to be changed it at all, so the new situation will not be reflected. Checking the number of affected records doesn't guarantees that the fragment will be absolutely bugs-free forever, but can seriously decrease the likelihood of bugs in the future.
Throw an exception if your INSERT, UPDATE or DELETE statement affected less or more records than expected.
In the following example, the column phone_number the table customer has the UNIQUE constraint. The SQL uses that field to uniquely identify an employee:
- Code: Select all
update customer
set status = @status
where phone_number = @phone_number
Suppose, however, that at some later time that UNIQUE constraint is removed, so we can insert a customer with an identical phone number to an existing customer. The UPDATE statement keeps working on the assumption that a customer can be uniquely identified by their phone number. Since this assumption is no longer valid, the SQL, in its current form, could erroneously update more than one row of data. To catch that situation, check the rowcount variable immediately after the SQL statement. To facilitate investigation, the error message should display the values in the WHERE clause, by which the data failed to be updated.
That code is for Sybase, but it illustrates the approach for any database developer. If needed, customize for your RDBMS and add ROLLBACK depending on how your application manages transactions:
- Code: Select all
declare @err varchar(500)
update customer
set status = @status
where phone_number = @phone_number
if @@rowcount > 1 begin
set @err = 'Error in proc <stored proc name>:'
+ CHAR(10) + CHAR(13) + 'More than one row updated in customer table.'
+ CHAR(10) + CHAR(13) + '@phone_number=' + convert(varchar, @phone_number)
raiserror 20001 @err
end
Throw an exception if your SELECT statement returned more than one record (or zero records when the data is mandatory).
When you populate a variable in Oracle (SELECT ... INTO ... WHERE), you have two great exceptions NO_DATA_FOUND and TOO_MANY_ROWS which will be thrown automatically, so you are covered. But, unfortunately, not all databases have that luxury. For example, Sybase dummies up in both the situations (if many rows are returned, the first of them silently populates the variable ), so you need to write additional code to check how many rows have been returned. Of course, you expect not more than one row, since it's a scalar variable, not a cursor. If the SELECT must return a value, then 0 rows is considered an error as well. Here is a code example for Sybase. As you see, the error message displays the number of returned records to distinguish between two the situations - in addition to the values, by which the data failed to be retrieved:
- Code: Select all
declare
@err varchar(500)
,@rowcount int
select @status = status
from customer
where phone_number = @phone_number
set @rowcount = @@rowcount
if @rowcount <> 1 begin -- change "<>" to ">" if the query is allowed to return nothing
set @err = 'Error in proc <stored proc name>:'
+ CHAR(10) + CHAR(13) + convert(varchar, @rowcount) + ' rows retrieved from customer table.'
+ CHAR(10) + CHAR(13) + '@phone_number=' + convert(varchar, @phone_number)
raiserror 20001 @err
end
Of course, the most obvious lesson to be learned here is that whenever we change our unique and/or primary keys, we need to review all the procedures that depend on the modified tables. However, the manual process of reviewing the potentially affected code is, like all manual processes, slow and prone to error. It may be more efficient to automate the process of identifying the modules that rely on particular assumptions about the underlying schema.