This is WONDERFUL !!! Works very nicely :)
thanx a lot. Dan -----Original Message----- From: Michael A Chase [mailto:[EMAIL PROTECTED] Sent: Thursday, March 20, 2003 3:36 PM To: '[EMAIL PROTECTED]'; Rozengurtel, Daniel Subject: Re: need help on "bind_columns " On Thu, 20 Mar 2003 15:06:28 -0500 "Rozengurtel, Daniel" <[EMAIL PROTECTED]> wrote: > I have a question about bind_columns feature. > In my next piece of code I am trying to get the values from specific fields > of a table to be associated with their names in ISSU_FIELDS hash. I cannot > indicate the exact fields I wanna fetch since its going to be chaning as per > client. Therefore I understand I need to use the (NAME_uc) or (NAME_lc) > feature as well. The following code works, if i loop thru the %issu_fields > and fetch for its keys the values obtained in sth->fetch loop. %issue_fields > is a dynamic hash, which can grow/shrink. The question is: how do I pass the > keys of that hash to bind_columns so that I dont retrieve '*' and then pick > things I want from it. You don't need to pass the keys, you need to pass references to the values associated with the keys. > (BTW: in terms of performnce: is it better to "select * from" and then get a > smaller set of fields or to specify the exact set of fields as in "select > field1, field2...from"?) It is more efficient to select just the columns you want. This is especially true if it allows you to skip copying the values from one hash to another. If the keys in %issu_fields are known before you create the SQL, and the key names correspond to the table column names, you can use the keys to generate your list of column names. # The sort isn't essential, but it does insure that the order of # the columns is always the same. my @col = sort keys %issu_fields; my $sql = "SELECT " . join( ", ", @cols ) . " FROM ..."; my $sth = prepare_cached( $sql ) or die "Prepare of $sql failed, $DBI::errstr\n"; > my $sth= > $dbh->prepare_cached(q{SELECT * FROM ISSU WHERE INSTR_ID=? AND END_TMS IS > NULL}) || die $dbh->errstr; > > my %issu_fields=( > DENOM_CURR_CDE=>'', > ACTVY_STAT_TYP=>'', > INSTR_ISSR_ID=>'', > ISS_TMS=>'' > ); > > my %row; > > $sth->bind_param(1, $instr_id, {TYPE=>SQL_CHAR}); > $sth->execute; Unless you are worried about trailing spaces getting removed, you can use '$sth -> execute( $instr_id );' instead of using bind_param(). Don't forget to check for errors. > #$sth->{NAME_uc} - returns a reference to an array contains col_names. > > $sth->bind_columns( \( @row{ @{$sth->{NAME_uc} } } )); # If you earlier generated a list of columns # you can bind directly into %issu_fields. $sth -> bind_columns( \( @[EMAIL PROTECTED] ) ); > while( $sth->fetch() ){ > foreach my $fld_nme (sort keys %issu_fields) > {$issu_fields{$fld_nme}=$row{$fld_nme};} > }# while > > print map "$_ =>$issu_fields{$_}\n", sort keys %issu_fields; -- Mac :}) ** I normally forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age. _____________________________________________________________________ IMPORTANT NOTICES: This message is intended only for the addressee. Please notify the sender by e-mail if you are not the intended recipient. If you are not the intended recipient, you may not copy, disclose, or distribute this message or its contents to any other person and any such actions may be unlawful. Banc of America Securities LLC("BAS") does not accept time sensitive, action-oriented messages or transaction orders, including orders to purchase or sell securities, via e-mail. BAS reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the BAS e-mail system.
