Embedded SQL

Whereas there are many languages available for writing complete applications, SQL is the only standard language for handling relational structures. Most languages therefore provide ways of embedding SQL commands into structured programs. A particularly good example of this is provided by PHP which has dedicated sets of commands for linking to MySQL databases.

PHP (Pre-Hypertext Processing) is a scripting language that can be used to produce dynamic Web content. If you are not familiar with PHP the examples below should still make sense. The important aspect is that PHP is a procedural language with the full range of control statements and data structures. Likewise, the examples refer to MySQL databases because of their tight integration with PHP. However, PHP can also be used with other databases including Oracle, and if you are interested in following this topic up, you can follow the resources link on the left.

Connecting to a database

Connecting to a database from a programming language is just the same as connecting with a tool like SQL Developer. You need to tell the software where the database is and what account to use. PHP provides a simple command to do this for MySQL databases. Note that PHP variables always start with a dollar sign and that a double slash indicates a comment:

    $db_host = "server_1";
    $username = "hr";
    $password = "hr";

    // Connect to MySQL
    mysql_connect($db_host, $username, $password);

Constructing and sending a query

A text string representing a query can be defined in a single assignment, or it can be built up gradually from literal text and variable values. The example below illustrates the second case. When the query text is complete, it is executed and an array of results is created. The results array is two-dimensional since each row is an array element in the vertical dimension, but because it is made up of columns, it is itself an array in the horizontal dimension.

In the example below, note that the string concatenation operator is a dot and that the operator .= appends text to the named variable. Also note that because the query string is built up over several concatenation operations, it is important that each part ends in a space character.

    $query  = "SELECT employee_id, first_name, last_name ";
    $query .= "FROM employees ";
    $query .= "WHERE department_id = " . $deptId . " ";
    $query .= "ORDER BY last_name";
    $result = mysql_query($query);

If you are using text values in the where clause of the query, you will also need to remember to handle any problem characters just as you would in any other situation. For example, single quote characters would need to be doubled to prevent them from being interpreted as string delimiters.

Processing query results

Many programming languages have the concept of a cursor which is a local array variable containing the values for the current row in a result set. As the results are processed the cursor is moved to each row in turn. PHP does not use this term explicitly, but the processing follows the same sequence and is controlled using standard language constructs. The example below shows the use of a while loop to iterate through each result row in turn. Each iteration of the loop generates a new row in an HTML table, and therefore illustrates how a dynamic Web page might be produced. Note that the PHP echo command sends the subsequent values to the standard output channel.

    echo "<table>";
    while ($loc = mysql_fetch_assoc($result)) {
       echo "<tr>";
       echo "<td>" . $loc{employee_id} . "</td>";
       echo "<td>" . $loc{first_name} . "</td>";
       echo "<td>" . $loc{last_name} . "</td>";
       echo "</tr>";
    }
    echo "</table>";

The standard term fetch is used to describe the assignment of a set of database values to a local variable.

Null values

Missing values in a database are shown as null values in a set of results and this can cause problems when trying to process those results in an application program. For example, a null value in a number column may produce an error if the application program attempts to compare the value with another number. To avoid such problems, it may be advisable to ensure that potential null values are handled by the query so that appropriate defaults are used instead of nulls in the results. The SQL function COALESCE (available in both Oracle and MySQL) can be used to do this.

Error handling You will already be used to seeing database error message that tell you when an operation is not successful for some reason. Because the same errors can occur when using SQL inside a program, it is important to have a way of passing those messages back to the user. When a PHP operation fails, the construct or die() can be used to generate error messages, and the example below shows how the most recent SQL error can be embedded into the message string. This same method can be used for any embedded SQL operation.

    $result = mysql_query($query) or
       die("Unable to retrieve employee information: " . mysql_error());

DML

Although the PHP function is called mysql_query( ), it can actually be used to execute any arbitrary SQL statement including DML. The example below shows how a record can be updated. It includes error checking, and an additional message to the user showing how many rows were updated using the function mysql_affected_rows( ).

    $query  = "UPDATE employees SET commission_pct = " . $newCommission . " ";
    $query .= "WHERE employee_id = " . $empId;
    $result = mysql_query($query) or
              die("Unable to update commission: " . mysql_error());

    echo mysql_affected_rows() . " row(s) updated";

Closing the database connection

Typically a database allows only a limited number of simultaneous connections. Once the program has completed its database operations it is important to close the connection in order to avoid blocking other users. In PHP this is accomplished as follows:

    mysql_close();