How to pass parameters from Perl script to SQL script and execute it from Perl script?

Question

I use Informix as database in Linux environment. I have a Perl script which should execute an SQL-script. Before execution, it should also pass all parameters to the SQL-script.

I can't figure out how to pass parameters to .sql script? It also does run but I get following error.

DBD::Informix::st fetchrow_array failed: SQL: -400: Fetch attempted on unopen cursor. at startSelectQuer.pl 

How can I realize this?

selectQuer.sql

DROP TABLE IF EXISTS tempTable;
SELECT * FROM 'informix'.systables INTO TEMP tempTable;

DROP TABLE IF EXISTS magic_ant;
 select * from  del_new
    where
       id    = $i_id       and
       year  = $i_year     and
       month = $i_month              
    into 
        temp magic_ant; 

    
    DROP TABLE IF EXISTS magic_buck;
    select * from  upper_new
    where
       id    = $i_id       and
       year  = $i_year     and
       month = $i_month              
    into 
        temp magic_buck; 

DROP TABLE IF EXISTS alleMagic;
select  * from  magic_ant
union
select  * from  magic_buck
into temp alleMagic;    

select lname, fname, ext from alleMagic;

startSelectQuer.pl

       #!/usr/bin/perl
use strict;
use warnings;
use DBI;
    
my ($ID)    = $_[0];
my ($YEAR)  = $_[1];
my ($MONTH) = $_[2];

my $BEG_ANT=801 ; 
my $END_ANT=803 ; 
my $BEG_BRU=802 ; 
my $END_BRU=900 ;

my($dbh, $sth, $query);

######################################################################
my $database = "$ENV{DBNAME}";         
my $user ="";                                                                                          
my $pass ="";                                                                                       
$dbh = DBI->connect("dbi:Informix:$database", $user, $pass);                                                  
######################################################################
die "failed to connect to MySQL database:DBI->errstr()" unless($dbh);

my $sqlFile = "/SQLSCRIPTS/selectQuer.sql";
open (SQL, "$sqlFile")   or die("Can't open file $sqlFile for reading");

# Loop though the SQL file and execute each and every one.
while (my $line = <SQL>) {

  chomp $line;
  $line = join(' ',split(' ',$line));
  if ((substr($line,0,2) ne '--') and (substr($line,0,3) ne 'REM')) {
     if (substr($line,- 1,1) eq ';') {
            $query .= ' ' . substr($line,0,length($line) -1);
            # replace with value
            replaceQueryWithValue($query);
            
           $sth = $dbh->prepare($query, {'ix_CursorWithHold' => 1}) 
          or die "prepare statement failed: $dbh->errstr()";
           $sth->execute() or die "execution failed: $dbh->errstr()";

           my $rows = $sth->rows;
            #loop through each row of the result set, and print it.
           if ($rows > 0) {
            # Getting error here as: DBD::Informix::st fetchrow_array failed:
            # SQL: -400: Fetch attempted on unopen cursor. 
              while(my @row = $sth->fetchrow_array) {
                 print qw($row[0]\t$row[1]\t$row[2]\n);
              }
           } else
              {
                  print "\nThere is no result for query: $query\n" ;
              }
            $query = ' ';
      } else {
                 $query .= ' ' . $line;    
          }
  }
}

# close data connection
$sth->finish;
$dbh->disconnect;

sub replaceQueryWithValue{
   $query =~ s/i_id/$ID/ig;
   $query =~ s/i_year/$YEAR/ig;
   $query =~ s/i_month/$MONTH/ig;
}

Answer 1

I used $sth->fetch instead of $sth->fetchrow_array after execution of .sql as below.

  $sth = $dbh->prepare( "select * from alleMagic;" );
  $sth->execute;
    
   # Column binding is the most efficient way to fetch data
   my $rv = $sth->bind_columns(\$lname, \$fname, \$ext );
   while ($sth->fetch) {
       print "$lname, $fname, $ext \n";

   }