Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
I asked this question before, and the question that still remains is about the actual values of the array after the code runs. When I am printing the values of my $sql ,I have a bunch of "?"(question mark) in my sql statement, is this right, in my understanding the sql code:
SELECT column_name FROM table_name
WHERE column_name IN (value1,value2,..)

[download]

Shoul the value1,value2 and if more, be the values of the @array and not the "?s?(question marks)?
Here is the code, I'm asking what is this code doing, cause I am trying to use it and I dont think it is working.

my $p_str = join ',', ('?') x @array;

[download]

SELECT column_name FROM table_name
WHERE column_name IN ($p_str)# here it's printing question marks, why?
+ Is this correct? Shoul the values here be the valuye from @array and
+ not a bunch of question marks?

[download]


Thank you very much!

Replies are listed 'Best First'.
Re: Explanation of Code Problem
by TedYoung (Deacon) on Jan 04, 2005 at 16:11 UTC

    Hi,

    You may want to read the DBI documentation about prepared statements. A prepared statement is one where instead of actually putting values in the sql, placeholders (?) are used instead. Then the statement can be resused a bunch of times with different data sets. Prepared statements have a number of advantages with regards to security, performance, and reusability all of which are dicussed in the DBI documentation.

    Here is an example:

    $sql = "select colnm from tblnm where colmn in (?,?,?);
$st = $dbh->prepare($sql);

# Select where colmn in (1, 2, 3)
$st->execute(1, 2, 3);

....

# Select where colmn in (4, 5, 6)
$st->execute(4, 5, 6);

    [download]

    And so on. In general, the SQL will not contain the values. The values (@array) are passed in during each execution.

    Hope this helps.

    Ted Young

    ($$<<$$=>$$<=>$$<=$$>>$$) always returns 1. :-)
[reply]
[d/l]
[select]

      Using placeholders (the question marks) has two advantages I can see:

      1) You can reuse the same prepared statement more than once (by binding different values), saving execution time.

      2) You don't have to validate (in the case of numerical args) or escape (in the case of string args) the arguments, as you would have to do if you were including them directly into the SQL statement.

[reply]
      So you're saying that the "?s" that I am seeing when I print the values of $sql just means how many values are in the @array, and that the actual values like you mentioned are been passed literally trough each execution based in the number of elements of the @array?
      Does that sounds like what you're trying to say?
[reply]
        Yep

        Ted Young

        ($$<<$$=>$$<=>$$<=$$>>$$) always returns 1. :-)
[reply]
[d/l]
Re: Explanation of Code Problem
by Grygonos (Chaplain) on Jan 04, 2005 at 16:19 UTC

    I think you're the OP is misunderstanding the use of placeholders here. Typically you would use placeholders like so 

    my $sql = q{SELECT * FROM tblX WHERE condition = ?};

$sth = $dbh->prepare($sql);
$sth->execute($value_to_sub_for_question_mark);

    [download]
    So it seems what you are trying to do is have a long list of placeholders and then specify the values to use for the placeholders sometime before the execute is called. You want the statement to have all the ? marks in it. It doesn't need to be re-prepped that way. (Part of the beauty of placeholders)

    However to do what you are wanting i think you would do this 

    my $sql = q{SELECT * FROM tblX WHERE condition1 = ? and condition2 = ?
+};
my $sth = $dbh->prepare($sql);
$sth->execute(@array);

    [download]
    That is untested and you may need to put the array in list context, but i think that's your intent.. just a guess

    Grygonos
[reply]
[d/l]
[select]
      What I am trying to do is to submit a bunch of, let's say account numbers per example; I am getting values previously from another DB query and the results from this query I am doing a 
      push @claim,$accounts;

      [download]

      and trying to run my sql query on each account number found, and I am just trying to get the better and fastest way to do it.
[reply]
[d/l]

        ITSOTIMTOWDI 

        my $sql = "QUERY WHERE ?";
my $sth = $dbh->prepare($sql);
foreach(@acct)
{
  $sth->execute($_);
  #do some other processing
}

        [download]
        That would be one way to query them all individually

        Grygonos
[reply]
[d/l]

Back to Seekers of Perl Wisdom