Re: [Info-Ingres] Why are procedures fussy about tids?

[Database Administrator]

It also fails on II2.6 and Ingres2006R2.



I've attached a test script. Just run it against any database you like.

eg. sql iidbdb < test_this.sql



Marty



From: info-ingres-bounces@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
Martin Bowes
Sent: 11 November 2008 15:07
To: info-ingres@kettleriverconsulting.com
Subject: [Info-Ingres] Why are procedures fussy about tids?



Hi All,



I have a procedure which is fairly simple:

create procedure show_x(gtt set of (id int4 not null))

as

declare

my_tid int4 not null;

id int4 not null;

msg varchar(256) not null;

begin

SELECT FIRST 1 tid, id INTO :my_tid, :id FROM GTT ;

msg = 'tid: ' + varchar(my_tid) + ', id: ' + varchar(id) ;

MESSAGE msg ;

end;



However, when I attempt to load that procedure I get: E_US0836 line 1,
Column 'tid' not found in any specified table.



Now here's the funny thing...both the following work...

1. Rewrite the procedure to use tid rather than my_tid.

create procedure show_x2(gtt set of (id int4 not null))

as

declare

tid int4 not null;

id int4 not null;

msg varchar(256) not null;

begin

SELECT FIRST 1 tid, id INTO :tid, :id FROM GTT ;

msg = 'tid2: ' + varchar(tid) + ', id: ' + varchar(id) ;

MESSAGE msg ;

end;

2. Rewrite the procedure to include an extra variable declaration for
'tid', but it is not used in the procedure.

create procedure show_x3(gtt set of (id int4 not null))

as

declare

my_tid int4 not null;

tid int4 not null; /* Dummy declaration, it is not
used */

id int4 not null;

msg varchar(256) not null;

begin

SELECT FIRST 1 tid, id INTO :my_tid, :id FROM GTT ;

msg = 'tid: ' + varchar(my_tid) + ', id: ' + varchar(id) ;

MESSAGE msg ;

end;



Anyone got an explanation for that behaviour?



I'm using II 9.0.4 (a64.lnx/105)NPTL +patch12707