how to run a sql by passing variable

--===============1639756963==
Content-Type: multipart/alternative; boundary="0-511145323-1178844294=:71345"

--0-511145323-1178844294=:71345
Content-Type: text/plain; charset=ascii

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 =<<"END_OF_SQL";
SELECT sysdate beg_date
from dual
union
select sysdate -1 beg_date
from dual
END_OF_SQL

$csr_dates = $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 = $csr_dates->fetchall_arrayref()
or die "Fetch of csr_dates failed due to $DBI::errstr";
$csr_dates->finish();

$sql_contracts =<<"END_OF_SQL";
select agrmt_id
from agreements
where date_id = '$beg_date'
END_OF_SQL

Praveen
--0-511145323-1178844294=:71345
Content-Type: text/html; charset=ascii

RE: how to run a sql by passing variable

From: activeperl-bounces@listserv.ActiveState.com
[mailto:activeperl-bounces@listserv.ActiveState.com] On Behalf Of
Praveen G Siddavarapu
Sent: 11 May 2007 01:45
To: activeperl@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

Not too difficult...

--------------------------------------------------
# Include the following in all scripts.
use strict;
use warnings;

use DBI;

my ($user, $pass, $sid) =3D @ENV{qw{ORACLE_USER ORACLE_PASS ORACLE_SID}};

my $dbh =3D DBI->connect("dbi:Oracle:$sid", $user, $pass,
{RaiseError =3D> 1})
or die "Failed to connect to DB: $DBI::errstr\n";

eval {
my $sql =3D qq{select sysdate beg_date from dual union
select sysdate -1 beg_date from dual};
my $sth =3D $dbh->prepare($sql);
$sth->execute;
my $dates_ref =3D $sth->fetchall_arrayref;
# We have a ref to an array of array refs, one per row.
foreach my $date (map {$_->[0]} @$dates_ref) {
my $sql =3D qq{select agrmt_id from agreements
where date_id =3D '$date'};
print "$sql\n";
}
};
die $@ if $@;
--------------------------------------------------

Alternatively, investigate parameter binding. In any event, see 'perldoc
DBI'.

HTH

-- =

Brian Raven =


==================== =====3D=
================
Atos Euronext Market Solutions Disclaimer
==================== =====3D=
================

The information contained in this e-mail is confidential and solely for the=
intended addressee(s). Unauthorised reproduction, disclosure, modification=
, and/or distribution of this email may be unlawful.
If you have received this email in error, please notify the sender immediat=
ely and delete it from your system. The views expressed in this message do =
not necessarily reflect those of Atos Euronext Market Solutions.

Atos Euronext Market Solutions Limited - Registered in England & Wales with=
registration no. 3962327. Registered office address at 25 Bank Street Lon=
don E14 5NQ United Kingdom. =

Atos Euronext Market Solutions SAS - Registered in France with registration=
no. 425 100 294. Registered office address at 6/8 Boulevard Haussmann 750=
09 Paris France.

L'information contenue dans cet e-mail est confidentielle et uniquement des=
tinee a la (aux) personnes a laquelle (auxquelle(s)) elle est adressee. Tou=
te copie, publication ou diffusion de cet email est interdite. Si cet e-mai=
l vous parvient par erreur, nous vous prions de bien vouloir prevenir l'exp=
editeur immediatement et d'effacer le e-mail et annexes jointes de votre sy=
steme. Le contenu de ce message electronique ne represente pas necessaireme=
nt la position ou le point de vue d'Atos Euronext Market Solutions.
Atos Euronext Market Solutions Limited Soci=E9t=E9 de droit anglais, enregi=
str=E9e au Royaume Uni sous le num=E9ro 3962327, dont le si=E8ge social se =
situe 25 Bank Street E14 5NQ Londres Royaume Uni.

Atos Euronext Market Solutions SAS, soci=E9t=E9 par actions simplifi=E9e, e=
nregistr=E9 au registre dui commerce et des soci=E9t=E9s sous le num=E9ro 4=
25 100 294 RCS Paris et dont le si=E8ge social se situe 6/8 Boulevard Hauss=
mann 75009 Paris France.
==================== =====3D=
================

_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs

RE: how to run a sql by passing variable

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@listserv.ActiveState.com
> Subject: how to run a sql by passing variable
>=20
>=20
> All
>=20
> 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
>=20
> $csr_dates =3D<<"END_OF_SQL";
> SELECT sysdate beg_date
> from dual
> union
> select sysdate -1 beg_date
> from dual
> END_OF_SQL
>=20
> $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();
>=20
> $sql_contracts =3D<<"END_OF_SQL";
> select agrmt_id
> from agreements
> where date_id =3D '$beg_date'
> END_OF_SQL
> =20
> Praveen=20
=20

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).
=20
Your second SQL statement should be something like:
=20
$sql_contracts =3D < select agrmt_id
from agreements
where date_id =3D ?
END_OF_SQL
=20
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.
=20
Prepare this statement as usual. Then pass the parameter you wish
to use in the placeholder as an argument to 'execute':
=20
$st_handle->execute($dates_ref[0][1]);
=20
See
http://search.cpan.org/~timb/DBI-1.55/DBI.pm#Placeholders_an d_Bind_Value
s
=20
HTH,
Eric
=20
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



charset=3Dus-ascii">