I'm using Data Integrator 11.7 and need to make a call to a Sybase ASE 12.5 stored procedure before starting an extract from taht database.
The Sybase procedure has the following header:
CREATE PROCEDURE pPublDWHProceedExtract (
@tablenamevc varchar(30) = 'all',
@offset int = 0,
@valid int output)
I need to retrieve the @valid return value before initiating the transfer.
When I import the procedure into DI, the @valid parameter appeard as an input parameter and I cannot get it's return value from a function call. If I try to use the SQL function, I get a Sybase error: This routine cannot be called until all fetchable results have been completely processed.
The sql function call looks like this:
sql('s4m_dwh_syb_ds', 'SET NOCOUNT ON' || chr(13) || 'declare @myvalid int' || chr(13) || 'exec pPublDWHProceedExtract @tablenamevc = \'all\', @action = \'begin\', @schedule = 0, @valid = @myvalid output' || chr(13) ||'select @myvalid');
How should I be calling this function?