Recent

Author Topic: Working with multiple databases  (Read 1938 times)

Vodnik

  • Full Member
  • ***
  • Posts: 175
Working with multiple databases
« on: January 07, 2020, 09:54:54 am »
Hello,
I have basic experience using DBgrid for producing some customer specific reports.
Now I have a task to build a report over 2 different databases: Informix and PostgreSQL.
Were the necessary tables in the same DB, I could use SQL SELECT with JOIN.
But they are not... So SQL is a bit different, and fields format, too. I plan to match records by date field, that is DATETIME YEAR TO FRACTION(3) in Informix and TIMESTAMPTZ in PostgreSQL.
Are there any common practice, general approach or ideas for that case?

MarkMLl

  • Hero Member
  • *****
  • Posts: 1791
Re: Working with multiple databases
« Reply #1 on: January 07, 2020, 10:04:58 am »
Are you going the have to have multiple databases (i.e. Informix and PostgreSQL) open simultaneously from the same program? I don't believe I've tried that.

Something that I do believe will need special attention is date formats. You will probably find that one of the databases is more flexible than the other, and you might find that operation sequences work better if first you get data from the less flexible one and then you apply it to the more flexible.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

Vodnik

  • Full Member
  • ***
  • Posts: 175
Re: Working with multiple databases
« Reply #2 on: January 07, 2020, 10:30:18 am »
Are you going the have to have multiple databases (i.e. Informix and PostgreSQL) open simultaneously from the same program?
I don't know yet how to deal with it. Seems I can use 2 sets of TODBCConnetion, TSQLTransaction, TSQLQuery, TDataSource components, but I definitely have to produce output in 1 TDBGrid...
 :-\

MarkMLl

  • Hero Member
  • *****
  • Posts: 1791
Re: Working with multiple databases
« Reply #3 on: January 07, 2020, 10:35:52 am »
I'd suggest stopping at this point and researching whether anybody has already written a wrapper or pooling server that allows PostgreSQL + Informix to appear as a single database, even if one of them (probably Informix) is strictly read-only.

Would it be feasible to transfer the Informix content into Postgres for the purpose of report etc. generation?

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

Vodnik

  • Full Member
  • ***
  • Posts: 175
Re: Working with multiple databases
« Reply #4 on: January 07, 2020, 11:17:43 am »
These two DBs are parts of some SW products, both do not allow DB modification.
Your idea may be a path to the solution: export data from 2 DBs to the third DB and use it for reports.
But: these are relatively huge and dynamic DBs, so I have to write a process that will do export online. That is not very easy to me.
Also third DB needs additional resources (space, processor time).

MarkMLl

  • Hero Member
  • *****
  • Posts: 1791
Re: Working with multiple databases
« Reply #5 on: January 07, 2020, 11:51:57 am »
Can you get enough access to the PostgreSQL database to be able to set up a local schema to contain a copy of the Informix data (i.e. a namespace to keep tables etc. separate)? I've not tried it but I'd have thought that in principle it would then be possible to have queries extending over the entire data, which would simplify things enormously.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

Vodnik

  • Full Member
  • ***
  • Posts: 175
Re: Working with multiple databases
« Reply #6 on: January 07, 2020, 09:02:55 pm »
I have admin access to PostgreSQL. But copying the data from Informix to PostgreSQL have the same problems as with third DB: real-time process needed, huge space, additional resources.

MarkMLl

  • Hero Member
  • *****
  • Posts: 1791
Re: Working with multiple databases
« Reply #7 on: January 07, 2020, 09:19:29 pm »
I'd suggest starting off with a subset of the data to make a business case for procuring the resources. Alternatively see if it's possible to write something like one of the PostgreSQL pooling proxies that will merge the two servers as far as your program is concerned.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1018
Re: Working with multiple databases
« Reply #8 on: January 07, 2020, 09:44:57 pm »
Hello,
I have basic experience using DBgrid for producing some customer specific reports.
Now I have a task to build a report over 2 different databases: Informix and PostgreSQL.
Were the necessary tables in the same DB, I could use SQL SELECT with JOIN.
But they are not... So SQL is a bit different, and fields format, too. I plan to match records by date field, that is DATETIME YEAR TO FRACTION(3) in Informix and TIMESTAMPTZ in PostgreSQL.
Are there any common practice, general approach or ideas for that case?

Are you going the have to have multiple databases (i.e. Informix and PostgreSQL) open simultaneously from the same program? I don't believe I've tried that.

Something that I do believe will need special attention is date formats. You will probably find that one of the databases is more flexible than the other, and you might find that operation sequences work better if first you get data from the less flexible one and then you apply it to the more flexible.

Are you going the have to have multiple databases (i.e. Informix and PostgreSQL) open simultaneously from the same program?
I don't know yet how to deal with it. Seems I can use 2 sets of TODBCConnetion, TSQLTransaction, TSQLQuery, TDataSource components, but I definitely have to produce output in 1 TDBGrid... :-\

I'd suggest stopping at this point and researching whether anybody has already written a wrapper or pooling server that allows PostgreSQL + Informix to appear as a single database, even if one of them (probably Informix) is strictly read-only.

Would it be feasible to transfer the Informix content into Postgres for the purpose of report etc. generation?

These two DBs are parts of some SW products, both do not allow DB modification.
Your idea may be a path to the solution: export data from 2 DBs to the third DB and use it for reports.
But: these are relatively huge and dynamic DBs, so I have to write a process that will do export online. That is not very easy to me.
Also third DB needs additional resources (space, processor time).

Can you get enough access to the PostgreSQL database to be able to set up a local schema to contain a copy of the Informix data (i.e. a namespace to keep tables etc. separate)? I've not tried it but I'd have thought that in principle it would then be possible to have queries extending over the entire data, which would simplify things enormously.

I have admin access to PostgreSQL. But copying the data from Informix to PostgreSQL have the same problems as with third DB: real-time process needed, huge space, additional resources.

I'd suggest starting off with a subset of the data to make a business case for procuring the resources. Alternatively see if it's possible to write something like one of the PostgreSQL pooling proxies that will merge the two servers as far as your program is concerned.
@Vodnik
The best solution would be to talk to both DBAs and request the same SQL ANSI-Compliant Stored Procedure to be created on both DBs:
https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0456.htm
https://stackoverflow.com/questions/10851558/simple-informix-select-stored-procedure
https://www.postgresqltutorial.com/postgresql-create-procedure/

Beyond that, you have, at least, two other solutions: in-memory databases and variable sourced reports:

- Use can use an in-memory databases as an intermediate layer before the report:
https://wiki.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC
Easy to use memory dataset
https://forum.lazarus.freepascal.org/index.php/topic,31698.0.html

- Use can use customized data (variables, text, XML, JSON etc) in reports:
https://wiki.freepascal.org/LazReport_Tutorial
https://wiki.lazarus.freepascal.org/FPReport
https://wiki.freepascal.org/FPReport_Designer
https://wiki.lazarus.freepascal.org/FPReport_Usage#Providing_data_to_the_report
https://github.com/fortesinformatica/fortesreport-ce
https://packages.lazarus-ide.org/

Vodnik

  • Full Member
  • ***
  • Posts: 175
Re: Working with multiple databases
« Reply #9 on: January 07, 2020, 10:04:30 pm »
Thank you for plenty of information, valdir.marcos!
Quote from: valdir.marcos
The best solution would be to talk to both DBAs and request the same SQL ANSI-Compliant Stored Procedure to be created on both DBs:
https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0456.htm
https://stackoverflow.com/questions/10851558/simple-informix-select-stored-procedure
https://www.postgresqltutorial.com/postgresql-create-procedure/
But how to put the data from two datasets into one dbgrid? How stored procedures can help with this?

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1018
Re: Working with multiple databases
« Reply #10 on: January 07, 2020, 10:17:01 pm »
Thank you for plenty of information, valdir.marcos!
Quote from: valdir.marcos
The best solution would be to talk to both DBAs and request the same SQL ANSI-Compliant Stored Procedure to be created on both DBs:
https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0456.htm
https://stackoverflow.com/questions/10851558/simple-informix-select-stored-procedure
https://www.postgresqltutorial.com/postgresql-create-procedure/
But how to put the data from two datasets into one dbgrid? How stored procedures can help with this?
Being the same SELECT, you just need to change the CONNECTION to check the information from different databases.

However, if you need to check them simultaneously on only one TDBGrid, then you will need an in-memory database as an intermediate layer to join them.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1018
Re: Working with multiple databases
« Reply #11 on: January 07, 2020, 10:20:17 pm »
You can also be brave and faithful and build your own solution with old school Printer or the new object oriented Printers:

https://wiki.freepascal.org/Using_the_printer

Printing in Lazarus
https://forum.lazarus.freepascal.org/index.php/topic,34739.0.html

https://www.freepascal.org/~michael/articles/lazprint/lazprint.pdf


If you use my old school lst/writeln method the unit is printer without S. If you use other methods, object oriented, with the printer class, the unit is printers, with the S.

Printing
https://forum.lazarus.freepascal.org/index.php/topic,35448.0.html

Printing a free Pascal program
https://forum.lazarus.freepascal.org/index.php/topic,8599.0.html

[SOLVED] Print Long String Memo1.Text
https://forum.lazarus.freepascal.org/index.php/topic,45382.0.html

[SOLVED - Sort of!] Printing RichMemo
https://forum.lazarus.freepascal.org/index.php/topic,47336.0.html

Printing more lines
https://forum.lazarus.freepascal.org/index.php/topic,13180.0.html

Problems with printer4Lazarus in Ubuntu
https://forum.lazarus.freepascal.org/index.php/topic,45412.0.html

TPrinter : Multipage problem with output PS File
https://forum.lazarus.freepascal.org/index.php/topic,24128.0.html

Printing: Paper "" not supported
https://forum.lazarus.freepascal.org/index.php/topic,13557.0.html

Loquin

  • New member
  • *
  • Posts: 5
Re: Working with multiple databases
« Reply #12 on: January 11, 2020, 04:44:39 pm »
Have you looked at FOREIGN DATA WRAPPERs on the postgresql side?

A postgresql foreign days wrapper essentially maps a foreign database table with a postgresql table header.  Then, you can use a sql statement in PG rjoining tables/views in both databases. 

Ref pg documentation for PG v9.1 and greater, and here:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
« Last Edit: January 11, 2020, 04:52:41 pm by Loquin »

MarkMLl

  • Hero Member
  • *****
  • Posts: 1791
Re: Working with multiple databases
« Reply #13 on: January 11, 2020, 05:34:28 pm »
Have you looked at FOREIGN DATA WRAPPERs on the postgresql side?

A postgresql foreign days wrapper essentially maps a foreign database table with a postgresql table header.  Then, you can use a sql statement in PG rjoining tables/views in both databases. 

Ref pg documentation for PG v9.1 and greater, and here:
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

Very good point.

MarkMLl
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

Loquin

  • New member
  • *
  • Posts: 5
Re: Working with multiple databases
« Reply #14 on: January 11, 2020, 06:42:56 pm »
One thing you will want to think about also is performance.  You say there is a lot of data in both databases.  Depending upon where the query filtering is taking place, the entire contents of the fdw (foreign data wrapper) table may need to be sent to the of database before it's filtered, which could ... drag.  A table-returning stored procedure in both the informix and pg databases may be needed, so that the filter criteria passed to it as parameters can be applied at the data source. 

This approach would also support data pagination (retrieval of data one 'page' at a time.)

 

TinyPortal © 2005-2018