MV to/from RDBMS – Not as tough as it sounds

TODO list for moving data between MV and a RDBMS

1) You of course know MV and you know your own accounts and files. You need knowledge of the equal and opposite side of the equation – or you need someone else with that knowledge. Someone must know something about the database platform as well as someone who knows the specific tables and fields available.

If you need to educate someone about what MV is, here is a quick intro for someone familiar with a RDBMS.

You cannot be successful here if your requirement for performing an operation is that you should not need to know anything about the other environment. You can do this today if you put aside notions of magic or letting some vendor create a drag-n-drop utility for you. You can do this all now and for free, but you must have someone who knows SQL and the RDBMS structures.

You are successful with this task if someone knows what commands to execute against the specific target RDBMS and has some idea of the expected results.

2) On the OS hosting your MV DBMS, find out how to query your RDBMS. Research in mainstream forums or Google with searches like “windows query mysql” or “commandline query sql”. You will need a driver and a utility for doing the command-line queries. If you are working with a local MySQL database then you may just need to type ‘mysql’ at sh/bash/c: and you’re there.

There are two kinds of “command-line” utilities. There is the kind where you type something like “mysql” at the shell/cmd prompt, and from there you do everything within a little subshell. This is standard for MV and other environments. The other kind is where you enter a full command but remain at the shell/cmd prompt after that command is executed – you don’t go into a program and stay there until you get out. You need the second version here. You need to be able to enter a command and capture the results, perhaps with redirection or pipes to route output away from stdout and to a file.

You are successful with this task if you can actually execute commands against the RDBMS, get immediate results or pipe results to a file, and you return to your shell/cmd prompt when done.

3) Find out how to get results back in a format that you’d prefer. Again research in mainstream forums or Google with searches like “mysql query results CSV” or “sql resultset xml”. You really can parse the standard results that come back from a query, but parsing plain text is subject to error which is largely eliminated with structured output.

4) Practice making CRUD queries to the target system with static/hard-coded data. Find out what happens when you Create with the wrong permissions, Read a record that doesn’t exist, Update with the wrong data type, or Delete a record that has a relation to some other table. What is the response when you do an Update? How are responses different when you get zero records selected, one record, or multiple records? Then make sure that you understand the data that comes back when you request CSV, XML, or some other formatting.

5) With your new mastery of the command line, employ your MV BASIC skills to write code that crafts the same statements you tried above. Use EXECUTE “!”:CMD CAPTURING OUT to execute those commands. The result in your OUT variable should be exactly the same as above, just delimited by attribute marks.

6) Now make the data more dynamic. Use a variable for an ID and pass that into a WHERE KEY = “key” clause. To perform an Insert or Update, read a MV record with common OPEN/READ statements, format specific fields as expected by the RDBMS using masks like MR2 or D4/, substitute the dynamic values into the static queries that you created above.

When you get results back from a SELECT, parse the fields from OUT. You’re going to have one or more records consisting of one or more fields. Loop through the results to create new records that make sense in your application.

That’s all there is to it. With that information you should be able to perform all read/write operations you need between MV and any RDBMS, whether it’s local or remote.

Continued…