Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

bind_param - confusion ?

by premjhere (Initiate)
on Jul 17, 2002 at 05:56 UTC ( #182344=perlquestion: print w/replies, xml ) Need Help??
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)";
Now the bind variable :KEIZIBAN appears twice. is it enough if i say
$sth->bind_param(':KEIZIBAN ',$KEIZIBAN , {ora_type=>ORA_VARCHAR2});
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});
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))"

    (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.)

Re: bind_param - confusion ?
by premjhere (Initiate) on Jul 17, 2002 at 08:46 UTC
    $sql = select a , b , c from test where (a = :var1 and b = :var2) or +(a = :var1 and c = :var3)
    is this enough ?
    $sth->bind_param(':var1',$var1 , {ora_type=>ORA_VARCHAR2}); $sth->execute();
    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();
    I am novice Perl monk. can u kindly explain me with an example or how should my code be changed to ?
      Use placeholders - it'll save you a lot of typing.


        To elaborate on this point, if you use placeholders your code will look like this:
        select something from sometable where foo = ? and bar = ?
        You would then prepare the statement as normal, but when executing, your execute clause will look like:$sth->execute(@bindvar);
        nice and simple.. :-)
      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)
      is equivalent to a statement like this:
      select a , b , c from test where a = :var1 and ( b = :var2 or c = :var3 )

      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 );
      or, more optimally:
      $sth = $dbh->prepare("select a from b where c=? and (d=? or f=?)"); $sth->execute( $cval, $dval, $fval );

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://182344]
Approved by graff
and the radiator hisses contentedly...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (17)
As of 2018-03-23 15:20 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (294 votes). Check out past polls.