in reply to Re: Problem passing date to SQL
in thread Problem passing date to SQL

I used "date" to keep things simple here. :) The actual statement looks like this: 
my $sth_1 = $dbh->prepare("SELECT sum(EMAIL_IRT),sum(DROP_CALL_COUNT),
+sum(ANS_CALL_COUNT),sum(EMAILS_IN),sum(EMAILS_OUT) FROM CS_STATS WHER
+E DATE_ADDED LIKE ?");

[download]
Also, I removed the row->0 if statement completely but still have the same problem - it looks like nothing is returned. This is what I have now: 
my $sth_1 = $dbh->prepare("SELECT sum(EMAIL_IRT),sum(DROP_CALL_COUNT),
+sum(ANS_CALL_COUNT),sum(EMAILS_IN),sum(EMAILS_OUT) FROM CS_STATS WHER
+E DATE_ADDED LIKE ?");

$sth_1->execute($date_1) or die "Couldn't get values from DB: " . DBI-
+>errstr;

$sth_1->execute();

my ($email_irt,$drop_call_count,$ans_call_count,$emails_in,$emails_out
+);

while (my @data = $sth_1->fetchrow_array()) {
    $email_irt = $data[0];
    $drop_call_count = $data[1];
    $ans_call_count = $data[2];
    $emails_in = $data[3];
    $emails_out = $data[4];
    }

print "$email_irt,$drop_call_count,$ans_call_count,$emails_in,

[download]

And this is the output:

Use of uninitialized value $email_irt in concatenation (.) or string a
+t monthly_stat_mailer.pl line 88.
Use of uninitialized value $drop_call_count in concatenation (.) or st
+ring at monthly_stat_mailer.pl line 88.
Use of uninitialized value $ans_call_count in concatenation (.) or str
+ing at monthly_stat_mailer.pl line 88.
Use of uninitialized value $emails_in in concatenation (.) or string a
+t monthly_stat_mailer.pl line 88.
Use of uninitialized value $emails_out in concatenation (.) or string 
+at monthly_stat_mailer.pl line 88.
,,,,

[download]

Replies are listed 'Best First'.
Re^3: Problem passing date to SQL
by choroba (Cardinal) on Aug 18, 2015 at 15:43 UTC 
    $sth_1->execute($date_1) or die "Couldn't get values from DB: " . DBI-
+>errstr;

$sth_1->execute();

    [download]

    Do you really run execute twice?

    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]
[select]
Re^3: Problem passing date to SQL
by kennethk (Abbot) on Aug 18, 2015 at 17:53 UTC
    You did not run the code I posted, and you never actually test if any assignment happens before outputting. That's why I had an array scoped outside the while loop, and then tested it before getting into output. When I asked you to run specific code, it was to check what's coming back from the DB call. 
    $sth_1->execute($date_1) or die "Couldn't get values from DB: " . DBI-
+>errstr;
$sth_1->execute();

    [download]
    Is this your actual code? The second execute is clearing the cursor.

    Try updating your connector to 

    my $dbh = DBI->connect( 'DBI:Oracle:SID',
                        'username',
                        'password',
                        {
                            PrintError  => 0,
                            RaiseError  => 1,
                            AutoCommit  => 0,
                        },
                    );

    [download]
    This way, if this is a database error, the statement handles will all autodie on errors and print out exactly what went wrong. It'll also tell you if you've reset a cursor with a second execute.

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

[reply]
[d/l]
[select]
Re^3: Problem passing date to SQL
by GotToBTru (Prior) on Aug 18, 2015 at 16:18 UTC

    You're only posting part of your code to keep this simple, but it's getting in the way of us helping. See http://sscce.org.

    Dum Spiro Spero
[reply]

In Section Seekers of Perl Wisdom