DBD::Google " SELECT * " does not return URL.

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

I just installed DBD::Google which is really neat actually. Anyway " SELECT * " does not return the URL column while " SELECT *, URL " does. Even with the query that does return the URL the hash key is lowercase and the column in the SQL is uppercase. Why does the URL column does not show up unless it is explicitly asked for? Below is the code I am using and what I am getting back for results.

When I query:

    SELECT *, URL FROM google WHERE q = "perl monks" LIMIT 1
[download]

I see the URL for the search result like:

$VAR1 = {
    'cached_size' => '',
    'snippet' => '',
    '_u_r_l' => '',
    'summary' => 'Contains tutorials, discussion forums, <b>Perl</b> p
+oetry, obfuscated code, and a large code repository.',
    'directory_category' => '',
    'url' => 'http://www.perlmonks.org/',
    'host_name' => '',
    'title' => '',
    'directory_title' => ''
    };
[download]

When I query:

    SELECT * FROM google WHERE q = "perl monks" LIMIT 1
[download]

I get this result back:

$VAR1 = {
    'cached_size' => '',
    'snippet' => '',
    '_u_r_l' => '',
    'summary' => 'Contains tutorials, discussion forums, <b>Perl</b> p
+oetry, obfuscated code, and a large code repository.',
    'directory_category' => '',
    'host_name' => '',
    'title' => '',
    'directory_title' => ''
    };
[download]

And of course the whole program:

use DBI;
use strict;
use Data::Dumper;

my $KEY = "See http://www.google.com/apis/ to get a key.";

my $dbh = DBI->connect("dbi:Google:", $KEY)
    or die("Can't connect to DB: " . $!);

my $sth = $dbh->prepare(qq[
    SELECT *, URL FROM google WHERE q = "perl monks" LIMIT 1
])
    or die("Can't prepare query: " . $!);

$sth->execute()
    or die("Can't execute query: " . $!);

while (my $r = $sth->fetchrow_hashref) {
    print Dumper($r), "\n";
}
[download]

Comment on DBD::Google " SELECT * " does not return URL. Select or Download Code

Replies are listed 'Best First'.
Re: DBD::Google " SELECT * " does not return URL. by dakkar (Hermit) on Feb 05, 2005 at 15:12 UTC
It's a funny bug in DBD::Google. If you look at the parser you'll see that `@default_columns` contains `URL`, but a few lines later it gets changed in this `for` loop: `for my $dc (@default_columns) { $dc =~ s/([A-Z])/_\l$1/g; $allowed_columns{$dc} = 1; }` [download] so that `URL` becomes `_u_r_l`. I am not going to test it, but I'd change the loop to: `for my $dc (@default_columns) { my $t; ($t=$dc) =~ s/([A-Z])/_\l$1/g; $allowed_columns{$t} = 1; }` [download] -- dakkar - Mobilis in mobile Most of my code is tested... Perl is strongly typed, it just has very few types (Dan)	[reply] [d/l] [select]
Re^2: DBD::Google " SELECT * " does not return URL. by superfrink (Curate) on Feb 05, 2005 at 20:35 UTC
Ah, interesting. So the loop is there to change "cachedSize" to "cached_size" but because "URL" is all uppercase it comes out "_u_r_l". The recommended change does indeed cause the result to include a key named "URL" which points to the URL string. Changeing the default column list to include "url" instead of "URL" also seems to work and the result includes a key with the lowercase name "url". I have not read google's API or most of the DBD::Google code so I'm not sure if there are any other side effects. I think I'll send the author an email and see what he thinks is the best fix. (Maybe just specifying the URL in the query.) Thanks, Chad	[reply]
Re: DBD::Google " SELECT * " does not return URL. by superfrink (Curate) on Jan 29, 2009 at 18:58 UTC
It is a few years after I made the original post but this bug bit me again as I was porting code to a new machine. For future reference the change I made to the code was: `# diff -u parser.pm.orig parser.pm --- parser.pm.orig 2004-03-04 18:48:54.000000000 -0500 +++ parser.pm 2009-01-29 13:56:06.000000000 -0500 @@ -31,7 +31,7 @@ $FUNC_RE = qr/$FIELD_RE(?:::$FIELD_RE)(?:[-]>$FIELD_RE)?/; # meth +ods? $FIELD_RE = qr/$FIELD_RE*/; -my @default_columns = sort qw( title URL snippet summary +my @default_columns = sort qw( title url snippet summary cachedSize directoryTitle hostName directoryCategory );` [download]	[reply] [d/l]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks