How to get individual fields into variables using DBI?

The code fragment below will print all rows in the
table mytable. If there are say 3 fields in mytable,
field1, field2, and field3, how would I get all of
the values into each field? That is, break up
each row by field into variables?

-Thanks



my $dbh = DBI->connect(
"dbi:Pg:dbname=$dbname;host=$host;port=$port",
$username, ' '
) or die $!;

my $sth = $dbh->prepare("select * from mytable");

$sth->execute();

while ( @row = $sth->fetchrow_array ) {
print "@row\n";
}

Re: How to get individual fields into variables using DBI?

In article ,
Fred wrote:

> The code fragment below will print all rows in the
> table mytable. If there are say 3 fields in mytable,
> field1, field2, and field3, how would I get all of
> the values into each field? That is, break up
> each row by field into variables?

Errmmm, you _do_ know what that "@" symbol in front of @row means, right?

Have you tried something like:

print "col0 = $row[0], col1 = $row[1]\n";

inside that while loop?

big

>
> -Thanks
>
>
>
> my $dbh = DBI->connect(
> "dbi:Pg:dbname=$dbname;host=$host;port=$port",
> $username, ' '
> ) or die $!;
>
> my $sth = $dbh->prepare("select * from mytable");
>
> $sth->execute();
>
> while ( @row = $sth->fetchrow_array ) {
> print "@row\n";
> }

--
"Everything you love, everything meaningful with depth and history,
all passionate authentic experiences will be appropriated, mishandled,
watered down, cheapened, repackaged, marketed and sold to the people
you hate." Mr Jalopy quoting Hooptyrides (on jalopyjunktown.com)

Re: How to get individual fields into variables using DBI?

On Wed, 21 Feb 2007 18:09:53 +1100, Iain Chalmers wrote:

> In article ,
> Fred wrote:
>
>> The code fragment below will print all rows in the
>> table mytable. If there are say 3 fields in mytable,
>> field1, field2, and field3, how would I get all of
>> the values into each field? That is, break up
>> each row by field into variables?
>
> Errmmm, you _do_ know what that "@" symbol in front of @row means, right?
>
> Have you tried something like:
>
> print "col0 = $row[0], col1 = $row[1]\n";
>
> inside that while loop?
>
> big


Thanks. I was thinking that each element of @row was an
entire row like:

$row[0] = "col1_here col2_here col3_here"
$row[1] = "col1_here col2_here col3_here"

Re: How to get individual fields into variables using DBI?

On Thu, 22 Feb 2007 08:40:58 -0500 Fred wrote:

F> On Wed, 21 Feb 2007 18:09:53 +1100, Iain Chalmers wrote:
>> In article ,
>> Fred wrote:
>>
>>> The code fragment below will print all rows in the
>>> table mytable. If there are say 3 fields in mytable,
>>> field1, field2, and field3, how would I get all of
>>> the values into each field? That is, break up
>>> each row by field into variables?
>>
>> Errmmm, you _do_ know what that "@" symbol in front of @row means, right?
>>
>> Have you tried something like:
>>
>> print "col0 = $row[0], col1 = $row[1]\n";
>>
>> inside that while loop?
>>
>> big

F> Thanks. I was thinking that each element of @row was an
F> entire row like:

F> $row[0] = "col1_here col2_here col3_here"
F> $row[1] = "col1_here col2_here col3_here"

If you are just getting started with DBI, I would suggest looking at
Rose::DB::Object. The Loader module will automatically generate all
the code for you (MySQL, SQLite, Postgres, and I'm working on Oracle), so
you can just say

my @results = # your query here

foreach my $item (@results)
{
printf "F1 %s F2 %s F3 %s\n", $item->col1(), $item->col2(), $item->col3();
}

Install Rose::DB::Object from CPAN:

perl -MCPAN -eshell'install Rose::DB::Object'

and then look at the simple docs for setting up Rose::DB and
Rose::DB::Object::Loader to write your code for you.

Even if you can't use the Loader to write the code automatically, you
can still use RDBO, just set up the tables yourself, but it's
ridiculously easy to do it with Loader when it's available.

I'm not the author of RDBO, but I like it a *lot*.

Ted