OS/2 eZine - http://www.os2ezine.com
Spacer
16 October 2001
 
Douglas Clark is a program management consultant who first started using OS/2 version 1.3. He's married, with 2 girls, and is old enough to remember when 4 color mainframe terminals were a big thing.

If you have a comment about the content of this article, please feel free to vent in the OS/2 eZine discussion forums.

There is also a Printer Friendly version of this page.

Spacer
Previous Article
Home
Next Article


Lone Peak Automation, LLC


ODBC on OS/2 Part 6

Well it's hard to believe that we are already on part 6, when I thought 6 months ago that I would be able to write this as one article. This month we cover Sybase SQL.

Sybase SQL Anywhere

Sybase acquired Powersoft, who had purchased Watcom, sometime around 1994-1995. I believe Powersoft acquired Watcom in order to get Watcom's C/C++ compiler, and Sybase acquired Powersoft in order to get PowerBuilder as a front end tool. In the process Sybase picked up Watcom SQL, an excellent SQL database. Since Sybase already had a SQL database they faced the dilemma of what to do with Watcom SQL. What they settled on was to position Sybase SQL Server, now Adaptive Server, as an enterprise application, and Watcom SQL, renamed SQL Anywhere, as a portable SOHO type database. We covered Sybase SQL Server/Adaptive Server and Watcom SQL in previous issues. Here we look at what Watcom SQL became.




When Sybase acquired Watcom, the Watcom SQL server ran on a number of platforms: OS/2, Windows 3.1 DOS, Windows 95, Netware and QNX. They continued all those platforms for the next major release - version 5 - and then dropped all the platforms except Windows 95/NT for all the later releases, now up to version 9 on Windows. However while releasing later versions of SQL Anywhere on Windows platforms, Sybase continued to sell and support SQL Anywhere v 5.5 on OS/2 up to last year, when they stopped selling the product. They still will support v 5.5 on OS/2 up to the end of this year. While Sybase does not sell SQL Anywhere, you can still purchase it on eBay; it fairly regularly appears and sells for $35 - $99. SQL Anywhere runs on OS/2 from v 2 through 4.5/eComStation. Note: version 5.5 is basically version 5 with maintenance releases. Sybase decided to market a standard and professional version of SQL Anywhere - the professional version bundling NetImpact Dynamo and PowerBuilder InfoMaker - neither of which work in OS/2 - and changed the version from 5.0 to 5.5 to indicate the availability of the professional version.

The changes between SQL Anywhere version 5 and Watcom SQL 4 can be broken into two categories:

  1. Incremental improvements in functionality and speed.
  2. Changes to make SQL Anywhere blend in with existing Sybase product line, i.e. SQL Server.

This second category of changes manifests itself as a new SQL "dialect" that matches the Transact SQL used in Sybase SQL Server, and some ability to connect to SQL Server databases from client applications written for SQL Anywhere. The idea being to make the transition from SQL Anywhere to SQL Server as easy as possible.

SQL Anywhere is positioned as a simple, full featured SQL database. Simple here pertains to installing, administrating and maintaining. Its closest competitor in the Windows world, is Interbase, which is a Borland product. Sybase, and before them Powersoft, was looking to create a development package to rival Borland's Delphi and Microsoft's Access. Where Borland bundled Interbase with Delphi to make a complete development package - a la Microsoft Access, Sybase created Powerbuilder bundled with SQL Anywhere; they also released Optima C++ bundled with SQL Anywhere.

SQL Anywhere supports SQL92 syntax and functionality, including outer joins. It also supports triggers, stored procedures and functions. Stored procedures and functions can be written in SQL/procedural language, or the procedure/function can call an "external" procedure/function in a DLL. This is an extremely powerful feature. The database also has a replication feature the can perform one way or two way replication between databases. Replication is a process of sending changes made in one database to another database in order to keep the data "in sync".

The database supports the following data types: CHAR, VARCHAR, LONG VARCHAR, TINYINT, SMALLINT, INTEGER, NUMERIC/DECIMAL, DOUBLE/FLOAT/REAL, DATE, TIME, TIMESTAMP, BINARY and LONG BINARY. The CHAR, VARCHAR and BINARY types are limited to a maximum size of 32767. The LONG types (VARCHAR, BINARY) are limited to a maximum of 2 GB. User defined types are also supported, with the user defined type being a built-in type along with precision/scale (if applicable), check and default values and NULL or NOT NULL specifications. User defined types facilitate creating columns in tables in a consistent manner.

A SQL Anywhere database is stored in files, each file having a maximum size of 2 GB. The database can be spread across up to 12 files on the same or different drives, for a potential maximum database size of 24 GB.

SQL Anywhere supports embedded C language programs, the ODBC interface (what we are interested in), DDE and what Sybase calls a WSQL High Level Interface DLL that has been Rexx enabled on OS/2. Included in the package are ODBC drivers and clients for OS/2, Windows 3.1/95/NT.

The SQL language is very well implemented. Most of the major constructs that I use regularly are included; the exceptions being the case statement and subqueries as tables in the from clause. The work-around for lack of subqueries in the from clause is to use views. The work-around for the case statement is to put the case statement in a function and call the function, as shown in the figure below A case statement is supported in procedures and functions; in the figure below a simple function named casehack is created with a case statement, then the function is used in a SQL statement where a "normal" case statement would be used.

 

SQL Anywhere Database Features
Feature Description

Database Type

Client/Server and local.

Security

User and object

Views

Yes

Referential Integrity

Yes

Object Comments

Yes?

Transactions

Yes

Isolation Levels

Read-uncommitted, read committed, repeatable read, serializable.

Deadlock Resolution

Yes - cancels last transaction to become blocked

Connect Speed

Average

On Line Backup

Yes

SQL Level

SQL92

Enhancements

User defined procedures and functions that can be blended into SQL

Query Optimizer

Yes - simple

Triggers

Yes

Procedure

Yes

Large Table Support

Yes

Large Row Support

Yes

Replication

Yes

Distributed Transactions

No

Heterogenous Vendor Access

No

Installing the Server/Client

The installation package includes a CD with the software and a license diskette. The license diskette determines the number of concurrent users allowed and the products that can be enabled.

The installation CD contains the following software:

  • Client - needed for connecting to a server running on another machine.
  • Server - contains the sever, and the client packages, plus SQL Remote - the replication package.
  • StandAlone - a database engine for accessing databases on the same machine. Intended for application development (like DB2/2's personal edition) and embedding in applications.
  • StandAlone with SQL Remote - combination of the stand-alone and SQL Remote packages.

To install run the setup.exe file in the OS2 directory in the folder that contains the package you want to install. The first screen will prompt you for what package you want to install.

Then you will get a screen where you can enter the locations for the files. I suggest you use the C:\WINDOWS directory for the Windows directory. I had problems when I tried to use the proper WINOS2 directory
C:\OS2\MDOS\WINOS2.

If you already have OS/2 ODBC installed you will probably get a dialog complaining about a conflict between the ODBC the installer wants to install and what you already have installed - which isn't really true since the ODBC files being installed are the Windows version. Select the option to keep the existing files and install the new files, provided the new files are going into the C:\WINDOWS directory.

At the end of the install you will be prompted for the license diskette. You can either insert the license diskette or point the dialog to a directory on your hard drive, if you have copied your license files from the diskette for safe keeping, as I do.

When the installation finishes you will have a folder like the one below.

Installing OS/2 ODBC Driver

You can install the OS/2 ODBC driver using either:

  • ODBCINI.CMD located in the X:\SQLANY50\OS2 directory - where X:\SQLANY50 is the directory where you installed SQL Anywhere. The Rexx program will only install the driver correctly if your ODBC INI files (ODBC.INI and ODBCINST.INI) are located in the C:\OS2 directory - where they should be anyways.
  • ODBC Installer. The installer will correctly install regardless where your ODBC INI files are located. Enter the install directory of SQL Anywhere as the Source Directory - this will insert the SQL Anywhere ODBC drive on the Drivers page.

To install a Data Source for SQL Anywhere start the ODBC administrator. Click the ADD button and select the SQL Anywhere driver. You will see the following setup screen. The SADEMO database is installed automatically as a demonstration database. The user ID is dba and password is SQL for this database.

Once you have the ODBC data source configured you are ready to connect. A SQL Anywhere server must be started on the machine you want to connect to. If you are using the setup shown above you can start the server on the same OS/2 machine; there is already an icon set up for doing that. If you are trying to connect to a database on the same machine you can use either the Server icon or the Stand-alone icon; if you are trying to connect to a database on another machine you must start the server on that machine.

Installing the WinOS2 ODBC Driver

To install the WinOS2 ODBC driver you will have to edit the C:\OS2\MDOS\WINOS2\ODBCINST.INI file, as shown below. You have to add an entry to the [ODBC Drivers] section for the name of the driver to declare the driver as installed, and then a section with that name which points to the driver file and setup file. In the figure below I add the line Interbase SQL Anywhere=Installed to the [ODBC Drivers] section. Then immediately below that you can see the section [ Interbase SQL Anywhere ] to identify that driver. In that section you need two lines:

Driver=x:\sqlany50\win\wod50w.DLL
Setup=x:\sqlany50\win\wod50w.DLL

It doesn't matter what you call the driver in the [ODBC Drivers] section as long as you use the same name in between the brackets in the section below. I used a name that begins with Interbase so that ReportSmith will work with this driver. The order of the driver sections below also doesn't matter.

After you create the entry in the ODBCINST.INI file for the Windows SQL Anywhere ODBC driver - in this example called Interbase SQN Anywhere - then you must create a data source pointer to that driver in order to connect. Open the ODBC icon in the Main folder in Program Manager. Click on the Add button and select the (in this case) Interbase SQL Anywhere driver; the driver name you select will be the same name you used in the [ODBC Drivers] section of the ODBCINST.INI file. Fill in the parameters as shown below. These settings are the same as in the OS/2 ODBC driver.

You may have to create the Windows Program Manager Folder for the SQL Anywhere Windows client application. This application routes data requests from the Windows ODBC driver to the proper SQL Anywhere server - either on the same machine or a different machine.

To create the program folder use the File - New menu option in Program Manager. Select the Program Group radio button and click OK. The use the File - New option again and pick Program Item. Then fill in the dialog box as shown below. The program Name entry is f:\sqlany50\win\dbclientw.exe sademo where sademo is the name of the server/database you want to connect to.

You may also want to create Program Manager icons for the DDE and Stop Client applications, although they are not necessary for ODBC applications. The DDE application command line is x:\sqlany50\win\wsqldde.exe . The Stop Client command line is x:\sqlany50\win\dbstopw.exe .

Once you have the Windows client configured you can connect. To connect:

Start the server on the machine you want to connect to. If you are connecting to a database on the same machine you can start either the server or the stand-alone application. Start the Windows client in Program Manager- this is the same Windows client you created above.

Now you can connect with a Windows ODBC application. The figure below shows ReportSmith with a report on the CONTACT table in the SADEMO database.

Using SQL Anywhere

The Server processes commands against databases from clients, the database being on one machine but the clients can be either on the same machine or across the network. A single server can handle requests against multiple databases on the same machine. Likewise a single client can make multiple connections to databases on the same or different machines.

In order to connect to a database and a server you must know the server's name , in addition to the database name. The server name identifies a specific server across the entire network. Therefore server names must be unique across the entire network. The database name identifies an individual database, and should be unique on a single machine.

When you start a server you can specify a server name with the -n parameter. If you do not specify a parameter and the server has opened a single database the server assumes the name of the database. If you start the server without opening a database, or the server is "serving" multiple databases you must name the server. If you look back at the ODBC setup screen you will notice that you specify the server name and the database name. It is the combination of server name and database name that allows you to get at specific database on a specific machine.

When the server starts it opens a window which displays the User IDs currently connected.

You can configure the server through the window with the File - Configure option. This provides options for encrypting packets, disallowing connections, etc.

If you are using ODBC to connect to a server/database that is on another machine, you must start the client first. The client routes the ODBC requests to the proper server. This is true for either OS/2 ODBC applications or WinOS2 applications; OS/2 applications (connecting to a server on another machine) require the OS/2 client to be running, Windows applications require the Windows client to be running. The OS/2 client is a windowed application, as shown below.

The main user interface to SQL Anywhere is ISQL. This is a PM Window application that sends SQL statements to a database, and invokes utilities against the database. Utilities are provided to:

  • Backup the database
  • Initialize (create) a new database
  • Compress a database
  • Export data to dBase format
  • Erase one or more database files

Conclusion

SQL Anywhere is an excellent OS/2 SQL database. It is still current, it is small, it is fast; and it is supported. The documentation is excellent. The ODBC drivers are very good and stable. Sybil, the Delphi look-alike supports SQL Anywhere natively. Purchasing it is a little less structured than going to your local software store, but it is available nevertheless. I highly recommend this database, especially for the price you will pay.

Next month we finish up the ODBC enabled databases for OS/2 with DB2/2 and mySQL. A brand new version of mySQL was released less than a month ago and that merits a fresh look.



Previous Article
Home
Next Article

Copyright (C) 2001. All Rights Reserved.