PROP:SQL parses the passed SQL and only sets things up for a return result if the SQL is a CALL or SELECT statement.\u00a0 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.\u00a0 PROP:SQLRowSet offloads the work of which type of calls to use to the developer.\u00a0 This allows calls that are not CALL or SELECT that return result sets (eg PRAGMA statement in SQLite), to work.<\/p>\n
So we could write code like this:<\/p>\n
SQLiteFile{PROP:SQLRowSet}=’PRAGMA table_list’ ! get the list of tables in the database
\nLOOP
\nNEXT(SQLiteFile)
\n…
\nEND<\/p>\n
or if using MSSQL<\/p>\n
MSSQLfile{PROP:SQLRowSet} = ‘WITH q AS (SELECT COUNT(*) FROM f) SELECT * FROM q’
\nLOOP
\nNEXT(MSSQLfile)
\n…
\nEND<\/p>\n","protected":false},"excerpt":{"rendered":"
PROP:SQL parses the passed SQL and only sets things up for a return result if the SQL is a CALL or SELECT statement.\u00a0 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.\u00a0 PROP:SQLRowSet offloads the work … Continue reading C9 – SQL improved with PROP:SQLRowSet<\/span>