Hi Brian,
My example wasn't the best, as you point out. What I've done recently with
a similar problem was save all new sth's in a hash. My problem was
slightly different in that I wasn't dealing with nulls, but with a badly
normalised schema. I needed to build a querry on a table and select from a
different column dependant on the data. It went something like this:
# from a package that builds a sub class of a DBI object....
sub get_from_shortcode {
my $self=shift;
my $dbh=$self->{dbh};
my ($ShortCode, $MesgLenCat, $DIWTYPE)[EMAIL PROTECTED];
my $error;
my $sth=$self->{"preparedStatements"}->
{"get_from_shortcode_$MesgLenCat"};
unless ($sth) {
print "Preparing new statement..." if DEBUGGING;
my $prepare_string = qq {
select price_class_${MesgLenCat}, ocean_cc,
provider_id
from shortcode
where shortcode = ?
};
eval {$sth=$dbh->prepare($prepare_string)};
if ($sth) {
# store it for future use
$self->{"preparedStatements"}->
{"get_from_shortcode$MesgLenCat"}=$sth;
print "done\n" if DEBUGGING;
} else {
$error=$dbh->errstr();
$self->disconnect;
die "get_from_shortcode: Failed to prepare:
$prepare_string: $@ $error\n";
}
}
# we now have a prepared statement
.
code continues.....
I see your point that you need to identify all the possibilities and
pre-prepared them, but here you only prepare what you need, and then of
course, only once. All you need to do is work out a naming convention so
that you can save the sth's in the hash.
Note: $self->disconnect calls this sub to destroy all the prepared
statements before disconnecting.
sub disconnect {
my $self=shift;
my $dbh=$self->{dbh};
for my $key (keys %{$self->{"preparedStatements"}}) {
print "destroying sth $key..." if DEBUGGING;
undef($self->{"preparedStatements"}->{$key});
print "done\n" if DEBUGGING;
}
$dbh->disconnect;
}
Regards
Tony Adolph
"CAMPBELL, BRIAN
D (BRIAN)" To: [EMAIL PROTECTED], "CAMPBELL,
BRIAN D (BRIAN)" <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc: [EMAIL PROTECTED], [EMAIL
PROTECTED], "Bretz, Ellen"
com> <[EMAIL PROTECTED]>, "GOMEZ, CYNTHIA M
(CYNTHIA)" <[EMAIL PROTECTED]>,
Paul Weinstein <[EMAIL PROTECTED]>,
"TOMAS, ROGER (ROGER)" <[EMAIL PROTECTED]>
07.05.2004 02:05 Subject: RE: null values
I'm not sure how your "unless" helps.
First, be advised that the prepare statement is NOT in the Column loop.
It's in the general loop that in which the "execute" statement is also
called. Note it may not seem that way, as my mail composer seems to have
taken out a few newlines in my code, making it harder to read. Also be
advised that the example does not show the sql string being re-initialized
with the fixed part of the statement; you have to add that in.
You must re-do the prepare each time because the sql statement potentially
changes on each iteration of the main loop. An "unless" would only make
sense if you kept track of which fields were null on the previous
iteration. If they were the same on the current iteration, then the $sth
is still good.
Then you would use "unless $nulls_the_same_as_prev_iteration".
Other alternatives:
1. Prepare in advance all the null/not-null combinations for each column
that allows nulls. If you have 2 such columns, then you have 4
combinations. Not a bad way to go. You only need 4 prepare calls, done
prior to the loop. If you have 8 such columns, you have 256 combinations.
Not a good deal if your app typically does 10 executes. If your app does
1000's of executes, then you have a clear execution payoff.
2. If you have the opportunity to analyze all of your data before hand, you
could reduce the number of combinations to just those prepared $sth's that
you actually need.
3. Your best bet? Use "lazy evaluation". You could prepare your $sth's in
the main loop as needed, and reuse $sth's for combinations have already
been created. Now that would be some fun code to write!
Questions:
Is there a hard limit to the number of statement handles that a database
handle can have? How much memory will each one use and do you have enough
memory?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 9:26 AM
To: CAMPBELL, BRIAN D (BRIAN)
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bretz, Ellen; GOMEZ, CYNTHIA
M (CYNTHIA); Paul Weinstein; TOMAS, ROGER (ROGER)
Subject: RE: null values
RE preparing muliple times:
You could do the following (unless $sth) :
my $sth; # <- must be outside the for loop
for (my $i = 0; $i <= $#columns; $i++)
{
$sql .= " AND " . $columns[$i] .
(defined($fields[$i])? " = ?" : " IS NULL") . "\n"; }
$sth = $dbh->prepare($sql) unless $sth;
$sth->bind_param(1,$pro_prospect_id);
.
.
.
.
