in reply to bind_param - confusion ?

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.

Replies are listed 'Best First'.
Re: Re: bind_param - confusion ?
by rdfield (Priest) on Jul 17, 2002 at 08:56 UTC
    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]
Re: Re: bind_param - confusion ?
by graff (Chancellor) on Jul 18, 2002 at 05:31 UTC
    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]

In Section Seekers of Perl Wisdom