cancel
Showing results for 
Search instead for 
Did you mean: 

"Cannot modify column" Behaviour change / bug in latest v16 & v17 ebfs

justin_willey
Participant
2,045

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.

Accepted Solutions (1)

Accepted Solutions (1)

chris_keating
Product and Topic Expert
Product and Topic Expert

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.
`
justin_willey
Participant
0 Kudos

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

chris_keating
Product and Topic Expert
Product and Topic Expert

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.

justin_willey
Participant

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

chris_keating
Product and Topic Expert
Product and Topic Expert

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

reimer_pods
Participant
0 Kudos

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)================

Breck_Carter
Participant

> 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? 🙂

VolkerBarth
Contributor

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

Breck_Carter
Participant

I'll let you know last Thursday.

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

Probably solved by this fix in 17.0.9.4935 and 16.0.0.2798 (which I have not tested):

    ================(Build #4921  - Engineering Case #817669)================

    The server has incorrectly returned the SQL error SQLE_CANNOT_MODIFY if a 
    procedure call in a trigger body took an old row column as INOUT or OUT parameter 
    argument. This has been fixed. To work around the problem you may define 
    the procedure parameter as IN or assign the old row column value to a local 
    variable that you use as procedure argument.