Using a multi-level hash in an insert statement...

sigzero has asked for the wisdom of the Perl Monks concerning the following question:

I would like to pass a multi-level hash (which is, of course, better than multi-level marketing) to an insert statement to get some data into a Pg database.

The table is:

name    date    type    federal    active

The hash looks like:

my %holidays = (
    "New Years Day" => {
        date    => '20050101',
        type    => 'US',
        federal => 'true',
        active  => 'true',
    },
    "Martin Luther King Day" => {
        date    => '20050119',
        type    => 'US',
        federal => 'true',
        active  => 'true',
    },
    "Groundhog Day" => {
        date    => '20050202',
        type    => 'US',
        federal => 'false',
        active  => 'false',
    },
);
[download]

I can do simple inserts but I haven't tried anything like this before. How would I code the "insert" statement?

Humbly submitted,

Robert

Comment on Using a multi-level hash in an insert statement... Download Code

Replies are listed 'Best First'.
Re: Using a multi-level hash in an insert statement... by dragonchild (Archbishop) on Apr 21, 2005 at 14:47 UTC
I'll start you off. You have three hash entries, so you want three rows, which means three executions of the same SQL statement. How would you write the three statements out by hand? How do you plan on iterating through the outer hash? (each is probably a good choice) My wife's blog	[reply]
Re: Using a multi-level hash in an insert statement... by sigzero (Novice) on Apr 21, 2005 at 15:25 UTC
Thanks gentlefolk! Robert	[reply]
Re: Using a multi-level hash in an insert statement... by Transient (Hermit) on Apr 21, 2005 at 14:49 UTC
IIRC (untested) `my $statement = "Insert into TABLE (name, date, type, federal, active) + values( ?, ?, ?, ?, ? ); $sth = $dbh->prepare( $statement ); foreach my $name ( keys %holidays ) { $sth->bind_param( 1, $name ); my $i = 2; $sth->bind_param( $i++, $holidays->{$name}->{$_} ) foreach keys %{$h +olidays->{$name}}; $sth->execute(); # check errors here too }` [download] Update: (uses named parameters - not supported in all DB's) `my $statement = "Insert into TABLE (name, date, type, federal, active) + values( :name, :date, :type, :federal, :active ); $sth = $dbh->prepare( $statement ); foreach my $name ( keys %holidays ) { $sth->bind_param( ':name', $name ); $sth->bind_param( ':'.$_, $holidays->{$name}->{$_} ) foreach keys %{ +$holidays->{$name}}; $sth->execute(); # check errors here too }` [download]	[reply] [d/l] [select]
Re^2: Using a multi-level hash in an insert statement... by davidrw (Prior) on Apr 21, 2005 at 15:07 UTC
This line: `$sth->bind_param( $i++, $holidays->{$name}->{$_} ) foreach keys %{$hol +idays->{$name}};` [download] Will cause transposing (e.g. '20050101' to be stored as 'name') of the key/values in the insert, because you can't rely on the order that keys() returns. Unforunately needs to be something like: `$sth->bind_param( $i++, $holidays->{$name}->{$_} ) foreach qw/ name da +te type federal active /;` [download] Or, even better, would be to use SQL::Abstract (disadvantage is that it can't be prepare'd): `use SQL::Abstract; my $sql = SQL::Abstract->new; while( my ($name,$data) = each %holidays ) { my %data = ( %$data, name => $name ); # assuming hash only has key +s corresponding to cols in table my($stmt, @bind) = $sql->insert('your_table_name', \%data); $dbh->do($stmt, {}, @bind); }` [download]	[reply] [d/l] [select]
Re^3: Using a multi-level hash in an insert statement... by Transient (Hermit) on Apr 21, 2005 at 15:09 UTC
Ah yes.. my mistake - forgotten about that sort order bit. using named placeholders - while not supported for all DB's, would work in lieu of this.	[reply]