Insert/Modify/Delete record from another Database dynamically

julkifli33

Hi all,
I have 3 databases : DB01,DB02,DB03
in these 3 databases there are multiple companies.

I tried and successful to import data from DB01->DB02 etc (using ExternalSQL Method)
my question is how are able to use this ExternalSQL dynamically
for example today i want to push data to table Sales Header from DB01 Company A, to DB02 Company X
and then the next day from DB01 Company B to DB03 Company Z

can i do that?
what i know is ExternalSQL is static.

ShaiHulud

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

julkifli33

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

ShaiHulud

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.

julkifli33

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

AlexDen

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.

ShaiHulud

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.