Bug 122048 - SQL TRIGGER does not properly display SQLSTATE message text in a Base form
Summary: SQL TRIGGER does not properly display SQLSTATE message text in a Base form
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.3.5.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-12-12 16:54 UTC by Russell Salerno
Modified: 2023-01-20 07:14 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Attachments
example database (10.26 KB, application/vnd.oasis.opendocument.database)
2018-12-14 13:44 UTC, Drew Jensen 		Details
bt with debug symbols (8.70 KB, text/plain)
2019-01-19 12:02 UTC, Julien Nabet 		Details
bt from table (5.92 KB, text/plain)
2019-01-19 12:20 UTC, Julien Nabet 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Russell Salerno 2018-12-12 16:54:33 UTC 
Description:
I have a LO Base app using a split HSQLDB 2.3.2. It includes a table "checks" that has a boolean column "issued". When issued is "true" I want to prevent deletion of that row. So I created the following trigger to detect this condition and prevent deletion:

CREATE TRIGGER "delete_check" BEFORE DELETE ON "checks"
REFERENCING OLD AS oldrow

FOR EACH ROW WHEN (oldrow."issued" = true)
BEGIN ATOMIC
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Issued check cannot be deleted';
END;

When I use the built-in grid view and try to delete a row with the issued flag set, the message box DOES pop up as expected.

But running in a Base form and trying to delete the row using the form navigation control, the message box does not pop up. (In either case the row is not deleted, which is good because it means the trigger is working.)

Deleting from the 'Toolbar' navigation bar does not generate the message and once tried it also negates the display of the message from the form navigation control. 

This does start working once the form is closed & opened again.



Steps to Reproduce:
1.create HSQLDB before delete trigger as shown above
2.attempt to delete record that matches the trigger condition using form navigation control
3.expected message box does not appear

Actual Results:
no message box is displayed

Expected Results:
message box with error message should be displayed


Reproducible: Always


User Profile Reset: No



Additional Info:
See https://ask.libreoffice.org/en/question/175909/how-to-get-sql-trigger-to-display-sqlstate-message-text-in-a-base-form/
Comment 1 Alex Thurgood 2018-12-13 16:08:53 UTC 
@Russell : you reported this against LO 4352 (cf. version field). Does the problem also occur in current production releases of LibreOffice ?
Comment 2 Drew Jensen 2018-12-14 13:44:26 UTC 
Created attachment 147559 [details]
example database

Confirmed using Ubuntu 18.04.1, LO6.3Alpha1 and embedded firebird database.

The example file consists of an table CHECKS:
CREATE TABLE CHECKS( ID BIGINT NOT NULL PRIMARY KEY, ISSUED BOOLEAN, AMOUNT DECIMAL(10,2));

A custom exception:
CREATE EXCEPTION EX_CHECK_ISSUED
'May not delete record for check already issued'; 

and the delete trigger;
CREATE TRIGGER DELETE_CHECK 
ACTIVE BEFORE DELETE ON "CHECKS"
AS
BEGIN
    IF (OLD."ISSUED" = TRUE) THEN
       EXCEPTION EX_CHECK_ISSUED;
END;

To reproduce you can open the attached file and attempt to delete a record with ISSUED set to TRUE form the table view, and the trigger will display an error dialog with our custom message.

Open the form, with a grid control, and as reported the delete is blocked but the error message is not displayed.
Comment 3 Alex Thurgood 2018-12-14 13:54:56 UTC 
Confirmed by Drew
Comment 4 Alex Thurgood 2018-12-14 13:56:57 UTC 
Pretty certain that this has come up before, but can't find the bug report in question where a discussion was had on the matter.
Comment 5 Drew Jensen 2018-12-14 14:05:13 UTC 
(In reply to Alex Thurgood from comment #4)
> Pretty certain that this has come up before, but can't find the bug report
> in question where a discussion was had on the matter.

Oddly, if you use the form, select a record and click the delete key the error message is displayed.

But if you use the context menu 'delete' or the toolbutton then no message.
Comment 6 Robert Großkopf 2018-12-15 14:37:33 UTC 
(In reply to Drew Jensen from comment #5)
> (In reply to Alex Thurgood from comment #4)
> > Pretty certain that this has come up before, but can't find the bug report
> > in question where a discussion was had on the matter.
> 
> Oddly, if you use the form, select a record and click the delete key the
> error message is displayed.
> 
> But if you use the context menu 'delete' or the toolbutton then no message.

I got an errormessage here when try deleting by delete key or by context menu of the row:
-------------
firebird_sdbc error:
*exception 1
*EX_CHECK_ISSUED
*May not delete record for check already issued
*At trigger 'DELETE_CHECK' line: 6, col: 49
caused by
'isc_dsql_execute'
------------
But if I click one time on delete of the navigation-bar the error won't appear any more for any row of the form. Seems navigation-bar suppresses the message.

Tested with LO 6.1.4.2 OpenSUSE 15 64bit rpm Linux.
Comment 7 Julien Nabet 2019-01-19 12:01:39 UTC 
On pc Debian x86-64 with master sources updated yesterday, I could reproduce this with Firebird file.
I noticed this on console when trying to delete a record which shouldn't be:
warn:connectivity.firebird:3787:3787:connectivity/source/drivers/firebird/PreparedStatement.cxx:286: isc_dsql_execute failed
warn:connectivity.firebird:3787:3787:connectivity/source/drivers/firebird/Util.cxx:55: firebird_sdbc error:
*exception 1
*EX_CHECK_ISSUED
*May not delete record for check already issued
*At trigger 'DELETE_CHECK' line: 6, col: 49
caused by
'isc_dsql_execute'

Trying another time doesn't show this message again on console.
Comment 8 Julien Nabet 2019-01-19 12:02:51 UTC 
Created attachment 148434 [details]
bt with debug symbols

BT from console trace
Comment 9 Julien Nabet 2019-01-19 12:20:48 UTC 
Created attachment 148435 [details]
bt from table

In comparison, here's the bt from same location when trying to delete a record from the table directly (so with the error message).
Comment 10 QA Administrators 2021-01-19 04:11:17 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2023-01-20 03:24:49 UTC Comment hidden (obsolete)
Comment 12 Robert Großkopf 2023-01-20 07:14:10 UTC 
Buggy behavior still there in LO 7.4.4.2 on OpenSUSE 15.3 64bit rpm Linux.