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

Monks, i have an SQL statement like this . 
$sql = "select count (*) from MSG where (Janl = :KEIZIBAN and sex = :o
+p_sex and flag = 1 and EntPC = :EntPC and Pref = :BFLAG ) or (Janl = 
+:KEIZIBAN and sex = :op_sex and flag = 2 and EntPC = :EntPC and ID  >
+ 0 and Pref = :BFLAG)";

[download]
Now the bind variable :KEIZIBAN appears twice. is it enough if i say 
$sth->bind_param(':KEIZIBAN ',$KEIZIBAN , {ora_type=>ORA_VARCHAR2});

[download]
or should i use the above statement twice : 
$sth->bind_param(':KEIZIBAN ',$KEIZIBAN , {ora_type=>ORA_VARCHAR2});
$sth->bind_param(':KEIZIBAN ',$KEIZIBAN , {ora_type=>ORA_VARCHAR2});

[download]
kindly explain me monks.

Replies are listed 'Best First'.
Re: bind_param - confusion ?
by graff (Chancellor) on Jul 17, 2002 at 06:07 UTC
    Why not re-write the sql so that the common conditions are stated only once -- then the question doesn't have to come up... 
    "select count(*) from MSG where Jan1= :KEIZIBAN and
 sex= :op_sex and EntPC= :EntPC and Pref= :BFLAG and
 (flag=1 or (flag=2 and ID > 0))"

    [download]

    (p.s.: This is the first time I've seen initial colon instead of initial+final single quotes around condition values in a where clause -- but if that works for you, great.)

[reply]
[d/l]
Re: bind_param - confusion ?
by premjhere (Initiate) on Jul 17, 2002 at 08:46 UTC
    Graff, 
    $sql = select a , b , c  from test where (a = :var1 and b = :var2) or 
+(a = :var1 and c = :var3)

    [download]
    is this enough ? 
    $sth->bind_param(':var1',$var1 , {ora_type=>ORA_VARCHAR2});
$sth->execute();

    [download]
    or since :var1 is repeated twice,should i make the code as below ? 
    $sth->bind_param(':var1',$var1 , {ora_type=>ORA_VARCHAR2});
$sth->bind_param(':var1',$var1 , {ora_type=>ORA_VARCHAR2});
$sth->execute();

    [download]
    I am novice Perl monk. can u kindly explain me with an example or how should my code be changed to ?
    TIA.
[reply]
[d/l]
[select]
      Use placeholders - it'll save you a lot of typing.

      rdfield

[reply]
        To elaborate on this point, if you use placeholders your code will look like this: 
        select something
from   sometable
where  foo = ? 
and    bar = ?

        [download]
        You would then prepare the statement as normal, but when executing, your execute clause will look like:$sth->execute(@bindvar); nice and simple.. :-)
[reply]
[d/l]
[select]
      My original point is that a statement like this: 
      select a , b , c  from test 
   where (a = :var1 and b = :var2) 
      or (a = :var1 and c = :var3)

      [download]
      is equivalent to a statement like this: 
      select a , b , c  from test 
   where  a = :var1 and ( b = :var2 or c = :var3 )

      [download]

      The difference isn't a matter of Perl experience; it's just that the latter query is simpler, easier to maintain, quicker to type, and maybe even faster on execution (if your DBMS isn't very good at optimizing queries). And you don't have to worry about whether you need to repeat a bind_param statement for a repeated value.

      I honestly don't know whether you need to repeat bind_param when using the same variable more than once in a statment, because I use the simplest form of statement I can, always.

      Anyway, I think the other answers about using the "?" place holder for bindings will be useful for you; note the following examples, which should both work, and yield the same result: 

      $sth = $dbh->prepare("select a from b where (c=? and d=?) or (c=? and 
+f=?)");
$sth->execute( $cval, $dval, $cval, $fval );

      [download]
      or, more optimally: 
      $sth = $dbh->prepare("select a from b where c=? and (d=? or f=?)");
$sth->execute( $cval, $dval, $fval );

      [download]

      (update fixed the prepare calls in the closing examples.)

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom