CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

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

 



jgraeve
New User

Dec 6, 2008, 9:48 AM

Post #1 of 8 (4289 views)
perl script hangs on query with where clause Can't Post

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

Post #2 of 8 (4284 views)
Re: [jgraeve] perl script hangs on query with where clause [In reply to] Can't Post

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

Post #3 of 8 (4283 views)
Re: [jgraeve] perl script hangs on query with where clause [In reply to] Can't Post

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

Post #4 of 8 (4281 views)
Re: [FishMonger] perl script hangs on query with where clause [In reply to] Can't Post

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

Post #5 of 8 (4279 views)
Re: [FishMonger] perl script hangs on query with where clause [In reply to] Can't Post

$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

Post #6 of 8 (4278 views)
Re: [jgraeve] perl script hangs on query with where clause [In reply to] Can't Post

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

Post #7 of 8 (4277 views)
Re: [jgraeve] perl script hangs on query with where clause [In reply to] Can't Post

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

Post #8 of 8 (4274 views)
Re: [jgraeve] perl script hangs on query with where clause [In reply to] Can't Post

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

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives