How to get individual fields into variables using DBI?

Discussion in 'Perl Misc' started by Fred, Feb 21, 2007.

  1. Fred

    Fred Guest

    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";
    }
    Fred, Feb 21, 2007
    #1
    1. Advertising

  2. 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)
    Iain Chalmers, Feb 21, 2007
    #2
    1. Advertising

  3. Fred

    Fred Guest

    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"
    Fred, Feb 22, 2007
    #3
  4. Fred

    Ted Zlatanov Guest

    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
    Ted Zlatanov, Feb 22, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    508
    Juha Laiho
    Jul 22, 2004
  2. Replies:
    9
    Views:
    914
  3. Krishna
    Replies:
    1
    Views:
    264
    Timothy Grant
    Aug 7, 2008
  4. Darren Smith
    Replies:
    2
    Views:
    146
    Darren Smith
    Jan 28, 2004
  5. Jerome Hauss
    Replies:
    0
    Views:
    157
    Jerome Hauss
    Oct 13, 2004
Loading...

Share This Page