RE: [sqr-users] Dynamic Substitution Vars

[Steve Cavill <steve.cavill@infoclarity.com.au>]

Matt,
How about setting up the command line  so when the job runs it simply reads
the parameters off the command line in pairs:
SQRW.EXE <connect> <flags> parm1name=parm1value parm2name=parm2value.... etc
use ONE input command in a loop with the batch-mode option to stop reading
when you run out of parms.
then use an evaluate to determine which parms have been supplied:

e.g.

SQRW.EXE <connect> <flags> begin-date='01-jan-03' code='blah blah' .... etc
...
 while (1)
  input $parm status=#stat batch-mode
  if #stat=3
   break
  else
   do get_parm ($parm)
  end-if
 end-while

begin-procedure get_parm($a)
 unstring $a by '=' into $parm $value
 !either print the parms now, or save in an array for printing later
evaluate $parm
 when 'begin-date'
  let $begin-date=$value
 when 'code'
  let $code=$value
 when....etc
end-evaluate

If you don't like the evaluate as hard to maintain you could also load the
parms into a table in pairs.  the table would have three (or more) columns
Report_code, input_parm_name, input_value

then your program can do a select against the table to get all the required
parms when required:
e.g.
begin-select
begin-date
from table
where   input_parm_name = 'begin-date' and Report_code='xxx'
end-select

The advantage of saving the parms in a table is you can keep the parms for
as long as you like if you use one table and make the Report_code unique.
So you can retrieve the parms at any time for history or problem diagnosis.

You can either load the parms into the table within the SQR program or have
a separate scheduler that loads the parms then calls the SQR.  This is how
the batch scheduler works in many applications, including but not limited to
Peoplesoft.

Cheers, Steve.

-----Original Message-----
From: sqr-users-admin@sqrug.org [mailto:sqr-users-admin@sqrug.org]On
Behalf Of Ray Ontko
Sent: Saturday, 18 October 2003 8:32 AM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] Dynamic Substitution Vars


Matt,

I don't know if this will work for you, but in situations where
I've needed dynamic SQR code, I have writen an SQR program that
generates the SQR program (using WRITE or PRINT).  You can even
use the generator program to launch the generated program using
CALL SYSTEM.

Ray

On Fri, Oct 17, 2003 at 04:26:20PM -0400, Matt Rogish wrote:
> All,
>
> I sent a message earlier about dynamic 'input' variables and got some
> interesting suggestions on and off list, none of which worked out quite as
> I had hoped.
>
> To re-cap:
> We have lots of programs which have this basic structure:
> set up variables
> ask for input
> run some SQL
> show report
> show summary
>
> In order to ease maintenance what I'd like to do is standardize the "set
up
> variables", "ask for input", and "show summary" pieces using some sort of
> dynamic SQR.
>
> In the summary section we list some report variables (counters and things)
> and we like to list the input parameters because there's nothing like
> getting a complex multi-section report and trying to guess what input
> parameters the particular report was run with (because the user forgets,
or
> you look at the report a month later, or they were in Brio Portal but have
> since been changed, etc.).
>
> The way we have it now is something like this:
> begin-procedure get-input
>   Input $code 'Enter some code'
>   Input $begin_date 'Enter report begin date' type=date
>   ..
> end-procedure
>
> begin-procedure show-input-summary
>   print 'Input Parameters:' (+1) bold
>
>   print 'Some Code: ' (+2)
>   print $code
>
>   print 'Begin Date: ' (+2)
>   print $begin_date
>
>   etc.
> end-procedure
>
> Oftentimes there are many parameters so it becomes cumbersome to list long
> blocks of Input and print statements.  It also makes maintenance a
> nightmare because you don't know which programs require what input and if
> you want to change/add one you have to change it several places.
>
> So, the goal is to minimize code changes when the input parameter list
> changes -- either by adding, changing, or removing input
> parameters.  Preferably, there would be one spot in the program where I
> change a parameter list and the get-input and show-input-summary
procedures
> do not have to change!  It goes without saying that there might have to be
> other changes to handle the addition or removal of a parameter, but that
> can come later. :)
>
> Some ideas I've considered are:
> Idea One -- Build an array which stores the input parameter name
> ("some_code") and input query string ("Enter some code") then loop through
> each one and build an 'Input' statement from this.
> Problem:
> I don't know how to execute dynamic SQR code, namely something like:
> let $stmt = 'Input $' || $input_var_name || ' ''' || $input_query || ''''
> [$stmt]
> This gives an error:
> (SQR 4008) Unknown function or variable in expression: [$stmt]
>
> Idea Two -- Create substitution variables, loop through each of them, and
> use them in Input statements.
> Problem:
> I can't dynamically *name* a substitution variable:
> Let #num = 1
> #DEFINE variable_number_#1
> Show {variable_number_1}
> This gives an error:
> (SQR 4707) No value found for substitution variable: {variable_number_1}
>
> Any help/ideas?
>
>
> Thanks,
>
> --
> Matt Rogish - rogishmn@muohio.edu
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
----------------------------------------------------------------------
Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users