Handling SQL errors from constraints

English
, ,
#1

In a Base Form, I want to give the users a “remove” button for errantly created records in a subform. Thing is, the records’ primary key is referenced by foreign keys in other tables. Since foreign key checks will be enabled, deleting referenced records will be prevented, which is what I want, but I want to be able to handle the error by presenting the user with a non-cryptic explanation. Is there a good reference on how to handle a SQL error from foreign key constraints? I know there’s an option to use an On Error GoTo…Resume" statement, but I want to control the response based on the specific type of error.

#2

Could you give an example for the error, which appears? You have already set the relations and also what should happen on update and deleting?

#3

I want to prevent deletions that would orphan records in other tables. Yes, the relations are set up. I get the error message

Error deleting the specified records

Cannot delete or update a parent row: a foreign key constraint fails (metrology.measurementdata, CONSTRAINT FK_MEASUREMENTDATA_ITEMINWORKORDER FOREIGN KEY (ITEMINWORKORDER) REFERENCES itemsinworkorders (ID))

#4

So you want a message like “Deleting impossible.”?
You will need something like

FUNCTION SaveRequired(oEvent AS OBJECT) AS BOOLEAN
SaveRequired = True
IF … THEN
…
SaveRequired = False
END IF
END FUNCTION

This should run from the form → Before record action. You have to ask for foreign key in a separate query.

#5

Thanks! Sounds like a promising direction. Would there be a way to ensure that the record action is a deletion before showing the message? The form is supposed to be used for additions. And the delete option I’m adding is only for situations where errant additions have been made.

#6

Have tested with XRay:
oEvent.Action might be ‘3’ for deleting a record.
Seems to be ‘2’ for update and ‘1’ for insert a new row.

#7

Thanks!

So, does this look right?

FUNCTION SaveRequired(oEvent AS OBJECT) AS BOOLEAN
SaveRequired = True
IF oEvent.Action = 3 THEN
	ON LOCAL ERROR GOTO DeleteError
END IF
DeleteError:
	MsgBox 'Cannot make deletions that leave orphaned records.'
	SaveRequired = False
END FUNCTION

I’m concerned that this won’t work, because by the time the error is thrown, this function will have ended. Or will it work if I just do “ON ERROR” instead of “ON LOCAL ERROR”?

#8

Why don’t you try it without LOCAL? ON ERROR must be declared before the error appears. So I would set ON ERROR directly in second row after FUNCTION …

#9

Regardless of whether I have LOCAL in there, it does not want to compile. It says END FUNCTION is an “unexpected symbol”, so I must have the function set up wrong or something missing.

#10

Your SaveRequired function implements the approveRowChange method of XRowSetApproveListener interface.

is called before a row is inserted, updated, or deleted.
Returns TRUE when changing is allowed, otherwise FALSE.

Accordingly, you perform the necessary checks and return False if the checks fail.
After finishing of your procedure (if True is returned), the appropriate command is sent to the database. Thus, your procedure has nothing to do with a possible subsequent error.

#11

SQLError.odb (14.4 KB)

#12

I see… Sorry, I didn’t realize your approach was to prevent the action that would throw the error rather than handle the error. I’ll need to set the function up to query the DB for potential orphans, and the FK constraints are irrelevant (other than as a failsafe that will notify the user in SQLese).

#13

Miracles. @Villeroy answers the question before it is asked. :slightly_smiling_face:

#14

Thanks, Villeroy!
I must confess I’m not really sure what is going on in that macro. (I’ll take a little time to play with it and try to figure it out.) It looks like it’s set up to do exactly what I originally asked: handle the error and throw an appropriate custom msgbox. (Obviously, I would have to adjust the instructions to match the error that I’m handling.) So, am I correct in assuming this should be assigned to the the “Error occurred” event for the form?

#15

It is actually. Have a look at the form properties, tab “Events”.

#16

Same issues since many years.

#17

Unfortunately, in my case, the line

IF frm.ImplementationName <> "org.openoffice.comp.svx.FormController" THEN EXIT SUB

is causing it to EXIT SUB, which seems odd to me because I added

msgBox frm.ImplementationName

and the message was

org.openoffice.comp.svx.FormController

So, the string comparison must be failing.

#18

Is there a particular reason I need that line?

#19

In case of error, the routine is called twice by the form object and by the form controller object. If I remember correctly, I was not able to handle the form object properly, so I discarded that call and handled the form controller instead.

#20

The form controller provides some kind of alternative API, more like the dispatches in Writer and Calc. The last 2 lines in my little macro hit the undo button on the form navigation bar.
If you comment out the last one or two lines you get only the error messages without hitting the undo button.
https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1form_1_1runtime_1_1FormFeature.html

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.