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]

In reply to SQL query using dynamic array by cocl04

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.