Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Is it possible to call prepare statement with a variable of a variable?

by dirtdog (Monk)
on Feb 25, 2011 at 16:13 UTC ( #890185=perlquestion: print w/replies, xml ) Need Help??
dirtdog has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I'm trying to create a robust script that executes a for loop and calls different sql based on the value in $_ , but the "prepare" statement doesn't like the fact that it's a variable of a variable. Basically, I'm creating an excel spreadsheet using the Spreadsheet::WriteExcel module and looping thru the multi-dimensional array to call each sql statement. Hopefully there is a way around this otherwise i'll have to hard code all of my prepare statements. And with Perl..there has to be a way.

my @reports = ( [doc_request => [qw(T_Doc_Request N_Doc_Request X_Doc_Request)]], [doc_errors => [qw(T_Doc_Errors N_Doc_Errors X_Doc_Errors )]], ); my $T_Doc_Request=qq{select.... }; my $N_Doc_Request=qq{select... }; my $X_Doc_Request=qq{select... }; my $T_Doc_Errors=qq{select.... }; my $N_Doc_Errors=qq{select... }; my $X_Doc_Errors=qq{select... }; for (@reports) { my $report = $_->[0]; $worksheet->write(1, 0, $report, $header); $i++; for my $section (@{$_->[1]}) { print qq{report '$report', section '$section'\n}; $cell_column = 0; $worksheet->write($i, $cell_column, $section,$format1); $cell_column++;

the following does not work because $section ends up being T_Doc_Request and i need it to be $T_Doc_Request

my $sth = $dbh->prepare($section); $sth->execute; while ( my @row = $sth->fetchrow_array() ) { print ">@row<\n"; $worksheet->write($i, $cell_column, @row[$cell_column],$forma +t1); }#end while $sth->finish; }#end for section }# end for report

Any help is greatly appreciated thanks

Replies are listed 'Best First'.
Re: Is it possible to call prepare statement with a variable of a variable?
by moritz (Cardinal) on Feb 25, 2011 at 16:21 UTC
Re: Is it possible to call prepare statement with a variable of a variable?
by davido (Archbishop) on Feb 25, 2011 at 17:09 UTC

    "robust" and "variable of a variable" (which has the connotation of wielding symbolic references) are not generally two concepts that co-exist peacefully. While it may be possible to create something robust that uses symbolic references, it's a practice that 40 years of computer science have moved us away from vigorously, and for good reason. There are a few situations where symbolic refs may be exactly the right tool (and Perl does allow them), but more often than not they represent a design flaw.

    Your script seems to want to carry the notion of $section as a string at one point, and $section as a variable later. Converting a string to a variable is a symbolic reference. There are better tools for that. Using a hash allows you to make the variable always be a variable, and make its keys always be strings. That's safer, and reduces the possibility of things going really wrong. It improves robustness.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://890185]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (9)
As of 2017-12-12 18:35 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (335 votes). Check out past polls.