11.3 Error Handling and Monitoring


Error handling is one of the most important things not only when working with databases. PHP and PostgreSQL have error-handling mechanisms that are easy to use and reliable.

Let's create a table for storing telephone numbers:

 phpbook=# CREATE TABLE phone (id serial, name text, phone text NOT NULL); NOTICE:  CREATE TABLE will create implicit sequence 'phone_id_seq' for SERIAL column 'phone.id' NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'phone_id_key' for table 'phone' CREATE 

The last column has been defined as NOT NULL, which means that a value has to be added to the column. In the next step you can try to write a script that tries to insert some data into the table:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $stat = pg_exec($dbh, "INSERT INTO phone (name) VALUES ('Susan')");         if      ($stat)         {                 echo "data inserted successfully<br>\n";         }         else         {                 echo "<br>an error has occurred<br>\n";                 echo pg_errormessage($dbh)."<br>";         }         pg_close($dbh); ?> 

After connecting to the database, an INSERT statement is executed. Because no telephone number has been passed to the database, an error will occur that can be displayed on the screen using pg_errormessage. The database handle, not the return value of the execute statement, has to be passed to the function this is an important point.

Let's see what the output is when the script is executed:

 connection to phpbook established ... Warning: PostgreSQL query failed: ERROR: ExecAppend: Fail to add null value in not null attribute phone in /var/www/html/createtab.php on line 6 an error has occurred ERROR: ExecAppend: Fail to add null value in not null attribute phone 

First pg_exec displays an error. After the error message you have displayed, the result of pg_errormessage has been printed on the screen. As you can see, PostgreSQL complains that a NULL value cannot be added to the table because the third column has been declared as NOT NULL.

Take a look at an additional example:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         echo "first attempt<br>\n";         $stat = pg_exec($dbh, "INSERT INTO phone (name) VALUES ('Susan')");         echo "<br>second attempt<br>\n";         $stat = pg_exec($dbh, "INSERT INTO phone (name, phone) VALUES                 ('Susan', '3432434')");         if      ($stat)         {                 echo "data inserted successfully<br>\n";                 echo "correct: ".pg_errormessage($dbh)."<br>";         }         else         {                 echo "<br>an error has occurred<br>\n";                 echo "wrong: ".pg_errormessage($dbh)."<br>";         }         pg_close($dbh); ?> 

The first SQL statement sent to the server violates the constraint defined on the third column. The second SQL statement is correct and the data will be added to the table. The most important thing in this example is to see that pg_errormessage does not return an error if the previous SQL statement has been executed successfully. This looks obvious but it isn't: In some database systems, functions always retrieve the most recent error even if a correct statement has been executed after the error has occurred.

Let's take a look at the listing generated by the PHP script:

 connection to phpbook established ... first attempt Warning: PostgreSQL query failed: ERROR: ExecAppend: Fail to add null value in not null attribute phone in /var/www/html/createtab.php on line 7 second attempt data inserted successfully correct: 

Sometimes you need to find out how many rows have been affected by a query. Especially when performing UPDATE operations, this can be useful to find out what has happened inside the database. The command used to extract the number of rows affected by a SQL statement is called pg_cmdtuples. You can see how this command works in the next listing:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $stat = pg_exec($dbh, "UPDATE phone SET phone='123456'                 WHERE name='Susan'");         if      ($stat)         {                 echo "UPDATE successful<br>\n";                 echo pg_cmdtuples($stat)."<br>";         }         pg_close($dbh); ?> 

In this scenario the phone number of Susan is updated. In our database Susan has been inserted once, so pg_cmdtuples has returned 1.

Let's see what is being displayed on the screen when the script is executed:

 connection to phpbook established ... UPDATE successful 1 

The result is in no way surprising.

Error handling is an easy task when working with PHP and PostgreSQL. As you have seen in this section, it takes only a few commands to perform almost all kinds of exception handling.



PHP and PostgreSQL. Advanced Web Programming2002
PHP and PostgreSQL. Advanced Web Programming2002
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net