MySQL Updates With Null When Perl Script Run From Shell Script

ThisGuyIKnow · 08-07-2008, 10:06 AM

I'm currently updating some old scripts I inherited from the person who had my job before me. These scripts generate newly acquired book listings once a month via a cron job. The scripts are zipped here except the getbooks expect script which is irrelevant to my issue. The data in newbooks.out isn't sensitive and is public on our website, so no worries about that.

The books.sh script runs the show calling mysql, getbooks and categorize.pl. The stumper is this: when I run ./books.sh, categorize.pl sets all the call_num fields to NULL. But if I run categorize.pl by itself from the command line, it does what it is supposed to do. (updates the titles table with the correct call_num values) When I add (or uncomment) the print statement on line 53 of categorize.pl, it shows that at least a correct, valid SQL statement is being assigned as the value of the $query2 variable.

Strange. Any ideas?

- Gavin

chrism01 · 08-07-2008, 08:03 PM

1. books.sh
I don't know why you've got square brackets around values like dbname, user, password, unlees you're trying to copy the syntax manuals.
Its less confusing if you just use fake values eg mydb, myuser, mypasswd

After calling the perl prog, it calls load.bat. If you read the code, the first thing that does is delete the entire table! Then it replaces the contents.

2. categorize.pl

All perl progs should start with these 2 lines:

#!/usr/bin/perl -w
use strict;

Same comment about square brackets. In fact they are unmatched for dbhostname.

You need to check your fn calls to see if they succeeded or failed eg

SELECT:

Code:

    $sth->execute();
    if ( $sth->errstr )
    {
        # SQL failure; log err msg from DB and email Sys Admin
        $error_msg = "db_get_record_types(): ".localtime()." ".
                                            $sth->errstr."\n".$sql;
        exit_with_error($error_msg);
    }
    else
    {
        # Collect values
        while( @db_row = $sth->fetchrow() )
        {

Supply your own code for exit_with_error().

UPDATE

Code:

    $num_rows = $dbh->do($sql);

    # Check for SQL failure
    if( !$num_rows ||
        $num_rows != 1 )
    {
        if( !$num_rows )
        {
            # SQL failure; log err msg from DB
            $error_msg = "db_upd_seq_request_id(): ".localtime()." ".
                                         $dbh->errstr."\n".$sql."\n";
        }
        else
        {
            # Data error
            $num_rows = 0 if $num_rows == 0E0 ;
            $error_msg = "db_upd_seq_request_id(): ".localtime().":\n".
                                    "$num_rows rows updated for ".
                                    "request_id $request_id";
        }
        # Log err msg and email Sys Admin
        exit_with_error($error_msg);
    }

db connect:

Code:

#******************************************************************************
#
# Function      : db_connect
#
# Description   : Connect to the Databases.
#
#                 NB: Set mysql_client_found_rows=1 -> mysql will set 
#                 rows updated to rows matched (like eg Oracle). 
#                 Otherwise, if you update a row to the same as it's 
#                 current value, it'll say zero rows updated (sigh...).
#
# Params        : none
#
# Returns       : none
#
#******************************************************************************
sub db_connect
{
    my (
        $dsn   # data source name
        );

    # Create data source string and connect ...
    # NB: AutoCommit OFF, so use start/rollback/commit txn explicitly
    $dsn = "DBI:mysql:".
               "database=".$cfg::params{'DB_NAME'}.";".
               "host=".$cfg::params{'DB_HOST'}.";".
               "port=".$cfg::params{'DB_PORT'}.";".
                "mysql_client_found_rows=1";    # See fn hdr
    $cfg::ull_dbh = DBI->connect( $dsn, $cfg::params{'DB_USER'},
                                    $cfg::params{'DB_PASSWD'},
                                    {RaiseError => 0, AutoCommit => 0} );

    if( $DBI::errstr)
    {
        exit_with_error($DBI::errstr);
    }
}

I strongly advise breaking it into subroutines. You may find this helpful:
http://perldoc.perl.org/
HTH

ThisGuyIKnow · 08-08-2008, 11:07 AM

LOL! Chris, my friend, I appreciate you answering my post. I may have picked up a few bad habits, but I can code just fine. How about answering my question instead of talking to me like newbie programmer?

Quote:

Originally Posted by chrism01

1. books.sh
I don't know why you've got square brackets around values like dbname, user, password, unlees you're trying to copy the syntax manuals.
Its less confusing if you just use fake values eg mydb, myuser, mypasswd

Oh, did you want my actual credentials?

(who cares what I use; you knew what I meant)

Quote:

Originally Posted by chrism01

After calling the perl prog, it calls load.bat. If you read the code, the first thing that does is delete the entire table! Then it replaces the contents.

Uh, yeah, I know what all the code does.

Quote:

Originally Posted by chrism01

2. categorize.pl

All perl progs should start with these 2 lines:

#!/usr/bin/perl -w
use strict;

While this is indeed common practice and helpful to many programmers, I don't usually do this. Again, don't need lesson, need solution.

Quote:

Originally Posted by chrism01

I strongly advise breaking it into subroutines. You may find this helpful:
http://perldoc.perl.org/
HTH

I don't always modularize my scripts. I do it when it's needed. The categorize.pl script I wrote is only 55 lines, and a significant portion of it is my %categories hash, spread out over 21 lines for readability.

Sorry if I seem sensitive; it's not so much that you're condescending me as you plain just didn't answer the question. Doesn't really help other people reading this post and certainly doesn't help me. We don't need to know your opinion on coding strategy or style.

Sounds like you know a fair amount about perl, though. Maybe you should write a tutorial or two and put them on a website.

chrism01 · 08-09-2008, 07:00 PM

Let me try it this way:

Your Perl prog as posted (linked) takes no external input. It reads the table and then updates it. It will do this regardless of being called by any other prog/script, so the IMMEDIATE results will be the same, what you want, in fact.
However, the shell script immediately calls load.bat which DELETEs the entire table (and then replaces it).
Anything the Perl script has done is lost.
What you end up with instead, is the contents of /home/expect/newbooks.out.

Maybe there's something else involved you haven't mentioned?
Basically, I'm confused as to why you'd bother running the Perl if the table is going to be wiped out immediately afterwards.

Re your other comments: I/we don't know you from Adam, I can only go on the code you showed me.
Apologies if you got the wrong idea.

I have to say I'd always error check the DBI calls, even in a basic script.
YMMV

ThisGuyIKnow · 08-11-2008, 09:53 AM

Quote:

Originally Posted by chrism01

Let me try it this way:
However, the shell script immediately calls load.bat which DELETEs the entire table (and then replaces it).
Anything the Perl script has done is lost.
What you end up with instead, is the contents of /home/expect/newbooks.out.

Bingo! Thanks for spotting my obvious (but not to me, apparently) and 'tarded mistake. categorize.pl needs to be called after the MySQL loads the data. Duh.

This is a classic example of a "programming senior moment"... and I'm far from being a senior.

I love it when this happens. It always make me LOL.

Thanks again, chrism01.

chrism01 · 08-11-2008, 06:22 PM

Actually, my first post mentioned that and you quoted me

Its prob a case of reading what you want to see, rather than what's really there. We've all done it..

In my case I've been prog for many yrs and made all the mistakes several times by now, so I consciously try to slow down, esp when debugging.
That's also why all my progs, even the short ones, are done to the spec in my first post.
I re-use my code a lot.
Anyway, onwards and upwards eh?

ThisGuyIKnow · 08-12-2008, 09:56 AM

Quote:

Originally Posted by chrism01

Its prob a case of reading what you want to see, rather than what's really there. We've all done it..

Uh huh.

Quote:

Originally Posted by chrism01

In my case I've been prog for many yrs and made all the mistakes several times by now...

Ditto & Amen to that.

Thanks again.