MS SQL Server

Installing Microsoft SQL Server on OS/2

• I discovered that I had omitted a file from the SYBFIX.ZIP file on Hobbes that should have been in the package. Step 18 of last month's article had you renaming the files LIBCS.DLL and LIBSYBDB.DLL; You also need to rename LIBSRV.DLL to LIBSRV.SAV. Step 19 last month had you download the file SYBFIX.ZIP from Hobbes and unzip it in your x:\SQL10\DLL directory. Download SYBFIX1.ZIP instead and unzip that into the x:\SQL10\DLL directory. It contains all the files from SYBFIX.ZIP plus the file I omitted: LIBSRV.DLL.
• Steps 9 - 12 in last month's article describe how to create a new "service". We need to create a "service" for the Microsoft SQL Server that we want to connect to; I'll show you how below.

Note: you can use the Sybase client to connect to Sybase servers and Microsoft SQL Servers at the same time. If you have already setup the Sybase client for a Sybase server, adding the Microsoft SQL Server "service" will not affect the Sybase settings or connections.

1. Enter a name for the "service" and click on OK. I called my service MSSQL.

   

   

2. Click on Edit - Add Server from the menu bar the then fill in the fields as shown below. Use the IP address of your Microsoft SQL Server machine; the ,1433 at the end of the IP address is the port number that Microsoft SQL Server is listening on - 1433 is the default port. Click the OK button.

   

3. Now you can ping the server to see if you have configured the "service" correctly. You may get an error box telling you that Sybping cannot find the NT section or the language EN_US. You can ignore this or you can correct it by making an entry for EN_US in the NT section. Steps 16-17 in last month's article describe how to do this, except those steps say to put the entry in the [OS2] section of the LOCALES.DAT file. You need to also put that same entry in the [NT] section of the file. In other words - the [OS2] and the [NT] sections of the file should have the same entries.

   

4. If you want to test the client before installing the ODBC driver, you can - the ISQL application included in the Sybase client will also work on Microsoft SQL Server. Step 20 in last month's article describes how to use it. For the -S parameter use the "service" you created above, and for the SQL command use select * from pubs.dbo.titles instead of the what is listed in last month's article. If everything is configured correctly you will get rows from the TITLES table.
5. Intersolv did not include the Microsoft SQL Server ODBC driver in version 3.01, only in version 2.11. That driver is included in both the DAX11.ZIP and ODBCDEMO.ZIP files on Hobbes. Use the ODBC Installer ODBCINST3.ZIP to install the driver. Instructions for using the ODBC Installer are in Part 3. Remember that you must have ObjectRexx installed and enabled on your machine for the Installer to work.

   

   Also: if you already have a version 3 ODBC Driver Manager installed on your machine you do not want to install the version 2 Driver Manager - just install the driver itself. Version 2.11 ODBC database drivers work just fine with the version 3 Driver Manager. Be sure to uncheck to Install Administrator and Driver Manager check box after entering the source file if you already have a version 3 Driver Manager installed.

   

6. After you have installed the ODBC Database Driver you must create a data source for the Microsoft SQL Server you want to connect to. Start the ODBC Administrator and click on the ADD button. You can use either the v 3 or the v 2.5 Administrator. Then select the SQL Server ODBC driver. Click on the OK button.

   

7. Enter a name for the data source, a description, and the "service" name you create above. You can also enter a database name - the pubs database is an example database that is included in the default Microsoft SQL Server install.

   

8. After setting up the Data Source for Microsoft SQL Server data we can use ODBC applications to work with the data. This screen shot shows data from the dbo.titles table in the pubs database from Microsoft SQL Server in Lotus Approach. I was not able to get the database part of StarOffice 5.1 to work on Microsoft SQL Server data; when trying to open a table I get a application error that shuts down StarOffice. It appears that StarOffice is making multiple connections to the same database when going through the process of defining a database. This might be reflective of the other problems StarOffice seems to have with ODBC databases. I am inclined to believe this is more a StarOffice defect than an ODBC driver defect.

   
   [Click image to view full size]

The screen shot below shows the Database Designer that is included as part of VisPro C/C++, VisPro Reports, and I think VisPro Rexx. The database shown is the pubs example database that is normally installed as part of Microsoft SQL Server 6.5. The Database Designer is an application that diagrams an existing database (something VisPro calls "reverse engineering"), and allows you to create new tables and relationships. Each box represents a table in the database. The lines between the boxes/tables depict the relationships between the tables established by primary and foreign keys.

[Click image to view full size]

Installing Microsoft SQL Server on WinOS2

1. Start the Install.
2. Select destination drive and path for the files, and the network protocol you want to use. I generally use TCP/IP because while it is not the fastest protocol around it is almost ubiquitous. You want to select Client Configuration and ISQL; the Server Books is optional.

   

3. If you select all the install options you will have a Program Group in Program Manager than looks like this when the install finishes.

   

4. Open the SQL Client Configuration application. Click on the Advanced button. Enter a name for the server (any name will do), the name of the DLL file that matches the protocol you want to use. TCP/IP uses DBMSSOC3.DLL. The Connection string is the IP address, port of the server you want to connect to. Click the Add/Change button and then the Done button.

   

5. Test the configuration by attempting a connection using ISQL. Double click on the ISQL icon. You will get a connection dialog box as shown below. Select the "server" you configured in the step above; if the drop down list is blank type the name in (the name is the same as you entered for the Server). Enter a User ID; sa is a default user ID that may or may not exist on your installation. Click on the Connect button.

   

6. Type in the SQL command select * from pubs.dbo.titles and click on the Execute button (green triangle). This assumes that you have the pubs database defined on your server. You should see the results as shown below.

   
   [Click image to view full size]

   

7. The ODBC driver is included in the client package. Start the SETUP.EXE file in the ODBC directory under the WIN16 directory. You can only select one driver - the SQL Server driver.
8. When the setup finishes it starts the ODBC Administrator so that you can add a new Data Source for this driver. Click the Add button and select the SQL Server driver.
9. On the configuration screen enter the Data Source Name (any name will do), a description for the Data Source, the "Server" name you created in Step 4. You can also enter a Database Name by pressing the Options button and entering the name.

   

You can now use ODBC for Microsoft SQL Server data sources in WinOS2.

Conclusion

PostgreSQL 7.1

PostgreSQL has an extremely rich set of data types, which include the usual date, character, and numeric types as well as PostgreSQL specific types like box, circle, line, path, lseg (line segment in 2D plane), point and polygon, inet (IP Address), money, and time with and without a time zone. All data types can also be single or multi-dimension arrays - which violates one of the tenets of the relational model but is becoming common in object/relational databases - see last months article.

The implementation of SQL is very complete, with features like:

1. Subselects in the FROM clause of a SELECT statement. These allow you to build complex selection statements without having to create views, which saves you from view maintenance. Click on BigSql for an example of a large SELECT statement that utilizes subselects in the FROM clause.
2. Left, right and full outer joins
3. Select statement as a source for INSERTs.
4. CASE statements are supported in SQL. A case statement allows you to modify the value returned for a column based on conditional clauses and is very useful, especially when formatting data for reports. The figure below shows a trivial case statement in PostgreSQL.

[Click image to view full size]

PostgreSQL has its own procedural language called PL/pgSQL which is roughly modeled after Oracle's PL/SQL. It can be used in creating procedures/functions and triggers. According to the documentation PostgreSQL also supports 2 other procedural languages in those roles: PL/Tcl and PL/Perl - although I have not tested this and do not know if PL/Tcl or PL/Perl are supported on the OS/2 port. You can also program functions and modifications to SQL in C. PostgreSQL is a very customizable database. An example of this is something PostgreSQL calls "rules". Rules are sort of like a decision tree on what PostgreSQL should do as it processes SQL and other statements. You can "hook" into those rules and modify PostgreSQL behavior in special circumstances. This is somewhat like Oracle's "instead of" triggers that allow you to substitute a different SQL statement or procedure for the one that tripped the trigger. Where I have found the most use for this type of feature is in making views that would normally not be updatable. Most databases will not handle updates to views that are very complex, and sometimes complex is considered to be nothing more than two tables joined in a single view. "Instead of" processing can be used to make those types of views updatable, because while the database doesn't know how to update the view, you do. So you write an "instead of" procedure/trigger that is tripped by the update view statement which substitutes update statements for each of the constituent tables for the statement that triggered the procedure. One of the nasty little side effects of normalizing database tables (the process of breaking data into separate tables with on duplication of data and with clear relationships) is that the data structure often assumes forms that are alien or cumbersome to the end user. If it not unusual to spend enormous amounts of time on the front end applications making the data look more "real world like" and yet still allow the data to be updatable. "Instead of" features can save lots of time spent on front end applications.

Installing PostgreSQL

• Install Feature Install first.
• Install the Java upgrade. You only need the run time environment (RTE), which is a smaller download than the SDK. Anything later than version 1.1.1 will supposedly work.
• With the v 4.1 package, there are two install programs you have to run: 1) an install program for MPTS, and 2) an install for TCP/IP. When you download and unzip the package (it's huge) there will be a directory tree for MPTS and another one for TCP.
• I have never been able to get the graphical (read normal) install to work on any machine I have tried - curse that stupid Feature Install crap. The only way I have been able to get TCP/IP to install is using unattended install and a response file, which is easier to do than it sounds. Take the sample.rsp file in the TCP subdirectory and copy it to your own file. Edit that file and remove everything except the destination for the install. Then run the install with the response file parameter

  install /r:filename.rsp

  where filename.rsp is the file you created from sample.rsp. DO NOT USE THE SAMPLE.RSP verbatim - otherwise you might get features and a configuration that doesn't work and is very difficult to overcome.

1. Download the EMXRT.ZIP package and install it, if you don't already have EMX v 0.9 fix 04.
2. Edit your CONFIG.SYS file and add the following lines:

   SET TMP=C:\TEMP
   SET TEMP=C:\TEMP
   SET TMPDIR=C:\TEMP

   If you don't already have them defined. The path C:\TEMP can be any path that points to an existing directory. This is where temporary files will be created on your system.

3. Download the file postgres7_1-server-bin_os2.zip and unzip it in a temporary directory.
4. Run INSTALL.EXE from the temporary directory. It appears from the one of the readme files that the database directory has to be on the same drive as the code. We will assume in the following steps that the directory installed into was x:\PGSQL. I strongly suggest that you not change the paths from the defaults - although changing the drive is fine.

   

5. Reboot. You should have folder on your desktop that looks like this.

[Click image to view full size]

If you only want the install the client skip to the section on the ODBC driver. The following steps will initialize a template database, and then create our database to work with.

1. Rename the file INITDB (no extension) in the directory x:\PGSQL\BIN to INITDB.SAVE.
2. Download the file INITDBPG.ZIP and unzip it into your x:\PGSQL\BIN directory.
3. Edit the INITDB and change the characters X: to the drive where you have installed PostgreSQL. If you did not use the default path(s) in the install you will also have to find each path statement and change that statement, not just the driver letter - which is why I suggested above not changing the paths.

   The new INITDB file you downloaded has some path information hard coded into the file - hence your need to edit the file and change the path information. The INITDB that comes with the package attempts to discern the path information itself. When I ran the INITDB that comes with the package I had the following errors and the install stopped.

   sed: Invalid 'from' reg expr: :
   Message file not found.
   Message file not found.
   The input file '/pgsql/share/template1.bki' needed by initdb does not
   belong to PostgreSQL 7.1. Check your installation or specify the
   correct path using the -L option.

4. Open a OS/2 Command Window and change to the x:\PGSQL\BIN directory. Type INITDB . This will create your "template" database. The template database contains stuff that is used to create other databases. This init process can take some time so be patient.
5. Start the server by clicking on the PostgreSQL Server icon in the PostgreSQL folder. It will open a window and that should look like this. If you do not see the READY at the bottom of the window it means that the server did not start correctly. Check the log file x:\PGSQL\LOG\SERVER.LOG for messages on why the server did not start.

   

   If the window disappears when you start the server, rather than staying open on the desktop like shown above, then the server did not start properly. The most common way to confirm this is through the existence of a telltale type file in the x:\PGSQLDATA directory called postmaster.pid. This existence of this file is used to indicate that the server is running - preventing multiple instances from starting at the same time - which seems to be common Unix programming trick. If this file exists when the server starts, say from a machine crash, then the server will not start. Just delete the file and restart the server if you are sure there are no other instances running.

6. Now we need to add some procedures for ODBC stuff to the template database for use by the ODBC driver. Click on the Postgres Console icon. Type \i /pgsql/share/odbc.sql and hit the enter key. This runs the statements in the file ODBC.SQL. Since all new databases are created from the template database we will not have to configure the new databases having added the ODBC stuff to the template database. You can use the command \! mode 90 50 to change the screen size - the 90 is the width in characters and 50 is the height in lines - use a size that fits your screen. (The \! sends a command to the shell and the mode command changes the screen size.)

   You should now create a database to use - since it is not a good idea to use the template database as a working database.

7. Open a Command window and change to the x:\PGSQL\BIN directory. type createdb mydatabase where mydatabase is the name of the database you want to create. We will assume the database name mytest for the rest of the install instructions. You may want to create a new console icon in the PostgreSQL folder to use on the new database; copy the existing console icon, change the name so you know which database this runs against and change the parameter field to your database name.

   

   Now we need to check that our database works, and to configure a new user ID.

8. Click on the icon to open the GUI Administration application.
9. Click on File - Datasource from the menu. This is where you will configure a datasource - which is almost identical in function to ODBC data sources, i.e. it identifies a machine and database that you want to access. We will create two datasources: one for the template database and one for the new database we created. Enter a name for the data source, your IP address for your own machine, and the name of the database you want to connect to, and press Add. The template database is called template1 . Create another datasource for the database you created in step 12 above.

   

10. Now log on to mytest (the new database you created). Use the File - Logging menu option. Enter postgres as the user name and password; this is the superuser created for us by the install program.
11. Now we want to create a new user. Use the System - User menu option. Fill in a user name and press Add User. I have not been able to get a password to work so I created a user without the password.

The Administration GUI tool is still a work in process. Some of the options work and some have yet to be implemented. It will be nice when it is completed.

The file x:\pgsqldata\pg_hba.conf contains authentication information used to validate database connections. You edit the file and add lines for each IP address or domain that you want to allow connections from, along with the type authentication required.

Now we are ready to install the ODBC driver.

ODBC

PostgreSQL only has an OS/2 ODBC driver; there is not a WinOS2 (Win16) ODBC driver. This is unfortunate because there are some very nice Windows 3.1 applications that run well on WinOS2 and are very useful for database work.

Installing the ODBC driver is very easy. If you use the ODBC Installer ODBCINST3.ZIP then you will not have to modify the CONFIG.SYS. There is also an installation program included in the PostgreSQL ODBC driver package, but you will have to modify your CONFIG.SYS file afterward to correct the ODBC_PATH and LIBPATH statements if you use this program.

1. If you have not already downloaded the ODBC Installer do so, and unzip the file into a directory where you want to install it. Remember: you must have ObjectRexx enabled on your machine for the ODBC Installer to work. If you get the error message

   SYNTAX ERROR: Routine not found in:
   installedList.i = strip(cahngestr('00'x,installedList.i,''))

   when starting the ODBC Installer you probably don't have ObjectRexx installed. To install ObjectRexx open an OS/2 Command Window and type switchrx . You will have to re-boot.

2. Download the file odbc3_01-bin_os2.zip. This contains the PostgreSQL ODBC driver, along with some other drivers and a version 3 ODBC Administrator and Driver Manager.
3. Start the ODBC Installer, select the file you downloaded as the source.

   

4. Click on the Drivers tab and the Available Drivers minor tab, if it is visible; if you don't have any other drivers installed you will not have an minor tabs on the Drivers page. Select the PostgreSQL driver.
5. Next you have to create a data source for connecting to your PostgreSQL database. Start the ODBC Administrator - usually located in the System Setup folder. Click on the Add button and select the PostgreSQL driver.
6. Fill in the database name, user name, and host machine address on the setup screen. You need to turn off the Read-only check box on both the Driver and DataSource sub-screens; click on the Driver and the DataSource buttons to get to the sub-screens. I am not sure what the rest of the options are, and the documentation describing the options is missing from the PostgreSQL web site(s). Click OK and you are ready to go.

[Click image to view full size]

The PostgreSQL ODBC driver does not work correctly with Lotus Approach or StarOffice. Lotus Approach is apparently calling an ODBC function that is not implemented in the driver. It's hard telling what StarOffice is doing since there are some other databases/drivers it doesn't work with. I was not able to test VX-Rexx against this version of the driver because VX-Rexx does not run on my WSeB/modified WSeB machines and I had the invalid packet length error on the Warp v4 machine; I have heard reports that the previous version of the driver did not work with VX-Rexx.

Conclusion

Among the so called "open source" SQL databases available on OS/2 - PostgreSQL, mySQL, miniSQL - PostgreSQL is feature-wise vastly superior in every way. PostgreSQL also compares very favorably with commercial databases, at least in the feature and design areas. The icing on the cake is the price. PostgreSQL is free, both for personal and for commercial use. It is the most free of all the open-source databases.

The down side is that the implementation is rather weak. ODBC functions are missing or incorrectly implemented in the ODBC driver; Lotus Approach and StarOffice will not work correctly with the driver. Large Object implementation is weak in the ODBC driver. Installation is difficult or impossible on some systems, or at least my machines. And the documentation is missing some sections.

Despite the implementation weaknesses this is really an outstanding database. When I looked at the previous version I wasn't too impressed, but this version has won me over. The good news is that with this version the database is pretty much complete feature-wise. This means that future development will probably concentrate on the implementation weaknesses and enhancing performance.

Stuff Described

The VisPro Database Designer is an application that is bundled in the VisPro C/C++, VisPro Rexx, and VisPro Reports products. The VisPro product line is made by the Hockware Company. Or I should say was made by Hockware, since it appears that Hockware has gone out of business. You can still occasionally find VisPro products on eBay. Which is a shame because while the support was less than pleasant, the products were generally very good. If you work with databases much the Database Designer is a valuable product. You can use it to view the existing relationships between tables in a database, or to create tables and establish key relationships between tables.

Lotus Approach has also been recently reviewed in OS/2 eZine. It is a database application which is built on the dBase file format. The screens can also be used on any ODBC data source, or any ODCB data source that works with Approach. Lotus Approach is bundled with eComStation as part of SmartSuite, which among other reasons makes eComStation an outstanding value.