New test for dbd::mysql demonstrating that once an execute fails all other executes on same statement fail
Patch against dbd::mysql 3.0002_4 which demonstrates that if you prepare
a statement and execute multiple times then as soon as one execute fails
all subsequent executes fail. This obviously effects execute_array in
particular as all the tuple_status values from the failed execute
onwards show an error.
I wasn't completely sure how to avoid tests which are supposed to fail
producing an error and failing the test. It appears turning PrintError
off for that test does what is required.
I have as yet, no solution to the problem.
Martin
--- Makefile.PL Thu Mar 30 00:03:18 2006
+++ Makefile.PL.mineThu Mar 30 00:02:46 2006
@@ -305,6 +305,9 @@
't/insertid.t' => { filename =>
'mysqlEmb/t/insertid.t',
makedir => 'mysqlEmb/t'
},
+ 't/pexecute.t' => { filename =>
'mysqlEmb/t/pexecute.t',
+
makedir => 'mysqlEmb/t'
+ },
't/mysql.t' => { filename => 'mysqlEmb/t/mysql.t',
makedir => 'mysqlEmb/t'
},
--- /dev/null Tue Sep 11 15:18:33 2001
+++ t/pexecute.tThu Mar 30 00:01:25 2006
@@ -0,0 +1,50 @@
+# -*- cperl -*-
+
+use strict;
+use DBI ();
+
+use vars qw($test_dsn $test_user $test_password $state);
+require "t/lib.pl";
+
+while (Testing()) {
+ my ($dbh, $sth);
+ #
+ # Connect to the database
+ Test($state or
+ ($dbh = DBI->connect($test_dsn, $test_user, $test_password,
+ {RaiseError => 0})));
+
+ #
+ # Find a possible new table name
+ #
+ my $table = "";
+ Test($state or
+ ($table = FindNewTable($dbh)));
+ #
+ # Create a new table
+ #
+ my $q = <<"QUERY";
+CREATE TABLE $table (id INTEGER PRIMARY KEY NOT NULL,
+ name VARCHAR(64))
+QUERY
+ Test($state or $dbh->do($q));
+
+ #
+ # Insert a row
+ #
+ $q = "INSERT INTO $table (id, name) VALUES (?,?)";
+ Test($state or ($sth = $dbh->prepare($q)));
+ Test($state or $sth->execute(1, "Jocken"));
+ $sth->{PrintError} = 0;
+ Test($state or !($sth->execute(1, "Jochen")));
+ $sth->{PrintError} = 1;
+ Test($state or $sth->execute(2, "Jochen"));
+
+ #
+ # Drop the table
+ Test($state or $dbh->do("DROP TABLE $table"));
+
+ #
+ # Close the database connection
+ Test($state or ($dbh->disconnect() or 1));
+}
Re: Possible bug in execute_array with dbd::mysql
The problem I reported is actually worse than it seems.
When I debug the code previously posted I find execute_array just calls
execute multiple times with the different parameters and in fact the
problem is that once an execute fails all following executes also fail.
This code (not using execute_array) fails:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
#'DBI:ODBC:mjetest', 'xxx', 'yyy',
) or die "DBI::errstr";
my ($sth, $ref, $rowval, @row, $affected, $param, $inserted, @tuple_status);
$dbh->do(q/drop table if exists mytest/);
$dbh->do(q/create table mytest (a int primary key, b char(50))/);
$sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->execute(1, 'one');
$sth->execute(2, 'two');
$sth->execute(3,, 'three');
#$sth->bind_param_array(1, [51,1,52,53]);
#$sth->bind_param_array(2, ['fiftyone', 'one', 'fiftytwo', 'fiftythree']);
#$inserted = $sth->execute_array( { ArrayTupleStatus => [EMAIL PROTECTED] } );
#print join(",", @tuple_status), "\n";
#print Dumper([EMAIL PROTECTED]), "\n";
$sth->execute(99, 'ninetynine');
$sth->execute(1, 'one');
$sth->execute(999, 'ninehundredandninetynine');
with:
bash-2.05$ perl z.pl
DBD::mysql::st execute failed: Duplicate entry '1' for key 1 at z.pl
line 28.
DBD::mysql::st execute failed: Duplicate entry '1' for key 1 at z.pl
line 29.
and yet the db contains:
> select * from mytest;
a:b:
1:one:
2:two:
3:three:
99:ninetynine:
999:ninehundredandninetynine:
showing the last execute actually worked!
As before, if DBD::ODBC and myodbc is used it works correctly so
this looks like an issue in dbd::mysql.
Martin
Martin J. Evans wrote:
Hi,
I think I may have found a bug in DBD::mysql for execute_array.
The following code demonstrates:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
#'DBI:ODBC:mjetest', 'xxx', 'yyy',
) or die "DBI::errstr";
my ($sth, $ref, $rowval, @row, $affected, $param, $inserted, @tuple_status);
$dbh->do(q/drop table if exists mytest/);
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
$sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->execute(1, 'one');
$sth->execute(2, 'two');
$sth->execute(3,, 'three');
$sth->bind_param_array(1, [51,1,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
$inserted = $sth->execute_array( { ArrayTupleStatus => [EMAIL PROTECTED] } );
print join(",", @tuple_status), "\n";
print Dumper([EMAIL PROTECTED]), "\n";
which when run produces:
DBD::mysql::st execute_array failed: Duplicate entry '1' for key 1 at z.pl line
23.
1,ARRAY(0x82b3ea0),ARRAY(0x82b3e94),ARRAY(0x82b3f00)
$VAR1 = [
1,
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
],
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
],
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
]
];
Note, the tuple_status array contains reference for the 2nd, 3rd and 4th
execute although the 3rd and 4th execute work - checked in the table which
shows:
a:b:
1:one:
2:two:
3:three:
51:fiftyone:
52:fiftytwo:
53:fiftythree:
afterwards. If I simply switch to DBD:ODBC and use myodbc it correctly
displays:
1,ARRAY(0x8293db4),1,1
$VAR1 = [
1,
[
-1,
'[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-5.0.15-log]Duplicate
entry \'1\' for key 1 (SQL-23000)(DBD: st_execute/
SQLExecute err=-1)',
'23000'
],
1,
1
];
and the table contents are the same.
I have not yet been able to fix this.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: [dbi] Re: execute not seen on selectrow_* for suclassed DBI
Tim Bunce wrote:
On Wed, Mar 29, 2006 at 09:02:43PM +0100, Martin J. Evans wrote:
Thanks Tim. I'm now hopefully looking in the right places.
Not seeing execute on a selectrow_array threw me initially
but it is an example of the problems I'm seeing.
I'm having a few problems accessing things at the time I want.
e.g. execute_array can be called with the parameters (which I
want to see) and this is ok, but can also be called with
bind_param_array then execute_array but in this case
sth->{Params} does not contain the parameters after the
sth->SUPER::execute_array call (I was suprised by that).
Looking at the code for bind_param_array I see the bound refs are
stored in $sth->{ParamArrays}.
Thanks, you've found me out, should have looked harder myself.
I've got some excuses but I'll not bore the list with them.
This is a problem for me since I want to examine the
tuple_status passed to execute_array and find the
parameters for the execute which failed. I've found another
way around this for now by moving the code which highlights
the parameters which failed into execute.
I'd accept a doc patch to document the ParamArrays attribute.
I'll accept that as my penalty for laziness ;-)
I've tested this in my code and with reference to the other problem
I posted with dbd::mysql Data::Dumper->Dump([$sth->{ParamArrays}],
'ParamArrays']) correctly shows:
$ParamArrays = {'1' => [51,1,52,53],'2' =>
['fiftyone','fiftytwo','fiftythree','one']};
Just the ticket - cheers.
Just to put some context on this I'm suclassing DBI as something
like DBIx::Log4Perl where all the SQL, parameters, fetched data,
transactions, error handler can be logged. I'm doing this because I
have a large Perl server accepting a huge number of jobs per second
which is very transaction heavy. It is impossible to debug since
it is almost impossible to reproduce the condition which caused the
error so I need as much info as possible at the time of the problem
and I need it as light weight as I can make it because slowing it
down sometimes hides the problem.
Sounds interesting.
Tim.
I don't want to make any promises right now since I'm very time poor
at the mo but if I find it works out for us I'll make it available.
Martin
Tim Bunce wrote:
On Wed, Mar 29, 2006 at 04:49:32PM +0100, Martin J. Evans wrote:
Tim,
Thanks, I do keep forgetting about Driver.xst.
So, just to be clear. In DBI.pm, selectrow_array calls _do_selectrow which
calls prepare/execute/fetchsomething but this may be overriden by a driver
using selectrow_array in Driver.xst. If this is the case:
a) is there any way for my subclassed DBI to know this?
Something like this might work:
... if exists &DBD::foo::db::selectrow_array;
Also, take a look at the docs for the DBI's can() method. Though if you
want to rely on that you might want to look into the implementation to
check for edge cases or other subtle issues.
b) is there an easy way to identify which other methods this could apply
to
Nope. You'd have to use the above on all of them.
Tim.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 29-Mar-2006 Tim Bunce wrote:
On Wed, Mar 29, 2006 at 11:54:32AM +0100, Martin J. Evans wrote:
The issue I am seeing is not quite as general as I made it sound. For
selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I
expect)
but
for selectrow_arrayref and selectrow_array I only see prepare.
Generally... Drivers are free to implement any method in any way they
choose.
Their own methods don't have to call execute(), for example.
Specifically... you're probably seeing the effect of the Driver.xst
C code that most compiled drivers embed into themselves. That code
embeds C implementations of selectall_arrayref and several other
methods into the driver itself. The selectall_arrayref code calls the
drivers C functions to do the work. The result is much, much, faster
than going though perl/DBI method dispatch for each row.
Tim.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 29-Mar-2006 Martin J. Evans wrote:
Hi,
I have subclassed DBI and override many methods including prepare,
execute
and
select* although they are predominantly just passed on to DBI.
If I do:
my $sth->prepare(sql);
$sth->execute;
I see the prepare and execute and pass them on to DBI.
If I do:
$dbh->selectrow_array(sql);
I see prepare and pass it on, but do not see execute. In my case this
is
more
than an annoyance as I cannot see any bound parameter passed to
execute.
e.g.
$selectrow_array = [
'select b from mytest where a = ?',
undef,
\1
];
prepare: select b from mytest where a = ?
!!! no execute here
Or, perhaps I'm doing something wrong.
Can anyone tell me if I should be seeing the execute when
$dbh->select***
is
called? I think I should.
Thanks
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.c
Re: [dbi] Re: execute not seen on selectrow_* for suclassed DBI
On Wed, Mar 29, 2006 at 09:02:43PM +0100, Martin J. Evans wrote:
> Thanks Tim. I'm now hopefully looking in the right places.
>
> Not seeing execute on a selectrow_array threw me initially
> but it is an example of the problems I'm seeing.
>
> I'm having a few problems accessing things at the time I want.
>
> e.g. execute_array can be called with the parameters (which I
> want to see) and this is ok, but can also be called with
> bind_param_array then execute_array but in this case
> sth->{Params} does not contain the parameters after the
> sth->SUPER::execute_array call (I was suprised by that).
Looking at the code for bind_param_array I see the bound refs are
stored in $sth->{ParamArrays}.
> This is a problem for me since I want to examine the
> tuple_status passed to execute_array and find the
> parameters for the execute which failed. I've found another
> way around this for now by moving the code which highlights
> the parameters which failed into execute.
I'd accept a doc patch to document the ParamArrays attribute.
> Just to put some context on this I'm suclassing DBI as something
> like DBIx::Log4Perl where all the SQL, parameters, fetched data,
> transactions, error handler can be logged. I'm doing this because I
> have a large Perl server accepting a huge number of jobs per second
> which is very transaction heavy. It is impossible to debug since
> it is almost impossible to reproduce the condition which caused the
> error so I need as much info as possible at the time of the problem
> and I need it as light weight as I can make it because slowing it
> down sometimes hides the problem.
Sounds interesting.
Tim.
> Martin
>
> Tim Bunce wrote:
> >On Wed, Mar 29, 2006 at 04:49:32PM +0100, Martin J. Evans wrote:
> >
> >>Tim,
> >>
> >>Thanks, I do keep forgetting about Driver.xst.
> >>
> >>So, just to be clear. In DBI.pm, selectrow_array calls _do_selectrow which
> >>calls prepare/execute/fetchsomething but this may be overriden by a driver
> >>using selectrow_array in Driver.xst. If this is the case:
> >>
> >>a) is there any way for my subclassed DBI to know this?
> >
> >
> >Something like this might work:
> >
> > ... if exists &DBD::foo::db::selectrow_array;
> >
> >Also, take a look at the docs for the DBI's can() method. Though if you
> >want to rely on that you might want to look into the implementation to
> >check for edge cases or other subtle issues.
> >
> >
> >>b) is there an easy way to identify which other methods this could apply
> >>to
> >
> >
> >Nope. You'd have to use the above on all of them.
> >
> >Tim.
> >
> >
> >>Martin
> >>--
> >>Martin J. Evans
> >>Easysoft Ltd, UK
> >>http://www.easysoft.com
> >>
> >>
> >>On 29-Mar-2006 Tim Bunce wrote:
> >>
> >>>On Wed, Mar 29, 2006 at 11:54:32AM +0100, Martin J. Evans wrote:
> >>>
> The issue I am seeing is not quite as general as I made it sound. For
> selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I
> expect)
> but
> for selectrow_arrayref and selectrow_array I only see prepare.
> >>>
> >>>Generally... Drivers are free to implement any method in any way they
> >>>choose.
> >>>Their own methods don't have to call execute(), for example.
> >>>
> >>>Specifically... you're probably seeing the effect of the Driver.xst
> >>>C code that most compiled drivers embed into themselves. That code
> >>>embeds C implementations of selectall_arrayref and several other
> >>>methods into the driver itself. The selectall_arrayref code calls the
> >>>drivers C functions to do the work. The result is much, much, faster
> >>>than going though perl/DBI method dispatch for each row.
> >>>
> >>>Tim.
> >>>
> >>>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>
> On 29-Mar-2006 Martin J. Evans wrote:
>
> >Hi,
> >
> >I have subclassed DBI and override many methods including prepare,
> >execute
> >and
> >select* although they are predominantly just passed on to DBI.
> >
> >If I do:
> >
> >my $sth->prepare(sql);
> >$sth->execute;
> >
> >I see the prepare and execute and pass them on to DBI.
> >
> >If I do:
> >
> >$dbh->selectrow_array(sql);
> >
> >I see prepare and pass it on, but do not see execute. In my case this
> >is
> >more
> >than an annoyance as I cannot see any bound parameter passed to
> >execute.
> >
> >e.g.
> >
> >$selectrow_array = [
> >'select b from mytest where a = ?',
> >undef,
> >\1
> > ];
> >prepare: select b from mytest where a = ?
> >!!! no execute here
> >
> >Or, perhaps I'm doing something wrong.
> >
> >Can anyone tell me if I should be seeing the execute when
> >$dbh->select***
> >is
> >called? I think I should.
> >
> >Thanks
> >
> >Martin
> >--
> >Martin J. Evans
>
Re: Message from Maintainer to DBD::mysql users, developers
Tim Bunce wrote: On Wed, Mar 29, 2006 at 09:58:16AM -0800, Mark Hedges wrote: On Wed, 29 Mar 2006, Tim Bunce wrote: On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: I apologise for what might seem somewhat of a bit of neglect on my part to get some features into DBD::mysql, features such as UTF support, some bugs in 3.0002_4. I've been super busy on some other projects, but have finished one of them and have today started to go through my mail in order to start addressing some needs of DBD::mysql. I'm wondering if it might help to discuss within this list what priorities users would like to see addressed in DBD::mysql, so I could come out with some sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Hmmm, I tried setting the default connection charset: /etc/my.cnf: [client] port= 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 mysql> show variables like 'character_set_connection'; +--+---+ | Variable_name| Value | +--+---+ | character_set_connection | utf8 | +--+---+ 1 row in set (0.00 sec) But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. Perhaps DBD::mysql doesn't yet support utf8. My reply above assumed that it did. [...later...] I see no mention of utf8 or unicode in the DBD::mysql docs. That's sad. Tim. There was a patch for utf8 posted either here or on the mysql perl list within the last week. I'm at home now so can't easily find it right now but it sort of suggested to me that someone was using utf8. Martin
Re: [dbi] Re: execute not seen on selectrow_* for suclassed DBI
Thanks Tim. I'm now hopefully looking in the right places.
Not seeing execute on a selectrow_array threw me initially
but it is an example of the problems I'm seeing.
I'm having a few problems accessing things at the time I want.
e.g. execute_array can be called with the parameters (which I
want to see) and this is ok, but can also be called with
bind_param_array then execute_array but in this case
sth->{Params} does not contain the parameters after the
sth->SUPER::execute_array call (I was suprised by that).
This is a problem for me since I want to examine the
tuple_status passed to execute_array and find the
parameters for the execute which failed. I've found another
way around this for now by moving the code which highlights
the parameters which failed into execute.
Just to put some context on this I'm suclassing DBI as something
like DBIx::Log4Perl where all the SQL, parameters, fetched data,
transactions, error handler can be logged. I'm doing this because I
have a large Perl server accepting a huge number of jobs per second
which is very transaction heavy. It is impossible to debug since
it is almost impossible to reproduce the condition which caused the
error so I need as much info as possible at the time of the problem
and I need it as light weight as I can make it because slowing it
down sometimes hides the problem.
Martin
Tim Bunce wrote:
On Wed, Mar 29, 2006 at 04:49:32PM +0100, Martin J. Evans wrote:
Tim,
Thanks, I do keep forgetting about Driver.xst.
So, just to be clear. In DBI.pm, selectrow_array calls _do_selectrow which
calls prepare/execute/fetchsomething but this may be overriden by a driver
using selectrow_array in Driver.xst. If this is the case:
a) is there any way for my subclassed DBI to know this?
Something like this might work:
... if exists &DBD::foo::db::selectrow_array;
Also, take a look at the docs for the DBI's can() method. Though if you
want to rely on that you might want to look into the implementation to
check for edge cases or other subtle issues.
b) is there an easy way to identify which other methods this could apply to
Nope. You'd have to use the above on all of them.
Tim.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 29-Mar-2006 Tim Bunce wrote:
On Wed, Mar 29, 2006 at 11:54:32AM +0100, Martin J. Evans wrote:
The issue I am seeing is not quite as general as I made it sound. For
selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I expect)
but
for selectrow_arrayref and selectrow_array I only see prepare.
Generally... Drivers are free to implement any method in any way they choose.
Their own methods don't have to call execute(), for example.
Specifically... you're probably seeing the effect of the Driver.xst
C code that most compiled drivers embed into themselves. That code
embeds C implementations of selectall_arrayref and several other
methods into the driver itself. The selectall_arrayref code calls the
drivers C functions to do the work. The result is much, much, faster
than going though perl/DBI method dispatch for each row.
Tim.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 29-Mar-2006 Martin J. Evans wrote:
Hi,
I have subclassed DBI and override many methods including prepare, execute
and
select* although they are predominantly just passed on to DBI.
If I do:
my $sth->prepare(sql);
$sth->execute;
I see the prepare and execute and pass them on to DBI.
If I do:
$dbh->selectrow_array(sql);
I see prepare and pass it on, but do not see execute. In my case this is
more
than an annoyance as I cannot see any bound parameter passed to execute.
e.g.
$selectrow_array = [
'select b from mytest where a = ?',
undef,
\1
];
prepare: select b from mytest where a = ?
!!! no execute here
Or, perhaps I'm doing something wrong.
Can anyone tell me if I should be seeing the execute when $dbh->select***
is
called? I think I should.
Thanks
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Is ArrayTupleStatus in execute_array mandatory?
On Wed, Mar 29, 2006 at 03:57:39PM +0100, Martin J. Evans wrote:
> According to the docs for execute_array:
>
> "The mandatory ArrayTupleStatus attribute is used to specify a reference to
> array which will receive the execute status of each executed parameter tuple."
>
> It would appear in DBI 1.50 you can omit it:
>
> $sth->execute_array({ });
>
> Is it mandatory and the docs are correct or is it that DBI does not check for
> its existence?
Um, ... checking the Changes file I find:
=head2 Changes in DBI 1.38,21th August 2003
...
Changed execute_array() to no longer require ArrayTupleStatus attribute.
...
I've updated the docs.
Thanks.
Tim.
Re: [dbi] Re: execute not seen on selectrow_* for suclassed DBI
On Wed, Mar 29, 2006 at 04:49:32PM +0100, Martin J. Evans wrote: > Tim, > > Thanks, I do keep forgetting about Driver.xst. > > So, just to be clear. In DBI.pm, selectrow_array calls _do_selectrow which > calls prepare/execute/fetchsomething but this may be overriden by a driver > using selectrow_array in Driver.xst. If this is the case: > > a) is there any way for my subclassed DBI to know this? Something like this might work: ... if exists &DBD::foo::db::selectrow_array; Also, take a look at the docs for the DBI's can() method. Though if you want to rely on that you might want to look into the implementation to check for edge cases or other subtle issues. > b) is there an easy way to identify which other methods this could apply to Nope. You'd have to use the above on all of them. Tim. > Martin > -- > Martin J. Evans > Easysoft Ltd, UK > http://www.easysoft.com > > > On 29-Mar-2006 Tim Bunce wrote: > > On Wed, Mar 29, 2006 at 11:54:32AM +0100, Martin J. Evans wrote: > >> The issue I am seeing is not quite as general as I made it sound. For > >> selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I > >> expect) > >> but > >> for selectrow_arrayref and selectrow_array I only see prepare. > > > > Generally... Drivers are free to implement any method in any way they > > choose. > > Their own methods don't have to call execute(), for example. > > > > Specifically... you're probably seeing the effect of the Driver.xst > > C code that most compiled drivers embed into themselves. That code > > embeds C implementations of selectall_arrayref and several other > > methods into the driver itself. The selectall_arrayref code calls the > > drivers C functions to do the work. The result is much, much, faster > > than going though perl/DBI method dispatch for each row. > > > > Tim. > > > >> Martin > >> -- > >> Martin J. Evans > >> Easysoft Ltd, UK > >> http://www.easysoft.com > >> > >> > >> On 29-Mar-2006 Martin J. Evans wrote: > >> > Hi, > >> > > >> > I have subclassed DBI and override many methods including prepare, > >> > execute > >> > and > >> > select* although they are predominantly just passed on to DBI. > >> > > >> > If I do: > >> > > >> > my $sth->prepare(sql); > >> > $sth->execute; > >> > > >> > I see the prepare and execute and pass them on to DBI. > >> > > >> > If I do: > >> > > >> > $dbh->selectrow_array(sql); > >> > > >> > I see prepare and pass it on, but do not see execute. In my case this is > >> > more > >> > than an annoyance as I cannot see any bound parameter passed to execute. > >> > > >> > e.g. > >> > > >> > $selectrow_array = [ > >> > 'select b from mytest where a = ?', > >> > undef, > >> > \1 > >> >]; > >> > prepare: select b from mytest where a = ? > >> > !!! no execute here > >> > > >> > Or, perhaps I'm doing something wrong. > >> > > >> > Can anyone tell me if I should be seeing the execute when $dbh->select*** > >> > is > >> > called? I think I should. > >> > > >> > Thanks > >> > > >> > Martin > >> > -- > >> > Martin J. Evans > >> > Easysoft Ltd, UK > >> > http://www.easysoft.com
Re: Message from Maintainer to DBD::mysql users, developers
On Wed, Mar 29, 2006 at 09:58:16AM -0800, Mark Hedges wrote: > On Wed, 29 Mar 2006, Tim Bunce wrote: > > > > On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: > > > On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: > > > > I apologise for what might seem somewhat of a bit of neglect on my part > > > > to get > > > > some features into DBD::mysql, features such as UTF support, some bugs > > > > in > > > > 3.0002_4. I've been super busy on some other projects, but have > > > > finished one of > > > > them and have today started to go through my mail in order to start > > > > addressing > > > > some needs of DBD::mysql. > > > > > > > > I'm wondering if it might help to discuss within this list what > > > > priorities > > > > users would like to see addressed in DBD::mysql, so I could come out > > > > with some > > > > sort of road map. > > > > > > Sorry for the late reply, but I just stumbled across it again recently: > > > > > > Since mysql supports different charsets per table and even per column, > > > I'd like an option to automatically convert them to and from perl's > > > internal UTF-8 encoding. > > > > > > (Actually, I'd like that to be the default behaviour, but it probably > > > would break a lot of existing scripts, so it should be an option at > > > first) > > > > I think that translates into just asking DBD::mysql to set the > > 'connection charset' to utf8 and then mysql server will look after the > > conversions for you. > > Hmmm, I tried setting the default connection charset: > > /etc/my.cnf: > [client] > port= 3306 > socket = /var/run/mysqld/mysqld.sock > default-character-set=utf8 > > mysql> show variables like 'character_set_connection'; > +--+---+ > | Variable_name| Value | > +--+---+ > | character_set_connection | utf8 | > +--+---+ > 1 row in set (0.00 sec) > > But the scalars selected from a utf8 field still do not have the > utf8 flag set in perl. Perhaps DBD::mysql doesn't yet support utf8. My reply above assumed that it did. [...later...] I see no mention of utf8 or unicode in the DBD::mysql docs. That's sad. Tim.
RE: forcing utf8 on selected values
On Wed, 29 Mar 2006, Mark Hedges wrote: > > > > Hmm, for some reason this morning the Matrix has allowed the > > selected characters to display correctly in the browser. This > > doesn't make a whole lot of sense to me now. > > > > The scalars still do not have the utf8 flag set, though. > > This is really weird. All the characters display correctly in > the browser now EXCEPT what used to be a generic apostrophe, > which now comes across as this non-printable character: > > U+0092 > > General Character Properties > > Unicode category: Other, Control > > Various Useful Representations > > UTF-8: 0xC2 0x92 > Octal escaped UTF-8: \302\222 > Decimal entity reference: ’ > > Annotations and Cross References > > Alias names: > PRIVATE USE TWO > > But this displays fine in mysql console on my terminal. Here I am talking to myself in public again. Scratch that -- the original source database is corrupt and actually has this character stored in these fields. Why they display correctly as quotes in the terminal or when viewed in western encoding is a mystery. Mark
RE: forcing utf8 on selected values
On Wed, 29 Mar 2006, Mark Hedges wrote: > > Hmm, for some reason this morning the Matrix has allowed the > selected characters to display correctly in the browser. This > doesn't make a whole lot of sense to me now. > > The scalars still do not have the utf8 flag set, though. This is really weird. All the characters display correctly in the browser now EXCEPT what used to be a generic apostrophe, which now comes across as this non-printable character: U+0092 General Character Properties Unicode category: Other, Control Various Useful Representations UTF-8: 0xC2 0x92 Octal escaped UTF-8: \302\222 Decimal entity reference: ’ Annotations and Cross References Alias names: PRIVATE USE TWO But this displays fine in mysql console on my terminal. Argh. Mark
Re: Message from Maintainer to DBD::mysql users, developers
On Wed, 29 Mar 2006, Tim Bunce wrote: > > On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: > > On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: > > > I apologise for what might seem somewhat of a bit of neglect on my part > > > to get > > > some features into DBD::mysql, features such as UTF support, some bugs in > > > 3.0002_4. I've been super busy on some other projects, but have finished > > > one of > > > them and have today started to go through my mail in order to start > > > addressing > > > some needs of DBD::mysql. > > > > > > I'm wondering if it might help to discuss within this list what > > > priorities > > > users would like to see addressed in DBD::mysql, so I could come out with > > > some > > > sort of road map. > > > > Sorry for the late reply, but I just stumbled across it again recently: > > > > Since mysql supports different charsets per table and even per column, > > I'd like an option to automatically convert them to and from perl's > > internal UTF-8 encoding. > > > > (Actually, I'd like that to be the default behaviour, but it probably > > would break a lot of existing scripts, so it should be an option at > > first) > > I think that translates into just asking DBD::mysql to set the > 'connection charset' to utf8 and then mysql server will look after the > conversions for you. Hmmm, I tried setting the default connection charset: /etc/my.cnf: [client] port= 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 mysql> show variables like 'character_set_connection'; +--+---+ | Variable_name| Value | +--+---+ | character_set_connection | utf8 | +--+---+ 1 row in set (0.00 sec) But the scalars selected from a utf8 field still do not have the utf8 flag set in perl. Various abstraction layer plugins like Class::DBI::utf8 and DBIx::Class::UTF8Columns attempt to bandage this problem. But I don't want to use heavyweight systems for lightweight high-volume queries that I can type more easily as SQL. Mark
RE: forcing utf8 on selected values
On Wed, 29 Mar 2006, Garrett, Philip (MAN-Corporate) wrote:
>> My database has utf8 columns, server/client character sets and
>> collation. Yet when I call fetchrow, resulting Perl scalars do
>> not have the utf8 flag set. That's really annoying. Am I
>> supposed to call
>>
>> map { utf8::upgrade($_) } $every,$stupid,$little,$scalar
>>
>> every time I do a select statement? Or map through the results
>> of any selectall_*ref's? This fixes the display problems when
>> printing a page to a browser with charset utf-8.
>>
>> Is there an easier way to do this? Please tell me there is.
>
> What database are you using? Perhaps the driver has an option
> to do this. I know Oracle does.
mysql.
I've been told this might work upon connecting:
$dbh->do('SET NAMES utf8')
But it doesn't have any effect.
Hmm, for some reason this morning the Matrix has allowed the
selected characters to display correctly in the browser. This
doesn't make a whole lot of sense to me now.
The scalars still do not have the utf8 flag set, though.
Mark
Possible bug in execute_array with dbd::mysql
Hi,
I think I may have found a bug in DBD::mysql for execute_array.
The following code demonstrates:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
#'DBI:ODBC:mjetest', 'xxx', 'yyy',
) or die "DBI::errstr";
my ($sth, $ref, $rowval, @row, $affected, $param, $inserted, @tuple_status);
$dbh->do(q/drop table if exists mytest/);
$dbh->do(q/create table mytest (a int primary key, b char(20))/);
$sth = $dbh->prepare(q/insert into mytest values (?,?)/);
$sth->execute(1, 'one');
$sth->execute(2, 'two');
$sth->execute(3,, 'three');
$sth->bind_param_array(1, [51,1,52,53]);
$sth->bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
$inserted = $sth->execute_array( { ArrayTupleStatus => [EMAIL PROTECTED] } );
print join(",", @tuple_status), "\n";
print Dumper([EMAIL PROTECTED]), "\n";
which when run produces:
DBD::mysql::st execute_array failed: Duplicate entry '1' for key 1 at z.pl line
23.
1,ARRAY(0x82b3ea0),ARRAY(0x82b3e94),ARRAY(0x82b3f00)
$VAR1 = [
1,
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
],
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
],
[
1062,
'Duplicate entry \'1\' for key 1',
'S1000'
]
];
Note, the tuple_status array contains reference for the 2nd, 3rd and 4th
execute although the 3rd and 4th execute work - checked in the table which
shows:
a:b:
1:one:
2:two:
3:three:
51:fiftyone:
52:fiftytwo:
53:fiftythree:
afterwards. If I simply switch to DBD:ODBC and use myodbc it correctly
displays:
1,ARRAY(0x8293db4),1,1
$VAR1 = [
1,
[
-1,
'[unixODBC][MySQL][ODBC 3.51 Driver][mysqld-5.0.15-log]Duplicate
entry \'1\' for key 1 (SQL-23000)(DBD: st_execute/
SQLExecute err=-1)',
'23000'
],
1,
1
];
and the table contents are the same.
I have not yet been able to fix this.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
RE: [dbi] Re: execute not seen on selectrow_* for suclassed DBI
Tim, Thanks, I do keep forgetting about Driver.xst. So, just to be clear. In DBI.pm, selectrow_array calls _do_selectrow which calls prepare/execute/fetchsomething but this may be overriden by a driver using selectrow_array in Driver.xst. If this is the case: a) is there any way for my subclassed DBI to know this? b) is there an easy way to identify which other methods this could apply to Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com On 29-Mar-2006 Tim Bunce wrote: > On Wed, Mar 29, 2006 at 11:54:32AM +0100, Martin J. Evans wrote: >> The issue I am seeing is not quite as general as I made it sound. For >> selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I expect) >> but >> for selectrow_arrayref and selectrow_array I only see prepare. > > Generally... Drivers are free to implement any method in any way they choose. > Their own methods don't have to call execute(), for example. > > Specifically... you're probably seeing the effect of the Driver.xst > C code that most compiled drivers embed into themselves. That code > embeds C implementations of selectall_arrayref and several other > methods into the driver itself. The selectall_arrayref code calls the > drivers C functions to do the work. The result is much, much, faster > than going though perl/DBI method dispatch for each row. > > Tim. > >> Martin >> -- >> Martin J. Evans >> Easysoft Ltd, UK >> http://www.easysoft.com >> >> >> On 29-Mar-2006 Martin J. Evans wrote: >> > Hi, >> > >> > I have subclassed DBI and override many methods including prepare, execute >> > and >> > select* although they are predominantly just passed on to DBI. >> > >> > If I do: >> > >> > my $sth->prepare(sql); >> > $sth->execute; >> > >> > I see the prepare and execute and pass them on to DBI. >> > >> > If I do: >> > >> > $dbh->selectrow_array(sql); >> > >> > I see prepare and pass it on, but do not see execute. In my case this is >> > more >> > than an annoyance as I cannot see any bound parameter passed to execute. >> > >> > e.g. >> > >> > $selectrow_array = [ >> > 'select b from mytest where a = ?', >> > undef, >> > \1 >> >]; >> > prepare: select b from mytest where a = ? >> > !!! no execute here >> > >> > Or, perhaps I'm doing something wrong. >> > >> > Can anyone tell me if I should be seeing the execute when $dbh->select*** >> > is >> > called? I think I should. >> > >> > Thanks >> > >> > Martin >> > -- >> > Martin J. Evans >> > Easysoft Ltd, UK >> > http://www.easysoft.com
RE: Upgrade.. DBI
ppm 'install Test::More' -Original Message- From: Paresh Rathod [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 6:35 AM To: dbi-users@perl.org Subject: Upgrade.. DBI Sir, Please provide me the help to upgrade the latest of DBI. I am using Oracle 9 It shows error: Can't locate Test/More.pm in @INC (@INC contains: lib C:/paresh/Perl/lib C:/pare sh/Perl/site/lib .) at makefile.pl line 36. BEGIN failed--compilation aborted at makefile.pl line 36. Waiting for ur response... Regards Paresh - Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less. This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
Re: execute not seen on selectrow_* for suclassed DBI
On Wed, Mar 29, 2006 at 11:54:32AM +0100, Martin J. Evans wrote: > The issue I am seeing is not quite as general as I made it sound. For > selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I expect) > but > for selectrow_arrayref and selectrow_array I only see prepare. Generally... Drivers are free to implement any method in any way they choose. Their own methods don't have to call execute(), for example. Specifically... you're probably seeing the effect of the Driver.xst C code that most compiled drivers embed into themselves. That code embeds C implementations of selectall_arrayref and several other methods into the driver itself. The selectall_arrayref code calls the drivers C functions to do the work. The result is much, much, faster than going though perl/DBI method dispatch for each row. Tim. > Martin > -- > Martin J. Evans > Easysoft Ltd, UK > http://www.easysoft.com > > > On 29-Mar-2006 Martin J. Evans wrote: > > Hi, > > > > I have subclassed DBI and override many methods including prepare, execute > > and > > select* although they are predominantly just passed on to DBI. > > > > If I do: > > > > my $sth->prepare(sql); > > $sth->execute; > > > > I see the prepare and execute and pass them on to DBI. > > > > If I do: > > > > $dbh->selectrow_array(sql); > > > > I see prepare and pass it on, but do not see execute. In my case this is > > more > > than an annoyance as I cannot see any bound parameter passed to execute. > > > > e.g. > > > > $selectrow_array = [ > > 'select b from mytest where a = ?', > > undef, > > \1 > >]; > > prepare: select b from mytest where a = ? > > !!! no execute here > > > > Or, perhaps I'm doing something wrong. > > > > Can anyone tell me if I should be seeing the execute when $dbh->select*** is > > called? I think I should. > > > > Thanks > > > > Martin > > -- > > Martin J. Evans > > Easysoft Ltd, UK > > http://www.easysoft.com
Re: Re: Upgrade.. DBI
On Wed, 2006-03-29 at 17:57 +0300, [EMAIL PROTECTED] wrote: > Please use [EMAIL PROTECTED] instead of [EMAIL PROTECTED] > I didn't use either, I replied to a post on dbi-users list. If you're email has changed then correct it with the dbi-user list, don't send replies like this when some posts to dbi-users. > -- Scott T. Hildreth <[EMAIL PROTECTED]>
Is ArrayTupleStatus in execute_array mandatory?
According to the docs for execute_array:
"The mandatory ArrayTupleStatus attribute is used to specify a reference to
array which will receive the execute status of each executed parameter tuple."
It would appear in DBI 1.50 you can omit it:
$sth->execute_array({ });
Is it mandatory and the docs are correct or is it that DBI does not check for
its existence?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Upgrade.. DBI
On Wed, 2006-03-29 at 05:34 -0800, Paresh Rathod wrote: > Sir, > > Please provide me the help to upgrade the latest of DBI. > I am using Oracle 9 > > It shows error: > > Can't locate Test/More.pm You need to install Test::More. > in @INC (@INC contains: lib C:/paresh/Perl/lib C:/pare > sh/Perl/site/lib .) at makefile.pl line 36. > BEGIN failed--compilation aborted at makefile.pl line 36. > > Waiting for ur response... > Regards > Paresh > > > - > Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ > countries) for 2¢/min or less. -- Scott T. Hildreth <[EMAIL PROTECTED]>
Upgrade.. DBI
Sir, Please provide me the help to upgrade the latest of DBI. I am using Oracle 9 It shows error: Can't locate Test/More.pm in @INC (@INC contains: lib C:/paresh/Perl/lib C:/pare sh/Perl/site/lib .) at makefile.pl line 36. BEGIN failed--compilation aborted at makefile.pl line 36. Waiting for ur response... Regards Paresh - Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less.
RE: execute not seen on selectrow_* for suclassed DBI
The issue I am seeing is not quite as general as I made it sound. For selectrow_hashref I see prepare/execute/fetch/fetchrow_hashref (as I expect) but for selectrow_arrayref and selectrow_array I only see prepare. Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com On 29-Mar-2006 Martin J. Evans wrote: > Hi, > > I have subclassed DBI and override many methods including prepare, execute > and > select* although they are predominantly just passed on to DBI. > > If I do: > > my $sth->prepare(sql); > $sth->execute; > > I see the prepare and execute and pass them on to DBI. > > If I do: > > $dbh->selectrow_array(sql); > > I see prepare and pass it on, but do not see execute. In my case this is more > than an annoyance as I cannot see any bound parameter passed to execute. > > e.g. > > $selectrow_array = [ > 'select b from mytest where a = ?', > undef, > \1 >]; > prepare: select b from mytest where a = ? > !!! no execute here > > Or, perhaps I'm doing something wrong. > > Can anyone tell me if I should be seeing the execute when $dbh->select*** is > called? I think I should. > > Thanks > > Martin > -- > Martin J. Evans > Easysoft Ltd, UK > http://www.easysoft.com
execute not seen on selectrow_* for suclassed DBI
Hi, I have subclassed DBI and override many methods including prepare, execute and select* although they are predominantly just passed on to DBI. If I do: my $sth->prepare(sql); $sth->execute; I see the prepare and execute and pass them on to DBI. If I do: $dbh->selectrow_array(sql); I see prepare and pass it on, but do not see execute. In my case this is more than an annoyance as I cannot see any bound parameter passed to execute. e.g. $selectrow_array = [ 'select b from mytest where a = ?', undef, \1 ]; prepare: select b from mytest where a = ? !!! no execute here Or, perhaps I'm doing something wrong. Can anyone tell me if I should be seeing the execute when $dbh->select*** is called? I think I should. Thanks Martin -- Martin J. Evans Easysoft Ltd, UK http://www.easysoft.com
Re: Message from Maintainer to DBD::mysql users, developers
On Wed, Mar 29, 2006 at 10:53:56AM +0200, Peter J. Holzer wrote: > On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: > > I apologise for what might seem somewhat of a bit of neglect on my part to > > get > > some features into DBD::mysql, features such as UTF support, some bugs in > > 3.0002_4. I've been super busy on some other projects, but have finished > > one of > > them and have today started to go through my mail in order to start > > addressing > > some needs of DBD::mysql. > > > > I'm wondering if it might help to discuss within this list what priorities > > users would like to see addressed in DBD::mysql, so I could come out with > > some > > sort of road map. > > Sorry for the late reply, but I just stumbled across it again recently: > > Since mysql supports different charsets per table and even per column, > I'd like an option to automatically convert them to and from perl's > internal UTF-8 encoding. > > (Actually, I'd like that to be the default behaviour, but it probably > would break a lot of existing scripts, so it should be an option at > first) I think that translates into just asking DBD::mysql to set the 'connection charset' to utf8 and then mysql server will look after the conversions for you. Tim.
Re: Message from Maintainer to DBD::mysql users, developers
On 2006-01-31 01:24:18 +0100, Patrick Galbraith wrote: > I apologise for what might seem somewhat of a bit of neglect on my part to > get > some features into DBD::mysql, features such as UTF support, some bugs in > 3.0002_4. I've been super busy on some other projects, but have finished one > of > them and have today started to go through my mail in order to start > addressing > some needs of DBD::mysql. > > I'm wondering if it might help to discuss within this list what priorities > users would like to see addressed in DBD::mysql, so I could come out with > some > sort of road map. Sorry for the late reply, but I just stumbled across it again recently: Since mysql supports different charsets per table and even per column, I'd like an option to automatically convert them to and from perl's internal UTF-8 encoding. (Actually, I'd like that to be the default behaviour, but it probably would break a lot of existing scripts, so it should be an option at first) hp -- _ | Peter J. Holzer| If I wanted to be "academically correct", |_|_) | Sysadmin WSR | I'd be programming in Java. | | | [EMAIL PROTECTED] | I don't, and I'm not. __/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users pgpiLrJv7aGcd.pgp Description: PGP signature
