[solved] How to address 2 databases by Laz? lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2024-01-27 12:33 I want to pump the data of my old database into a new one, with similar tables and similar fields. These migration tools I tried: IBcopy to IBPump. Every of it is that hard to use. IPcopy has no visual interface and is too hard to use in the Powershell Those Pump tools (2 versions) need dlls, every another one. I do not want to install that much for  the hope, they run one day. So I tried it by Lazarus and select and insert: I have 2 sets of DB Moduls with TIBDatabase, TIBQuery and TIBTransaction. But I cannot connect both the databases. I read the error message, the other database would be "shut down". What can I do? ---------------------------------------------------------------------- Hansvb · 2024-01-27 14:16 Hi, this probably won't be of much use to you, but in my daily work I work a lot with (Oracle) databases. I often read data from files or move data to and from another database (schema). I always do that with FME (safe software). That's expensive professional software, but I think you can apply for a home use license. It's meant to be for learning, but you might be able to use it. Then moving data is almost just a drag drop action. ---------------------------------------------------------------------- Thaddy · 2024-01-27 14:28 A good remark, Hans, I might add that that is dependent on the database used. E.g. banking software would not work without queries over multiple databases. As long as these databeses are in the same format. Oracle can indeed do that, And PostgreSQL and others. The Interbase guru (RVK)  said it is not possible: bogus. Mind you, there must be a owner-slave relationship for this to work, but e.g. in banking this is essential! (That is mostly Oracle, btw) and Oracle has a free developer program. But PostgreSQL can do - almost - the exact same. ---------------------------------------------------------------------- Nicole · 2024-01-27 15:07 I could bring it to work by Lazarus. My first table of in the new database has filled with data. The new primary key of this table was generated perfeclty, the Charset looks good. So I thank God and all you helpful guys here. This is really good an end of this week. This was an easy table. The hard ones with will follow. ---------------------------------------------------------------------- rvk · 2024-01-27 15:20 Quote from: Thaddy on January 27, 2024, 03:28:03 pmOracle can indeed do that, And PostgreSQL and others. The Interbase guru (RVK)  said it is not possible: bogus.I said it wasn't possible with Firebird in FlameRobin. I said NOTHING about other databases. Learn to read Thaddy       (aaah... I guess I know to whom I'm talking... sigh...) But hey, Thaddy... If YOU know a way to do this in Firebird... PLEASE let us know !!! (with a clear example so TS can actually do something with it !) You can do something in Firebird with EXTERNAL DATASOURCE. But that's limited and as far as I know not possible in FlameRobin (but I could be wrong). https://www.firebirdfaq.org/faq16/ ---------------------------------------------------------------------- Thaddy · 2024-01-27 18:14 Usually I can prove it in code... but you are a very very valuable resource for database programming in general. you just focus on the imho wrong product. Are you sure you never ran into problems with your beloved preference? I don't believe that. honest reply. ---------------------------------------------------------------------- rvk · 2024-01-27 18:21 Quote from: Thaddy on January 27, 2024, 07:14:26 pmUsually I can prove it in code... Of course it works in code if you create two database connection. (I stated as much in that topic) The issue was that you can't address TWO databases in ONE single SQL statement in Firebird. It can be done in other databases (as you stated) but NOT in Firebird (and that was what I was saying, so... not bogus, unless you can show us that SQL command). ---------------------------------------------------------------------- Thaddy · 2024-01-28 06:17 Oh, and what is this then? Is this really not supported in FireBird? Here's the simplest, basic, SQL: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT *FROM database1.schema1.table1JOIN database2.schema2.table2ON table1.COLUMN = table2.COLUMN;Such queries work with many mainstream database products. Seems to me that should also be possible in your favourite but I have not tried. This is quite a common pattern within corporates. Even across domains. (way more complex, though, I myself could not do it, but have seen it done) Let me know if it works for you or not. Trick is of course to query through their schema's. I have a suspicion it might work in FireBird too. You know my aversion to FireBird, so I could not test it for FireBird. List of products that are known to support this: PostgreSQL // mentioned that and use it MySQL MariaDB MS SQL server Oracle //mentioned that and used it and I believe SQLite too. Will test that later.[edit] tested, WORKS!! ---------------------------------------------------------------------- paweld · 2024-01-28 07:07 @Thaddy: As @rvk wrote Firebird does not allow queries from several databases: https://www.firebirdfaq.org/faq16/ ---------------------------------------------------------------------- Thaddy · 2024-01-28 07:19 I see. At least I read they are aware it is a great miss and there is some support. Makes it definitely less usable compared to other products. I still would like to see it tested. Even SQLite supports it. If is really not possible and it is important to her Nicole should choose a different database engine ---------------------------------------------------------------------- rvk · 2024-01-28 08:35 Quote from: Thaddy on January 28, 2024, 08:19:46 amI see. At least I read they are aware it is a great miss and there is some support. Makes it definitely less usable compared to other products. I still would like to see it tested. Even SQLite supports it. I tested it yesterday. Firebird supports something like ON EXTERNAL DATA SOURCE (see post below). Unfortunately this only works in a FOR EXECUTE STATEMENT. It will execute the statement and retrieve data from the other database but it is executed in its own 'context'. So you would need to save the result in variables and insert them below that, in the local database. There is no select * from db1.table1 into db2.table2 support here. https://firebirdsql.org/rlsnotesh/rnfb25-psql-exctstmnt.html Yes, that's a great miss in this situation. (Although I've never come across a situation where I would need it myself.) BTW. There is still another option present and that's EXTERNAL FILE. You can do CREATE TABLE ext_log EXTERNAL FILE 'd:\externals\log_me.txt' But that only supports text/binary flat files with fixed-length fields. You could create such table in one database and export the table and import it like that in the other. No BLOB support but VARCHAR works. You would need to do this per table. A lot of work (that's probably why copying the INSERTS would be easier). If you are going the route with external file, it would be easier to create an elaborate procedure which traverses all tables and creates such external tables, inserts the data and drops the external tables again (after copying the files). The same can then be done for import in the other database.. But hey... There are existing aops for pumping data from one database to another  Quote from: rvk on January 27, 2024, 06:55:15 pmI tried the ON EXTERNAL DATA SOURCE option of Firebird in FlameRobin but it's really limited. I can do something like this and that works: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SET TERM #;EXECUTE BLOCK RETURNS (LINK BIGINT) ASBEGIN  FOR EXECUTE STATEMENT 'select LINKNUMMER from RELATIE'       ON EXTERNAL DATA SOURCE 'localhost:c:\dblocation\test.fdb' AS USER 'sysdba' PASSWORD 'masterkey'  INTO :link  DO SUSPEND;END But you can't directly SELECT all fields automatically into a table in the local database. You would need to SELECT all fields in the correct variabele and after that command, INSERT those fields in your local table. And that for every table you want to copy. It would be much easier to do with the actual INSERT statements or via code in Lazarus with two DB connections. {snip off topic} ---------------------------------------------------------------------- Zvoni · 2024-01-29 07:51 Of course it's possible, exactly as rvk pointed out: Two Databases, two connections (each with its own transaction and TSQLQuery). Connect to the first database with Con1, trans1 and qry1 Connect to the second Database with con2, trans2 and qry2 Make sure you create the table in Database2 exactly to specification and then something along these lines (Aircode) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i While Not qry1.EOF Doqry2.Append;qry2.FieldByName('Field1'):=qry1.FieldByName('F ield1');//other Fieldsqry2.Post;qry1.Next;End;trans2.Commit; Is it efficient? Probably not! Will it get the Job done?..... ---------------------------------------------------------------------- Thaddy · 2024-01-29 08:15 I already gave the correct answer and rvk provided the specifics why it could hardly be done in SQL on Interbase and derivatives without jumping through hoops. A query like my simple example simply works, just not in FlameRobin. It is about the SQL and that is usually very efficient. I have to thank rvk for going into so much detail and presenting alternative options over the weekend. ---------------------------------------------------------------------- alpine · 2024-01-29 08:16 Quote from: Zvoni on January 29, 2024, 08:51:49 amand then something along these lines (Aircode) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {TSQLQuery}qry2.CopyFromDataset(qry1, True); Not sure it will work, but it looks like it will. ---------------------------------------------------------------------- Zvoni · 2024-01-29 08:23 Quote from: alpine on January 29, 2024, 09:16:28 amQuote from: Zvoni on January 29, 2024, 08:51:49 amand then something along these lines (Aircode) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {TSQLQuery}qry2.CopyFromDataset(qry1, True); Not sure it will work, but it looks like it will. Nice one! Didn't think of that. Downside to it: You cannot "deviate" from the original Datatypes in database1, if you want to use different Datatypes in database2. My version will even work across different DBMS (think migrating from SQLite to MySQL et al) as long as you "convert" in code (Remember: SQLite doesn't have a DateTime-Datatype) ---------------------------------------------------------------------- Thaddy · 2024-01-29 08:32 SQLite does have TDateTime, since it is merely a double. Also, and I take offence, querying a database or two does not equal importing a database or two. That is a huge difference. ---------------------------------------------------------------------- Thaddy · 2024-01-29 08:36 Quote from: Thaddy on January 29, 2024, 09:32:56 amSQLite does have TDateTime, since it is merely a double. Also, and I take offence, querying a database or two does not equal importing a database or two. That is a huge difference. ---------------------------------------------------------------------- Zvoni · 2024-01-29 08:37 Quote from: Thaddy on January 29, 2024, 09:32:56 amSQLite does have TDateTime, since it is merely a double. No, it doesn't. SQLite has three storage-classes for DateTimes: TEXT, REAL or INTEGER. You are talking about Affinity, that when you USE the DateTime- Datatype in the CREATE TABLE-Statement, SQLite will "translate" it to whatever SQLite thinks is appropriate. I've seen enough threads here, where people used Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i CREATE TABLE "SomeTable" (    ID INTEGER,    SomeDate DateTime);and wondered why they receive Garbage sometime EDIT: Where in blazes is the "Querying vs. importing" coming from?!?!??! From the very first post QuoteI want to pump the data of my old database into a new one, with similar tables and similar fields.There. It's about Importing/Migrating from one Database to another. If the Import/Migration is successful, the "old" Database will become obsolete ---------------------------------------------------------------------- alpine · 2024-01-29 09:08 I searched according to my memories for something called IBDataPump. It happens to be in the github, LGPL-ed: https://github.com/CleverComponents/Interbase-DataPump I wonder what it will get to be ported to Laz, and whether it will worth it. ---------------------------------------------------------------------- Zvoni · 2024-01-29 09:53 Quote from: Zvoni on January 29, 2024, 09:37:49 amEDIT: Where in blazes is the "Querying vs. importing" coming from?!?!??! From the very first post QuoteI want to pump the data of my old database into a new one, with similar tables and similar fields.There. It's about Importing/Migrating from one Database to another. If the Import/Migration is successful, the "old" Database will become obsolete AfterNote: An "Import"/"Migration" should be planned very carefully, especially the ORDER in which you import/migrate the tables (Remember: Master-Table before Child-Table) And an Import/migration is 99.9999% of times a "table-wise" import/migration. No Joins or whatever. Pure table-data ---------------------------------------------------------------------- Nicole · 2024-01-29 10:34 I better confess: What a master / child table means, - I do not know this. In the meanwhile I migrated 5 tables and it seems to work fine. Frame Robin shows arrived data. If not, this will not be the problem. As I have written Lazarus code for every single table, I can delete the new table and it will be re- written by my code. On migration I took care to write the tables which hold my joins are written before that ones, which need them. Is this what is "child table"? Or is this something else? ---------------------------------------------------------------------- rvk · 2024-01-29 10:47 Quote from: Nicole on January 29, 2024, 11:34:36 amWhat a master / child table means, - I do not know this.Master-child tables are tables with one-to-many relationship. For example... you have one client with multiple orders. You create a record in one table (the table clients). And for every new order for a client you create a record in the orders table with a foreign key pointing to the client. The clients table is master (one) and the orders table is child (many). You probably already use such table but just didn't know the terminology ---------------------------------------------------------------------- Zvoni · 2024-01-29 10:59 Quote from: Nicole on January 29, 2024, 11:34:36 amI better confess: What a master / child table means, - I do not know this. In the meanwhile I migrated 5 tables and it seems to work fine. Frame Robin shows arrived data. If not, this will not be the problem. As I have written Lazarus code for every single table, I can delete the new table and it will be re- written by my code. On migration I took care to write the tables which hold my joins are written before that ones, which need them. Is this what is "child table"? Or is this something else? In a nutshell: Each table, that has a Foreign Key to somewhere else is a Child-Table, if a Table doesn't have a Foreign Key to anywhere, it's a Master-table ---------------------------------------------------------------------- rvk · 2024-01-29 11:11 Quote from: Zvoni on January 29, 2024, 11:59:12 amIn a nutshell: Each table, that has a Foreign Key to somewhere else is a Child-Table, if a Table doesn't have a Foreign Key to anywhere, it's a Master-table ???? My RELATIE (client) table has a Foreign key to BRANCHE, RAYON, COUNTRY and lots of other places. And RELATIE is definitely a master table because the V_DOCUMENT (with orders etc) has a foreign key pointing back to RELATIE. So it depends on the relationship between two tables if you can call one a master and the other a child. But that doesn't mean it's the case for ALL relationships. So a table can be a child AND master table at the same time (depending on which relationship you look at) And in the case of my RELATIE pointing to a BRANCHE... that also not considered master/child because one RELATIE points to just one BRANCHE. The Master (=1) to Child (=many) seems a much better description ---------------------------------------------------------------------- Zvoni · 2024-01-29 11:33 Quote from: rvk on January 29, 2024, 12:11:46 pmQuote from: Zvoni on January 29, 2024, 11:59:12 amIn a nutshell: Each table, that has a Foreign Key to somewhere else is a Child-Table, if a Table doesn't have a Foreign Key to anywhere, it's a Master-table ???? My RELATIE (client) table has a Foreign key to BRANCHE, RAYON, COUNTRY and lots of other places. And RELATIE is definitely a master table because the V_DOCUMENT (with orders etc) has a foreign key pointing back to RELATIE. So it depends on the relationship between two tables if you can call one a master and the other a child. But that doesn't mean it's the case for ALL relationships. So a table can be a child AND master table at the same time (depending on which relationship you look at) And in the case of my RELATIE pointing to a BRANCHE... that also not considered master/child because one RELATIE points to just one BRANCHE. The Master (=1) to Child (=many) seems a much better description Not exactly my train of thought. I was presupposing the "import/migration" of tables. If a table has a Foreign Key, then it definitely is a Child-Table. Period! Yes, a Child-Table can be a Master-table again, for some other tables, but for "import/migration" you can't (or better said: shouldn't) import/migrate it, BEFORE its master has been imported/migrated. That's what i meant, that the ORDER in which you import/migrate tables is important Especially, if you have a redesign of your tables, like in these discussions here about surrogate primary keys/foreign keys. e.g. the switch from "natural" PK/FK to surrogates, there is mechanism for migration you can use, without having to go through all child- records assigning the correct surrogate PK from the master-table. Of course provided, that the old "natural" keys still work. I'll give you a hint: Import the old MASTER-table into a new one, which does have a Auto-ID Primary Integer Key. The Auto-ID gets populated like you're used to. Note: You have to take the old PrimaryKey with you into the new table, BUT NOT as a PK. Just as a "regular" Field Now the fun starts: You import the Child-Table, but with a Join to the new master-table, but joining on the old keys, but assigning the new FK as a result from that JOIN Aircode Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO "NewChildTable" (NewField1, NewField2, NewForeignKey)SELECT O.OldField1, O.OldField2, N.NewMasterPrimaryKeyFROM OldChildTable AS O INNER JOIN NewMasterTable AS N ON O.OldForeignKey=N.OldMasterPrimaryKey After everything is done (successfully i hope) you could even drop the old Keys (Yes i know, involves rewriting the queries, but you should do that in any case because of use of indices) EDIT2: I forgot: I presupposed in my approach, that no "orphan"-Child-Records are allowed. Remember: I can declare a Foreign Key to a PK in another table, but still allow NULL for the Foreign Key (Child-Record without parent --> "Orphan") If "Orphans" are allowed (NULL as Value in FK) then the just change the INNER JOIN to a LEFT JOIN EDIT3: I just thought of a "special" case: a Table which has a Foreign Key pointing to its own Primary Key, incl. ON UPDATE and ON DELETE Clauses, especially the CASCADE. Of course, here NULLs must be allowed for the ForeignKey. Now, this is some real fun, because in that case you also have to consider the order of the records themselves ---------------------------------------------------------------------- Nicole · 2024-01-29 16:41 Thank you for the explanation! This is really hard core to me. At least, I have understood, what means "child table". Yes, I just generated the table with newly born primary key just by inserting their values. The primary key was written fine. Luckily I learnt in the last 10 years at least, how to write generators and triggers. This is the first time all my tables have one system of generators. Some recent tables had some strange work-arounds. You do not want to see it, believe me. Not even I want it.  :'( The most dangerous thing was, that there are grand-child-tables and combinations of foreign keys. So it is crucial, that the primary keys are the same in certain tables. As far as I HOPE, all the migrated tables have migrated these very key-kombinations as well. If not, - I have lost all my data, because the grandchildren's data would make no sense any more. Let's cross fingers. By sure I will only know it, if by code is able to read the new tables. I expect to have lost really much code of Lazarus. This is why I messed on with my old database that long. I will make a text-file of your posting and put it into my firebird- directory. ---------------------------------------------------------------------- jcmontherock · 2024-01-29 20:52 I'm using the following script with MySQL. Maybe it works with Firebird with some changes... Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i set @old_db := 'DBin';set @new_db := 'DBout';select concat('create database if not exists ', @new_db, ";")select concat('drop table if exists ', @new_db, '.', table_name, ";"),       concat('create table ', @new_db, '.', table_name, ' like ', @old_db, '.', table_name, ";"),       concat('alter  table ', @new_db, '.', table_name, ' disable keys', ";"),       concat('insert ', @new_db, '.', table_name, ' select * from ', @old_db, '.', table_name, ";"),       concat('alter  table ', @new_db, '.', table_name, ' enable keys', ";")from information_schema.tables where table_schema like @old_db; ---------------------------------------------------------------------- Nicole · 2024-01-30 13:49 * saved as text and filed * Thx! ---------------------------------------------------------------------- Zvoni · 2024-01-30 13:56 Quote from: jcmontherock on January 29, 2024, 09:52:10 pmI'm using the following script with MySQL. Maybe it works with Firebird with some changes... Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i set @old_db := 'DBin';set @new_db := 'DBout';select concat('create database if not exists ', @new_db, ";")select concat('drop table if exists ', @new_db, '.', table_name, ";"),       concat('create table ', @new_db, '.', table_name, ' like ', @old_db, '.', table_name, ";"),       concat('alter  table ', @new_db, '.', table_name, ' disable keys', ";"),       concat('insert ', @new_db, '.', table_name, ' select * from ', @old_db, '.', table_name, ";"),       concat('alter  table ', @new_db, '.', table_name, ' enable keys', ";")from information_schema.tables where table_schema like @old_db;   Note: This will only work in Firebird-Server-Mode, not as embedded/filebased, and then only if both Databases run on the same Server Specific SQL-Dialect not withstanding ---------------------------------------------------------------------- rvk · 2024-01-30 14:04 Quote from: Zvoni on January 30, 2024, 02:56:50 pmNote: This will only work in Firebird-Server-Mode, not as embedded/filebased, and then only if both Databases run on the same Server Specific SQL-Dialect not withstanding Even Firebird-Server-Mode doesn't support dot-notation like that to access other databases. Explained earlier in this topic (or the other topic, I can't keep track anymore with all those topics about the same subject). Edit: yes, here https://forum.lazarus.freepascal.org/index.php/topic,66021.msg504127.h tml#msg504127 You can use EXECUTE block to access other database but not the dot- notation as shown. ---------------------------------------------------------------------- Zvoni · 2024-01-30 14:22 Quote from: rvk on January 30, 2024, 03:04:17 pmEven Firebird-Server- Mode doesn't support dot-notation like that to access other databases. Explained earlier in this topic (or the other topic, I can't keep track anymore with all those topics about the same subject). Edit: yes, here https://forum.lazarus.freepascal.org/index.php/topic,66021.msg504127.h tml#msg504127 You can use EXECUTE block to access other database but not the dot- notation as shown. Blame my lack of FireBird-knowledge/experience