Shell Programming and Scripting

Hi,

I am extracting SQL queries into a file and the file is as follows

*********************************************************
select BatchKey ,restartStatus ,batchContextBuffer ,batchPgmId ,StartKey ,
EndKey ,Mcbatchcontrol_ver
from
qsecminload.Mcbatchcontrol_t where RefId = :1
*********************************************************
SELECT DEO_ENTITY_ID, DEO_EVNT_ID, DEO_DPT_ID, DPT_EVNT_TYP,
DPT_EVNT_GRP, DPT_SEC_ID, DPT_SEC_TYP, DPT_SEC_SB_TYP, DEO_OPTN_SEQ_N,
DEO_OPTN_TYP, DEO_DPOT_EVNT_OPTN.OU_ID, DEO_DPOT_EVNT_OPTN.PART_ID,
DEO_DPOT_EVNT_OPTN.USERID, DPT_EVNT_CLSFCTN
FROM
DPT_DPOT_EVNT_DTLS, EDD_EVNT_DT_DTLS, DEO_DPOT_EVNT_OPTN,
SBD_SYS_BSNS_DTS WHERE DPT_ENTITY_ID = EDD_ENTITY_ID AND
EDD_ENTITY_ID = DEO_ENTITY_ID AND SBD_ENTITY_ID = DEO_ENTITY_ID ORDER BY DEO_EVNT_ID ,
DEO_ENTITY_ID , DEO_DPT_ID , DEO_OPTN_SEQ_N
**********************************************************


I have to identify the pattern (from or FROM) and copy the contents till the Pattern (where or WHERE). Next I have to split the content copied based on comma(,) then count the number of variables.

The out put will have to be as follows
**********************************************************
qsecminload.Mcbatchcontrol_t
count=1
**********************************************************
DPT_DPOT_EVNT_DTLS, EDD_EVNT_DT_DTLS, DEO_DPOT_EVNT_OPTN,
SBD_SYS_BSNS_DTS
count=4

Please help. Thanks in advance

Please post what code you have written so far to solve your programs requirements.

# Program to get the Query, Parse count, Execute count, Fetch count, Total cpu time and Total elapsed time.

# Begin Prompt for User Input .. Input Trace File

$inputFile = &promptUser("\n\t\t Enter the TraceFile Name");


#Open input and output files and create File Handles

open(INFILE,"<$inputFile") or die " Input File opening ERROR :: $!";
$opFile = $inputFile.".xls";
open(OUTFILE,">$opFile") or die " Output File opening ERROR :: $!";

# Write the Headings to the file
print OUTFILE "SQL Query and the Indexes used in the Query is printed in the output_log file \n\n";
#print OUTFILE "===================================================================\n";
#print OUTFILE "SQL QUERY ~PARSE Count ~EXECUTE Count ~FETCH Count ~TOTAL CPU TIME ~TOTAL ELAPSED TIME ~FullTableScan\n";
# initializations

$SQLLINE="";
$CUTLINE="";
$CUTLINE1="";
$FLAG=0;
$SUFLAG=0;
$Indexflag=0;
$MyFlag=0;
$Rows=0;
$Blocks=0;
$Result=0;
$Zero=0;
$Level=10;
#
# Working on the input file One Line at a Time !!! Read from given I/P file Line by Line.
foreach $actual_line (<INFILE>)
{
# To get the PLAN !!!

# if line is a sql statement change the flag status
# and copy the line to a variable


if( $actual_line =~ m/^SELECT/ || $actual_line =~ m/^select/ || $actual_line =~ m/^update/ || $actual_line =~ m/^UPDATE/) {
$FLAG=1;
$SUFLAG=1;
#print OUTFILE "------------------------------------------------------------------------------- \n\n";
chomp($actual_line);
$SQLLINE=$actual_line;
}

#change the flag status when complete sql statement is read

elsif($actual_line =~ m/^call/){
#print "call";
$FLAG=0;
print OUTFILE "***************************************************************************** \n";
print OUTFILE "$SQLLINE" ;
$CUTLINE=$SQLLINE;
@charArray = split(/FROM/,$CUTLINE);
$CUTLINE1=@charArray[1];
print OUTFILE "$CUTLINE1" ;

$SQLLINE="";
next;
}

# read the complete sql statement line by line and join them together

elsif($FLAG==1) {
chomp($actual_line);
$line=$actual_line;
$temp=$SQLLINE;
$SQLLINE=$temp.$line;
next;
}
# Above code Completes the program for copying the SQL Query.


else{
next;
}



}


##
# Close all file handles !!!
##
close(INFILE) or die " INFILE CLOSING ERROR : $!";
close(OUTFILE) or die " OUTFILE CLOSING ERROR : $!";

#
# Display Console message to user ..

print "\t\tOUTPUT printed to File : $opFile \n";



# Prompting the usr to enter an Input file or accept a default I/P File ...
#
#

sub promptUser
{

local($promptString,$defaultValue) = @_;

if ($defaultValue)
{
print $promptString, "[", $defaultValue, "]: ";
}
else
{
print $promptString, ": ";
}

$| = 1; # force a flush after our print
$_ = <STDIN>; # get the input from STDIN (presumably the keyboard)

chomp;

if ("$defaultValue")
{
return $_ ? $_ : $defaultValue; # return $_ if it has a value
}
else
{
return $_;
}
}



This is the code that I have created. The problem comes as it is splitting for FROM but it is not splitting for from.
Also need to continue with the logic

Try this:

Code:

@charArray = split(/FROM|from/, $CUTLINE);

This might be more accurate:

@charArray = split(/\bfrom\b/i, $CUTLINE);

otherwise 'from' could be a substring of another word and will split the file incorrectly. 'i' makes the match case-insensitive so 'FROM' and 'from' will match.

Thanks Kevin.