asked on

Is there a Pervasive 9.6 equivalent of the %ROWTYPE in Oracle?

I can find very little on working with cursors in Pervasive. It seems as though if I want to read a record into a variable, it has to be 1 variable for every field. Is there some way of reading an entire row?

Also any small code snippits of working cursors in Pervasive would be muchly appreciated. The help file is very limited in this area.

ASKER CERTIFIED SOLUTION

Mirtheil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GeekHipster

ASKER

This demonstrates 5 columns, when theres say 100, it just looks silly. It certainly works, but it seems to me there must be a tidier way of doing this and I'm just learning it in Pervasive, so hey, what the heck, I'll ask the experts. ;)

Thanks.

CREATE PROCEDURE updateSomething
	(IN :strField CHAR(20))
	RETURNS (VAL BIT)
AS 
BEGIN
	DECLARE :FIELD1 CHAR(4);
	DECLARE :FIELD2 CHAR(20);
	DECLARE :FIELD3 CHAR(1);
	DECLARE :FIELD4 CHAR(20);
	DECLARE :FIELD5 CHAR(60);
 
	DECLARE curONE CURSOR FOR 
		SELECT * FROM MyFirstTable WHERE FIELD1 = :strField;
 
	OPEN curONE;
	FETCH NEXT FROM curONE INTO
		:FIELD1,
		:FIELD2,
		:FIELD3,
		:FIELD4,
		:FIELD5;
	CLOSE curONE; --Gets 1 row, stores the values into the FIELDx variables
 
	DECLARE curTWO CURSOR FOR 
		SELECT * FROM MySecondTable
		WHERE FIELD3 in ('1234','5678') FOR UPDATE;
 
	OPEN curTWO;
	MyLoop:
		LOOP FETCH NEXT FROM curTWO;
			IF SQLSTATE = '02000' THEN LEAVE MyLoop; END IF;
			UPDATE SET 
				FIELD1 = :FIELD1,
				FIELD2 = :FIELD2,
				FIELD3 = :FIELD3,
				FIELD4 = :FIELD4,
				FIELD5 = :FIELD5
				WHERE CURRENT OF curTWO;
		END LOOP;
	CLOSE curTWO; --Gets several records, updates them with the values from the FIELDx variables
	
	SELECT 1 AS VAL;  --If we get this far, lets say it worked.
END;

Open in new window

GeekHipster

ASKER

Don't ask me what kind of table has hundreds of columns. Wasn't my idea! :)

SOLUTION

Bill Bach

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GeekHipster

ASKER

I just have to update rows in table B, with information from table A. I could do this in the application layer easily enough I know. I'd just like to reference an entire row somehow instead of having to store every field individually. I don't think I'm going to find what I'm looking for. Thanks for the help, it works like this in any case.