DBD::Oracle - selectcol_arrayref - Can't seem to get hash from query
System is Solaris, running Perl 5.6.0. it was installed in pkgadd format so I'm not sure what to do about the following:
The only two problems I had building DBD:Oracle were as follows:
Warning: If you have problems you may need to rebuild perl with threading enabled. Warning: If you have problems you may need to rebuild perl with -Uusemymalloc.
Perhaps this explains the problems I'm having, perhaps not. Here's what's going on:
I installed DBI v. 1.38 which went through make -> make test -> make install without complaint. I installed DBI::Oracle 1.14 which gave me the two complaints above.
Now, I have copied the example from http://search.cpan.org/author/TIMB/DBI-1.37/DBI.pm where the work is done by these two lines:
my $ary_ref = $dbh->selectcol_arrayref("select id, name from table", { Columns=>[1,2]
});
my %hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => nameThen I try to pass the hash to the following function:
sub buildPopup {
$n = shift;
%h = @_; return $q->popup_menu(
-name=>$n,
-values=>[sort keys %h],
-labels=>{%h},
-default=>'0'
);}
The above function is called with:
$OwnerPicker = buildPopup("Own", %hash);
And $OwnerPicker is part of the here document that I am printing as the output of my cgi. What should happen is that the following values... here excerpted from a sqlplus session... should be used to generate a popup menu:
SQL> select id, name from ownertab;
ID NAME ---------- -------------------- 0 Choose DA Dave SD Sachin YY Youli
SQL>
Instead, what I am getting is the following in the HTML source:
<select NAME="Own"> <option VALUE="">ARRAY(0x2a1a2c) </select>
The result of the following line:
print "Hash is " . %hash;
is:
"Hash is 1/8"
on the output. I'm pretty sure that there is something wrong with the way I'm handling this hash, but I'm not sure what... I see the following curious output when I have tracing turned on... what really bugs me is the two lines before the DESTRY messages.... it seems that some kind of error is happening here but I can't seem to make it happy:
DBI::db=HASH(0x28e6cc) trace level set to 3 in DBI 1.38-nothread
-> selectcol_arrayref in DBD::_::db for DBD::Oracle::db
(DBI::db=HASH(0x2c6e78)~0x28e6cc 'SELECT id, Name FROM OWNERTAB' HASH(0x2a1c8c))
1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x28e6cc)~INNER 'SELECT id, Name FROM
OWNERTAB' HASH(0x2a1c8c))
dbih_setup_handle(DBI::st=HASH(0x28e7bc)=>DBI::st=HASH(0x2a1d28), DBD::Oracle::st,
28e7c8, Null!)
dbih_make_com(DBI::db=HASH(0x28e6cc), 2a6878, DBD::Oracle::st, 208, 0) thr#0
dbd_st_prepare'd sql SELECT
dbd_describe SELECT (EXPLICIT, lb 80)...
fbh 1: 'ID' NULLable, otype 96-> 5, dbsize 10/11, p10.s0
fbh 2: 'NAME' NULLable, otype 96-> 5, dbsize 20/21, p20.s0
dbd_describe'd 2 columns (row bytes: 30 max, 30 est avg, cache: 304)
1 <- prepare= DBI::st=HASH(0x28e7bc) at DBI.pm line 1436 via
/var/apache/cgi-bin/pickerFunction.cgi line 74
-> execute for DBD::Oracle::st (DBI::st=HASH(0x28e7bc)~0x2a1d28)
dbd_st_execute SELECT (out0, lob0)...
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at DBI.pm line 1438 via /var/apache/cgi-bin/pickerFunction.cgi
line 74
-> bind_col in DBD::_::st for DBD::Oracle::st (DBI::st=HASH(0x28e7bc)~0x2a1d28 1
SCALAR(0x2a1d10))
dbih_setup_fbav for 2 fields => 0x2a1e48
dbih_sth_bind_col 1 => SCALAR(0x2a1d10)
<- bind_col= 1 at DBI.pm line 1443 via /var/apache/cgi-bin/pickerFunction.cgi line
74
-> bind_col for DBD::Oracle::st (DBI::st=HASH(0x28e7bc)~0x2a1d28 2
SCALAR(0x2a1da0))
dbih_sth_bind_col 2 => SCALAR(0x2a1da0)
<- bind_col= 1 at DBI.pm line 1443 via /var/apache/cgi-bin/pickerFunction.cgi line
74
-> fetch for DBD::Oracle::st (DBI::st=HASH(0x28e7bc)~0x2a1d28)
dbd_st_fetch 2 fields...
dbd_st_fetch no-more-data
<- fetch= undef at DBI.pm line 1450 via /var/apache/cgi-bin/pickerFunction.cgi
line 74
<- selectcol_arrayref= ( [ ] ) [1 items] at pickerFunction.cgi line 74 via
/var/apache/cgi-bin/pickerFunction.cgi line 36
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x2a1d28)~INNER)
<- DESTROY= undef at pickerFunction.cgi line 75
! -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x28e6cc)~INNER)
! <- DESTROY= undef during global destruction
$I have fooled with the way that I set the hash, changing @$ary_ref to %ary_ref, $$ary_ref, etc etc and while I always get "ARRAY(0x######)", HASH(0x######)" or "" on the output, in some cases I see "Hash is 4/8" on the output. I have copied the entire CGI below; it's really just a test script to make sure I can populate a dropdown list from a database. I have a much larger script in mind, but I can't seem to get this to work and I have a deadline of Wednesday 9/3 to prove it can be done. My programming Perl DBI book should be here tomorrow from Amazon, but I'm not certain it will have an answer for me. I am pretty sure that my buildPopup() function is OK, since it gets generated properly if I change the name of %hsh below to %hash. Can someone point me in the right direction here, at least giving me a clue what's going wrong?
Thanks for reading this far...
Dave Anderson
#!/usr/bin/perl -w
use CGI::Carp qw(fatalsToBrowser); use CGI qw(:standard); use DBI;
# Clean up environment for taint mode before calling sendmail
BEGIN {
$ENV{PATH} =
".:/oracle/app/oracle/product/8.1.7/bin:/usr/bin::/usr/dt/bin:/usr/openwin/bin:/bin:/usr/ucb";
$ENV{LD_LIBRARY_PATH}="/oracle/app/oracle/product/8.1.7/lib:/lib:/usr/lib";
$ENV{ORACLE_HOME}="/oracle/app/oracle/product/8.1.7";
$ENV{TWO_TASK}="ho817dev";
$ENV{ORACLE_USERID}="statusck/statusck";
#delete @ENV{ qw( IFS CDPATH ENV BASH_ENV ) };}
my $q = new CGI;
$Submit = $q->submit(-name=>'button_name',
-value=>'Submit');
$IDbox = $q->textfield(-name=>'ID',-size=>5,-maxlength=>5);
$Namebox = $q->textfield(-name=>'Name',-size=>12,-maxlength=>15);$string = ":O";
output_form();
exit;
# Begin Subroutines #############################################
sub output_form {
print header();
dbmagic();
setupFormFields();
htmlFile();
print $htmlFile;
print end_html();
}sub setupFormFields {
#%hash = (0,Choose,
# DA,Dave,
# SD,Sachin,
# YY,Youli,
# KK,Krishna,
# IZ,Isabel,
# $q->param('ID'),$q->param('Name'));$OwnerPicker = buildPopup("Own", %hash);
}
sub dbmagic { $dbh = DBI->connect("DBI:Oracle:ho817dev", 'statusck', 'statusck'); $dbh->{RaiseError} = 1; $dbh->trace(3,"trace.log");
$stmt = $dbh->prepare("SELECT * FROM OWNERTAB", { Columns=>[1,2] });
$ary_ref = $dbh->selectcol_arrayref($stmt);
%hash = @$ary_ref; # build hash from key-value pairs so $hash{$id} => name%hsh = (0,Choose,
DA,Dave,
SD,Sachin,
YY,Youli,
KK,Krishna,
IZ,Isabel,
$q->param('ID'),$q->param('Name'));return %hash;
}
sub buildPopup {
$n = shift;
%h = @_; return $q->popup_menu(
-name=>$n,
-values=>[sort keys %h],
-labels=>{%h},
-default=>'0'
);}
sub htmlFile {
$htmlFile= <<END_OF_FILE
<html><head> <link rel=stylesheet href="../style.css" type="text/css"> </head>
<body>
<form method="POST" action="http://qatoolsdt3.ariba.com/cgi-bin/pickerFunction.cgi";> <table border="0" width="911" height="1"> <tr> <td width="903" colspan="8" height="1"> <p align="center"><strong><font size="3">Tools Team re-usable migration instances</font></strong></p> </td> </tr> <tr> <td width="903" colspan="8" height="1"> <p align="center"><strong><font size="3">$string</font></strong></p> </td> </tr> <tr> <td width="903" height="16" colspan="8"> <p align="center"><font size="2">Edit instance ID $ID:</font></td> </tr> <tr> <td width="6" height="1"></td> <td width="239" height="1" colspan="2"><p align="center"><b>General</b></p></td> <td width="282" height="1" colspan="2"><p align="center"><b>Base Instance Details</b></p></td> <td width="268" height="20" colspan="2"><p align="center"><b>Migrated Instance Details</b></p></td> <td width="84" height="1"></td> </tr> <tr> <td width="6" height="1"> </td> <td width="97" height="1"><p align="right">Owner:</p></td> <td width="136" height="1">$OwnerPicker</td> <td width="108" height="1"><p align="right">7.1 UI Port:</p></td> <td width="168" height="1">UIPort71</td> <td width="98" height="20"><p align="right">8.2 Path:</p></td> <td width="164" height="20">MigratedInstancePath</td> <td width="84" height="1"> </td> ID:$IDbox Name:$Namebox $Submit </tr>
</table>
</form>
</body>
</html> END_OF_FILE }
