DBD::Mysql Strange Locking error

cormanaz has asked for the wisdom of the Perl Monks concerning the following question:

Good day Monks. I have a small sub that fetches a value from mysql:

sub getsqlvalue {
    my @results = ();
    my ($dbh,$sqlstatement)= @_;
    my $sth = $dbh->prepare($sqlstatement);
    my @row;
    $sth->execute || die "Could not execute MySQL statement: $sqlstate
+ment";
    while (@row=$sth->fetchrow_array) { push(@results, [ @row ]); }
    $sth->finish();
    return $results[0][0];
}
[download]

I am using this in a program that is accessing a MyISAM table in MySQL. I issue a write lock on several tables in the same schema, but not this table named "vocabulary." Then when I try to do a SELECT on vocabulary I get the error "DBD::mysql::st execute failed: Table 'vocabulary' was not locked with LOCK TABLES" on the $sth->execute line in the above sub. I have tested the SQL statement being executed and it is valid. I have no clue what the problem is, since it is correct that I have not locked that table! Anyone know what's going on?

Many TIA

Steve UPDATE I found the answer in the MySQL docs for lock tables: "When you use LOCK TABLES, you must lock all tables that you are going to use in your queries." So the problem was indeed that I had not locked the table.

Comment on DBD::Mysql Strange Locking error Download Code

Replies are listed 'Best First'.
Re: DBD::Mysql Strange Locking error by davidrw (Prior) on May 05, 2006 at 20:39 UTC
Why loop through all the results when you only want the first row? (see DBI for method descriptions) `sub getsqlvalue { my ($dbh,$sqlstatement)= @_; my ($value) = $dbh->selectrow_array($sqlstatement) or die "Could n +ot execute MySQL statement: '$sqlstatement' " . $dbh->errstr; return $value; }` [download] (i know, doesn't answer primary question, but goes to efficiency)	[reply] [d/l]
Re: DBD::Mysql Strange Locking error by Herkum (Parson) on May 05, 2006 at 19:55 UTC
We cannot see the SQL statement that you are passing, so how can know whether it is not an error? For all I know, the table needs to be locked to execute your statement!	[reply]
Re^2: DBD::Mysql Strange Locking error by cormanaz (Deacon) on May 05, 2006 at 22:00 UTC
It's a plain-old select statement, which is why I didn't post it, but here you go `$wordid = getsqlvalue($dbh,"select id from words.vocabulary where toke +n like '$searchword'");` [download]	[reply] [d/l]