Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question: 

sub sold_rep_calculations {
    $dbh->do("UPDATE INV SET PRICE=$PRICE WHERE CODE==$CODE");
    $dbh->do("UPDATE INV SET SALESMAN=$SALESMAN WHERE CODE==$CODE");
    $dbh->do("UPDATE INV SET DATE_SOLD=$date WHERE CODE==$CODE");
    my $updy="Y";
    $dbh->do("UPDATE INV SET SOLD=$updy WHERE CODE==$CODE");
    $dbh->do("UPDATE SALESMAN
            SET YTDGROSS=( $YTDGROSS + $PRICE ) WHERE ID==$ID");
    $dbh->do("UPDATE SALESMAN
            SET YTDCOMM=( $YTDCOMM + ( $PRICE * $COMM / 100 ) )
            WHERE ID==$ID");
    $dbh->do("UPDATE SALESMAN
        SET COMM_OWED=( $COMM_OWED + ( $PRICE * $COMM / 100 ) )
        WHERE ID==$ID");
}

[download]
The above snippet produces the following error... DBD::XBase::db do failed: Field Y not found in table INV But all the other statements write the proper data to the Database file, and the my $updy="Y"; $dbh->do("UPDATE INV SET SOLD=$updy WHERE CODE==$CODE"); is written exactly the same as all the other statements. If someone could point out my mistake, or even give me a few tips I would appreciate it greatly.

Replies are listed 'Best First'.
Re: Perl Beginners problem with DBI
by jeffa (Bishop) on Mar 17, 2004 at 14:31 UTC
    The first error i see is "WHERE CODE==$CODE" should be "WHERE CODE = $CODE". Your queries could be written a bit more ... professionally: 
    $dbh->do(
   'UPDATE INV SET SALESMAN = ? WHERE CODE = ?',
   undef, $SALESMAN, $CODE,
);

    [download]
    And be sure that you connect with RaiseError set to true while you are at it: 
    my $dbh = DBI->connect(
   $data_source, $username, $pass,
   {RaiseError => 1},
);

    [download]

    UPDATE:
    Now i see the real culript: 

    my $updy="Y";
$dbh->do("UPDATE INV SET SOLD=$updy  ... ");

    [download]
    You need to quote that first! 
    $dbh->do("UPDATE INV SET SOLD='$updy'  ... ");

    [download]
    or just use placeholders instead and let DBI do the quoting: 
    $dbh->do("UPDATE INV SET SOLD = ? WHERE CODE = ?", undef,$updy,$CODE);

    [download]

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
      Thanks that fixed my problem. I'm only wondering why you need the undef. I guess I'll have to look it up.
[reply]
        Very good question, and very good answer ... but it's hard to grok straight out of the docs. In the case of $dbh->do() ... i never use the attribute hash, which is why it is set to undef. It kinda gets in the way, but it is a small price to pay for the gain. I use the attribute hash when i am fetching rows from the DB. Here is a quick example, just replace the query with one of your own: 
        use DBI;
use Data::Dumper;

...

my $query = 'select * from foo';
my %attr  = ( Slice => {} );
print Dumper $dbh->selectall_arrayref($query);
print Dumper $dbh->selectall_arrayref($query, \%attr);

        [download]
        The first DBI call returns a two dimensional array, or as we like to call it in Perl, a List of Lists (LoL). This is a nice "snapshot" of the table that is returned, one of the coolest features of Database programming in Perl.

        The second DBI call returns something even cooler. A List of Hashes (LoH). Each hash contains key/value pairs of the column name and its value for the current row. Just run the code and you should see what i mean. :)

        What's great about the second call is that you can then pass the results straight to your Templating Engine of choice, such as HTML::Template or Template. I gave another example over at Re: DBI fetchrow_hashref issue. Fun stuff.

        UPDATE: you are welcome zigdon, but credit where credit is due, i learned this from gmax.

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
        That's where the statement specific attributes go.

        rdfield

[reply]
Re: Perl Beginners problem with DBI
by matija (Priest) on Mar 17, 2004 at 14:36 UTC
    The primary cause of your problem is that all the other values you are setting are numbers, so the SQL parser takes them without quotes. Only $updy is a character string - and you're passing it in without the quotes.

    If you changed the code to say:

    $dbh->do("UPDATE INV SET SOLD=\"$updy\" WHERE CODE==$CODE");

    [download]
    it would work.

    But don't do that.

    Instead, learn to use placeholders. It is true that they make you type more, but they take care of quoting problems like the one above:

    $sth=$dbh->prepare("UPDATE INV SET SOLD=? WHERE CODE==?");
$sth->execute($updy,$CODE); # Why is that var all UPcase, BTW?

    [download]
    If you are performing this stuff in a loop, you can move the prepare stage out of the loop, and speed it up.

    Finaly, I don't know if your SQL database doesn't allow multiple updates in a single statement (most do), but if you're having that much trouble with writing SQL, you really should take a look at an abstraction layer like Class::DBI, which lets you treat database tables and rows as objects, and manipulate them more easily.

[reply]
[d/l]
[select]
      Thanks for the advice, its not really that I am having that much trouble with DBI, its just that I have never even looked at a database before in my life. And I don't have a good resource for learning about using DBI, I just kind of did a quick google search and started to try figuring out what I was doing. Oh yea, $CODE is uppercase because I am updating an old DBase III program at the request of a friend, so I just opened it up and started modifying it and the variables in it happened to be uppercase. I am an amature programmer with no formal training, so i don't pretend for a moment I have a clue what I'm doing. Thanks a lot for your help though.
[reply]
      BTW, what database allows double quotes around literal strings: 
      $dbh->do("UPDATE INV SET SOLD=\"$updy\" WHERE CODE==$CODE");

      [download]
      The SQL standard is single quotes around strings. Double quotes are used around identifiers.
[reply]
[d/l]
        > BTW, what database allows double quotes around literal strings

        MySQL allows both. But you're right, I should be more carefull about that.

[reply]
Re: Perl Beginners problem with DBI
by Tomte (Priest) on Mar 17, 2004 at 14:34 UTC

    use placeholders! I assume the error will vanish if you do. I further assume that quotes around interpolated variables in your query string where appropriate would help, but just use placeholders....easier to read, easier to maintain, safer in execution...

    regards,
    tomte

    Hlade's Law:

    If you have a difficult task, give it to a lazy person --
    they will find an easier way to do it.

[reply]

Back to Seekers of Perl Wisdom