CODEWITHSUNDEEP
mysqlperl
Leorudolph
Leorudolph

Reputation: 11

Perl Script broken after upgrade to 5.8.8

We have a old application that works fine using Perl-5.8.5.8 but does not work any more on Perl-5.8.8, I'm sure its a simple change but I can't find it.

DBD::mysql::st execute failed: Unknown column 'stores.id' in 'on clause' at /var/www/cgi-bin/app/list.cgi line 70

This is the context of line 70

my $sth=$dbh->prepare($sql);
$sth->execute();   ################Line 70

Here is the complete program

#!/usr/bin/perl
do "share.pl";

sub get_propertytable {
        my ($id, $devicename) = @_;
        my $content = "<table><tr><th colspan=2>$devicename $id</th></tr>";
        my $sql = "SELECT propertytypes.propertyname, deviceproperties.propertyvalue FROM deviceproperties INNER JOIN propertytypes ON deviceproperties.propertytype = propertytyp$
        my $sthq = $dbh->prepare($sql);
        $sthq->execute();
        $sthq->bind_columns(\$propname, \$propvalue);
        while ($sthq->fetch()){
                if ($propname !~ /"Connected"/){
                $content .= "<tr><td>$propname</td><td>: $propvalue</td></tr>";}
        }
    $content .= "</table>";
}

sub get_storetable {
        my $id = @_[0];
        my $content = "<table><tr><th colspan=2>Store $id</th></tr>";
        my $sql = "SELECT storename,address,postcode,phoneno FROM stores WHERE id=$id";
        my $sthq = $dbh->prepare($sql);
        $sthq->execute();
        my ($storename, $address, $postcode, $phoneno);
        $sthq->bind_columns(\$storename, \$address, \$postcode, \$phoneno);
        $sthq->fetch();    
        $address =~ s/,/<br>/g;
        my $content = "<table><tr><th colspan=2>$storename</th></tr>";
        $content .= "<tr><td valign=top>Address :</td><td>$address</td></tr>";
        $content .= "<tr><td>Post Code :</td><td>$postcode</td></tr>";
        $content .= "<tr><td>Phone No :</td><td>$phoneno</td></tr>";
        $content .= "</table>";
}

sub get_franchisetable {
        my $id = @_[0];
        if ($id){
        my $content = "<table><tr><th colspan=2>Franchise $id</th></tr>";
        my $sql = "SELECT contactname,company,contactno,email FROM Franchisees WHERE id=$id";
        my $sthq = $dbh->prepare($sql);
        $sthq->execute();
        my ($contactname, $company, $contactno, $email);
        $sthq->bind_columns(\$contactname, \$company, \$contactno, \$email);
        $sthq->fetch();
        $address =~ s/,/<br>/g;
        my $content = "<table><tr><th colspan=2>$company</th></tr>";
        $content .= "<tr><td valign=top>Contact Name :</td><td>$contactname</td></tr>";
        $content .= "<tr><td>Contact No :</td><td>$contactno</td></tr>";
        $content .= "<tr><td>Email :</td><td>$email</td></tr>";
        $content .= "</table>";
        }
}

if ( valid_user() == 1 ) {

my $sql = "SELECT id,devicename FROM devicetypes";
my $sth=$dbh->prepare($sql);
$sth->execute();
$sth->bind_columns(\$id, \$devicename);
while ($sth->fetch()){
        $devicetypes[$id] = $devicename;
}
$sth->finish();

my $sql = "SELECT stores.id, stores.storename, devices.id,devices.devicetype,tradingpartner.name,tradingpartner.id
FROM stores,storetradingpartner,tradingpartner
LEFT JOIN devices ON devices.store = stores.id
WHERE stores.id = storetradingpartner.storeid
AND tradingpartner.id = storetradingpartner.partnerid
ORDER BY tradingpartner.name,stores.storename,devices.devicetype,devices.id";
my $sth=$dbh->prepare($sql);
$sth->execute();   ################# Line 70
#$sth->bind_columns(\$storeid, \$store, \$deviceid, \$devicetype, \$tradingpartner, \$partnerid);

print "Content-type: text/html

<body link='#000000' vlink='#000000' alink='#000000'>
<script type=\"text/javascript\" src=\"/clit/wz_tooltip.js\" language='javascript'>
</script>
<SCRIPT SRC=\"/clit/getpropertytable.js\">
</SCRIPT>

<script language='javascript' type=\"text/javascript\">
function hidediv(thediv) {
        if (document.getElementById(thediv).style.display == 'none'){
                document.getElementById(thediv).style.display = 'block';
        } else {
                document.getElementById(thediv).style.display = 'none';
        }
    return 0;
}


//</script>
<font face=arial>
<h1>Asset List</h1>
<a href=index.cgi>Back to ticket list</a>
<br><br>

Upvotes: 1

Views: 203

Answers (1)

ysth
ysth

Reputation: 98398

I'm suspecting you have upgraded to a newer MySQL server version, too.

Queries like this:

FROM stores, storetradingpartner, tradingpartner
LEFT JOIN devices ON devices.store = stores.id

are parsed differently, I believe, as of MySQL 4.0 5.0.12. You should avoid mixing implicit joins (e.g. table, table2, table3) with explicit joins (like LEFT JOIN).

Try this:

FROM stores
INNER JOIN storetradingpartner ON stores.id = storetradingpartner.storeid
INNER JOIN tradingpartner ON tradingpartner.id = storetradingpartner.partnerid
LEFT JOIN devices ON devices.store = stores.id
ORDER BY...

http://dev.mysql.com/doc/refman/5.5/en/join.html:

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Note that there were other join changes in 5.0.1 and 5.0.12 to bring MySQL more in line with the SQL standard, some of which will result in differences in rows returned, not just produce errors as in this case.

Upvotes: 9

Related Questions