Enterprise Information Systems on Mac OS X
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
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
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
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
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
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
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
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:
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
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
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
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
OK, we've looked around the facilities a bit. Let's get down to converting that 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
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
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
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.
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:
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 email@example.com.