How to use MERGE INTO via a form?

Discuss the database features
Post Reply
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

How to use MERGE INTO via a form?

Post by dreamquartz »

Hi All,

I am still working on a problem that is related to the large amount of data being entered in tables, and searching/viewing the content.
As a result, I am looking for a method to single out one record, in an 'ocean' of records, that already exists.
The main reason is: prevent duplication.
For ease of use, Views have created via Queries of certain tables.
An example:
Table tAddress has the following records:
AddressID
FKAddressBareID
FKPersonID
FKCompanyID
FKPostalCodeID
FKTypeOfAddressID
UnitNumber
Floor

This information is difficult to read, so a Query creates a View of tAddress to show a readable Address, like:
"Dreamquartz [# A 1234 Brenner Strasse 2nd Floor, Munich Germany 67E45T (Residence)]".

The Query we use, is always recreating the view of the table tAddress, every time a new Address is entered.
This is taking more and more time.
What we want to create is a situation where:
1. the information is presented to the DataBase, checked, based on a TRIGGER, and if exists, identified by AddressID, or added, if not present
2. only create a view of that information, which is either present, or new
for selection purposes in a form called fAddress.

For testing purposes I have created the following:
1.

Code: Select all

CREATE TABLE "tMail"
		 (
			 "MailID" BIGINT PRIMARY KEY,
			 "FKTypeOfEmailPhoneFaxID" INT,
			 "FKPersonID" BIGINT,
			 "FKEmployeeID" BIGINT,
			 "Mail" VARCHAR(100),
			 "Check" BOOLEAN
		 )
2.

Code: Select all

CREATE VIEW "vMail" AS SELECT * FROM "tMail"
3.

Code: Select all

CREATE TRIGGER "triggerMail"
	 INSTEAD OF INSERT ON "vMail"
		 REFERENCING NEW ROW AS NEWROW FOR EACH ROW
		    MERGE INTO "tMail"
			 USING
				 (
					  VALUES
						 (
							 DEFAULT,
							 NEWROW."FKTypeOfEmailPhoneFaxID",
							 NEWROW."FKPersonID",
							 NEWROW."FKEmployeeID",
							 NEWROW."Mail",
							 FALSE
						 )
				 ) AS vals
					 (
						 "MailID",
						 "FKTypeOfEmailPhoneFaxID",
						 "FKPersonID",
						 "FKEmployeeID",
						 "Mail",
						 "Check"
					 ) ON "tMail"."Mail" = vals."Mail"
	 WHEN MATCHED
		 THEN UPDATE SET "tMail"."Check" = TRUE
	 WHEN NOT MATCHED
		 THEN INSERT VALUES
				 vals."MailID",
				 vals."FKTypeOfEmailPhoneFaxID",
				 vals."FKPersonID",
				 vals."FKEmployeeID",
				 vals."Mail",
				 vals."Check"
The 'MERGE INTO' TRIGGER is needed to be able to set 'Check' to 'TRUE' from 'FALSE' if the entry is already in the table, because it allows for insert and update of a record.

I am stuck at the point that I can only seem to use 'DIRECT SQL' for data entry to make use of this TRIGGER.
I need to make a function like what i described above available in a form format for ease of use for our Client.

Can anyone give me some directions in how to make it possible to enter the data via a form and still utilize the 'MERGE INTO' TRIGGER?
I think it has to do with the syntax of the TRIGGER itself, because it uses 'VALUES' and not really the information presented for 'NEWROW'

Thanks in advance,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How 2 use MERGE INTO via a form?

Post by Villeroy »

Why not an UPDATE statement followed by an INSERT statement?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to use MERGE INTO via a form?

Post by Villeroy »

P.S. On form level I don't see any problem. You need a form to select and update existing records and another one to insert new records. In order to distinguish new records from existing ones, you need an index that prevents insertions of duplicates and one of these power filter forms.

P.P.S. A filter form with a subform shows the entered criteria in the subform where it is either an editable matching record or a new record with preset values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: How 2 use MERGE INTO via a form?

Post by dreamquartz »

Villeroy wrote:Why not an UPDATE statement followed by an INSERT statement?
What type of UPDATE do you mean?
There is an update for "Check", in MATCHED

Your 2nd response:
When entering the data into a form, duplicate information will simply be added; the TRIGGER does NOT fire.

I do not what the problem could be there.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: How to use MERGE INTO via a form?

Post by dreamquartz »

Villeroy,

can you please respond to my question under Extensions as well?

I cannot run my DataBase correctly.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to use MERGE INTO via a form?

Post by Villeroy »

I don't feel responisble for your lack of backup strategy.
I don't understand why installing extensions into a new user profile should fail.
After all those years you don't know what an UPDATE statement is? This is what Base does when you save a modified record. Base performs an INSERT statement when you save a new record. When importing a bunch of records you could first update those which already exist and then insert those which do not exist. However, when we are talking about form entries, there is no need for anything more complicated than a filtering form. You enter data into the main form and the subform offers to edit an existing record or insert a new one. And if you set up the right index you won't get any duplicates by mistake.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: How to use MERGE INTO via a form?

Post by dreamquartz »

Villeroy wrote:I don't feel responisble for your lack of backup strategy.
I don't understand why installing extensions into a new user profile should fail.
After all those years you don't know what an UPDATE statement is? This is what Base does when you save a modified record. Base performs an INSERT statement when you save a new record. When importing a bunch of records you could first update those which already exist and then insert those which do not exist. However, when we are talking about form entries, there is no need for anything more complicated than a filtering form. You enter data into the main form and the subform offers to edit an existing record or insert a new one. And if you set up the right index you won't get any duplicates by mistake.
I just made a mistake with the install.
I don't understand either.
I purged LO, and went back to lo 6.1.5.
I just wanted to create a clean install with new settings.

To be honest I do not make a backup of .config. I've never did.
Never thought it would be necessary.
I did copy the folder before installing the new LO, to be safe, but for what ever reason I decided everything 'Works', and hit the 'Delete' on my computer.
I will add .config to my Backups..... :crazy:

I do indeed know what UPDATE means, but when going over all the literature I could find, there is no mention of adding that in a MERGE INTO TRIGGER, so that is why I did not do it.

I am using a lot of BEFORE INSERT TRIGGERS, to prevent duplication, but now I am looking for a method to determine the 'record id' when the duplication occurs.

I created a simple form, but the MERGE INTO TRIGGER does not appear to work.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: How to use MERGE INTO via a form?

Post by dreamquartz »

Partially solved the issue about Extensions
Will tag the issue as SOLVED

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to use MERGE INTO via a form?

Post by Villeroy »

dreamquartz wrote:To be honest I do not make a backup of .config. I've never did.
Just activate "Déjà Dup", the backup utilitiy distributed with Ubuntu. By default it backs up your entire home except Downloads.
Prepare some external drive with a Linux file system and a special path as a target (a path that you don't use on other devices). Tell the program to backup into that path daily. Whenever you plug in the device, the backup will start automatically.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: How to use MERGE INTO via a form?

Post by dreamquartz »

I do Backups on a daily basis, but never included .config.
Now it is.

No back to the initial issue about MERGE INTO.
I am stuck at the point of making it possible to do entries via a form in stead of DIRECT SQL.
I have been trying all kinds of different approaches, but all have failed so far.

The TRIGGER does not fire when there is a duplication added via a form, while everything seems to work just fine when using DIRECT SQL.

Any suggestions,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to use MERGE INTO via a form?

Post by Villeroy »

When using a form, your manual entry affects a single record only. You either insert one new record or you update one existing record (leaving aside that a form can delete many records).
The record indicator and the toolbar clearly indicates what you are doing right now. Alternatively, you can use two separate forms for update and insert.

Things are different when doing mass updates, e.g. via copy&paste.


Form "Insert or Update" in the attached file demonstrates how to enter 3 values for "Person", "Category" and "Date" into a filter form. The subform offers either the existing record with a "Value" for editing or a new record without "Value" for insertion.
Form "Insert or Update2" separates update and insertion
Attachments
FilterData6.odb
(111.62 KiB) Downloaded 181 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply