This is a multi-part message in MIME format.
--===============1463398586==
Content-class: urn:content-classes:message
Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C793C8.BDB0332B"
This is a multi-part message in MIME format.
------_=_NextPart_001_01C793C8.BDB0332B
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
> From: Praveen G Siddavarapu
> Sent: Thursday, May 10, 2007 8:45 PM
> To: activeperl [at] listserv.ActiveState.com
> Subject: how to run a sql by passing variable
>
>
> All
>
> I have the below sql, it returns two rows, and I need to pass these
> two dates and run another sql, how do I do that.. basically I need to
> run the sql two times with two different time periods
>
> $csr_dates =3D<<"END_OF_SQL";
> SELECT sysdate beg_date
> from dual
> union
> select sysdate -1 beg_date
> from dual
> END_OF_SQL
>
> $csr_dates =3D $dbh->prepare($sql_dates)
> or die "Prepare of csr_dates failed due to
$DBI::errstr\n$sql_main\nFile: ".__FILE__."\nLine:". __LINE__.
> "\n";
> $csr_dates->execute
> or die "Execute of csr_dates failed due to\n";
> ## Fetch ALL the dates data
> $dates_ref =3D $csr_dates->fetchall_arrayref()
> or die "Fetch of csr_dates failed due to $DBI::errstr";
> $csr_dates->finish();
>
> $sql_contracts =3D<<"END_OF_SQL";
> select agrmt_id
> from agreements
> where date_id =3D '$beg_date'
> END_OF_SQL
>
> Praveen
First, as an aside, when posting code, you should *always* copy/paste
the actual code you're using, rather than retyping it (and possibly
introducing additional errors).
Your second SQL statement should be something like:
$sql_contracts =3D <<END_OF_SQL;
select agrmt_id
from agreements
where date_id =3D ?
END_OF_SQL
The "?" is a "placeholder". Notice that it is not surrounded by
quotes -- it's not a string that becomes part of the SQL statement; it
is passed to the SQL engine as a literal value separate from the
statement.
Prepare this statement as usual. Then pass the parameter you wish
to use in the placeholder as an argument to 'execute':
$st_handle->execute($dates_ref[0][1]);
See
http://search.cpan.org/~timb/DBI-1.55/DBI.pm#Placeholders_an d_Bind_Value
s
HTH,
Eric
P.S.: You are maybe not aware that die() includes the file name and
line number by default?
------_=_NextPart_001_01C793C8.BDB0332B
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<STYLE type=3Dtext/css>DIV {
MARGIN: 0px
}
</STYLE>
<META content=3D"MSHTML 6.00.6000.16414" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>> From: Praveen G
Siddavarapu<BR>> Sent: Thursday, May 10, 2007 8:45 PM<BR>> To: <A
href=3D"mailto:activeperl [at] listserv.ActiveState.com">activepe rl [at] listserv.A=
ctiveState.com</A><BR>>
Subject: how to run a sql by passing variable<BR>> <BR>> <BR>>
All<BR>> <BR>> I have the below sql, it returns two rows, and I =
need to
pass these<BR>> two dates and run another sql, how do I do =
that..
basically I need to<BR>> run the sql two times with two different =
time
periods<BR>> <BR>> $csr_dates =3D<<"END_OF_SQL";<BR>> =
SELECT
sysdate beg_date<BR>> from dual<BR>> union<BR>> select sysdate =
-1
beg_date<BR>> from dual<BR>> END_OF_SQL<BR>> <BR>> =
$csr_dates =3D
$dbh->prepare($sql_dates)<BR>> or die "Prepare =
of
csr_dates failed due to $DBI::errstr\n$sql_main\nFile: =
".__FILE__."\nLine:".
__LINE__.<BR>> "\n";<BR>> =
$csr_dates->execute<BR>>
or die "Execute of csr_dates failed due to\n";<BR>> ## Fetch ALL the =
dates
data<BR>> $dates_ref =3D
$csr_dates->fetchall_arrayref()<BR>> or die =
"Fetch of
csr_dates failed due to $DBI::errstr";<BR>> =
$csr_dates->finish();<BR>>
<BR>> $sql_contracts =3D<<"END_OF_SQL";<BR>> select =
agrmt_id<BR>>
from agreements<BR>> where date_id =3D '$beg_date'<BR>>
END_OF_SQL<BR>> <BR>> Praveen </FONT></DIV>
<DIV> </DIV><FONT face=3DArial color=3D#0000ff size=3D2>
<DIV><BR>First, as an aside, when posting code, you should *always*
copy/paste<BR>the actual code you're using, rather than retyping it (and =
possibly<BR>introducing additional errors).</DIV>
<DIV> </DIV>
<DIV>Your second SQL statement should be something like:</DIV>
<DIV> </DIV>
<DIV> $sql_contracts =3D
<<END_OF_SQL;<BR> select
agrmt_id<BR> from =
agreements<BR>
where date_id =3D ?<BR> END_OF_SQL</DIV>
<DIV> </DIV>
<DIV>The "?" is a "placeholder". Notice that it is not surrounded
by<BR>quotes -- it's not a string that becomes part of the SQL =
statement;
it<BR>is passed to the SQL engine as a literal value separate from
the<BR>statement.</DIV>
<DIV> </DIV>
<DIV>Prepare this statement as usual. Then pass the parameter you
wish<BR>to use in the placeholder as an argument to 'execute':</DIV>
<DIV> </DIV>
<DIV> =
$st_handle->execute($dates_ref[0][1]);</DIV>
<DIV> </DIV>
<DIV>See <A
href=3D"http://search.cpan.org/~timb/DBI-1.55/DBI.pm#Placeho lders_and_Bin=
d_Values">http://search.cpan.org/~timb/DBI-1.55/DBI.pm#Place holders_and_B=
ind_Values</A></DIV>
<DIV> </DIV>
<DIV>HTH,<BR>Eric</DIV>
<DIV> </DIV>
<DIV>P.S.: You are maybe not aware that die() includes the file name =
and<BR>line
number by default?<BR></FONT></DIV></BODY></HTML>
------_=_NextPart_001_01C793C8.BDB0332B--
--===============1463398586==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
ActivePerl mailing list
ActivePerl [at] listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
--===============1463398586==--
