MySQL Forums
Forum List  »  Perl

perl help on nested queries
Posted by: John-Scott Atlakson
Date: August 04, 2006 02:49PM

Hello,
I'm very new to perl (as of last night) and programming in general. I'm trying to 1) pull addresses from a MySQL database, 2)run each address through Geo::Coder::US and 3)then insert the results back into MySQL. So far, I am having success with 1 & 2, and can print the lat/long reults. Now I just need to figure out how to take these values and send them back to MySQL. Here's the code I have so far:

use strict;
use DBI();
use Geo::Coder::US;
Geo::Coder::US->set_db( "geocoder.db" );

my $dbh = DBI->connect("DBI:mysql:database=mydb;host=localhost",
"root", "",
{'RaiseError' => 1});

my $sth = $dbh->prepare("SELECT CONCAT_WS(' ',number,street,',',city,',','NY') AS address FROM properties LIMIT 1");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
my ($match) = Geo::Coder::US->geocode("$ref->{'address'}");
print "$match->{lat},$match->{long}";
}
$sth->finish();

This works perfectly. To get the INSERT step working, I changed the 'while' block to:
while (my $ref = $sth->fetchrow_hashref()) {
my ($match) = Geo::Coder::US->geocode("$ref->{'address'}");
my $sth2 = $dbh->prepare("INSERT INTO properties (latitude,longitude) VALUES (?, ?)");
$sth2->execute($match->{lat},$match{long});
}

But now I get the error: "Global symbol "%match" requires explicit package name..."
I'm not entirely clear what's going on here. I know that the error is returned since I have 'use strict;'. But I don't understand why I can print "$match->{lat}" in the first example but it becomes a global variable (and complains) in the second example. I realize this question is more perl specific than MySQL, but I figured I'd have a decent chance of finding someone here who has successfully done something similar to what I'm attempting. I'm really surprised I made it this far. I appreciate any pointers on how to properly do this.

Options: ReplyQuote


Subject
Written By
Posted
perl help on nested queries
August 04, 2006 02:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.