In general,
database error handling is like other runtime error handling. You should
write code that is prepared for errors that may occur, and respond
to the errors rather than leave it up to the runtime to do so. In
a general sense, the possible database errors can be divided into
three categories: connection errors, SQL syntax errors, and constraint
errors.
Connection errors
Most database errors are connection errors, and they can occur
during any operation. Although there are strategies for preventing
connection errors, there is rarely a simple way to gracefully recover
from a connection error if the database is a critical part of your
application.
Most connection errors have to do with how the runtime interacts
with the operating system, the file system, and the database file.
For example, a connection error occurs if the user doesn’t have
permission to create a database file in a particular location on
the file system. The following strategies help to prevent connection
errors:
-
Use user-specific database files
-
Rather than using a single database file for all users who
use the application on a single computer, give each user their own database
file. The file should be located in a directory that’s associated
with the user’s account. For example, it could be in the application’s
storage directory, the user’s documents folder, the user’s desktop,
and so forth.
-
Consider different user types
-
Test your application with different types of user accounts,
on different operating systems. Don’t assume that the user has administrator
permission on the computer. Also, don’t assume that the individual
who installed the application is the user who’s running the application.
-
Consider various file locations
-
If you allow a user to specify where to save a database file
or select a file to open, consider the possible file locations that
the users might use. In addition, consider defining limits on where
users can store (or from where they can open) database files. For
example, you might only allow users to open files that are within
their user account’s storage location.
If a connection error
occurs, it most likely happens on the first attempt to create or
open the database. This means that the user is unable to do any
database-related operations in the application. For certain types
of errors, such as read-only or permission errors, one possible
recovery technique is to copy the database file to a different location.
The application can copy the database file to a different location
where the user does have permission to create and write to files,
and use that location instead.
Syntax errors
A syntax error occurs when a SQL statement is incorrectly formed,
and the application attempts to execute the statement. Because local
database SQL statements are created as strings, compile-time SQL
syntax checking is not possible. All SQL statements must be executed
to check their syntax. Use the following strategies to prevent SQL
syntax errors:
-
Test all SQL statements thoroughly
-
If possible, while developing your application test your
SQL statements separately before encoding them as statement text
in the application code. In addition, use a code-testing approach
such as unit testing to create a set of tests that exercise every
possible option and variation in the code.
-
Use statement parameters and avoid concatenating (dynamically
generating) SQL
-
Using parameters, and avoiding dynamically built SQL statements, means
that the same SQL statement text is used each time a statement is executed.
Consequently, it’s much easier to test your statements and limit
the possible variation. If you must dynamically generate a SQL statement,
keep the dynamic parts of the statement to a minimum. Also, carefully
validate any user input to make sure it won’t cause syntax errors.
To
recover from a syntax error, an application would need complex logic
to be able to examine a SQL statement and correct its syntax. By
following the previous guidelines for preventing syntax errors,
your code can identify any potential run-time sources of SQL syntax
errors (such as user input used in a statement). To recover from
a syntax error, provide guidance to the user. Indicate what to correct to
make the statement execute properly.
Constraint errors
Constraint errors occur when an
INSERT
or
UPDATE
statement
attempts to add data to a column. The error happens if the new data
violates one of the defined constraints for the table or column.
The set of possible constraints includes:
-
Unique constraint
-
Indicates that across all the rows in a table, there cannot
be duplicate values in one column. Alternatively, when multiple
columns are combined in a unique constraint, the combination of
values in those columns must not be duplicated. In other words,
in terms of the specified unique column or columns, each row must
be distinct.
-
Primary key constraint
-
In terms of the data that a constraint allows and doesn’t allow,
a primary key constraint is identical to a unique constraint.
-
Not null constraint
-
Specifies that a single column cannot store a
NULL
value and
consequently that in every row, that column must have a value.
-
Check constraint
-
Allows you to specify an arbitrary constraint on one or more tables.
A common check constraint is a rule that define that a column’s
value must be within certain bounds (for example, that a numeric
column’s value must be larger than 0). Another common type of check
constraint specifies relationships between column values (for example,
that a column’s value must be different from the value of another
column in the same row).
-
Data type (column affinity) constraint
-
The runtime enforces the data type of columns’ values, and
an error occurs if an attempt is made to store a value of the incorrect
type in a column. However, in many conditions values are converted
to match the column’s declared data type. See
Working with database data types
for more information.
The
runtime does not enforce constraints on foreign key values. In other
words, foreign key values aren’t required to match an existing primary
key value.
In addition to the predefined constraint types,
the runtime SQL engine supports the use of triggers. A trigger is
like an event handler. It is a predefined set of instructions that
are carried out when a certain action happens. For example, a trigger
could be defined that runs when data is inserted into or deleted
from a particular table. One possible use of a trigger is to examine
data changes and cause an error to occur if specified conditions
aren’t met. Consequently, a trigger can serve the same purpose as
a constraint, and the strategies for preventing and recovering from
constraint errors also apply to trigger-generated errors. However,
the error id for trigger-generated errors is different from the
error id for constraint errors.
The set of constraints that
apply to a particular table is determined while you’re designing
an application. Consciously designing constraints makes it easier
to design your application to prevent and recover from constraint
errors. However, constraint errors are difficult to systematically
predict and prevent. Prediction is difficult because constraint
errors don’t appear until application data is added. Constraint
errors occur with data that is added to a database after it’s created. These
errors are often a result of the relationship between new data and
data that already exists in the database. The following strategies
can help you avoid many constraint errors:
-
Carefully plan database structure and constraints
-
The purpose of constraints is to enforce application rules
and help protect the integrity of the database’s data. When you’re
planning your application, consider how to structure your database
to support your application. As part of that process, identify rules
for your data, such as whether certain values are required, whether a
value has a default, whether duplicate values are allowed, and so
forth. Those rules guide you in defining database constraints.
-
Explicitly specify column names
-
An
INSERT
statement can be written without
explicitly specifying the columns into which values are to be inserted, but
doing so is an unnecessary risk. By explicitly naming the columns
into which values are to be inserted, you can allow for automatically
generated values, columns with default values, and columns that
allow
NULL
values. In addition, by doing so you
can ensure that all
NOT NULL
columns have an explicit
value inserted.
-
Use default values
-
Whenever you specify a
NOT NULL
constraint
for a column, if at all possible specify a default value in the
column definition. Application code can also provide default values.
For example, your code can check if a String variable is
null
and
assign it a value before using it to set a statement parameter value.
-
Validate user-entered data
-
Check user-entered data ahead of time to make sure that it
obeys limits specified by constraints, especially in the case of
NOT NULL
and
CHECK
constraints.
Naturally, a
UNIQUE
constraint is more difficult
to check for because doing so would require executing a
SELECT
query
to determine whether the data is unique.
-
Use triggers
-
You can write a trigger that validates (and possibly replaces) inserted
data or takes other actions to correct invalid data. This validation
and correction can prevent a constraint error from occurring.
In
many ways constraint errors are more difficult to prevent than other
types of errors. Fortunately, there are several strategies to recover
from constraint errors in ways that don’t make the application unstable
or unusable:
-
Use conflict algorithms
-
When you define a constraint on a column, and when you create
an
INSERT
or
UPDATE
statement,
you have the option of specifying a conflict algorithm. A conflict
algorithm defines the action the database takes when a constraint
violation occurs. There are several possible actions the database
engine can take. The database engine can end a single statement
or a whole transaction. It can ignore the error. It can even remove
old data and replace it with the data that the code is attempting
to store.
For more information see the section “ON CONFLICT
(conflict algorithms)” in the
SQL support in local databases
.
-
Provide corrective feedback
-
The set of constraints that can affect a particular SQL command
can be identified ahead of time. Consequently, you can anticipate constraint
errors that a statement could cause. With that knowledge, you can build
application logic to respond to a constraint error. For example,
suppose an application includes a data entry form for entering new
products. If the product name column in the database is defined
with a
UNIQUE
constraint, the action of inserting
a new product row in the database could cause a constraint error. Consequently,
the application is designed to anticipate a constraint error. When the
error happens, the application alerts the user, indicating that
the specified product name is already in use and asking the user
to choose a different name. Another possible response is to allow
the user to view information about the other product with the same
name.
|
|
|