Re^4: Multiple queries on DBI corresponding to multiple csv files?

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

Comment on Re^4: Multiple queries on DBI corresponding to multiple csv files? Select or Download Code

Replies are listed 'Best First'.
Re^5: Multiple queries on DBI corresponding to multiple csv files? by jtech (Sexton) on Feb 25, 2019 at 10:08 UTC
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]	[reply] [d/l]
Re^6: Multiple queries on DBI corresponding to multiple csv files? by poj (Abbot) on Feb 25, 2019 at 12:52 UTC
You can still use placeholders in the do statement `$dbh->do($sql,undef,$id);` For queries without placeholders, either create another type with a different sub for my $id (@id){ my @query = ( [1,"DECLARE GLOBAL TEMPORARY TABLE SESSION.temp SELECT name,id FROM mytable WHERE id = ? ON COMMIT PRESERVE ROWS WITH NORECOVERY"], [3,"SELECT name FROM SESSION.temp WHERE salary >= 1","csvfile2_$id.csv"], [2,"SELECT id FROM mytable WHERE id = ? AND salary >= 1","csvfile1_$id.csv",$id], ); for (@query){ my ($type,$sql,$filename) = @$_; if ($type == 1){ $dbh->do($sql,undef,$id); } elsif ($type == 2){ run_query($sql,$filename,$id); } elsif ($type == 3){ run_query_noparam($sql,$filename); } } } [download] or amend the existing `run_query` sub to check for existence of parameter `if (defined $id){ print "Running $sql for $id\n"; $sth->execute($id); } else { print "Running $sql (no parameters)\n"; $sth->execute(); }` [download] poj	[reply] [d/l] [select]