All,

I need some assistance passing data from an array to an sql statement. I don't want to pass the entire array at one time. I want to iterate through the array and pass the data by array index to a sql statement. I made the mistake of sending an email over the weekend that was not clear and did not contain all the needed info. I am someone that writes complex repuirements specifications as well as manages complex projects. To avoid getting hammered for asking a vague question to see if something could be done, I should have been more clear. Anyway, please read the below description and let me know if you have questions.

Request / Specification for the process:

Step 1: Select a list of id's and load into an array. I don't want to hard code the id's because the list may change.

Step2: Select data for a specific id by passing a single id from the array index to the sql statement.

Step 3: Email the data to a specific id. The email address are simply "id@...com".

Note: This process should iterate through the array by index.

Currently, I perform the following:

Step 1: Select data for a group of id's. The IDs are hard coded for a specific group and selected by hierachy. i.e. 'Select * from table where id = 'VP'';

Step 2: Email data to the group of id's. The email addresses are hard coded.

Note: When I hard code the data, I have to repeat the process for each group.

I am trying to select a list of distinct id's and load them into an array. Iterate through the index and select data for a specific id. And email the data to a specific id. The hard coded group email process works. In terms of the dynamic select...As the below code iterate throught the array, it sends out an email correctly to each id. However when I open the excel files, only the first array index has data from the select statement. All of the email headings, worksheet formats, and attachment labels are correct. It is as if, the sql statement saves the first variable. Any help will be appreciated...Thanks, Cory

############################
#
#   Set the Perl Modules
#
############################

use strict;
use DBI;
use Spreadsheet::WriteExcel::Big;
use Mail::Sender;

my $i = 0;
my $j = 1;.....


###############################
#
#   Create Array_ref for array.
#
###############################

my $rows = $query->fetchall_arrayref;

###############################
#
#   Load ID data in array for indexing.
#   Note: The initial fetching of the array loads into array index 0.
###############################

foreach $i(0..$#{$rows})
{
   foreach $j (0..$#{$rows->[$i]} ) 
   {
    $rows->[$i][$j];
   }
}###################################################################
#
# Wrap the process into a loop to iterate through the array by index.
#
###################################################################

while ($i<=2) 
{
my $placeholder = ":p_$j";

print "$placeholder\n";

my $rd = "$rows->[$i][0]";

print "$rd\n";

#   create a new EXCEL instance

my $Excelfile = "Q4 $rd PERFORMANCE RPT.xls"; 
my $excel = Spreadsheet::WriteExcel::Big->new("$Excelfile");
my $worksheet = $excel->addworksheet("Q4_STORE");


###Create Subroutine to Select Data and insert into Worksheet###

sub get_store_data {

#  Connect to Oracle database.

my $dbh = DBI->connect('dbi:Oracle:xxxxx','xxxxx','xxxxx',{
    AutoCommit => 0,
    RaiseError => 1,
    PrintError => 1,
    }) 
|| die "Database connection not made: $DBI::errstr";

#DBI-> trace(2);

#   Set up Query for Specific ID.

my $stmt = "select 
                  week_end_date, SVP, RD,
                  DM, store, wtd_smrr_gain,QTD_SMRR_GAIN,
                  wtd_bor_gain,QTD_BOR_GAIN,
                  wtd_cust_gain,QTD_CUST_GAIN,
                  WTD_CARD_CLOSED,QTD_AVG_CARD_CL
             from 
                   bonus_4Q_store
             where
                   SVP <> 'RD' and RD = ?
             order by
                   svp,dm,store";   

#   Prepare Query

my $sth = $dbh->prepare($stmt);

#   Bind Parameters.

#$sth->bind_param($placeholder, $rd);

#   Execute Query

$sth->execute($rd) or die $dbh->errstr;L);

#   Create an array reference.

my $a_row = $sth->fetchall_arrayref();

#  insert Data into spreadsheet

foreach $stmt (@{$a_row}) 
{ 
   $worksheet->write_col(14,0,$a_row,$format); 
   last;
} 

#  Finish part 1

$sth->finish();
$dbh->rollback;
$dbh->disconnect();

}###end of function1###

#  Call Functions / Subroutines

&get_store_data;
print "$rd DONE WITH Q4 Store DATA! \n";
$excel->close();

$i++;
$j++;
}
exit(0);
[download]