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
– TLP, Commented Nov 2, 2020 at 14:49
$sql in your prepare statement should be a string, not a file address. — TLP
– TLP, Commented Nov 2, 2020 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
– Jonathan Leffler, Commented Nov 3, 2020 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
– Jonathan Leffler, Commented Nov 3, 2020 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 — or MRE or whatever name SO now uses) or an SSCCE (Short, Self-Contained, Correct Example) 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
– Jonathan Leffler, Commented Nov 3, 2020 at 22:11

Dave Cross · Accepted Answer · 2020-11-02 11:44:19Z

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 2, 2020 at 11:44

Dave Cross

69.5k3 gold badges55 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

itro Over a year ago

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

Dave Cross Over a year ago

@itro: Did you make the changes I suggested?

itro Over a year ago

Yes I did change as you mentioned. Thanks.

Dave Cross Over a year ago

@itro: Then it's hard to help more without seeing the current version of your code. Perhaps you could edit your question to add it at the end.

itro · Accepted Answer · 2020-11-05 14:00:27Z

0

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

   }

answered Nov 5, 2020 at 14:00

itro

7,26629 gold badges84 silver badges128 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related