Re: VMS x86-64 database server

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