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); . . . .
