[Solved] How to share tables between databases

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] How to share tables between databases

Postby gkick » Wed Feb 26, 2020 6:40 am

Hi all,

Lets say we have a CRM db, an accounting db, a rental db, a student registration db , a family ancestry db,....in other words multiple Base databases and they all need to access data in a table of the CRM db. Since they are all standalone databases, how do you get access to the crm data ?
Is it possible for a db to connect to more then one backend ? Or some sort of end of day replication ? Use Calc as go between ?
Would love to learn how such a requirment can be achieved.
Thks
Last edited by Hagar Delest on Fri Feb 28, 2020 9:09 am, edited 1 time in total.
Reason: tagged solved
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to share tables between databases

Postby Sliderule » Wed Feb 26, 2020 7:15 am

Your HSQL external ( for example HSQL Version 2.5.0 ) can have multiple SCHEMAs, for example:

  1. Schema default: PUBLIC
  2. Schema: "CRM"
  3. Schema: "account"
  4. Schema: "student registration"
  5. Schema: "family ancestry"
  6. Schema: INFORMATION_SCHEMA
Therefore, if a particular user has been granted authority to read and or write to a particular SCHEMA or TABLE, AND each SCHEMA is in the same database, all is well.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to share tables between databases

Postby gkick » Wed Feb 26, 2020 2:54 pm

Thank you for the swift response. Unfortunately the AND is no given, the table resides in a separate database.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to share tables between databases

Postby Sliderule » Wed Feb 26, 2020 4:09 pm

gkick wrote:Thank you for the swift response. Unfortunately the AND is no given, the table resides in a separate database.

Therefore, your database design is . . . you fill in the blank.

Perhaps, you could define a HSQL TEXT TABLE, that is, for example, use SqlTool.jar to EXPORT the data from one database, and, you will have that data available ( since the last time you exported it ) from the other database.

HSQL 2.x Online Documentation Text Tables: http://www.hsqldb.org/doc/2.0/guide/texttables-chapt.html

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: How to share tables between databases

Postby gkick » Thu Feb 27, 2020 5:53 am

Well no, not a question of design as per se, but more of a conceptual thing.

Small , medium size business do not go for full blown CRM, but more likely have some MRP2 module ,some form of contact db, one for invoicing, one for hr and most likely there is a bit of duplication going on.
If I recall correctly, old Access97 had the ability to attach tables from another db and or spreadsheets as well.
O well, can't have it all.
cheers
G
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to share tables between databases

Postby robleyd » Thu Feb 27, 2020 7:38 am

Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3191
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to share tables between databases

Postby Villeroy » Thu Feb 27, 2020 9:55 am


I think, this page describes how the same HSQL server runs more than one database. And a Base document connects to one specific database only.
Shared_Tables1.png
Server with 2 databases sharing one table


Sliderule suggests something like this:
Shared_Tables2.png
Server with 2 databases sharing one table and 2 Base clients
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to share tables between databases

Postby Villeroy » Thu Feb 27, 2020 10:05 am

And this is how I solved the same problem for me and my small databases. :lol:
Attachments
Shared_Tables3.png
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to share tables between databases

Postby robleyd » Thu Feb 27, 2020 10:19 am

I was thinking that, with several databases available, one might be able to use something like
SELECT * FROM CRM_db.TABLENAME ....

Specifying both the DB name and table name. But if Base is disadvantaged by only being able to connect to one database at a time, guess that isn't a possibility.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3191
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How to share tables between databases

Postby Villeroy » Thu Feb 27, 2020 10:35 am

robleyd wrote:I was thinking that, with several databases available, one might be able to use something like
SELECT * FROM CRM_db.TABLENAME ....

Yes, if "CRM_db" is the name of a database schema which includes the table named "TABLENAME". This is organized entirely on the server side.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27876
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Solved]Re: How to share tables between databases

Postby gkick » Fri Feb 28, 2020 4:36 am

Thank you all gentlemen, interesting topic
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 207
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 2 guests