|
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; 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. |
|
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. ` 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)================ 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
|
|
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.
|
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.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'"?
Seems reasonable. So you need to specify your IN params as such, I assume?
You're right, thanks. I've corrected the text of the error message.