Re: Problem passing date to SQL

I'm a little surprised that this SQL works since DATE is a reserved word, and you are treating dates like strings. But that aside, from DBD::Oracle:

rows

$rv = $sth->rows;

Returns the number of rows affected for updates, deletes and inserts and -1 for selects.

i.e. that's not how rows works. What happens when you run

$sth->execute($date) or die "Couldn't get values from table: " . DBI->
+errstr;

my @data;
while (my @row = $sth->fetchrow_array) {
    @data = @row;
}
if (@data) {
    local $" = ',';
    print "@data\n";
} else {
    "0 results\n";
    exit;
}
[download]

I also note in your posted code, you are calling fetchrow_array on $sth_1 and not $sth, which is the SQL you prepare. Please make sure code you post actually runs; perhaps are testing the wrong statement handle in your original code as well? How do I post a question effectively?

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

Comment on Re: Problem passing date to SQL Select or Download Code

Replies are listed 'Best First'.
Re^2: Problem passing date to SQL by JoeTheProgrammer (Novice) on Aug 18, 2015 at 15:40 UTC
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]	[reply] [d/l] [select]
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]