Perl- Output file is always 0 byte

Shell Programming and Scripting
1

Hi all,

I am new to perl programming. However i have a script that connects to the database and spools that into an output file.
Strange thing is that sometimes this script works and sometimes the ouput spool file is always 0 byte.
I have verified the sql query and the query always returns some value not sure why perl is not able to output this value to my spool file

  • providing a Snapshot of my Code
open(Spool, "> $filename") or die "Error opening $filename\n";

while( $Conn->FetchRow() ) {

     my $Temp =$Conn->Data("STATUS");
     print "Value is $Temp";  --> $Temp has always some value in it
-- Approach 1
    print Spool $Temp; --> This does not work output file is 0 byte
--- Approach 2
    printf(Spool "%s\n", $Conn->Data("STATUS" )); --> This does not work output file is 0 byte
     }
     
   $Conn->Close();
   
   close Spool;

I have used a similar code before and their it seems to work


 open(Spool, "> $filename") or die "Error opening $filename\n";
  my $blank = "| ";
  my $lineCtr = 0;
  printf(Spool "****************Biztalk Check****************\n");
while( $Conn->FetchRow() ) {


    printf(Spool "\n%s %s %s %s\n",
    $Conn->Data("COMP"),
    $Conn->Data("SI"),
    $Conn->Data("num_swifts"),
    $Conn->Data("STATUS")
   );
     }
     
   $Conn->Close();
   
   close Spool;

Could anyone suggest what is wrong here ?

2

There is no problem in the statements used in both the approaches.

  1. check that zero file size is created as soon as you ran the perl script. Or output or previous run.
  2. Check the file was created by another user you dont have permission to over write/append it.
3

I am executing it from my local machine and i have write access to the share where i spool the file, the file is created 0 byte so it rules out the permission issue

4

Can you post your entire Perl program ?

tyler_durden

5

Here is the entire code

use Win32::ODBC;
use Time::Local;
use Getopt::Std;




my $cString = sprintf("Driver=SQL Server;Server=szrh721;Database=pagesSys_5701SR4;Trusted Connection=yes");   
my $filename="C:\\Perl\\scriptt\\DB.txt";
#my $filename="P:\\DB.txt";
unlink($filename);
$dbQuery = "BEGIN

SET NOCOUNT ON

DECLARE
\@V_BATCHJOBID INT ,
\@V_jobaccountid INT ,
\@V_jobaccountstatusid INT,
\@V_PCName VARCHAR(50),
\@V_JobName VARCHAR(50),
\@V_Description VARCHAR(50),
\@V_ErrorMessage VARCHAR(3000),
\@V_StatusTimeStamp DATETIME ,
\@V_Flag_Stuck VARCHAR(2),
\@V_Limit varchar(2)
-- One hour
SET \@V_Limit =1


DECLARE CUR_PEND CURSOR FOR 
SELECT  JobType.BATCHJOBID,
	PCStatus.jobaccountid,
	CurrStatus.jobaccountstatusid,
	JobType.PCName,
        JobType.Name,
        PCStatus.Description,
        PCStatus.ErrorMessage,
        PCStatus.StatusTimeStamp
  FROM (SELECT BJ.PCName, BJ.BATCHJOBID AS BATCHJOBID ,BJ.Name as JobName, BJT.Name, BJT.TimeLimit
          FROM BatchJob BJ (nolock), BatchJobType BJT (nolock)
         WHERE BJ.BatchJobTypeID = BJT.BatchJobTypeID
           AND (BJ.BatchJobStatusID = 1 OR BJ.BatchJobStatusID = 3)
           AND BJ.BatchJobID =
               (SELECT Min(BJ2.BatchJobID)
                  FROM BatchJob BJ2 (nolock)
                 WHERE BJ2.PCName = BJ.PCName
                   and (BJ.BatchJobStatusID = 1 OR BJ.BatchJobStatusID = 3))) JobType
  LEFT OUTER JOIN (SELECT Distinct Jas.PCName,JAS.jobaccountstatusid,JAS.jobaccountid,
                                   Acs.Description,
                                   Jas.StatusTimeStamp,
                                   Jas.ErrorMessage
                     FROM AccountStatus Acs (nolock), JobAccountStatus Jas (nolock)
                    WHERE Jas.StatusID = Acs.StatusID
                      AND Jas.StatusTimeStamp =
                          (SELECT Max(Jas2.StatusTimeStamp)
                             FROM JobAccountStatus Jas2 (nolock)
                            WHERE Jas.PCName = Jas2.PCName)) PCStatus ON JobType.PCName =
                                                                         PCStatus.PCName
  LEFT OUTER JOIN (SELECT BJ.PCName, Count(*) as c
                     FROM BatchJob BJ (nolock)
                    WHERE (BJ.BatchJobStatusID = 1 OR
                          BJ.BatchJobStatusID = 3)
                    Group by PCName) Assigned ON JobType.PCName =
                                                 Assigned.PCName
 JOIN (select jobaccountstatusid from JobAccountStatus(nolock)) CurrStatus 
on CurrStatus.jobaccountstatusid=PCStatus.jobaccountstatusid
OPEN CUR_PEND

FETCH NEXT FROM CUR_PEND INTO 
\@V_BATCHJOBID  ,
\@V_jobaccountid ,
\@V_jobaccountstatusid,
\@V_PCName ,
\@V_JobName ,
\@V_Description ,
\@V_ErrorMessage ,
\@V_StatusTimeStamp 

	WHILE \@\@FETCH_STATUS = 0
	BEGIN
     --- Find current pending reports
SET \@V_Flag_Stuck= (SELECT COUNT(b.BatchJobID)
 FROM   BatchJob b (nolock)
 WHERE  b.NextDate <= getdate()
 AND b.Active = 1
 AND b.BatchJobStatusID <= 3 
 AND B.BATCHJOBID <> \@V_BATCHJOBID)



-- FORMAT FOR OUTPUT
/*
       select @v_batchjobid as Batchjobid,
	      @V_StatusTimeStamp as VisibleTime,
	      BJ2.STARTDATE as ActualStartTime,
	      @V_PCName as PCNAME,
	      datediff(mi,BJ2.STARTDATE,getdate())as RunTime from  BatchJob BJ2 
	      WHERE BJ2.BATCHJOBID= @V_BATCHJOBID 
              and  datediff(mi,BJ2.STARTDATE,getdate()) > 2 */

 select  'JOB ID: '+cast(\@v_batchjobid as varchar(12))+
	 ' STARTED AT: '+  CAST(  \@V_StatusTimeStamp AS VARCHAR(20))+
	 ' ON '+    \@V_PCName +
	 ' HAS BEEN RUNNING FOR MORE THAN '+  CAST(  datediff(hh,BJ2.STARTDATE,getdate()) AS VARCHAR(8))+
         ' Hour(s). CURRENTLY '+
          CAST (\@V_Flag_Stuck AS VARCHAR(10)) +
         ' WAITING ' as STATUS from  BatchJob BJ2 (nolock)
	      WHERE BJ2.BATCHJOBID= \@V_BATCHJOBID 
              and  datediff(mi,BJ2.STARTDATE,getdate()) >= \@V_LIMIT



	FETCH NEXT FROM CUR_PEND INTO 
	\@V_BATCHJOBID  ,
	\@V_jobaccountid ,
	\@V_jobaccountstatusid,
	\@V_PCName ,
	\@V_JobName ,
	\@V_Description ,
	\@V_ErrorMessage ,
	\@V_StatusTimeStamp

        END

CLOSE CUR_PEND
DEALLOCATE CUR_PEND

END";


############################################################
# connect
my $Conn;
$Conn = new Win32::ODBC($cString);
die "Unable to connect to database" . Win32::ODBC::Error(  ) . "\n"
  unless (defined $Conn);

#printf("Using %s database on %s \n\n", $configData->{"db"}, $configData->{"server"} );

#printf("%s\n\n", $sql);

if( $Conn->Sql($dbQuery ) )  
  {
  print "Query failed. \n";
  print "Error: " . $Conn->Error() . "\n";
  print $dbQuery;
  $Conn->Close();
  exit(1);
  }    
  
  my $temp="";

  open(Spool, ">>$filename") or die "Error opening $filename\n";
 
while( $Conn->FetchRow() ) 
{
     my $Temp =$Conn->Data("STATUS");
     #print "Value is $Temp";
     print Spool "This line goes to the file.\n"; #Testing 
     printf(Spool "%s",$Conn->Data("STATUS" ));
 }
     
    $Conn->Close();
    close Spool;
   
   
   ###Read Spool file if it has something to say
   
   open(Spool, "> $filename") or die "Error opening $filename\n";
    while (<spool>) {}
    my $lineCtr =  $.;
   print $lineCtr;
   
   if (@linctr >0) {
   #Send Email using VB code
   
   system("start C:\\My-Scripts\\Email_Batch.vbs"); 
   
 }
    
   #unlink($filename);
   exit(0);

---------- Post updated at 02:27 PM ---------- Previous update was at 02:08 PM ----------

Sorry, I found my Mistake, After spooling to the file , i again open the file for writing , this creates a new file which is 0 byte..
Apologies for wasting everyones time :frowning:

6

Your problem is near the end of the script:

 ###Read Spool file if it has something to say
   
   open(Spool, "> $filename") or die "Error opening $filename\n";
    while (<spool>) {}
    my $lineCtr =  $.;
   print $lineCtr;
   
   if (@linctr >0) {
   #Send Email using VB code
   
   system("start C:\\My-Scripts\\Email_Batch.vbs"); 
   
 }
    
   #unlink($filename);
   exit(0);

U have to use:

open(Spool, "> $filename") or die "Error opening $filename\n";