perl arrays in postgresql insert statement

Question

I think i have a logical problem in my code. I have a mongodb with template,value,row,column. For example $record->{template} it's T1 and $record->{column} 1 and $record->{row} i have some dates ("d.m.Y"). I need to arrage @insert_data values depending $record->{row} and $record->{column}

The problem is that i can't use @insert_data[1] or @insert_data[2] or ..etc in that insert statement, but i don't understand why, and i don't understand how can i resolve my problem.

while (my $record = $collection->next){
    if($record->{template} eq "T1"){
        my @insert_data;
        if($record->{column} == 1 and $record->{row} != 0){
            #my @insert_data[1] = $record->{VALUE};
        }
        if($record->{column} == 2 and $record->{row} != 0){
            #my @insert_data[2] = $record->{VALUE};
        }
        if($record->{column} == 3 and $record->{row} != 0){
            #my @insert_data[3] = $record->{VALUE};
        }
        if($record->{column} == 4 and $record->{row} != 0){
            #my @insert_data[4] = $record->{VALUE};
        }
        if($record->{column} == 5 and $record->{row} != 0){
            #my @insert_data[5] = $record->{VALUE};
        }
        if($record->{column} == 6 and $record->{row} != 0){
            #my @insert_data[6] = $record->{VALUE};
        }
        $stmt->execute(@insert_data[1],@insert_data[2],@insert_data[3],@insert_data[4],@insert_data[5],@insert_data[6],$record->{template});
    } elsif($record->{template} eq "T5"){

    } elsif($record->{template} eq "T10"){

    }   
}
}

Thanks!

Axeman · Accepted Answer · 2013-05-16 12:48:35Z

You're pretty much doing it wrong if you pass successive slots of an array to a method. You should never need to pass func( $a[1], $a[2], ... $a[n], ... to a sub. Passing them separately will ensure that an argument slot is used so that you might not encounter $record->{template} earlier than you expect it, but so will slice notation. ( @a[1..6] ).

And unless you're doing Perl 6 (you're not) the slots of an array are scalars, so you will need the scalar sigil ($) to assign to a slot.

As in: $insert_data[1].

Also, anywhere outside of legacy COBOL, this code is horrible: In any modern language, you should not have to branch to access structures. You should just be able to do

my $col = $record->{column}; # cache frequently referenced item.
if ( $record->{row} != 0 and $col > 0 and $col < 7 ) {
    $insert_data[ $col ] = $record->{VALUE};
}

And why you would want to pass a bunch of undefined values and only one actual value to a query is beyond me, but it looks like this:

$stmt->execute( @insert_data[1..6], $record->{template} );

Keep in mind that the slice notation ( @a[$i..$j] ) means that it will send 6 positional values as arguments. This may be important because in both your implementation and my implementation if $record->{column} is 1, the array is simply [ undef, $record->{VALUE} ]; it simply has 2 elements in it, at offset #0 and offset #1.

Without the slice notation, you pass two arguments before $record->{template}--probably unexpected. But the slice notation ensures that how ever many slots of the array have actually been assigned and allocated, the resulting expression will take up 6 positions (5 of which will be undef).

But first and foremost, for such a newbie as yourself, USUW:

use strict;
use warnings;

That needs to go at the top of your code. If you don't understand an error that strict kills the script for you can easily look it up in perlfaq and googling a solution is easy as well.

If you don't understand a message that warnings is giving, then you can also insert this library at the top of your code:

use diagnostics -verbose;

Which can tend to give you more explanation than you want to read.

Thanks for your answer. I understand what was wrong and other explanations. I have one more question if you can help me... I forgot to mention that all data are corelated. I need to insert all data on the same row. For example: $record->{row} = 1 and $record->{column} [1..6] = one record in my postgresql database. Now, are on 6 records in postgresql.

mpapec · Accepted Answer · 2013-05-16 12:32:59Z

1

It would be great to add checks after $stmt->execute, ie.

 $stmt->execute(@insert_data[1..6], $record->{template}) or die $stmt->errstr;

use strict; can also be helpful.

answered May 16, 2013 at 12:32

mpapec

50.8k8 gold badges72 silver badges133 bronze badges

Comments

tjd · Accepted Answer · 2013-05-16 12:29:17Z

0

I notice you're not giving us any error messages, but I'll presume this info may be useful:

@array_name[integer] is a single element array slice. $array_name[integer] is a scalar value from within the array.

I'd recommend you pass values into the execute statement instead of arrays.

answered May 16, 2013 at 12:29

tjd

4,1041 gold badge27 silver badges34 bronze badges

Collectives™ on Stack Overflow

perl arrays in postgresql insert statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related