Re: inserting data coming from a hash

2005-05-22 Thread Steven Lembark



-- 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

2005-05-22 Thread Steven Lembark



-- 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

2005-04-25 Thread Bart Lateur
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

2005-04-21 Thread Michael A Chase
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

2005-04-21 Thread CAMPBELL, BRIAN D (BRIAN)
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

2005-04-21 Thread CAMPBELL, BRIAN D (BRIAN)
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

2005-04-21 Thread Robert
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

2005-04-21 Thread David Nicol
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

2005-04-21 Thread David
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

2005-04-21 Thread Michael Styer
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

2005-04-21 Thread Robert
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