You have NO IDEA how many hours I spent trying to figure out what "I" was doing wrong and thought I had solved it but then, accidentally, figured out it wasn't me :)
Here is some code that demonstrates the problem.
Jim
x = 0
If x == 1 Then
SQLText = $"
CREATE TRIGGER pn_notify_BINS BEFORE INSERT ON pn_notify
FOR EACH ROW
BEGIN
.....
END
$"
message("HEY",SQLText)
EndIf
The problem is the result of a interpreter optimization. You will need to switch to string concatenation under the conditions that cause this bug to appear. Those conditions are using a language structure related key word in a multiline string used in assignment or as a string literal in a conditionally executed block.
The fix is not simple nor quickly implemented but will be included in the next release.
Thanks. I have implemented a temporary work-around that will keep me from having to roll-back a software release.
Jim
The bug manifests itself when control statements appears as the
first word of a stand alone line in a multiline string literal. The bug does not appear when the control statement word is
not the
first word of a multiline line or when the control statement word is on the line with the
assignment operator. When these content conditions are met the bug appears when multiline appears in an 'if' conditional statement that is not executed because the statement's expression evaluates to false (0). The bug will always appear in a 'while', 'for' or 'foreach' loop control statement when the content requirements are met. It can also be demonstrated by meeting the multiline content requirements in a non-executed case of a 'switch' or 'select' statement.
Any of the following control statements used as the first word of a stand alone line of a multiline string literal will reproduce the problem:
- if
- then
- else
- elseif
- endif
- end
- while
- endwhile
- for
- foreach
- next
- endfor
- switch
- select
- case
- endswitch
- endselect
Good to know. Also seems to happen when the last word as well. Wasn't that the case in my example?
Jim
No, it is only the first word of a line. 'End' just happens to be the only word on the 3rd from the last line. It doesn't matter which line of the multiline contains the word as long as it isn't the line of the multline with the assignment operator.
Okay...misunderstood. Thanks again.
Jim
And thanks for providing a succinct, easily reproducible example of the bug.
This is similar (same?) as the above but thought I would post it, in the event it is helpful in covering all the bases in the fix, as the problem doesn't occur if there is only one set of code but if I duplicate it with minor changes it generates an error. That is, if you remove the second IF block it works fine. The CASE word is the issue. Spent a bunch of time again on this issue since I didn't think about this being a problem since it worked fine with the one set :-(
Jim
mltype = "I"
com_mylist_received = "HELLO"
com_mylist_iemail = "HELLO"
If mltype == "I" Then
SQLText = $" Select
'{"rec_num" :: "', rec_num, '",',
'"isbn" :: "', isbn, '",',
'"title" :: "', '<b>',title,'</b>",',
'"author" :: "', Replace(author,'|',' / '), '",',
'"actual_price" :: "', FORMAT(actual_price,2), '",',
'"list_price" :: "', FORMAT(list_price,2), '",',
'"pn_date" :: "', SubString(Cast(pn_date as char),1,10), '",',
'"pn_status_date" :: "', SubString(Cast(pn_status_date as char),1,10), '",',
'"pn_status" :: "',
Case pn_status When 0 Then ''
When 1 Then Replace(Replace(pn_status,'1',Concat('','$":StrReplace(com_mylist_received,'"','\\"'):$"')),'{DATE}',SubString(pn_status_date,1,10))
When 2 Then Replace(Replace(pn_status,'2',Concat('','$":StrReplace(com_mylist_iemail,'"','\\"'):$"')),'{DATE}',SubString(pn_status_date,1,10))
Else '' END, '",',
'"pn_type" :: "', pn_type, '"},'
from notify where ulogin = '%ulogin%';
$"
EndIf
If mltype == "A" Then
SQLText = " Select rec_num, pn_status_date, pn_status, pn_type, isbn, title, author, actual_price, list_price, pn_date from notify where ulogin = '%ulogin%' and pn_type = 'A' order by author"
SQLText21 = $" Select
'{"rec_num" :: "', rec_num, '",',
'"isbn" :: "', isbn, '",',
'"title" :: "', '<b>',title,'</b>",',
'"author" :: "', Replace(author,'|',' / '), '",',
'"actual_price" :: "', FORMAT(actual_price,2), '",',
'"list_price" :: "', FORMAT(list_price,2), '",',
'"pn_date" :: "', SubString(Cast(pn_date as char),1,10), '",',
'"pn_status_date" :: "', SubString(Cast(pn_status_date as char),1,10), '",',
'"pn_status" :: "',
Case pn_status When 0 Then ''
When 1 Then Replace(Replace(pn_status,'1',Concat('','$":StrReplace(com_mylist_received,'"','\\"'):$"')),'{DATE}',SubString(pn_status_date,1,10))
When 2 Then Replace(Replace(pn_status,'2',Concat('','$":StrReplace(com_mylist_iemail,'"','\\"'):$"')),'{DATE}',SubString(pn_status_date,1,10))
Else '' END, '",',
'"pn_type" :: "', pn_type, '"},'
from notify where ulogin = '%ulogin%' ;
$"
EndIf
message("HI",SQLText)
Exact same problem. Don't use a control statement as the first word of a line in a multline string literal.
I understand. Was just curious why it worked when I only had one occurrence of it but started failing when I added the second.
Jim
Just don't do it.
Consider.
#DefineSubroutine Foo()
return $"
case
endif
if
end
next
for
endselect
select
endswitch
switch
endfor
foreach
$"
#EndSubroutine
if 0
strBar = Foo()
endif
Pause('This Works', 'And as a bonus improves performance when used in a tight loop.')
Thanks. Will try to keep this in mind. Guessing after a few hours of beating my head against the wall the next time I will eventually remember it :) Really appreciate the MultiLine feature...it has been a HUGE help in making such things more readable and maintainable. I've been doing a lot with JavaScript and AJAX calls recently so formatting SQL to return JSON is made A LOT easier without all the extra concatenation.
Guessing I am not supposed to ask why this works either?
Jim
Don't ask.
I also want to say, the multiline string thing is wonderful. Code so much more readable. I don't care if it has some wrinkles, it's beautiful to me!
$0.02,
Kirby
Hopefully, the feature will be as wrinkle free as freshly pressed dress pants in the next release.