in reply to sql select with array

When @selector is interpolated into your string, the elements are being separated by a space. Since SQL engines expect those fields to be separated by commas, your code is bombing out. So, you need to add commas into your SQL to get it to work properly.

One "questionable" (but fun) way of doing this is by modifying the global $" variable, which is the separator used when interpolating arrays into strings. Its default value is a single space, which explains the above situation.
Setting it to ',' yields code like this: (warning, untested) 

my $sql;
{ 
  local $" = ',';  # localize $" for use in the next statement
  $sql = "select @selector from people where foo like '%bar%'"
}

my $row = $dbh->selectall_arrayref($sql) or die "SQL Error: $sql";

[download]

-Blake

Replies are listed 'Best First'.
Re: Re: sql select with array
by davorg (Chancellor) on Oct 22, 2001 at 13:35 UTC

    I'm interested to know why you think that changing $" is a "questionable" solution. It seems to me that it's situations like this that $" was invented for.

    --
    <http://www.dave.org.uk>

    "The first rule of Perl club is you don't talk about Perl club."

[reply]
      I would say it "questionable" as well. (And I use it all the time. :-) Three reasons.
      1. I try to avoid global variables as much as possible.
      2. $" is line noise and accordingly easy to miss by a maintainer (who is most likely not at my level of perl).
      3. Beginners are much more likely to set global variables than to localize them.
      (4. $" screws up the syntax highlighting in many editors.)

      Join() performs the same task in a much more maintainable way, so its not really needed. Also, if you think about it join is about the same or less in terms of usage.

      local $"=','; my $s="@list";
my $s=join(',',@list);

      [download]

      As i said though I use it in obfus, snippets and temporary debuging code all the time. But only cause I like to. :-)

      Yves
      --
      You are not ready to use symrefs unless you already know why they are bad. -- tadmc (CLPM)

[reply]
[d/l]
      I always hesitate to tell anonymonks to go tinkering with global vars..... Its the case of "a little information can be dangerous." Once you understand the full implications, I think it is a fine solution, though.

      -Blake

[reply]

In Section Seekers of Perl Wisdom