At 10:03 PM 15-05-02 +0000, you wrote:

>This is long, apologies. I've examined this for an hour now and
>can't find the reason for the fault.
>
>I have a stored procedure that writes a script out to an external
>table for later running in DSQL.
>
>It works fine except for one glitch: a variable within a select seems
>to be stuck on the very first value retrieved. It's the var_pkname
>variable noted below with <-----. This variable holds the primary
>key name for the current table name. As you can see from the sample
>output at the very bottom, it correctly finds the pk name of the
>first table, but fails to retrieve the pk names of the other 25+
>tables.
>
>It's odd because the procedure is walking through all the table
>names, and as far as I can tell it should also be walking through the
>pk names. I have run the code snippet in DSQL
>substituting :var_tablename with various valid table names and the
>primary index name always comes back for the correct table.
>
> 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)
> )
>
>'T_ACTION' -> 'ACT_PK'
>'T_ADDRESS' -> 'ADDR_PK'
>'T_CODESET' -> 'CODE_PK'
>
>All correctly reported primary keys for those tables. So I know that
>bit of SQL works fine. Anyway, here's a chunk of the stored
>procedure. If someone can point out why the :var_pkname value isn't
>updating as the procedure cycles through the table names, I'd love to
>hear it.

A few comments on the logic...

1. I suggest (a) initialising your variables and (b) re-initialising them
at the start of each iteration of the FOR loop.

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.
(Your variable name confused me for a while, since the value you are
pulling here isn't the PK name but the name of the PK column, right?)

So try this instead:
...
VAR_PKNAME = '<NONE>';
SELECT first 1 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;

If that doesn't do the trick, then you probably have to use a FOR..SELECT
LOOP with a counter, as we used to have to do before we had SELECT FIRST:
...
declare variable loopcounter smallint;
...
loopcounter = 0;
VAR_PKNAME = '<NONE>';
for 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 do
begin
if (loopcounter = 0) then
begin
/* write your log record */
loopcounter = loopcounter + 1;
VAR_PKNAME = '<NONE>';
end
end

If in fact you DO have any complex primary keys, your processing loop will
have to use another variable to store the column name on each iteration of
this loop and you'll have to concatenate the value (and watch out for sizes)
e.g.
...
declare variable var_pk_segment;
Declare variable loopcounter smallint;
...
loopcounter = 0;
VAR_PKNAME = '<NONE>';
var_pk_segment = '';
for 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_pk_segment do
begin
if (loopcounter = 0) then
VAR_PKNAME = var_pk_segment;
else
VAR_PKNAME = VAR_PKNAME||'&'||var_pk_segment;
var_pk_segment = '';
loopcounter = loopcounter + 1;
end
/* write your log record */
....

also...
3. Move this ahead of the start of the FOR loop:

/* SET QUOTE VARIABLE */
VAR_QUOTE = UDF_CHAR(39);

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________