procedure only returning 1 row

[tc3596]

CREATE PROCEDURE usp_SubAssembly()

returns(Item_No char(15), Qty decimal(13, 6));

begin


DECLARE BTUCursor CURSOR FOR
SELECT Item_No, Qty FROM MSFRCFIL_SQL
WHERE ITEM_NO Like 'M%' AND LOC = 'FG'
order by ITEM_NO asc,LOC asc;

Declare :Item char(15);
Declare :Qty decimal(13, 6);

OPEN BTUCursor;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

select :Item, :Qty;

CLOSE BTUCursor;

end


...any thoughts? I'm new to pervasive but proficient with SQL Server

 

[mirtheil]

You need to loop through the cursor to get all of the records. The PSQL documentation is at

http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-22.html.

I'm assuming that there's more to the procedure because the example you gave doesn't even need a stored procedure. WIth PSQL, there's no performance advantage to a stored procedure.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[tc3596]

Yes, there is more. I have a loop inside this cursor. Boy, this is a lot easier in sql server. I need, to loop thru each item number and qty and do another loop for each one of these.

 

[tc3596]

i feel stupid...

this is not working...I really need assistance.

I put this in..

OPEN BTUCursor;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

loop

select :Item, :Qty;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

End loop;
end;

...this query won't stop (infinite loop)

...if i take out the loop... end loop parts, i get 1 record.

 

[tc3596]

i just tried this...

CREATE PROCEDURE usp_SubAssembly()

returns(Item_No char(15), Qty decimal(13, 6)) WITH DEFAULT HANDLER ;

begin


DECLARE BTUCursor CURSOR FOR
SELECT Item_No, Qty FROM MSFRCFIL_SQL
WHERE ITEM_NO Like 'M%' AND LOC = 'FG'
order by ITEM_NO asc,LOC asc FOR READ ONLY ;

Declare :Item char(15);
Declare :Qty decimal(13, 6);



OPEN BTUCursor;


FETCH NEXT FROM BTUCursor INTO :Item, :Qty;
testloop:
LOOP

IF (SQLSTATE = '02000') THEN
LEAVE testloop;
end if;

select :Item, :Qty;

FETCH NEXT FROM BTUCursor INTO :Item, :Qty;

End loop;
CLOSE BTUCursor;
end;


...ODBC SQLSTATE S1000 Native Error Code = -1
...i am running this thru sql data manager

 

[mirtheil]

Perhaps we're going about this wrong. What are you trying to accomplish with this Stored Procedure? What's the end result? All of the example I see related to cursors are in conjunction with DELETE and UPDATE and INSERT statements.
The reason I say this is that the SP above can be rewritten as:

CREATE PROCEDURE usp_SubAssembly()
returns(Item_No char(15), Qty decimal(13, 6)) WITH DEFAULT HANDLER ;
begin

 SELECT  Item_No, Qty  FROM MSFRCFIL_SQL
    WHERE ITEM_NO Like 'M%' AND LOC  =  'FG'      
    order by ITEM_NO asc,LOC asc;
end;

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[tc3596]

mirtheil,
You responded to my other post...see thread318-1273004

I really appreciate you assisting me with this. The end result is a display from crystal reports. No other program is in the mix. Basically, have the stored procedure as a source of data for the report. I wanted to write the whole thing in crystal, but that would be really slow and confusing.