?
Solved

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

Posted on 2009-02-21
5
Medium Priority
?
509 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:GeekHipster
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
mirtheil earned 400 total points
ID: 23700643
There is no %ROWTYPE in PSQL.  
What are you trying to do with the cursor?  PSQL cursors are typically only available in Stored Procedures.  Are you trying to do a Stored Procedure?  Typically SPs don't give any performance increase in PSQL.

Post up what you've got an we'll offer suggestions.
0
 
LVL 1

Author Comment

by:GeekHipster
ID: 23700734
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

0
 
LVL 1

Author Comment

by:GeekHipster
ID: 23700769
Don't ask me what kind of table has hundreds of columns.  Wasn't my idea!   :)
0
 
LVL 29

Assisted Solution

by:Bill Bach
Bill Bach earned 100 total points
ID: 23701859
You are doing it correctly.  Put more simply, it is very bad coding practice to use SELECT * in an SP, since if the table changes later on, the Sp breaks.  You should only read the fields that you need.

By the way, if you have a specific goal in mind, please post what you are trying to achieve.  The contrived example is highly unusual.
0
 
LVL 1

Author Comment

by:GeekHipster
ID: 23705462
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question