Insert/Modify/Delete record from another Database dynamically

Answers

• Options

  ShaiHulud Member Posts: 228

  2019-07-02 edited 2019-07-02

  Not sure how to switch dynamically between companies, because you'd need to change the table names, but switching between the databases dynamically is doable.
  
  (Note: this paragraph is just guesses at this point - I haven't tested it myself)
  To handle multiple companies, you could create a views in the external databases for each of the tables you want. they'd have the same fields plus an extra field like "CompanyName". And then add a trigger to the view that would insert into a table with a dynamically generated name (SET @SQLQuery = 'INSERT INTO ' + @TableName + '$' + @CompanyName + ' VALUES (<values>);'
  
  To do just the database switching, you need to store the connection information to all the other databases (separate table would be best). Then, have another field on General Ledger Setup that would point to the "active" external database (would be selecting from the database connection information table).
  
  In Codeunit 1's CompanyOpen, or its equivalent codeunit/function on BC, (you probably did this already) you need to register connections to all the databases, and then in the same place, select the default one, based on the new field on G/L Setup:

  ExternalDBConnInfo.SETRANGE(Active, TRUE); //probably should have something like that
  IF ExternalDBConnInfo.FINDSET THEN BEGIN
    REPEAT
      REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, ExternalDBConnInfo.Name, ExternalDBConnInfo."Connection String");
    UNTIL ExternalDBConnInfo.NEXT = 0;
  END;
  SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, GeneralLedgerSetup."Active DB Name"); 
  

  
  Before you insert/modify/delete into external tables, make sure you set the default connection again to make sure that if the setting has been changed, you pick the correct database.

  SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, GeneralLedgerSetup."Active DB Name");
  ExternalTable.TRANSFERFIELDS(InternalTable);
  ExternalTable.CompanyName := GeneralLedgerSetup."Active DB Company Name";
  ExternalTable.INSERT(FALSE);
  

  
  
  Alternatively, you could also try linking the SQL servers, create SQL triggers on database tables that would, based on values of certain fields, contact the correct remote database and correct table, and perform an insert/update/delete

  0
• Options

  julkifli33 Member Posts: 1,073

  2019-07-03

  Hi ShaiHulud ,
  thanks for the reply.
  for old version i used the same way like you told me.
  but i am using ExternalSQL which is not use SQL Syntax anymore.
  for change company i can use CHANGECOMPANY
  but the thing is... for externalsql is consider hardcoded in the table properties.
  what i want is similar like externalsql but with dynamics, we can change the company name for the table

  0
• Options

  ShaiHulud Member Posts: 228

  2019-07-03

  Dumb idea, but what if (depending on the volume of data and how frequent the pushes need to happen)...
  you dynamically modified the entire object - as in, export in text, open text file, read and modify the "ExternalName" property (if necessary), save, import, compile (finsql.exe parameter or poweshell), select the right database, then insert/modify/delete?
  
  Of course, if you need to very often by many users, you'd need to ensure that only one user can do it at a time.
  

  0
• Options

  julkifli33 Member Posts: 1,073

  2019-07-04

  What i mean is... for the external SQL
  can we make ExternalName become dynamic
  currently i am using only for 1 company is ok
  but how about if > 50 companies
  
  

  0
• Options

  AlexDen Member Posts: 85

  2019-07-04

  Hi,
  
  It's not possible, you have to change your approach. You can:
  1) Create view with union of all tables as it was mentioned above.
  2) Use web services to transfer data between DB.
  3) Create ExternalSQL table in destination DB and read data there from your source DB. (If you have only one source DB and company).
  4) Use ADO.net components to transfer data.
  
  

  0
• Options

  ShaiHulud Member Posts: 228

  2019-07-04

  julkifli33 wrote: »

  What i mean is... for the external SQL
  can we make ExternalName become dynamic
  currently i am using only for 1 company is ok
  but how about if > 50 companies
  

  
  I meant that, if the system usage permits it (not a very frequent operation, or users are OK with some wait time), you could modify the entire object code dynamically.

  • Export the object in text (EXPORTOBJECTS)
  • Modify the line that contains the ExternalName property to include your desired company name and save the file
  • 
    
  • >Alternatively, you could have objects prepared for every company<
  • Import the object and compile it (IMPORTOBJECTS plus calling finsql.exe with parameters to compile the object)
  • Set the connection to the right database (if necessary)
  • Insert/Modify/Delete data

  
  Again, not the best solution, but given that this is the specific way you want to go, there aren't any other options I can think of.

  0