Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL pretty print

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX
View previous topic :: :: View next topic  
Author Message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Sun May 10, 2020 4:17 am    Post subject: SQL pretty print
Reply with quote

to save reinventing the wheel, does anyone have, or know of, any REXX module that converts a string into formatted text.

eg, if src = 'select * from sysibm.sysdummy1 where 1=1 and (2=2 or 3=3)', then output (call it outf.) might be something like:

Code:
select *
from
sysibm.sysdummy1
where
    1=1
or
    (2=2 or 3=3)
Back to top
View user's profile Send private message

sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 778
Location: Maryland

PostPosted: Sun May 10, 2020 6:28 pm    Post subject:
Reply with quote

I tried to expose the real, fundamental problem causing this issue, but my post has been silently removed by moderators.

So far this issue is equivalent to:
Quote:
Is there any tool or any way to fix the problem, when my house has been built topsy-turvy?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2355
Location: Hampshire, UK

PostPosted: Sun May 10, 2020 8:22 pm    Post subject: Reply to: SQL pretty print
Reply with quote

Which is not the answer to the question - does anybody know of a tool for prettying up SQL code? Correcting the problem at source is not going to correct the current mess.
Back to top
View user's profile Send private message
Joerg.Findeisen

Active User


Joined: 15 Aug 2015
Posts: 250
Location: Bamberg, Germany

PostPosted: Sun May 10, 2020 8:45 pm    Post subject: Re: Reply to: SQL pretty print
Reply with quote

Nic Clouston wrote:
Does anybody know of a tool for prettying up SQL code?

You mean something like https://codebeautify.org/sqlformatter?
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Mon May 11, 2020 6:52 am    Post subject:
Reply with quote

yes, that will do fine ...

as I said, i could write it myself, but wondering if someone has done it in REXX.

(using this site online or as a possible web service is not an option )
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2187
Location: Silicon Valley

PostPosted: Mon May 11, 2020 10:44 am    Post subject: Reply to: SQL pretty print
Reply with quote

Quote:
but wondering if someone has done it in REXX.


This sort of worked for me as an editor macro. It was part of another tool, so you might see a few things commented out.

Code:
/* rexx */
/*-------------------------------------------------------------------*/
/* Exec name . . : zzzESQLM                                          */
/* Function  . . : macro to split SQL statements                     */
/*                                                                   */
/* Author  . . . : Pedro Vera                                        */
/*                                                                   */
/*-------------------------------------------------------------------*/

/*-------------------------------------------------------------------*/
/* customization section                                             */
/*-------------------------------------------------------------------*/
         /* split line before  */
/*trace('r')*/

srch.1 = ' AND '      ;indent.1 = 2
srch.2 = ' OR '       ;indent.2 = 3
srch.3 = ' ON '       ;indent.3 = 2
srch.4 = ' WHERE '    ;indent.4 = 0
srch.5 = ' LEFT '     ;indent.5 = 0
srch.6 = ' SELECT '   ;indent.6 = 0
srch.7 = ' GROUP '    ;indent.7 = 0
srch.8 = ' FROM '     ;indent.8 = 0
srch.9 = ' FOR '      ;indent.9 = 0
srch.10= ' WHEN '     ;indent.10= 3
srch.11= ' THEN '     ;indent.11= 6
srch.12= ' ELSE '     ;indent.12= 0
srch.13= ' END '      ;indent.13= 0
srch.14= 'MERGE '     ;indent.14= 2
srch.15= ' USING '    ;indent.15= 1
srch.16= ' UPDATE '   ;indent.16= 1
srch.17= ' INSERT '   ;indent.17= 1
srch.18= ' CASE '     ;indent.18= 1
srch.19= 'VALUES '    ;indent.19= 1

         /* split line after */
after  = 19           ;
srch.20= ';'          ;indent.20= 0
srch.21= '+'          ;indent.21= 0
srch.22= '-'          ;indent.22= 0
srch.23= '/'          ;indent.23= 0
srch.0 = 23

         /* ignore within parens */
char.1 = 'VARCHAR('    ;
char.2 = ' CHAR('       ;
char.0 = 2

/*-------------------------------------------------------------------*/
/* Process the file and break up the lines                           */
/*-------------------------------------------------------------------*/
Address ISREDIT
"MACRO"
"UNNUM"
"HILITE ON OTHER PAREN "

/* "PASTE ADBISQLV DELETE" */

/*-------------------------------------------------------------------*/
/* Get all of the lines and compose single long string for           */
/* ease of parsing                                                   */
/*-------------------------------------------------------------------*/
"cursor = .zlast"
"(row) = CURSOR"
alllines = ' '
Do x = 1 to row
  "(aline) = LINE" x
  /* If NUM ON, ignore numbers */
  If datatype(right(aline,8),'N') Then
    parse var aline aline 73 .
  alllines = alllines || strip(aline) || ' '
End
"DELETE ALL NX"

/*-------------------------------------------------------------------*/
/* Change single quote to '~' to simplify subsequent changes         */
/*-------------------------------------------------------------------*/
alllines = translate(alllines,"~", "'")


workline = alllines


/*-------------------------------------------------------------------*/
/* Remove quoted string in working storage to avoid false positives  */
/*-------------------------------------------------------------------*/
qt0   = 2
qt2   = 0
Do y  = 1 To 10 While (qt0 > 1)
  /* find a quote (left) */
  qt1 = Pos("~",workline, qt0)
  If qt1 > 0 Then
    Do
      /* find a quote (right)                            */
      qt2     =     Pos("~",workline, qt1+1)

      /* remove quoted string To avoid false positives   */
      workline = Overlay(' ',workline, qt1+1, qt2-qt1-1)

      qt      = 0
    End
  qt0 = qt2 + 1

End


/*-------------------------------------------------------------------*/
/* determine parenthesis level for each paren in the SQL             */
/*-------------------------------------------------------------------*/
parens   = copies(' ',length(workline))

plevel = 0
Do z = 1 to length(workline)
  char1 = substr(workline,z,1)
  Select
    When char1 = '(' Then
      Do
        plevel = plevel + 1
        parens = OVERLAY(plevel, parens, z, 1)
        say z '= <'char1'>' plevel
      End
    When char1 = ')' Then
      Do
        parens = OVERLAY(plevel, parens, z, 1)
        say z '= <'char1'>' plevel
        plevel = plevel - 1
      End
    Otherwise
      say z '= <'char1'>'
  End
End

/*-------------------------------------------------------------------*/
/* Remove VALUE commas     from working storage to void line breaks  */
/*-------------------------------------------------------------------*/
strt = 2
valstr = 1
Do While (valstr > 0)
  valstr = Pos('VALUE(', workline, strt)
  If (valstr  > 0) Then
    Do
      valst2 = valstr + length('VALUE')
      plevel  = substr(parens,valst2,1)
      parend  =    Pos(plevel, parens, valst2+1)
      worksub = substr(workline, valst2, parend-valst2+1)
      commasub = 1
      Do While (commasub > 0)
        commasub =     Pos(',', worksub)
        If commasub > 0 Then
          Do
            commaline = commasub + valst2 - 1
            worksub  = Overlay(' ', worksub , commasub , 1)
            workline = Overlay(' ', workline, commaline, 1)
          End
      End
      strt     = valstr + 2
      say 'work<'workline'>'
    End
End



/*-------------------------------------------------------------------*/
/* Remove TYPE values      from working storage                      */
/*-------------------------------------------------------------------*/
Do ix = 1 To char.0
  strt   = 2
  valstr = 1
  Do While (valstr > 0)
    valstr = Pos(char.ix, workline, strt)
    If (valstr  > 0) Then
      Do
        valstr   =  valstr + length(char.ix)
        valend   =     Pos(')', workline, valstr)

        workline = Overlay(' ', workline, valstr, valend-valstr)
        strt     = valend
      End
  End
End


/*-------------------------------------------------------------------*/
/* Find keywords and insert carriage return                          */
/*-------------------------------------------------------------------*/
Do ix = 1 To srch.0
  strt = 2
  fnd  = 1
  Do While (fnd > 0)
    fnd  = Pos(srch.ix, workline,strt)
    If fnd  > 0 Then
      Do
        brk = fnd -1
        If ix > after  Then
           brk   = fnd
        workline = Insert('05'x || Left(' ',indent.ix) , workline , brk)
        alllines = Insert('05'x || Left(' ',indent.ix) , alllines, brk)
        strt     = fnd + 2 + indent.ix
      End
  End
End

/*-------------------------------------------------------------------*/
/* break up the line and insert into edit space                      */
/*-------------------------------------------------------------------*/
therest = alllines
Do While (Length(therest) > 0)
  Parse Var therest aline '05'x  therest
  Parse Var aline keywrd .
  If keywrd = 'LEFT' |,
     keywrd = 'WHERE' Then
    "line_AFTER .zlast= <1 ' ' >"  /* skip a line */
  "line_AFTER .zlast= <1 '"aline"' >"
End

"CHANGE '~' '7D'x ALL"
"RESET"
/* "DEFINE END  ALIAS CANCEL" */



It works on the whole file. If you have sql imbedded into another language, it will not work.

fyi. I had wanted to improve this and make it into a product, but it was a spare time project and I retired before finishing it.
Back to top
View user's profile Send private message
jzhardy

New User


Joined: 31 Oct 2006
Posts: 73
Location: brisbane

PostPosted: Tue May 12, 2020 2:21 am    Post subject:
Reply with quote

brilliant ... this is exactly the sort of thing I was looking for.

This will save me a lot of time!

many thanks
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2187
Location: Silicon Valley

PostPosted: Tue May 12, 2020 3:29 am    Post subject: Reply to: SQL pretty print
Reply with quote

Quote:
brilliant ... this is exactly the sort of thing I was looking for.


{Blushing}
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> CLIST & REXX All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Print next line strings when a condit... tgmuntor DFSORT/ICETOOL 9 Wed May 20, 2020 9:08 pm
No new posts Submit Print Job For PDS Member With ... AllenSieracki TSO/ISPF 17 Fri Apr 10, 2020 9:31 pm
No new posts Print call stack in Mainframe C DIPANKAR SAHA ABENDS & Debugging 2 Thu Mar 15, 2018 2:58 pm
No new posts print out the correct info in LOOP? jackzhang75 CLIST & REXX 7 Wed Dec 23, 2015 10:39 pm
No new posts How to print a variable in SAS progra... LOKESH DAIVAM All Other Mainframe Topics 6 Thu Aug 13, 2015 12:24 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us