How to get individual fields into variables using DBI?

[Fred]

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(
"dbig: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";
}

 

[Iain Chalmers]

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

 

[Fred]

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"

 

[Ted Zlatanov]

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:B::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:B::Object from CPAN:

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

and then look at the simple docs for setting up Rose:B and
Rose:B::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