Replacing symbol with a list of variables

[kb2001]

I am new to perl and am looking for a starting point or approach to my problem.

We have an application that generates a SQL statement to run a data extract. The SQL has multiple ? throughout. These are replaced by a list of values that are comma delimited. I am looking for a way to take the first value in the list to replace the first ? in the SQL. Second value in the list replaces the second ?, and so on. There are between 20 and 50 places to replace the data in each SQL statement and it is very time consuming.

I'd like to find a way to do the replacement, but am not sure how to approach it. I would think given a file.sql and a file.input I could generate finished.sql. Can somebody point me in the right direction on this, please?

My initial thoughts are to read the data list into an array, and replace the ? with $data[0], $data[1] or something like that, but am unsure how to do that incrementally in order. Is this a good approach or is there a better way to do this?

 

[kb2001]

I have the piece to split the data out into an array, to put single quotes around the strings, though not eloquently. That was easier than I thought, but I have no idea where to begin on how to put them into the SQL statement in order replacing the ?

#!/usr/bin/perl

open(DATAFILE, "sql.values");

$line=<DATAFILE>;
@data= split(/,/,$line);

$i=0;

while ( @data[$i] ) {
   chomp(@data[$i]);
   if ( @data[$i] eq 'IN' || @data[$i] eq 'NON-RX' || @data[$i] eq 'RX' || @data[$i] eq 'N' || @data[$i] eq 'Y' || @data[$i] eq '1Q' ) {
     @data[$i] = "\'@data[$i]\'";
   }
   print "$i\t@data[$i]\n";
   $i++;
}

 

[prex1]

@data=split(/,/,$line);
@sql=split(/\?/,$sql);
@sql<=@data && warn"Too much data in $line for $sql\n";
@sql>$#data+2 && die"Not enough data in $line for $sql\n";
%quoted=('IN',1,'NON-RX',1,'RX',1,'N',1,'Y',1,'1Q');
$result='';
for($i=0;$i<@sql;$i++){
  if(exists$quoted{$data[$i]}){
    $result.="$sql[$i]'$data[$i]'";
  }else{
    $result.=$sql[$i].$data[$i];
  }
}
$result.=$sql[$#sql];

Franco

http://www.xcalcs.com

: Online engineering calculations

http://www.megamag.it

: Magnetic brakes for fun rides

http://www.levitans.com

: Air bearing pads

 

[stevexff]

Er, I'm a bit confused. The whole point of having the ? signs in the SQL is that you don't have to do the replacement. You just prepare the statement, and then execute it as many times as you want, passing in the parameters. DBI automatically substitutes them into the positions of the ?s in the prepared statement.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]