From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 =<<"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
Not too difficult...
--------------------------------------------------
# Include the following in all scripts.
use strict;
use warnings;
use DBI;
my ($user, $pass, $sid) = @ENV{qw{ORACLE_USER ORACLE_PASS ORACLE_SID}};
my $dbh = DBI->connect("dbi:Oracle:$sid", $user, $pass,
{RaiseError => 1})
or die "Failed to connect to DB: $DBI::errstr\n";
eval {
my $sql = qq{select sysdate beg_date from dual union
select sysdate -1 beg_date from dual};
my $sth = $dbh->prepare($sql);
$sth->execute;
my $dates_ref = $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 = qq{select agrmt_id from agreements
where date_id = '$date'};
print "$sql\n";
}
};
die $@ if $@;
--------------------------------------------------
Alternatively, investigate parameter binding. In any event, see 'perldoc
DBI'.
HTH
--
Brian Raven
=========================================
Atos Euronext Market Solutions Disclaimer
=========================================
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 immediately
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 London
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 75009 Paris
France.
L'information contenue dans cet e-mail est confidentielle et uniquement
destinee a la (aux) personnes a laquelle (auxquelle(s)) elle est adressee.
Toute copie, publication ou diffusion de cet email est interdite. Si cet e-mail
vous parvient par erreur, nous vous prions de bien vouloir prevenir
l'expediteur immediatement et d'effacer le e-mail et annexes jointes de votre
systeme. Le contenu de ce message electronique ne represente pas necessairement
la position ou le point de vue d'Atos Euronext Market Solutions.
Atos Euronext Market Solutions Limited Société de droit anglais, enregistrée au
Royaume Uni sous le numéro 3962327, dont le siège social se situe 25 Bank
Street E14 5NQ Londres Royaume Uni.
Atos Euronext Market Solutions SAS, société par actions simplifiée, enregistré
au registre dui commerce et des sociétés sous le numéro 425 100 294 RCS Paris
et dont le siège social se situe 6/8 Boulevard Haussmann 75009 Paris France.
=========================================
_______________________________________________
ActivePerl mailing list
ActivePerl@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
