Sending SQL statements directly to the data source

ABL 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 OpenEdge 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 VARIABLE handle1 AS INTEGER NO-UNDO.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
  ("SELECT name, address, city, state, postalcode
    FROM customer WHERE creditlimit >= 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 an ABL procedure. You can read the results into the proc–text–buffer, 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 VARIABLE handle1 AS INTEGER NO-UNDO.
DEFINE VARIABLE iMax    AS INTEGER NO-UNDO.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
  ("SELECT MAXIMUM(custnum) FROM customer").
FOR EACH proc-text-buffer:
   iMax = INTEGER(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 compiler does not process it, so errors occur only at run time and not when you compile a procedure.