Technique to use proc-text-buffer

DataServer for Microsoft SQL Server

RDBMS Stored Procedure Details : Interfacing with RDBMS stored procedures : Retrieving result sets using proc-text-buffer : Technique to use proc-text-buffer

The following example shows the results of the first ABL stored procedure, pcust, which is first presented as an example in A stored procedure with multiple result sets, output parameters, and a single return value.

pcust stored procedure

/* pcust stored procedure */
DEFINE BUFFER cust-state-join-buffer FOR proc-text-buffer.

RUN STORED-PROC pcust (20, output 0, output 0).
FOR EACH cust-state-join-buffer:
DISPLAY cust-state-join-buffer.
END.

CLOSE STORED-PROC pcust.
DISPLAY pcust.orders pcust.states.

The OpenEdge-defined buffer, proc-text-buffer, has one character field named proc-text. The buffer accepts the returned database results, converts them to CHARACTER data type, and concatenates them into one string.

The next example illustrates returning database results into the proc-text-buffer and converting the results to the INTEGER data type.

Returning database results into the proc-text-buffer and results conversion

DEFINE VARIABLE handle1 AS INTEGER NO-UNDO.
DEFINE VARIABLE iMax    as INTEGER NO-UNDO.

RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
("SELECT max (cust_num) 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 MS SQL data source. The ABL does not process it, so errors occur only at run time and not when you compile a procedure.

Note: The ABL QUOTER function can be useful for quoting values or handling embedded quotes when building SQL statements that will be passed into stored procedures with the send-sql-statement option.