Hello: I have, I believe, a variable interpolation question. I have a perl program that reads in a file that contains sql statments. I pull each SQL and push it into a hash, where the hash is a transaction_number and the associated SQL. The sql statments however hold Perl variables such as:

   ...
   WHERE asof_date = '$test_date'
   ...
[download]

Of course, $test_date is defined in the program, and my hope is that once the sqls are read in, the variables in the sqls take the approproate value that is assigned to them in the program. I can make the program work fine if I replace the actual string of variable with the variable like this in the loop while I am reading in the file:

   ...
   s/\$test_date/$test_date/g;
   ...
[download]

But that is not really what I wanted to do. I was hoping that the variable in the sql file is interpolated and takes on the value what is defined in the program. Here is a skelatal of the code:

#===============================================#
$ss_source_file   = "C:/code/mla/tmp.sql";
$test_date = "30-APR-2011";
$schema    = "DEV";
#####################
%trancd_sql = (); # each SQL in a hash with a 
                  # transaction number and associated SQL

open(SS_SOURCE, "< $ss_source_file ") || die "Can't open SQL source fi
+le! $!\n";
while (<SS_SOURCE>) {
   if (/^;/) {    # the end of each SQL 
      $end_sql   = 1; 
      $begin_sql = 0;
      $trancd_sql{$tran_code} = $sql; ## push each  tran_code/SQL into
+ hash
      $sql = '';
      $tran_code = '';
   }
   
   ## beginning of an sql
   if (/^\s*-{2,}\s*tran_code\s*\d{4,}/) { 
      ($tran_code) = $_ =~ /^\s*-{2,}\s*tran_code\s*(\d{4})/;
      $begin_sql = 1;
      $end_sql   = 0;
   }

   if (($begin_sql == 1) && ($end_sql != 1)) {
      #s/\$test_date/$test_date/g; # works if uncommented
      #s/\$schema/$schema/g;       # works if uncommented
      $sql .= $_;
   }
   
}
close(SS_SOURCE);

$trcd = 1;
while ($trcd > 0) {
   print "Enter Tran code, or '0' to exit: ";
   $trcd = <STDIN>;
   chomp($trcd);
   print $trancd_sql{$trcd};
}   
#====================================================#
[download]

and here is the data file with he sqls in it.

#=====================================================#
-- tran_code 1752
SELECT *
   FROM $schema.TRANSACTOPMS
      WHERE TRANSACTION_CD IN ('146','147')
      AND AMT < 0
      AND ASOF_DATE = '$test_date'

;
-- tran_code 1753
SELECT *
      FROM $schema.TRAN
      WHERE TRANSACTION_CD IN ('196','197')
      AND ASOF_DATE = '$test_date'
      AND RECORD_VERSION_NBR = '0'
;

-- tran_code 1758
SELECT COUNT(SS_NO), SUM(ABS(SS_PRINCIPAL))     
      FROM $schema.SS_MLA_TRAN
      WHERE INVESTOR_ID = '195'
      AND MAN_CD = '4'
      AND ASOF_DATE = '$test_date'
      AND RECORD_VERSION_NBR = '0'
      and GROUP_ID = 'PCC'
;      

-- tran_code 1765
      ... and so on, you get the idea
;      
#==================================================#
[download]

What I wanted to see as output is:

SELECT *
   FROM DEV.TRANSACTOPMS
      WHERE TRANSACTION_CD IN ('146','147')
      AND AMT < 0
      AND ASOF_DATE = '30-APR-2011'
[download]

instead of the:

SELECT *
   FROM $schema.TRANSACTOPMS
      WHERE TRANSACTION_CD IN ('146','147')
      AND AMT < 0
      AND ASOF_DATE = '$test_date'
[download]

As I mentioned before, I can make this work by replacing the actual string (which is a variable name in the sql), but my objective here is getting a more elegant solution and for my own personal learning as well. Any ideas of what I am missing? Greatly appreciate your input!

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.