Announcement

Collapse
No announcement yet.

Line Continuation

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Line Continuation

    I use this type of block structure for creating SQL queries throughout my code.

    I think it would be really nice to have a type of BLOCK continuation where it is not required to place line continuation characters at the end of each line. Maybe something like a line continuation meta command like #LINE, #ENDLINE.

    Maybe some of you have a better way of accomplishing this task.

    I have used Sql = Sql & "Add to Sql" in the past but this seems resource wasteful.

    Any Ideas???

    Sql = " UPDATE" & _
    $TBL_TRUCK & _
    " SET" & _
    " attached_bol = " & ShipRec.BOL & _
    " FROM" & _
    " (SELECT TOP " & Trim$(Str$(Qty)) & " * FROM" & _
    " (SELECT DISTINCT T3.A4GLIdentity FROM" & _
    " (SELECT TOP 100 PERCENT FROM" & _
    $TBL_TRUCK & _
    " LEFT JOIN " & $TBL_CARTON & _
    " ON UNIQUE_PALLET_NO = CL_SHIP_ATCHD_CODE" & _
    " WHERE CL_SHIP_ORDER_NO = '" & OrderNum & "'" & _
    " ORDER BY UNIQUE_PALLET_NO" & _
    " ) T3" & _
    " ) T2" & _
    " ) T1" & _
    " WHERE " & $TBL_TRUCK & ".A4GLIdentity = T1.A4GLIdentity"


    Thanks,

    Peter House

  • #2
    Just from experience....

    COBOL has a continuation character which can be used to continue quoted strings to a second physical line, but in all the years I did COBOL I never used it, and I believe I only saw it used once. Getting the spacing right is darned near impossible. I take that back.. it IS impossible.

    Note also you can't add a 'continuation' character without also adding an 'escape' character, too, as you could not use the continuation character as a literal without one.

    So now you have to deal with the problem of using the 'escape' character as a literal... and even doubling it up doesn't help, because what if you need a 'double-escape' character?

    All in all, I'm not sure there is a way to accomplish what you want... but I'm sure if you can come up with an idea the folks at PowerBASIC would be happy to listen.

    MCM
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      FWIW... you can send an SQL string with additional embedded spaces as well as embedded CRLF and the DBMS will ignore them. eg, the way I built the SQL strings in this demo:
      Generic 'ADO' Connection and Query Tester (CC 5+/Win 9+) 11-02-08

      Or, you can use replaceable tokens in a 'base' string and the REPLACE statement or REGREPL to change those tokens.

      And, if the statement won't go over 1024 total characters, USING$() is a great tool, too, especially when you need 'single-quoted string literals' ........
      Code:
      stmt = USING$("select name from customer where cust_no='&', Custno$)
      or even
      Code:
      stmt = USING$("select name from customer where cust_id=&&&', $SQ, CustId$, $SQ)

      MCM
      Michael Mattias
      Tal Systems (retired)
      Port Washington WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        Originally posted by Michael Mattias View Post
        All in all, I'm not sure there is a way to accomplish what you want... but I'm sure if you can come up with an idea the folks at PowerBASIC would be happy to listen.

        MCM
        How about this for NFS:
        Code:
        #LINE ' concatenate following lines
        Sql = " UPDATE"
        $TBL_TRUCK
        " SET"
        " attached_bol = " & ShipRec.BOL
        " FROM"
        " (SELECT TOP " & Trim$(Str$(Qty)) & " * FROM"
        " (SELECT DISTINCT T3.A4GLIdentity FROM"
        " (SELECT TOP 100 PERCENT FROM"
        $TBL_TRUCK
        " LEFT JOIN " & $TBL_CARTON
        " ON UNIQUE_PALLET_NO = CL_SHIP_ATCHD_CODE"
        " WHERE CL_SHIP_ORDER_NO = '" & OrderNum & "'"
        " ORDER BY UNIQUE_PALLET_NO"
        " ) T3"
        " ) T2"
        " ) T1"
        " WHERE " & $TBL_TRUCK & ".A4GLIdentity = T1.A4GLIdentity"
        #ENDLINE 'stop concatenation
        ?

        Or better yet just write a little PB Quickie that reads a textfile and concatenates it to a single line, then outputs it to the clipboard?

        =========================================
        "Men fear thought
        as they fear nothing else on earth
        more than ruin
        more even than death. ...
        Thought is subversive and revolutionary,
        destructive and terrible,
        thought is merciless to privilege,
        established institutions,
        and comfortable habit.
        Thought looks into the pit of hell
        and is not afraid.
        Thought is great and swift and free,
        the light of the world,
        and the chief glory of man."
        Bertrand Russell (1872-)
        =========================================
        Last edited by Gösta H. Lovgren-2; 23 Aug 2009, 11:09 AM.
        It's a pretty day. I hope you enjoy it.

        Gösta

        JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
        LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

        Comment


        • #5
          Line Continuation Continued

          I was really thinking something more along the lines of this example and not a new continuation character.

          Code:
          #SINGLELINE
            Sql = " UPDATE"
                    " " & $TBL_TRUCK
                    " SET"
                    " attached_bol = " & ShipRec.BOL
                    " FROM"
                    " (SELECT TOP " & Trim$(Str$(Qty)) & " * FROM"
                    " (SELECT DISTINCT T3.A4GLIdentity FROM"
                    " (SELECT TOP 100 PERCENT FROM"
                    " " & $TBL_TRUCK
                    " LEFT JOIN " & $TBL_CARTON
                    " ON UNIQUE_PALLET_NO = CL_SHIP_ATCHD_CODE"
                    " WHERE CL_SHIP_ORDER_NO = '" & OrderNum & "'"
                    " ORDER BY UNIQUE_PALLET_NO"
                    " ) T3"
                    " ) T2"
                    " ) T1"
                    " WHERE " & $TBL_TRUCK & ".A4GLIdentity = T1.A4GLIdentity"
          #SINGLELINEEND

          Comment


          • #6
            Are you absolutely sure you want or need a routine for such a long string concanotation?

            It's been my personal experience that such long programming structures are hard to read and difficult to debug.

            If you do want/need it, you might consider this option:

            t$ = "The quick brown fox"
            t$ = t$ + " jumped over the"
            t$ = t$ + " lazy dog's back."
            There are no atheists in a fox hole or the morning of a math test.
            If my flag offends you, I'll help you pack.

            Comment


            • #7
              Line Continuation Continued

              Mel,

              I have used the technique you suggest with VB6 and it certainly makes it easy to comment out single lines and is fairly simple to format and read. The VB IDE also did not handle line continuation very well.

              The biggest problem is the multiple string concatenation takes a long time to execute based on what I have read. I changed to the line continuation character method with PB since I would like to let the compiler do this work as there is no value add to letting it happen at run time.

              I am not sure at this time which method is worse (neither is good IMHO) and was thinking there might be a better way.

              I could also write the code more cleanly but I feel it is important to keep the structure of the SQL Query I am building visible and not make the SQL any more difficult to read.

              When developing, I usually develop the basic SQL query in the SQL Analyzer and then copy and paste the code in Jellyfish. Gösta had a good suggestion to create an add-in which would do a format and paste from the clipboard.

              Thanks,

              Peter

              Comment


              • #8
                The biggest problem is the multiple string concatenation takes a long time to execute based on what I have read
                Well, sure, in general string concatenation is 'slow,' but how often are you actually doing it?

                And if you are doing it that much in this case, you'd be better off preparing your statements and binding some variables to it anyway.


                BTW, if you want to build string literals, why even bother with the quotes?

                Code:
                  S$ =        _ 
                #STRINGBLOCK 
                   select X,  SUM(A)  
                     from Y 
                     where Z = ? 
                     group by    G 
                     order by    1 
                #ENDSTRINGBLOCK
                MCM
                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  I had a request, long time ago, for a text block:

                  Code:
                    
                  TEXT
                  whatever gets placed here
                  gets stored in string as is.
                  Don't need to add quotes.
                  Can paste from word processor, etc...
                  END TEXT TO s
                  stanthemanstan~gmail
                  Dead Theory Walking
                  Range Trie Tree
                  HLib ~ Free Data Container Lib ~ Arrays, Lists, Stacks, Queues, Deques, Trees, Hashes

                  Comment


                  • #10
                    I got to thinking about the subject of this thread.

                    Long story short, you might try: (typing this free-hand, so...)

                    Code:
                    temp$ = string$(20000,chr$(0))
                    s$ = " UPDATE" : LL = 1 : mid$(temp$,LL,len(s$)) = s$ : LL = LL + len(s$)
                    s$ = " " & $TBL_TRUCK : mid$(temp$,LL,len(s$) = s$ : LL = LL + len(s$)
                    
                    Etc. Etc. and so on and so forth
                    
                    temp$ = trim$(temp$,chr$(0))
                    It's a variation of a single-character string build posted elsewhere on the board. I haven't tried this but it should speed things up considerably.
                    There are no atheists in a fox hole or the morning of a math test.
                    If my flag offends you, I'll help you pack.

                    Comment


                    • #11
                      Originally posted by Peter House View Post
                      The biggest problem is the multiple string concatenation takes a long time to execute based on what I have read. Thanks,

                      Peter
                      Gee, Peter, String concat is slow but so is the speed of light in some cases. What you have read must be pretty dated (circa 1980's, early 90's). Today in PB compilers it it's nearly instantaneous/unnoticeable unless you are doing it continuously 1,000's of times a second or something.

                      As for readability,
                      Code:
                      s$ = s$ +"Something"
                      s$ = s$ +"Something Else"
                      s$ = s$ +"Something"
                      s$ = s$ +"Something More"
                      s$ = s$ +"Something"
                      s$ = s$ +"Something Pretty"
                       
                      Clipboard Set to s$
                      Looks pretty readable to me.

                      ============================================================
                      "I heard someone tried the monkeys-on-typewriters bit
                      trying for the plays of W. Shakespeare,
                      but all they got was the collected works of Francis Bacon."
                      Bill Hirst
                      ============================================================
                      It's a pretty day. I hope you enjoy it.

                      Gösta

                      JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
                      LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

                      Comment


                      • #12
                        Line Continuation Part Deux

                        Gösta,

                        You have me laughing at myself for even considering string concatenation as a performance issue! I learned long ago that elegance can be beautiful and it can also cause you to do stupid things for the sole sake of elegance and never mind the rest of the project.

                        I have a single project which I ported over from VB. 100% the same functionality but VB builds a 4MB install and Powerbasic is under 100K in a single executable.

                        Michael,

                        I cannot find anything about #STRINGBLOCK Are you surethis is in Powerbasic 9 and not in Powerbasic 10 I don't see how it would support variable without performing substitution afterwards - which might be a good idea after all.

                        Mel,

                        I do not have a clue what your post is about. It looks like some type of allocate the string and then substitue stuff in. I don't get it.


                        I like working with Assembler - you know exactly what you are getting. Give me an 8 bit micro and I will move the world!

                        Thanks,

                        Peter

                        Comment


                        • #13
                          Originally posted by Peter House View Post
                          I do not have a clue what your post is about. It looks like some type of allocate the string and then substitue stuff in. I don't get it.
                          Your observation is exactly spot-on.

                          When building a string, like the one you want, you are running into memory block management. That slows things down considerably.

                          Running my technique (trick, scheme), you pre-build a string with dummy characters (chr$(0)) then replace the character positions with the one(s) you want. After all is said and done, remove any excess characters and there you are.

                          Since you are dealing with only one instance of memory management in creating the pre-build, replacing characters in the string is considerably faster than building it up on-the-fly.

                          I hope this explains the situation.
                          There are no atheists in a fox hole or the morning of a math test.
                          If my flag offends you, I'll help you pack.

                          Comment


                          • #14
                            Line Continuation More of the Same

                            Mel,

                            I thought it was probably something like that and said as much " It looks like some type of allocate the string and then substitue stuff in"

                            That and my wife and kids were calling me to dinner so not much time to read it fully or respond with much precision.

                            Makes sense it would be faster but it does not seem to work well for maintenance when something needs to be changed and all the insertion points shift.

                            Thanks for your time.

                            Peter

                            Comment


                            • #15
                              I have found the following routine to be excellent when dealing with a lot of string concatenations. It allocates 8K buffer blocks and fills them.

                              Code:
                              '//
                              '//  Fast function for concatenating many strings
                              '//
                              Function AppendStr2( ByVal stPos   As Long, _
                                                   ByRef sBuffer As String, _
                                                   ByVal Addon   As Long, _
                                                   ByVal lenAdd  As Long _
                                                   ) Export As Long
                              
                              
                                  #Register None
                              
                                  Local pBuffer As Long
                                  
                                  ' If the buffer is not large enough to handle the adding
                                  ' of this string then we need to expand the buffer.
                                  If stPos + lenAdd + 1 > Len(sBuffer) Then
                                     sBuffer = sBuffer & String$( Max&(lenAdd, 8 * 1024), 0)   ' increase 8K minimum
                                  End If
                              
                                  ' Copy the new string to the end of the buffer
                                  pBuffer = StrPtr(sBuffer)
                              
                                  ! cld               ; Read forwards
                              
                                  ! mov edi, pBuffer  ; Put buffer address In edi
                                  ! Add edi, stPos    ; Add starting offset To it
                              
                                  ! mov esi, Addon    ; Put String address In esi
                                  ! mov ecx, lenAdd   ; length In ecx As counter
                              
                                  ! rep movsb         ; Copy ecx Count Of bytes From esi To edi
                              
                                  ! mov edx, stPos
                                  ! Add edx, lenAdd   ; Add stPos And lenAdd For Return value
                              
                                  ! mov Function, edx
                              
                              End Function
                              
                              
                                 Local sBuffer    As String
                                 Local st         As String
                                 Local nPos       As Long
                                 Local y       As Long
                               
                                 nPos = 0
                                 For y = 1 To 100000
                                      st = "This is some string that I want to add."
                                      nPos = AppendStr2( nPos, sBuffer, StrPtr(st), Len(st) )
                                 Next
                                 sBuffer = Left$(sBuffer, nPos)
                              Paul Squires
                              FireFly Visual Designer (for PowerBASIC Windows 10+)
                              Version 3 now available.
                              http://www.planetsquires.com

                              Comment


                              • #16
                                I would have thought that there was a place for BUILD$ here somewhere.

                                Comment


                                • #17
                                  Array and JOIN$
                                  Code:
                                  Local asSql() As String
                                  Local sSql As String
                                  
                                  ReDim asSql(16)
                                  
                                  asSql(0 ) = "UPDATE"
                                  asSql(1 ) = $TBL_TRUCK
                                  asSql(2 ) = "SET"
                                  asSql(3 ) = "attached_bol = " + ShipRec.BOL
                                  asSql(4 ) = "FROM"
                                  asSql(5 ) = "(SELECT TOP " + RTrim$(Str$(Qty)) + " * FROM"
                                  asSql(6 ) = "(SELECT DISTINCT T3.A4GLIdentity FROM"
                                  asSql(7 ) = "(SELECT TOP 100 PERCENT FROM"
                                  asSql(8 ) = $TBL_TRUCK
                                  asSql(9 ) = "LEFT JOIN " + $TBL_CARTON
                                  asSql(10) = "ON UNIQUE_PALLET_NO = CL_SHIP_ATCHD_CODE"
                                  asSql(11) = "WHERE CL_SHIP_ORDER_NO = " + $SQ + OrderNum + $SQ
                                  asSql(12) = "ORDER BY UNIQUE_PALLET_NO"
                                  asSql(13) = ") T3"
                                  asSql(14) = ") T2"
                                  asSql(15) = ") T1"
                                  asSql(16) = "WHERE " + $TBL_TRUCK + ".A4GLIdentity = T1.A4GLIdentity"
                                  
                                  sSql = Join$(asSql(), $SPC)
                                  Note that leading spaces are removed from each string as the $SPC delimiter in JOIN$ will manage this; RTRIM$ is used instead of TRIM$ to shave a smidgen of time; $SQ instead of "'" for readability and to help reduce errors in typing; + instead of & as a concatenation operator as this seems to be the PB custom.


                                  BUILD$
                                  Code:
                                  Local s1, s2, s3, s4, s5, s6, s7, s8 As String
                                  Local s9, s10, s11, s12, s13, s14, s15, s16, s17 As String
                                  Local sSql As String
                                  
                                  s1  = " UPDATE"
                                  s2  = $TBL_TRUCK
                                  s3  = " SET"
                                  s4  = " attached_bol = " + ShipRec.BOL
                                  s5  = " FROM"
                                  s6  = " (SELECT TOP " + RTrim$(Str$(Qty)) + " * FROM"
                                  s7  = " (SELECT DISTINCT T3.A4GLIdentity FROM"
                                  s8  = " (SELECT TOP 100 PERCENT FROM"
                                  s9  = $TBL_TRUCK
                                  s10 = " LEFT JOIN " + $TBL_CARTON
                                  s11 = " ON UNIQUE_PALLET_NO = CL_SHIP_ATCHD_CODE"
                                  s12 = " WHERE CL_SHIP_ORDER_NO = " + $SQ + OrderNum + $SQ
                                  s13 = " ORDER BY UNIQUE_PALLET_NO"
                                  s14 = " ) T3"
                                  s15 = " ) T2"
                                  s16 = " ) T1"
                                  s17 = " WHERE " + $TBL_TRUCK + ".A4GLIdentity = T1.A4GLIdentity"
                                  
                                  sSql = Build$(s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17)
                                  Using a string array and JOIN$ is more simple and clean than using BUILD$ here.

                                  The time taken to build a SQL statement with whichever technique will be considerably faster than executing it, so it is better (in my opinion) to aim for readability instead in its construction.


                                  Compound assignment
                                  With PB9+, the following
                                  Code:
                                  s$ = "Something"
                                  s$ = s$ +"Something Else"
                                  s$ = s$ +"Something"
                                  s$ = s$ +"Something More"
                                  s$ = s$ +"Something"
                                  s$ = s$ +"Something Pretty"
                                  can be replaced with
                                  Code:
                                  s$  = "Something"
                                  s$ += "Something Else"
                                  s$ += "Something"
                                  s$ += "Something More"
                                  s$ += "Something"
                                  s$ += "Something Pretty"

                                  Comment


                                  • #18
                                    Here's something simple, use tags:

                                    Code:
                                    data = "The quick %1% jumps over the %2%"
                                     
                                    REPLACE "%1%" WITH "brown fox" In data
                                    REPLACE "%2%" WITH "lazy dog" In data
                                     
                                    ? data
                                    *The text in "data" would be kept in a file

                                    I abandoned string concatenation for anything complex a long time ago. I found the tag replacement method quick and a lot more readable
                                    kgpsoftware.com | Slam DBMS | PrpT Control | Other Downloads | Contact Me

                                    Comment


                                    • #19
                                      Originally posted by Christopher Carroll View Post
                                      Array and JOIN$


                                      Maybe better. Lines can be inserted/deleted without have to renumber every element. Still (nearly) as readable.

                                      Code:
                                      Local asSql() As String
                                      Local sSql As String
                                      Local ctr As Long
                                       
                                      ReDim asSql(1 To 100)'<< make bigger than ever used
                                      Reset ctr 'just to be readable & reminder
                                       
                                      Incr ctr: asSql(ctr) = "UPDATE"
                                      Incr ctr: asSql(ctr) = $TBL_TRUCK
                                      Incr ctr: asSql(ctr) = "SET"
                                      Incr ctr: asSql(ctr) = "attached_bol = " + ShipRec.BOL
                                      Incr ctr: asSql(ctr) = "FROM"
                                      Incr ctr: asSql(ctr) = "(SELECT TOP " + RTrim$(Str$(Qty)) + " * FROM"
                                      Incr ctr: asSql(ctr) = "(SELECT DISTINCT T3.A4GLIdentity FROM"
                                      '...
                                        ReDim Preserve asSql(1 To ctr)'make right size
                                       
                                      sSql = Join$(asSql(), $Spc)
                                      Probably best reading in a text file though.

                                      =================================================
                                      "I'm all in favor of keeping dangerous weapons
                                      out of the hands of fools.
                                      Let's start with typewriters (word processors)."
                                      Frank Lloyd Wright (1868-1959)
                                      =================================================
                                      It's a pretty day. I hope you enjoy it.

                                      Gösta

                                      JWAM: (Quit Smoking): http://www.SwedesDock.com/smoking
                                      LDN - A Miracle Drug: http://www.SwedesDock.com/LDN/

                                      Comment


                                      • #20
                                        >Probably best reading in a text file though

                                        Or if you really don't want the possibility of a user 'messing with' your SQL with Notepad... a program resource.

                                        User-Defined Resource Demo January 26 2003
                                        Michael Mattias
                                        Tal Systems (retired)
                                        Port Washington WI USA
                                        [email protected]
                                        http://www.talsystems.com

                                        Comment

                                        Working...
                                        X