Skip to Content
0
Former Member
Jul 17, 2009 at 04:44 PM

Data integrator Sybase stored procedure calls

43 Views

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',

@action varchar(30),

@schedule int,

@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?