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;
}

It is probably not a great idea to try to interpolate an object function call in a string. `$dbh->errstr()`. — TLP, Nov 02 '20 at 14:49
`$sql` in your prepare statement should be a string, not a file address. — TLP, Nov 02 '20 at 14:54
Between the two lines `replaceQueryWithValue($query);` and `$sth = $dbh->prepare($query, {'ix_CursorWithHold' => 1})`, you should arrange to print the value in `$query` (appropriately annotated) so that you can see what it is that you're asking the Informix DBMS to prepare and then execute. The 'fetch on an unopen cursor' indicates that whatever statement you are executing isn't a select statement — many of the statements in the file are not select statements which return rows (the `INTO TEMP` clause means the statement does not return values). — Jonathan Leffler, Nov 03 '20 at 22:06
Note that the third `and` in the statements in the file such as `select * from del_new where id = $i_id and year = $i_year and month = $i_month and into temp magic_ant; ` is erroneous. You should either include another condition after the third `and` or drop it. — Jonathan Leffler, Nov 03 '20 at 22:09
It is helpful if you include minimal schemas for the tables you use, and minimal sets of data, with the expected and actual results. This is part of creating an MCVE ([Minimal, Complete, Verifiable Example](https://stackoverflow.com/help/mcve) — or MRE or whatever name SO now uses) or an SSCCE ([Short, Self-Contained, Correct Example](http://sscce.org/)) for an SQL-related question. Forcing those who might help you to do that work doesn't make it easy to get answers — in general, people won't do it. — Jonathan Leffler, Nov 03 '20 at 22:11
Also, the assignments `my ($ID) = $_[0]; my ($YEAR) = $_[1]; my ($MONTH) = $_[2];` do not pull command line arguments, and `@_` doesn't contain anything, so you end up with warnings about uninitialized values if you try to print `$ID`, `$YEAR` or `$MONTH`. Replace the references to `$_[0]` etc with `$ARGV[0]` at least gives the code a chance of working. Or maybe you extracted the body of a subroutine and did not adapt it to the 'out of subroutine' world… Whatever the reason, the code shown doesn't stand much chance of working. Which makes it hard to debug for you. — Jonathan Leffler, Nov 03 '20 at 22:18

score 2 · Answer 1 · answered Nov 02 '20 at 11:44

2

When asking a question like this, it's useful if you tell us exactly what isn't working as you expect it to. Does nothing happen? Do you get an error message? Does your computer burst into flames?

Without that, we're pretty much guessing. But I'm happy to have a guess here. I know nothing about Informix, but I would guess that you're seeing the "prepare statement failed" error. Is that right?

If so, it seems to be that you're trying to compile an SQL statement that looks like SQLSCRIPTS/selectQuer.sql - when actually, that is the name of a file that you should be opening and reading your SQL statement from.

answered Nov 02 '20 at 11:44

Dave Cross

68,119
3
51
97

It does not run and I traced it up to this line which block it continue. $sth->execute($ID, $YEAR, $MONTH) or die "execution failed: $dbh->errstr()"; – itro Nov 02 '20 at 14:06
2

@itro: Did you make the changes I suggested? – Dave Cross Nov 02 '20 at 14:48
Yes I did change as you mentioned. Thanks. – itro Nov 03 '20 at 10:52
1

@itro: Then it's hard to help more without seeing the current version of your code. Perhaps you could [edit your question](https://stackoverflow.com/posts/64644501/edit) to add it at the end. – Dave Cross Nov 03 '20 at 14:02

score 0 · Accepted Answer · answered Nov 05 '20 at 14:00

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";

   }

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

2 Answers2