Line Continuation

[Peter House]

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

[Michael Mattias]

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]

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' ........

stmt = USING$("select name from customer where cust_no='&', Custno$)

or even

stmt = USING$("select name from customer where cust_id=&&&', $SQ, CustId$, $SQ)

MCM

[Gösta H. Lovgren-2]

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:

#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-)
=========================================

[Peter House]

Line Continuation Continued

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

#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

[Mel Bishop]

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."

[Peter House]

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

[Michael Mattias]

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?

  S$ =        _ 
#STRINGBLOCK 
   select X,  SUM(A)  
     from Y 
     where Z = ? 
     group by    G 
     order by    1 
#ENDSTRINGBLOCK

MCM

[Stanley Durham]

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

  
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

[Mel Bishop]

I got to thinking about the subject of this thread.

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

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.

[Gösta H. Lovgren-2]

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,

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
============================================================

[Peter House]

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

[Mel Bishop]

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.

[Peter House]

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

[Paul Squires]

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.

'//
'//  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)

[David Roberts]

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

[Christopher Carroll]

Array and JOIN$

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$

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

s$ = "Something"
s$ = s$ +"Something Else"
s$ = s$ +"Something"
s$ = s$ +"Something More"
s$ = s$ +"Something"
s$ = s$ +"Something Pretty"

can be replaced with

s$  = "Something"
s$ += "Something Else"
s$ += "Something"
s$ += "Something More"
s$ += "Something"
s$ += "Something Pretty"

[Kev Peel]

Here's something simple, use tags:

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

[Gösta H. Lovgren-2]

Array and JOIN$

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

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)
=================================================

[Michael Mattias]

>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