Home: Perl Programming Help: DBI:
perl script hangs on query with where clause



jgraeve
New User

Dec 6, 2008, 9:48 AM


Views: 11443
perl script hangs on query with where clause

Hi,

Can somebody tell me why the second query in this script hangs.
The first query is a simple query without a where clause.
The second one is a query on the same table with a simple where clause.

Tried this on Activestate 5.10.0 distribution (ActivePerl-5.10.0.1004-MSWin32-x86-287188.msi)and Camelbox 5.10.0 distribution (camelbox_2008.304.0742Z-odin.exe)

Mysql = 5.0.51b

Thx

use strict;
use warnings;
use DBI;

my $database="mde";
my $hostname="localhost";
my $dsn = "dbi:mysqlPP:database=$database;host=$hostname";
my $user="root";
my $password="neptunus";
my $sth;

my $dbh = DBI->connect($dsn, $user, $password, {'RaiseError' => 1});
my $drh = DBI->install_driver("mysqlPP");
print "Drop table test...";
$sth = $dbh->do(qq{drop table test});
print "done\n\n";

print "create table test...";
$sth = $dbh->do(qq{
create table test (
v1 int auto_increment primary key
, v2 varchar(10)
, v3 varchar(10)
, v4 varchar(10)
, v5 varchar(10)
)
});
print "done\n\n";

print "Inserting row...";
$dbh->do(qq{insert test(v2,v3,v4,v5) values("a", "b", "c", "d")});
print "done\n\n";

print "The next statement works fine...\n";
print "Executing: select v2 from test...";
$sth = $dbh->prepare("select v1 from test");
$sth->execute();
my @row = $sth->fetchrow_array;
$sth->finish;
print "done\n\n";

print "The next statement will hang...\n";
print "Executing: select v2 from test where v1=1\n";
$sth = $dbh->prepare("select v2 from test where v1=1");
$sth->execute();
print "select with where clause hangs. You will never see this message\n";
$sth->finish;

$dbh->disconnect;

Code



      
    


FishMonger
Veteran / Moderator

Dec 6, 2008, 10:24 AM


Views: 11438
Re: [jgraeve] perl script hangs on query with where clause

Have you stepped through the script to verify that it is hanging where you think?

Your DBI->connect statement should include an <b>or die</b> statement and you probably should print out the @row array to verify that it holds the values you expect.

Does it hang when you change it to this:


Code
$sth = $dbh->prepare("select v2 from test where v1=?"); 
$sth->execute(1);


Why are you using DBD::mysqlPP instead of DBD::mysql? DBD::mysql would be more efficient and it's possible that the problem you're experiencing is due to DBD::mysqlPP


FishMonger
Veteran / Moderator

Dec 6, 2008, 10:33 AM


Views: 11437
Re: [jgraeve] perl script hangs on query with where clause

Try changing:
$dbh->do(qq{insert test(v2,v3,v4,v5) values("a", "b", "c", "d")});

to:
$dbh->do(qq{insert test(v1,v2,v3,v4,v5) values(undef, "a", "b", "c", "d")});


jgraeve
New User

Dec 6, 2008, 10:51 AM


Views: 11435
Re: [FishMonger] perl script hangs on query with where clause

I debugged the script and it hangs on execute statement for the query with the where clause.

I tried with the placeholder => same result

I don't exactly know why I use mysqlPP instead of mysql. I asked myself the same question. I just followed some examples. I tried
with
my $drh = DBI->install_driver("mysql");
but this doesn't change anything


jgraeve
New User

Dec 6, 2008, 10:53 AM


Views: 11433
Re: [FishMonger] perl script hangs on query with where clause

$dbh->do(qq{insert test(v1,v2,v3,v4,v5) values(undef, "a", "b", "c", "d")});
results in
C:\Perl\eg>test.pl
Drop table test...done

create table test...done

DBD::mysqlPP::db do failed: #42S22Unknown column 'undef' in 'field list' at C:\Perl\eg\test.pl line 31.
DBD::mysqlPP::db do failed: #42S22Unknown column 'undef' in 'field list' at C:\Perl\eg\test.pl line 31.
Inserting row...

and then the script exits.

My original insert statement worked fine


jgraeve
New User

Dec 6, 2008, 10:56 AM


Views: 11432
Re: [jgraeve] perl script hangs on query with where clause

actually the problem seems to be the autoincrement column

If I change the column in the where clause to v2 instead of v1, the query runs fine.

Any ideas?


FishMonger
Veteran / Moderator

Dec 6, 2008, 11:08 AM


Views: 11431
Re: [jgraeve] perl script hangs on query with where clause

What is the value returned for v1 from this select?

$sth = $dbh->prepare("select * from test limit 1");

If it's not 1 then you may want to alter the create statement.

v1 int auto_increment primary key not null


(This post was edited by FishMonger on Dec 6, 2008, 11:09 AM)


FishMonger
Veteran / Moderator

Dec 6, 2008, 11:39 AM


Views: 11428
Re: [jgraeve] perl script hangs on query with where clause

The DBI->install_driver statement is not needed since you're already specifying the driver in the connect statement.