Multiple SQL/ODBC Databases

February 11, 201015 yr

I have multiple SQL databases, all exactly the same format and structure, that I talk to using FM9 scripts, standard relationships, and ODBC drivers. I only need to talk to one SQL database at a time. At this time, each database has it's own ODBC DNS and it's own relationship hook through a shadow table.

Is there a way I can dynamically allocate, connect to, and talk to any one of my external databases. I only need to communicate with one database at a time. I also do not want to impose any restriction at all on the number of SQL databases I can access.

I don't mind having multiple instances of DB shadow files, each with a different ODBC DNS, that I open and close to access. I would not like to have multiple relationship instances. If I could tie in the relationship stuff dynamically that would be great.

Any suggestions or recommendations how to approach this problem would be appreciated.

February 11, 201015 yr

Not really. A shadow table can only be accessed through a table occurrence much like normal FileMaker tables. You will need at least 1 ( and corresponding layout ) for each table / view you want to access.

February 12, 201015 yr

Author

Thanks John,

I am testing a configuration that has N instances of the SQL shadow table files, all related to a FM file/table structure that in itself is a shadow buffer of the data we want to exchange. In effect I have an exchange buffer sitting between both sides, the main FM and the SQL.

Since I only need one SQL table at time, and I do not need real time response, I am only allowing one SQL sided file to be open at a time. I can post the date to the exchange buffer, and then post from the exchange buffer to the SQL instance that is open. It is a two step interchange but that is perfectly fine in this application.

Each of the N SQL shadow file/tables instances is mapped to the correct DB via ODBC DNS setup and also related to the exchange buffer.

I am constructing the test environment to check out the key elements of the design and to learn a bit more about the proper approach. I am sure I will have more questions.

Thanks,

Joe