foolish/simple sql question

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

Hello all, Well, I have a perl script I am writng which is supposed to run like this:

status 7005

Which should return something like this:

st8a: 22
st8b: 2
[download]

And I have this code thusfar:

#! /usr/bin/perl

use strict;
use DBI;

die "Usage: status <id> [<id> ...]\n" unless $#ARGV >= 0;

my $id = @ARGV[0];

my $dbh = DBI->connect("dbi:Pg:dbname=eppie;host=localhost;port=5432")
  || die "Error connecting to database: $DBI::errstr\n";

my $sth = $dbh->prepare(qq{
                SELECT st8a, st8b FROM sample WHERE id=$id});

$sth->execute()
  || die "Error executing query: " . $dbh->errstr . "\n";

$sth->finish();
$dbh->disconnect();
[download]

My question now is, and this is embarassing -- how do I, you know, get that data that the SQL command is bringin up and put it into variables that I can use? Am I even doing all of this properly? Thanks, you guys are the best.

Comment on foolish/simple sql question Select or Download Code

Replies are listed 'Best First'.
Re: foolish/simple sql question by eric256 (Parson) on Jun 01, 2004 at 19:18 UTC
The documents at DBI are a huge help. They have lots of samples and explanations. You should also use placeholders instead of putting the data straight into your query. (hint: look for fetch, or select in the names of the methods) ___________ Eric Hodges	[reply]
Re: foolish/simple sql question by Joost (Canon) on Jun 01, 2004 at 19:24 UTC
Please use <code> tags instead of <pre>. Anyway, something like: `my $sth = $dbh->prepare(qq{SELECT st8a, st8b FROM sample WHERE id=$id} +); $sth->execute() \|\| die "Error executing query: " . $dbh->errstr . "\n" +; my ($val1,$val2) = $sth->fechrow_array();` [download] Or even `my ($val1,$val2) = $dbh->selectrow_array("SELECT st8a, st8b FROM sampl +e WHERE id=$id");` [download] Should work. And you should watch what people can put in the $id variable, either make sure it's number, use $dbh->quote() or use placeholders. See the DBI docs. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l] [select]