[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Utility program to read SQL statements in SQTs.
A while back some poor soul who had lost his source code asked whether he
could reconstruct the original program from his SQT. The answer of course
is "no"; the question (and answer) are in SQR's FAQ.TXT in the installation
directory. In fact, it's probably axiomatic for any compiler, whose sole
reason d'etre is to produce fast, compact machine code.
Nonetheless, I found the question intriguing. If you run a Unix "more"
command on an SQT, you'll notice that all the SQL code is still readable.
(There are other things there too, like variables and procedure names). So
what I did was write a small SQR program which strips off the control
characters using a Unix "tr" command, and then formats the SQL code it
finds. Since the program is only 5K, I took the liberty of sending it out
directly to the SQRUG list.
Please note the following restrictions and disclaimers:
(1) It only works on Unix.
(2) It makes one (Oracle) database call using the "least" function. It
could be rewritten without this, but it wasn't worth the bother.
(3) Sometimes the SQL statements get a little chopped up.
(4) Usually some other ASCII characters (non-SQL) get left inside.
(5) I take no responsibility for the accuracy of any output.
(6) Please send comments or problems directly to my e-mail address:
chananm@netvision.net.il
(7) Don't start throwing away your source code!
It's not really very useful, more of a curiosity than anything else. Though
if you received a product with only the SQTs and wanted to see what they
were doing, I suppose it would be of some value.
Enjoy!
!******************************************************************************
! Name: READ_SQT.SQR !
! Purpose : Prints out SQL statements in an SQT !
! Description: Use system call to UNIX to use TR command to remove control !
! characters, then the SQR formats the output. !
! Notes : Not 100%, but prints almost all of the SQL. !
! Makes 1 (Oracle) database call to use the "least" funtion. !
! Need to rule with Mfile with the following line: !
! EXPRESSIONSPACE=30000
! !
! Usage: sqr read_sqt usr/pwd -MMfile !
! !
! Programmer : Chanan Morrison !
! Date : FEB 98 !
! Changes Log: !
! ---------------------------------------------------------------------------- !
! DATE | PROGRAMMER | DESC !
! ---------------------------------------------------------------------------- !
! | | !
! | | !
!******************************************************************************
Begin-Report
#define #file_width 2048
input $filename
Do MAIN
End-Report
Begin-Setup
page-size 2000 80
End-Setup
Begin-Heading 2
let $title = 'SQL statements in ' || $filename
print $title (1) center
print '_' (2,1,79) fill
End-Heading
!--------------------------------------------------------------------------
! Use Unix TR to remove control characters , then read
!--------------------------------------------------------------------------
Begin-Procedure MAIN
move {#file_width} to $width 9999
let $cmd = 'tr -cs ''[:alnum:][:punct:][:blank:]'' ''[ *]'' < ' ||
$filename || ' | fold -w' || $width || ' > temp0001 '
call system using $cmd #stat
open 'temp0001' as 1 for-reading record={#file_width} status=#filestat
if #filestat != 0
show 'Unable to open temporary file '
else
while not #end-file
Do READ
end-while
end-if
close 1
call system using 'rm temp0001' #stat
End-Procedure
!--------------------------------------------------------------------------
! Look for a keyword, and print text from that keyword till the next
!--------------------------------------------------------------------------
Begin-Procedure READ
read 1 into $record:{#file_width}
let $rec = lower($record)
let #start = 0
while 1
Do SEARCH_KEYWORD
if &next = 9999 or &next = 0
break
end-if
let #start = &next + 1
Do SEARCH_KEYWORD
if &next = 9999
let #len = 79
else
let #len = &next - #start
end-if
let $sub = substr($record,#start - 1,#len)
! Put blank line before certain key-words ...
let $check = lower(substr($sub,1,7))
if $check = 'select ' or $check = 'update ' or $check = 'insert '
or $check = 'delete '
if lower(substr($sub,1,10)) != 'update of '
print '' (+1,1)
end-if
end-if
! remove long number strings in SQR 3 ...
let #x = instr($sub,' 00',1)
if #x != 0
let $sub = substr($sub,1,#x)
end-if
let #x = instr($sub,'000000',1)
if #x != 0
let $sub = substr($sub,1,#x)
end-if
print $sub (+1,1) wrap 79 10
end-while
End-Procedure
!--------------------------------------------------------------------------
! Finds next keyword in record
!--------------------------------------------------------------------------
Begin-Procedure SEARCH_KEYWORD
let #a = instr($rec,'select ',#start)
let #b = instr($rec,'from ',#start)
let #c = instr($rec,'where ',#start)
let #d = instr($rec,'group by ',#start)
let #e = instr($rec,'order by ',#start)
let #f = instr($rec,'update ',#start)
let #g = instr($rec,'insert ',#start)
let #h = instr($rec,'delete ',#start)
let #i = instr($rec,'values',#start)
let #j = instr($rec,'and ',#start)
let #k = instr($rec,'for update of',#start)
let #l = instr($rec,'[$',#start)
Do DECODE(#A,$A)
Do DECODE(#B,$B)
Do DECODE(#C,$C)
Do DECODE(#D,$D)
Do DECODE(#E,$E)
Do DECODE(#F,$F)
Do DECODE(#H,$H)
Do DECODE(#G,$G)
Do DECODE(#I,$I)
Do DECODE(#J,$J)
Do DECODE(#K,$K)
Do DECODE(#L,$L)
Begin-Select
to_number(least($a,$b,$c,$d,$e,$f,$g,$h,$i,$j,$k,$l)) &next
>From DUAL
End-Select
End-Procedure
Begin-Procedure DECODE (#in_num,:$out_num)
if #in_num = 0
let $out_num = '9999'
else
move #in_num to $out_num 0999
end-if
End-Procedure
Chanan Morrison