Re: DBI:mysql does not return any value (resolved)

Hello List;

If any body likes to know, in the code it selfs where no problem.
To solve the strange behaviour of the fetchrow_array I changed it to 
fetchrow_hashref and it worked like it should.

So thanks to anybody who looked a minute on my mails?

Ciao Thomas


----- Original Message -----
From: "Thomas Goik" <[email protected]>
To: <[email protected]>
Sent: Wednesday, August 15, 2007 9:24 AM
Subject: 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]
> 


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]