Clarion Community Help

You can use property syntax (PROP:SQL) to execute SQL statements in your program. You can send any SQL statements supported by the SQL backend.

For example, multi-record updates can 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 sometimes makes sense for you to use an SQL statements to the backend, and PROP:SQL lets you do this.

When you issue a PROP:SQL that returns a result set (such as an SQL SELECT or Stored Procedure), 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 the same number of fields as the SQL SELECT statement will return. If the Clarion ERRORCODE procedure returns 90, the FILEERRORCODE() and FILEERROR() functions return any error code and error message set by the server. In order to return a result set, you must begin your statement with either SELECT or CALL. If you need to return a result set and your statement does not begin with either SELECT or CALL, then use PROP:SQLRowSet.

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

Note that BLOB fields are not in the RECORD structure, and should not be listed in a PROP:SQL.  If you need to retrieve an image field from an SQL backend, use SET/NEXT and BLOB syntax to retrieve the blob data.

Example:

MySQLFile{PROP:SQL}='SELECT field1,field2 FROM table1' |
          & 'WHERE field1 > (SELECT max(field1)'    |
          & 'FROM table2'         
!Returns a result set that you process one row at a time using NEXT()
 
For example:
LOOP UNTIL ERROR()
   NEXT(MySQLFile)
   !do some work
END
 
MySQLFile{PROP:SQL} = 'CALL GetRowsBetween(2,8)'                  !Call stored procedure

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

MySQLFile{PROP:SQL} = 'GRANT SELECT ON mytable TO fred.mitchells' !Set some Database permissions

SQLString = MySQLFile{PROP:SQL}  !retrieve the last SQL statement sent by the driver

SEND

You can alternatively use the Clarion SEND procedure to send an SQL command to the backend database. This is provided for backward compatibility with earlier versions of Clarion. We recommend using the property syntax (PROP:SQL) to send SQL statements to the backend database.

Example:

SEND(SQLFile,'SELECT field1,field2 FROM table1'     |
      & 'WHERE field1 > (SELECT max(field1)'  |
      & 'FROM table2') ! Returns a result set you 
                       ! get one row at a time
                       ! using NEXT(SQLFile)
 
SEND(SQLFile,'CALL GetRowsBetween(2,8)')   !Call stored procedure
 
SEND(SQLFile,'CREATE INDEX ON table1(field1 DESC)') !No result set

When doing batch updates to a table, it can be much more efficient to execute an embedded SQL statement than to rely on the code generated by the Process template.

Example: use PROP:SQL to change the Salary column in every record by 50%:

 CODE
 Salaries{PROP:SQL} = 'UPDATE Salaries SET (SALARY=SALARY * 1.5) WHERE JOB=''Developer''' 

See Also: Using Embedded SQL.