Creating, retrieveing, updating and deleting data
This section illustrates how the Relational DAS can be used to create,
retrieve, update and delete data in a relational database.
Many of the examples are illustrated with a three-table database that
contains companies, departments within those companies, and employees
that work in those departments. This example is used in a number of
places within the SDO literature. See the examples section of the
» Service Data Objects specification
or the
Examples
section of the documentation for the SDO extension.
The Relational DAS is constructed with metadata that defines the
relational database and how it should be mapped to SDO.
The long section that follows describes this metadata and how to
construct the Relational DAS. The examples that follow it all assume that
this metadata is in an included php file.
The examples below and others can all be found in the
Scenarios
directory in the Relational DAS package.
The Relational DAS throws exceptions in the event that it finds errors
in the metadata or errors when executing SQL statements against the
database. For brevity the examples below all omit the use of try/catch
blocks around the calls to the Relational DAS.
These examples all differ from the expected use of SDO in two
important respects.
First, they show all interactions with the database completed within
one script. In this respect these scenarios are not realistic but are
chosen to illustrate just the use of the Relational DAS.
It is expected that interactions with the database will be separated
in time and the data graph serialised and deserialised into the PHP
session one or more times as the application interacts with an end user.
Second, all queries executed against the database use hard-coded
queries with no variables substituted. In this case it is safe to
use the simple
executeQuery()
call, and this is what the examples illustrate.
In practice, though, it is unlikely that the SQL statement is known
entirely ahead of time. In order to allow variables to be safely
substituted into the SQL queries, without running the risk of
injecting SQL with unknown effects, it is safer to use the
executePreparedQuery()
which takes a prepared SQL statement containing placeholders
and a list of values to be substituted.