Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Question Email :: Send ? how to Attach


    Hello Everyone,

    With the below given scripts I do get an Email from Gmail to my Hotmail Inbox .

    But i do get the Error messages as well while compile .

    So what i'm wrong with this ?

    #!/usr/bin/perl -w

    # Script to be used to extract the data from
    use strict;
    use warnings;
    use DBI;
    use Cwd;
    use Text::CSV;
    use Email::Send;
    use Email::Send::Gmail;
    use Email::Simple::Creator;
    use Carp;

    my $sth;
    # To know your Present Working Directory
    my $pwd = cwd();
    my $filename = 'albie.csv';

    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root') || die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";

    $sth = $dbh->prepare( "SELECT *FROM orderdetails");

    #execute the query
    $sth->execute( );
    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";

    my $fields = join(' ',@{ $sth->{NAME_lc} });

    open (FILE, ">>" ,$filename) or die " Unable to create .$filename\n " ;

    print FILE "$fields\n";

    #$sth->execute( );

    while ( my @row = $sth->fetchrow_array( ) ) {

    open (FILE, ">>" ,$filename) or die " Unable to create .$filename\n " ;

    print FILE "@row\n";

    }


    my $email = Email::Simple->create(
    header => [
    From => 'srinidelite@gmail.com',
    To => 'srinidelite@hotmail.com',
    Subject => 'hello email::send::gmail',
    ],
    body => 'will Email::Send::Gmail fail? y/n',
    );

    my $sender = Email::Send->new(
    { mailer => 'Gmail',
    mailer_args => [
    username => 'srinidelite@gmail.com',
    password => '*****',
    ]
    }
    );
    eval { $sender->send($email) };
    confess "Error sending email: $@" if $@;

    print "success\n";

    warn "Problem in retrieving results", $sth->errstr( ), "\n"
    if $sth->err( );


    print "Hi your File $filename has been placed \n in the directory called $pwd \n " ;
    $sth -> finish(); $dbh->disconnect();
    print "Goodbye\n";


    Error Message

    Return::Value is deprecated at G:/Dwimperl/perl/site/lib/Return/Value.pm line 13 require Return/Value.pm called at G:/Dwimperl/perl/site/lib/Email/Send.p m line 11 Email::Send::BEGIN() called at G:/Dwimperl/perl/site/lib/Return/Value.pm line 0 eval {...} called at G:/Dwimperl/perl/site/lib/Return/Value.pm line 0 require Email/Send.pm called at ETLTEST.pl line 9 main::BEGIN() called at G:/Dwimperl/perl/site/lib/Return/Value.pm line 0 eval {...} called at G:/Dwimperl/perl/site/lib/Return/Value.pm line 0 Connection Successful Query results: ================================================ success Hi your File albie.csv has been placed in the directory called C:/Users/srini/Documents Goodbye Press any key to continue . . .
    And one more ,

    If I would like to attach the result as an attachment ? What should I suppose to do ?

    Thanks in Advance,
    Srini.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    You should use Email::Sender instead of Email::Send

    Taken from Return::Value documentation.
    Return::Value - (deprecated) polymorphic return values

    version 1.666001
    DO NOT USE THIS LIBRARY ^

    Return::Value was a bad idea. i'm sorry that I had it, sorry that I followed through, and sorry that it got used in other useful libraries. Fortunately there are not many things using it. One of those things is Email::Send which is also deprecated in favor of Email::Sender.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0
    Originally Posted by FishMonger
    You should use Email::Sender instead of Email::Send

    Taken from Return::Value documentation.
    Thanks I have corrected it ...

    What about rest of my code is it looks fine ????
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    What about rest of my code is it looks fine ????
    It could use some improvements.

    1) Remove the -w switch. Using the warnings pragma is better.

    2) There is no need to predeclare $sth. Declare it in the assignment statement.

    3) You should always include error handling on the prepare and execute statements. The easiest way to do that would be to add { RaiseError => 1 } to the connect statement. If you want finer control over the error handling, then do explicit checks on each statement instead of using RaiseError.

    4) Using a space as your field separator in a csv file is not the best choice. You should use a comma, or a tilde, or similar char that would clearly delimitate the fields.

    5) Did you really mean to open the file in append mode? Normally it should be opened in write mode.

    6) The die statement on the open call should include the reason it failed, which is kept in the $! var.

    7) Do not open the file in the while loop. You only need to open it once outside of the loop and write to it inside the loop.

    8) You're loading the Text::CSV module but not using it. Why aren't you using it?

    9) The warn statement if $sth->err() should be moved up close to where the error might occur, or simply removed because you're already applying error handling on all db statements. Right?

    10) Instead of using eval, it would be better to use the Try::Tiny module as shown in the Email::Sender documentation. Yes, it is slightly more verbose, but it is also the more preferred method.

    11) You should review where and when you're outputting status messages to the user to be sure you're giving the user accurate info. Specifically, I'm referring to this section:
    Code:
    eval { $sender->send($email) };
    confess "Error sending email: $@" if $@;
    
    print "success\n";
    If the error message is outputted, you also tell the user that it was successful. You need to be clear on what failed and what was successful.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Thank you ! Incredible mentor you're !


    Originally Posted by FishMonger
    It could use some improvements.

    1) Remove the -w switch. Using the warnings pragma is better.

    2) There is no need to predeclare $sth. Declare it in the assignment statement.

    3) You should always include error handling on the prepare and execute statements. The easiest way to do that would be to add { RaiseError => 1 } to the connect statement. If you want finer control over the error handling, then do explicit checks on each statement instead of using RaiseError.

    4) Using a space as your field separator in a csv file is not the best choice. You should use a comma, or a tilde, or similar char that would clearly delimitate the fields.

    5) Did you really mean to open the file in append mode? Normally it should be opened in write mode.

    6) The die statement on the open call should include the reason it failed, which is kept in the $! var.

    7) Do not open the file in the while loop. You only need to open it once outside of the loop and write to it inside the loop.

    8) You're loading the Text::CSV module but not using it. Why aren't you using it?

    9) The warn statement if $sth->err() should be moved up close to where the error might occur, or simply removed because you're already applying error handling on all db statements. Right?

    10) Instead of using eval, it would be better to use the Try::Tiny module as shown in the Email::Sender documentation. Yes, it is slightly more verbose, but it is also the more preferred method.

    11) You should review where and when you're outputting status messages to the user to be sure you're giving the user accurate info. Specifically, I'm referring to this section:
    Code:
    eval { $sender->send($email) };
    confess "Error sending email: $@" if $@;
    
    print "success\n";
    If the error message is outputted, you also tell the user that it was successful. You need to be clear on what failed and what was successful.

    Wow ! Man I must say "you are an incredible mentor for me "
    Again Thanks .. I'm just about 1 or 2 weeks learner [perl] hence will learn from you guys as much as i can


    Finally With my script i get an email ....
    Now even though its success I used to get below exceptions as well . Could you be able to figure it out , what causes it this ?

    Error Detils

    ********Query*results:
    ================================================
    Email*Sending*-->*success
    Goodbye
    srinidelite@vm-0:~/Applications$*
    srinidelite@vm-0:~/Applications$*perl*./ET*
    Return::Value*is*deprecated*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*13
    ********require*Return/Value.pm*called*at*/usr/local/share/perl/5.14.2/Email/Send.pm*line*11
    ********Email::Send::BEGIN()*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    ********eval*{...}*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    ********require*Email/Send.pm*called*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*11
    ********main::BEGIN()*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    ********eval*{...}*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    "my"*variable*$sth*masks*earlier*declaration*in*same*scope*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*31.
    Connection*Successful*

    Script

    #!/usr/bin/perl -w
    # Script to be used to extract the data from

    #Declaring Nessacry Header Files here
    use strict;
    use warnings;
    use DBI;
    use Cwd;
    use Text::CSV;
    use Email::Sender;
    use Email::Send;
    use Email::Send::Gmail;
    use Email::Simple::Creator;

    # Gloabl Variable Declartion
    my $sth;
    my $rowCount ;
    my $count;
    my $status = 'FAIL';
    my $filename = 'albie.csv';
    my $csv = Text::CSV->new( {eol => "\n" });

    # To know your Present Working Directory
    my $pwd = cwd();

    # Establishing Connection witn Database
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root','sudhersan') || die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";

    # Pass you SQL Statment Here And Don't Forget to Execute it
    my $sth = $dbh->prepare( "SELECT *FROM orderdetails");
    $rowCount = $dbh->selectrow_array(qq{SELECT count(*)FROM orderdetails});
    #execute the query
    $sth->execute( );
    #$count_0f_Rows = $count->execute() ;

    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";

    # Printing Header / Coloumn Name
    my $employees = $sth->fetchall_arrayref({}) ;
    my @columns = keys ${ $employees}[0];
    open (FILE, ">>" ,$filename) or die " Unable to create .$filename\n " ;
    print FILE join (",", @columns)."\n";

    #prining Whole Row Data Now
    $sth->execute( );
    while ( my @row = $sth->fetchrow_array( ) )
    {
    open (FILE, ">>" ,$filename) and $status = 'PASS' or die " Unable to create .$filename\n " ;
    print FILE join (",", @row)."\n";
    }
    warn "Problem in retrieving results", $sth->errstr( ), "\n"
    if $sth->err( );

    # Informing the User for the File Loaction
    my $fullpath = join ("/", $pwd, $filename);


    #Time to notify
    my $email = Email::Simple -> create (header => [From => 'srinidelite@gmail.com',To => 'srinidelite@hotmail.com',Subject => 'ETL Phase 1 --> Extracting Data from Database'],
    body =>
    "Test Results : $status
    --------------------
    Count Of Rows Returned : $rowCount
    File Available Location : $fullpath
    " );

    my $sender = Email::Send->new({ mailer => 'Gmail',mailer_args => [username => 'srinidelite@gmail.com',password => 'c4024444221',]});

    eval { $sender->send($email) };
    die "Error sending email: $@" if $@;

    print "Email Sending --> success\n";

    #Time to Release the Connections
    $sth -> finish();
    $dbh->disconnect();
    print "Goodbye\n";





    Thanks ,
    Srini.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    Please go back and READ my first post. It tells you where those errors are coming from and how to fix them.

    Also, go back to my 2nd post and read each of the 11 improvements I suggested; most of which you did not implement i.e., you ignored.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Still Having Trouble in


    Originally Posted by FishMonger
    Please go back and READ my first post. It tells you where those errors are coming from and how to fix them.

    Also, go back to my 2nd post and read each of the 11 improvements I suggested; most of which you did not implement i.e., you ignored.
    Hi ,

    When i get corrected my code with some of your suggestions i get below error's as well .. couldn't be able to figure it [ Still Learning]
    . Help appreciated

    ERROR LOG

    srinidelite@vm-0:~/Applications$*perl*./ET*
    Return::Value*is*deprecated*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*13
    ********require*Return/Value.pm*called*at*/usr/local/share/perl/5.14.2/Email/Send.pm*line*11
    ********Email::Send::BEGIN()*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    ********eval*{...}*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    ********require*Email/Send.pm*called*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*11
    ********main::BEGIN()*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    ********eval*{...}*called*at*/usr/local/share/perl/5.14.2/Return/Value.pm*line*0
    Possible*unintended*interpolation*of*@row*in*string*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*48.
    Backslash*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*51,*near*"",*"\"
    ********(Missing*operator*before*\?)
    String*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*51,*near*"@row").""
    ********(Missing*operator*before*")."?)
    Backslash*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*51,*near*"")."\"
    ********(Missing*operator*before*\?)
    String*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*53,*near*"warn*'Problem*in*retrieving*results',*$sth->errstr(*),*""
    **(Might*be*a*runaway*multi-line*""*string*starting*on*line*51)
    ********(Missing*semicolon*on*previous*line?)
    String*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*57,*near*"my*$fullpath*=*join*(""
    **(Might*be*a*runaway*multi-line*""*string*starting*on*line*53)
    ********(Missing*semicolon*on*previous*line?)
    Global*symbol*"@row"*requires*explicit*package*name*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*48.
    Missing*comma*after*first*argument*to*die*function*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*51,*near*"",*"\"
    syntax*error*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*51,*near*"",*"\"
    Global*symbol*"@row"*requires*explicit*package*name*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*51.
    Search*pattern*not*terminated*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*57.

    CODE

    #!/usr/bin/perl -w
    # Script to be used to extract the data from

    #Declaring Nessacry Header Files here
    use strict;
    use warnings;
    use DBI;
    use Cwd;
    use Text::CSV;
    use Email::Sender;
    use Email::Send;
    use Email::Send::Gmail;
    use Email::Simple::Creator;

    # Gloabl Variable Declartion
    my $sth;
    my $rowCount ;
    my $count;
    my $status = 'FAIL';
    my $filename = 'albie.csv';
    my $csv = Text::CSV->new( {eol => "\n" });

    # To know your Present Working Directory
    my $pwd = cwd();

    # Establishing Connection witn Database
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root','sudhersan') || die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";

    # Pass you SQL Statment Here And Don't Forget to Execute it
    $sth = $dbh->prepare( "SELECT *FROM orderdetails");
    $rowCount = $dbh->selectrow_array(qq{SELECT count(*)FROM orderdetails});
    #execute the query
    $sth->execute( );
    #$count_0f_Rows = $count->execute() ;

    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";

    # Printing Header / Coloumn Name
    my $employees = $sth->fetchall_arrayref({}) ;
    my @columns = keys ${ $employees}[0];
    open (FILE, ">>" ,$filename) or die $! ;
    print FILE join (",", @columns)."\n";

    #prining Whole Row Data Now
    $sth->execute( );
    open (FILE, ">>" ,$filename) and $status = 'PASS' or die $! " ;
    while ( my @row = $sth->fetchrow_array( ) )
    {
    print FILE join (",", "\@row")."\n";
    }
    warn 'Problem in retrieving results', $sth->errstr( ), "\n"
    if $sth->err( );

    # Informing the User for the File Loaction
    my $fullpath = join ("/", $pwd, $filename);


    #Time to notify
    my $email = Email::Simple -> create (header => [From => 'srinidelite@gmail.com',To => 'srinivasan.n@thinksoftglobal.com',Subject => 'ETL Phase 1 --> Extracting Data from Database'],
    body =>
    "Test Results : $status
    --------------------
    Count Of Rows Returned : $rowCount
    File Available Location : $fullpath

    --------------------------------------------------------
    This is an auto Generated Email - pls Dont Respond
    ---------------------------------------------------------
    " );

    my $sender = Email::Send->new({ mailer => 'Gmail',mailer_args => [username => 'srinidelite@gmail.com',password => 'c4024444221',]});

    if($sender->send($email) )
    { print "Email Sending --> success\n"; }
    else
    {
    die "Error sending email: $@" if $@;}



    #Time to Release the Connections
    $sth -> finish();
    $dbh->disconnect();
    print "Goodbye\n";


    Thanks,
    Srini
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    Why are you still using Email::Send? The use of that module is causing the first set of errors. DO NOT USE THAT MODULE!!!!!

    1) Delete line 11 (i.e., use Email::Send;).

    2) Change line 73 to use Email::Sender instead of Email::Send.

    3) Change line 51 to:
    Code:
    print FILE join (",", @row)."\n";
    There are several other things that need to be changed, but these 3 are the most important for now.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    Since you're routing the email through Gmail, it might be best to use Email::Send::Gmail (which you are already loading) instead of either Email::Send or Email::Sender.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Still having compile errors


    Originally Posted by FishMonger
    Since you're routing the email through Gmail, it might be best to use Email::Send::Gmail (which you are already loading) instead of either Email::Send or Email::Sender.
    I still get the below errors as well . Im clueless about these

    Error Log

    srinidelite@vm-0:~/Applications$*perl*./ET*
    Bareword*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*54,*near*"warn*"Problem"
    **(Might*be*a*runaway*multi-line*""*string*starting*on*line*51)
    ********(Do*you*need*to*predeclare*warn?)
    String*found*where*operator*expected*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*57,*near*"my*$fullpath*=*join*(""
    **(Might*be*a*runaway*multi-line*""*string*starting*on*line*54)
    ********(Missing*semicolon*on*previous*line?)
    Missing*comma*after*first*argument*to*die*function*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*54,*near*"warn*"Problem*in*"
    syntax*error*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*54,*near*"warn*"Problem*in*"
    Search*pattern*not*terminated*at*./ETL_Phase1_ExtactData_SendEmail.pl*line*57.

    Corrected Code

    #!/usr/bin/perl -w
    # Script to be used to extract the data from

    #Declaring Nessacry Header Files here
    use strict;
    #use warnings;
    use DBI;
    use Cwd;
    use Text::CSV;
    use Email::Sender;
    use Email::Send::Gmail;
    use Email::Simple::Creator;

    # Gloabl Variable Declartion
    my $sth;
    my $rowCount ;
    my $count;
    my $status = 'FAIL';
    my $filename = 'albie.csv';
    my $csv = Text::CSV->new( {eol => "\n" });
    my @row;

    # To know your Present Working Directory
    my $pwd = cwd();

    # Establishing Connection witn Database
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root','sudhersan') || die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";

    # Pass you SQL Statment Here And Don't Forget to Execute it
    $sth = $dbh->prepare( "SELECT *FROM orderdetails");
    $rowCount = $dbh->selectrow_array(qq{SELECT count(*)FROM orderdetails});
    #execute the query
    $sth->execute( );
    #$count_0f_Rows = $count->execute() ;

    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";

    # Printing Header / Coloumn Name
    my $employees = $sth->fetchall_arrayref({}) ;
    my @columns = keys ${ $employees}[0];
    open (FILE, ">>" ,$filename) or die $! ;
    print FILE join (",", @columns) ;

    #prining Whole Row Data Now
    $sth->execute( );
    open (FILE, ">>" ,$filename) and $status = 'PASS' or die $! " ;
    while ( @row = $sth->fetchrow_array( ) )
    {
    print FILE join ("," ,@row) ;

    }
    warn "Problem in retrieving results", $sth->errstr( ), "\n"
    if $sth->err( );
    # Informing the User for the File Loaction
    my $fullpath = join ("/", $pwd, $filename);

    #Time to notify
    my $email = Email::Simple -> create (header => [From => 'srinidelite@gmail.com',To => 'srinivasan.n@thinksoftglobal.com',Subject => 'ETL Phase 1 --> Extracting Data from Database'],
    body =>
    "Test Results : $status
    --------------------
    Count Of Rows Returned : $rowCount
    File Available Location : $fullpath

    --------------------------------------------------------
    This is an auto Generated Email - pls Dont Respond
    ---------------------------------------------------------
    " );

    my $sender = Email::Sender->new({ mailer => 'Gmail',mailer_args => [username => 'srinidelite@gmail.com',password => 'c4024444221',]});

    if($sender->send($email) )
    { print "Email Sending --> success\n"; }
    else
    {
    die "Error sending email: $@" if $@;}



    #Time to Release the Connections
    $sth -> finish();
    $dbh->disconnect();
    print "Goodbye\n";
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    Please use the CODE tags whenever you post any code. The code tags will retain the code formatting/indentation and separate the code from the rest of the post which makes it easier to read and follow.

    This new error is caused my an extra (unneeded) quote char at the end of line 28.

    Doing the "SELECT count(*)FROM orderdetails" is unnecessary. You can retrieve the row count via $sth->rows after the while loop.
    Code:
    my $rowCount = $sth->rows;
    Likewise, there is no need to do this fetch statement.
    Code:
    my $employees = $sth->fetchall_arrayref({}) ;
    my @columns = keys ${ $employees}[0];
    The column/field names could be retrieved via:
    Code:
    my $columns = $sth->{'NAME'}; # $columns is an array ref
    DO NOT use bareword filehandles. Use lexical vars for filehandles and include the filename in the die statement.
    Code:
    open (my $fh, '>' , $filename) or die "Can't write to '$filename' $!";
    You're opening that file twice for no reason (once on line 43 and again on 48). Remove the one on line 48.

    DO NOT DO THIS:
    Code:
    print FILE join (",", @columns) ;
    Instead, do this:
    Code:
    $csv->print($fh, $columns);  # $columns is the array ref noted above.
    Last edited by FishMonger; August 21st, 2013 at 10:13 AM.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    I cleaned up and reworked your script. I added a couple features which will require using perl 5.10 or above. If you're using an older version, we'll need to adjust a few statements. It still may need a couple more slight tweaks, but give it a try.

    Code:
    #!/usr/bin/perl
    
    use 5.010;
    use strict;
    use warnings;
    use autodie;
    use DBI;
    use Cwd;
    use Text::CSV;
    use Email::Sender;
    use Email::Send::Gmail;
    use Email::Simple::Creator;
    
    my $csv       = Text::CSV->new( {eol => "\n" });
    my $filename  = 'albie.csv';
    open my $fh, '>', $filename;  # error handling is handled by the autodie pragma
    
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root','sudhersan',
                           {RaiseError => 1})
            or die "Could not connect to database: $DBI::errstr";
    say "DB Connection Successful";
    
    say "\tPreparing to query DB";
    
    # error checking is handled by the enabling of RaiseError in the connect statament
    my $sth = $dbh->prepare("SELECT * FROM orderdetails");
    $sth->execute;
    
    ## Retrieve and append the results to csv file
    say "\tRetrieving query results:\n" . '=' x 50;
    
    # output csv header fields
    $csv->print($fh, $sth->{'NAME'});
    $csv->print(*STDOUT, $sth->{'NAME'});
    
    while ( my @row = $sth->fetchrow_array )
    {
        $csv->print($fh, \@row);
        $csv->print(*STDOUT, \@row);
    }
    close $fh;
    
    my $rowCount = $sth->rows;
    my $status   = $rowCount > 0 ? 'PASS' : 'FAIL';
    my $fullpath = join('/', cwd(), $filename);
    
    #Time to Release the Connections
    $sth->finish;
    $dbh->disconnect;
    
    say "\nPreparing to send email";
    
    my $email_body = <<BODY;
    Test Results : $status
    ----------------------
    
    Count Of Rows Returned  : $rowCount
    File Available Location : $fullpath
    
    --------------------------------------------------------
    This is an auto Generated Email - pls Dont Respond
    ---------------------------------------------------------
    BODY
    
    my $email = Email::Simple->create(header =>[
                                                From    => 'srinidelite@gmail.com',
                                                To      => 'srinivasan.n@thinksoftglobal.com',
                                                Subject => 'ETL Phase 1 --> Extracting Data from Database'
                                                ],
                                                body => $email_body
    );
    
    my $sender = Email::Sender->new({mailer => 'Gmail',
                                     mailer_args => [
                                                     username => 'srinidelite@gmail.com',
                                                     password => 'c4024444221',
                                                     ]
                                     }
                                    );
    
    if($sender->send($email) )
    {
        say "Email Sent";
    }
    else
    {
        die "Error sending email: $@" if $@;
    }
    
    say "Goodbye";
    Last edited by FishMonger; August 21st, 2013 at 03:53 PM.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Can't locate object method "create" via package "Email::Simple"


    Originally Posted by FishMonger
    I cleaned up and reworked your script. I added a couple features which will require using perl 5.10 or above. If you're using an older version, we'll need to adjust a few statements. It still may need a couple more slight tweaks, but give it a try.

    Code:
    #!/usr/bin/perl
    
    use 5.010;
    use strict;
    use warnings;
    use autodie;
    use DBI;
    use Cwd;
    use Text::CSV;
    use Email::Sender;
    use Email::Send::Gmail;
    use Email::Simple::Creator;
    
    my $csv       = Text::CSV->new( {eol => "\n" });
    my $filename  = 'albie.csv';
    open my $fh, '>', $filename;  # error handling is handled by the autodie pragma
    
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root','sudhersan',
                           {RaiseError => 1})
            or die "Could not connect to database: $DBI::errstr";
    say "DB Connection Successful";
    
    say "\tPreparing to query DB";
    
    # error checking is handled by the enabling of RaiseError in the connect statament
    my $sth = $dbh->prepare("SELECT * FROM orderdetails");
    $sth->execute;
    
    ## Retrieve and append the results to csv file
    say "\tRetrieving query results:\n" . '=' x 50;
    
    # output csv header fields
    $csv->print($fh, $sth->{'NAME'});
    $csv->print(*STDOUT, $sth->{'NAME'});
    
    while ( my @row = $sth->fetchrow_array )
    {
        $csv->print($fh, \@row);
        $csv->print(*STDOUT, \@row);
    }
    close $fh;
    
    my $rowCount = $sth->rows;
    my $status   = $rowCount > 0 ? 'PASS' : 'FAIL';
    my $fullpath = join('/', cwd(), $filename);
    
    #Time to Release the Connections
    $sth->finish;
    $dbh->disconnect;
    
    say "\nPreparing to send email";
    
    my $email_body = <<BODY;
    Test Results : $status
    ----------------------
    
    Count Of Rows Returned  : $rowCount
    File Available Location : $fullpath
    
    --------------------------------------------------------
    This is an auto Generated Email - pls Dont Respond
    ---------------------------------------------------------
    BODY
    
    my $email = Email::Simple->create(header =>[
                                                From    => 'srinidelite@gmail.com',
                                                To      => 'srinivasan.n@thinksoftglobal.com',
                                                Subject => 'ETL Phase 1 --> Extracting Data from Database'
                                                ],
                                                body => $email_body
    );
    
    my $sender = Email::Sender->new({mailer => 'Gmail',
                                     mailer_args => [
                                                     username => 'srinidelite@gmail.com',
                                                     password => 'c4024444221',
                                                     ]
                                     }
                                    );
    
    if($sender->send($email) )
    {
        say "Email Sent";
    }
    else
    {
        die "Error sending email: $@" if $@;
    }
    
    say "Goodbye";

    Thank you Fish Monger but i do get the following compilation errors as well

    Can't locate object method "create" via package "Email::Simple" at ./ETL_Phase1_ExtactData_SendEmail.pl line 65. srinidelite@vm-0:~/Applications$

    Thanks,
    Srini.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    Since that portion of the script is the same as each of your prior versions, I must assume you were getting that error on them as well.

    Change this line:
    Code:
    use Email::Simple::Creator;
    To:
    Code:
    use Email::Simple;
    What version of the module do you have installed? The current version on CPAN is 2.201 and does have the create method. If you have an older version, then you should upgrade.
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Perl Version


    Originally Posted by FishMonger
    Since that portion of the script is the same as each of your prior versions, I must assume you were getting that error on them as well.

    Change this line:
    Code:
    use Email::Simple::Creator;
    To:
    Code:
    use Email::Simple;
    What version of the module do you have installed? The current version on CPAN is 2.201 and does have the create method. If you have an older version, then you should upgrade.

    This*is*perl*5,*version*14,*subversion*2*(v5.14.2)*built*for*x86_64-linux-gnu-thread-multi
    (with*80*registered*patches,*see*perl*-V*for*more*detail)
    *
    Copyright*1987-2011,*Larry*Wall
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo