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
-- 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
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.
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
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
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 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
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
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(
qinsert 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
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
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.
