Query across multiple databases on same server

  • Status: Closed
  • Præmier: $300
  • Modtagne indlæg: 8
  • Vinder: sopholos

Konkurrence Instruktioner

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 [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], [login to view URL], Year([login to view URL]) [Year], Month([login to view URL]) [Month], Sum([login to view URL]) Sales
From TicketHD h, Customer c
Where [login to view URL] Between '01/01/2015' And '06/30/2015'
And c.Tel=[login to view URL]
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.
[login to view URL]

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

Anbefalede Evner

Arbejdsgiverfeedback

“Did a great job would hire again. ”

Profilbillede ProgramDelphi, United States.

Bedste indlæg fra denne konkurrence

Se flere indlæg

Offentlig Præciserings Opslagstavle

  • mlnvasko
    mlnvasko
    • 2 år siden

    Hi,

    • 2 år siden
    1. mlnvasko
      mlnvasko
      • 2 år siden

      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 år siden
    2. mlnvasko
      mlnvasko
      • 2 år siden

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

      • 2 år siden
  • davidgrs
    davidgrs
    • 2 år siden

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

    • 2 år siden
  • fb557935755b9f2
    fb557935755b9f2
    • 2 år siden

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

    • 2 år siden
    1. sopholos
      sopholos
      • 2 år siden

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

      • 2 år siden
  • ProgramDelphi
    Konkurrenceafholder
    • 2 år siden

    delphi

    • 2 år siden
  • BlackLotos2006
    BlackLotos2006
    • 2 år siden

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

    • 2 år siden
  • expertcoder1vw
    expertcoder1vw
    • 3 år siden

    Hi,

    • 3 år siden
    1. ProgramDelphi
      Konkurrenceafholder
      • 2 år siden

      hi

      • 2 år siden
  • WebInfoSolutions
    WebInfoSolutions
    • 2 år siden

    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 år siden
    1. ProgramDelphi
      Konkurrenceafholder
      • 2 år siden

      This is the way it is setup

      • 2 år siden
  • ProgramDelphi
    Konkurrenceafholder
    • 3 år siden

    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

    • 3 år siden
    1. videando
      videando
      • 2 år siden

      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 år siden
    2. ProgramDelphi
      Konkurrenceafholder
      • 2 år siden

      are you going to place an entry ?

      • 2 år siden
  • ProgramDelphi
    Konkurrenceafholder
    • 2 år siden

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

    • 2 år siden
  • kukhtinigor
    kukhtinigor
    • 3 år siden

    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

    • 3 år siden
    1. videando
      videando
      • 2 år siden

      No memory table, please!!!

      • 2 år siden
  • gyk
    gyk
    • 3 år siden

    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!

    • 3 år siden
    1. sopholos
      sopholos
      • 3 år siden

      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.

      • 3 år siden
    2. sopholos
      sopholos
      • 3 år siden

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

      • 3 år siden
  • gyk
    gyk
    • 3 år siden

    But of course, customer always right :)

    • 3 år siden
  • ProgramDelphi
    Konkurrenceafholder
    • 3 år siden

    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

    • 3 år siden
  • expertcoder1vw
    expertcoder1vw
    • 3 år siden

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

    • 3 år siden
    1. ProgramDelphi
      Konkurrenceafholder
      • 3 år siden

      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

      • 3 år siden
  • kukhtinigor
    kukhtinigor
    • 3 år siden

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

    • 3 år siden
    1. ProgramDelphi
      Konkurrenceafholder
      • 3 år siden

      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.

      • 3 år siden

Vis flere kommentarer

Sådan kommer du i gang med konkurrencer

  • Opret din konkurrence

    Opret din konkurrence Hurtigt og nemt

  • Få tonsvis af indlæg

    Få tonsvis af indlæg Fra hele verden

  • Tildel det bedste indlæg

    Tildel det bedste indlæg Download filerne - Nemt!

Opret en Konkurrence Nu eller slut dig til os i dag!