What's not working?

What do you expect this code to do?

Why are you performing the same query multiple times? Do you expect new data to be entered in the database between the Perl statements?

Are you certain that your database operations are succeeding?

Are you receiving the data you expect?

Have you considered placeholders? (Your queries look vulnerable to a SQL injection attack.)

$dbh=$db->prepare("SELECT nvl(mb_award,0.00),
                          nvl(mb_accept,0.00)
                   FROM Items
                   WHERE itemnum=? AND seller=?
                         AND closef='1'");
$dbh->execute($form{'item'}, $fdnum);
my ($mb_award,$mb_accept) = $dbh->fetchrow_array();

my $mb_amount_disp = $mb_award > 0.00 ? $mb_award : 
                        $mb_accept > 0.00 ? $mb_accept :
                           0.00; # Assume you have negative values
[download]

use strict;
use DBI;
use DBD::Oracle qw(:ora_types);

# Connect as Oracle
$ENV{ORACLE_HOME}='/users/oracle/OraHome1';
my $dbh=DBI->connect("dbi:Oracle:MyDatabase","username","password");

# Select the some columns from the database
my $sth = $dbh->prepare("select a as Alias, b as Bob
                         from MyTable
                         where c=?");
$sth->execute("Some Value");

# One convenient method is to use a hash reference
while (my $res = $sth->fetchrow_hashref())
{
    # You can refer to values using the name of the column/alias
    # for example...
    my $alias = $res->{Alias};
    my $bob = $res->{Bob};
    ....
}

$sth->finish;
$dbh->disconnect();
[download]

Perhaps I misunderstand your code, but you already have the values for mb_award and mb_accept after your initial SELECT. Why do you fetch them again with two more SQL commands?

Your SELECT statements are also insecure if the $form{item} or $fdnum variables are under the control of a user; you should either use ? placeholders or else quote them.

Also, try using the -w switch and use strict, and see if that points you to any errors. And, you don't do any error checking from your various DB calls, so no matter how badly DBI wants to tell you where your error is, it can't.

It's weird to use a string for numeric comparisons. You probably just want if ($mb_award > 0) {.

It's a little weird to assign the array returned from $dbh->fetchrow_array() to a single value. I think it will work, but if the array actually returns more than one thing it may not do what you expect. It would be more idiomatic to say:

($mb_amount_disp) = $dbh->fetchrow_array();
[download]

If none of these hints help, you'll need to post some sample rows, what you expect to happen, and what actually happens. The code here certainly does something, but it may not be the same thing you want it to do...

First is the usual discomfort at seeing values interpolated directly into the SQL statement, rather than using placeholders.   Are you sure that $form{'item'} and $fdnum don't contain any spaces or apostrophes or something else that your SQL engine make think is something other than a value?   For that matter, do you need apostrophes surrounding those two variables?

Second is the strange use of 'handle' variable names.   I can see that $db is apparently your DBI database handle returned from connect().   Often people use $dbh for that handle value, and use something else for the statement handle.   Using $dbh instead of $sth for a statement handle causes a little queasiness for me.

Another thing that confuses me is that all the SQL statements are the same, at least as shown in your example.   What different behavior did you think you would get?

Enough blathering from me.   Why don't you back up and just check the results from your first prepare/execute/fetch set of statements.   Let's see, untested code ahead:

  my ($mb_award,$mb_accept); 

  $db->{RaiseError} = 1; # turn on error checking\

  my $sth=$db->prepare( "SELECT mb_award,mb_accept FROM Items WHERE it
+emnum=? AND seller=? AND closef='1'" );

  $sth->execute($form{'item'},$fdnum);

  ($mb_award,$mb_accept) = $sth->fetchrow_array();

  print "  We see '$mb_award' and '$mb_accept'\n";
[download]

are you useing strict? ie add 'use strict' near the top of your script.

Do some reading on using 'placeholders' in your sql.

Why the extra db selects? You appear to be fetching the same values as you already have.

There's something funky here:

cleaning it up a bit..

if ($mb_award > 0) {
  $mb_amount_disp = $mb_award;
} elsif ($mb_accept > 0) {
  $mb_amount_disp = $mb_accept;
}
[download]

or if you know for certain one and only one of the values will always be > 0

$mb_amount_disp = $mb_award > 0 ? $mb_award : $mb_accept;
[download]

But what should happen when neither or both values are > 0 ?

cheers,

J

Hi all,

thanks for your replies. You have to forgive me; I am very new to perl !! And to add insult to injury, I had stepped away from it for a while ... other projects!

my first post is what I had ended up with after many, many failed attempts. Originally I had.

my ($mb_award,$mb_accept);

$dbh=$db->prepare("SELECT mb_award,mb_accept FROM Items WHERE itemnum=$form{'item'} AND seller=$fdnum AND

closef='1'");
$dbh->execute();
($mb_award,$mb_accept) = $dbh->fetchrow_array();


my $mb_amount_disp;

if ($mb_award > "0.00") {
$mb_amount_disp = $mb_award;
}
elsif ($mb_accept > "0.00") {
$mb_amount_disp = $mb_accept

I was thinking I was "using up my variable" somewhere ..... so I put the same query in again. it is strange that this is only working for the mb_accept column...

I am using MySql for the database and the default values for the two columns are 0.00 . Entries will be made in the rows mb_accept or mb_award; never both! I need this bit of code to see which row the entry has been made in, and then display the amount. I tried using edoc's snippet ...

if ($mb_award > 0) {
$mb_amount_disp = $mb_award;
} elsif ($mb_accept > 0) {
$mb_amount_disp = $mb_accept;
}

but i still end up with the same result! Nothing... it doesn't even display the default value 0.00

thanks again,

-paul

It sounds like one of the columns in your table has NULL values. Try to add nvl to your SELECT statement, and also try to change


($mb_award,$mb_accept) = $dbh->fetchrow_array();
[download]

to two steps -

use Data::Dumper;
...
my @res = $dbh->fetchrow_array();
print Dumper(@res);

$mb_award = $res[0];
$mb_accept = $res[1];
[download]

Use the Data::Dumper module to investigate your variables - I suspect you will see an undef in @res (because one of the column might be NULL).

One last observation is that your test of greater than "0.00" does not work if both values are equal to "0.00" or one is "0.00" and the other is NULL. In other words, the test is incomplete. Perhaps you need to add some exception handling...



if ($mb_award > 0) {
  $mb_amount_disp = $mb_award;
} elsif ($mb_accept > 0) {
  $mb_amount_disp = $mb_accept;
} else {
  # both values are <= 0 or undef, what shall I do?
  ....
}
[download]

Please do a "mysql> describe your_table_name;" and post the results here - I'm wondering what the data type is of the column you are comparing to 0.00.