Hi I hope you can shed some light on something that has vexed me for a while.  My problem is with Perl's interpretation of SQL with clauses with numerical conditions.

This works fine:

my $codeval="KAZ";
 $sth = $dbh->prepare(qq{
   select pbid From snames where code =?
 });
 $sth->execute($codeval);
my $field = $sth->fetchrow_array();
$sth->finish();

While this locks:

my $pholder=50;
 $sth = $dbh->prepare(qq{
   select code from snames where pbid =?
 });
 $sth->execute($pholder);
my $field = $sth->fetchrow_array();
$sth->finish();


Any help would be really appreciated.
Patrick

Background:
mysql> describe snames;

| Field     | Type        | Null | Key | Default | Extra          |
| pbid      | int(11)     | NO   | PRI | NULL    | auto_increment
| code      | varchar(20) | YES  |     | NULL    |                |
| name      | varchar(56) | YES  |     | NULL    |                |
| live      | tinyint(1)  | YES  |     | NULL    |                |
| startdate | date        | YES  |     | NULL    |                |
| enddate   | date        | YES  |     | NULL    |                |

mysql> select code from snames where pbid=50;
| code |
| KAZ  |

mysql> select pbid from snames where code="KAZ";

| pbid |
|   50 |

PerlVersion is  ActivePerl-5.10.0.1002-MSWin32-x86-283697
MySQL is 5.0.51a-community-nt
OS Vista Home


read the DBI and or DBD::Mysql documentation about checking for errors returned by the database.  

on 06/12/08 at 05:20:59, KevinAD wrote:

read the DBI and or DBD::Mysql documentation about checking for errors returned by the database.

Hey - Kevin - I'm not sure if this IS an error condition to be checked for - our poster reports that it LOCKS but has no loop in his code.

I would be interested to know if the problem (lock up) lies in the prepare, the execute of the fetch - pbayliss, can you add in some extra prints to find out, please ... and also check DBI::errstr at each point?   Thanks

on 06/12/08 at 07:46:19, Graham Ellis wrote:

Hey - Kevin - I'm not sure if this IS an error condition to be checked for - our poster reports that it LOCKS but has no loop in his code. I would be interested to know if the problem (lock up) lies in the prepare, the execute of the fetch - pbayliss, can you add in some extra prints to find out, please ... and also check DBI::errstr at each point?   Thanks

I'm not sure either. But he has to start somewhere with the debugging and that seems like a place to start.  Hopefully he comes back to read your suggestion.

Hey guys - thanks for your interest.

On the debugging - the lock occurs on $sth->execute($pholder); line.

I need to spend some time looking at it (outside work) but have made some sort of progress.  What does work is removing the placeholder in the sql AND respecifying the mySQL datatype as varchar (from tinyint)- need to retest to see if the datatype change is relevant.

I will be looking at how to check and implement the DBI::errstr over the weekend - as you probably gathered I am quite new to this........

---