Clarion Community Help

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
| **Navigation:**  [[introduction.htm|Language Reference]] > [[chapter runtime properties.htm|App C - PROP: Runtime Properties]] > Complete Property List >PROP:SQL | [[prop snapheight.htm|{{btn_prev_n.gif|Previous page}}]][[chapter runtime properties.htm|{{btn_home_n.gif|Return to chapter overview}}]][[prop sqlrowset.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

PROP:SQL

You can use Clarion's property syntax (PROP:SQL) to execute SQL statements in your program code by using PROP:SQL 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 supported by the SQL server.

This capability lets your program do backend operations independent of the SQL Accelerator driver's generated SQL. For example, multi-record updates can often be accomplished more efficiently with a single SQL statement than with a template generated Process procedure that updates one record at a time. In cases like these it makes sense for you to take control and send custom SQL statements to the backend, and PROP:SQL lets you do this.

If you issue an SQL statement that returns a result set (such as an SQL SELECT 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 the same number of fields as the SQL SELECT statement returnswill return. 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. In order to return a valid result set, you must also begin your statement with either SELECT or CALL.

You may also query the contents of PROP:SQL to get the last SQL statement issued by the file driver.

Example:

SQLFile{PROP:SQL}='SELECT field1,field2 FROM table1' |

&amp; 'WHERE field1 > (SELECT max(field1)'    |

&amp; 'FROM table2'         !Returns a result set you

! get one row at a time

! using NEXT(SQLFile)

SQLFile{PROP:SQL}='CALL GetRowsBetween(2,8)'            !Call stored procedure

SQLFile{PROP:SQL}='CREATE INDEX ON table1(field1 DESC)' !No result set

SQLFile{PROP:SQL}='GRANT SELECT ON mytable TO fred'     !DBA tasks

SQLString=SQLFile{PROP:SQL}                             !Get last SQL statement