Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
20.9.2014 - We have just updated our course layouts and descriptions and added our 2015 schedule.

Perl and mySQL select probmes

Posted by pbayliss (pbayliss), 11 June 2008
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


Posted by KevinAD (KevinAD), 12 June 2008
read the DBI and or DBD::Mysql documentation about checking for errors returned by the database.  

Posted by admin (Graham Ellis), 12 June 2008
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

Posted by KevinAD (KevinAD), 12 June 2008
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.

Posted by pbayliss (pbayliss), 13 June 2008
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........



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho