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);
.
.
.
.
Not sure about the bind_param, but I should think something similar should
be done (?)
Cheers
Tony Adolph
"CAMPBELL, BRIAN
D (BRIAN)" To: "Bretz, Ellen" <[EMAIL
PROTECTED]>, [EMAIL PROTECTED]
<[EMAIL PROTECTED] cc: [EMAIL PROTECTED], "TOMAS,
ROGER (ROGER)" <[EMAIL PROTECTED]>, Paul
com> Weinstein <[EMAIL PROTECTED]>, "GOMEZ,
CYNTHIA M (CYNTHIA)" <[EMAIL PROTECTED]>
Subject: RE: null values
06.05.2004 17:54
I posted this solution originally.
Please be aware that this solution does require a "prepare" for each
execute. So if you are running this logic in a loop many times, the run
time will be more expensive than other methods that were posted. You can
monitor the performance of your app and see if that is an issue for you.
The other methods that were posted allow you to construct a "fixed" (i.e.
unchanging) SQL string, using extended features of Oracle (which I believe
are all non-ANSI). That allows you to prepare once, and execute many
times, which more efficient. I would advise you to use the technique below
only if:
- The number of executes is small or performance is not an important issue
in your app.
- Your non-Oracle database does not support any of the other methods, or
portability to other databases is very important.
In my case, I work with Informix databases, and none of the posted methods
are supported in Informix. I checked the documentation, to see if there
was anything similar, and came up empty. My docs are old, so if any one
knows otherwise, I'd like to hear about it. Or maybe I should get new
docs. :-)
With all the disclaimers out of the way, below are the answers to your
questions, and more. Marked with a ">>"
-----Original Message-----
From: Bretz, Ellen [mailto:[EMAIL PROTECTED]
This works great but how do I switch something under @fields from defined
on one select to undefined the next time through.
Having the field null doesn't work.
-----Original Message-----
From: Christopher G Tantalo [mailto:[EMAIL PROTECTED]
I just ran into this problem last week, and got a great solution. I am
re-posting the code that a fellow newsgrouper gave to me for my solution
my $sql = "select dns_id
from dmg.do_not_solicit
where dns_prospect_id = ?\n";
my @columns = qw(
dns_area_code
dns_phone
dns_zip_code
dns_pr_branch
dns_pr_client_nbr
dns_pr_client_status
);
>>
>> The above is designed to be executed once in your program.
>>
my @fields = (
$dnc_areacode,
$dnc_phone,
$dnc_zipcode,
$pro_pr_branch,
$pro_pr_client_nbr,
$pro_pr_client_status
);
>>
>> The above statement is designed to be executed multiple times
>> with each prepare & execute, in order to refresh the array
>> with the latest values. However, it would be better to
>> pair this array up the first, and execute it once. You can do that
>> with references. See an alternate solution for this below...
>>
for (my $i = 0; $i <= $#columns; $i++)
{
$sql .= " AND " . $columns[$i] .
(defined($fields[$i])? " = ?" : " IS NULL") . "\n"; }
my $sth = $dbh->prepare($sql); $sth->bind_param(1,$pro_prospect_id);
my $j = 2;
for (my $i = 0; $i <= $#fields; $i++)
{
$sth->bind_param($j++,$fields[$i]) if (defined(@fields[$i])
>>
>> ................................... ^ Bug! Should be: if defined($fields
[$i]);
>>
}
===================================================
Alt solution, starting with fields, with loop added.
By storing references, you can make changes to the variables, and the sql
contruction
Code will contiue to pick up the latest values in the variables.
Warning. This is untested. Caveat Emptor.
my @fields = (
\$dnc_areacode,
\$dnc_phone,
\$dnc_zipcode,
\$pro_pr_branch,
\$pro_pr_client_nbr,
\$pro_pr_client_status
);
while ($some_condition)
{
# make changes to variables here
for (my $i = 0; $i <= $#columns; $i++)
{
$sql .= " AND " . $columns[$i] .
(defined(${$fields[$i]})? " = ?" : " IS
NULL") . "\n";
}
my $sth = $dbh->prepare($sql);
$sth->bind_param(1,$pro_prospect_id);
my $j = 2;
for (my $i = 0; $i <= $#fields; $i++)
{
$sth->bind_param($j++,${$fields[$i]}) if defined
(${$fields[$i]});
}
# execute, process results, etc. here
}
>> I realize that a more elegant solution might be to combine the two
arrays,
>> columns and fields, into a single array of hashes. I'll leave that as
an
>> "exercise for the reader".
