| **Navigation:**  SQL Drivers > [[optimizing set processing using the where driver string.htm|Optimizing using the WHERE driver string]] >====== Using Embedded SQL ====== | [[performance considerations.htm|{{btn_prev_n.gif|Previous page}}]][[optimizing set processing using the where driver string.htm|{{btn_home_n.gif|Return to chapter overview}}]][[runtime sql properties for views using sql drivers.htm|{{btn_next_n.gif|Next page}}]] |
|  ||

**See Also:** [[using embedded sql.htm#calling a stored procedure|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.

{{notebox.jpg|NoteBox.jpg}}

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//**//]'])]//**

{{black.jpg|black.jpg}}

| //call_type// | CALL or NORESULTCALL |
| //parameter// | //constant// or //bound_field////constant //must conform to the syntax of your backend. Normally numerics and strings are the same as Clarion. For ODBC systems, date constants are in the format **{d 'yyyy-mm-dd'}**, time constants are **{t 'hh:mm:ss'}** and time stamp constants are **{ts 'yyy-mm-dd hh:mm:ss'}**.//bound_field// is either an //output_bound_field// or //output_bound_field// '['//bind_type//']' |
| //output_bound_field// | &//variable //(This must be a variable that you have previously bound using the BIND function) |
| //bind_type// | IN, OUT, INOUT, BINARY (valid on all SQL drivers, except Oracle) [BINARY] on a parameter indicates that this should be bound as a binary field. See the example code below to see how this can be used. |

{{black.jpg|black.jpg}}

__**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.

{{notebox.jpg|NoteBox.jpg}}

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**

{{notebox.jpg|NoteBox.jpg}}

**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.**

{{black.jpg|black.jpg}}

**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**

{{notebox.jpg|NoteBox.jpg}}

**The above example shows how to return an output parameter.**

{{black.jpg|black.jpg}}

**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])'**

{{notebox.jpg|NoteBox.jpg}}

**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.htm|MSSQL Accelerator]] topic.