When you start to develop mission-critical business applications using your favorite development tool, you should seriously consider iAnywhere's SQL Anywhere database management system to support the data management requirements. The iAnywhere company is a subsidiary of Sybase. In the middle 1990s, Sybase acquired along with Powersoft's PowerBuilder product a company from Waterloo, Ontario, Canada, known as Watcom. The company had many products, but was also internationally recognized with the best small footprint and performance DBMS engine then called Watcom SQL. Sybase renamed the "little engine that could" to SQL Anywhere, as it now supported a wide variety of operating system platforms while also venturing into the handheld (PDA) and smartphone device arena as well.

For this article I will focus on SQL Anywhere version 10 and PowerBuilder 11.0. Some of the features reviewed may not be supported in previous versions of these software releases. Where possible, I will try and identify features that are only supported in the current release and if there are any alternatives in previous versions.

Business Rational
Deciding on a particular DBMS technology to implement corporate data repositories is not a decision made lightly. However, in today's fast-paced changing technology world there can be many mitigating factors that may influence this decision. In previous years the older mainframe mentality prevailed that often made acquiring a particular DBMS more of a choice between the who's who of the top vendors. The main reason was to try and choose one DBMS vendor that the IT infrastructure team would support. In reality though, many large corporations and government departments have more then one DBMS vendor product installed. In the Canadian Government, according to recent reports, there are on average 3.5 DB vendor products being used today.

Today, corporations and government departments also have to consider COTS (Computer Off-The-Shelf) applications that often impose another DBMS on the enterprise, the cost of ownership, ease of embedding a DBMS into a turnkey system, ease of administration, self-tuning capabilities, or specialty features like compression, encryption and security all come into play. To this end SQL Anywhere (SA) version 10, released last year, meets or exceeds these key criteria in many ways. In the Canadian Federal Government, where I am located, key mission-critical systems are built on SA such as UN Deployment System (DND), Federal Election System (Elections Canada), and eight mission-critical systems (City of Ottawa). We can also see many commercial system integration scenarios such as the AirMan, Quick Books, Nortel switches, and Cisco Routers that have embedded the SQL Anywhere DBMS under the product covers. I think this speaks highly for the caliber of the SA software.

For Sybase-based developers who use tools like PocketBuilder, WorkSpace, InfoMaker, and PowerBuilder, SA is included for free with no restricted functionality. You may also deploy the DBMS engine for free with your production application on a stand-alone basis. Only where you have a server-based implementation or data exchange scenario are there runtime charges. Sybase also uses the SA DBMS in its Application Servers like EAServer and many of its other various products. For the PowerBuilder developer like myself, I have found in many cases that this engine is the best for my clientele. So in this article I would like to emphasize the considerations and usages that a developer should contemplate and take advantage of when using the SQL Anywhere DBMS.

Installation
If you are installing the SQL Anywhere engine as part of a development tool like PowerBuilder or to accompany Visual Studio (SA has a plug-in for that IDE too), you'll probably find that the SA installer will try to create an "SQL Anywhere" install folder typically under the "C:\Program Files\Sybase" root folder structure. Now for any vendor's products, I like to think ahead and plan for new versions or even support multiple versions of a particular software product. As you go through migration phases between one product release to another, you will most likely have to support simultaneous versions. The other aspect is for regression testing of business applications where you may have to test your system with various versions of the DBMS. To this end, this section will try to deliver some installation considerations and tips that may help you in these endeavors.

Directory Structure
During an install the SQL Anywhere installer will typically request to create a folder for the particular SA version you're deploying. Let's look at two recent versions of SA - release 9 and 10 - as an example. Maybe it's my peculiar habit, but I like to organize the installations of all products by major release. During any product installation when the installer suggests a product folder and path, I usually try and override the top level to reflect the product release. The SA installation's top level is usually defaulted to "C:\Program Files\Sybase" - but I like to change this default to Sybase9 or Sybase10 to reflect all products that could be installed under that version. When you open the Sybase10 top level folder, you can see all the products of that major release reflected from that one point.

Environment Variables
Each instance of the SQL Anywhere software install will also try and utilize various system environment variables to control the runtime processing of the DBMS engine. What I despise about environment variables is that they sometimes get corrupted by reinstallations or by applying patches to the operating system (I actually lost most of my environment variables last December when a Microsoft security patch reinitialized all of them)! To counter this potential threat, as soon as I install SQL Anywhere (or any software vendor product for that matter), I look at any new environment variables that were created/modified and additions made to the System Path (see Figure 1).

For new environment variables, I make sure that they are located in the System area, not the User section. I find that sometimes the software can have access or visibility limitations when located in the User variable section. If you do locate a user variable, just re-create it in the System section and cut/paste the value(s) across to the new entry.

System Path
Once the SQL Anywhere product install has completed, it will have updated your System Path by adding path statements that point to the "shared" and "Win32" sub-folders of the SQL Anywhere installation. There are some potential problems with this approach if you let the system configuration continue in this state, such as: switching SA versions may cause a common DLL to be loaded with the wrong version; the system path could become corrupted and stop SA from running; the System Path could become too long and be truncated; or, other software installations could interfere with the System Path configuration.

To circumvent the potential problems mentioned earlier, I would recommend that you create a System Environment Variable for each version of SA that you wish to install. Then copy the related SA System Path entries to the new environment variable. Now you are ready to reconfigure the System Path properly to potentially avoid problems in the future and to easily switch between SA versions at runtime. To do this we need to modify the System Path to dynamically generate the path statement from the environment variables instead. This can be done via the use of the %name% marker that is preprocessed by the Windows operating system.

As an example, let's see what SQL Anywhere version 10 might have added to the System Path. In my case it was: "C:\Program Files\Sybase10\SQL Anywhere 10\win32;C:\Program Files\Sybase10\Shared\win32;C:\Program Files\Sybase10\Shared\Sybase Central 5.0.0\win32"

I would suggest that you create a new system environment variable, for example "SQLAnywhere10", and assign the SA paths listed above to this variable. Then, in the actual O/S System Path you can remove the hard-coded paths and replace these with the %SQLAnywhere10% variable. This procedure can also be repeated for SQL Anywhere version 8, 9 and even 11 in the future. Just add an environment variable that reflects the SA release number, then modify the System Path accordingly. When you're finished, your System Path might look something like:

"C:\Inetpub\wwwroot\TLB;C:\WINDOWS; C:\WINDOWS\ System32\Wbem;C:\WINDOWS\system32; %MSDotNetSDK2.0%;%MSdotNet2.0%;%MSdotNet1.1%; %EAS5.5%;%PowerBuilder11%;%PowerBuilder10.5%; %PowerBuilder10%;%PowerBuilder9%; %PocketBuilder2%;%ASE15%;%SQLServer2005%; %Oracle10g%;%Roxio%;%MSVisualStudio%; %GS_DLL%;%SQLAnywhere10%;% SQLAnywhere9%; % SQLAnywhere8%;"

Sybase Central
The SQL Anywhere DBMS provides a Database Administrator console named "Sybase Central" (SC) to manage the full administration of any of its databases for backup, restore, creation, unload/reload, security, etc., type of functionality. This is a life-line application for the database administrator to allow interaction with the SA DBMS. It should be noted that SC is utilized by other Sybase products - for example EAServer, and Adaptive Server Enterprise. Installations of these other related products, especially if done incorrectly, can damage your SC install profile for SQL Anywhere.

To protect the SC configuration for the SA installed features, you can easily back this key information up in case of an improper install or if the SC installation folder becomes corrupted for some reason. The SA profile information is located in the "plug-in" section of SC. To access this area, launch the SC application and navigate to the "Tools"=>"Plug-Ins" menu option (see Figure 2). This will now display a list of installed product plug-ins and you should be able to see the SQL Anwhere one listed. Highlight the SA version you have installed and then press the "Properties" button. This will now bring you to a dialog whose first tab page (General) lists the Java "class path" for this plug-in's feature. At this point I would open a text processor, something like Microsoft Notepad, and copy the displayed class path into the utility's work area. Next, select the "advanced" tab page on the open SC "Property" dialog. You should now see a list of .jar files that the plug-in uses for its execution. Again, highlight all this information and copy this across to the open Notepad utility. You should now have a comprehensive list of the SA install profile in Notepad. Use the File=>Save menu in Notepad and save this information in a safe location. I normally save information like this in my "C:\Data\Sybase" folder in which I religiously back up the "C:\Data" root folder on a regular basis including all of its sub-folders - thus, ensuring all my development machine's critical information is retained in case of a disk failure.

Now if the Sybase Central application becomes corrupted and can no longer locate the SQL Anywhere plug-in, the restoration becomes a "snap"! Just launch SC and again navigate to the Tools=>Plug-ins menu and its resulting dialog. This time though you probably won't see the SA plug-in listed. To re-create this information, just select the "Register" button and this will bring you to a "wizard" that will help you along. In the Wizard, select the "Register a plug-in by specifying a .jar file" radio button. Open up your saved SC plug-in information file that you saved in Notepad as I described earlier and copy the Class path that lists the "SAPlugin.jar" to the edit box located just below this radio button, then select the "next" option. The second part of the wizard will ask you to confirm that this plug-in should be loaded at startup. Leave this option selected and press the "next" button. In the final part of the Wizard, it will ask you for the supporting JAR files that the execution class needs. You will need to copy the library list of supporting JAR files to the multi-line edit box provided and then press the "Finish" button. This last step will return you to the SC main screen and you should once again have the SQL Anywhere product available for use!

I would strongly suggest that this procedure be duplicated for all other Sybase products installed as well. I have seen an installation of SA, for example, damage an EAServer or ASE product plug-in - especially if the installation is inadvertently interrupted. That way, all your Sybase product plug-ins can be re-created at a moment's notice without having to reinstall Sybase Central (once for each Sybase product)!

PDA / SmartPhone
You might be installing SQL Anywhere on behalf of a Sybase product like PocketBuilder for use on a PDA or SmartPhone. The installation for SA for the development environment - even Visual Studio for Windows Mobile - is straightforward as it copies the software onto your development machine in the "Program Files" area of the MS-Windows operating system. The installer for SA will be able to locate the fact that you have device emulators installed for Windows Mobile and will make the SA DBMS engine automatically available for these emulation testing tools.

In the case where you have actual real hardware devices attached to your development machine, the SA installer will prompt you to install either SA or SA Light (Lite for my U.S. friends) to a given device. The SA installer senses this through an "ActiveSync" or "Windows Mobile Device Center" (Vista O/S) open session. The key point to consider though before proceeding is where you would like the SA runtime to reside as the installer will ask you to pick either "main memory" or a storage device (like and SD card for example). I personally like to install the SA engine and application database to an external storage device. The rationale for this in my mind could be for the following reasons:

• Takes less memory away from the device that can be used for application execution purposes.
• In the case of a device failure, the external memory card can be transferred to a new device for immediate use.
• An "Auto Run" feature can be set to reinstall SA and your application when inserted on a new device (how convenient is that!).

Developing a Schema
Before you can start to develop your business application, the developer(s) in concert possibly with the database administration will be required to populate the data dictionary (aka System Tables) with the "metadata" that will describe the application's database. This metadata is often referred to generically as a schema. Schema development and generation is typically a lengthy process that may often involve highly specialized tools such as System Architect, ERWin, and PowerDesigner, for example. These are Data Modeling tools that aid the database designers and track the various design criteria. The new database is typically engineered through three phases: Conceptual Design, Logical Design and Physical Design and involve a very formal procedure. The final product is usually a diagram commonly referred to as an Entity Relationship (aka ER) diagram of the main data components required to support your application.

For the application builders using a RAD (Rapid Application Design) or Proto-cycling approach, these formal tools may hinder the spontaneous nature of the "on-the-fly" design and coding or, these specialized tools may be too expensive for the application team to acquire. In these cases, the application designer and DBA might be the same person, so they need to use tools that are readily available and easy to use. For PowerBuilder developers this is extremely convenient as its Database Painter is very adept at helping you construct and maintain Tables, Views, Procedures, Functions, Indexes, and Foreign Keys. What the painter lacks is the support for developing database Triggers and the ability to debug Procedures, Functions, and Triggers. However, the good news is that Sybase Central for SQL Anywhere can pick up the slack in these latter areas very easily.

Getting Started
A handy thing to do is to set up the ability in PowerBuilder to launch the Sybase Central utility application from the main toolbar. That way, when you need this DBA console you don't have to leave the PB IDE to perform this task. PowerBuilder has supported a great feature for a long time that allows the developer to define a toolbar item that can be programmed to interact with the outside world. We can take advantage of this feature to accomplish launching the SC console as follows:

1. Start your PB IDE environment.
2. Use the RHMB on the top-level toolbar and choose "Customize" from the resulting pop-up menu.
3. In the Customize dialog that appears, select the "Custom" radio button option at the top of the dialog.
4. Scroll through the list of icons in the top section and highlight one that you would like to use to identify the SC tool (I personally like the "SQL" icon).
5. Drag the desired icon down to the active toolbar visibly emulated in the lower section of the "customize" dialog.
6. Once you drop the icon, another dialog will ask you for some command-line options and another dialog will now appear.
7. For the command line (see Figure 3), select the "Browse" button and locate the Sybase Central executable. This should populate the command line with something like: "C: \ Program Files \ Sybase10 \ SQL Anywhere 10 \ Sybase Central 5.0.0 \ win32 \ scjview.exe".
8. For the Item Text, enter a short description of what you are defining. For example, "Sybase Central".
9. For the last "Microhelp" edit box enter as long a description as you like. For example: "Launch the SQL Anywhere 10 database console...".
10. When you are satisfied with the parameters, press the OK button.
11. The new icon should now appear on the PB upper tool bar and pressing this at any time will launch the Sybase Central tool (see Figure 4).

Now you have the SC DBA console at your "beck and call" anytime you desire by simply pressing the SC icon on your PB toolbar. A nice time-saving feature when you are in the midst of intense PowerBuilder application and database development!

DataBase Painter
PowerBuilder's Database Painter has quite the ability to construct and maintain various entities in your SA database. Before you can do this though, you must define a connection to the SA database engine through the ODBC connection option of the Database Profile Painter in PB. This painter is located approximately centered on the main toolbar of the PB IDE. Pressing this icon will launch the Profile Painter and allow you to declare the proper connection information to the SA database that you desire to work with. Please refer to your PowerBuilder and/or SA database documentation for the correct connection parameters. You will notice though that if you installed SA with the sample database along with PB at the same time, PB has already defined a connection profile for you automatically. Once you have the profile ready, select the connection profile to highlight it, then press the "connect" button to action the request and create a physical connection to the SA database engine.

Now launch the Database Painter in PowerBuilder and you are ready to either: introspect, maintain, or create any database entity(ies) that you may require for your SA database. If the development team or DBA have already populated "table" entities, the visible introspection mechanism is extremely intuitive to use. You will see a Tree control on the left-hand side of the database painter. One of the entries is labeled "Tables". You can select the "+" sign and expand the table section to see a list of these entities (see Figure 5). To visually see the table, drag the name of the table name from the tree to the center "Object Layout" pane and your table should visually appear. You may notice at this time that various indexes and primary and/or foreign keys will display as well. The primary and foreign keys are important because they define Parent-to-Child relationships in the database. In the case of a primary key, the icon is a gold-colored key with a line to the database column that it pertains to. If this appears, you can have PowerBuilder along with the SA Data Definition Language compiler (DDL) help you to locate possible Child tables. In order to do this, use the RHMB on the foreign key and select the "Open dependent table(s)" option from the resulting pop-up menu. This will request that the PB DB Painter interface with the SA DDL compiler return a list of Tables that relate to the table you have displayed in the "Object Layout" pane. This action can result in zero, one, or more tables visually appearing.

If you have a table visually displayed and can see a Foreign Key (FK) symbol displayed, it will appear as a blue-colored key picture with a line connected to the column(s) that define it. Use the RHMB on the FK and select the "Open Referenced Table" option in the resulting pop-up menu. This action will request PB to communicate to the DDL Compiler in SA and return the information about the Parent table in this relationship. The parent table relationship should also now appear in the Database Painter's object layout pane (see Figure 6). If you continue to probe the various primary and foreign key picture icons and select either a parent or child table for each of these, you will soon have a comprehensive diagram about the database structure that you need to develop against. This graphic layout feature is only available in PowerBuilder and cannot be seen from the SC console for SA, which many developers find extremely intuitive - from the feedback I've received. To drill down for the details on any aspect that you now see, use the RHMB on any table, column, index key, etc., and select the "Properties" option from the resulting pop-up menu. Requesting to look at a database entity property will populate the "Properties" pane on the right side of the painter with as much detail as the SA DDL compiler returns to the painter.

In Part 2 I will discuss tables and columns, views, triggers, events, stored procedures, and more.