Clarion Community Help

See Also: Stored Procedures

You can use Clarion's property syntax (PROP:SQL) to send SQL statements to the backend SQL server, within the normal execution of your program. For backward compatibility, you can also use the SEND function to send SQL statements; however, we recommend using the property syntax.

When you issue a SELECT statement using PROP:SQL, the selected fields must match the fields declared in the named file or view. In addition, if you use VIEW{Prop:SQL} to issue a SELECT statement, the fields in the SELECT must be ordered based on the field order in the file definition, not the PROJECT sequence.

PROP:SQL

You can use Clarion's property syntax (PROP:SQL) to send SQL statements to the backend SQL server, within the normal execution of your program. 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 which 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 FILEERRORCODE() and FILEERROR() functions return any error code and error message set by the back-end SQL server.

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

Examples:

SQLFile{PROP:SQL}='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)

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

SEND and PROP:SQL

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

Examples:

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

Using Embedded SQL for Batch Updates

SQL does a good job of handling batch processing procedures such as: printing reports, displaying a screen full of table rows, or updating a group of table rows.

The SQL Accelerator drivers take full advantage of this when browsing a table or printing. However, they do not always use it to its best advantage with the Process template or in code which loops through a table to update multiple records. Therefore, 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.

For example, to use PROP:SQL to increase all Salesman salaries by 10% you could:

SQLFile   FILE,DRIVER('Oracle'),NAME(SalaryFile)

Record     RECORD

SalaryAmount  PDECIMAL(5,2),NAME('JOB')

           END

          END

 CODE

 SqlFile{PROP:SQL} = 'UPDATE SalaryFile SET '&|

                     'SALARY=SALARY * 1.1 WHERE JOB=S'

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

PROP:SQLFilter

You can use PROP:SQLFilter to filter your VIEWs using native SQL code rather than Clarion code.

When you use PROP:SQLFilter, the SQL filter is passed directly to the server. As such it cannot contain the name of variables or functions that the server is not aware of; that is the filter expression must be valid SQL syntax with valid SQL column names. For example:

VIEW{PROP:SQLFilter} = 'Date = TO_DATE(01-MAY-1996,DD-MON-YYYY)'

or

VIEW{PROP:SQLFilter} = 'StrField LIKE AD%'

Combining VIEW Filters and SQL Filters

When you use PROP:SQLFilter, the SQL filter may replace any filter specified for the VIEW, or it may be in addition to a filter specified for the VIEW. Prefix the SQL filter with a plus sign (+) to append the SQL filter to the VIEW filter specified. For example:

VIEW{PROP:SQLFilter} = '+ StrField LIKE AD%'

When you append the SQL filter by using the plus sign, the logical end result of the filtering process is (View Filter) AND (SQL Filter).

Omit the plus sign (+) to replace the Clarion filter with the SQL filter. When you replace the Clarion filter with the SQL filter by omitting the plus sign, the logical end result of the filtering process is simply (SQL Filter).

Calling a Stored Procedure

To call a stored procedure the following SQL syntax is used to build the SQL calling statements.

[output_bound_field = ] call_type [([parameter[,parameter]'])]

CALL

To call a stored procedure you use property syntax to issue the SQL syntax 'CALL storedprocedure.'

Example:

MyFile{PROP:SQL} = 'CALL SelectRecordsProcedure (&MyVar[INOUT])'

NORESULTCALL

The SQL Accelerator drivers also allow the syntax 'NORESULTCALL storedprocedure' for stored procedures that do not return a result set.

Example:

MyFile{PROP:SQL} = 'NORESULTCALL SelectRecordsProcedure (&MyVar[INOUT])'

PROP:SQLRowSet

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.

Return Values (bind_type)

The Acclerator drivers support return codes, output parameters, and in/out parameters for stored procedures. These are defined using IN, OUT, and INOUT. IN declares a variable as input, OUT declares a variable as output, and INOUT declares a variable as both input and output. You can also have your stored procedures return a result set.

When you issue a {PROP:SQL} = '&return = call storedProcedure' against MSSQL, the field bound to return will not hold the result of the procedure call until all records have been retrieved via calls to NEXT(file)

The BINARY switch is used to signal the driver to pass the data in the bound field as binary data rather than character data. See the example below.

Example:

MyFile   FILE,DRIVER('ODBC')

Record    RECORD

ErrorCode  LONG

ErrorMsg   STRING(100)

         END

        END

CODE

OPEN(MyFile)

MyFile{PROP:SQL} = 'CALL ProcWithResultSet'

NEXT(MyFile)

IF ~ERRORCODE()

 IF MyFile.ErrorCode THEN STOP(MyFileErrorMsg).

END

The above example shows how to return a result set. The result set must match the fields declared in the named file or view. The storedprocedure ProcWithResultSet includes a final select statement that results with the set of requested data.

Example:

PROGRAM

MAP

CallProc(STRING)

END

MyFile  FILE,DRIVER('MSSQL')

Record   RECORD

c         LONG

        END

       END

Ret   LONG

Out   STRING(10)

 CODE

BIND('RetCode', Ret)

BIND('Out', Out)

CallProc('&RetCode = CALL StoredProcTest(1,&Out)')

MESSAGE(Return value of StoredProcTest =' & Ret)

MESSAGE(Output parameter of StoredProcTest =' & Out)

 

CallProc PROCEDURE(Str)

CODE

MyFile{PROP:SQL} = Str

The above example shows how to return an output parameter.

Example:

 PROGRAM

 MAP

 END

 PRAGMA('link(C%V%MSS%X%%L%.LIB)')

SQLFile FILE,DRIVER('MSSQL'),NAME('SYSFILES')

REC      RECORD

ID        LONG

NAME      CSTRING(100)

        END

       END

TS              STRING(8)

 CODE

   OPEN(SQLFile)

   SQLFile{PROP:SQL} = 'DROP PROCEDURE tstest'

   SQLFile{PROP:SQL} = 'CREATE PROCEDURE tstest @ts as timestamp AS '& |

                       ' return'

   BIND('TS',TS)

   TS='<;0><;0><;0><;0><;0><;0><;5H><;0DDH>'

  SQLFile{PROP:SQL}='NORESULTCALL TSTEST(&amp;TS[IN][BINARY])'

The above example shows how to use the IN and BINARY switches.

For a more specific example tailored to MSSQL, refer to the MSSQL Accelerator topic.