Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

error calling MySQL from Perl

by mooseboy (Pilgrim)
on Mar 16, 2003 at 18:28 UTC ( #243501=perlquestion: print w/replies, xml ) Need Help??

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";

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.

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

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";

    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.

    _ _ _ _ (_|| | |(_|>< _|

      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.

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";
    --- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';

      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

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!
Re: error calling MySQL from Perl
by zby (Vicar) on Mar 16, 2003 at 18:38 UTC
    This is an SQL question - but anyway. The subselect should be in parenthesis like this:
    destination = (SELECT destination FROM checkin ... )/
    I don't know why there is that qq/ perhaps it was pasted from some kind of reference but it should not be there.

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

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

    I've found that an easy way to at least get started in the right direction for problems like this it to print out the query or statement to stdout. Then cut and paste it onto the command line of mysql. Often after doing this it's more obvious what I'm doing wrong.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://243501]
Approved by data64
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (2)
As of 2021-06-18 02:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (87 votes). Check out past polls.

    Notices?