MV to/from RDBMS – Not as tough as it sounds
I’ve posted notes here on exchanges between the MVDBMS and Relational databases. I think anyone who needs to do this should be able to take the info here and run with it.
Other posts on this topic include:
MV and Relational Data Exchanges Part 1
MV / Relational exchanges – Part 2
And (spoiler) what I propose below is merely an implementation of the technique I described in my blog MV to anything … sure, why not?.
Most people look to their MV DBMS vendor for solutions to exchange data with other databases. To put this gently, you don’t see requests like that in RDBMS forums. People generally don’t connect from “within” one database to another. They connect into one database, query to retrieve data, then connect into another database and query to post data. That’s what we’ll do here in the first solution proposed. You may not like that idea, but you can begin moving data between systems today if you follow the steps here. Or you can wait a few years and spend a few thousand dollars and you might get the results you want after trying a lot of different products – or you might not.
A good solution to moving data between MV and a RDBMS is simply to shell out to do queries. Sure there is a performance hit. Sure it’s not as elegant as perhaps the sql.cls in QM or OpenDB for D3, but if you start with this technique you can move toward improved elegance and performance later without losing your investment of time here. I’ll get to that later. To start, let’s just make it work.
Whether you do it yourself or you get some product to do your connectivity, you’re going to have a couple prerequisites that cannot be avoided. In order to query a RDBMS you need SQL, and in order to query from one system to another you need a connection.
Sun Tzu said “know yourself, know the enemy, know the terrain”.
Some things never change.