Definitely always use placeholders; even more, as the OP mentions an ORA error - sign that Oracle is the underlying DB - I'd go with something like the following:
my ($db_ssn, $db_lastname, $db_birth_year, $db_store);
my $sql = <<'END_SQL';
SELECT ssn, RTRIM(last_name,''),TO_CHAR(birth_date,'YYYY'),store
FROM pyhadmin.py_emp_shadow
WHERE TO_CHAR(birth_date,'YYYY') LIKE :birth_year
AND ssn LIKE :ssn
AND RTRIM(last_name,' ') LIKE UPPER(:last_name)
AND rownum <= 1
END_SQL
eval {
my $sth = $dbh->prepare($sql);
$sth->bind_param( ':birth_year', $year, ORA_NUMBER );
$sth->bind_param( ':ssn', $ssn, ORA_NUMBER );
$sth->bind_param( ':last_name', $last_name, ORA_VARCHAR2);
$sth->execute();
($db_ssn, $db_lastname, $db_birth_year, $db_store)
= $sth->fetchrow_array;
$sth->finish();
};
if ($@) {
# there was an fatal error; die() if you want,
# but maybe you can do something to recover and go on ,
# log something, etc.
}
Have a look on DBD::Oracle for this special form of placeholders.
These aren't portable, so avoid them if your code is going to run on other DBs, but as long as you're on Oracle, the code is much easier to read/maintain like this. Also, it is a lot harder to make (typical, sometimes hard to debug) errors like swapping values for the placeholders - think about more complex queries, where you would need to (re)use the same value in more then one place.
| [reply]
[d/l] |
Just to be clear with your example, you are "quoting" SSN? So if I dont need to both SSN rather just quote the lastname, I just change the $SSN values near the top to $name? | [reply] |
| [reply] |