Thanks to all for help. Solution has been found, although I still
don't understand the server logic, and the solution itself seems to
violate singleton logic worse than the original. I'll detail what I
suspect is happening, but it's just a guess.

First, the state of play after trying all suggestions without
success. Skip directly to the bottom (WHAT WORKED section) if you
want to see how I made the code work by changing one word -- which
still baffles me.

--- In ib-support@y..., "Woody" <woody.tmw@i...> wrote:
>> INTO :VAR_PKNAME; <----------*** VARIABLE NOT REFRESHING
>The first thing I would try would be to set the variable to
>something like
>"NotFound" just before you execute this. In that way, you can tell
>whether
>or not it is actually finding the index for the current table or
>not. If it
>isn't, you can start looking at things such as case, etc.

I initialised the variable var_pkname but it made no difference.
Plus there was the fact that it *was* putting the first selected loop
value in there ('pk_act'); it just wasn't seemingly getting
subsequent selected values.

--- In ib-support@y..., Helen Borrie <helebor@t...> wrote:
> 1. I suggest (a) initialising your variables and (b) re-
initialising them
> at the start of each iteration of the FOR loop.

Good practice, of course, but no change to results in my case.

> 2. This isn't a singleton select - on a complex index there will be
> multiple rows meeting the WHERE criteria of the subselect. If all
of your
> PKs are single column, you will always get a singleton, but, from
the point
> of view of SQL logic, I think you should probably force it to be
singleton.

It does indeed seem to be a singleton issue, evidenced by the fact
that it performs the select into var_pkname once then for all further
select loops either:

a) fetches the same value: first match of 'PK_ACT';

b) returns null and therefore var_pkname remains at the original
value of 'pk_act' -- actually no, I just remember I tested this by
initializing var_pkname to 'ERROR' immeditately inside the FOR SELECT
DO and it returned 'pk_act' every time, never that initialized value;

The select is singleton even if it is possible for it to return
multiple values. The way I have set up this db, the results of the
select are unique, and I have verified this with:

SELECT RDB$INDEX_NAME, RDB$RELATION_NAME
FROM RDB$INDICES
WHERE (RDB$INDEX_NAME STARTING WITH 'RDB$PRIMARY')
AND (RDB$RELATION_NAME starting with 'T_')

which returns a unique set of rows.

So going over what I assume to be facts at this point after much
testing:

1) The select of var_pkname works in DSQL (without the INTO line),
that is:

a) this original code from the SP has the var_pkname problem -

SELECT RDB$FIELD_NAME
FROM RDB$INDEX_SEGMENTS
WHERE RDB$INDEX_NAME =
(
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE (RDB$INDEX_NAME STARTING WITH 'RDB$PRIMARY')
AND (RDB$RELATION_NAME = :VAR_TABLENAME)
)
INTO :VAR_PKNAME;

b) yet this DSQL version works fine, returning correct rdb$field_name
(and therefore :var_pkname) -

SELECT RDB$FIELD_NAME
FROM RDB$INDEX_SEGMENTS
WHERE RDB$INDEX_NAME =
(
SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE (RDB$INDEX_NAME STARTING WITH 'RDB$PRIMARY')
AND (RDB$RELATION_NAME = 'T_ADDRESS') <--- or any valid tablename
);

2) The select is effectively singleton;

3) Initializing the variable var_pkname inside the FOR SELECT DO
block has no effect;

4) The FOR SELECT DO block *is* iterating through the entire set of
table names therefore the selection of var_pkname is happening for
each var_tablename;


WHAT WORKED...

I changed the line above from

WHERE RDB$INDEX_NAME =

to

WHERE RDB$INDEX_NAME IN

and suddenly the loop produces the correct values for var_pkname.
Now, whenever var_tablename gets a new value, var_pkname is updated
accordingly with the table's primary key column name. Weird, huh.
This seems to badly violate singleton logic, and for it to work the
IN has to be producing just one value in the set.

Okay, well that's it for now. I have totally twisted my brain on
this one and lost about 6 hours. I'm happy to have got it working
but the logic still escapes me and that's a continuing worry.

Any further thoughts on this appreciated. I have a couple of ideas
about how this might be a bug (handling of variables within an SP)
but I can't think of a way to test/reproduce it by other means.
However, as always I expect that there is a logical explanation and
that the server is just doing what I told it to do.

Regards,
Andrew Ferguson