Working with multiple databases lazarus / 16-Databases ---------------------------------------------------------------------- Vodnik · 2020-01-07 08:54 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 · 2020-01-07 09:04 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 ---------------------------------------------------------------------- Vodnik · 2020-01-07 09:30 Quote from: MarkMLl on January 07, 2020, 10:04:58 amAre 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 · 2020-01-07 09:35 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 ---------------------------------------------------------------------- Vodnik · 2020-01-07 10:17 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 · 2020-01-07 10:51 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 ---------------------------------------------------------------------- Vodnik · 2020-01-07 20:02 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 · 2020-01-07 20:19 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 ---------------------------------------------------------------------- valdir.marcos · 2020-01-07 20:44 Quote from: Vodnik on January 07, 2020, 09:54:54 amHello, 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? Quote from: MarkMLl on January 07, 2020, 10:04:58 amAre 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. Quote from: Vodnik on January 07, 2020, 10:30:18 amQuote from: MarkMLl on January 07, 2020, 10:04:58 amAre 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... Quote from: MarkMLl on January 07, 2020, 10:35:52 amI'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? Quote from: Vodnik on January 07, 2020, 11:17:43 amThese 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). Quote from: MarkMLl on January 07, 2020, 11:51:57 amCan 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. Quote from: Vodnik on January 07, 2020, 09:02:55 pmI 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. Quote from: MarkMLl on January 07, 2020, 09:19:29 pmI'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_t he_report https://github.com/fortesinformatica/fortesreport-ce https://packages.lazarus-ide.org/ ---------------------------------------------------------------------- Vodnik · 2020-01-07 21:04 Thank you for plenty of information, valdir.marcos! Quote from: valdir.marcosThe 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 · 2020-01-07 21:17 Quote from: Vodnik on January 07, 2020, 10:04:30 pmThank you for plenty of information, valdir.marcos! Quote from: valdir.marcosThe 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 · 2020-01-07 21:20 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 Quote from: Thaddy on January 17, 2017, 12:30:14 pmIf 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 · 2020-01-11 15:44 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 ---------------------------------------------------------------------- MarkMLl · 2020-01-11 16:34 Quote from: Loquin on January 11, 2020, 04:44:39 pmHave 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 ---------------------------------------------------------------------- Loquin · 2020-01-11 17:42 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.) ---------------------------------------------------------------------- devEric69 · 2020-01-11 19:45 The ideal would be to find a trick to CRUD by SQL on only one of the 2 bases, for all the records, of the first base, and of the second base (with words like "IN", "UNION", or stored procedures...). Otherwise, maybe, tiOPF (https://wiki.freepascal.org/tiOPF) could wrap and talk with both databases in parallel. ---------------------------------------------------------------------- valdir.marcos · 2020-01-12 02:48 Quote from: Loquin on January 11, 2020, 04:44:39 pmHave 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_wrappersVery interesting. ---------------------------------------------------------------------- valdir.marcos · 2020-01-12 02:53 Quote from: devEric69 on January 11, 2020, 08:45:56 pmThe ideal would be to find a trick to CRUD by SQL on only one of the 2 bases, for all the records, of the first base, and of the second base (with words like "IN", "UNION", or stored procedures...). Otherwise, maybe, tiOPF (https://wiki.freepascal.org/tiOPF) could wrap and talk with both databases in parallel.Your idea is not usual, but database replication is: https://en.wikipedia.org/wiki/Replication_(computing) https://www.ibm.com/support/knowledgecenter/SSXVXZ_2.1.4/com.ibm.i2.ib ase.admin.doc/What_is_Database_Replication.html https://www.geeksforgeeks.org/data-replication-in-dbms/ ---------------------------------------------------------------------- Vodnik · 2020-01-13 13:52 Thank you for ideas, Loquin and devEric69! Never heard about this, investigating... ---------------------------------------------------------------------- devEric69 · 2020-01-13 15:32 I don't know the technical and societal context of your project, but the simplest solution would be - if and only if it doesn't pose a "diplomatic" problem, and if it doesn't go against this constraint that makes the specificity of your project, of course - would be to do a SQL refactor first: to pump all the data of a database, and to reinject the data 3FN in the second one, and to work then only on the remaining database.