Call statement issue

[cbushey1]

Hey there,

I am looking for some help with my include/call statements. I have found what I think is a bug but if anyone knows a way around this or perhaps can find a flaw in my method I would appreciate the assistance!

I am trying to do a loop to load all my script files into the application. Each script file is a SUB routine. The code below will work if I comment out the loop for the Include statements and uncomment the single include statement above the CALL statement so that makes me think there is some issue with loaded SUB routines using a loop.

Here is my code:

SET vRoot = '..\..\..\..\SQL Script Library';

/* Builds a list of Only Custom scripts in the folder*/

FOR Each File in filelist ('$(vRoot)'&'\*.qvs')

ScriptList:

Load

SubField('$(File)','\',-1) AS ScriptFullName

, SubField(SubField(SubField('$(File)','\',-1),'_',1),'.',1) AS GenericScriptName

AUTOGENERATE 1

Where SubField('$(File)','\',-1) like '*_BD*';

next File

/* Appends the remaining scripts skipping any generic scripts where custom scripts should be run instead */

FOR Each File in filelist ('$(vRoot)'&'\*.qvs')

Concatenate (ScriptList)

Load

SubField('$(File)','\',-1) AS ScriptFullName

, SubField(SubField(SubField('$(File)','\',-1),'_',1),'.',1) AS GenericScriptName

AUTOGENERATE 1

Where NOT EXISTS(GenericScriptName,SubField(SubField(SubField('$(File)','\',-1),'_',1),'.',1));

next File

/* Uses the list of script names created above and loads only those scripts into the application to be called upon. */

For i = 0 to NoOfRows('ScriptList') -1

Let vFileName = Peek('ScriptFullName',$(i),'ScriptList');

$(Include='..\..\..\..\SQL Script Library\'$(vFileName)');

TRACE $(vFileName) Loaded;

Next i

///* Clean up variable list */

//SET vRoot = '';

//SET File = '';

//SET i = '';

//SET vFileName = '';

//DROP TABLE ScriptList;

//$(Include='..\..\..\..\SQL Script Library\Eligibility_BD.qvs);

CALL Elig (vIncludeElig,vIncludeEmployer);

[Peter_Cammaert]

Small piece of experience that's worth a ton...

[cbushey1]

Thank you both Peter and Marcus for offering your thoughts on this thread. It is much appreciated.

I have entered into a support case to discuss the functionality of the loop and why the sub routines wouldn't be available after the loop has run.

In the interim, I have worked on improving my loop to have both the include and the call statements and also improved my sub routines to not include the variables as parameters since they were defined previously as global variables via a script file. Given that my scripts need to run in a specific order, I will have be including a way to rank and sort the scripts according to a predefined ranking.

I will close the post as it appears the issue has been identified, issue with looping include statements. !

[Peter_Cammaert]

It's getting even worse

Apparently, an IF statement also creates a new lexical level (of a type that is commonly called "Block"). If you define a SUB in an IF block, the SUB will only be visible inside the IF and all nested blocks.

After this reversal of IF-fortune, it took me a while to find a method to test whether a SUB definition survives into the next FOR cycle. But then I got it: the magic of $-sign substitution to the rescue again !

The following proves that a SUB definition stays alive inside the FOR loop. Not that it has any use that I know of...

LET C = 0;

LET vSub = '

SUB ADD(A, B)

  LET C = A + B;

END SUB

';

FOR i = 1 TO 2

  $(vSub)

  LET vSub = 'CALL ADD(1, 2);';

NEXT

It may not be the outcome we would have liked, but at least I learned a thing or two

PS Older init systems in Unix/Linux-land had the same issue with differently named scripts needing an implicit order of execution indicator. If I'm not mistaken, the designers resorted to adding a numerical two digit prefix to all file names that was used to sort the list of script files. Maybe that could be of help?

[cbushey1]

Glad to see I wasn't the only one struggling with this issue and glad there are some work arounds to it.

My solution I am testing now but given I didnt want to go down the route of assigning all my include statements into a single variable and having multiple call statements as I eventually wanted to loop the call statements, I have included both the include and call in the same loop.

I will certainly keep this thread in mind for the future and when I am done testing my code I will post so you can see the solution I went with.