Re: inserting data coming from a hash
-- Robert <[EMAIL PROTECTED]> This is my hash structure: "Veterans Day" => { date=> '2005', type=> 'US', federal => 'true', active => 'true', }, Would I just use a placeholder (?) in my statement and pass it in via that? It will be in a loop as I have quite a few. Thoughts and suggestions would be appreciated. Aside: If you can live with re-popluating the same hash you could also try binding specific hash values to the placeholders via bind_inout and re-cycling the hash iteslf. This may prove more trouble than just slicing out the values, however. -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
Re: inserting data coming from a hash
-- Robert <[EMAIL PROTECTED]> This is my hash structure: "Veterans Day" => { date=> '2005', type=> 'US', federal => 'true', active => 'true', }, Would I just use a placeholder (?) in my statement and pass it in via that? It will be in a loop as I have quite a few. Use a hash slice to extract the values in order: # @fields is the collection of keys from your # data that match the order of your query. # # Nice thing about this trick is that you # can have more keys in the data than you # actually insert (e.g., the data may require # more than one query to insert). my @fieldz = qw( date type federal active ); my $sth = $dbh->prepare ( q{ insert blah blah values ( ?, ?, ?, ? ) } ); ... # sometime later you set up a hash[ref] of # stuff to insert and just hash-slice it # to get what you need out: my $data = { date=> ... , type=> ... , federal => ... , active => ... , foo => ... , # these don't hurt anything bar => ... , # since @fields doesn't include them. } $sth->execute( @[EMAIL PROTECTED] ); -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
Re: inserting data coming from a hash
On Thu, 21 Apr 2005 09:36:33 -0400, Robert wrote: >This is my hash structure: > >"Veterans Day" => { >date=> '2005', >type=> 'US', >federal => 'true', >active => 'true', > }, > >Would I just use a placeholder (?) in my statement and pass it in via that? Use DBIx::Simple, it becomes a one-liner. Well, almost: you don't have an entry for the holiday name. $db->insert('mytable', { name => $key, date=> '2005', type=> 'US', federal => 'true', active => 'true', }); DBIx::Simple takes all the manual work out of your hand, prepares the statement, exectutes it (with placeholders)... and keeps a cache of prepared statements, so it's virtually as efficient as if you had built it by hand. -- Bart.
Re: inserting data coming from a hash
On 04/21/2005 10:11 AM, Robert said: This was "a" solution: my $insert_stmt = "Insert into TABLE (name, date, type, federal, active) values( :name, :date, :type, :federal, :active )"; my $sth = $dbh->prepare( $insert_stmt ); my $holidays; foreach my $name ( keys %holidays ) { $sth->bind_param( ':name', $name ); $sth->bind_param( ':'.$_, $holidays->{$name}->{$_} ) foreach keys %{$holidays->{$name}}; $sth->execute() or die "Cannot execute SQL statement: $DBI::errstr\n"; } This may only work with DBD::Oracle. You also have to be very careful to have exactly the same hash keys as your bind variable names. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: inserting data coming from a hash
David, Your solution is similar to mine. But I like your use of the hash slice better. I keep forgetting about those darn slices. -Original Message- From: David [mailto:[EMAIL PROTECTED] Sent: Thursday, April 21, 2005 6:53 AM To: dbi-users@perl.org Subject: Re: inserting data coming from a hash A placeholder only holds one place. You would need to have a placeholder for each value you wanted to insert. Something like my $sth = $dbh->prepare('insert into holiday (' . join(',', sort keys %hash) . ') values (?, ?, ?, ?)') || die "prepare failed"; $sth->execute(@hash{sort keys %hash}) || die "execute failed"; if your hash keys correspond to your column names. dd -- David Dooling
RE: inserting data coming from a hash
If I understand your question, it seems like you would want to do: $sth = $dbh->prepare(q{ insert into tab (date,type,federal,active) values (?,?,?,?); }); Then in a loop: $dref = $hash{$d}; # $d has "Veterans day" in one iteration $sth->execute($dref->date,$dref->type,$dref->federal,$dref->active); OR, if you want to improve management of your members, consider the following. This assumes member names = your column names, and ensures the bind order is in sync with the SQL column order. my @members = qw{date type federal active}; #define members $sth = $dbh->prepare( q"insert into tab (" . $join(",", $members) . q") values (". $join(",", "?" x @members) . q");" ); Then in a loop: $dref = $hash{$d}; # $d has "Veterans day" in one iteration my $i = 1; $sth->bind_param($i++, $d->{$_}) foreach @members; $sth->execute(); Warning. This is untested code. But hopefully you get the idea. Was this the kind of answer that you were looking for? -Original Message- From: Robert [mailto:[EMAIL PROTECTED] Sent: Thursday, April 21, 2005 6:37 AM To: dbi-users@perl.org Subject: inserting data coming from a hash This is my hash structure: "Veterans Day" => { date=> '2005', type=> 'US', federal => 'true', active => 'true', }, Would I just use a placeholder (?) in my statement and pass it in via that? It will be in a loop as I have quite a few. Thoughts and suggestions would be appreciated. Robert
Re: inserting data coming from a hash
This was "a" solution: my $insert_stmt = "Insert into TABLE (name, date, type, federal, active) values( :name, :date, :type, :federal, :active )"; my $sth = $dbh->prepare( $insert_stmt ); my $holidays; foreach my $name ( keys %holidays ) { $sth->bind_param( ':name', $name ); $sth->bind_param( ':'.$_, $holidays->{$name}->{$_} ) foreach keys %{$holidays->{$name}}; $sth->execute() or die "Cannot execute SQL statement: $DBI::errstr\n"; }
Re: inserting data coming from a hash
On 4/21/05, Robert <[EMAIL PROTECTED]> wrote: > This is my hash structure: > > "Veterans Day" => { > date=> '2005', > type=> 'US', > federal => 'true', > active => 'true', > }, > > Would I just use a placeholder (?) in my statement and pass it in via that? > It will be in a loop as I have quite a few. assuming your holidays are a HoH called %Holidays, you'd use five placeholders and call execute ($HolidayName, @{$Holidays{$HolidayName}}{qw/date type federal active/}); if you don't mind saving a wee bit of programmer time and spending more hardware effort you might be able to use Tie::DBI to allow something like $HolidayDatabase{$HolidayName} = $Holidays{$HolidayName}; provided the key names (and the boolean representations) match. -- David L Nicol $ perl -wc -e 'use strict; the deer and antelope play ; print 1' -e syntax OK
Re: inserting data coming from a hash
On Thu, Apr 21, 2005 at 09:36:33AM -0400, Robert wrote: > This is my hash structure: > > "Veterans Day" => { > date=> '2005', > type=> 'US', > federal => 'true', > active => 'true', > }, > > Would I just use a placeholder (?) in my statement and pass it in via that? > It will be in a loop as I have quite a few. A placeholder only holds one place. You would need to have a placeholder for each value you wanted to insert. Something like my $sth = $dbh->prepare('insert into holiday (' . join(',', sort keys %hash) . ') values (?, ?, ?, ?)') || die "prepare failed"; $sth->execute(@hash{sort keys %hash}) || die "execute failed"; if your hash keys correspond to your column names. dd -- David Dooling
Re: inserting data coming from a hash
On Thu, 21 Apr 2005 09:36:33 -0400, "Robert" said: > This is my hash structure: > > "Veterans Day" => { > date=> '2005', > type=> 'US', > federal => 'true', > active => 'true', > }, > > Would I just use a placeholder (?) in my statement and pass it in via > that? > It will be in a loop as I have quite a few. Well, you can't pass a hash into a statement directly, so no. But you can and should use placeholders to pass in the hash values. What does your SQL statement look like? Mike
inserting data coming from a hash
This is my hash structure: "Veterans Day" => { date=> '2005', type=> 'US', federal => 'true', active => 'true', }, Would I just use a placeholder (?) in my statement and pass it in via that? It will be in a loop as I have quite a few. Thoughts and suggestions would be appreciated. Robert