Re: DBI:mysql does not return any value

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" <[email protected]>
To: "Thomas Goik" <[email protected]>
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/[email protected]