topic Reading cursor returning from Stored Procedure in Talend Studio

Reading cursor returning from Stored Procedure

Anonymous — Mon, 20 Apr 2015 18:35:05 GMT

Hello,
I'm using Talend Open Studio 5.6.1 and MS SQL server (2000). It is possible to retrieve and read a cursor returned from a Stored Procedure call?
I've tried using tMSSqlSP without success when calling the sample stored procedure below. I'm trying to get it working first with a simple SP such as the one below. Can anyone give me some tips or point to an example of using tMSSql components (or tMSSqlSP) to call an SP and then read a cursor from it?
Thanks very much for any help.
Problems

How to pass in a reference to an "OUTPUT" cursor into the SP?
How to read the cursor that is returned?


CREATE PROCEDURE . 
    @myCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @myCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT my_id, my_value
      FROM dbo.zExample;
    OPEN @myCursor;
GO

Re: Reading cursor returning from Stored Procedure

Anonymous — Tue, 21 Apr 2015 08:43:25 GMT

Hi,
Do you want to pass a cursor to a Mysql Procesure? Could you please give us more details about your job requirment?
Best regards
Sabrina

Re: Reading cursor returning from Stored Procedure

Anonymous — Tue, 21 Apr 2015 18:35:31 GMT

I have a stored procedure in Microsoft SQL Server. The stored procedure returns a cursor, with two fields. Assessment_id, and battery_id. The goal is to call the stored procedure, get the cursor, and for each row in the cursor, split it into the fields it contains.
Here is the final output, stored procedure, TSQL to call it, and screenshots from Talend Studio
In Talend, I used the tMSSqlSP and tParseRecordSet components.
Output from tLowRow

999|123456
My hovercraft is full of eels|42

Stored procedure

CREATE PROCEDURE .    
AS
    SET NOCOUNT ON;
	DECLARE @batteryAssessmentMappingCursor CURSOR --FORWARD_ONLY READ_ONLY FOR
      SELECT assessment_id, battery_id
      FROM dbo.TPDB_AssessmentBatteryMapping;
GO

How to get results out in Microsoft SQL Server Studio

DECLARE	@return_value int
EXEC	@return_value = .
SELECT	'Return Value' = @return_value
GO