Not exactly a dbi question

Hi all,

I hope someone here can help me.

The DBD::mysql docs seem to say that mysql_affected_rows
is still a viable attribute. I have:

$sql = "update user
set type = ?,
last_name = ?,
state_id = ?,
org_id = ?,
email = ?
where id = ?";
$sth = $dbh->prepare($sql);
$sth->execute($user_type, $last_name, $state_id, $org_id,
$email, $user_id) || die
"Cannot update: " . $sth->errstr();
$result = $sth->{'mysql_affected_rows'};

but $result is always undefined though execute() is successful.
I _think_ $result is supposed to be the number of rows affected.
What am I doing wrong and/or is there a better way?

Thanks,
Jon

RE: Not exactly a dbi question

> -----Original Message-----
> From: Jonathan Mangin [mailto:jon.mangin@comcast.net]=20
> Sent: Tuesday, April 04, 2006 10:37 AM
> To: dbi-users@perl.org
> Subject: Not exactly a dbi question
>=20
> Hi all,
>=20
> I hope someone here can help me.
>=20
> The DBD::mysql docs seem to say that mysql_affected_rows is still a
> viable attribute. I have:
>=20
> $sql =3D "update user
> set type =3D ?,
> last_name =3D ?,
> state_id =3D ?,
> org_id =3D ?,
> email =3D ?
> where id =3D ?";
> $sth =3D $dbh->prepare($sql);
> $sth->execute($user_type, $last_name, $state_id, $org_id,
> $email, $user_id) || die
> "Cannot update: " . $sth->errstr();
> $result =3D $sth->{'mysql_affected_rows'};
>=20
> but $result is always undefined though execute() is successful. I
> _think_ $result is supposed to be the number of rows affected. What am
> I doing wrong and/or is there a better way?

The execute() method returns the number of rows affected for DML
(insert/update/delete) statements, e.g.

my $rows =3D $sth->execute($sql) || die "can't update: " .
$sth->errstr;
print "Updated $rows rows.\n";

The "|| die" stuff still works even when 0 rows are affected because in
that case, it returns "0 but true".

Philip

RE: Not exactly a dbi question

> -----Original Message-----
> From: Garrett, Philip (MAN-Corporate)
[mailto:Philip.Garrett@manheim.com]=20
> Sent: Tuesday, April 04, 2006 10:52 AM
> To: dbi-users@perl.org
> Subject: RE: Not exactly a dbi question
>=20
> > -----Original Message-----
> > From: Jonathan Mangin [mailto:jon.mangin@comcast.net]
> > Sent: Tuesday, April 04, 2006 10:37 AM
> > To: dbi-users@perl.org
> > Subject: Not exactly a dbi question
> >=20
[snip]
> >=20
> > but $result is always undefined though execute() is successful. I=20
> > _think_ $result is supposed to be the number of rows affected. What
am=20
> > I doing wrong and/or is there a better way?
>=20
> The execute() method returns the number of rows affected for DML
> (insert/update/delete) statements, e.g.
>=20
> my $rows =3D $sth->execute($sql)
> || die "can't update: " . $sth->errstr;
> print "Updated $rows rows.\n";

Err, that should be $sth->execute().

Philip

RE: Not exactly a dbi question

On Tue, 4 Apr 2006, Garrett, Philip (MAN-Corporate) wrote:
> > -----Original Message-----
> > From: Jonathan Mangin [mailto:jon.mangin@comcast.net]
> >
> > Hi all,
> >
> > I hope someone here can help me.
> >
> The execute() method returns the number of rows affected for DML
> (insert/update/delete) statements, e.g.
>
> my $rows = $sth->execute($sql) || die "can't update: " .
> $sth->errstr;
> print "Updated $rows rows.\n";
>
> The "|| die" stuff still works even when 0 rows are affected because in
> that case, it returns "0 but true".

Incidentally this means that if you are checking the value of
rows affected to determine what to do next, i.e.

if ($rows) {
it_was_there();
}

Then you have to add 0 to the value of rows to "cast" it back to a number.
("zero but true" is usually represented as the string "0E0", which is
hackish and non-obvious Perl magic.)

I usually do it all in an eval instead:

use English '-no_match_vars'; # i.e. $@ === $EVAL_ERROR
use Carp qw( confess );

my $rows = eval { 0 + $sth->execute($sql) };

confess($EVAL_ERROR) if $EVAL_ERROR; # or throw an Exception::Class

if ($rows) {
it_was_there();
}

$sth->errstr ends up in $EVAL_ERROR if you have RaiseError set.

Mark

Re: Not exactly a dbi question

The execute() method returns the number of rows affected for DML
(insert/update/delete) statements, e.g.

my $rows = $sth->execute($sql) || die "can't update: " .
$sth->errstr;
print "Updated $rows rows.\n";

The "|| die" stuff still works even when 0 rows are affected because in
that case, it returns "0 but true".

Philip


Thanks, Philip, that works though not as expected.
Running the following snippet multiple times:

my $user_id = 'harvey';
my $user_type = 'PAT';
my $last_name = 'Wallbanger';
my $state_id = '1';
my $org_id = 'IN2';
my $email = 'harvey@comcast.net';

my $sql = "update user
set type = ?,
last_name = ?,
state_id = ?,
org_id = ?,
email = ?
where id = ?";
my $sth = $dbh->prepare($sql);
my $result = $sth->execute($user_type, $last_name, $state_id,
$org_id, $email, $user_id) || die
"Cannot update: " . $sth->errstr();
my $action = defined($result) ? 'MOD' : undef;

print "$result\n";
print "$action\n";

always returns '1' and 'MOD' whether an update actually
occurred or not. How can I define $action only upon an
actual update? Perhaps I need to upgrade?

Thanks,
Jon

RE: Not exactly a dbi question

> -----Original Message-----
> From: Jonathan Mangin [mailto:jon.mangin@comcast.net]=20
> Sent: Tuesday, April 04, 2006 3:14 PM
> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
> Subject: Re: Not exactly a dbi question
>=20
>=20
> The execute() method returns the number of rows affected for DML
> (insert/update/delete) statements, e.g.
>=20
> my $rows =3D $sth->execute($sql) || die "can't update: " .
> $sth->errstr;
> print "Updated $rows rows.\n";
>=20
> The "|| die" stuff still works even when 0 rows are affected because
in that case, it returns "0 but true".
>=20
> Philip
>=20
>=20
> Thanks, Philip, that works though not as expected.
> Running the following snippet multiple times:
>=20
> my $user_id =3D 'harvey';
> my $user_type =3D 'PAT';
> my $last_name =3D 'Wallbanger';
> my $state_id =3D '1';
> my $org_id =3D 'IN2';
> my $email =3D 'harvey@comcast.net';
>=20
> my $sql =3D "update user
> set type =3D ?,
> last_name =3D ?,
> state_id =3D ?,
> org_id =3D ?,
> email =3D ?
> where id =3D ?";
> my $sth =3D $dbh->prepare($sql);
> my $result =3D $sth->execute($user_type, $last_name, $state_id,
> $org_id, $email, $user_id) || die
> "Cannot update: " . $sth->errstr(); my
$action =3D defined($result) ? 'MOD' : undef;
>=20
> print "$result\n";
> print "$action\n";
>=20
> always returns '1' and 'MOD' whether an update actually occurred or
> not. How can I define $action only upon an actual update? Perhaps I
> need to upgrade?

No upgrade necessary. The $result will *always* be defined unless there
was an
error. Otherwise, it contains a numeric value for the number of rows
affected
(which also happens to always evaluate to true in boolean context).

Try this:

my $rows =3D $sth->execute(...) || die $sth->errstr;
if ($rows == 0) {
print "No rows updated.\n";
}
else {
printf "Updated $rows rows.\n";
}

Philip

Re: Not exactly a dbi question

----- Original Message -----
From: "Garrett, Philip (MAN-Corporate)"
To:
Sent: Tuesday, April 04, 2006 3:23 PM
Subject: RE: Not exactly a dbi question


> -----Original Message-----
> From: Jonathan Mangin [mailto:jon.mangin@comcast.net]
> Sent: Tuesday, April 04, 2006 3:14 PM
> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
> Subject: Re: Not exactly a dbi question
>
>
> The execute() method returns the number of rows affected for DML
> (insert/update/delete) statements, e.g.
>
> my $rows = $sth->execute($sql) || die "can't update: " .
> $sth->errstr;
> print "Updated $rows rows.\n";
>
> The "|| die" stuff still works even when 0 rows are affected because
in that case, it returns "0 but true".
>
> Philip
>
>
> Thanks, Philip, that works though not as expected.
> Running the following snippet multiple times:
>
> my $user_id = 'harvey';
> my $user_type = 'PAT';
> my $last_name = 'Wallbanger';
> my $state_id = '1';
> my $org_id = 'IN2';
> my $email = 'harvey@comcast.net';
>
> my $sql = "update user
> set type = ?,
> last_name = ?,
> state_id = ?,
> org_id = ?,
> email = ?
> where id = ?";
> my $sth = $dbh->prepare($sql);
> my $result = $sth->execute($user_type, $last_name, $state_id,
> $org_id, $email, $user_id) || die
> "Cannot update: " . $sth->errstr(); my
$action = defined($result) ? 'MOD' : undef;
>
> print "$result\n";
> print "$action\n";
>
> always returns '1' and 'MOD' whether an update actually occurred or
> not. How can I define $action only upon an actual update? Perhaps I
> need to upgrade?

No upgrade necessary. The $result will *always* be defined unless there
was an
error. Otherwise, it contains a numeric value for the number of rows
affected
(which also happens to always evaluate to true in boolean context).

Try this:

my $rows = $sth->execute(...) || die $sth->errstr;
if ($rows == 0) {
print "No rows updated.\n";
}
else {
printf "Updated $rows rows.\n";
}

Philip


OK. Tried that. Always says:

Updated 1 row.

The data never changed. The record was not truly updated.
The timestamp verifies this. Now what?

--Jon

RE: Not exactly a dbi question

>=20
> > > -----Original Message-----
> > > From: Jonathan Mangin [mailto:jon.mangin@comcast.net]=20
> > > Sent: Tuesday, April 04, 2006 3:14 PM
> > > To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
> > > Subject: Re: Not exactly a dbi question
> > >=20
> > >=20
> > > The execute() method returns the number of rows affected for DML
> > > (insert/update/delete) statements, e.g.
> > >=20
> > > my $rows =3D $sth->execute($sql) || die "can't update: " .
> > > $sth->errstr;
> > > print "Updated $rows rows.\n";
> > >=20
> > > The "|| die" stuff still works even when 0 rows are affected
because
> > in that case, it returns "0 but true".
> > >=20
> > > Philip
> > >=20
> > >=20
> > > Thanks, Philip, that works though not as expected.
> > > Running the following snippet multiple times:
> > >=20
> > > my $user_id =3D 'harvey';
> > > my $user_type =3D 'PAT';
> > > my $last_name =3D 'Wallbanger';
> > > my $state_id =3D '1';
> > > my $org_id =3D 'IN2';
> > > my $email =3D 'harvey@comcast.net';
> > >=20
> > > my $sql =3D "update user
> > > set type =3D ?,
> > > last_name =3D ?,
> > > state_id =3D ?,
> > > org_id =3D ?,
> > > email =3D ?
> > > where id =3D ?";
> > > my $sth =3D $dbh->prepare($sql);
> > > my $result =3D $sth->execute($user_type, $last_name, $state_id,
> > > $org_id, $email, $user_id) || die
> > > "Cannot update: " . $sth->errstr(); my
> > $action =3D defined($result) ? 'MOD' : undef;
> > >=20
> > > print "$result\n";
> > > print "$action\n";
> > >=20
> > > always returns '1' and 'MOD' whether an update actually occurred
or
> > > not. How can I define $action only upon an actual update? Perhaps
I
> > > need to upgrade?
> >=20
> > No upgrade necessary. The $result will *always* be defined unless
there
> > was an
> > error. Otherwise, it contains a numeric value for the number of
rows
> > affected
> > (which also happens to always evaluate to true in boolean context).
> >=20
> > Try this:
> >=20
> > my $rows =3D $sth->execute(...) || die $sth->errstr;
> > if ($rows == 0) {
> > print "No rows updated.\n";
> > }
> > else {
> > printf "Updated $rows rows.\n";
> > }
>=20
> OK. Tried that. Always says:
>=20
> Updated 1 row.
>=20
> The data never changed. The record was not truly updated.
> The timestamp verifies this. Now what?

Did you commit()?

Philip

Re: Not exactly a dbi question

----- Original Message -----
From: "Garrett, Philip (MAN-Corporate)"
To:
Sent: Tuesday, April 04, 2006 3:52 PM
Subject: RE: Not exactly a dbi question


>
> > > -----Original Message-----
> > > From: Jonathan Mangin [mailto:jon.mangin@comcast.net]
> > > Sent: Tuesday, April 04, 2006 3:14 PM
> > > To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
> > > Subject: Re: Not exactly a dbi question
> > >
> > >
> > > The execute() method returns the number of rows affected for DML
> > > (insert/update/delete) statements, e.g.
> > >
> > > my $rows = $sth->execute($sql) || die "can't update: " .
> > > $sth->errstr;
> > > print "Updated $rows rows.\n";
> > >
> > > The "|| die" stuff still works even when 0 rows are affected
because
> > in that case, it returns "0 but true".
> > >
> > > Philip
> > >
> > >
> > > Thanks, Philip, that works though not as expected.
> > > Running the following snippet multiple times:
> > >
> > > my $user_id = 'harvey';
> > > my $user_type = 'PAT';
> > > my $last_name = 'Wallbanger';
> > > my $state_id = '1';
> > > my $org_id = 'IN2';
> > > my $email = 'harvey@comcast.net';
> > >
> > > my $sql = "update user
> > > set type = ?,
> > > last_name = ?,
> > > state_id = ?,
> > > org_id = ?,
> > > email = ?
> > > where id = ?";
> > > my $sth = $dbh->prepare($sql);
> > > my $result = $sth->execute($user_type, $last_name, $state_id,
> > > $org_id, $email, $user_id) || die
> > > "Cannot update: " . $sth->errstr(); my
> > $action = defined($result) ? 'MOD' : undef;
> > >
> > > print "$result\n";
> > > print "$action\n";
> > >
> > > always returns '1' and 'MOD' whether an update actually occurred
or
> > > not. How can I define $action only upon an actual update? Perhaps
I
> > > need to upgrade?
> >
> > No upgrade necessary. The $result will *always* be defined unless
there
> > was an
> > error. Otherwise, it contains a numeric value for the number of
rows
> > affected
> > (which also happens to always evaluate to true in boolean context).
> >
> > Try this:
> >
> > my $rows = $sth->execute(...) || die $sth->errstr;
> > if ($rows == 0) {
> > print "No rows updated.\n";
> > }
> > else {
> > printf "Updated $rows rows.\n";
> > }
>
> OK. Tried that. Always says:
>
> Updated 1 row.
>
> The data never changed. The record was not truly updated.
> The timestamp verifies this. Now what?

Did you commit()?

Philip


Autocommit is on. Perhaps you're misunderstanding.
An update is not supposed to happen if the column to be
updated is equal to the data being stuffed into it.
I want $row to reflect that but $row is always 1
even when an update did not happen. Is it broke?

--Jon

Re: Not exactly a dbi question

On 4/4/06 15:06, "Jonathan Mangin" wrote:

> Autocommit is on. Perhaps you're misunderstanding.
> An update is not supposed to happen if the column to be
> updated is equal to the data being stuffed into it.
> I want $row to reflect that but $row is always 1
> even when an update did not happen. Is it broke?
>
> --Jon

No, the default changed:

2003-06-22 Rudy Lippan (2.9002)
* moved pod into mysql.pm from mysql.pod
* Changed the default behaviour of mysql_found_rows, so now
'UPDATE table set field=?' will return the number of rows matched
and not the number of rows physically changed. You can get the old
behaviour back by adding "mysql_found_rows=0" to the dsn passed
to connect.

RE: Not exactly a dbi question

> -----Original Message-----
> From: Paul DuBois [mailto:paul@snake.net]=20
> Sent: Tuesday, April 04, 2006 4:20 PM
> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
dbi-users@perl.org
> Subject: Re: Not exactly a dbi question
>=20
> On 4/4/06 15:06, "Jonathan Mangin" wrote:
>=20
> > Autocommit is on. Perhaps you're misunderstanding. An update is not
> > supposed to happen if the column to be updated is equal to the data
> > being stuffed into it. I want $row to reflect that but $row is
> > always 1 even when an update did not happen. Is it broke?
> >=20
> > --Jon
>=20
> No, the default changed:
>=20
> 2003-06-22 Rudy Lippan (2.9002)
> * moved pod into mysql.pm from mysql.pod
> * Changed the default behaviour of mysql_found_rows, so now
> 'UPDATE table set field=3D?' will return the number of rows
matched
> and not the number of rows physically changed. You can get the
old
> behaviour back by adding "mysql_found_rows=3D0" to the dsn =
passed
> to connect.

Sorry, I've been answering your question while assuming the mysql driver
conformed to the documented DBI interface.

Perhaps this is a mysql thing, not a DBD::mysql thing?

Oh well.

Philip

Re: Not exactly a dbi question

Ah. Not broke, but poorly-tuned.
Thanks Paul, and Philip for all your help.

--Jon



----- Original Message -----
From: "Garrett, Philip (MAN-Corporate)"
To:
Sent: Tuesday, April 04, 2006 4:31 PM
Subject: RE: Not exactly a dbi question


> -----Original Message-----
> From: Paul DuBois [mailto:paul@snake.net]
> Sent: Tuesday, April 04, 2006 4:20 PM
> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
dbi-users@perl.org
> Subject: Re: Not exactly a dbi question
>
> On 4/4/06 15:06, "Jonathan Mangin" wrote:
>
> > Autocommit is on. Perhaps you're misunderstanding. An update is not
> > supposed to happen if the column to be updated is equal to the data
> > being stuffed into it. I want $row to reflect that but $row is
> > always 1 even when an update did not happen. Is it broke?
> >
> > --Jon
>
> No, the default changed:
>
> 2003-06-22 Rudy Lippan (2.9002)
> * moved pod into mysql.pm from mysql.pod
> * Changed the default behaviour of mysql_found_rows, so now
> 'UPDATE table set field=?' will return the number of rows
matched
> and not the number of rows physically changed. You can get the
old
> behaviour back by adding "mysql_found_rows=0" to the dsn passed
> to connect.

Sorry, I've been answering your question while assuming the mysql driver
conformed to the documented DBI interface.

Perhaps this is a mysql thing, not a DBD::mysql thing?

Oh well.

Philip

Re: Not exactly a dbi question

On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)"
wrote:

>> -----Original Message-----
>> From: Paul DuBois [mailto:paul@snake.net]
>> Sent: Tuesday, April 04, 2006 4:20 PM
>> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
> dbi-users@perl.org
>> Subject: Re: Not exactly a dbi question
>>
>> On 4/4/06 15:06, "Jonathan Mangin" wrote:
>>
>>> Autocommit is on. Perhaps you're misunderstanding. An update is not
>>> supposed to happen if the column to be updated is equal to the data
>>> being stuffed into it. I want $row to reflect that but $row is
>>> always 1 even when an update did not happen. Is it broke?
>>>
>>> --Jon
>>
>> No, the default changed:
>>
>> 2003-06-22 Rudy Lippan (2.9002)
>> * moved pod into mysql.pm from mysql.pod
>> * Changed the default behaviour of mysql_found_rows, so now
>> 'UPDATE table set field=?' will return the number of rows
> matched
>> and not the number of rows physically changed. You can get the
> old
>> behaviour back by adding "mysql_found_rows=0" to the dsn passed
>> to connect.
>
> Sorry, I've been answering your question while assuming the mysql driver
> conformed to the documented DBI interface.
>
> Perhaps this is a mysql thing, not a DBD::mysql thing?
>
> Oh well.
>
> Philip

Perhaps. What is the non-conformity to the documented DBI interface to which
you refer?

Re: Not exactly a dbi question

----- Original Message -----
From: "Paul DuBois"
To: "Garrett, Philip (MAN-Corporate)" ;

Sent: Wednesday, April 05, 2006 2:55 AM
Subject: Re: Not exactly a dbi question


> On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)"
> wrote:
>
> >> -----Original Message-----
> >> From: Paul DuBois [mailto:paul@snake.net]
> >> Sent: Tuesday, April 04, 2006 4:20 PM
> >> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
> > dbi-users@perl.org
> >> Subject: Re: Not exactly a dbi question
> >>
> >> On 4/4/06 15:06, "Jonathan Mangin" wrote:
> >>
> >>> Autocommit is on. Perhaps you're misunderstanding. An update is not
> >>> supposed to happen if the column to be updated is equal to the data
> >>> being stuffed into it. I want $row to reflect that but $row is
> >>> always 1 even when an update did not happen. Is it broke?
> >>>
> >>> --Jon
> >>
> >> No, the default changed:
> >>
> >> 2003-06-22 Rudy Lippan (2.9002)
> >> * moved pod into mysql.pm from mysql.pod
> >> * Changed the default behaviour of mysql_found_rows, so now
> >> 'UPDATE table set field=?' will return the number of rows
> > matched
> >> and not the number of rows physically changed. You can get the
> > old
> >> behaviour back by adding "mysql_found_rows=0" to the dsn passed
> >> to connect.
> >
> > Sorry, I've been answering your question while assuming the mysql driver
> > conformed to the documented DBI interface.
> >
> > Perhaps this is a mysql thing, not a DBD::mysql thing?
> >
> > Oh well.
> >
> > Philip
>
> Perhaps. What is the non-conformity to the documented DBI interface to
which
> you refer?
>
>
Adding mysql_client_found_rows=0 to the dsn behaves as you describe
(though it returns ~ 0E0 [thanks, Mark]).
mysql_found_rows=0 changes nothing. So which is the real name of
this attribute?
--Jon

Re: Not exactly a dbi question

On 4/5/06 7:35, "Jonathan Mangin" wrote:

>
> ----- Original Message -----
> From: "Paul DuBois"
> To: "Garrett, Philip (MAN-Corporate)" ;
>
> Sent: Wednesday, April 05, 2006 2:55 AM
> Subject: Re: Not exactly a dbi question
>
>
>> On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)"
>> wrote:
>>
>>>> -----Original Message-----
>>>> From: Paul DuBois [mailto:paul@snake.net]
>>>> Sent: Tuesday, April 04, 2006 4:20 PM
>>>> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
>>> dbi-users@perl.org
>>>> Subject: Re: Not exactly a dbi question
>>>>
>>>> On 4/4/06 15:06, "Jonathan Mangin" wrote:
>>>>
>>>>> Autocommit is on. Perhaps you're misunderstanding. An update is not
>>>>> supposed to happen if the column to be updated is equal to the data
>>>>> being stuffed into it. I want $row to reflect that but $row is
>>>>> always 1 even when an update did not happen. Is it broke?
>>>>>
>>>>> --Jon
>>>>
>>>> No, the default changed:
>>>>
>>>> 2003-06-22 Rudy Lippan (2.9002)
>>>> * moved pod into mysql.pm from mysql.pod
>>>> * Changed the default behaviour of mysql_found_rows, so now
>>>> 'UPDATE table set field=?' will return the number of rows
>>> matched
>>>> and not the number of rows physically changed. You can get the
>>> old
>>>> behaviour back by adding "mysql_found_rows=0" to the dsn passed
>>>> to connect.
>>>
>>> Sorry, I've been answering your question while assuming the mysql driver
>>> conformed to the documented DBI interface.
>>>
>>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>>>
>>> Oh well.
>>>
>>> Philip
>>
>> Perhaps. What is the non-conformity to the documented DBI interface to
> which
>> you refer?
>>
>>
> Adding mysql_client_found_rows=0 to the dsn behaves as you describe
> (though it returns ~ 0E0 [thanks, Mark]).
> mysql_found_rows=0 changes nothing. So which is the real name of
> this attribute?
> --Jon

The name is mysql_client_found_rows. Looks like the change note has a typo
(twice). Sorry, I didn't even notice this, or I would have pointed it out.

RE: Not exactly a dbi question

>-----Original Message-----
>From: Paul DuBois [mailto:paul@snake.net]=20
>Sent: Wednesday, April 05, 2006 2:55 AM
>To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>Subject: Re: Not exactly a dbi question
>
>On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)"
> wrote:
>
>>> -----Original Message-----
>>> From: Paul DuBois [mailto:paul@snake.net]
>>> Sent: Tuesday, April 04, 2006 4:20 PM
>>> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
>> dbi-users@perl.org
>>> Subject: Re: Not exactly a dbi question
>>>=20
>>> On 4/4/06 15:06, "Jonathan Mangin" wrote:
>>>=20
>>>> Autocommit is on. Perhaps you're misunderstanding. An update is not

>>>> supposed to happen if the column to be updated is equal to the data

>>>> being stuffed into it. I want $row to reflect that but $row is=20
>>>> always 1 even when an update did not happen. Is it broke?
>>>>=20
>>>> --Jon
>>>=20
>>> No, the default changed:
>>>=20
>>> 2003-06-22 Rudy Lippan (2.9002)
>>> * moved pod into mysql.pm from mysql.pod
>>> * Changed the default behaviour of mysql_found_rows, so now
>>> 'UPDATE table set field=3D?' will return the number of rows
>>> matched
>>> and not the number of rows physically changed. You can get the
>>> old
>>> behaviour back by adding "mysql_found_rows=3D0" to the dsn
passed
>>> to connect.
>>=20
>> Sorry, I've been answering your question while assuming the mysql
>> driver conformed to the documented DBI interface.
>>=20
>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>
> Perhaps. What is the non-conformity to the documented DBI interface to
> which you refer?

The DBI pod says:

For a non-SELECT statement, execute returns the number of rows
affected, if known. If no rows were affected, then execute returns
"0E0", which Perl will treat as 0 but will regard as true. Note that
it is not an error for no rows to be affected by a statement. If the
number of rows affected is not known, then execute returns -1.

The OP said that execute() was returning 1, whether the row was affected
or
not.

Philip

Re: Not exactly a dbi question

On 4/5/06 9:26, "Garrett, Philip (MAN-Corporate)"
wrote:

>> -----Original Message-----
>> From: Paul DuBois [mailto:paul@snake.net]
>> Sent: Wednesday, April 05, 2006 2:55 AM
>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>> Subject: Re: Not exactly a dbi question
>>
>> On 4/4/06 15:31, "Garrett, Philip (MAN-Corporate)"
>> wrote:
>>
>>>> -----Original Message-----
>>>> From: Paul DuBois [mailto:paul@snake.net]
>>>> Sent: Tuesday, April 04, 2006 4:20 PM
>>>> To: Jonathan Mangin; Garrett, Philip (MAN-Corporate);
>>> dbi-users@perl.org
>>>> Subject: Re: Not exactly a dbi question
>>>>
>>>> On 4/4/06 15:06, "Jonathan Mangin" wrote:
>>>>
>>>>> Autocommit is on. Perhaps you're misunderstanding. An update is not
>
>>>>> supposed to happen if the column to be updated is equal to the data
>
>>>>> being stuffed into it. I want $row to reflect that but $row is
>>>>> always 1 even when an update did not happen. Is it broke?
>>>>>
>>>>> --Jon
>>>>
>>>> No, the default changed:
>>>>
>>>> 2003-06-22 Rudy Lippan (2.9002)
>>>> * moved pod into mysql.pm from mysql.pod
>>>> * Changed the default behaviour of mysql_found_rows, so now
>>>> 'UPDATE table set field=?' will return the number of rows
>>>> matched
>>>> and not the number of rows physically changed. You can get the
>>>> old
>>>> behaviour back by adding "mysql_found_rows=0" to the dsn
> passed
>>>> to connect.
>>>
>>> Sorry, I've been answering your question while assuming the mysql
>>> driver conformed to the documented DBI interface.
>>>
>>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>>
>> Perhaps. What is the non-conformity to the documented DBI interface to
>> which you refer?
>
> The DBI pod says:
>
> For a non-SELECT statement, execute returns the number of rows
> affected, if known. If no rows were affected, then execute returns
> "0E0", which Perl will treat as 0 but will regard as true. Note that
> it is not an error for no rows to be affected by a statement. If the
> number of rows affected is not known, then execute returns -1.
>
> The OP said that execute() was returning 1, whether the row was affected
> or
> not.

I don't think there is an error here unless the meaning of "affected"
becomes defined more precisely. For non-SELECT statements, "rows affected"
can mean either "rows matched" (regardless of whether actually changed) or
"rows changed". The default for MySQL is the rows-changed value, and that
was also the default for DBD::mysql until the 2.9002 change. The default
for DBD::mysql now is the rows-matched value.


Does the DBI spec require some particular interpretation of "affected"?
(The JDBC spec requires the rows-matched value.)

RE: Not exactly a dbi question

=20

>-----Original Message-----
>From: Paul DuBois [mailto:paul@snake.net]=20
>Sent: Wednesday, April 05, 2006 10:41 AM
>To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>Subject: Re: Not exactly a dbi question
>[snip]
>>>>
>>>> Sorry, I've been answering your question while assuming the mysql=20
>>>> driver conformed to the documented DBI interface.
>>>>=20
>>>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>>>=20
>>> Perhaps. What is the non-conformity to the documented DBI interface=20
>>> to which you refer?
>>=20
>> The DBI pod says:
>>=20
>> For a non-SELECT statement, execute returns the number of rows
>> affected, if known. If no rows were affected, then execute
returns
>> "0E0", which Perl will treat as 0 but will regard as true. Note
that
>> it is not an error for no rows to be affected by a statement. If
the
>> number of rows affected is not known, then execute returns -1.
>>=20
>> The OP said that execute() was returning 1, whether the row was=20
>> affected or not.
>=20
> I don't think there is an error here unless the meaning of
> "affected" becomes defined more precisely. For non-SELECT
> statements, "rows affected" can mean either "rows matched"
> (regardless of whether actually changed) or "rows changed". The
> default for MySQL is the rows-changed value, and that was also the
> default for DBD::mysql until the 2.9002 change. The default for
> DBD::mysql now is the rows-matched value.
>
> Does the DBI spec require some particular interpretation of
> "affected"? (The JDBC spec requires the rows-matched value.)

It's making more sense now.

I'm a little confused though -- how does a DML operation have a
different number for rows-matched and rows-changed? Isn't the point of
DML to change all rows matched? I'll go looking at the JDBC docs for an
explanation.

Philip

Re: Not exactly a dbi question

On 4/5/06 9:46, "Garrett, Philip (MAN-Corporate)"
wrote:

>
>
>> -----Original Message-----
>> From: Paul DuBois [mailto:paul@snake.net]
>> Sent: Wednesday, April 05, 2006 10:41 AM
>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>> Subject: Re: Not exactly a dbi question
>> [snip]
>>>>>
>>>>> Sorry, I've been answering your question while assuming the mysql
>>>>> driver conformed to the documented DBI interface.
>>>>>
>>>>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>>>>
>>>> Perhaps. What is the non-conformity to the documented DBI interface
>>>> to which you refer?
>>>
>>> The DBI pod says:
>>>
>>> For a non-SELECT statement, execute returns the number of rows
>>> affected, if known. If no rows were affected, then execute
> returns
>>> "0E0", which Perl will treat as 0 but will regard as true. Note
> that
>>> it is not an error for no rows to be affected by a statement. If
> the
>>> number of rows affected is not known, then execute returns -1.
>>>
>>> The OP said that execute() was returning 1, whether the row was
>>> affected or not.
>>
>> I don't think there is an error here unless the meaning of
>> "affected" becomes defined more precisely. For non-SELECT
>> statements, "rows affected" can mean either "rows matched"
>> (regardless of whether actually changed) or "rows changed". The
>> default for MySQL is the rows-changed value, and that was also the
>> default for DBD::mysql until the 2.9002 change. The default for
>> DBD::mysql now is the rows-matched value.
>>
>> Does the DBI spec require some particular interpretation of
>> "affected"? (The JDBC spec requires the rows-matched value.)
>
> It's making more sense now.
>
> I'm a little confused though -- how does a DML operation have a
> different number for rows-matched and rows-changed? Isn't the point of
> DML to change all rows matched? I'll go looking at the JDBC docs for an
> explanation.

It's most easily seen for a statement such as this:

UPDATE tbl_name SET col_name = 0 WHERE col_name = 0;

If you've selected the rows-changed count, $sth->rows() will always return
0, because the statement doesn't actually change any col_name value from its
current value.

If you've selected the rows-matched count, $sth->rows() will return the
number of rows for which col_name is 0.

RE: Not exactly a dbi question

=20

>-----Original Message-----
>From: Paul DuBois [mailto:paul@snake.net]=20
>Sent: Wednesday, April 05, 2006 10:56 AM
>To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>Subject: Re: Not exactly a dbi question
>
>On 4/5/06 9:46, "Garrett, Philip (MAN-Corporate)"
> wrote:
>
>> =20
>>=20
>>> -----Original Message-----
>>> From: Paul DuBois [mailto:paul@snake.net]
>>> Sent: Wednesday, April 05, 2006 10:41 AM
>>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>>> Subject: Re: Not exactly a dbi question [snip]
>>>>>>=20
>>>>>> Sorry, I've been answering your question while assuming the mysql

>>>>>> driver conformed to the documented DBI interface.
>>>>>>=20
>>>>>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>>>>>=20
>>>>> Perhaps. What is the non-conformity to the documented DBI
interface=20
>>>>> to which you refer?
>>>>=20
>>>> The DBI pod says:
>>>>=20
>>>> For a non-SELECT statement, execute returns the number of rows
>>>> affected, if known. If no rows were affected, then execute
>> returns
>>>> "0E0", which Perl will treat as 0 but will regard as true. Note
>> that
>>>> it is not an error for no rows to be affected by a statement.
If
>> the
>>>> number of rows affected is not known, then execute returns -1.
>>>>=20
>>>> The OP said that execute() was returning 1, whether the row was=20
>>>> affected or not.
>>>=20
>>> I don't think there is an error here unless the meaning of
"affected"=20
>>> becomes defined more precisely. For non-SELECT statements, "rows=20
>>> affected" can mean either "rows matched"
>>> (regardless of whether actually changed) or "rows changed". The=20
>>> default for MySQL is the rows-changed value, and that was also the=20
>>> default for DBD::mysql until the 2.9002 change. The default for=20
>>> DBD::mysql now is the rows-matched value.
>>>=20
>>> Does the DBI spec require some particular interpretation of=20
>>> "affected"? (The JDBC spec requires the rows-matched value.)
>>=20
>> It's making more sense now.
>>=20
>> I'm a little confused though -- how does a DML operation have a=20
>> different number for rows-matched and rows-changed? Isn't the point
of=20
>> DML to change all rows matched? I'll go looking at the JDBC docs for=20
>> an explanation.
>=20
> It's most easily seen for a statement such as this:
>
> UPDATE tbl_name SET col_name =3D 0 WHERE col_name =3D 0;
>
> If you've selected the rows-changed count, $sth->rows() will always
> return 0, because the statement doesn't actually change any col_name
> value from its current value.
>
> If you've selected the rows-matched count, $sth->rows() will return
> the number of rows for which col_name is 0.

Thanks for the explanation. I'm glad we had this discussion, because I'm
sure that would have bitten me had I been using JDBC or MySQL.

I tend to think that the row is still affected in a logical sense.
Whether the DBMS backend decides to physically write a row that hasn't
actually changed really isn't any of my business.

It gets even more confusing if there's a trigger on the table. In that
case, a trigger could be fired even though the row wasn't physically
updated (with Oracle, anyway).

Philip

Re: Not exactly a dbi question

On 4/5/06 10:04, "Garrett, Philip (MAN-Corporate)"
wrote:

>
>
>> -----Original Message-----
>> From: Paul DuBois [mailto:paul@snake.net]
>> Sent: Wednesday, April 05, 2006 10:56 AM
>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>> Subject: Re: Not exactly a dbi question
>>
>> On 4/5/06 9:46, "Garrett, Philip (MAN-Corporate)"
>> wrote:
>>
>>>
>>>
>>>> -----Original Message-----
>>>> From: Paul DuBois [mailto:paul@snake.net]
>>>> Sent: Wednesday, April 05, 2006 10:41 AM
>>>> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
>>>> Subject: Re: Not exactly a dbi question [snip]
>>>>>>>
>>>>>>> Sorry, I've been answering your question while assuming the mysql
>
>>>>>>> driver conformed to the documented DBI interface.
>>>>>>>
>>>>>>> Perhaps this is a mysql thing, not a DBD::mysql thing?
>>>>>>
>>>>>> Perhaps. What is the non-conformity to the documented DBI
> interface
>>>>>> to which you refer?
>>>>>
>>>>> The DBI pod says:
>>>>>
>>>>> For a non-SELECT statement, execute returns the number of rows
>>>>> affected, if known. If no rows were affected, then execute
>>> returns
>>>>> "0E0", which Perl will treat as 0 but will regard as true. Note
>>> that
>>>>> it is not an error for no rows to be affected by a statement.
> If
>>> the
>>>>> number of rows affected is not known, then execute returns -1.
>>>>>
>>>>> The OP said that execute() was returning 1, whether the row was
>>>>> affected or not.
>>>>
>>>> I don't think there is an error here unless the meaning of
> "affected"
>>>> becomes defined more precisely. For non-SELECT statements, "rows
>>>> affected" can mean either "rows matched"
>>>> (regardless of whether actually changed) or "rows changed". The
>>>> default for MySQL is the rows-changed value, and that was also the
>>>> default for DBD::mysql until the 2.9002 change. The default for
>>>> DBD::mysql now is the rows-matched value.
>>>>
>>>> Does the DBI spec require some particular interpretation of
>>>> "affected"? (The JDBC spec requires the rows-matched value.)
>>>
>>> It's making more sense now.
>>>
>>> I'm a little confused though -- how does a DML operation have a
>>> different number for rows-matched and rows-changed? Isn't the point
> of
>>> DML to change all rows matched? I'll go looking at the JDBC docs for
>>> an explanation.
>>
>> It's most easily seen for a statement such as this:
>>
>> UPDATE tbl_name SET col_name = 0 WHERE col_name = 0;
>>
>> If you've selected the rows-changed count, $sth->rows() will always
>> return 0, because the statement doesn't actually change any col_name
>> value from its current value.
>>
>> If you've selected the rows-matched count, $sth->rows() will return
>> the number of rows for which col_name is 0.
>
> Thanks for the explanation. I'm glad we had this discussion, because I'm
> sure that would have bitten me had I been using JDBC or MySQL.
>

The MySQL Connector/J driver automatically tells the server to return the
rows-matched value due to the JDBC requirement.


> I tend to think that the row is still affected in a logical sense.
> Whether the DBMS backend decides to physically write a row that hasn't
> actually changed really isn't any of my business.
>
> It gets even more confusing if there's a trigger on the table. In that
> case, a trigger could be fired even though the row wasn't physically
> updated (with Oracle, anyway).
>
> Philip
>

Re: Not exactly a dbi question

Paul DuBois schreef:

> UPDATE tbl_name SET col_name = 0 WHERE col_name = 0;
>
> If you've selected the rows-changed count, $sth->rows() will always
> return 0, because the statement doesn't actually change any col_name
> value from its current value.

That would be an odd optimization. The update should take place, even if
the value wouldn't seem to change anything. Hidden fields like "record
last updated", or triggers, could depend on this.

--
Affijn, Ruud

"Gewoon is een tijger."

Re: Not exactly a dbi question

----- Original Message -----
From: "Dr.Ruud"
To:
Sent: Wednesday, April 05, 2006 11:53 AM
Subject: Re: Not exactly a dbi question


> Paul DuBois schreef:
>
> > UPDATE tbl_name SET col_name = 0 WHERE col_name = 0;
> >
> > If you've selected the rows-changed count, $sth->rows() will always
> > return 0, because the statement doesn't actually change any col_name
> > value from its current value.
>
> That would be an odd optimization. The update should take place, even if
> the value wouldn't seem to change anything. Hidden fields like "record
> last updated", or triggers, could depend on this.
>
> --
> Affijn, Ruud
>
> "Gewoon is een tijger."
>

mySql made this change approx. 3 years ago(?), so it's an old
discussion that I missed. Apparently the whole world agrees
with you. I simply didn't want to populate an application's
change_log with an event that didn't happen. Hence my need
for the old behaviour.
--Jon

Re: Not exactly a dbi question

On 4/5/06 14:07, "Jonathan Mangin" wrote:

>
> ----- Original Message -----
> From: "Dr.Ruud"
> To:
> Sent: Wednesday, April 05, 2006 11:53 AM
> Subject: Re: Not exactly a dbi question
>
>
>> Paul DuBois schreef:
>>
>>> UPDATE tbl_name SET col_name = 0 WHERE col_name = 0;
>>>
>>> If you've selected the rows-changed count, $sth->rows() will always
>>> return 0, because the statement doesn't actually change any col_name
>>> value from its current value.
>>
>> That would be an odd optimization. The update should take place, even if
>> the value wouldn't seem to change anything. Hidden fields like "record
>> last updated", or triggers, could depend on this.
>>
>> --
>> Affijn, Ruud
>>
>> "Gewoon is een tijger."
>>
>
> mySql made this change approx. 3 years ago(?), so it's an old
> discussion that I missed. Apparently the whole world agrees
> with you. I simply didn't want to populate an application's
> change_log with an event that didn't happen. Hence my need
> for the old behaviour.
> --Jon

Well, no. The underlying basis for this behavior is whether you pass the
CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API call. This flag
has been around for many years (since 3.21); it's nothing new. That's just
a point of trivia, though. DBD::mysql lets you select either behavior, so
you can have whichever one you want.

Need help to convert text

Dear All,

Actually I'm facing problem when I'm trying to write in perl same function
which is available in VB and Function code is mention below

Public Function EncodeArabic(ByVal arabicString As String) As String
Dim _targetEncoding As System.Text.Encoding
Dim _encodedCharsArray() As Byte
Dim _resultString As String = String.Empty
'1256 is the code page for windows arabic

' 1201 is the code page of Unicode big endian
_targetEncoding = System.Text.Encoding.GetEncoding(1201)
_encodedCharsArray = _targetEncoding.GetBytes(arabicString)
'_resultString = ByteToHexadecimal(_encodedCharsArray)
_resultString = ByteToHexadecimal(_encodedCharsArray)
Return _resultString
End Function
Public Function ByteToHexadecimal(ByVal imageByteArray() As Byte) As
String

Dim hexString As String = String.Empty

Dim i As Integer
For i = 0 To imageByteArray.Length - 1 Step i + 1

hexString += imageByteArray(i).ToString("x2") 'it was X2

Next

Return hexString

End Function

____________________________________________________________ ________________
________

Actually I'm want to write this function to send SMS which supports
ByteToHexadecimal using http request but text is in Arabic. So any one can
help me to solve this problem.

Regards,
Nayeem.