PDO PgSQL: _pdo_pgsql_notice

15 years ago by Samuel ROZE — view source

unread

Hi !

I'm reading the source of PDO PostgreSQL driver and i see that there's
an empty function, _pdo_pgsql_notice, which is very interesting ! I
think that it is used when PostgreSQL throw Notices.
But, nothing is done...

I want to know if somebody know why the content of the function is
commented:

static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char message) /
{{{ /
{
/ pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data; */
}

(line 98 of /etc/pdo_pgsql/pgsql_driver.c - PHP 5.2 - r272374)

How can I store the content of "message" into a field of the
PDOStatement ? I view that there's only a "pdo_dbh_t" param, I may just
store this message into a field of the PDO class ?

Thanks in advance.
Samuel.

15 years ago by Samuel ROZE — view source

unread

To use PostgreSQL's Notices with PDO, i'm modifying the source for
trying to store into "errmsg" (which I can get with PDO::errorInfo) the
notice.

The "_pdo_pgsql_notice" function is called everytime that there's an
notice. The notice message is in the "message" var. This what i had
tried:

static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char message) /
{{{ */
{
pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle
*)dbh->driver_data;
pdo_pgsql_error_info *einfo = &H->einfo;

    einfo->errcode = 1;

    if (einfo->errmsg) {
            pefree(einfo->errmsg, dbh->is_persistent);
            einfo->errmsg = NULL;
    }
    einfo->errmsg = _pdo_pgsql_trim_message(message,

dbh->is_persistent);
}

And in "pdo_pgsql_fetch_error_func", i've added an else condition to
view if errmsg is null are not...

    if (einfo->errcode) {
            add_next_index_long(info, einfo->errcode);
            add_next_index_string(info, einfo->errmsg, 1);
    } else {
zend_throw_exception_ex(php_pdo_get_exception(), 0 TSRMLS_CC,

"einfo->errcode is false : %s , %s", einfo->errcode, einfo->errmsg);
}

And, I've a function which send some notices (when I throw exception
from "_pdo_pgsql_notice" with the "message" var, I see the notice ! :-)
But, einfo->errcode is null !

-> Fatal error: Uncaught exception 'PDOException' with message
'einfo->errcode is false : (null) , (null)'
in /etc/php-5.2.10/test/pgsql_notice.php:22
Stack trace:
#0 /etc/php-5.2.10/test/pgsql_notice.php(22): PDO->errorInfo()
#1 {main}
thrown in /etc/php-5.2.10/test/pgsql_notice.php on line 22

Is anyone can help me ?
Thanks a lot !

Samuel. (French)

Le mardi 06 octobre 2009 à 07:22 +0200, Samuel ROZE a écrit :

Hi !

I'm reading the source of PDO PostgreSQL driver and i see that there's
an empty function, _pdo_pgsql_notice, which is very interesting ! I
think that it is used when PostgreSQL throw Notices.
But, nothing is done...

I want to know if somebody know why the content of the function is
commented:

static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char message) /
{{{ /
{
/ pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data; */
}

(line 98 of /etc/pdo_pgsql/pgsql_driver.c - PHP 5.2 - r272374)

How can I store the content of "message" into a field of the
PDOStatement ? I view that there's only a "pdo_dbh_t" param, I may just
store this message into a field of the PDO class ?

Thanks in advance.
Samuel.

15 years ago by Matteo Beccati — view source

unread

Samuel ROZE ha scritto:

To use PostgreSQL's Notices with PDO, i'm modifying the source for
trying to store into "errmsg" (which I can get with PDO::errorInfo) the
notice.

I might find some time to look at it shortly. Not sure using "errmsg" is
appropriate though, as notices are usually returning additional
information, not error messages.

Please allow some time for me to check how they are currently dealt with
in the pgsql extension and to see how they can be implemented in the
PDO driver.

Cheers

Matteo Beccati

15 years ago by Samuel ROZE — view source

unread

Hi,

I might find some time to look at it shortly. Not sure using "errmsg" is
appropriate though, as notices are usually returning additional
information, not error messages.

I think i'll have to make a "noticemsg" var, but I don't
know how implement a new "noticeInfo" (or somethink like
that) function to get this value. The better way is to
create a new array "notices" (or a function which returns an
array) into the PDOStatement, because the notice is
generally linked to the statement. But, sometimes not...

Please allow some time for me to check how they are currently dealt with
in the pgsql extension and to see how they can be implemented in the
PDO driver.

Actually, the PosgreSQL driver for PDO don't report
notices.. The content of "_pdo_pgsql_notice" is commented !

Thanks a lot for the time you passed/you'll pass !
Samuel.

15 years ago by Matteo Beccati — view source

unread

Hi,

Please allow some time for me to check how they are currently dealt with
in the pgsql extension and to see how they can be implemented in the
PDO driver.

Actually, the PosgreSQL driver for PDO don't report
notices.. The content of "_pdo_pgsql_notice" is commented !

Thanks a lot for the time you passed/you'll pass !

Here's a proof of concept patch you can apply to the PHP_5_3 branch:

http://www.beccati.com/misc/php/pdo_pgsql_notices_php5_3.patch

I still need to clean it up a bit and discuss it with the team, but
here's how it currently works:

$db = new PDO(...);
$db->setAttribute(PDO::PGSQL_ATTR_ENABLE_NOTICES, true);

$db->exec($query); // works with $db->query() too
$aNotices = $db->pgsqlGetNotices();

And you will get an array of the notices raised by the last query.

To sum up:

Notices are disabled by default, minimising the overhead
Unlike the pgsql extension which returns the most recent one, multiple
notices can be fetched
A new query will reset the stored notices
Available on the base class because exec() doesn't return a PDOStatement

Hope this helps

Cheers

Matteo Beccati

15 years ago by Samuel ROZE — view source

unread

Thanks all lot for your work ! It is be what I want !

I'll view it this evening and I probably will transform it for PHP 5.2
(which is my version) and put it here.

At tonight ! :-)
Samuel.

Le mercredi 07 octobre 2009 à 00:31 +0200, Matteo Beccati a écrit :

Hi,

Please allow some time for me to check how they are currently dealt with
in the pgsql extension and to see how they can be implemented in the
PDO driver.

Actually, the PosgreSQL driver for PDO don't report
notices.. The content of "_pdo_pgsql_notice" is commented !

Thanks a lot for the time you passed/you'll pass !

Here's a proof of concept patch you can apply to the PHP_5_3 branch:

http://www.beccati.com/misc/php/pdo_pgsql_notices_php5_3.patch

I still need to clean it up a bit and discuss it with the team, but
here's how it currently works:

$db = new PDO(...);
$db->setAttribute(PDO::PGSQL_ATTR_ENABLE_NOTICES, true);

$db->exec($query); // works with $db->query() too
$aNotices = $db->pgsqlGetNotices();

And you will get an array of the notices raised by the last query.

To sum up:

Notices are disabled by default, minimising the overhead

Unlike the pgsql extension which returns the most recent one, multiple
notices can be fetched

A new query will reset the stored notices

Available on the base class because exec() doesn't return a PDOStatement

Hope this helps

Cheers

Matteo Beccati

15 years ago by Samuel ROZE — view source

unread

Well, i've show the patch this morning... It's great ! The allocation of
the HashTable when use setAttribute might not be really a good job but
other, in my mind, is !

See you afternoon for PHP 5.2 path. :-)

Samuel.

Le mercredi 07 octobre 2009 à 00:31 +0200, Matteo Beccati a écrit :

Hi,

Please allow some time for me to check how they are currently dealt with
in the pgsql extension and to see how they can be implemented in the
PDO driver.

Actually, the PosgreSQL driver for PDO don't report
notices.. The content of "_pdo_pgsql_notice" is commented !

Thanks a lot for the time you passed/you'll pass !

Here's a proof of concept patch you can apply to the PHP_5_3 branch:

http://www.beccati.com/misc/php/pdo_pgsql_notices_php5_3.patch

I still need to clean it up a bit and discuss it with the team, but
here's how it currently works:

$db = new PDO(...);
$db->setAttribute(PDO::PGSQL_ATTR_ENABLE_NOTICES, true);

$db->exec($query); // works with $db->query() too
$aNotices = $db->pgsqlGetNotices();

And you will get an array of the notices raised by the last query.

To sum up:

Notices are disabled by default, minimising the overhead

Unlike the pgsql extension which returns the most recent one, multiple
notices can be fetched

A new query will reset the stored notices

Available on the base class because exec() doesn't return a PDOStatement

Hope this helps

Cheers

15 years ago by Matteo Beccati — view source

unread

Hi,

Well, i've show the patch this morning... It's great ! The allocation of
the HashTable when use setAttribute might not be really a good job but
other, in my mind, is !

Yeah, I'm not very happy about it either. It was just a proof of concept
after all ;)

Cheers

Matteo Beccati

15 years ago by Samuel ROZE — view source

unread

Le mercredi 07 octobre 2009 à 07:57 +0200, Samuel ROZE a écrit :

Well, i've show the patch this morning... It's great ! The allocation of
the HashTable when use setAttribute might not be really a good job but
other, in my mind, is !

See you afternoon for PHP 5.2 path. :-)

Well, I've done a patch for PHP 5.2 branch:
http://www.d-sites.com/wp-content/uploads/2009/10/pdo_pgsql_notices_php5.2.patch

It is working but there are two memory leaks when running with CGI:

[Wed Oct 7 15:20:10 2009] Script: 'pgsql_notice.php'
/usr/src/php-5.2.10/ext/pdo_pgsql/pgsql_driver.c(105) : Freeing
0x01C43A80 (24 bytes), script=pgsql_notice.php
[Wed Oct 7 15:20:10 2009] Script: 'pgsql_notice.php'
/usr/src/php-5.2.10/ext/pdo_pgsql/pgsql_driver.c(55) : Freeing
0x01C43AF0 (22 bytes), script=pgsql_notice.php
=== Total 2 memory leaks detected ===

Joined, the pgsql_driver.c file.

15 years ago by Pierre Joye — view source

unread

hi,

The idea is good and matches my thoughts on this topic.

Some comments:

no driver specific API, so getNotices instaed of get<driver>Notices
common attribute name as well and maybe a better naming ATTR_LOG_NOTICES?

5.2 is also only for security fixes. Can you try to produce a patch
against 5.3 and trunk please?

Cheers,

Le mercredi 07 octobre 2009 à 07:57 +0200, Samuel ROZE a écrit :

Well, i've show the patch this morning... It's great ! The allocation of
the HashTable when use setAttribute might not be really a good job but
other, in my mind, is !

See you afternoon for PHP 5.2 path. :-)

Well, I've done a patch for PHP 5.2 branch:
http://www.d-sites.com/wp-content/uploads/2009/10/pdo_pgsql_notices_php5.2.patch

It is working but there are two memory leaks when running with CGI:

[Wed Oct 7 15:20:10 2009] Script: 'pgsql_notice.php'
/usr/src/php-5.2.10/ext/pdo_pgsql/pgsql_driver.c(105) : Freeing
0x01C43A80 (24 bytes), script=pgsql_notice.php
[Wed Oct 7 15:20:10 2009] Script: 'pgsql_notice.php'
/usr/src/php-5.2.10/ext/pdo_pgsql/pgsql_driver.c(55) : Freeing
0x01C43AF0 (22 bytes), script=pgsql_notice.php
=== Total 2 memory leaks detected ===

Joined, the pgsql_driver.c file.

--

--
Pierre

http://blog.thepimp.net | http://www.libgd.org

15 years ago by Samuel ROZE — view source

unread

Le mercredi 07 octobre 2009 à 15:47 +0200, Pierre Joye a écrit :

The idea is good and matches my thoughts on this topic.

Some comments:

no driver specific API, so getNotices instaed of get<driver>Notices

It was discuss, it need hours to implement this method to all
drivers...

common attribute name as well and maybe a better naming ATTR_LOG_NOTICES?

Why not, but it must be in the same time that the last question

5.2 is also only for security fixes. Can you try to produce a patch
against 5.3 and trunk please?

There's the patch of Matteo Beccati at this address for PHP 5.3:
http://www.beccati.com/misc/php/pdo_pgsql_notices_php5_3.patch

I can make another for the trunk, why not :-). I'll probably try to make
this functionality as global today or tomorrow.

Thanks for your feedback.
Samuel.

Cheers,

Le mercredi 07 octobre 2009 à 07:57 +0200, Samuel ROZE a écrit :

Well, i've show the patch this morning... It's great ! The allocation of
the HashTable when use setAttribute might not be really a good job but
other, in my mind, is !

See you afternoon for PHP 5.2 path. :-)

Well, I've done a patch for PHP 5.2 branch:
http://www.d-sites.com/wp-content/uploads/2009/10/pdo_pgsql_notices_php5.2.patch

It is working but there are two memory leaks when running with CGI:

[Wed Oct 7 15:20:10 2009] Script: 'pgsql_notice.php'
/usr/src/php-5.2.10/ext/pdo_pgsql/pgsql_driver.c(105) : Freeing
0x01C43A80 (24 bytes), script=pgsql_notice.php
[Wed Oct 7 15:20:10 2009] Script: 'pgsql_notice.php'
/usr/src/php-5.2.10/ext/pdo_pgsql/pgsql_driver.c(55) : Freeing
0x01C43AF0 (22 bytes), script=pgsql_notice.php
=== Total 2 memory leaks detected ===

Joined, the pgsql_driver.c file.

15 years ago by Matteo Beccati — view source

unread

Samuel ROZE ha scritto:

Le mercredi 07 octobre 2009 à 15:47 +0200, Pierre Joye a écrit :

5.2 is also only for security fixes. Can you try to produce a patch
against 5.3 and trunk please?

There's the patch of Matteo Beccati at this address for PHP 5.3:
http://www.beccati.com/misc/php/pdo_pgsql_notices_php5_3.patch

I can make another for the trunk, why not :-). I'll probably try to make
this functionality as global today or tomorrow.

Cool, I'll leave that to you and I'll make myself available for reviewing.

Cheers

Matteo Beccati

15 years ago by Matteo Beccati — view source

unread

Hi Christopher,

What are notices? Do they relate anything in other Databases?
I.e. can a generic interface be implemented instead of a postgres
specific one?

I'm not sure. Notices are non-error messages that can be triggered
during a query for informative purposes. For example CREATE TABLE
triggers some notices when sequences or indexes are automatically created:

test=# create table foo (id serial not null primary key);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for
serial column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE

or they can be raised during a function call:

test=# CREATE FUNCTION foo() RETURNS void AS $$DECLARE i int; BEGIN FOR
i IN 1..3 LOOP RAISE NOTICE 'i = %', i; END LOOP; END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
test=# SELECT foo();
NOTICE: i = 1
NOTICE: i = 2
NOTICE: i = 3
foo

(1 row)

On PostgreSQL they require a special handler to be set to catch them,
but the default behaviour would be to ignore them because they are
usually not particularly interesting.

Cheers

Matteo Beccati

15 years ago by Matteo Beccati — view source

unread

Christopher Jones ha scritto:

Could you use the new PG specific attribute to enable them
but make them output/handled by the existing error/exception
interface?

That's what I originally thought. But there can be multiple notices
triggered by a single query and they shouldn't make the query fail (i.e.
throwing an exception would be awkward for a successful query).

Cheers

Matteo Beccati

15 years ago by Lukas Kahwe Smith — view source

unread

Christopher Jones ha scritto:

Could you use the new PG specific attribute to enable them
but make them output/handled by the existing error/exception
interface?

That's what I originally thought. But there can be multiple notices
triggered by a single query and they shouldn't make the query fail
(i.e.
throwing an exception would be awkward for a successful query).

MySQL has similar notices. Like when stuff gets truncated etc.
I also think that using the current "error modes" is probably not
ideal. Or rather these arent exception worthy, and they should
obviously not return false either.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

15 years ago by Christopher Jones — view source

unread

Lukas Kahwe Smith wrote:

Christopher Jones ha scritto:

Could you use the new PG specific attribute to enable them
but make them output/handled by the existing error/exception
interface?

That's what I originally thought. But there can be multiple notices
triggered by a single query and they shouldn't make the query fail (i.e.
throwing an exception would be awkward for a successful query).

MySQL has similar notices. Like when stuff gets truncated etc.
I also think that using the current "error modes" is probably not ideal.
Or rather these arent exception worthy, and they should obviously not
return false either.

Is there a common interface that would suit both MySQL & Postgres usage?

Chris

--
Blog: http://blogs.oracle.com/opal
Twitter: http://twitter.com/ghrd

15 years ago by Matteo Beccati — view source

unread

Christopher Jones wrote:

Lukas Kahwe Smith wrote:

Christopher Jones ha scritto:

Could you use the new PG specific attribute to enable them
but make them output/handled by the existing error/exception
interface?

That's what I originally thought. But there can be multiple notices
triggered by a single query and they shouldn't make the query fail (i.e.
throwing an exception would be awkward for a successful query).

MySQL has similar notices. Like when stuff gets truncated etc.
I also think that using the current "error modes" is probably not
ideal. Or rather these arent exception worthy, and they should
obviously not return false either.

Is there a common interface that would suit both MySQL & Postgres usage?

From the user perspective I'd say yes. We can surely make generic
attributes and methods to deal with warnings/notices/dbms_output.

From what I could see, things get a bit more complicate on the code side:

PgSQL:

Set a notice processor callback
Clear the notice buffer before a query
Asynchronously buffer notices inside the callback
Set a no-op processor callback

MySQL:

Call mysql_warning_count() after a succesful query
if the result is non-zero, execute "SHOW WARNINGS" and fetch the results

Oracle:

Enable output buffering
Fetch the buffer after a successful query
Clear the buffer
Disable output buffering

Adding generic hooks/functions will also require a PDO API version bump.

Cheers

Matteo Beccati

15 years ago by Pierre Joye — view source

unread

hi,

Christopher Jones wrote:

Lukas Kahwe Smith wrote:

Christopher Jones ha scritto:

Could you use the new PG specific attribute to enable them
but make them output/handled by the existing error/exception
interface?

That's what I originally thought. But there can be multiple notices
triggered by a single query and they shouldn't make the query fail (i.e.
throwing an exception would be awkward for a successful query).

MySQL has similar notices. Like when stuff gets truncated etc.
I also think that using the current "error modes" is probably not
ideal. Or rather these arent exception worthy, and they should
obviously not return false either.

Is there a common interface that would suit both MySQL & Postgres usage?

From the user perspective I'd say yes. We can surely make generic
attributes and methods to deal with warnings/notices/dbms_output.

From what I could see, things get a bit more complicate on the code side:

PgSQL:

Set a notice processor callback

Clear the notice buffer before a query

Asynchronously buffer notices inside the callback

Set a no-op processor callback

MySQL:

Call mysql_warning_count() after a succesful query

if the result is non-zero, execute "SHOW WARNINGS" and fetch the results

Oracle:

Enable output buffering

Fetch the buffer after a successful query

Clear the buffer

Disable output buffering

Adding generic hooks/functions will also require a PDO API version bump.

Having them part of the PHP errors is counter intuitive and add extra
work for little gain. Mysql being the most cleaner way to do it at
this stage as it does not interfer with php code at all.

Could we make it independent from php's errors (be notices or
warnings)? For example to add a PDO statement method to fetch these
messages after having executed or prepared a query, getMessages for
example.

Cheers,

Pierre

http://blog.thepimp.net | http://www.libgd.org

15 years ago by Matteo Beccati — view source

unread

Pierre Joye ha scritto:

Having them part of the PHP errors is counter intuitive and add extra
work for little gain. Mysql being the most cleaner way to do it at
this stage as it does not interfer with php code at all.

Yes. That's exactly why I added a new method, although driver specific.

Could we make it independent from php's errors (be notices or
warnings)? For example to add a PDO statement method to fetch these
messages after having executed or prepared a query, getMessages for
example.

The PDO::exec(), method doesn't return a PDOStatement. With exec being
the most common way to execute DDL or DML queries, the ones that are
more likely to return a notice or warning.

Cheers

Matteo Beccati

15 years ago by Lester Caine — view source

unread

( Having stripped 6 people from the reply list ... )

Matteo Beccati wrote:

Pierre Joye ha scritto:

Having them part of the PHP errors is counter intuitive and add extra
work for little gain. Mysql being the most cleaner way to do it at
this stage as it does not interfer with php code at all.

Yes. That's exactly why I added a new method, although driver specific.

Could we make it independent from php's errors (be notices or
warnings)? For example to add a PDO statement method to fetch these
messages after having executed or prepared a query, getMessages for
example.

The PDO::exec(), method doesn't return a PDOStatement. With exec being
the most common way to execute DDL or DML queries, the ones that are
more likely to return a notice or warning.

Hopefully now we can get back to addressing the areas where PDO is
simply not up to the job? I'm sure half of the reason that there has
been no real rush to support PDO from some areas is simply because to
many of the driver specific elements are missing, and saying that we
just need to add them for each driver still seems wrong to me. Currently
the best way to handle a specific database is it's own generic driver,
and if using PDO, one normally ends up loading the relevant generic one
when the holes start to appear? The database engines are getting more
and more complex, and while PDO sort of provides a flat playing field
for data (one with it's own holes ) it still has some way to go to
provide a flatter interface to database extensions such as 'notice' ?

--
Lester Caine - G8HFL

Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

15 years ago by Samuel ROZE — view source

unread

Le mercredi 07 octobre 2009 à 09:06 +0200, Matteo Beccati a écrit :

From what I could see, things get a bit more complicate on the code side:

PgSQL:

Set a notice processor callback

Clear the notice buffer before a query

Asynchronously buffer notices inside the callback

Set a no-op processor callback

MySQL:

Call mysql_warning_count() after a succesful query

if the result is non-zero, execute "SHOW WARNINGS" and fetch the results

Oracle:

Enable output buffering

Fetch the buffer after a successful query

Clear the buffer

Disable output buffering

Adding generic hooks/functions will also require a PDO API version bump.

This is a bit complicated and very different ! Actually, each database
works as it want and it may be better to use different functions for
each driver.

15 years ago by Pierre Joye — view source

unread

Le mercredi 07 octobre 2009 à 09:06 +0200, Matteo Beccati a écrit :

From what I could see, things get a bit more complicate on the code side:

PgSQL:

Set a notice processor callback

Clear the notice buffer before a query

Asynchronously buffer notices inside the callback

Set a no-op processor callback

MySQL:

Call mysql_warning_count() after a succesful query

if the result is non-zero, execute "SHOW WARNINGS" and fetch the results

Oracle:

Enable output buffering

Fetch the buffer after a successful query

Clear the buffer

Disable output buffering

Adding generic hooks/functions will also require a PDO API version bump.

This is a bit complicated and very different ! Actually, each database
works as it want and it may be better to use different functions for
each driver.

That defeats the whole purpose of PDO. The meaning of each message
will indeed different, but if one needs to call different APIs for
each driver, I doubt anyone will ever use them.

Cheers,

Pierre

http://blog.thepimp.net | http://www.libgd.org

15 years ago by Samuel ROZE — view source

unread

Le mercredi 07 octobre 2009 à 13:30 +0200, Pierre Joye a écrit :
[...]

This is a bit complicated and very different ! Actually, each database
works as it want and it may be better to use different functions for
each driver.

That defeats the whole purpose of PDO. The meaning of each message
will indeed different, but if one needs to call different APIs for
each driver, I doubt anyone will ever use them.

You're right... If developers are motivated to do that, it'll be a
really great thing ! :-)

Cheers,

15 years ago by Matteo Beccati — view source

unread

Pierre Joye ha scritto:

This is a bit complicated and very different ! Actually, each database
works as it want and it may be better to use different functions for
each driver.

That defeats the whole purpose of PDO. The meaning of each message
will indeed different, but if one needs to call different APIs for
each driver, I doubt anyone will ever use them.

Quoting my chat with Rasmus in Verona earlier this year: "PDO is a
database driver abstraction layer, not a database abstraction layer", or
something along the lines, but I see your point ;)

However, I've been thinking about this a bit more and I think that the
different "message" meanings can be summarised like this:

MySQL raises warnings, esp. when not running in strict mode
Oracle uses them as a custom way to deliver messages/data
PostgreSQL being a bit of both worlds: informational + custom

By all means, I like the idea of a unified "message" API, but do we
really need the syntactic sugar (and development effort)? MySQL and
Oracle drivers can already access them with a standard PDO::query()
call. Only PgSQL notices require a special treatment because they are
currently discarded.

I guess this is becoming more philosophical question: is the base PDO
class intended to only have common and portable functionalities and leve
everything else to the drivers or should it also try and group other
similar features to be more consistent even though not likely to be
portable?

Also I guess that mixing and matching names could be confusing: an
experienced Oracle developer using "dbms_output" would hardly imagine
that they can be fetched by calling getMessages() just by taking a quick
look to the documentation. Same goes for a Postgres developer who can
easily understand what pg_last_notice() does and would probably not
associate the familiar notice concept with getMessages.

Sorry for the long post, it's just my 2 cents.

Cheers

Matteo Beccati

15 years ago by Lukas Kahwe Smith — view source

unread

Pierre Joye ha scritto:

On Wed, Oct 7, 2009 at 1:27 PM, Samuel ROZE
samuel.roze@aliceadsl.fr wrote:

This is a bit complicated and very different ! Actually, each
database
works as it want and it may be better to use different functions for
each driver.

That defeats the whole purpose of PDO. The meaning of each message
will indeed different, but if one needs to call different APIs for
each driver, I doubt anyone will ever use them.

Quoting my chat with Rasmus in Verona earlier this year: "PDO is a
database driver abstraction layer, not a database abstraction
layer", or
something along the lines, but I see your point ;)

However, I've been thinking about this a bit more and I think that the
different "message" meanings can be summarised like this:

MySQL raises warnings, esp. when not running in strict mode

Oracle uses them as a custom way to deliver messages/data

PostgreSQL being a bit of both worlds: informational + custom

By all means, I like the idea of a unified "message" API, but do we
really need the syntactic sugar (and development effort)? MySQL and
Oracle drivers can already access them with a standard PDO::query()
call. Only PgSQL notices require a special treatment because they are
currently discarded.

I guess this is becoming more philosophical question: is the base PDO
class intended to only have common and portable functionalities and
leve
everything else to the drivers or should it also try and group other
similar features to be more consistent even though not likely to be
portable?

Also I guess that mixing and matching names could be confusing: an
experienced Oracle developer using "dbms_output" would hardly imagine
that they can be fetched by calling getMessages() just by taking a
quick
look to the documentation. Same goes for a Postgres developer who can
easily understand what pg_last_notice() does and would probably not
associate the familiar notice concept with getMessages.

yeah .. its certainly a valid question.
we have stuff like lastInsertId(), which depending on the driver
either gets the current value of a sequence or the last id generated
for the connection.
so going by that example unifying things under a common API makes
sense if that means that it makes writing portable code easier.

so if pgsql and mysql both give warnings that "paranoid" developers
might want to interpret as an error whatever, then it seems to make
sense to unify this under a common API .. so the key test is not to
unify messaging API's but more unify things that are semantically
similar.

regards,
Lukas Kahwe Smith
mls@pooteeweet.org

15 years ago by johannes@schlueters.de — view source

unread

Hi,

yeah .. its certainly a valid question.
we have stuff like lastInsertId(), which depending on the driver
either gets the current value of a sequence or the last id generated
for the connection.
so going by that example unifying things under a common API makes
sense if that means that it makes writing portable code easier.

For that we shouldn't do just random small changes but take a look at
the whole picture (PDO2?)

Most of the things need new hooks or change PDO APIs in a breaking way
so they can't be done in 5.3, so maybe the time till 6.0 can be used to
really identify and collect the issues and then either refactor or
recreate it depending on the results.

There was a group once working on these things which ended in
political/legal/... discussions maybe the effort can be revived now, two
years later.

What I see is that many people would like having a proper abstraction
layer (what ever is to be abstracted) and PDO has too many rough edges.

johannes

15 years ago by Pierre Joye — view source

unread

hi,

By all means, I like the idea of a unified "message" API, but do we
really need the syntactic sugar (and development effort)? MySQL and
Oracle drivers can already access them with a standard PDO::query()
call. Only PgSQL notices require a special treatment because they are
currently discarded.

I guess this is becoming more philosophical question: is the base PDO
class intended to only have common and portable functionalities

Yes, and I consider this request as something we could have in a portable way.

Also I guess that mixing and matching names could be confusing: an
experienced Oracle developer using "dbms_output" would hardly imagine
that they can be fetched by calling getMessages() just by taking a quick
look to the documentation. Same goes for a Postgres developer who can
easily understand what pg_last_notice() does and would probably not
associate the familiar notice concept with getMessages.

That's the problem when dealing with many databases. Messages can be
confusing, notices could be better. In the end it is a documentation
matter and if users do not read the manual (experienced or not) there
will be no good name anyway ... :).

--
Pierre

http://blog.thepimp.net | http://www.libgd.org

15 years ago by Samuel ROZE — view source

unread

Actually, PDO want to abstract the communication between the Database
and PHP. In "the communication" there are the connection and the
querying of tables which raise errors and notices (for many Databases
drivers). So, in my mind, it will be a great job to standardise
recuperation of theses...although the method to get them is different,
that is "the communication" !

Samuel.

Le mercredi 07 octobre 2009 à 15:49 +0200, Matteo Beccati a écrit :

Pierre Joye ha scritto:

This is a bit complicated and very different ! Actually, each database
works as it want and it may be better to use different functions for
each driver.

That defeats the whole purpose of PDO. The meaning of each message
will indeed different, but if one needs to call different APIs for
each driver, I doubt anyone will ever use them.

Quoting my chat with Rasmus in Verona earlier this year: "PDO is a
database driver abstraction layer, not a database abstraction layer", or
something along the lines, but I see your point ;)

However, I've been thinking about this a bit more and I think that the
different "message" meanings can be summarised like this:

MySQL raises warnings, esp. when not running in strict mode

Oracle uses them as a custom way to deliver messages/data

PostgreSQL being a bit of both worlds: informational + custom

By all means, I like the idea of a unified "message" API, but do we
really need the syntactic sugar (and development effort)? MySQL and
Oracle drivers can already access them with a standard PDO::query()
call. Only PgSQL notices require a special treatment because they are
currently discarded.

I guess this is becoming more philosophical question: is the base PDO
class intended to only have common and portable functionalities and leve
everything else to the drivers or should it also try and group other
similar features to be more consistent even though not likely to be
portable?

Also I guess that mixing and matching names could be confusing: an
experienced Oracle developer using "dbms_output" would hardly imagine
that they can be fetched by calling getMessages() just by taking a quick
look to the documentation. Same goes for a Postgres developer who can
easily understand what pg_last_notice() does and would probably not
associate the familiar notice concept with getMessages.

Sorry for the long post, it's just my 2 cents.

Cheers

15 years ago by Samuel ROZE — view source

unread

Ok, it is possible with Oracle.. Do you think that if it is for only
both of PDO's supported databases is a reason to make that generaly ?
For other drivers it must just burdening PDO. It's what I think...

Le mardi 06 octobre 2009 à 23:08 -0700, Christopher Jones a écrit :

Samuel ROZE wrote:

Notices are informations returned with your own function which was
writed for. For instance, a function (trigger or simple) which have to
insert, delete and update lines can use the RAISE NOTICE (in PostgreSQL)
to send notices to the client.

It can be "2 deleted lines, 0 updated lines and 11 inserted lines", what
you want ! For me, it is important for stats.

Others Databases don't support that (RAISE NOTICE) but I think there's
an equivalent in Oracle but I don't know it.

There's a way to "print" message from Oracle. See dbms_output in
http://www.oracle.com/technology/tech/php/underground-php-oracle-manual.html
These can be retrieved with existing OCI8 or PDO_OCI calls to stored
procedures using bind variables.

What we want to avoid is someone trying to port apps to different
databases and having to rewrite more code than needed. Easier said
than done :)

Chris

Samuel.

Le mardi 06 octobre 2009 à 22:47 -0700, Christopher Jones a écrit :

What are notices? Do they relate anything in other Databases?
I.e. can a generic interface be implemented instead of a postgres
specific one?

Chris

Samuel ROZE wrote:

Thanks all lot for your work ! It is be what I want !

I'll view it this evening and I probably will transform it for PHP 5.2
(which is my version) and put it here.

At tonight ! :-)
Samuel.

Le mercredi 07 octobre 2009 à 00:31 +0200, Matteo Beccati a écrit :

Hi,

Please allow some time for me to check how they are currently dealt with
in the pgsql extension and to see how they can be implemented in the
PDO driver.
Actually, the PosgreSQL driver for PDO don't report
notices.. The content of "_pdo_pgsql_notice" is commented !

Thanks a lot for the time you passed/you'll pass !
Here's a proof of concept patch you can apply to the PHP_5_3 branch:

http://www.beccati.com/misc/php/pdo_pgsql_notices_php5_3.patch

I still need to clean it up a bit and discuss it with the team, but
here's how it currently works:

$db = new PDO(...);
$db->setAttribute(PDO::PGSQL_ATTR_ENABLE_NOTICES, true);

$db->exec($query); // works with $db->query() too
$aNotices = $db->pgsqlGetNotices();

And you will get an array of the notices raised by the last query.

To sum up:

Notices are disabled by default, minimising the overhead

Unlike the pgsql extension which returns the most recent one, multiple
notices can be fetched

A new query will reset the stored notices

Available on the base class because exec() doesn't return a PDOStatement

Hope this helps

Cheers

Matteo Beccati

PDO PgSQL: _pdo_pgsql_notice

dbh->is_persistent); }

"einfo->errcode is false : %s , %s", einfo->errcode, einfo->errmsg); }

Cheers

Cheers

Cheers

Cheers

Cheers

test=# CREATE FUNCTION foo() RETURNS void AS $$DECLARE i int; BEGIN FOR i IN 1..3 LOOP RAISE NOTICE 'i = %', i; END LOOP; END;$$ LANGUAGE plpgsql; CREATE FUNCTION test=# SELECT foo(); NOTICE: i = 1 NOTICE: i = 2 NOTICE: i = 3 foo

Cheers

Cheers

Cheers

Cheers,

Cheers

-- Lester Caine - G8HFL

Cheers,

Cheers

Cheers

dbh->is_persistent);
}

"einfo->errcode is false : %s , %s", einfo->errcode, einfo->errmsg);
}

test=# CREATE FUNCTION foo() RETURNS void AS $$DECLARE i int; BEGIN FOR
i IN 1..3 LOOP RAISE NOTICE 'i = %', i; END LOOP; END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
test=# SELECT foo();
NOTICE: i = 1
NOTICE: i = 2
NOTICE: i = 3
foo

--
Lester Caine - G8HFL