Volume Number: 21 (2005)
Issue Number: 3
Column Tag: Enterprise Database Technology

Enterprise Information Systems on Mac OS X

by David Swain

Confronting the Myths and Challenges of the Enterprise World

There was great anticipation in the capital city of the Land of Smiles! Among the young, who had never seen other times, there was talk of a new Golden Age coming where their new data engines would once again help their land become a world power in commerce and an even mightier force in the arts. They were rightfully proud of the achievements of their land's techno-artists and of the world's renewed interest in their advanced electronic creations.

But those who were older and who did remember the earlier Golden Age viewed the coming boom in commerce with both joy and dread. Yes, it was possible that a much larger share of the world's wealth of information would now rely on their people and technology for safekeeping and secure access. And it was possible that great economic rewards would enrich their fair land as a result of this newfound global respect. But it was also possible that a few key mistakes made at just the wrong moment and in just the wrong place could bring disaster where there should have been success. This could build an even greater barrier between the Land of Smiles and the outside world, forever closing them off from the great spiritual and physical treasures that could have been theirs. These elders remembered how the Land of Smiles really fell out of favor with the business world years ago...

Myth vs Reality

There are a number of myths that circulate through the enterprise world and shape the behavior and decisions of those who work in that environment. Myths are very powerful when enough people believe them. They are cautionary tales intended to keep the inexperienced from harm. But if they are still believed by those who have otherwise reached maturity, they can impede progress and stifle growth.

As with any myth, enterprise information technology myths may very well have some basis in fact or in history. But they are more often misinterpretations, sometimes deliberate, of facts or events that are fueled by fear of the unknown and fanned by people with something to sell. Even if the basis of a myth was true at some historical time, it may no longer be true. Continuing to foster such a myth could become a barrier to progress.

Such is our first myth...

Myth #1 - There Are No Enterprise Database Tools for Macintosh

Not all in the Land of Smiles had abandoned the rituals of data management with the native technology in those years. But those who persisted in this practice often did so in secret, fearing ridicule or banishment. Many sought refuge in other lands in order to ply their craft - and many of those became swallowed up in the foreign culture that had become their home. Some even began to believe the vicious rumors they heard from their neighbors about the Land of Smiles, or at least they nodded in assent in order to blend in. But most longed in private for the day they could return to their beloved land and freely pursue their chosen trade...

Of the widely held beliefs in the enterprise database market, one of the most prevalent categories of belief regards the use of Apple products and Mac-based software for "serious" work. Basically put, the core belief is that "there are no enterprise-level database application development tools" built for this platform. With the growing number of powerful Apple server products being purchased, with the return of more "visible" database products like Oracle to the Macintosh platform, and with a little education, this misperception can change.

But to facilitate this change, many of us will have to learn some new skills as well. And we must also learn to use the right tools to do the best job. If we choose the simpler tools with which we are familiar in the small business market to attempt to build mission-critical applications for the enterprise market, we may find ourselves becoming the source of new myths that the Macintosh platform is not suitable for professional database work. It is not a matter of whether we can use a certain tool, but whether we should use that tool when there are better tools available.

Years ago there were many small business systems built using software that ran on the Macintosh operating system - some better than others. It was a simpler time when the word "gigabyte" had never been uttered in polite conversation and the tools of the day on any platform were much less sophisticated (or capable) than they are now. Some database companies that remained with the Macintosh platform since that time have made great progress. And the increasing acceptance of Mac OS X Server and Apple server and mass storage products should bring more of the major database server companies back as well. But how well equipped for enterprise database work is Mac OS X even now?

The State of the Platform

It is not well known in enterprise database circles, but Mac OS X has never been without powerful enterprise-level tools for information management. While the coming of Oracle has brought much-deserved attention to Mac OS X and Mac OS X Server as database client and server platforms, equally capable products have been with us since the beginning. The very day Mac OS X officially shipped (Saturday, March 24, 2001), an excellent SQL database product named FrontBase officially shipped its new Mac OS X version, joining successful versions already running on Linux and Unix systems. Another fine SQL database named OpenBase also shipped for OS X at that time, having also spent the long OS X beta period honing its abilities. (We are passing over the MySQL that came installed with OS X because it was still missing some important pieces for enterprise work.) More such products have come to the party since then.

The day before OS X shipped, a database client application development tool named Omnis Studio shipped its first version designed to run on Mac OS X, filling out a product line that already ran on Classic Mac, Windows, Linux and Sun Solaris. It was already capable of creating client applications on Mac OS X for database servers running on other platforms, but from the beginning it was also able to connect with FrontBase as part of a joint effort between the two companies.

The programmers at FrontBase had created a link (called a Data Access Module, or DAM) that allowed Omnis Studio to serve as a "front end" for their product using the native syntax of FrontBase. The creators of Omnis Studio had developed this DAM technology over many years and that program shipped with a number of DAMs for other database engines. But they also make the APIs for this technology available to SQL database companies with legitimate needs so that those companies can build an Omnis Studio DAM for their own product. Doing so in essence gave FrontBase an excellent and full-featured GUI development tool for both desktop and web browser-based applications without having to build one themselves. The good folks at OpenBase followed suit by publishing their own native DAM for Omnis Studio a few months later.

The beauty of the DAM technology is that it allows Omnis Studio to speak the native SQL dialect of each database engine while maintaining a single code base in an Omnis Studio application, in essence becoming a universal translator among all the SQL engines for which an Omnis Studio DAM exists. But is this really necessary? Isn't SQL already a standard and universal language?

Myth #2 - SQL is a Standard Language

The world had become a confusing place in that dark age. Only people could make such simple things so complicated.

Everyone in the whole world spoke the same language - but not exactly. They all used the same words, but many of the most important words often had vastly different meanings from one group to the next. Words that speak of love in one society elicit great offence in others.

No one knew how this came about, but everyone agreed on one thing: Their own group's use of language followed the one true and correct tradition. All others were vulgar dialects, unfit for civil discourse and indicating a lower intellectual capacity on the part of the speaker.

No fence is as high as the one built by the mind...

SQL a universal language? Almost true... But in practical terms, SQL is a model for a database language based on a standard. Its implementation in actual database products varies widely. We cannot go out and purchase "SQL". If all SQL databases were exactly alike, there would be no reason to have more than one of them. Look at the marketing spin of a few products:

FrontBase prides itself in strictly adhering to the SQL/92 standard, for example, to the point where they even suggest that programmers purchase C. J. Date's A Guide to the SQL Standard as a reference manual to their product. But even FrontBase adds extensions to that standard for practical reasons. OpenBase published a 14-page white paper on how their product complies better with a variety of data integrity and security standards "which every database should meet, but few do" to distinguish themselves. And Oracle is, well, Oracle! ... to the point where everyone else mentions in their own campaigns how they are equal to or better than Oracle at something important, but still much more affordable.

Why emphasize this here? The point is that there are significant differences among SQL database products. On many scales, neither is better or worse than the other. Each choice has its trade-offs. Switching from one to another is where their differences really make a difference to us. Two actors can work from the same script in entirely different ways and both create equally brilliant performances. But problems would arise if we suddenly had to swap in, say, Hoffman for Pacino after all the shooting on a movie had been done (just because new management was better friends with Hoffman's agent). It doesn't matter that the script was the same for each performance; the interpretation of the script is the reality we must deal with.

Fortunately, in an enterprise database application we don't necessarily have to rewrite the whole thing just because a decision is made to change database engines. This is because of the way such applications are structured.

Division of Labor

If you have only worked with file sharing or self-contained database products, then you may not be fully aware of the division of labor in a client/server system. Briefly, there are two parts to a client/server database application: the Database and the Application. While this may not seem to be a blinding flash of brilliance or a startling revelation, it is a distinction that is often blurred in practice - to the detriment of the finished system. Here is how these two parts could be defined:

• The Database part protects and serves the information for the system. This is also called the back end of the application. It is made up of the SQL server engine and data repository that reside on a database server machine (or cluster). The SQL server sits on this machine and responds to requests for accessing the various databases it manages. Data access requests include select, insert, update and delete commands, but there are also requests for changing the structure of a database and for managing user access to a database or specific parts of it.
• The Application part includes programs running on client machines elsewhere on the network that provide the GUI interface for operations such as presenting data, accepting data entry and generating reports. This is also called the front end of the application. The Application sends requests to the Database and receives responses from it, but it also may perform other tasks and may interact with other services (such as email) and devices (such as printers).

Of course, opinions vary as to where the boundaries of these two domains should really be placed, but the division is well recognized. Some database publishers actively encourage including functional bits of application code directly in the database, partly as a means of discouraging shifting to another database in the future. Developers at some "stable" installations (where they are fairly certain management will not change databases on them) find that this practice has some performance benefits. But many application developers who must serve users of various SQL engines prefer to put as much functionality into the Application side as possible for easier portability.

Client and server programs will generally reside on different machines (except possibly for web-based application servers) - and those machines can even be running different operating systems. As long as a communication bridge can be established, it doesn't matter where the Database and Application are relative to one another. But for our purposes in this series of articles, we will focus on 100% Mac OS X solutions, with both client and server machines running on some kind of modern Macintosh machine.

The issue of management's effect on database decision-making has been broached. Let's look at this a bit more closely.

Myth #3 - Our Company Has Standardized On XYZ

Data artists were more often servants than masters. Theirs was an obscure, secretive trade with many mystical words of power to be mastered. But they had protected their craft so well that true skill was rare, so many of the biggest businesses succumbed to the temptation of out-sourcing to other lands where intellectual labor was cheaper. This, in turn, had often led to less than satisfactory results, but managers are not programmers and their expertise is in budgets rather than in bits and bytes...

And managers were also servants of even more powerful masters. When these masters were displeased, it was the managers who bore their wrath. This was both a blessing and a curse for the data artist...

For the past many years now, the enterprise IT sphere has been in flux, with management turnovers happening frequently. Changing management often signals changing database platform. Typically, when a new VP of IT enters office, an impression must be made that immediate progress arrived with the change in management. A common tactic for making a strong first impression is to change the database platform and/or operating system on which that company is "standardized". The rationale for such changes is most often that the new platform is "more powerful" or "more standard" or even "less expensive in the long term" or arguments along those lines, but the real reason is as likely to be that the new VP of IT (and the upper management staff that arrived on those coattails) is simply more familiar with that product or has some relationship with that company.

Sometimes it doesn't even require a change in management to shake things up. Service contracts come up for renewal periodically and computers bought in a bundle come to "end of life" and "need" to be replaced in a single, massive move. Negotiations over price of updating can be enough to cause a change of "standards".

None of this is necessarily bad, as this tactic may bring more corporations to Mac OS X sooner. And being in bed with a major database supplier - at least for the span of a few years - may actually have benefits for a corporation. But however it occurs, such a change causes an immediate major headache for the staff in the trenches, who must actually work with the new hardware and/or software and solve the problems brought about by management's change of heart. What problems? The problem that both the data and the application from the current system must be converted to the new environment.

Why is this a problem? Well, SQL comes in as many flavors as there are vendors with SQL databases as we have already seen. Also, different operating systems require different software - and some database products are only available for certain platforms. If management is fully committed to a proposed change in standards (that is, if they insist that there be no exceptions), then a number of changes could cascade from a single decision.

For example, a company that had been running MS SQL Server and that now intends to move to an all Macintosh network must also decide on a new database vendor, since Microsoft does not make a Mac OS X version of that product. Sure, this company could keep a lonely outcast Windows machine on the network and continue to use it as a database server, but that goes against their new "standard". If the Application is also written using some tool that does not exist for Mac OS X (like Visual Basic), then that has to be entirely rebuilt as well. Many believe that job security is a wonderful thing, but the unreasonably short timetables that sometimes come with such turnovers could even shake the foundations of that belief in the short term.

A parallel problem confronts those who would like to develop vertical market applications for selling into some segment of the enterprise market. Many corporations are adamant about only using their (current) "standard" database platform - and this "standard" varies from one company to another (as well as from year to year in some cases). This makes the developer's job more difficult if their code needs to be built for each specific back end. Supporting multiple code bases is a thankless task and is a real pain when it comes to providing bug fixes and upgrades!

I hear someone in the back mumbling something about ODBC. True, many database products can be accessed using ODBC (acronym for Open DataBase Connectivity), but this does not yield the same kind of performance and access to security features as does using the native dialect of each specific database product. ODBC is merely a technology, like SQL, that was originally intended to give applications (such as spreadsheet and chart generation programs) that live outside the database application access to that data for specialized tasks. Such access would generally only be one way - reading data but not updating it. Just like Omnis Studio DAMs, ODBC drivers must still be written for each database platform we need to access.

Certainly ODBC is the native dialect of MS SQL Server (after all, ODBC began with Microsoft) and a few other products, but it does not by itself give access to important (and sometimes proprietary) internal features of specific SQL products. ODBC compliance is still an important feature for applications that need to query SQL databases, but it is not part of the SQL standard.

JDBC (the Java cousin of ODBC from Sun) is a Java API for connecting to SQL databases and "other tabular data sources". It has similar intent and limitations to those of ODBC, but offers its API to the Java language rather than to variants of C.

But what if we want a complete database application development platform - one that can take advantage of built-in and proprietary features for a variety of SQL products, as well as the more common and mundane tasks, as well as provide a rich GUI interface all within a single code base? What if we need to be prepared for occasional, or even frequent, transfers of data between different SQL products? Where might we look for a programming platform that can serve these needs?

Omnis Studio to the Rescue

When she was younger, in the previous age, she was very serious about protecting and retrieving data and about interacting with the largest and most powerful engines - even the foreign ones. But she did not smile well. This did not bring her favor in the Land of Smiles, so when others appeared who had far better smiles, her countrymen turned their backs on her to follow them. But too late they learned that smiles are not all that is important in this work and so many projects failed that the Land of Smiles had to bear the shame for many years.

She had spent the last age in the shadows, gaining knowledge and power, always seeking to fulfill her purpose in facilitating data access. She journeyed to the other nations - the Land of the Sun, the Land of the Penguin and the Land of a Thousand Flags - and had dwelt among their data masters and had learned the special ways of each. She speaks the languages of all of their data engines as though she were a native... and she has learned the proper smile for each land - even of the land where smiles are rare.

Now the land of her birth is again ready for greatness in the information world and she is ready to lead the transformation! She is Omnis, the communicator, the facilitator, the guardian of the truth of the data - and the friendly face on the complexities of enterprise...

The database conversion problem outlined above could prove to be a daunting challenge if it were not for tools like those found in Omnis Studio. Its SQL Browser facility makes quick work of this otherwise tedious and laborious process. The computer still has to do the work, of course, but the programmer doesn't have to spend a lot of time and effort on it as well. After all, isn't that why we use computers in the first place?

The "secret" is that Omnis Studio uses a specially designed facility called a Data Access Module or DAM to communicate with each database in its own syntax. This facility knows how to properly handle data of various types for each platform and translate each value between its native format and a form that Omnis Studio can use. There are also many SQL features built into objects within Omnis Studio that generate SQL code appropriate for the current database connection - even when the application is simultaneously connected to multiple and dissimilar SQL servers. Omnis Studio provides a common language, a "database Esperanto" if you will, that allows us to program the common SQL operations for an application once and then have them properly translated to the syntax for the SQL product to which an application element is currently connected.

The Omnis family of products has offered SQL database connectivity for Macintosh computers since Omnis 3 Plus version 3.3 published in 1987. This long experience with the needs of enterprise databases is rare on the Macintosh platform. With roots going back to its first incarnation in 1979, Omnis is indeed a mature technology.

Today, Omnis Studio for Mac OS X ships with DAMs for Oracle, Sybase, MySQL (commercial version only at MySQL's request), ODBC (the native DAM for Microsoft's SQL Server, but a general purpose DAM for other databases) and JDBC. FrontBase and OpenBase have also created their own DAMs to allow Omnis Studio to be front ends for them as mentioned earlier. On other platforms, Omnis Studio ships with these DAMs as well as DAMs for DB2 and Informix. There is even a special version of Omnis Studio for SAP that is core certified (meaning that applications developed for SAP using this version of Omnis Studio are automatically certified, saving the developer significant time and currency!). Should any of these database server products choose to appear on Mac OS X in the future, Omnis Studio will be ready for them.

But how does this work to our advantage as programmers and consultants? Let's perform a little experiment to illustrate the power of the DAM...

A Hands-On Experiment

Suppose that we have just been given the task of transferring data from one database product to another. We have existing data being managed in OpenBase and someone somewhere in our company has decided that we will now switch to FrontBase. (In reality, it could just as easily take place the other way around. It just so happens that a number of example databases are installed with OpenBase and not with FrontBase, so we don't have to build or install anything extra to perform our experiment...)

Of course, to perform this experiment and prove to yourself that it works as described in this article, you will need to have the right tools at your disposal. Fortunately, we have chosen software that is available for free (at least for evaluation and/or development purposes), so all you need to do is put in some time and effort to download, install and license them. And it will be worth the effort, because we will use these programs for exercises in future articles as well.

There is no room in this article to guide you through the installation of these software packages, but we can point you in the right direction. The installation process for each product is quite simple (this is all on Mac OS X, after all), so the manufacturers' instructions should see you through. Web addresses for each of these companies are given at the end of this article.

We also provide some additional setup information that you will need once you have installed the software. Again, space is a limited resource in print publications, so we must send you to the Web for this auxiliary information. There we walk you through the process of acquiring, installing and licensing these packages, starting the proper OpenBase database for the exercise in this article, creating a new empty database in FrontBase that will become a clone of the OpenBase database in our experiment and creating session templates in Omnis Studio that generically define access channels to OpenBase and FrontBase, which we will use as a basis for defining more specific channels for the exercises in this and subsequent articles. The notes you'll need are at:

http://www.davidswain.com/mactech0305swinstall.html

But whether you go off and prepare to perform this experiment for yourself or you just read through the example in a comfortable easy chair, I'm certain you'll begin to see the power and simplicity of this impressive tool!

SQL Browser Basics

For this experiment, we only need to use a utility built into Omnis Studio. We don't need to do any actual programming this time, although we will execute a couple of lines of code from an SQL command line to solve some problems we will encounter in the next issue. On the other hand, it is important that you know that the Omnis Studio Integrated Development Environment (IDE) is, in fact, built using the programming features of Omnis Studio itself - so a competent Omnis programmer could build any part of this utility.

The steps and illustrations shown here are for the 3.3.3 version of Omnis Studio. Version 4.x streamlines this and related processes through the use of an integrated browser, so we don't have to open so many windows (like the Mac OS X finder) when drilling down into deeper levels of the process. If you own a copy of Omnis Studio version 4.0.2, you should have no trouble following along.

To summarize what was done in the Web exercises, we have a database named Company running in OpenBase. The user for this database is admin and there is no password. This example database was installed with our copy of OpenBase. We created a new (and, therefore, empty) database with the same name in FrontBase, which automatically began running upon creation. The default user for that database is _system and again there is no password. The OpenBase and FrontBase DAMs are both nestled snugly in their appropriate locations so Omnis Studio has access to them. We are now ready to launch Omnis Studio.

After doing so, we must navigate to the SQL Browser utility. We open the SQL Browser in Omnis Studio 3.x by selecting Tools>SQL Browser... from the main menu bar. The SQL Object Browser window shown here then appears:

Figure 1. Omnis Studio 3.x SQL Object Browser Window

In Omnis Studio 4.x, the SQL Browser is integrated into the same window as the other browsers of the IDE and the window itself conforms to the Mac OS X standard of tools-only, no embedded menu bar. The Browser window opens when we first launch that version of Omnis Studio. But if it closes, we can open it again using View>Browser... from the main menu bar or simply by pressing Command-2. Then enter the SQL Browser by selecting the item with that name from the hierarchical tree list on the left side of the window.

The SQL Browser allows us to poke around in SQL databases for which we know valid usernames and passwords. We can also perform certain utility functions and move structural information between the table definitions in existing databases and Omnis Studio Schema, Table and Query Classes in an application library. All of these are subjects for other times. Right now we need to establish a session to each of our databases using our two DAMs so that we can manually interact with them.

In the online setup exercises, we created generic session templates for each of our SQL platforms. We will now duplicate each of these templates to create session objects that point to specific databases. OpenBase and FrontBase have their own ways of specifying login information, but we can use the appropriate syntax for each product in the fields of the Session Editor window. We get there by choosing Session>Modify Session Templates... from the menu bar embedded in the SQL Object Browser window. This opens the Session Template Manager window. The mechanics of what follows assumes the reader has performed the setup exercises, but the flow of the process should make sense whether or not the exercises have been followed to this point.

First, we'll duplicate the OpenBase generic template and open the duplicate session object. Since it is already set up to use the OpenBase DAM, we only have to specify information about the database we want to access. When logging on to an OpenBase database, we must specify the path to the database using the syntax <databasename>@<hostname>. So we will use Company@localhost here. We also know that the user whose account we want to use is admin and that this user does not have a password. We will also use the name OBCompany for this session. Our form should then look like the following figure:

Figure 2. Session Definition for Company on OpenBase

Now we need to create a similar template to access the Company database running on FrontBase. Besides using a different name for this connection, notice that we need to use a different login syntax for FrontBase. It uses <hostname>/<databasename>, so we must enter localhost/Company. Also, we have a different user (who is still not using a password!), so we must enter this differently as well. Your template should look like this before accepting it:

Figure 3. Session Definition for Company on FrontBase

Now let's close the Session Template Manager and return to the main SQL Browser view so we can actually connect to our databases. In Omnis Studio version 3.3.3, just close the Session Template Manager window. In Omnis Studio 4.x, just click the Back button in the navigation list.

Opening a Channel

Assuming that we specified all the correct information, opening a session to each database is a very simple process. The name of any complete session template will appear in the Open submenu of the Session menu of the SQL Object Browser window when it is selected. We open a session to a specific database by selecting the name we gave to that session template from this submenu.

Figure 4. Opening a Session Using a Session Template

An icon appears in this window for each open session. When both of our sessions are open, the window should look like this:

Figure 5. Open Sessions for FrontBase and OpenBase

Browsing Existing Data and Structures

We can drill down into a session to see more information by simply double-clicking on a session icon. This opens a structure category view.

Figure 6. Structure Category View for Company on OpenBase

If we want to see the tables (and ultimately the data in those tables) in our database, we just double-click on the Tables icon in this view to expose the Tables view.

Figure 7. Tables for Company on OpenBase

Here we see that our Company database contains eleven tables. So far we haven't seen anything that can't be done in the Manager programs for either SQL product. But from here we can do a number of useful things on the Application side of the equation.

For example, we could drag one or all of these tables to our Omnis Studio application library to create equivalent table structures in the application. The DAM used to set up the connection determines which Omnis Studio data type maps properly to each column in the SQL database. These facilities come in really handy for our conversion exercise!

If we prefer to view the tables in the database as a list rather than as an array of icons, we can change to the Details view using the View menu of the window. This applies to any of the browser windows we have already encountered.

Figure 8. Switching View Modes in a Browser Window

We can view either the column structure of the data in a given table. To view this structure, we just double-click on a table icon or the line in our list view for that table.

If we instead want to view the data for that table, we can use the Show Data... item from the context menu for that table.

Figure 9. Show Data for company Table in Company Database

This opens the Interactive SQL window with a pre-built query to select all columns and all records for the selected table.

Figure 10. Interactive SQL Window with All Records for company Table

Of course, if our table contains millions of records, this may not be a good idea. But we can access this window in other ways and type in a more reasonable query for large tables. This is our SQL command line and we can use to for a number of purposes - including repairing data that won't transfer from one database to another.

OK, we've looked around the facilities a bit. Let's get down to converting that data!

Converting Data

We need to drill down to the Tables view level for both databases to perform our transfer from one to the other. Once we have done that, it's simply a matter of drag-and-drop - if all goes well. Follow along closely because we will get into a little bit of trouble once we've traversed the smooth water...

First, we will do an easy one. Place the Tables view windows for our two databases so they can both be clearly seen and drag the company table from OBCompany to FBCompany.

Figure 11. Drag company Table to FBCompany Session

A dialog will appear asking whether we want to transfer both the structure and the data. This is exactly what we want to do, so we just click the OK button or press the Return key to accept.

Figure 12. SQL Transfer Dialog

We are then asked to verify the column structure we wish to create in the new table for the target database. This is our opportunity to make any adjustments. We want an exact duplicate in this case, so we again accept by clicking OK.

Figure 13. Destination Table Structure Verification Dialog

If there are no problems, Omnis Studio first creates a new table in the Company database and then begins to transfer all existing records. A dialog with a progress bar appears, indicating that data is now being transferred.

After the data has been transferred, another dialog appears asking us to verify the indexes for the target table. If we see no problems, we again accept.

Figure 14. Index Verification Dialog

After all this activity (mostly on the part of Omnis Studio), we see our completed table now appears in the Tables view for FBCompany. Go ahead and view the data for this table to verify that it was transferred.

Figure 15. Completed Transfer of company Table

That was certainly easy! But we can do better than this. We can select multiple tables from the source database and transfer them to the target database in a single process. Follow along and give it a try. Perform these steps precisely, though, because I'm avoiding known problems that we will address and solve in the next issue...

Select all the tables in OBCompany from callHistory to products by dragging across them in the list. Then drag the whole lot of them to the FBCompany table window.

Figure 16. Drag Multiple Tables to FBCompany Session

The dialogs detailed above will open for each table in succession. Just accept each one. There should not be any problems with any of them. Notice that the user is still needed to make decisions and accept dialogs along the way, but there are few, if any, difficult decisions to make as long as the data and structure from one database is compatible with the other.

But there can be issues...

Dealing With Conversion Problems

Next time we will tackle a couple of tables with conversion problems and see how Omnis Studio makes short work of them. Try dragging the transactions table from OBCompany to FBCompany and watch what happens. It all begins pleasantly enough, just like the others did. The table structure looks fine and the data begins to transfer and then Bang! An error message appears part way through the data transfer! This is not a major problem, but we've run out of space to solve it this month. (Extra points for the people who solve this before they see the next article - and double extra points if you also solve the issues with the contacts table unaided!)

Conclusion

We have now performed a successful conversion of both table structures and data from one database to another for a number of tables. We did not have to export the data from each table into tab-delimited file, massage that data in spreadsheets to put it into the proper format for import or manually import it into the target database. We just dragged table images from one window to another. If there was a problem, the utility told us about the problem and then rolled back the transaction (structure and data transfers were separate transactions) so that we wouldn't have to deal with the additional headache of partial transfers. The utility also provides tools that allow us to solve what problems do arise - which we will explore in the next article.

Pretty cool!

So what did we learn from all of this?

First, we learned that not all SQL database products are created equally. This does not necessarily make one intrinsically better or worse than another, but it gives enterprise consumers a choice.

Second, we saw some common data conversion problems that can occur in real-life situations and we experienced potential solutions to those problems. We also had a chance to work with a useful utility in a sophisticated application development tool that gave us avenues for solving such problems.

Finally, we found out that there is a lot to learn if we want to be successful in the enterprise environment. There are new concepts and new tools to master in the process. But we learned that our favorite operating system platform is more than ready to enter the enterprise database marketplace. And it can do so proudly and effectively.

Useful Web Sites

You may wish to further explore some of the products and technologies mentioned in this article. Here are web links to sites for a number of companies who create SQL database products and related software:

FrontBase, www.frontbase.com

OpenBase, www.openbase.com

Oracle, www.oracle.com

FirstSQL, www.firstsql.com

MySQL, www.mysql.com

PostgreSql, www.postgresql.org

Omnis Studio, www.omnis.net/mactech

Actual Technologies, www.actualtechnologies.com

Bibliography and References

You may wish to brush up on your SQL skills as well. You may need them for the rest of this article series. Here are a couple of books you may find useful:

Date, C. J. and Darwen, Hugh A Guide to the SQL Standard. 4th edn. Addison-Wesley, 2000.

Taylor, Allen G. SQL for Dummies. 5th edn. IDG Books Worldwide, 2003.

---

Since 1982, David Swain, founder of Polymath Business Systems, has leveraged his diverse background in the physical and social sciences, the business world, and the visual and performing arts to educate IT programmers and managers about the complexities of information management systems and the software used to build them. He is a regularly featured speaker at database application development conferences around the English-speaking world, which also offers him great opportunities for collecting exotic stock footage and still images for use in the video and DVD production classes he offers at his home in Bedford, New Hampshire. You can reach him at dataguru@mac.com.