Fetching Databases notices in PDO

• Version: 1.0
• Date: 2009-13-10
• Author: Samuel ROZE samuel.roze@gmail.com
• Status: Proposal for new patches
• Other formats:
  1. PDF: http://www.d-sites.com/wp-content/uploads/2009/10/RFC-PDO-Notices.pdf

The most important Databases have a functionality which returns some notices about the current query. These notices can be raised by the Database engine within a CREATE TABLE, UPDATE or DELETE query for instance or by a user function.

Generally, notices contain information about the parallel executions, about the number of fetched rows or about the performances of the query.

In PDO, notices are stored into a “notices” HashTable in the “_pdo_dbh_t” structure. Notices are inserted into it by each Database's PDO's driver using:

zend_hash_next_index_insert(dbh->notices, &val, sizeof(zval *), NULL);

Where zval is the zval message string and val the allocated message string.

This is done if user have specified that he want to fetch notices using the setAttribute or the $option parameter on the PDO constructor.

• The name of the attribute is: PDO::ATTR_LOG_NOTICES
• The possible values are:
  • PDO::NOTICES_NONE -- no fetching
  • PDO::NOTICES_ARRAY -- fetching & noticeInfo returning value as an array

The name of the function that users have use to get notices is “noticeInfo” which returns an array (when PDO::NOTICES_ARRAY is the value of PDO::ATTR_LOG_NOTICES). The name is based on the “errorInfo” function to be more user-friendly.

Note: noticeInfo is a PDO function, not a PDOStatement function.

Notices in PostgreSQL are raised in Pl/PgSQL with the “RAISE NOTICE” command. They can be fetched using a notice processor defined by the “PQsetNoticeProcessor” pointing on a “_pdo_pgsql_notice” function which insert the message into dbh->notices like the principle described into the Get notices in PDO part.

In MySQL, the working of notices is NOT like PostgreSQL. MySQL raise notices within simple queries like UPDATE or DELETE. These notices cannot be raised by a user function.

In the MySQL C API, there's a function whose purpose is to get the number of warnings (and notices) for the last query. This function is mysql_warning_count()
[http://dev.mysql.com/doc/refman/5.1/en/mysql-warning-count.html]

If mysql_warning_count result is greater than or equal to 1, there are warnings and we will get them. To get these warning, we have to fetch results of the “SHOW WARNINGS” query.
[http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html]

If mysql_warning_count() = 0, we can get the result of mysql_info which returns informations about the last query. If the result of it is different to NULL, put the results into dbh->notices.
[http://dev.mysql.com/doc/refman/5.1/en/mysql-info.html]

In Oracle, notices can be raised by DBMS_OUTPUT within a Pl/SQL function and some notifications are created by the Oracle Server.

“Call OCIInitialize() with OCI_EVENTS mode to specify that the application is interested in registering for and receiving notifications. This starts a dedicated listening thread for notifications on the client.”
[http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci09adv.htm#sthref1428]

Informations about notification callback which I didn't understand very much: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci09adv.htm#sthref1446

This is about getting notifications from Oracle: http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28395/oci10new.htm#sthref1457

Global method for Oracle, in addition of these previous methods, to get DMBS_OUTPUT:

• Execute

  BEGIN DBMS_OUTPUT.ENABLE(NULL); END;

  when PDO::ATTR_LOG_NOTICES is turned a value different to PDO::NOTICES_NONE.
  

• After each successful queries, fetch:

  BEGIN DBMS_OUTPUT.GET_LINE(:LN, :ST); END;

  with :LN and :ST two OUT parameters which define the informations and the number of them.
  

• Execute

  BEGIN DBMS_OUTPUT.DISABLE(); END;

  when PDO::ATTR_LOG_NOTICES is turned a value equal to PDO::NOTICES_NONE.
  

For others Databases who have a notice functionality, we have to reflect. Personally, i do not know which Databases...