PROP:SQLRowSet

Top  Previous  Next

 

You can use Clarion's property syntax (PROP:SQLRowSet) to execute SQL statements that return result sets in your program code by using PROP:SQLRowSet and naming the FILE or imported SQL VIEW in the data dictionary as the target within the normal execution of your program. This is only valid when using an SQL file driver (such as the ODBC, Scalable SQL, or Oracle drivers). You can send any SQL statements that return results supported by the SQL server.

After issuing the SQL statement, you use NEXT(file) to retrieve the result set one row at a time, into the file's record buffer. The FILE declaration receiving the result set must have at least as many fields as the SQL SELECT statement returns. If the Clarion ERRORCODE procedure returns 90, the FILEERRORCODE() and FILEERROR() functions return any error code and error message set by the back-end SQL server.

 

Example:

SQLiteFile{PROP:SQLRowSet}='PRAGMA table_list' ! get the list of tables in file

LOOP

 NEXT(SQLiteFile)

 …

END

f{PROP:SQLRowSet} = 'WITH q AS (SELECT COUNT(*) FROM f) SELECT * FROM q'

 NEXT(f)

 

The names used in the SQL statement are the SQL table names, not the Clarion field names.

 

See Also:Using Embedded SQL, PROP:SQL