[Info-vax] Any stronger versions of the LMF planned ?, was: Re: LMF Licence Generator Code

[Arne Vajhøj]

On 8/12/2021 10:04 PM, Lawrence D’Oliveiro wrote:
> On Friday, August 13, 2021 at 1:18:36 PM UTC+12, Arne Vajhøj wrote:
>> On 8/12/2021 7:52 PM, Lawrence D’Oliveiro wrote:
>>> filter_clause = " and ".join \ ( "%s = %s" % (field_name,
>>> format_sql_value(params[field_name])) for field_name in
>>> field_names if params[field_name] != "" )
>> 
>> There are two well known solutions.
>> 
>> Single static SQL using the coalesce trick and sending over NULL
>> for not used parameters.
>> 
>> Building dynamic SQL with placeholders and just sending over the
>> parameters used.
> 
> I like the way you don’t offer any actual code, just some vague
> hand-waving arguments. Go on, then: let’s see how you address an
> actual business need by actually thinking about the problem, rather
> than just mindlessly regurgitating something you read on Stack
> Overflow.

Actually I provided an precise solution just described in English.

Building dynamic SQL with placeholders and just sending over the
parameters used means to build an SQL string with:

... WHERE field2 = ? AND field5 = ? ...

and send over the value2, value5, ... as parameters.

In Python something like:

     sqlstr = 'SELECT name1,name2 FROM pairs'
     param = []
     if any(activeconditions):
         sqlstr = sqlstr + ' WHERE ' + ' AND '.join(map(lambda item: 
item[0] + '=?', activeconditions))
         param = list(map(lambda item: item[1], activeconditions))
     c.execute(sqlstr, param)

Single static SQL using the coalesce trick and sending over NULL
for not used parameters means to use a fixed SQL string like:

... WHERE field1 = COLALESCE(?,field2) AND field2 = COLALESCE(?,field2) ...

and send over values for all fields but NULL for those fields not to be 
used.

In Python something like:

     sqlstr = 'SELECT name1,name2 FROM pairs WHERE name1 = 
COALESCE(?,name1) AND name2 = COALESCE(?,name2)'
     allfields = ['name1', 'name2']
     param = []
     for f in allfields:
         param.append(conditions[f])
     c.execute(sqlstr, param)

In VMS COBOL something like:

01 NAME1 PIC X(32).
01 NAME2 PIC X(32).
01 NAME1PRES PIC S9.
01 FINDNAME1 PIC X(32).
01 NAME2PRES PIC S9.
01 FINDNAME2 PIC X(32).
EXEC SQL DECLARE curs CURSOR FOR SELECT name1,name2 FROM pairs WHERE 
name1 = COALESCE(:findname1:name1pres,name1) AND name2 = 
COALESCE(:findname2:name2pres,name2) END-EXEC.
...
     EXEC SQL OPEN curs END-EXEC
     ...
     EXEC SQL CLOSE curs END-EXEC.

All pretty standard.

Arne