HTML DEVELOPER’S GUIDE FOR ADOBE AIR
Working with local SQL databases in AIR
Last updated 9/28/2011
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
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
values. In addition, by doing so you can ensure that all
columns have an explicit value inserted.
Use default values
Whenever you specify a
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
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
constraints. Naturally, a
constraint is more difficult
to check for because doing so would require executing a
query to determine whether the data is unique.
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
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”
on page 341.
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
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.
Working with database data types
Adobe AIR 1.0 and later
When a table is created in a database, the SQL statement for creating the table defines the affinity, or data type, for each
column in the table. Although affinity declarations can be omitted, it’s a good idea to explicitly declare column affinity