error calling MySQL from Perl

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

Fellow monks,

Having decided I need to learn something about Perl's database capabilities, I was just re-reading chapter 13 of Simon Cozens' Beginning Perl (the only book I have that touches on the subject) and have gotten most of the code examples to work, but there's one script that keeps giving me a MySQL error that I just can't figure out. Here it is:

#!/usr/bin/perl
use warnings;
use strict;
use DBI;

my ($dbh, $rows);

$dbh = DBI->connect('dbi:mysql:test', 'root', '') ||
   die "Error opening database: $DBI::errstr\n";

$rows = $dbh->do("UPDATE checkin
        SET    destination =
        qq/SELECT destination
        FROM checkin
        WHERE firstname='Henry' AND lastname='Rollins'/
        WHERE  firstname='Bill' AND lastname='Gates'
") || die "Couldn't insert record : $DBI::errstr";

print "$rows row(s) added to checkin";
$dbh->disconnect || die "Failed to disconnect\n";
[download]

The error I get is as follows:

DBD::mysql::db do failed: You have an error in your SQL syntax near 'S
+ELECT destination
        FROM checkin
        WHERE firstname='Henry' AND last' at line 3 at ./broken line 1
+1.
Couldn't insert record : You have an error in your SQL syntax near 'SE
+LECT destination
        FROM checkin
        WHERE firstname='Henry' AND last' at line 3 at ./broken line 1
+1.
[download]

I've spent ages ploughing through the MySQL docs, but as these are my first wobbly steps in relational databases, I'd be grateful for any assistance.

TIA, mooseboy

Comment on error calling MySQL from Perl Select or Download Code

Replies are listed 'Best First'.

Re: error calling MySQL from Perl
by gmax (Abbot) on Mar 16, 2003 at 19:45 UTC

Apart from the syntax problem, MySQL versions earlier than 4.1 (which is far from stable at the moment) do not support subqueries.

Here's a workaround to such limitation.

#!/usr/bin/perl -w
use strict;
use DBI;

my ($dbh, $rows);

$dbh = DBI->connect('dbi:mysql:test', 
    'myusername',
    'mypassword',
    {RaiseError=>1}) ||
   die "Error opening database: $DBI::errstr\n";

#
# First, get destination from your table
#
my ($destination) = @{$dbh->selectcol_arrayref(qq{
     SELECT destination
     FROM checkin
     WHERE firstname='Henry' AND lastname='Rollins'}
     )} 
#    || die "destination not found\n"; # wrong 
     or die "destination not found\n"; # correct
#
# Notice that the script fails if destination is not found.
# 

# 
# then, use $destination to update the table
#
$rows = $dbh->do(qq{UPDATE checkin
        SET    destination = '$destination'
        WHERE  firstname='Bill' AND lastname='Gates'})
        || die "Couldn't update record : $DBI::errstr";

print "$rows row(s) updated in checkin";
$dbh->disconnect || die "Failed to disconnect\n";
[download]

A few comments:

You are using UPDATE and then printing "rows added."
Be aware that the UPDATE statement modifies an existing record. It does not add rows.
What you are doing with your SQL statements is taking one column (destination) from a row identified by name and surname, and then inserting such column into another record identified by another name/surname pair.
Make sure that this is really what you want to do.
You are using a MySQL installation with "root" user and no password.
This is a huge security risk. Please fix it as fast as you can.

update - Fixed a small bug in the script.

 _  _ _  _  
(_|| | |(_|><
 _|
[download]

[reply]
[d/l]
[select]

Re: Re: error calling MySQL from Perl

by mooseboy (Pilgrim) on Mar 16, 2003 at 20:23 UTC

Thanks for the workaround and the tips -- I tested it but now Bill is flying off to the mysterious destination "1"?! I only installed MySQL yesterday and got so engrossed I forgot to take the most elementary security precautions. Duh!

Update: tested gmax's code again incorporating small bugfix -- now works perfectly! Looks like I'll have to fork out for a copy of O'Reilly's Programming the Perl DBI. Cheers to all monks for their helpful suggestions.

[reply]

Re: error calling MySQL from Perl
by pfaut (Priest) on Mar 16, 2003 at 18:38 UTC

I think the qq// needs to be replaced with parenthesis. That's inside the quoted string so perl isn't going to do anything special with it. What's trying to happen there is a subselect which your version of MySQL may not support unless it is very recent. The subselect should be in parethesis. Try the following.

$rows = $dbh->do("UPDATE checkin
        SET    destination =
        (SELECT destination
        FROM checkin
        WHERE firstname='Henry' AND lastname='Rollins')
        WHERE  firstname='Bill' AND lastname='Gates'
") || die "Couldn't insert record : $DBI::errstr";
[download]

---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';
[download]

[reply]
[d/l]
[select]

Re: Re: error calling MySQL from Perl

by mooseboy (Pilgrim) on Mar 16, 2003 at 19:18 UTC

Thanks, I've tried replacing the qq/ with parens , but I still get exactly the same error -- maybe because my version of MySQL doesn't support subselect? Running mysqladmin version gives this:

mysqladmin  Ver 8.23 Distrib 3.23.48, for suse-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software
+,
and you are welcome to modify and redistribute it under the GPL licens
+e
Server version     3.23.48-Max-log
Protocol version   10
Connection      Localhost via UNIX socket
UNIX socket     /var/lib/mysql/mysql.sock
Uptime:         21 hours 24 min 28 sec
[download]

[reply]
[d/l]
[select]

Re: error calling MySQL from Perl
by jasonk (Parson) on Mar 16, 2003 at 18:35 UTC

qq// is a perl quoting mechanism, not an SQL one, so your database has no idea what to do with it. Arguments passed to do() or prepare() need to be valid SQL.

We're not surrounded, we're in a target-rich environment!

[reply]

Re: error calling MySQL from Perl
by zby (Vicar) on Mar 16, 2003 at 18:38 UTC

destination =
        (SELECT destination
        FROM checkin
        ...
        )/
[download]

qq/

Beside that I'm not sure if in the current MySQL there are subselect supported.

[reply]
[d/l]
[select]

Re: error calling MySQL from Perl
by drake50 (Pilgrim) on Mar 16, 2003 at 19:21 UTC

[reply]


We don't bite newbies here... much
	PerlMonks