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

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]

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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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]

[reply]
[d/l]
[select]