on 2018 Dec 12 8:52 AM
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 1in 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.
Request clarification before answering.
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. `
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
Thanks again Chris - I was concerned that the v16 fix might miss the last ebf.
It is our current plan to include this fix in a v16 SP for Windows and Linux.
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)================
> 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? 🙂
So, Breck The Time Traveller, did SAP make it to fix the bug before 2019?
I'll let you know last Thursday.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.