Professional Web Applications Themes

how to return all rows selected in subroutine? - PERL Beginners

Howdy: I am trying to return an array of all records in the subroutine below. I want to hold all of that info in one location so that I can use it (each row and each column of said row) later in a program. In the $sql part, I had the 'selectrow_array'; as was noted to me in another email, it returns ONE row in the array. But I want to select ALL of the rows in the query. So, I got rid of that and just set up the sql/prepare/execute part. According to the DBI book, fetchall_arrayref can be ...

  1. #1

    Default how to return all rows selected in subroutine?

    Howdy:

    I am trying to return an array of
    all records in the subroutine below.

    I want to hold all of that info
    in one location so that I can use
    it (each row and each column of said
    row) later in a program.

    In the $sql part, I had the 'selectrow_array';
    as was noted to me in another email, it returns
    ONE row in the array. But I want to select
    ALL of the rows in the query. So, I got rid
    of that and just set up the sql/prepare/execute
    part.

    According to the DBI book, fetchall_arrayref
    can be used to fetch all the data to be returned.
    So I thought I could just:

    *do my query
    *prep and check the query
    *execute
    *create an empty array
    *push each row returned from fetchall_array in
    to the array
    *call the sub routine and do something silly
    like get the 2nd records and 3rd column and
    use it

    [snippet of my broken code]

    sub getcols {
    my ($table)=_;
    my $sql=q(
    select
    column_name,
    nullable,
    column_id,
    data_type
    from
    all_tab_columns
    where
    lower(table_name)='$table'
    and
    column_id > 0
    );

    my $sth=$dbh->prepare($sql);
    sth->execute();

    my rows;

    while (my ($col_name, $nullable, $col_id,
    $data_type)=$sql->fetchrow_array()) {
    push (rows, $sql);
    }

    [/snippet of my broken code]


    On the other hand, there is a program that I am
    trying to imitate that DOES this very thing:

    [snip of working code]

    sub getcols {
    my($table)=_;
    return doquery('find rows',"
    SELECT a1.attname, a1.attnotnull, a1.attnum, t.typname,
    a1.attlen, a1.atttypmod, c1.relkind
    FROM pg_class c1,
    pg_attribute a1,
    pg_type t
    WHERE c1.relname='$table'
    and a1.attnum > 0
    and a1.attrelid = c1.oid
    and a1.atttypid = t.oid
    ORDER BY upper(attname);
    ");

    }
    #--------------- GET THE TARGET TABLE'S VARIABLES
    $target=&getcols($opt_t);
    if ( $target->ntuples==0 ) {
    print STDERR "Target table $opt_t not found\n";
    exit 1;
    }

    print "resultstatus=".$result_error{$target->resultStatus}."\n".
    "n= ".$target->ntuples."\n" if $debug;

    #--------------- DOES THE SOURCE HAVE THE NECESSARY VARS?
    while ( trow=$target->fetchrow ) {
    print "-- $trow[0]\n" if $debug;
    $view=1 if $srow[6] eq 'v';
    if ($trow[0] eq 'user') {
    $cols[$trow[2]-1]=q!'ADMIN'!;
    }
    elsif ($trow[0] eq 'updated') {
    $cols[$trow[2]-1]=q!'now'::datetime!;
    $cols[$trow[2]-1]=q!'now'::datetime!;
    }
    else {
    print "whatever\n";
    }
    [/snip of working code]

    I know this is getting tired, but I would appreciate it
    if someone could tell me why the 'broken code' will only
    pull back one row and not hold anything into an array to
    be used later.

    TIA!

    -X

    Shaunn Johnson Guest

  2. #2

    Default RE: how to return all rows selected in subroutine?

    I'll skip over most of your mail, and go right to the heart of the matter...
    > I am trying to return an array of
    > all records in the subroutine below.
    #
    # ...WARNING: UNTESTED CODE AHEAD...
    #

    sub getcols {
    # I prefer this over the ($table) syntax...
    # but it has the same end result
    my $table = shift;

    # this is the array or rows returned.
    my return_rows;

    # never use Perl variables in your queries, it
    # opens you up to "SQL Injection" attacks.
    # Do a query on Google for more info about
    # what this is.
    my $sql = 'select column_name, nullable,
    column_id, data_type
    from all_tab_columns
    where lower(table_name) = ?
    and column_id > 0';

    # prepare with the placeholder, and pass
    # $table in the execute() statement.
    my $sth = $dbh->prepare($sql);
    $sth->execute($table);

    # this loops over each record returned,
    # and pushes a REFERENCE to the array on
    # to the result array. This is essentially
    # a multi-dimentional array if you do not
    # understand referenced.
    while (my row = $sth->fetchrow_array()) {
    push return_rows, \row;
    }

    return return_rows;
    );


    # call the method like so...
    my records = getcold('my_table_name);

    # print first record, first field
    # (the column_name column). the array
    # is zero-based, so [0] is the first item.
    print $records[0][0];

    # print second record, fourth field
    print $records[1][3];

    ....etc...

    See Also:
    perldoc perllol
    perldoc perlreftut


    Rob



    -----Original Message-----
    From: Johnson, Shaunn [mailto:SJohnson6bcbsm.com]
    Sent: Friday, August 29, 2003 4:14 PM
    To: [email]beginnersperl.org[/email]
    Subject: how to return all rows selected in subroutine?


    Howdy:

    I am trying to return an array of
    all records in the subroutine below.

    I want to hold all of that info
    in one location so that I can use
    it (each row and each column of said
    row) later in a program.

    In the $sql part, I had the 'selectrow_array';
    as was noted to me in another email, it returns
    ONE row in the array. But I want to select
    ALL of the rows in the query. So, I got rid
    of that and just set up the sql/prepare/execute
    part.

    According to the DBI book, fetchall_arrayref
    can be used to fetch all the data to be returned.
    So I thought I could just:

    *do my query
    *prep and check the query
    *execute
    *create an empty array
    *push each row returned from fetchall_array in
    to the array
    *call the sub routine and do something silly
    like get the 2nd records and 3rd column and
    use it

    [snippet of my broken code]

    sub getcols {
    my ($table)=_;
    my $sql=q(
    select
    column_name,
    nullable,
    column_id,
    data_type
    from
    all_tab_columns
    where
    lower(table_name)='$table'
    and
    column_id > 0
    );

    my $sth=$dbh->prepare($sql);
    sth->execute();

    my rows;

    while (my ($col_name, $nullable, $col_id,
    $data_type)=$sql->fetchrow_array()) {
    push (rows, $sql);
    }

    [/snippet of my broken code]


    On the other hand, there is a program that I am
    trying to imitate that DOES this very thing:

    [snip of working code]

    sub getcols {
    my($table)=_;
    return doquery('find rows',"
    SELECT a1.attname, a1.attnotnull, a1.attnum, t.typname,
    a1.attlen, a1.atttypmod, c1.relkind
    FROM pg_class c1,
    pg_attribute a1,
    pg_type t
    WHERE c1.relname='$table'
    and a1.attnum > 0
    and a1.attrelid = c1.oid
    and a1.atttypid = t.oid
    ORDER BY upper(attname);
    ");

    }
    #--------------- GET THE TARGET TABLE'S VARIABLES
    $target=&getcols($opt_t);
    if ( $target->ntuples==0 ) {
    print STDERR "Target table $opt_t not found\n";
    exit 1;
    }

    print "resultstatus=".$result_error{$target->resultStatus}."\n".
    "n= ".$target->ntuples."\n" if $debug;

    #--------------- DOES THE SOURCE HAVE THE NECESSARY VARS?
    while ( trow=$target->fetchrow ) {
    print "-- $trow[0]\n" if $debug;
    $view=1 if $srow[6] eq 'v';
    if ($trow[0] eq 'user') {
    $cols[$trow[2]-1]=q!'ADMIN'!;
    }
    elsif ($trow[0] eq 'updated') {
    $cols[$trow[2]-1]=q!'now'::datetime!;
    $cols[$trow[2]-1]=q!'now'::datetime!;
    }
    else {
    print "whatever\n";
    }
    [/snip of working code]

    I know this is getting tired, but I would appreciate it
    if someone could tell me why the 'broken code' will only
    pull back one row and not hold anything into an array to
    be used later.

    TIA!

    -X
    Rob Hanson Guest

Similar Threads

  1. Replies: 7
    Last Post: September 2nd, 05:44 AM
  2. Getting selected rows out of an access database
    By badLarry in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 7th, 04:07 PM
  3. Getting a list of ID's from Selected Rows
    By Thomasa Gregg in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: October 21st, 09:03 PM
  4. How can be the return value from a subroutine saved in global array
    By Gautam Chaurasia in forum PERL Beginners
    Replies: 3
    Last Post: February 1st, 11:43 PM
  5. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139