Good stuff!

I have created the Menu and it is accepting params so, it works fine when the id is typed, but it doesn't work when it is left blank ""

I am struggling with this error message: Can't locate object method "selectall_array" via package "DBI::db" at ./test.pl line 60.

Well, that is the line 60: @id = map{ $_->[0] } $dbh->selectall_array($sql,undef,'no');

what am I doing wrong here?

#!/usr/bin/perl -W

use DBI;
use Text::CSV_XS;

# GLOBALS
@id = "";
$dbname = "";
$user = "";

# MAIN MENU
# Don't allow duplicated arguments and blank/undef arguments
# except for id (in this case blank/undef id means all ids)
foreach my $arg (@ARGV)
{
    if ($arg eq '-h' || $arg eq '--help')
    {
        die "Script usage manual here\n";
        exit;
    }
    
    if ($arg =~ /^(\w+)$/)
    {
        die "Error: Multiple databases: $dbname and $arg." if $dbname;
        
        $dbname = $1 ; next;
    }
    
    if ($arg =~ /^--?u(\w+)$/)
    {
        die "Error: Multiple users $user and $arg." if $user;
        
        $user = $1 ; next;
    }
    
    if ($arg =~ /^--?id(\d+)$/)
    {
        die "Error: Multiple ids specified: -id$id[0] and $arg.\n" if 
+$id[0];

        $id[0] = $1 ; next;
    }
    elsif ($arg =~ /^--?id(\S+|)$/)
    {
        die "Error id format, use -id<integer>\n";
    }    
}

die "No database, type <dbname>\n" unless $dbname;
die "No username, use -u<username>\n" unless $user;


# DBI CONNECTION
my $dbh = get_dbh();

# EXEC
if ($id[0] eq "")
{   
    my $sql = "SELECT DISTINCT id from mytable
             WHERE active = ?";
    @id = map{ $_->[0] } $dbh->selectall_array($sql,undef,'no');
}

for my $id (@id)
{
  my @query =
  (
    ["SELECT id FROM mytable     WHERE id=? AND salary >= 1","csvfile1
+_$id.csv"],
    ["SELECT name FROM mytable   WHERE id=? AND salary >= 1","csvfile2
+_$id.csv"],
    ["SELECT salary FROM mytable WHERE id=? AND salary >= 1","csvfile3
+_$id.csv"],
    ["SELECT dept FROM mytable   WHERE id=? AND salary >= 1","csvfile4
+_$id.csv"],
  );

  for (@query)
  {
    run_query(@$_,$id);
  }
}

# SUBROUTINES
sub get_dbh
{
    my($dbh) = DBI->connect("dbi:IngresII:$dbname","$user","")
        or die "Could not connect to database $dbname\n";
    
    return $dbh;
}

sub run_query 
{
    my ($sql, $csvfile, $id) = @_;
    print "Running $sql for $id\n";
    
    open my $fh, '>', $csvfile or die "Could not open $csvfile: $!";

    my $csv = Text::CSV_XS->new ({ binary => 1, eol => "\n" });
    my $sth = $dbh->prepare ($sql); 
    $sth->execute($id);
    $csv->print($fh, $sth->{NAME_lc});
    my $count = 1;
    
    while (my $row = $sth->fetchrow_arrayref)
    {
        $csv->print($fh, $row);
        ++$count;
    }
    
    close $fh;
    
    print "ID: $id, $count lines dumped to $csvfile\n";
}
[download]

Which version of DBI are you running? select_array was added in v1.635, 24/04/2016.

Update: from the command line, get DBI version: perl -MDBI -e 'print $DBI::VERSION'

DBI Version: 1.627

That explains a lot. Is there any other way to do it without update the DBI?

try the selectall_arrayref method

  #@id = map{ $_->[0] } $dbh->selectall_array($sql,undef,'no')
  my $ref =  $dbh->selectall_arrayref($sql,undef,'no');          
  @id = map{ $_->[0] } @$ref;
[download]

poj

It worked like a charm! I will just do an adjustment as it is getting only the last element from the array. Cheers!

That piece of code is returning only the last id. How to get all ids using it?

I have changed it for the below code but now it is returning only the first element. Can you help me, please?

if ($id[0] eq "")
{   
    my $sql = "SELECT DISTINCT id FROM mytable WHERE active = ?";
    $id[0] =  @{$dbh->selectall_arrayref($sql,undef,'no')};
    #@id = map{ $_->[0] } @$ref;
    print join (", ", @id), "\n";
}
[download]

New question :)

Any way to create a temporary table in the array @query and make it contents available to the other queries?

Something like...

my @query =
(
    [DECLARE GLOBAL TEMPORARY TABLE SESSION.temp SELECT id FROM mytabl
+e ON COMMIT PRESERVE ROWS WITH NORECOVERY, "/dev/null"],
    ["SELECT name FROM SESSION.temp   WHERE id=? AND salary >= 1","csv
+file2_$id.csv"],
}
[download]

I suggest you add another 'type' field to run the non-select query with $dbh->do($sql). However you probably only need run that query once not for each id.

for my $id (@id){
  my @query = (
    [1,"DECLARE GLOBAL TEMPORARY TABLE SESSION.temp 
        SELECT name,id FROM mytable 
        ON COMMIT PRESERVE ROWS WITH NORECOVERY"],
    [2,"SELECT name FROM SESSION.temp
        WHERE id=? AND salary >= 1","csvfile2_$id.csv"], 
    [2,"SELECT id FROM mytable
        WHERE id=? AND salary >= 1","csvfile1_$id.csv"],
  );

  for (@query){
    my ($type,$sql,$filename) = @$_;
    if ($type == 1){
      $dbh->do($sql);
    } elsif ($type == 2){
      run_query($sql,$filename,$id);
    }
  }
}
[download]

poj

Cool, this new approach allows filtering the $id on the temp table now :)

So, use the place holder "WHERE open = ?" it is not necessary anymore for all the selects statement but, still necessary for those selects that use a different table than the temp one. And this is taking me back to avoid placeholders in the queries again.

Is there any better approach for those mix of selects that came from the temp table and those ones that came from another table?

    [1,"DECLARE GLOBAL TEMPORARY TABLE SESSION.temp 
        SELECT name,id FROM mytable WHERE id=$id
        ON COMMIT PRESERVE ROWS WITH NORECOVERY"],
    [2,"SELECT name FROM SESSION.temp
        salary >= 1","csvfile1_$id.csv"], 
    [2,"SELECT id FROM mytable2
        WHERE id=$id AND salary >= 2","csvfile2_$id.csv"],
[download]