FOREACH in a stored porcedure

[dmaranan]

I'm runnig 7.3 and am having some problems with syntax.

Here is a foreach in a stored procedure:

FOREACH temp_table FOR
select...
into....
from ..
where ..
END FOREACH

How do I access values from the temp_table in another SQL statement in this stored procedure?

I was going to use temporary tables in the stored procedure, but I discovered that this causes many problems when multiple people attempt to called on the same procedure. I'd use cursors, but apparently the foreach statement is actually an implicit cursor (!). Also you cannot delcare an explicit cursor in a procedure! Any help would be great!

Dan

 

[olded]

Hi:

I'm sorry, but I'm not following the problem you're having with temp tables in stored procedures.

You're right about a foreach being an implicit cursor; Within a temp table, you can create a temp table, populate it with data with DML statements, and access the temp table with a FOREACH statement.

I think you'll have to post more information about the problems you're having.

Regards,


Ed
Schaefer

 

[dmaranan]

Ed,

When you create a procedure with the following syntax:

SELECT a, b, c
FROM alphabet
INTO TEMP alpha;

{Select from the temp table within the procedure.}
SELECT a, b, c, 1 sort
FROM alpha

it creates a temp table called alpha that is stored in the database. Unfortunately if a number of users execute the same procedure some users receive ane error like. "Table alpha already exists in the database."

In Oracle, a work around is to use a cursor, which basically stores a temporary table in specified memory address, but not in the database. So users can execute the procedure without any error messages. Informix's answer is to create an implicit cursor within a procedure using the FOREACH syntax. Unfortunately I have no idea how to open the cursor within a stored procedure. The informix 7.3 manual says you can access a cursor using OPEN name_cursor, but then later it says that OPEN cannot be used within a stored procedure (!). Thanks for any help that you can offer.

 

[olded]

Hi:

You have found a bug in Informix. In the early 90's I had a similar problem with Informix 4GL since fixed. If you have Informix tech support, I'd report it.

In the mean-time, there's two things you can try:

1) Before exiting the stored procedure, drop the temp table.

2) Instead of creating the temp table with the select, create the temp table stand alone, and then populate it with a select .. insert into the temp table.

(I'd create it with 'no log'. No sense sending this output to the Logical Logs).

Sorry, I can't be of more help.

Regards,

Ed