Release 10.1A: OpenEdge Data Management:
DataServer for ODBC

> ## Sending SQL statements directly to the data source

> Progress uses stored-procedure syntax to allow you to send extended SQL statements directly to a data source. The DataServer uses the `RUN` `STORED–PROCEDURE` statement with the `send–sql–statement` option to pass SQL statements to the data source. Although Progress SQL allows you to use SQL statements, this option gives you access to your data source vendor’s SQL syntax and its extensions to SQL, such as Transact–SQL for Sybase. For example, you can issue SQL statements from within Progress procedures to modify the data definitions of your data source. You can send multiple SQL statements by concatenating them and passing the string as a single parameter to the `send–sql–statement` option.

> You use the `RUN` `STORED–PROC` statement with the `send–sql–statement` option and pass the SQL statements as a parameter. The syntax of the statement must be valid SQL syntax for the underlying database and must follow SQL naming and case conventions. For example, this code passes a `SELECT` statement as a parameter:

>     DEFINE VAR handle1 AS INTEGER.
>     RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
>     ("SELECT name, address, city, state, postal-code
>     FROM customer WHERE credit-limit >= 500").
>     FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
>     		DISPLAY proc-text-buffer.
>     END.
>     CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

> This example returns the `name`, `address`, `city`, `state`, and `postal` `code` for all `customers` whose `max–credit` is greater than or equal to $500. You must read the results into a buffer as you would with a stored procedure called by a Progress procedure. You can read the results into the `proc–text–buffer` defined by Progress, as in the example above, or you can define your own buffer from within your data source that accepts a data type other than the `CHARACTER` data type.

> The following example illustrates returning database results into the `proc–text–buffer` and converting the results to the `INTEGER` data type:

>     DEFINE VAR handle1 AS INTEGER.
>     RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
>     ("SELECT max (cust_num) FROM customer").
>     FOR EACH proc-text-buffer:
>     		imax = int (substring (proc-text, 1, 3)).
>     		DISPLAY imax.
>     END.
>     CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

> The DataServer passes the SQL statement directly to the ODBC data source. The Progress Compiler does not process it, so errors occur only at run time and not when you compile a procedure.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095