Query across multiple databases on same server

  • Status: Closed
  • Hadiah: $300
  • Penyertaan diterima: 8
  • Pemenang: sopholos

Keterangan Peraduan

1. We have a database server with multiple databases on it (all have the same schema, different data)
lets call it company A and company B

2. We are looking for a way to query across all the databases or some databases

3. The end user would select company A,B,AB,AC

4. Needs to be easy to configure, as more databases may be added at any time. This data access must be real-time.

5. We envision having a function written in Delphi that would dynamically restructures the SQL in such a way to enable this functionality

Say, as an example, you have an application that inserts orders - each application has its own DB running as a separate company . What we are then looking for is an efficient way for a single application to then access the order information in all the other databases in order to run reports as one company.

This is an Example of SQL we now use.

Select [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], Year([url removed, login to view]) [Year], Month([url removed, login to view]) [Month], Sum([url removed, login to view]) Sales
From TicketHD h, Customer c
Where [url removed, login to view] Between '01/01/2015' And '06/30/2015'
And c.Tel=[url removed, login to view]
Group By 1, 2, 3, 4, 5, 6, 7, 8, 9;

I would test each solution on this example.

The database server we are using is Advantage database.
[url removed, login to view]

I believe any generic solution would work.
Maybe fireDac could do the trick somehow

Kemahiran-kemahiran Cadangan

Maklum balas Majikan

“Did a great job would hire again. ”

Gambar Profil ProgramDelphi, United States.

Penyertaan teratas dari peraduan ini

Lihat Penyertaan Lain

Papan Penerangan Awam

  • mlnvasko
    mlnvasko
    • 2 tahun yang lalu

    Hi,

    • 2 tahun yang lalu
    1. mlnvasko
      mlnvasko
      • 2 tahun yang lalu

      If its only about view the data, I would:
      1/ create separate DB view, to have it together in one place (but as a view to original DB's) with using union + add managment fields (Comp. A, B, .. and other necessary fields)
      or
      2/ Create DWH to merge all data to separeate DB and build another app (reports, app, ... up tp you) to see the data together over it

      • 2 tahun yang lalu
    2. mlnvasko
      mlnvasko
      • 2 tahun yang lalu

      then you can easy select from the view as you are used to do

      • 2 tahun yang lalu
  • davidgrs
    davidgrs
    • 2 tahun yang lalu

    depends on the fields, this can be resolve using union not problem. Pls send us an example that you need.

    • 2 tahun yang lalu
  • fb557935755b9f2
    fb557935755b9f2
    • 2 tahun yang lalu

    It's can be easy solved if you use Oracle DB. Just use:
    select * from Schema1.table1
    union all
    select * from Schema2.table1
    ...

    • 2 tahun yang lalu
    1. sopholos
      sopholos
      • 2 tahun yang lalu

      It can be easily solved that way even on MySQL, but our contest holder use some RDBMS from 90s %)

      • 2 tahun yang lalu
  • ProgramDelphi
    Pemegang Peraduan
    • 2 tahun yang lalu

    delphi

    • 2 tahun yang lalu
  • BlackLotos2006
    BlackLotos2006
    • 2 tahun yang lalu

    Delphi, C#, C++, C Builder, SQL, Interbase/Firebird, Oracle, MS SQL Server, MY SQL, Pascal, MS Access, HTML, WMI, XML, PHP, Bootstrap, JQuery

    • 2 tahun yang lalu
  • expertcoder1vw
    expertcoder1vw
    • 2 tahun yang lalu

    Hi,

    • 2 tahun yang lalu
    1. ProgramDelphi
      Pemegang Peraduan
      • 2 tahun yang lalu

      hi

      • 2 tahun yang lalu
  • WebInfoSolutions
    WebInfoSolutions
    • 2 tahun yang lalu

    I am not sure why you should be using separate database for separate Companies. As mentioned in the requirement that schema will be same but data will vary. So in this case. I would have used a Mater table with Company information and each will have their own referenced data.

    • 2 tahun yang lalu
    1. ProgramDelphi
      Pemegang Peraduan
      • 2 tahun yang lalu

      This is the way it is setup

      • 2 tahun yang lalu
  • ProgramDelphi
    Pemegang Peraduan
    • 2 tahun yang lalu

    It could cost more then 2500.00 for a 50 user license. Abusively there is a niche for this type of RDBMS it is now owned by SYBASE and they are coming out with a new ver. now

    • 2 tahun yang lalu
    1. videando
      videando
      • 2 tahun yang lalu

      If YES, only will be necesary implement a TQuery descendant with some added properties (database list, ...) for builing (dynamicly) join statements to create (using a masked query) the final query.

      • 2 tahun yang lalu
    2. ProgramDelphi
      Pemegang Peraduan
      • 2 tahun yang lalu

      are you going to place an entry ?

      • 2 tahun yang lalu
  • ProgramDelphi
    Pemegang Peraduan
    • 2 tahun yang lalu

    in this case the TEL is key in both tables. in all reports i could indicate what the key field is.

    • 2 tahun yang lalu
  • kukhtinigor
    kukhtinigor
    • 2 tahun yang lalu

    Hi. I solved this issue next way.
    1. of course you have to some keys in different databases to join Customers.
    2. i entered table with fields Company (A, B) and connection strings (''), So to add new company with new database you have to add new record to this table
    3. User choice date period and Company (Check Box)
    4. I create memory table (TClientDataSet as example or TmemDataSet) and fill with static fields (LastName, Name, Address, City, State, Zip, Tel, [Year], [Month]) and SumFields (Summ_A, Summ_B etc(how many database i have choosen))
    5. open query one by one and fill this dataset with data

    • 2 tahun yang lalu
    1. videando
      videando
      • 2 tahun yang lalu

      No memory table, please!!!

      • 2 tahun yang lalu
  • gyk
    gyk
    • 2 tahun yang lalu

    Sorry guys, but I have to say what such a data organisation is from the last century. Recently it is being done by separating company data with one field in the company table and filtering by company data, but not joining multiple tables to one table at db client level!

    • 2 tahun yang lalu
    1. sopholos
      sopholos
      • 2 tahun yang lalu

      And I must to say that using such RDBMS - last century %). SQL Server, Oracle and even MySQL can do cross-db queries. SQL Server can also do cross-server queries and even cross RDBMS-queries (!). But reality too pain to use this features on some projects.

      • 2 tahun yang lalu
    2. sopholos
      sopholos
      • 2 tahun yang lalu

      btw, very interesting to know how much costs this ADS?

      • 2 tahun yang lalu
  • gyk
    gyk
    • 2 tahun yang lalu

    But of course, customer always right :)

    • 2 tahun yang lalu
  • ProgramDelphi
    Pemegang Peraduan
    • 2 tahun yang lalu

    The Customer table is a list of customers with a PK TEL and TicketHD is a Table of sales details with a FK TEL. Sales Details from customer in company A may be in the TicketHD table of company B

    • 2 tahun yang lalu
  • expertcoder1vw
    expertcoder1vw
    • 2 tahun yang lalu

    Can you post this as a regular project? I already did this before. and have a working solution. (Delphi XE7).

    • 2 tahun yang lalu
    1. ProgramDelphi
      Pemegang Peraduan
      • 2 tahun yang lalu

      There is more then one way to do this. The benefit of a contest for me is I could see before hand who has the best solution and what really works. If you have done this before this would make it easier for you

      • 2 tahun yang lalu
  • kukhtinigor
    kukhtinigor
    • 2 tahun yang lalu

    very simple decision for not very large data size (near 200 mB). In other case it would be better to create local file.

    • 2 tahun yang lalu
    1. ProgramDelphi
      Pemegang Peraduan
      • 2 tahun yang lalu

      as you said, this may work with small files with bigger files I believe filling the memory table with all the data and then ruining the query on it would be a slow processes.

      • 2 tahun yang lalu

Tunjukkan lebih banyak komen

Bagaimana mula dengan peraduan

  • Papar peraduan anda

    Papar Peraduan Anda Cepat dan senang

  • Dapatkan bertan-tan penyertaan

    Dapatkan Bertan-tan Penyertaan Dari seluruh dunia

  • Anugerahkan penyertaan terbaik

    Anugerahkan penyertaan terbaik Muat turun fail-fail - Senang!

Papar Peraduan Sekarang atau Sertai kami Hari Ini!