headers
Thomas Goik | 14 Aug 2007 12:15

DBI:mysql does not return any value

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.
(Continue reading)

Permalink | Reply | 1 comment |
headers
Thomas Goik | 14 Aug 2007 14:16

Re: DBI:mysql does not return any value

Hi Yair;

>May be your perl version is old, the last working versions are 5.6 and 5.8. 
>If the modules are new versions working into an old

perl -V
Summary of my perl5 (revision 5 version 8 subversion 4) ...

This is not my case

Thanks, any other idea!

Ciao Thomas

-----Original Message-----
From: Thomas Goik [mailto:liste <at> mi-espacio.com]
Sent: Tuesday, August 14, 2007 1:16 PM
To: perl <at> lists.mysql.com
Subject: DBI:mysql does not return any value

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?
(Continue reading)

Permalink | Reply | 0 comments |
headers
Patrick Galbraith | 14 Aug 2007 15:51

Re: DBI:mysql does not return any value

Hi,

What do you get if you remove the 'select ... FOUND_ROWS..' ?

Patrick

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()") .
(Continue reading)

Permalink | Reply | 0 comments |
headers
Thomas Goik | 15 Aug 2007 09:24

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),
},
.....
);
 <at> stats = keys %stats;
foreach(sort  <at> stats) {

            $sth = $dbh->prepare(" $stats{$_}{where} ") || die $dbh->errstr;
            $sth->execute();
             print "\n" . $dbh->selectrow_array("select FOUND_ROWS()") .
"\n";
(Continue reading)

Permalink | Reply | 1 comment |
headers
Patrick Galbraith | 20 Aug 2007 12:39

Re: Problem with Perl DBD::Mysql module tests

Paul,

Thanks much! I will test this out, and can include this in the next 
release if it helps the situation you describe, which is something I've 
wanted to deal with for some time.

regards,

Patrick

Paul Wayper wrote:

>Hi there!
>
>I recently had to install the Perl DBD::Mysql module from CPAN (using
>perl -MCPAN -e shell).  As a good administrator I have changed the root
>MySQL password, and this causes the make tests to fail.  With a little
>digging around in t/lib.pm I found that if I set the environment
>variable DBI_PASS and run the tests it would import it.  However, this
>did not work and the $::test_password variable remained blank.  Further
>digging around found that, in the import of t/mysql.mtest it overwrites
>all the variables that have been previously set.  I made a couple of
>changes to the mtest file to preserve any previously set variables and
>include the diff here:
>
>--- ../DBD-mysql-4.005-MXzdoi/t/mysql.mtest     2007-07-20
>17:31:37.000000000 +1000
>+++ t/mysql.mtest       2007-07-23 10:53:54.000000000 +1000
> <at>  <at>  -12,13 +12,13  <at>  <at> 
>          'testpassword' => '',
(Continue reading)

Permalink | Reply | 0 comments |
headers
Thomas Goik | 22 Aug 2007 23:56

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 <at> mi-espacio.com>
To: <perl <at> 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],
>                                    },
(Continue reading)

Permalink | Reply | 0 comments |
headers
Robert Rombaut | 23 Aug 2007 16:16
Picon
Favicon

install problem with DBD-mysql-4.005 on Solaris 10

Hi,

I'm trying to install DBD-mysql-4.005 on Solaris 10.  I have  
previously installed mysql-5.0.41-solaris10-sparc and have it up and  
running.

When I run "perl Makefile.PL" the default options are set to ...

--cflags=-I/oudvmt/local_rt/mysql/include -mt -D_FORTEC_ -xarch=v8
--libs=-xarch=v8 -L/oudvmt/local_rt/mysql/lib -lmysqlclient -lz - 
lposix4 -lresolv -lgen -lsocket -lnsl -lm

Because -mt and xarch=v8 are not recognized by gcc, I pass in the  
following options instead ...

--cflags=--cflags "-I/oudvmt/local_rt/mysql/include"  --libs
--libs="-L/oudvmt/local_rt/mysql/lib -lmysqlclient -lz -lposix4 - 
lresolv -lgen -lsocket -lnsl -lm"

This seems to work for "perl Makefile.PL" and "make" except "make"  
has the following warnings ...

Warning: duplicate function definition 'do' detected in mysql.xs,  
line 225
Warning: duplicate function definition 'rows' detected in mysql.xs,  
line 612

I persevere ahead and run "make test" and get the following error for  
all tests ...
(Continue reading)

Permalink | Reply | 0 comments |

Gmane