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" <liste@mi-espacio.com> To: <perl@lists.mysql.com> 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" <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=liste@mi-espacio.com
>

-- 
MySQL Perl Mailing List
For list archives: 
http://lists.mysql.com/perl
To unsubscribe:    
http://lists.mysql.com/perl?unsub=lists@pantek.com