This issue seems to have come in since 16.0.0.2419 & 17.0.9.4838 and only when a procedure is called by a trigger.

The issue is reproducible in 16.0.0.2754 & 17.0.4913

create procedure proc1(param1 char(100))
begin
-- do nothing
end;

create table table1 (f1 char(100));

create trigger trig1 after insert on table1 REFERENCING NEW AS new_rec for each row begin call proc1(new_rec.f1) end;

Calling the procedure directly:

create variable res int;
res = call proc1('hh');
select res;
is fine. But when called by the trigger:
insert into table1 (f1) values ('x');
you get the error:
Could not execute statement.
Cannot modify column 'f1 in table 'new_rec' 
SQLCODE=-191, ODBC 3 State="42000" Line 1, column 1
in 16.0.0.2754 & 17.0.4913 BUT NOT in 16.0.0.2419 & 17.0.9.4838 where the insert is successful:

It seems to be related to the parameters. If not specified, SQLA treats them as INOUT and if you declare param1 one as INOUT, the behaviour is the same. If you explicitly declare param1 as IN, everything is OK in all versions.

asked 12 Dec '18, 08:52

Justin%20Willey's gravatar image

Justin Willey
7.2k123160229
accept rate: 21%

edited 12 Dec '18, 09:08

I wonder if the issue is related to this?

 ================(Build #4898  - Engineering Case #817338)================
    When a field of a row variable is used as an OUT(or INOUT) parameter of the 
    procedure, the procedure did not return the variable properly. This has been 
    fixed. For example:
    create procedure myproc(out id int) as
    begin
       set id = 3;
    end;
    create variable rowvar row(field1 int);
    set rowvar.field1 = 1;
    call myproc (rowvar.field1);
    select rowvar.field1; // expect 3, it was set to 1 incorrectly.

(12 Dec '18, 08:57) Justin Willey
Replies hidden
1

Aside: Is there a typo w.r.t. to the column name "f1" somewhere between "new_rec.f1" and "column 'fT in table 'new_rec'"?

(12 Dec '18, 09:00) Volker Barth
Replies hidden

Seems reasonable. So you need to specify your IN params as such, I assume?

(12 Dec '18, 09:06) Volker Barth

You're right, thanks. I've corrected the text of the error message.

(12 Dec '18, 09:07) Justin Willey

We are aware of this issue and are working on a resolution. It is related to the following change:

` ================(Build #4885 - Engineering Case #816858)================

In very rare circumstances, the server could crash if a function or procedure 
created with 
EXTERNAL NAME 'native-call' returns the special FLOAT or DOUBLE values NAN, 
INF, 
and INFINITY and the value is used in an SQL expression.
The problem does not happen if the function or procedure is created as external 
procedure
with EXTERNAL NAME '<call-specification>' LANGUAGE <language-type>.

Also the server has incorrectly cleared the SQL error if an function or 
procedure output 
parameter value could not be assigned due to a conversion or truncation 
error.

These problems has been fixed.
`
permanent link

answered 12 Dec '18, 16:19

Chris%20Keating's gravatar image

Chris Keating
4.1k2370
accept rate: 33%

converted 12 Dec '18, 16:28

Justin%20Willey's gravatar image

Justin Willey
7.2k123160229

Many thanks Chris. Are you able to say if a fix will be published for v16 as well as v17?

(12 Dec '18, 16:30) Justin Willey
1

I believe that we plan to make the fix in both v16 and v17 but it is unlikely that we will have a software update available before end of year. I will update the thread once I confirm this.

(12 Dec '18, 16:38) Chris Keating
Replies hidden
1

Thanks again Chris - I was concerned that the v16 fix might miss the last ebf.

(12 Dec '18, 16:41) Justin Willey
2

It is our current plan to include this fix in a v16 SP for Windows and Linux.

(13 Dec '18, 09:07) Chris Keating

If I've read that correctly, there has been a fix with a very similar description contained in EBF 16.0.0.2754:

 ================(Build #2748  - Engineering Case #816858)================

In very rare circumstances, the server could crash if a function or procedure 
created with 
EXTERNAL NAME 'native-call' returns the special FLOAT or DOUBLE values NAN, 
INF, 
and INFINITY and the value is used in an SQL expression.
The problem does not happen if the function or procedure is created as external 
procedure
with EXTERNAL NAME '<call-specification>' LANGUAGE <language-type>.

Also the server has incorrectly cleared the SQL error if an function or 
procedure output 
parameter value could not be assigned due to a conversion or truncation 
error.

These problems has been fixed.

I found the same text in EBF 17.0.9.4793 under

 ================(Build #4885  - Engineering Case #816858)================

(14 Dec '18, 04:49) Reimer Pods
Replies hidden
1

> very similar description

By "similar" you mean "identical", right?

> same text in EBF 17.0.9.4793 under ... Build #4885

Is that a spoiler for Season 3 of Travelers on Netflix? :)

(14 Dec '18, 11:44) Breck Carter
1

So, Breck The Time Traveller, did SAP make it to fix the bug before 2019?

(14 Dec '18, 12:06) Volker Barth
1

I'll let you know last Thursday.

(14 Dec '18, 12:32) Breck Carter
showing 4 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×236
×122
×109
×65
×38

question asked: 12 Dec '18, 08:52

question was seen: 103 times

last updated: 14 Dec '18, 12:32