Perl Programming for Splitting

Shell Programming and Scripting
1

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

2

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

3

# 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
$_ = &lt;STDIN&gt;;         \# 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

4

Try this:

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

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.

6

Thanks Kevin.