in reply to Re^3: Quick 'Quote' DBI Question
in thread Quick 'Quote' DBI Question

I'm using fetchrow() as a shorthand of fetchrow_array(). I'm pretty sure that's normal usage, but I can't find it documented anywhere. So I'll use fetchrow_array() from here on.

In my example, $result is simply the return value of fetchrow_array() - I.e. a single row of data with a single column. Note that a single query can return multiple rows of results: 

while (my ($col1,$col2) = $sth->fetchrow_array()) {
  # ...
}

[download]
Every iteration of that while loop sets $col1 and $col2 to the first and second columns of the next result row.

Using placeholders doesn't have any direct influence on how you fetch the results. It only influences how you prepare and execute the query before reading the results.

Note that using placeholders like this splits up the process in three distinct steps:

  1. preparing the query (with the question marks as placeholder marking input values). - this gives you a statement handle.
  2. executing the query from the statement handle (where you need to supply the values for each place-holder)
  3. fetching the results of the executed query from the statement handle.
That means you can also prepare a statement only once and then re-execute it with different values (provided you save the statement-handle somewhere - or use the prepare_cached() method). This can speed up querying since the database then doesn't have to parse your query each time - but if it does depends on the implementation.

"What should it profit a man, if he should win a flame war, yet lose his cool?"

Replies are listed 'Best First'.
Re^5: Quick 'Quote' DBI Question
by Trihedralguy (Pilgrim) on Apr 06, 2007 at 14:56 UTC
    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]
[reply]
[d/l]
[select]
      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]
        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]

In Section Seekers of Perl Wisdom