Re^4: SQL queries using dynamic array

[Anonymous Monk]

I appreciate your response.  I have my password set to my email addres
+s at work.  I also left my laptop at work.  So therefore, I can't get
+ to it.  Secondly, I already have a process that works great and the 
+syntax is correct.  I will post it on Monday.  I apologize.  Here is 
+what I am doing...I have a process that pulls data from oracle, forma
+ts an excel worksheet, and auto emails reports to the field by id gro
+up (District Manager, Regional Manager, VP, etc…).  Currently, all of
+ the email addresses are hard coded and they are sent by group.  The 
+business owner wants the reports to be tailored to individuals and se
+nt to that specific individual.  
So, I have a test process that queries the database and loads a list o
+f distinct id's into an array.  The email addresses are id@...com.  S
+o that part is easy.  By using a loop, I can successfully pass the id
+ to the Perl code and update the correct email heading and changes th
+e title of the file to the correct id and emails it to the correct ad
+dress.  I want to have the select * from table where id = id.  ...Whe
+re the id is specific to the array index.  I don’t want to use an IN 
+‘id’ for all id’s that match.  Currently, the test sql process select
+s the first array index for the id, but no data returns for the next 
+ids.  So, only id 1 (array index '0') has data.  The rest of the emai
+ls return no data.  I just need help dynamically selecting info for a
+ specific id.  The current process works, but I need it to be dynamic
+.  I hope this helps you understand.  I will send the code.  Will you
+ help me?  I have worked on this all week.  I just happen to do a sea
+rch at home and found this posting.  I know the prepare statement sho
+uld probably should not be done in a while loop or a loop for that ma
+tter.  But I am not sure how to approach the loop.  I basically took 
+the current process that works and tried to wrap it into a loop and p
+ass the new id from the distinct list to the sql statement and email.
+  The email process works, but the prepare sql statement only returns
+ the first index.  I thought that the $value could be used with a pla
+ceholder and updated each time the array index incremented.  But is l
+ooks like that is a bad idea.  Please help!!!!

i.e.
@id = ('A01','B55')


Thank you for your advice.

[graff]

Please help!!!!

Sorry, but it looks like the kind of help you need is not technical in nature. You completely ignored my advice about formatting your posts, you are not posting any code that makes any sort of sense, and your rambling description is also nonsensical.

The impression I get is that you have no idea what you are trying to do, which would explain why you are unable to describe it clearly. And since you are not paying any attention to the advice you've been given, there's not much I can offer that would help you.

The current process works, but I need it to be dynamic.

Well, you haven't really shown us anything that works yet, have you? So no, this doesn't help us understand. You might just need something as simple as "placeholders" in the sql statement, but I'm not sure, because nothing you've presented so far makes any sense.

[cocl04]

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

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

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

##################################
#
#  Connect to Oracle database.
#
###################################  

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


############################
#
#   Set up Query for Store
#
############################

my $stmt_1 = "select alignment from rd_list"; 

############################
#
#   Prepare Query
#
############################

my $query = $connection->prepare($stmt_1); 


############################
#
#   Execute Query
#
############################

$query->execute() or die $connection->errstr;

###############################
#
#   Declaration.
#
###############################

my ($alignment);

############################################
#
#   binds each column to a scalar reference
#
############################################

$query->bind_columns(undef,\$alignment);

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

my $rows = $query->fetchall_arrayref;

###############################
#
#   Load data in array.
#
###############################

foreach $i(0..$#{$rows})
{
   foreach $j (0..$#{$rows->[$i]} ) 
   {
    $rows->[$i][$j];
   }
}

my $num = $query->rows;

print "\nThis process returns $num of Rd's.\n\n";


$query->finish();
$connection->rollback;
$connection->disconnect();


######################################################################
+################################################



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");

######################################################################
+#######################
#
#  Start Store process...
#
######################################################################
+#######################

########Create Worksheet format for first worksheet########

################################
#
#   format worksheet for Headers
#
#################################

my $headings = $excel->add_format();
$headings->set_bold();
$headings->set_color('white');
$headings->set_bg_color('blue');
$headings->set_align('top');
$headings->set_text_wrap();

####################################
#
#   format / merge cells for Header
#
#####################################

$worksheet->merge_range('A1:D1',"4th Quarter District Manager Quarterl
+y Performance Incentive!",$headings);
$worksheet->merge_range('A2:D2'," ", $headings);
$worksheet->merge_range('A3:D3', "Period Measured: 10-01-2008 through 
[phone]", $headings);
$worksheet->merge_range('A4:D4', " ", $headings);
$worksheet->merge_range('A5:D5', "Growth:", $headings);
$worksheet->merge_range('A6:D6', "plus 18 Customers OR", $headings);
$worksheet->merge_range('A7:D7', "plus 27 BOR OR", $headings);
$worksheet->merge_range('A8:D8', "plus 3300 in SMRR", $headings);
$worksheet->merge_range('A9:D9', " ", $headings);
$worksheet->merge_range('A10:D11', "Each store that meets one of the g
+rowth goals must also achieve a 5.9% average for Q4.", $headings);

########################################
#
#   set column widths for Column Headers
#
#########################################

$worksheet->set_column('A:A',16.57);
$worksheet->set_column('D:D',23.71);
$worksheet->set_column('F:F',16.71); 
$worksheet->set_column('G:G',16.29); 
$worksheet->set_column('H:H',15.29);
$worksheet->set_column('I:I',14.86);
$worksheet->set_column('J:J',16.29);
$worksheet->set_column('K:K',15.86);
$worksheet->set_column('L:L',26.86);
$worksheet->set_column('M:M',19.00);

########################################
#
#   format worksheet for Column Headers
#
#########################################

my $col_headings = $excel->add_format();
$col_headings->set_bold();
$col_headings->set_size(10);
$col_headings->set_color('white');
$col_headings->set_align('center');
$col_headings->set_bg_color('blue');
$col_headings->set_border(2);
$col_headings->set_shrink();

################################
#
#  Write Heading(s) to worksheet
#
################################

$worksheet->write(13, 0, "WEEK_END_DATE", $col_headings);
$worksheet->write(13, 1, "SVP", $col_headings);
$worksheet->write(13, 2, "RD",  $col_headings);
$worksheet->write(13, 3, "DM", $col_headings);
$worksheet->write(13, 4, "STORE", $col_headings);
$worksheet->write(13, 5, "WTD_SMRR_GAIN", $col_headings);
$worksheet->write(13, 6, "QTD_SMRR_GAIN", $col_headings);
$worksheet->write(13, 7, "WTD_BOR_GAIN", $col_headings);
$worksheet->write(13, 8, "QTD_BOR_GAIN", $col_headings);
$worksheet->write(13, 9, "WTD_CUST_GAIN", $col_headings);
$worksheet->write(13, 10, "QTD_CUST_GAIN", $col_headings);
$worksheet->write(13, 11, "WTD_CARD_CLOSED", $col_headings);
$worksheet->write(13, 12, "QTD_AVG_CARD_CL", $col_headings);

######################
#
# Freeze panes
#
######################

$worksheet->freeze_panes(14,5);

############################
#
#  format rest of data data
#
############################

my $format = $excel->add_format();
$format->set_size(10);
$format->set_align('center');
$format->set_border(1);
$format->set_num_format('General;[Red](-General);General');


########Create Function / Subroutine to Get Data and insert into Works
+heet########

sub get_store_data {

##################################
#
#  Connect to Oracle database.
#
###################################  

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


#DBI-> trace(2);

############################
#
#   Set up Query for Store
#
############################

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; 

###############################
#
#   Declaration.
#
###############################

#my ( $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);


############################################
#
#   binds each column to a scalar reference
#
############################################

#$sth->bind_columns(undef,\$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);

###############################
#
#   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();



##################################
#
#   Create email addresses for RD.
#
##################################

my $at = "@";

my $rentacenter_com = "rentacenter.com";

my $rd_at_rentacenter_com = "$rd$at$rentacenter_com";

print "$rd_at_rentacenter_com\n";

##################################
#
#   Create email addresses for ME.
#
##################################

my $cory_clay = "cory.clay";

my $cory_clay_at_rentacenter_com = "$cory_clay$at$rentacenter_com";

print "$cory_clay_at_rentacenter_com\n";

##################################
#
#   Create email Headding for RD.
#
##################################

my $Q4 = "Q4 ";

my $PERFORMANCE_RPT = " PERFORMANCE RPT.xls";

my $Q4_RD_PERFORMANCE_RPT = "$Q4$rd$PERFORMANCE_RPT";

print "$Q4_RD_PERFORMANCE_RPT\n\n";

#########################################
#
#   Email the email with Send attachment.
#
##########################################

my $sender = new Mail::Sender
{
    smtp => 'xx.xx.x.xx',
    from => '[e-mail]'
};

$sender->MailFile(
{
    to => $cory_clay_at_rentacenter_com,
    subject => $Q4_RD_PERFORMANCE_RPT,
    msg     => "All,
Test Body of email.

Thanks,

Cory Clay
$cory_clay_at_rentacenter_com
\n",
    file    => $Q4_RD_PERFORMANCE_RPT,
});

$sender->Close;

$i++;
$j++;

}

exit(0);

[cocl04]

I am sorry. I never should have written anything at home. I have a very simple task.

Step 1: Select a list of id's and load into an array. Don't hard code 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.

Currently, I perform the following:

Step 1: Select data for a group of id's. The data is hard coded for a specific group.

Step 2: Email data to the group of id's.

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. I am sorry if that is not clear. The hard coded group email process works. In terms of the dynamic select...As I iterate throught the array, it prints and emails the correct id, however the select only returns the first index in the array. Again from your tone, it was a mistake to send this at home without all the info I needed. I understand if you don't want to help...but I really need your help...I write sql all day...not perl...This dynamic process would be all the help I need. Will you help me?...Thanks, Cory

[graff]

First, thank you very much for improving your posting style -- it really helps.

I'm looking at your steps, and trying to relate them to the long piece of code you posted -- it's not easy, because the terms (variable names, table column names, etc) that appear in the code do not match the terms you use in describing your two steps.

Apart from that, there is a lot that is wrong with your code as posted. For example, look at how $i is used -- set to zero at the top, then used as a loop counter when cycling through data from a query, then used in  while ($i<=2) (it gets incremented inside the loop, but it's probably already greater than 2 before you reach the while loop). Other problems:

• You do two separate database connections -- are these two separate databases? If not, you should connect just once.
• Inside the while loop, you have "my $rd", and you declare a large subroutine that depends on having $rd in scope -- it would be better to pass $rd as a parameter to the sub, so the sub does not need to be inside the while loop.
• There's not nearly enough error checking.

Rather than go further into the problems, let's see if I can rephrase the steps and the code -- you can tell me if I'm guessing correctly about what the task really is:

1. Get a list of ids for data recipients from the database -- each id will be used to query for the data that needs to be sent, and as part of the email address to send to.

2. For each id in the list:
   1. query the database to get the data for that recipient
   2. create an excel file and load it with this person's data
   3. send email to the recipient (and attach the excel file?)

I think this is what the code ought to look like -- you'll need check all the details carefully, and make corrections if I've misunderstood anything. Obviously, I can't really test it, but it passes "perl -cw" with no problems.

Read more... (6 kB)

Just a few closing remarks about my version vs. yours:

• Note that the "main" part of the program is quite brief, and someone reading the code can see pretty easily what it does, and how it relates to the description I gave above -- in fact, the description and the main part of the code are almost the same thing.

• Since I removed all the five-line comments from your script (and many of the blank lines around them), more of the actual code is visible on the screen at one time, making it easier to see how variables are used, how the logic flows, etc. Most of your commenting was unnecessary -- it took up a lot of space just to say things that should be obvious from the code itself.

• I've tried to keep most of the variable declarations close to where they are used, also making it easier to see the strategy and flow of the code.

• I've made the subroutines independent of the main part of the code, passing the parameters that are needed for each sub, making the subs more modular and self-contained (helpful when you want to reuse code, which usually involves moving subs into separate modules/packages).

• In the part that sets up the excel file, I've used a hash or array together with a loop instead of repeating a bunch of subroutine calls -- the less repetitive typing you do when coding, the better.
Again, I'm not sure this really does what you want, but I hope it's moving you in the right direction.