CODEWITHSUNDEEP
sqlstored-proceduresinformix
DoctorOsterreich
DoctorOsterreich

Reputation: 53

Holding a select row into a variable and accessing its fields with informix

Is there a way in Informix to create a ROW type named or unnamed that you can put a row resulted from a select into? I'm working in a stored procedure.

What I want is something like this:

DEFINE ROW rowVar;

SELECT * INTO rowVar FROM myTableName;

Haven't been able to find the correct syntax so far. I want the row object to behave sort-of like it would be SAMEAS with the table columns.

Upvotes: 1

Views: 1173

Answers (2)

John
John

Reputation: 23

I do this regularly with Informix.

define o_Row row(cs_nr int not null, addr_nr int, last_name varchar(255));

foreach
  select cs into o_Row from cs where cs_nr = 1234
end foreach;

if you are returning more than one row or are not in a foreach you can use a multiset.

define o_Row multiset(row(cs_nr int not null, addr_nr int, last_name varchar(255))not null);

Upvotes: 1

Luís Marques
Luís Marques

Reputation: 1451

It is not possible to use an "undefined" ROWtype in Informix Stored Procedure Language (SPL). If you try the following (using Informix 12.10.FC8DE):

CREATE PROCEDURE sp_dummy();
DEFINE generic_row ROW;
END PROCEDURE;

It returns the following error:

-999    Not implemented yet.

The Informix manual does not seem to be correct:

The following statements show examples of generic ROW variables and named ROW variables:

DEFINE d ROW; -- generic ROW variable

If you define the fields of the ROW then you can use it inside the SPL.

Upvotes: 0

Related Questions