This is an old revision of the document!
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:
Databases notices
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.
Get notices in PDO
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 is 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.
Get notices with PostgreSQL
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.
Get notices in MySQL
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]
Get notices in Oracle
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
For others Databases who have a notice functionality, we have to reflect. Personally, i do not know which Databases...