LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 08-07-2008, 11:06 AM   #1
ThisGuyIKnow
Member
 
Registered: Mar 2006
Posts: 43

Rep: Reputation: 15
MySQL Updates With Null When Perl Script Run From Shell Script


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
 
Old 08-07-2008, 09:03 PM   #2
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,311

Rep: Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040
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
 
Old 08-08-2008, 12:07 PM   #3
ThisGuyIKnow
Member
 
Registered: Mar 2006
Posts: 43

Original Poster
Rep: Reputation: 15
Thumbs up

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 View Post
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 View Post
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 View Post
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 View Post

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.
 
Old 08-09-2008, 08:00 PM   #4
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,311

Rep: Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040
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

Last edited by chrism01; 08-09-2008 at 09:50 PM.
 
Old 08-11-2008, 10:53 AM   #5
ThisGuyIKnow
Member
 
Registered: Mar 2006
Posts: 43

Original Poster
Rep: Reputation: 15
Red face Ima dork...

Quote:
Originally Posted by chrism01 View Post
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.
 
Old 08-11-2008, 07:22 PM   #6
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,311

Rep: Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040Reputation: 2040
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?
 
Old 08-12-2008, 10:56 AM   #7
ThisGuyIKnow
Member
 
Registered: Mar 2006
Posts: 43

Original Poster
Rep: Reputation: 15
Thumbs up Yep

Quote:
Originally Posted by chrism01 View Post
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 View Post
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
shell script to perl script john83reuben Programming 6 02-24-2008 07:17 PM
how to run a shell script in the perl script sharad Linux - General 1 05-24-2006 04:23 AM
Shell script to run pl/sql script. colly Linux - General 1 09-09-2004 07:49 AM
Shell Script, C, C++, or Perl ? mrpc_cambodia Programming 5 08-12-2004 11:18 AM
how to find the pid of a perl script from shell script toovato Linux - General 1 12-19-2003 07:25 PM


All times are GMT -5. The time now is 03:02 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration