[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

A hard-to-answer question

I have a feeling that this is an unanswerable question, but we'll see.  I'm no PERL expert - you might even say I'm a newbie at it, because I am.  Here's the scenario:

We're working on a system so that:

a] a PERL script is used to export data from an Oracle db on one server
b] the data is saved in four seperate text files
c] a PERL script is then set to run at midnight of each night on another server, to take the data from the text files (which are uploaded to the new server each night), and import it into a MySQL db
d] I then pull that data out of the db and into a webpage

But all is not well...I've received the same error twice now, and being the non-PERL-expert I am, I'm baffled, especially considering another script is being used to pull similar data out and in, and that runs fine.

Here's the script:

-------------------------------------------------------------------------------------------------------------
# This Perl script is used to import (fixed width format) text files into MYSQL
# database tables
# Author:      Justin Parsons (CAP)
# Date:            06-Apr-05


      use DBI;
      # We are using the DBI Module

      # Declare your connection string variables here, CAP_WEBSITE is the MYSQL database name.
      my ($dsn) = "DBI:mysql:CAP_WEBSITE:localhost";
      my ($user_name) = "root";
      my ($password) = "";
      my ($sth, $dbh);
      my (@ary);

      # Provide a connection to the database
      $dbh = DBI-> connect ($dsn, $user_name, $password {Raiserror => 1});

      ############ DELETE TABLE CONTENT ###############################
      # Prepare the three Delete statements and execute the statements
      
      $sth = $dbh->prepare ("DELETE FROM X_SITE_INFO");
      $sth->execute ();

      $sth = $dbh->prepare ("DELETE FROM X_SITE_RESOURCES");
      $sth->execute ();

      $sth = $dbh->prepare ("DELETE FROM X_SITE_URL");
      $sth->execute ();

      $sth = $dbh->prepare ("DELETE FROM X_SITE_HOURS");
      $sth->execute ();
      ##################################################################

      ############ OPTIMIZE TABLES #####################################
      # Recover empty space      

      $sth = $dbh->prepare ("OPTIMIZE TABLE X_SITE_INFO");
      $sth->execute ();

      $sth = $dbh->prepare ("OPTIMIZE TABLE X_SITE_RESOURCES");
      $sth->execute ();

      $sth = $dbh->prepare ("OPTIMIZE TABLE X_SITE_URL");
      $sth->execute ();

      $sth = $dbh->prepare ("OPTIMIZE TABLE X_SITE_HOURS");
      $sth->execute ();
      ##################################################################

      ############ LOAD DATA IN TABLES #################################

      $sth = $dbh->prepare ("LOAD DATA INFILE
                        '/home/www/CAPInfoAgent/Scripts/X_SITE_INFO.txt' INTO TABLE X_SITE_INFO
                        FIELDS TERMINATED BY '\t'
                         LINES TERMINATED BY '\n'");
      $sth->execute ();

      $sth = $dbh->prepare ("LOAD DATA INFILE
                        '/home/www/CAPInfoAgent/Scripts/X_SITE_RESOURCES.txt' INTO TABLE X_SITE_RESOURCES
                        FIELDS TERMINATED BY '\t'
                         LINES TERMINATED BY '\n'");
      $sth->execute ();

      $sth = $dbh->prepare ("LOAD DATA INFILE
                        '/home/www/CAPInfoAgent/Scripts/X_SITE_URL.txt' INTO TABLE X_SITE_URL
                        FIELDS TERMINATED BY '\t'
                         LINES TERMINATED BY '\n'");
      $sth->execute ();

      $sth = $dbh->prepare ("LOAD DATA INFILE
                        '/home/www/CAPInfoAgent/Scripts/X_SITE_HOURS.txt' INTO TABLE X_SITE_HOURS
                        FIELDS TERMINATED BY '\t'
                         LINES TERMINATED BY '\n'");
      $sth->execute ();
      ##################################################################

        $sth->finish ();
        $dbh->disconnect ();


exit (0);

---------------------------------------------------------------------------------------------------------

It's strange because I run this script and the tab-delimited text files on our local system and it ran fine, but down there, no go.  Here's the error which gets returned:

---------------------------------------------------------------------------------------------------------
/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl: line 5: :
command not found/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl:
line 6: : command not found/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl: line 7: use: command
not found/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl: line 7:
: command not found/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl: line 9: :
command not found/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl:
line 11: syntax error near unexpected token `$dsn'/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl: line 11: `      my ($dsn) =
"DBI:mysql:CAP_WEBSITE:localhost";
----------------------------------------------------------------------------------------------------------

Ideas?  Because I'm stumped.
0
jpve
Asked:
jpve
1 Solution
 
manav_mathurCommented:
Have you included the hadh-bang line at the top of the script

#!/absolute/path/to/perl/executable
0
 
jeopboyCommented:
I would also check the include path on the server.  Are the DBI libraries being found?
0
 
ozoCommented:
The errors look like shell errors, not perl errors.
If you're running it under unix with the command
/home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl
instead of the command
perl /home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl
it will by default run as if your command was
sh /home/www/CAPInfoAgent/Scripts/Import_Data_JP.pl
unless there is a
#!
line at the start of the file specifying which program to use to run your file
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jpveAuthor Commented:
It's being run in a Linux box, that is on a seperate server, housed in an entirely seperate building...but you're saying I should add the #! line at the start?  If so, what should go on this line?

I'm stumped because I've run this script on our Linux here, and it runs correctly; I've made changes to one of the tab-delimited text files, uploaded it, run the script, and then I look at the db and the change has been made, so it's obviously running up here.

The system here is called Webmin (www.webmin.com).  It has a section underneath System called Scheduled Chron Jobs, which is where I place the script...you then set the time and intervals for whenever you would like this script to run, so in the end, it will be running automatically every midnight, after the data gets transferred from the other server and the four data files get uploaded.

I'm leaning towards that it may be that perhaps the four text files are uploaded to the wrong location, which would obviously cause an error in the script, though I'm pretty sure the administrator down at the other building has uploaded them into the correct directory.  I have no idea where to go from here.
0
 
ozoCommented:
Probably something like
#!/usr/bin/perl
depending on where perl lives on the machine it is running on
How do you run the script here, and how do you run the script there?
What is the cron command that you scheduled?
Where do you see those error messages (which seem to be missing a few newlines)?
0
 
jpveAuthor Commented:
The script is run just by entering it into the Chron Job system...it runs automatically at every midnight, though we can still run it manually for testing purposes.  The error messages pop up after you run the job.  I'm at a loss of what to do here...this needs to be done by Friday.
0
 
jmcgOwnerCommented:
I know you say you tested the script after uploading it, but in addition to being incorrectly interpreted by a shell rather than perl, it looks like you might still have DOS/Windows line-ending conventions in the file. The stray carriage-return characters being reported in error messages could obscure parts of the error message, which would make things look very confusing.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now