MV and Relational Data Exchanges Part 1

One of the most common inquiries we see in MV forums involves exchanging data between the MV DBMS and relational databases. In this multi-part series over the next couple weeks, I’ll describe ways to get data flowing in both directions without using platform-specific ODBC tools.

This Part 1 is just an introduction and I’ll start describing some details in Part 2. I expect a new segment on this every 4 days or so, maybe with other blog postings in between.

Preface

The details of my solution are based on mv.NET. You may be able to get the whole task done very inexpensively with mv.NET, like a couple hundred dollars or euros. You could use the information here and do it all yourself for free, but you’re still going to have issues finding or creating components that do everything you need. I really want you to ask yourself one question when you’re done reading all of this: Is my do-it-yourself time worth more or less than the cost of an mv.NET license? If your time is worth more, then don’t do it yourself, buy a license and be done with it. Somehow MV people have the attitude that they need to do everything themselves using built-in tools, no matter how long it takes them. I’ve seen people spend months on projects to avoid spending a couple hundred dollars for some tool. Please don’t do this. However you solve your problems, whether it’s with mv.NET or otherwise, or getting consulting services from here or there, always weigh the ultimate costs of time versus tools- including how much it’s costing your company to not have a solution for some weeks or months that it takes to custom-develop solutions.

Task and Requirements

The first problem I want to discuss is how to retrieve data from a remote relational server from MV BASIC. My requirements are quite specific. I don’t want DBMS-specific tools like an ODBC client connector, and I don’t want to use MV-specific SQL syntax which may or may not be SQL-92 compliant.

There are a few reasons for this. Not all relational DBMS platforms speak the same dialect of SQL, any more than all MV platforms have the same syntax for Access / English / Recall queries. MV tools cannot possibly accommodate the nuances to communicate with all relational platforms and their various releases and options. Also, I personally do not like to rely on ODBC client or server interfaces provided by MV DBMS vendors. If it doesn’t work it may take months or years to get a fix and I just can’t tolerate that sort of liability.

My preference is to use mv.NET, which is a DBMS-independent tool, to bridge a connection to ODBC servers using open market client components. This will get the communications and SQL out of the MV environment. Why mv.NET? I’ve answered that question in this blog many times and you just need to go to the mv.NET category to get those answers. In short, it works well, it’s well supported, and it’s very inexpensive.

You could use UO, UO.NET, UOJ, D3Class Library, QMClient, or any of the other "free" tools in the market to help accomplish some of these goals. But let’s face it, you’ve had these tools for years and you’re still wondering how to do this stuff. I’ll describe how to make all of this happen quickly, and I’m just asking you to use tools that I know work well.

Approach is Critical : Separating Components

Before discussing specifics I want to discuss the principal approach that I prefer to use in all forms of communications. This is the separation of the file-level DBMS access from the processes that make use of the data.

And that’s what will be discussed in the next article in this series.

Please feel free to monitor this blog or just this article using the RSS link provided below and at the bottom of every page – that way you’ll know when updates are made. (Information about RSS readers and You can also comment or ask questions in the Nebula R&D Forum and I’ll be happy to discuss any of this at length as I progress with this material.