Breaking a String Across Multiple Lines

Question

I wrote a script which is connecting to an Oracle database to select multiple entries in a specific table.

The statement looks like this:

rs.open "SELECT PATH301 FROM NC301B WHERE EDIPROC like 'P30_' AND (LF301M > 0) AND (PATH301 NOT LIKE '%saptemp%') AND (PATH301 NOT LIKE '%SAPTEMP%') AND (PATH301 NOT LIKE '%usr%') AND (PATH301 NOT LIKE '%Windows%');", cn, 3

Now I want to know if it is possible to split this statement over multiple lines. For example like this:

rs.open "SELECT PATH301 FROM NC301B 
WHERE EDIPROC like 'P30_' 
AND (LF301M > 0) 
AND (PATH301 NOT LIKE '%saptemp%') 
AND (PATH301 NOT LIKE '%SAPTEMP%') 
AND (PATH301 NOT LIKE '%usr%') 
AND (PATH301 NOT LIKE '%Windows%');", cn, 3

That first SELECT Statement is way to big and not looking good at all. I hope you understand what I mean.

Solution 1

Well I'm going to post an answer anyway, never been a fan of the Fastest Gun in the West Problem

Using the Line Continuation Character

As I mentioned in the comments it sounds like you want to make the SQL command more readable in code. The normal way to do this is using the Line Continuation Character (_) also known as a Statement Break.

Dim sql
sql = "SELECT PATH301 " & _
  "FROM NC301B " & _
  "WHERE EDIPROC like 'P30_' " & _
  "AND (LF301M > 0) " & _
  "AND (PATH301 NOT LIKE '%saptemp%') " & _
  "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
  "AND (PATH301 NOT LIKE '%usr%') " & _
  "AND (PATH301 NOT LIKE '%Windows%');"

Bear in mind that this differs if we are not dealing with a string, for example to continue an If statement on to two lines would look something like this;

If result = ( _
  condition1 _
  And condition2 _
  And condition3) Then

Because we cannot break a string across lines we cheat by terminating the string continuing the line and then concatenating the string to the next string & _. This also means this will work;

Dim sql
sql = "SELECT PATH301 " _
  & "FROM NC301B " _
  & "WHERE EDIPROC like 'P30_' " _
  & "AND (LF301M > 0) " _
  & "AND (PATH301 NOT LIKE '%saptemp%') " _
  & "AND (PATH301 NOT LIKE '%SAPTEMP%') " _
  & "AND (PATH301 NOT LIKE '%usr%') " _
  & "AND (PATH301 NOT LIKE '%Windows%');"

It's all down to personal preference and neither way has any performance benefit over its counterpart.

Personally, though I find this approach is more trouble than it's worth, especially when it comes to SQL strings.

Using String Concatenation

Say you want to test without one of the conditions let's say

AND (PATH301 NOT LIKE '%SAPTEMP%')

trying to comment out that line will generate a

Microsoft VBScript compilation error:
Syntax error

Here is an example;

Dim sql
sql = "SELECT PATH301 " & _
  "FROM NC301B " & _
  "WHERE EDIPROC like 'P30_' " & _
  "AND (LF301M > 0) " & _
  "AND (PATH301 NOT LIKE '%saptemp%') " & _
  '"AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
  "AND (PATH301 NOT LIKE '%usr%') " & _
  "AND (PATH301 NOT LIKE '%Windows%');"

Which makes it very inflexible, which is why I prefer to use String Concatenation to build up a string, like this;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 "
sql = sql & "FROM NC301B "
sql = sql & "WHERE EDIPROC like 'P30_' "
sql = sql & "AND (LF301M > 0) "
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') "
sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') "
sql = sql & "AND (PATH301 NOT LIKE '%usr%') "
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

Although a little extra work it is far more flexible and allows you to test SQL strings by commenting out lines without affecting the entire SQL string, like so;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 "
sql = sql & "FROM NC301B "
sql = sql & "WHERE EDIPROC like 'P30_' "
sql = sql & "AND (LF301M > 0) "
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') "
'sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') "
sql = sql & "AND (PATH301 NOT LIKE '%usr%') "
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

Another little thing I like to do is follow each line with & vbNewLine like this;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 " & vbNewLine
sql = sql & "FROM NC301B " & vbNewLine
sql = sql & "WHERE EDIPROC like 'P30_' " & vbNewLine
sql = sql & "AND (LF301M > 0) " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%usr%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

That way when outputting the string (be in using Classic ASP, WScript etc) it formats correctly and when displaying in a HTML page (if using Classic ASP) you can easily use

sql = Replace(sql, vbNewLine, "<br />")

to format it correctly, very useful when trying to debug problems with dynamic SQL.

Solution 2

The SQL statement is a string, so you can split it into multiple strings and concatenate them. Use the line continuation operator (_) for wrapping the statement. For readability reasons I'd put the query in a variable though, like this:

qry = "SELECT PATH301 FROM NC301B " & _
      "WHERE EDIPROC like 'P30_' AND (LF301M > 0) " & _
      "AND (PATH301 NOT LIKE '%saptemp%') " & _
      "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
      "AND (PATH301 NOT LIKE '%usr%') " & _
      "AND (PATH301 NOT LIKE '%Windows%');"
rs.open qry, cn, 3

Multiline strings are not supported in VBScript.

Solution 3

Use line continuation (_) and concatenation or Join:

s = "a b c"
WScript.Echo s
s =   "a " _
    & "b " _
    & "c"
WScript.Echo s
s = Join(Array( _
          "a" _
        , "b" _
        , "c" _
))
WScript.Echo s

output:

cscript 37564704.vbs
a b c
a b c
a b c

Solution 4

Should do what you want, assuming you just want it to look nicer; this doesn't affect any functionality at all...

rs.open "SELECT PATH301 FROM NC301B " & _
"WHERE EDIPROC like 'P30_' " & _
"AND (LF301M > 0) " & _
"AND (PATH301 NOT LIKE '%saptemp%') " & _
"AND (PATH301 NOT LIKE '%SAPTEMP%') " & _ 
"AND (PATH301 NOT LIKE '%usr%')  " & _
"AND (PATH301 NOT LIKE '%Windows%');", cn, 3

View more solutions

Comments

[Developer Webs]

Why not just roll a simple string builder class and each line would call sb.Append("foo"). It would be far faster than calling a string constructor a LOT. That way you can still comment out individual lines. Sample code: docs.microsoft.com/en-us/previous-versions/office/developer/‌​…

[user692942]

@DeveloperWebs You certainly could, but I've not seen massive issues with performance when building strings especially ones that are used for SQL statements. Maybe if I was outputting HTML but then I'd just use Response.Write().

[Developer Webs]

With 10 concatenations (you're doing 2 per line plus calling the string constructor multiple times per line) the StringBuilder will be more efficient, and it meets your commenting out condition. Also, if the concatenations are within any type of loop, StringBuilder could be far faster.

[user692942]

@DeveloperWebs your preaching to the choir. I never said it wasn’t more efficient but unless you’re doing massive concatenations the performance benefit is going to be minimal. In fact most of the time would use a Stored Procedure instead of hardcoded SQL statements. Let’s also not forget exposing a .Net library via COM has it’s own overheads (registration of COM wrapper etc.).

[Developer Webs]

I was using a COM class :)

[user692942]

@DeveloperWebs you would have to there is no other approach if you wanted to use a .Net library. That was my point.

[Developer Webs]

I'm not sure why you're mentioning .NET. Perhaps I missed something. I was referring to a COM StringBuilder object (I linked to a Microsoft's sample VB6 example).

[user692942]

@DeveloperWebs sorry StringBuilder is a well known .Net class, didn’t see the VB source. Regardless the same still applies any outside library has to be implemented via COM.