[Example] Queries that can edit multi-tables

Creating tables and queries

[Example] Queries that can edit multi-tables

Postby Arineckaig » Tue Jan 17, 2012 5:11 pm

This thread arises from a recent post by DACM at:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=39&t=46630&p=216159#p216752

For some time now I have been experimenting with Queries that permit the editing of data from more than one Table source. As expalained in DACM's post the key is that the Query (or SQL) should include the PKs and linking FKs from each of the tables. In earlier versions of OOo Base this appeared not to be possible and even in OOo 3.3 there are limitations. For example, it appears to require the Query, or rather the underlying SQL, to be parsed by the Base GUI, and it only permits limited scope for adding new records.

In certain limited cases the ability to edit fields in more than one table from a single query or data form could have potentially useful benefits: for example, by comparison with the use of CONCAT for the display in a list box. I have attached a simple demo file to illustrate what can be done. I would welcome comments and be grateful to learn from the experience of others.
Attachments
Multi-tableFormREV.odb
Revision uses 'JOIN' in Query and SQL as being more reliable when there are more than two tables.
(23.11 KiB) Downloaded 659 times
Last edited by Arineckaig on Wed Feb 08, 2012 11:20 am, edited 5 times in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Queries that can edit multi-tables

Postby Villeroy » Tue Jan 17, 2012 6:28 pm

Thank you very much. It works well across 3 tables, JOIN syntax and HSQLDB 2.5 in server mode with LibreOffice 3.3.4 as client. I'll have to simplify some of my forms.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26142
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Queries that can edit multi-tables

Postby Arineckaig » Tue Jan 17, 2012 7:19 pm

FWIW I have tested it successfully with Many-to-Many links, with JOINs over four tables and with MySQL as the DMS. I do not trust it yet sufficiently, however, to justify risking its use with serious data: for example why does it not work with direct SQL? Will be interested where you find its problems.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Queries that can edit multi-tables

Postby Villeroy » Tue Jan 17, 2012 8:40 pm

Nothing works with direct SQL. SELECT * FROM "X" is read-only. Pairs of forms and subforms remain unlinked if either part runs direct SQL.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26142
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Queries that can edit multi-tables

Postby Arineckaig » Tue Jan 17, 2012 9:02 pm

Many thanks for your explanation. Until now I had quite failed to notice that direct SQL produced a Read-only result even with a single table source, whereas when GUI parsed the output can be used for updates. I learn something new (and dare I say impressive) about the Base GUI every day.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Example] Queries that can edit multi-tables

Postby RandyB » Thu Jan 10, 2013 10:31 pm

Wow, how lucky I am to have found this thread. I spent hours searching for the answer to my inability to use a Base form or query to INSERT or UPDATE my Contacts table in MySQL. I am leaving Office Access DB (which will sever my last tie with Microsoft!) in favor of MySQL, to be managed through a Base front-end. My first project is my checkwriting and reporting application for 3 bank accounts (16 years records) onto my new Linux-Mint PC. My Contacts table links to a Postal Code table from which the city and state are retrieved and a Prefix table that contains courtesy titles (Mr., Doctor, Senator, etc.) I used a Right Join from the Contracts table to the two look-up tables. On the Base form, I could see the desired data from all 3 tables but could not add or edit anything. My query contained only 2 of the 3 primary keys. As for the third PK, didn't need it, didn't want it. Now that you have shown me the light, life is good. I was so frustrated that I was about to drop Base and install/learn ooRexx to accomplish what I had hoped Base could do. Now that I know how to do what I need to, in Base, I am back in the Base camp. MANY THANKS!
LibreOffice 3.6 on Linux Mint 14 (64-bit)
RandyB
 
Posts: 1
Joined: Thu Jan 10, 2013 10:01 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 5 guests