Re^5: Quick 'Quote' DBI Question

I think I understand this a lot better now, thank you.
I have one more related quick question.
In my first statement the where on SSN has a % sign on it, because the user only enters the last 4 digits, how do I do this with placeholders?
The original line was:and SSN like '%$ssn'
Here is my new SQL with placeholders:

my $sql = <<"END"
SELECT
  SSN, RTRIM(LAST_NAME,' '),
  TO_CHAR(BIRTH_DATE,'YYYY'),
  STORE
FROM
  PYHADMIN.PY_EMP_SHADOW
where
      TO_CHAR(BIRTH_DATE,'YYYY') like ?
  and SSN like ?
  and RTRIM(LAST_NAME,'  ') like upper(?)
  and rownum <= 1
END
;
my $yi = $dbh->prepare($sql);
$yi->execute($year, $ssn, $name);
#--------Send the values from the query into variables
($SSNc, $LAST_NAMEc, $YEARc, $storenum) = $yi->fetchrow_array;
[download]

Comment on Re^5: Quick 'Quote' DBI Question Select or Download Code

Replies are listed 'Best First'.
Re^6: Quick 'Quote' DBI Question by Joost (Canon) on Apr 06, 2007 at 15:02 UTC
IIRC you will have to put the "%" sign in the the argument to execute() yourself, since placeholders only handle quoting, they don't try to parse anything else: `$yi->execute($year,"%".$ssn,$name);` [download] Note that that also means you have to be careful about what's in $ssn - if someone manages to put "_" or "%" characters in $ssn the query might no longer do what you think it should. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re^7: Quick 'Quote' DBI Question by Trihedralguy (Pilgrim) on Apr 06, 2007 at 15:05 UTC
Sweet! I have it where SSN can be numbers only by doing something like: `if ($ssn =~ /^[0-9]\z/ && $year =~ /^[0-9]\z/) {`	[reply] [d/l]
Re^8: Quick 'Quote' DBI Question by ww (Archbishop) on Apr 06, 2007 at 15:16 UTC
ooops. update The "oops" is on me, that is. Missed that there are supposed to be only four digits. That leaves you needing a small fix to your regex to deal with cases when the SSN is entered with hyphens, as 123-45-6789 However, you might still want to make sure that's the case, by using a numeric quantifier instead of the deathstar `s/if ($ssn =~ /^[0-9]*\z/if ($ssn =~ /^[0-9]{4}\z/`	[reply] [d/l]
Re^9: Quick 'Quote' DBI Question by Trihedralguy (Pilgrim) on Apr 06, 2007 at 15:34 UTC