From: arne@vajhoej.dk   
      
   On 7/9/2025 3:22 AM, Lawrence D'Oliveiro wrote:   
   > On Tue, 8 Jul 2025 20:31:37 -0400, Arne Vajhøj wrote:   
   >> On 7/8/2025 7:37 PM, Lawrence D'Oliveiro wrote:   
   >>> On Tue, 8 Jul 2025 18:20:50 -0400, Arne Vajhøj wrote:   
   >>>> Standards evolve. They add lots of new stuff. And sometimes they   
   >>>> remove stuff that is not needed anymore.   
   >>>   
   >>> But those standards in particular have not evolved.   
   >>   
   >> SQL standard has evolved. Lot of stuff has been added.   
   >   
   > But nothing new in EXEC SQL. For example, did they offer any   
   > equivalent to the following utility functions?   
   >   
   >      def sql_string_list(the_list) :   
   >          "returns a list containing the quoted items of the_list, suitable" \   
   >          " for use in an “in” clause."   
   >          return \   
   >              "(" + ", ".join([sql_string(s) for s in the_list]) + ")"   
   >      #end sql_string_list   
   >   
   >      def escape_sql_wild(s, escch) :   
   >          "escapes SQL pattern wildcards in s with escch. The same escch   
   needs" \   
   >          " to be passed to the ESCAPE clause for the LIKE operator."   
   >          if not isinstance(s, str) :   
   >              raise TypeError("expecting s to be a string")   
   >          #end if   
   >          if not isinstance(escch, str) or len(escch) != 1 :   
   >              raise TypeError("expecting escch to be a single-character   
   string")   
   >          #end if   
   >          result = []   
   >          for ch in s :   
   >              if ch == escch or ch == "%" or ch == "_" :   
   >                  result.append(escch)   
   >              #end if   
   >              result.append(ch)   
   >          #end for   
   >          return "".join(result)   
   >      #end escape_sql_wild   
   >   
   >      def escape_sql_name(n, escch = "\"") :   
   >          "converts n to escaped form to avoid potential conflicts with SQL   
   keywords" \   
   >          " and other syntax errors (e.g. from embedded spaces). escch should   
   be “\"”" \   
   >          " as per the SQL standard."   
   >          assert len(escch) == 1   
   >          out = []   
   >          for i, split1 in enumerate(n.split(escch)) :   
   >              if i != 0 :   
   >                  out.append(escch * 2)   
   >              #end if   
   >              out.append(split1)   
   >          #end for   
   >          return escch + "".join(out) + escch   
   >      #end escape_sql_name   
      
   IN is tricky due to the variable number of data.   
      
   But dynamic SQL with dynamic data is still bad.   
      
   I would write your code a little simpler:   
      
   $ type dyn.py   
   import sqlite3   
      
   def esc(s):   
        return "''".join(s.split("'"))   
      
   def q(s):   
        return f"'{s}'"   
      
   def dump_range(con, vals):   
        jvals = ",".join(q(esc(val)) for val in vals)   
        c = con.cursor()   
        c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jvals})")   
        for row in c.fetchall():   
            print('%d %s' % (row[0], row[1]))   
      
   with sqlite3.connect('test.db') as con:   
        dump_range(con, ['A', 'CCC', 'EEEEE'])   
        dump_range(con, ['BB', 'DDDD'])   
   $ python dyn.py   
   1 A   
   3 CCC   
   5 EEEEE   
   2 BB   
   4 DDDD   
      
   But it is both simpler and safer to use dynamic SQL   
   with just dynamic non-data:   
      
   $ type sta.py   
   import sqlite3   
      
   def dump_range(con, vals):   
        jparm = ",".join('?' for val in vals)   
        c = con.cursor()   
        c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jparm})", vals)   
        for row in c.fetchall():   
            print('%d %s' % (row[0], row[1]))   
      
   with sqlite3.connect('test.db') as con:   
        dump_range(con, ['A', 'CCC', 'EEEEE'])   
        dump_range(con, ['BB', 'DDDD'])   
   $ python sta.py   
   1 A   
   3 CCC   
   5 EEEEE   
   2 BB   
   4 DDDD   
      
   It is even more tricky in embedded SQL, but of course   
   it is possible.   
      
   You can do the same solution with dynamic SQL with just   
   dynamic non-data by using EXEC SQL with PREPARE and EXECUTE   
   (that requires you to use SQLDA).   
      
   Possible but requires some code.   
      
   Easier to use a LOCAL TEMPORARY TABLE and stay static.   
      
   Cobol Rdb example:   
      
   $ type emb.sco   
   identification division.   
   program-id. emb.   
   *   
   data division.   
   working-storage section.   
   EXEC SQL BEGIN DECLARE SECTION END-EXEC   
   EXEC SQL INCLUDE SQLCA END-EXEC.   
   01 con pic x(255).   
   01 f1 pic 9(9) display.   
   01 f2 pic x(50).   
   01 inclause.   
   03 nvals pic 9(9).   
   03 vals pic x(50) occurs 100 times.   
   01 i pic 9(9) comp.   
   EXEC SQL END DECLARE SECTION END-EXEC   
   EXEC SQL DECLARE curs CURSOR FOR SELECT f1,f2 FROM t1 WHERE f2 IN   
   (SELECT f2 FROM f2list) END-EXEC.   
      
   procedure division.   
   main-paragraph.   
        move "FILENAME disk4:[rdb]test" to con   
        EXEC SQL CONNECT TO :con END-EXEC   
        move 2 to nvals   
        move "BB" to vals(1)   
        move "CCC" to vals(2)   
        perform dump-range-paragraph   
        move 2 to nvals   
        move "A" to vals(1)   
        move "BB" to vals(2)   
        perform dump-range-paragraph   
        stop run.   
   dump-range-paragraph.   
        perform varying i from 1 by 1 until i > nvals   
            move vals(i) to f2   
            EXEC SQL INSERT INTO f2list VALUES(:f2) END-EXEC   
        end-perform   
        EXEC SQL OPEN curs END-EXEC   
        move 0 to SQLCODE   
        perform until not SQLCODE = 0   
            EXEC SQL FETCH curs INTO :f1, :f2 END-EXEC   
            if SQLCODE = 0   
                display f1 " " f2   
            end-if   
        end-perform   
        EXEC SQL CLOSE curs END-EXEC   
        EXEC SQL DELETE FROM f2list END-EXEC.   
   $ sqlpre /cob /sqloptions=connect emb   
   $ link emb + sys$library:sql$user73/libr   
   $ run emb   
   000000002 BB   
   000000003 CCC   
   000000001 A   
   000000002 BB   
      
   It could probably be done better by a real Cobol   
   programmer, but ...   
      
   Arne   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)