Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

generating list box form oracle database

by new_2_perl (Acolyte)
on Jun 19, 2001 at 19:46 UTC ( #89672=perlquestion: print w/replies, xml ) Need Help??

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

Hi guys, I have have a perl page that I want to generate an html drop down list box. The data in the list box comes from an oracle database. I am using oraperl to try and do this. Each line of the list box needs to be each line(row) of the data coming from the database table. There are four fields in each line(row) of the database table. My question is what is the best way to go about doing this? I have been trying to play with the fetch command like this:
sub make_list { $sql = "select * \n"; $sql .= "from e1_attrib_type \n"; $cur = &ora_open($lda, $sql); while ( (@d) = &ora_fetch($cur)) { @ln[$i] = $d[$i]; } &ora_close($cur); return( @ln ); }
but obviously this is not right :-) I can't sem to think of the right way to go about generating this list box. Thanks for your help guys..

Replies are listed 'Best First'.
Re: generating list box form oracle database
by jorg (Friar) on Jun 19, 2001 at 20:01 UTC
    Three tips to set you on your way :
  • use DBI, the standard database interface driver
  • DBD::Oracle, the standard oracle interface library
  • use CGI, the standard web library

    These libraries are all available on CPAN


    "Do or do not, there is no try" -- Yoda
Re: generating list box form oracle database
by mikeB (Friar) on Jun 19, 2001 at 20:05 UTC
    Take a look at the HTML::Template module. It allows you to build a hash containing the list box entries, while keeping your HTML code simple. Here's an excerpt from code using HTML::Template. It builds table rows of check boxes, rather than list box entries, but the code would be similar.

    Sample perl (using DBI):

    while ($sth->fetch) { my %hash = (description => $description, itemNum => $itemNum); push @items, \%hash; } $tmpl->param(items => \@items);

    Corresponding template excerpt:

    <TMPL_LOOP name="items"> <tr> <td> <INPUT id=<TMPL_VAR name="itemNum"> name=<TMPL_VAR name="itemNum"> + type=checkbox> <LABEL for="<TMPL_VAR name="itemNum">"><TMPL_VAR name="description +"></LABEL> </td> </tr> </TMPL_LOOP>
Re: generating list box form oracle database
by kurt1992 (Novice) on Jun 19, 2001 at 21:42 UTC
    Like the monks have said, DBI is the way to go. This is the standard way to connect to a database in Perl, and has been for a while. This example uses DBI to do the same thing with a mysql database, the only difference in using Oracle is that the connection routine would have to change and you would have to use DBD::Oracle. (you have to set some env variables when connecting, ORACLE_HOME, and ORACLE_SID, i believe, but check out the docs).

    Anyway, the get_catnames() routine which returns a reference to an array which is handed over to the popup_menu() function would work just the same in oracle, providing it was working with a valid $dbh.

    #!/usr/bin/perl -Tw use strict; use CGI qw# :standard :form #; use DBI; use DBD::mysql; my $dbh = get_handle('dbname', 'dbuser', 'dbpasswd'); my $listref = get_catnames( \$dbh ); # sending a ref print header; print start_html; print popup_menu( -name=>'categories', -value=>$listref ); $dbh->disconnect; ##### ##### sub get_catnames { my $dbh = shift; my @cats = (); my $statement = "select category_name from mtx_catnames"; my $sta = $$dbh->prepare($statement); # deref this $sta->execute; my $cats = $sta->fetchall_arrayref; for my $row ( @{ $cats } ) { push @cats, $row->[0]; } return \@cats; } sub get_handle { my ($DB, $DBUSER, $DBPASS) = @_; my $dsn = "DBI:mysql:database=$DB;host=localhost"; my $dbh = DBI->connect($dsn, $DBUSER, $DBPASS) or die( qq|cannot connect to database $DB| ); return $dbh; }
(ichimunki) Re: generating list box form oracle database
by ichimunki (Priest) on Jun 19, 2001 at 20:19 UTC
    What kind of errors/results is this giving you?

    update: Ack! I had a great example which was completely wrong, because it was based on a misunderstanding of the ora_fetch() command.

    So now I'll simply be a voice in the chorus: consider DBI and DBD::Oracle modules rather than oraperl. The resulting code will be more portable (even if you don't care about it for this script, your skillset is enhanced by this).
      the sql satement is valid sql. It is the same thing that you worte. e1_attrib_type is the name of the table
        You are right. I'm just not used to seeing SQL with embedded \n's or without the ; at the end. sorry.
Re: generating list box form oracle database
by new_2_perl (Acolyte) on Jun 19, 2001 at 21:55 UTC
    Ok I have it working for one line with this code.
    @ln = &make_list; print"<font size=5>Delete Attribute From Database</font>\n"; print"<form method=post action=/cgi-bin/BXJOH27/>\n +"; print"<select size=10 name=\"attrib_lines\">\n"; print"<option>$ln[0] $ln[1] $ln[2] $ln[3]</option>\n"; print"</select>\n"; print"</form>\n"; sub make_list { $sql = "select * \n"; $sql .= "from e1_attrib_type \n"; my $cur = ora_open( $lda, $sql ); my @d = ora_fetch( $cur ); ora_close( $cur ); return( @d ); }
    But I need the list box to have every line from the database table. How can I modufy this code to make the list box contain every line and not just 1. Thanks
      Having never used oraperl, it's only a guess that ora_fetch() only returns one line of the result set. If there's an ora_fetchall() method, that might work better. Otherwise, you might need to loop:
      my @d; while (my $row = ora_fetch($cur)) { push @d, $row; }

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://89672]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (4)
As of 2021-10-23 08:15 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (87 votes). Check out past polls.