|
|
| Applications |
| Mailing Lists |
| Miscellaneous |
| Operating Systems |
| Programming |
Re: DBI:mysql does not return any value
From: Thomas Goik <liste(at)mi-espacio.com>
Date: Wed Aug 15 2007 - 03:24:43 EDT
},
.....
);
@stats = keys %stats;
foreach(sort @stats) {
}
....
END
> Have you run "show warnings" after each SQL query in your script just to
> see whether there's anything in there?
<- execute= 1800 at stats.pl line 80 ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x82cb814)~INNER) thr#814cc80
! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x82c7084)~INNER) thr#814cc80
&imp_dbh->mysql: 82c776c
! <- DESTROY= undef during global destruction
> That's odd, I can't replicate your error. I've created a single table:
> CREATE TABLE `foo` (
> `id` int(10) unsigned NOT NULL auto_increment,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=3501 DEFAULT CHARSET=latin1;
> I populate it with 3500 rows:
> ---
> #!/usr/bin/perl -w
> use strict;
> use DBI;
> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
> my $sth = $dbh->prepare( "INSERT INTO foo SET id = NULL" );
> for ( 1 .. 3500 ) {
> $sth->execute();
> }
> ---
> I then run the following script to query it, which is similar to your
> script:
> ---
> #!/usr/bin/perl -w
> use strict;
> use DBI;
> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
> my $sth = $dbh->prepare( "SELECT * FROM foo" );
> $sth->execute();
> my $total = $dbh->selectrow_array("select FOUND_ROWS()");
> while ( my ( $id ) = $sth->fetchrow_array() ) {
> print $id, "\n";
> }
> print "There were $total overall\n";
> ---
> It prints a line for each row that it selected from the table. and the
> total (3500 rows) at the end.
> What is in your $stats{$_}{where} variable? I'm wondering whether it's
> the problem.
> Have you run "show warnings" after each SQL query in your script just to
> see whether there's anything in there?
> --
> Kenny
> Thomas Goik wrote:
>
Date: Wed Aug 15 2007 - 03:24:43 EDT
Hi Kenny;
> What is in your $stats{$_}{where} variable? I'm wondering whether it's
> the problem.
In this hash are the diferent tasks to do:
my %stats = ( 'abc001' => {
bind => 1,
sql => qq[select count(*) from tblItems where itSeller = ? and
itClosedflag=0 ],
where => qq[select SQL_CALC_FOUND_ROWS itSeller from tblItems where
itClosedflag=0 GROUP BY itSeller],
},
'item_planstart' => {
bind=> 1,
sql => qq(select count(*) from tblItems where itSeller = ? and
itClosedflag=6 ),
where => qq(select itSeller from tblItems where itClosedflag=6 group
by itSeller),
},
.....
);
@stats = keys %stats;
foreach(sort @stats) {
$sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr;
$sth->execute();
print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
"\n";
$sdb = $dbh->prepare($stats{$_}{sql});
while($user_id = $sth->fetchrow_array()) {
# perform the stat->sql query with
$sdb->execute($user_id);
$cnt = $sdb->fetchrow_array();
$dbh->do("replace stats.user_stat set user_id = $user_id,
name ='$_', value = " . ($cnt || 0));
}
}
....
END
> Have you run "show warnings" after each SQL query in your script just to
> see whether there's anything in there?
I used DBI->trace(4) to get some information. DBI::db=HASH(0x82c7084) trace level set to 0x0/4 (DBI @ 0x0/0) in DBI 1.46-ithread (pid 11592)
-> prepare for DBD::mysql::db (DBI::db=HASH(0x82a4030)~0x82c7084 '
select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
GROUP BY itSel
ler ') thr#814cc80
dbih_setup_handle(DBI::st=HASH(0x82c71e0)=>DBI::st=HASH(0x82cb814), DBD::mysql::st, 82c71ec, Null!)
dbih_make_com(DBI::db=HASH(0x82c7084), 82c7718, DBD::mysql::st, 216, 0)
thr#814cc80
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x82c71e0) at stats.pl line 79 -> execute for DBD::mysql::st (DBI::st=HASH(0x82c71e0)~0x82cb814) thr#814cc80
-> dbd_st_execute for 082cb904
-> mysql_st_interal_execute
<- mysql_st_internal_execute ERROR: returning -1
<- dbd_st_execute 1800 rows
<- execute= 1800 at stats.pl line 80 ! -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x82cb814)~INNER) thr#814cc80
! <- DESTROY= undef during global destruction ! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x82c7084)~INNER) thr#814cc80
&imp_dbh->mysql: 82c776c
! <- DESTROY= undef during global destruction
Bye Thomas
- Original Message ----- From: "Kenny Scott" <kenny@kennyscott.co.uk> To: "Thomas Goik" <liste@mi-espacio.com> Sent: Tuesday, August 14, 2007 10:03 PM Subject: Re: DBI:mysql does not return any value
> Hi,
>
> That's odd, I can't replicate your error. I've created a single table:
>
> CREATE TABLE `foo` (
> `id` int(10) unsigned NOT NULL auto_increment,
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=3501 DEFAULT CHARSET=latin1;
>
> I populate it with 3500 rows:
>
> ---
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
> my $sth = $dbh->prepare( "INSERT INTO foo SET id = NULL" );
> for ( 1 .. 3500 ) {
> $sth->execute();
> }
> ---
>
> I then run the following script to query it, which is similar to your
> script:
>
> ---
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> my $dbh = DBI->connect( "dbi:mysql:test", "root" ) or die DBI->errstr;
> my $sth = $dbh->prepare( "SELECT * FROM foo" );
> $sth->execute();
> my $total = $dbh->selectrow_array("select FOUND_ROWS()");
> while ( my ( $id ) = $sth->fetchrow_array() ) {
> print $id, "\n";
> }
>
> print "There were $total overall\n";
> ---
>
> It prints a line for each row that it selected from the table. and the
> total (3500 rows) at the end.
>
> What is in your $stats{$_}{where} variable? I'm wondering whether it's
> the problem.
>
> Have you run "show warnings" after each SQL query in your script just to
> see whether there's anything in there?
>
> --
> Kenny
> >
> Thomas Goik wrote:
>> Hello;
>>
>> I'm on a strange problem in my script i can't resolved by myself, so i
>> hope that anybody has an idea.
>>
>> The problem is that when i start a $dbh->prepare statement which will
>> return more than X rows the result is -1. Exactly the same statement on
>> mysql shell will return the result, in this case 1800 rows.
>>
>> is there any limitation on the rows returned in DBI:mysql?
>> I connect from the machine where mysql is running
>>
>> The statement will search inside of 1.800.000 items of its owners and
>> this are 1800 .
>> The statement in the script is:
>> select SQL_CALC_FOUND_ROWS itSeller from tblItems where itClosedflag=0
>> GROUP BY itSeller
>>
>> $sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr ;
>> $sth->execute();
>> # is searching in the table
>> print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
>> "\n";
>> # 1800
>> while($user_id = $sth->fetchrow_array()) {
>> # nothing
>> }
>>
>> if i do so on mysql it will return the right numbers and rows.
>>
>> max_allow_package = 80 MB
>> mysql version 5.0.32
>> Perl : 5.008004 (i386-linux-thread-multi)
>> OS : linux (2.6.15.6)
>> DBI : 1.46
>> DBD::mysql : 2.9006
>> DBD::XBase : 0.240
>> DBD::Sponge : 11.10
>> DBD::Proxy : 0.2004
>> DBD::File : 0.31
>> DBD::ExampleP : 11.12
>> DBD::DBM : 0.02
>>
>>
>>
>>
>>
>
-- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe: http://lists.mysql.com/perl?unsub=lists@pantek.comReceived on Wed Aug 15 03:24:59 2007
This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 10:15:35 EDT
|
Contact Us Legal Notices Order Services Online Pantek Home Privacy Policy IT news Site Map Pantek Library |