TweetFollow Us on Twitter

Enterprise Information Systems on Mac OS X, Part II

Volume Number: 21 (2005)
Issue Number: 5
Column Tag: Programming

Enterprise Information Systems on Mac OS X, Part II

Confronting the Myths and Challenges of the Enterprise World

by David Swain

The Saga Continues...

It is another peaceful day in the Land of Smiles. The weather is beautiful, the people are happy and data is flowing smoothly. It is one of those days that seem just too good to be true.

But as with many outwardly pleasant things, there can be trouble lurking just beneath the calm surface. Every day has its problems and challenges in spite of the beauty and apparent harmony we see around us. But that is life - and we only truly grow by facing new challenges, developing new skills and solving new problems. Exhilaration is the state we achieve for each such cycle we complete. From that peak of personal triumph we can see new possibilities spread out before us - until we are forced to focus again on the next struggle...

In our last episode, we saw how easily table structures and associated data can be converted from one SQL database platform to another using some of the powerful features of the Omnis Studio SQL Browser utility. All went smoothly and without incident when the database tables used simple and non-controversial data types and otherwise followed all the rules. In fact, most of the dialog windows that appeared to us during the process may have seemed superfluous and felt like impediments to our progress. But there can just as easily be cases where these dialogs prove very helpful...

The series of dialogs mentioned above opens for each table in succession when we attempt to translate multiple tables in a single operation. If there are no problems presented and the default behavior is what we want, we just accept each one with a simple Return keystroke or mouse click. There were no problems with any of the tables we chose to translate last time because the data and structures from one database were compatible with the other. We were lucky - this will not always be the case! But before we continue, a little housekeeping...

If you are just joining us, this project is a continuation from the one we began in the last issue. In fact, we also covered some important background information on the availability of enterprise-level databases and application development tools for Mac OS X - including how to download, install and license the ones used in these exercises for free! Do whatever you can to obtain the March 2005 issue of MacTech and work through the example exercise found in the previous article. You'll be happy you did!

And if you were with us last time, we apologize for a slight oversight. There was to have been a sidebar or preamble accompanying the article explaining that one of the application vendors had made available a special demonstration version of their top-of-the-line product (and a significant discount on the purchase of a full copy as well!) exclusively for MacTech readers. In order to take advantage of this offer (which has been extended through July 2005), an applicant needs to supply a special magazine code that was to be included in that sidebar. That code is MT002. To learn more about that offer, go to the MacTech Readers page of the Omnis web site at You will also encounter this code and a link to the associated URL by accessing the instructions for downloading, licensing and installing FrontBase, OpenBase and Omnis Studio demo versions for the previous article in this series found at .

Dealing With Conversion Problems

In the last article in this series, we made great progress in translating a database named Company from OpenBase to FrontBase by a simple drag-and-drop process using the SQL Browser facility of Omnis Studio. We translated first a single table and then a group of tables and the process was completed without a hitch. That was too easy! But we left two tables for another time because they might have some problems. The rest of this article assumes you have read through (or, better, worked through) the exercise in the previous article in MacTech, March 2005.

So let's tackle a table with a conversion problem! Try dragging the transactions table from OBCompany to FBCompany. 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!

Figure 1. Improper Date Value in Record 4

We can't move these dialogs, but if we look in the shadow of the Error dialog, we see that the error occurred when processing record number 4 out of the 29 records in this table. This table isn't very large, so we can use the Interactive SQL window to look at record number 4. To do this, we dismiss the error dialog and then use the Show Data... context menu item on the transactions table by control-clicking (or right-clicking using a multi-button mouse) on the icon for that table in the OBCOMPANY_Tables window.

We first notice that there are two Date columns named card_expiration and transactionDate. Both of these appear to be empty in record number 4 (as does most of the rest of the record), so we must think back about what we know regarding our three products.

Experience tells us that FrontBase is a bit fussy about certain things, adhering closely to standards as it does. One of those things we remember is that it does not like to receive empty values in Date columns. Standard SQL does not recognize an "empty" date value, but expects to see a NULL value if no date value is given. Because it strictly adheres to SQL standards, FrontBase prefers either a valid date value or a NULL value. OpenBase isn't so fussy, so perhaps an empty value or two sneaked into this record. One way to find out is to set the values of these two columns in this record to NULL and see whether that fixes the transfer problem.

We need to do this with a specific SQL statement. This statement not only needs to set the value of these two columns to NULL, but it needs to do so for only this one record. If we don't qualify this, the two columns will be set to NULL for all records in the table, wiping out valid values in other records in the source data. So we need to find a way to distinguish this record. Most programmers would put a unique id column of some sort into a table, but there doesn't appear to be one here. (OpenBase maintains an internal column named _rowid, but this is genereated by the system and is not part of the data per se - so it is not available for export.) Most of the columns in this record are, in fact, empty so there aren't too many choices for selection criteria. Fortunately, the value of 572 in the credit column appears to be unique within our 29 rows. While we can safely assume that this column was not intended as a key column, we formulate an SQL statement in this specific case to perform our correction as follows:

update transactions set card_expiration = NULL, transactionDate = NULL where credit = 572

The Interactive SQL window we used to view the data in this table also allows us to make such a correction. We simply type in the SQL statement we want to use and then execute it by clicking on the Run button (the one with the running person icon) in the toolbar of the window:

Figure 2. Statement to Replace Empty Date Values in Record 4 With NULLs

We won't notice any change in the record image because both empty and NULL values are represented the same way in this window (which has changed in more recent versions of Omnis Studio), but no error message was triggered by the execution of this code, so we can assume that the value assignments were successful. After we have made this correction, we can then close this window and return to the Tables view windows to see whether our transfer can now be accomplished. But when the SQL Transfer dialog appears this time, we want to de-select copying the structure because that happened successfully the first time. If we leave that checked, a dialog will appear telling us that a table named transactions already exists in the target database and prompting us for a new table name. Since we do want to use the existing transactions table in FBCompany, we uncheck Structure in the dialog before accepting.

Figure 3. Data Only Transfer

Once this is done, the data transfer runs with no problems, so our guess that empty Date values were the cause of the problem was correct. Now let's try the last table.

Drag the contacts table from OBCompany to FBCompany. Omnis Studio asks us to verify the column structure of the table as always, but after we accept this, it comes back with an error:

Figure 4. Error When Trying to Create contacts Table in FBCompany

Something is wrong with the data structure we want to impose on our FrontBase database. No other information is given, though, so we have to work this out ourselves. (The error dialog, which comes from the FrontBase DAM, reports "no error" - so we're on our own!) There are 90 columns in this table, so let's hope it's something simple!

Rather than waste your time trying to find a really obscure problem, here is the answer: The person who set up the table in OpenBase used a FrontBase reserved word (actually, an SQL reserved word) for the name of a column. The name of this column is group. Again we have a difference in cultures. FrontBase is within its rights to impose the restriction that reserved words should not be used for other purposes. The SQL standard clearly lists GROUP as a reserved key word. On the other hand, OpenBase is within its rights to ignore this restriction in that standard as long as doing so does not lead to problems within the rest of its operations. There is a segment of the enterprise database market to which each approach will appeal. Our job here is simply to find a solution to the problem of translating from one system to the other.

The simplest thing to do is to change the name group to something that FrontBase will accept, but which is still descriptive of the function of the column. Looking at the data we see that the column contains a pair of numbers each enclosed in square brackets, as if there might be two group identifiers or a group and subgroup identifier in each row. Without further analysis, we might decide to use the name groupids (which is not reserved) for this column in the target database.

So how do we handle the problem? We can either locate the column description in the SQL Transfer Retry SQL Script window shown above and change the column name there (if that window is even still open) or we can change it in the Destination Table Structure Verification dialog we see when we first begin the transfer process if we choose to start the process over. Either way, we will see that the structure transfer process now goes smoothly.

Figure 5. Improper Date Value Error for contacts Table

But wait! There is still an error in transferring the data. It doesn't happen until record number 397 of the 512 records we must convert, but it still causes the entire lot to be rolled back. It's another Date error.

A brief look at the structure of this table reveals that we have three Date columns this time, but an equally brief look at the data shows that two of these columns are completely filled with valid date values. So the problem must lie within the callBackDate column, which has many empty-looking cells.

While this error didn't occur until we were almost through, we don't know whether this is the only one. (Of course, we could always perform a SELECT on this criteria, but...) Rather than take chances on fixing this for one record when more may have the problem, we can formulate an SQL statement that fixes the all:

update contacts set callBackDate = NULL
         where callBackDate = ''

Figure 6. Statement to Replace Empty Date Values in callBackDate With NULLs

Once we execute this, we can try our transfer once more. Again we only need to transfer the data, not the structure, since the structure transfer has already been accomplished. Omnis Studio performs this transfer on a column-by-column basis, so it does not matter that the group column has a different name in the target table.

We have now performed a successful conversion of both table structures and data from one database to another. We did not have to export the data from each table into a 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 provided tools that allowed us to solve what problems did arise.

But this drag-and-drop database conversion is only a parlor trick compared to the features of Omnis Studio that help us to build front end applications for SQL databases! The DAM technology behind this utility feature offers us many more advantages for truly rapid application development!

The Man Behind The Curtain

Let me tell you a secret - there really is no magic. There is only technology of which the casual observer either is not aware or chooses to remain ignorant. It is this option for ignorance that most often keeps the savvy practitioner in a position of power, but sometimes techno-artists deliberately keep their technologies secret and shroud them in mystical words and incantations. They do not readily disclose those mysteries to just anyone - and sometimes they only confide in a small group of loyal apprentices who can carry on and expand upon the original work.

When Omnis first decided that she wanted to be able to create data objects that could address the master databases, she had an idea for a technology that could be used universally with any of them. This was not her first innovation, but it has become known as one of her most powerful...

Omnis Studio can only translate column definitions and data from one database platform to another by temporarily assimilating that data itself. No translator can interpret effectively without listening to and understanding the words of the first speaker because that understanding is the only way the full and proper meaning of the original utterance can be conveyed to the receiver. Omnis Studio performs a similar service, as we have already seen, so it must set up a temporary table structure with appropriate data types for all columns so it can handle the translation on its own terms.

That is one purpose of the Omnis DAM technology. It maps column definitions between Omnis Studio variable data types and corresponding data types on the target SQL back end. This, in turn, gives us a basis for building applications for existing or new SQL databases because we can directly create Omnis Studio Schema Classes from database tables (or the other way around) by the same easy drag-and-drop technique.

A Schema Class is the direct analogue to an SQL database table definition, but it is a construct contained within an Omnis Studio library. It includes in its contents the name of the database table for which it serves as a reflection so that variables derived from it within an application can be automatically bound to appropriate columns within that table of the database. Let's create one and see what we get.

Adding Schema Classes To A New Library

The first thing we must do is create a new library file to hold the elements of our application. If the Library Browser window (named Libraries in its title bar) is not open in our copy of Omnis Studio, we can most easily access it by pressing the Command-2 key combination. It is also available through the View menu as well as through an icon on the application toolbar that looks like a compass and has a tooltip that says Browser. The window will be empty, but if we click the New Library button (the stack of books on that window's toolbar) or select Library>New... from the window's menu bar, a library icon will appear as shown in Figure 7. The name of this library defaults to New Library, but we can change it to read whatever we want. If we want to share this file with our Windows using friends, it is courteous practice to append a .lbs extension to the name. This extension will not appear in the Libraries window, but it will appear in the Finder.

Figure 7. New Library in Library Browser

To view the contents of our new library file, we can simply double-click its icon in the Libraries window. This opens the Class Browser window for that library, which displays the name of the library in its title bar. In Figure 8 we see that a new library contains only one class named Startup_Task by default. No time to explain that one now, though. We are about to add some Schema Classes to keep it company.

Figure 8. Class Browser Displaying Default Class for a New Library

Hopefully we all still have the FBCOMPANY_Tables window open. If not, quickly open the FBCOMPANY session and drill down to the Tables view. Click on this window to make it topmost and then press the Command-A key combination. This will select all the tables in our Company database on FrontBase. Now just drag them over to the New Library Class Browser window and drop them there. There should be no data type issues for any column in any of these tables, since they were created by dragging tables from the OpenBase version of this database in the first place. The result is a set of Schema Classes in our application library that properly maps to the column definitions of each table in the database, as shown in Figure 9.

Figure 9. Dragging Tables to Create Schema Classes

Just to check and see that all went well, we can double-click on a Schema Class to view the details of the column definitions it contains. We can do the same for a Table in the SQL Browser Table View window. The data types we see there are those for the database platform, while the data types in the Schema Class are those for Omnis Studio. The mapping of data types is built into the DAM for the SQL database we choose to access.

Figure 10. Schema Class and Database Table Details

If we had instead developed an application first and then wanted to use FrontBase as our database, we would have already built our Schema Classes by hand. In that case, we could reverse the process we followed above and dragged the Schema Classes to the Tables view of our database. Tables would have been created in the database as a result. In fact, we might have build an application for OpenBase and then decided to use it with FrontBase (or deploy it for another customer who uses a different SQL database). If we had not done any programming that requires proprietary features of the original database product, the application should work as well with one back end as another. Of course, it's never quite that simple, but you'd be amazed at how easy it is even when differences are involved.

What Next?

Now that we have cloned the table structure from an SQL database into our application library, we can begin the process of building a simple GUI application for the database. But that is a subject for another time.

In the next article, we will examine how Omnis Studio uses special data types called list and row variables to map the columns of an entire Schema Class onto an easy-to-manipulate multi-valued unit whose columns are automatically bound to the columns of the corresponding database table. We will also see how we can bring other features of the DAM technology alive by creating Object Instances and Table Instances that inherit their abilities from DAMs. If you have done SQL programming in other application development software, prepare to be impressed!

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


Community Search:
MacTech Search:

Software Updates via MacUpdate

Microsoft Office 2016 16.11 - Popular pr...
Microsoft Office 2016 - Unmistakably Office, designed for Mac. The new versions of Word, Excel, PowerPoint, Outlook, and OneNote provide the best of both worlds for Mac users - the familiar Office... Read more
Adobe Photoshop CC 2018 19.1.2 - Profess...
Photoshop CC 2018 is available as part of Adobe Creative Cloud for as little as $19.99/month (or $9.99/month if you're a previous Photoshop customer). Adobe Photoshop CC 2018, the industry standard... Read more
Adobe Dreamweaver CC 2018 -...
Dreamweaver CC 2018 is available as part of Adobe Creative Cloud for as little as $19.99/month (or $9.99/month if you're a previous Dreamweaver customer). Adobe Dreamweaver CC 2018 allows you to... Read more
Adobe Flash Player - Plug-in...
Adobe Flash Player is a cross-platform, browser-based application runtime that provides uncompromised viewing of expressive applications, content, and videos across browsers and operating systems.... Read more
Drive Genius 5.2.0 - $79.00
Drive Genius features a comprehensive Malware Scan. Automate your malware protection. Protect your investment from any threat. The Malware Scan is part of the automated DrivePulse utility. DrivePulse... Read more
MegaSeg 6.0.6 - Professional DJ and radi...
MegaSeg is a complete solution for pro audio/video DJ mixing, radio automation, and music scheduling with rock-solid performance and an easy-to-use design. Mix with visual waveforms and Magic... Read more
ffWorks 1.0.7 - Convert multimedia files...
ffWorks (was iFFmpeg), focused on simplicity, brings a fresh approach to the use of FFmpeg, allowing you to create ultra-high-quality movies without the need to write a single line of code on the... Read more
Dash 4.1.5 - Instant search and offline...
Dash is an API documentation browser and code snippet manager. Dash helps you store snippets of code, as well as instantly search and browse documentation for almost any API you might use (for a full... Read more
Evernote 7.0.3 - Create searchable notes...
Evernote allows you to easily capture information in any environment using whatever device or platform you find most convenient, and makes this information accessible and searchable at anytime, from... Read more
jAlbum Pro 15.3 - Organize your digital...
jAlbum Pro has all the features you love in jAlbum, but comes with a commercial license. You can create gorgeous custom photo galleries for the Web without writing a line of code! Beginner-friendly... Read more

Latest Forum Discussions

See All

Around the Empire: What have you missed...
Oh hi nice reader, and thanks for popping in to check out our weekly round-up of all the stuff that you might have missed across the Steel Media network. Yeah, that's right, it's a big ol' network. Obviously 148Apps is the best, but there are some... | Read more »
All the best games on sale for iPhone an...
It might not have been the greatest week for new releases on the App Store, but don't let that get you down, because there are some truly incredible games on sale for iPhone and iPad right now. Seriously, you could buy anything on this list and I... | Read more »
Everything You Need to Know About The Fo...
In just over a week, Epic Games has made a flurry of announcements. First, they revealed that Fortnite—their ultra-popular PUBG competitor—is coming to mobile. This was followed by brief sign-up period for interested beta testers before sending out... | Read more »
The best games that came out for iPhone...
It's not been the best week for games on the App Store. There are a few decent ones here and there, but nothing that's really going to make you throw down what you're doing and run to the nearest WiFi hotspot in order to download it. That's not to... | Read more »
Death Coming (Games)
Death Coming Device: iOS Universal Category: Games Price: $1.99, Version: (iTunes) Description: --- Background Story ---You Died. Pure and simple, but death was not the end. You have become an agent of Death: a... | Read more »
Hints, tips, and tricks for Empires and...
Empires and Puzzles is a slick match-stuff RPG that mixes in a bunch of city-building aspects to keep things fresh. And it's currently the Game of the Day over on the App Store. So, if you're picking it up for the first time today, we thought it'd... | Read more »
What You Need to Know About Sam Barlow’s...
Sam Barlow’s follow up to Her Story is #WarGames, an interactive video series that reimagines the 1983 film WarGames in a more present day context. It’s not exactly a game, but it’s definitely still interesting. Here are the top things you should... | Read more »
Pixel Plex Guide - How to Build Better T...
Pixel Plex is the latest city builder that has come to the App Store, and it takes a pretty different tact than the ones that came before it. Instead of being in charge of your own city by yourself, you have to work together with other players to... | Read more »
Fortnite Will Be Better Than PUBG on Mob...
Before last week, if you asked me which game I prefer between Fortnite Battle Royale and PlayerUnknown’s Battlegrounds (PUBG), I’d choose the latter just about 100% of the time. Now that we know that both games are primed to hit our mobile screens... | Read more »
Siege of Dragonspear (Games)
Siege of Dragonspear 2.5.12 Device: iOS Universal Category: Games Price: $9.99, Version: 2.5.12 (iTunes) Description: Experience the Siege of Dragonspear, an epic Baldur’s Gate tale, filled with with intrigue, magic, and monsters.... | Read more »

Price Scanner via

Sunday Sales: $200 off 13″ Touch Bar MacBook...
Amazon has new 2017 13″ 3.1GHz Touch Bar MacBook Pros on sale this weekend for $200 off MSRP, each including free shipping: – 13″ 3.1GHz/256GB Space Gray MacBook Pro (MPXV2LL/A): $1599.99 $200 off... Read more
B&H drops prices on 15″ MacBook Pros up t...
B&H Photo has dropped prices on new 2017 15″ MacBook Pros, now up to $300 off MSRP and matching Adorama’s price drop yesterday. Shipping is free, and B&H charges sales tax for NY & NJ... Read more
Apple restocks Certified Refurbished 2017 13″...
Apple has restocked Certified Refurbished 2017 13″ 2.3GHz MacBook Pros for $200-$230 off MSRP. A standard Apple one-year warranty is included with each MacBook, models receive new outer cases, and... Read more
13″ Space Gray Touch Bar MacBook Pros on sale...
Adorama has new 2017 13″ Space Gray Touch Bar MacBook Pros on sale for $150 off MSRP. Shipping is free, and Adorama charges sales tax in NY & NJ only: – 13″ 3.1GHz/256GB Space Gray MacBook Pro (... Read more
Best deal of the year on 15″ Apple MacBook Pr...
Adorama has New 2017 15″ MacBook Pros on sale for up to $300 off MSRP. Shipping is free, and Adorama charges sales tax in NJ and NY only: – 15″ 2.8GHz Touch Bar MacBook Pro Space Gray (MPTR2LL/A): $... Read more
Save $100-$150+ on 13″ Touch Bar MacBook Pros...
B&H Photo has 13″ Touch Bar MacBook Pros on sale for $100-$150 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13″ 3.1GHz/256GB Space Gray MacBook Pro... Read more
Current deals on 27″ Apple iMacs, models up t...
B&H Photo has 27″ iMacs on sale for up to $150 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 27″ 3.8GHz iMac (MNED2LL/A): $2149 $150 off MSRP – 27″ 3... Read more
Thursday Deal: 13″ 2.3GHz MacBook Pro for $11...
B&H Photo has the 13″ 2.3GHz/128GB Space Gray MacBook Pro on sale for $100 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13-inch 2.3GHz/128GB Space... Read more
How to save $100-$190 on 10″ & 12″ iPad P...
Apple is now offering Certified Refurbished 2017 10″ and 12″ iPad Pros for $100-$190 off MSRP, depending on the model. An Apple one-year warranty is included with each model, and shipping is free: –... Read more
Silver 12″ 1.3GHz MacBook on sale at B&H...
B&H Photo has the 2017 12″ 1.3GHz Silver MacBook on sale for $1399.99 including free shipping plus sales tax for NY & NJ residents only. Their price is $200 off MSRP, and it’s the lowest... Read more

Jobs Board

Art Director, *Apple* Music + Beats1 Market...
# Art Director, Apple Music + Beats1 Marketing Design Job Number: 113258081 Culver City, California, United States Posted: 07-Mar-2018 Weekly Hours: 40.00 **Job Read more
*Apple* Solution Consultant - Apple (United...
# Apple Solution Consultant Job Number: 113569564 Williston, Vermont, United States Posted: 06-Mar-2018 Weekly Hours: 40.00 **Job Summary** Are you passionate about Read more
*Apple* Media Products (AMP) Engineering Man...
# Apple Media Products (AMP) Engineering Manager Job Number: 86497853 Santa Clara Valley, California, United States Posted: 07-Mar-2018 Weekly Hours: 40.00 **Job Read more
QA Automation Engineer, *Apple* Pay - Apple...
# QA Automation Engineer, Apple Pay Job Number: 113202642 Santa Clara Valley, California, United States Posted: 02-Mar-2018 Weekly Hours: 40.00 **Job Summary** At Read more
Lead *Apple* Solution Consultant - Apple (U...
# Lead Apple Solution Consultant Long Island NY Job Number: 113486035 Long Island City, New York, United States Posted: 07-Mar-2018 Weekly Hours: 40.00 **Job Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.