How do I pass a Perl array as a scalar into a subroutine?

Question

A newborn Perl developer here. I've been wracking my brain and searching all over online trying to figure this out... exasperated, I am come to you seeking clarity.

I have the following code (only relevant parts left in) as rest is working):

my @arrMissingTids;
@arrMissingTids = %hshTids;

my $missingtid;
foreach $missingtid (@arrMissingTids) {
    print "$missingtid\n";
}

This works well, returning the values I want to be in the array:

500000246,500000235,500000185,500000237,500000227,500000252

However, when I pass this to a subroutine and include it in a variable name, it does not provide the list as written above, but rather just the number 1. Code for that is below:

myqry(@arrMissingTids);

sub myqry($) {

    my $missingtids = @_;

    $sql = "select 
        i.tid i_tid, i.name i_name
        from 
        instrument i
        where i.tid in ($missingtids)";

    print "$sql/n";
}

Print $sql returns the following:

Select i.tid i_tid, i.name i_name from instrument i where i.tid in (1)

When I want it to return the following:

Select i.tid i_tid, i.name i_name from instrument i where i.tid in (500000246,500000235,500000185,500000237,500000227,500000252)

Thanks in advance for any pointers in the right direction!

Answer 1

There are three problems here. The first is using a function prototype, just leave it away, see Why are Perl 5's function prototypes bad?.

The second is a mismatch of types in the function call and at the recipient side of the function itself. Either use arrays both times, or array references both times.

The third is that treating data as part of a SQL query, possibly opening the gate for a SQL injection attack. This is safely mitigated by assembling the query string with placeholders for use with DBI.

myqry(@arrMissingTids);
sub myqry {
    my @missingtids = @_;
    $sql = "select
        i.tid i_tid, i.name i_name
        from
        instrument i
        where i.tid in (" . join(',', ('?') x @missingtids) . ")";
    print "$sql\n";
    # $dbh->selectall_arrayref($sql, {}, @missingtids)
}

---

myqry(\@arrMissingTids);
sub myqry {
    my @missingtids = @{ shift() };
    $sql = "select
        i.tid i_tid, i.name i_name
        from
        instrument i
        where i.tid in (" . join(',', ('?') x @missingtids) . ")";
    print "$sql\n";
    # $dbh->selectall_arrayref($sql, {}, @missingtids)
}

Answer 2

If anyone hasn't mention it yet, the prototype here is an issue:

sub myqry($) {

Consider this:

sub test1($) {
    print "$_\n" foreach @_;
}

sub test2 {
    print "$_\n" foreach @_;
}

my @args = ('a', 'b', 'c');

test1(@args);
test2(@args);      

and the output:

3
a
b
c

By now you've realized that an array in scalar context is just the number of elements, eg:

my $n = @args;

$n is 3. By passing an array into a subroutine which reduces it to a scalar, you end up with one arg, the number of elements in the array. Then you do this:

my $missingtids = @_;

which will always just be one because of the ($) in the sub definition (the array has already been reduced to one element). Hence, you get 1.

$0.02: IMO prototypes in perl are a bad idea ;)

Answer 3

The problem is right here:

my $missingtids = @_;

You're calling the array @_ in scalar context. That means that you're assigning to $missingtids the number of elements in @_. One way to work around this would be to pass an array reference:

sub myqry {

    my $missingtids_ref = shift;
    my @missingtids=@$missingtids_ref;

    $sql = "select 
        i.tid i_tid, i.name i_name
        from 
        instrument i
        where i.tid in (" . join(",",@missingtids) . ")";

    print "$sql/n";
}

For more information, take a look at perldoc perlref and perldoc perldata.

Answer 4

So you want to create the string

... in (1,2,3,4)

Use join

myqry(join(',', @arrMissingTids))

But that's rather inside-out. This would be better:

sub myqry {
    my $missingtids = join(',', @_);

    return "select 
        i.tid i_tid, i.name i_name
        from 
        instrument i
        where i.tid in ($missingtids)
    ";
}

myqry(@arrMissingTids);

Answer 5

Thanks for all the help. After all is said and done, I ended up getting rid of the prototype and using this code, which worked perfectly and was pieced together from all the help above:

myqry(@arrTids);

sub myqry {
   $missingtids = join(",",@_);

   .rest of code...
}

Answer 6

Untested, but probably right:

myqry(\@arrMissingTids);

sub myqry($) {

    my $missingtids = shift; # or $_[0]

    $sql = "select 
        i.tid i_tid, i.name i_name
        from 
        instrument i
        where i.tid in (" . join(',', @{$missingtids}) . ")";

    print "$sql/n";
}

Of course you could pass the array itself instead of a reference, but then you would need to change the prototype and rewrite the array references. But the stuff above should get you going.