C9 – SQL improved with PROP:SQLRowSet

PROP:SQL parses the passed SQL and only sets things up for a return result if the SQL is a CALL or SELECT statement.  It cannot always assume a result set as some backends (MSSQL in particular) do not work if you call UPDATE using calls that normally return a result set.  PROP:SQLRowSet offloads the work of which type of calls to use to the developer.  This allows calls that are not CALL or SELECT that return result sets (eg PRAGMA statement in SQLite), to work.

So we could write code like this:

SQLiteFile{PROP:SQLRowSet}=’PRAGMA table_list’ ! get the list of tables in the database
LOOP
NEXT(SQLiteFile)

END

or if using MSSQL

MSSQLfile{PROP:SQLRowSet} = ‘WITH q AS (SELECT COUNT(*) FROM f) SELECT * FROM q’
LOOP
NEXT(MSSQLfile)

END