Calling Sybase Stored Procedures

Wherever procedures are allowed in HCL Enterprise Integrator (HEI), a Sybase stored procedure may be used such as the destination of a Direct Transfer or within an LSX script. The stored procedure being called must have its parameters set up correctly for the call.

Input values are provided to Sybase procedures as named parameters. This requires that the parameters in Sybase use the same names as the HEI or Domino® Enterprise Connection Services (DECS) fields being provided as input values. The inputs being provided include key values when being used in the context of a keyed operation (selection, update, or delete context), and data values when relevant (insert or delete context). The input value data types are selected as the closest match to the data type in HEI or DECS. They will be converted by Sybase to the procedure parameter defined data types, provided the conversion is supported by Sybase.

Any output from a Sybase stored procedure must be returned through a final SELECT statement in the procedure. The result set of this selection will become the result set produced by the stored procedure.

The following is an example of a Sybase stored procedure. This is the format that would be required for the Open event of an Advanced RealTime Virtual Fields activity assuming that the key field is called NUMBER1 and the data fields are called NUMBER2, TEXT1, and TEXT2. In this context, the key field is the input parameter and the result set is expected to include the data fields followed by the key field.

create procedure OpenProc @NUMBER1 int
as select NUMBER2, TEXT1, TEXT2, NUMBER1 from tablename
where NUMBER1 = @NUMBER1