sybperl-l Archive

From: Phil dot Moore at msdw dot com
Subject: Here document troubles w/ SQL
Date: Jun 20 2000 9:47PM

>>>>> "Julia" == Julia Stone  writes:

Julia> I am having a problem with using a scalar variable in my here
Julia> document.  Here is a small example of my problem:

Julia> my $dept_name;

Julia> my $sql = <<"EOSQL";
Julia> SELECT count(students)
Julia> FROM newStudents
Julia> WHERE dept = "$dept_name"
Julia> EOSQL

Julia> I want to loop through department names.  I have done this before
Julia> without a loop.  But this time I continually end up with an empty
Julia> string!

Julia> foreach my $school_dept (@all_depts)
Julia> {

Julia> $dept_name = $school_dept;
Julia> # I'll check to make sure the value of $dept_name is correct,
Julia> and it has been
Julia> print "Value of dept_name: $dept_name \n";
Julia> my @row = $data_dbh->nsql($query,"ARRAY");
Julia> die "$cmdname: $DB_ERROR, stopped at" if $DB_ERROR;
Julia> print "Query: $sql \n";
Julia> }

Julia> When I print the $sql value it consistently returns with the following:

Julia> SELECT count(students)
Julia> FROM newStudents
Julia> WHERE dept = " "

Julia> All values are in place, but somehow this $sql isn't able to resolve
Julia> $dept_name ... anyone have any ideas why?

The $dept_name is evaluated only when you first assign the contents of
the here-doc to the variable $sql, not dynamically each time you
evaluate the variable $sql.

You could play games with eval to make this happen, but its simpler to
create a small subroutin that takes the $dept_name as an argument, as
returns the SQL string.  Then, each iteration through the foreach
loop, you will call this subrouting to get a new $sql string, and then
use that in your query.