# CHAPTER 2: Installation and Set Up

## 2.1 Software Configuration

Empress ODBC Client application can be setup to run on either MS Windows platform or on UNIX platform. The following configuration for the ODBC Client/Server scenario is needed:

| ODBC Client side
ODBC Client on MS Windows
Empress ODBC Driver for MS Windows
ODBC Client on UNIX
Empress ODBC Driver for UNIX (shared library or an archive library file)
| Connectivity (ODBC) Server side
Connectivity (ODBC) Server on MS Windows and UNIX
Empress RDBMS
Empress Connectivity (ODBC) Server

The following configuration for the ODBC Local Access scenario is needed
(there is no need for a server):

| ODBC Client side
ODBC Client on MS Windows
Empress RDBMS
Empress ODBC Local Access Driver for MS Windows
ODBC Client on UNIX
Empress RDBMS
Empress ODBC Local Access Driver for UNIX (shared library or an archive library file)

## 2.2 Setting Up the Empress ODBC Driver and ODBC Local Access Driver On Windows

To set up the Empress ODBC Driver for Windows, perform the following steps:

1. Install the Empress ODBC Driver for Windows

   Empress ODBC Driver for Windows is included in every formal released product of Empress RDBMS. To install the Empress ODBC Driver, insert the Empress product CD into the Windows cdrom driver, the installation will start automatically. If the installation does not start automatically, the program can be started manually.

   Empress ODBC Local Access Driver for Windows is included in Empress RDBMS for Windows.

2. Setup the ODBC data source
   - Open the Windows ODBC Manager (ODBC Data Source Administrator) in Windows Control Panel
   - Select the correct data source type "Tab" and click "Add" to add a data source
   - Select Empress ODBC Interface or Empress ODBC Local Access Interface and click "Finish"
   - The "Empress ODBC Data Source Name Setup" dialog box appears as follows:

   - Following table gives information about the "Empress ODBC Data Source Name Setup" dialog box:

`Data Source Name` | Defines ODBC data source name.
`Description` | Defines the description for the data source.
`Server` | Defines a domain name or IP address of the server machine. This field is disabled for ODBC Local Access Data Source.
`Port` | Defines an ODBC service port number. This field is disabled for ODBC Local Access Data Source.
`Database` | Defines a logical database name or physical full Empress database path. The logical database name must be mapped in the Connectivity (ODBC) Server configuration file and only can be used for ODBC Client/Server Scenario.
`UID` | Defines default login name.
`PWD` | Defines default password.
`Translation` | Defines the ODBC driver translation library.
`Trace File` | Defines the ODBC driver trace file. For the ODBC client/server scenario and the multi-thread client environment there could be several trace log files generated. For example, if user enters "trace.log" in his Data Source Name Setup and runs a multi-thread application, there will be "trace.log" file associated with one thread which uses Empress ODBC Interface, "trace.log.002" associated with the second thread which uses Empress ODBC Interface, "trace.log.003" with the third thread that uses Empress ODBC Interface and so on. In ODBC Local Access scenario and multi-thread client environment there will be only one "trace.log" file generated.
`Trace Level` | Define the trace level (1-3). The trace level will be used to control the trace message. The larger number generates more detailed message.
`ODBC 3.x` | Setting this box, causes Empress ODBC Driver to conform to Microsoft ODBC 3.52 Standard. Empress strongly suggest user to set this option.
`ODBC 2.x Level 2` | Setting this box, causes Empress ODBC Driver to conform to Microsoft ODBC 2 Standard.
`ODBC 2.x Level 1` | Setting this box forces the Empress ODBC Driver to perform as an ODBC Conformance Level 1 Driver, according to Microsoft ODBC 2 Standard.
`General Records Buffer` | Defines the size of the memory cache for storing fetched records. If this size is not sufficient to store fetched records, a file will be used to store them. If "Pre-Fetched Records Cache" value is greater than "General Records Buffer" value, pre-fetched records will be stored in the file.
`Pre-Fetched Records Cache` | Defines the size of the space used for storing records in advance when SQLFetch is used.
`Bulk/Text Data Buffer` | Defines the size of the cache for Bulk/Text attributes data, only to be used when SQLGetData is invoked (e.g. not relevant for SQLBindCol).

3. Compile the ODBC client program

   Empress provides `empocc` utility to help user compile their ODBC client program. If you would like to use Empress Local Access, use `empocc -bcs` (using this option will bypass Empress ODBC client server mechanism and access the Empress local database directly). For detailed information, please reference `empocc` command.

## 2.3 Setting Up the Empress ODBC Driver and Empress ODBC Local Access Driver On UNIX

To set up the Empress ODBC Client for UNIX, perform the following steps:

1. Install the Empress ODBC Library for UNIX

Empress ODBC Library is installed while installing the Empress product. It usually does not need to be installed separately.

2. Setup the ODBC datasource

The ODBC database is defined in ODBC Data Source file. The default data Source file name is `odbc.ini`. It should be located in the same directory where the ODBC program is started. The user can also use an Empress variable MSODBCINIFILE to define the name and location of ODBC Data Source file.

Here is the sample `odbc.ini` file,

    [Data Source Name]
    DESCRIPTION=
    SERVER=
    PORT=6322
    DATABASE=
    TRACEFILE=
    TRACELEVEL=
    DEFAULTPWD=
    DEFAULTUID=
    ODBCLEVEL=
    ODBCVERSION=
    CACHE_SIZE=65536
    CACHE_BTSIZE=131072
    CACHE_FSIZE=131072

    where:

`Data Source Name` | Defines ODBC data source name.
`DESCRIPTION` | Defines the description for the data source.
`SERVER` | Defines a domain name or IP address of the server machine. For ODBC Local Access, the value of `SERVER` is `LOCAL`.
`PORT` | Defines an ODBC service port number. This field is ignored for ODBC Local Access Data Source.
`DATABASE` | Defines a logical database name or physical full Empress database path. The logical database name must be defined in the Connectivity (ODBC) Server configuration file and only can be used for ODBC Client/Server Driver.
`DEFAULTUID` | Defines default login name.
`DEFAULTPWD` | Defines default password.
`TRACEFILE` | Defines the ODBC driver trace file. For the ODBC client/server scenario and the multi-thread client environment there could be several trace log files generated. For example, if user enters "trace.log" in his Data Source Name Setup and runs a multi-thread application, there will be "trace.log" file associated with one thread which uses Empress ODBC Interface, "trace.log.002" associated with the second thread which uses Empress ODBC Interface, "trace.log.003" with the third thread that uses Empress ODBC Interface and so on. In ODBC Local Access scenario and multi-thread client environment there will be only one "trace.log" file generated.
`TRACELEVEL` | Define the trace level (1-3). The trace level will be used to control the trace message. The larger number generates more detailed message.
`ODBCVERSION` | Defines the version of Microsoft ODBC Standard which Empress ODBC Driver to conform to. It can only be set to 03.52 or 02.50. Empress suggest the user to set `ODBCVERSION` to`` 03.52.
`ODBCLEVEL` | Sets the level of Microsoft ODBC Conformance Level which the Empress ODBC Driver to perform. It can only be set to 1 or 2 when `ODBCVERSION` is set to 02.50. It is ignored when `ODBCVERSION` is set to 03.52.
`CACHE_FSIZE` | Defines the size of the memory cache for storing fetched records. If this size is not sufficient to store fetched records, a file will be used to store them. If "Pre-Fetched Records Cache" value is greater than "General Records Buffer" value, pre-fetched records will be stored in the file.
`CACHE_SIZE` | Defines the size of the space used for storing records in advance when SQLFetch is used.
`CACHE_BTSIZE` | Defines the size of the cache for Bulk/Text attributes data, only to be used when SQLGetData is invoked (e.g. not relevant for SQLBindCol).

The tracing information for an OBDC client can also be set by environment variables. In UNIX, setting the two environment variables `EMPODRV_TRACEFILE` and `EMPODRV_TRACELEVEL` overrides the values of `TRACEFILE` and `TRACELEVEL` for that environment. For example, in a C Shell environment:

      setenv EMPODRV_TRACELEVEL 2
      setenv EMPODRV_TRACEFILE ./drv2.log

will set the trace level to 2, and trace file to drv2.log for that session.

3. Compile the ODBC client program

Empress provides `empocc` utility to help user compile their ODBC client program. If you would like to use Empress Local Access, use `empocc -bcs` (using this option will bypass Empress ODBC client server mechanism and access the Empress local database directly). For detailed information, please reference `empocc` command.

## 2.4 Setting Up the Empress Connectivity (ODBC) Server On UNIX

To set up the Empress Connectivity (ODBC) Server on UNIX, perform the following steps:

1. It is assumed that you have already installed the Empress RDBMS.

2. Create and set the users to access the Connectivity (ODBC) Server in an Empress password file using the utility `emppassword`.

   1. Connectivity Server needs to access to a password file created as follows:

              emppassword -f password_file create

   2. The users need to be added to the password file:

              emppassword -f password_file add user_login_name

      Add your name and password to the password file. Note that the administrator of an Empress Server is the user that starts the server, indicated by $USER. Anyone who wants to start the Empress Server must have his login name and password in the password_file.
   Note:
     If you don't want to create a new Empress Password file, you can use Operating System's current password file for Empress Connectivity (ODBC) Server. This file is usually: `/etc/passwd`. Note that You can not edit this file with `emppassword` utility, and you can not use a Copy of this file.

3. Set up the Network Server Configuration File (`netserver.cfg`).
   The default `netserver.cfg` is:

           EMPRESSPATH/config/netserver.cfg

   The user can copy this file to his working directory under `$HOME/.empress`, and make the changes to that file. You might need to uncomment some configuration attributes by removing the colon (`:`) behind the attribute. Setting the attributes is done by setting their value to an "X". Unsetting the attributes is done by removing the "X" from the value of an attribute.
   1. If you don't need security check for the users of Connectivity (ODBC) Server, you can disable the security check by commenting out the configuration attribute SECURITY_CHECK_PASSWORD and unsetting it.

              MSNETSERVER
              :
              : common
              :
                  NAME=empodbcsv
                  TYPE=odbc
                  HOST=localhost
                   ....
                  SECURITY_CHECK_PASSWORD=
              :   SECURITY_PASSWORD_FILE=location_of_password_file
                   ....
              MSNETSERVEREND

   2. If you need to check the users of the Connectivity (ODBC) Server for not allowing any user to use the server, one change that you should make to this file is setting value of attribute `SECURITY_PASSWORD_FILE` belonging to the block related to ODBC (the one having `TYPE=odbc`). Set this attribute to point to the password_file that you created in previous step.

              MSNETSERVER
              :
              : common
              :
                  NAME=empodbcsv
                  TYPE=odbc
                  HOST=localhost
                   ....
               :  SECURITY_CHECK_PASSWORD=
                  SECURITY_PASSWORD_FILE=location_of_password_file
                   ....
              MSNETSERVEREND

   The environment variable indicating the name of `netserver.cfg` is: `MSNETSERVERCONFIGFILE`. This is indicated in the file `EMPRESSPATH/config/inifile`, as:

          MSNETSERVERCONFIGFILE=netserver.cfg

   Notes:

   1. You can change the name of the default Connectivity (ODBC) Server by setting a new name to `NAME` attribute in this file.
   2. If you want to change the name of the network server configuration file to something different from `netserver.cfg`, you should also set the environment variable `MSNETSERVERCONFIGFILE` to the new name. e.g. in C Shell:

              setenv MSNETSERVERCONFIGFILE new_netserver.cfg

   3. This file is searched in order by the path, set by environment variable `MSCONFIGFILEPATH`. It's default value is:

             MSCONFIGFILEPATH="$HOME/.empress;$HOME;${EMPRESSPATH}/config"

      If you want to copy the file to any other directory, you should make the `MSCONFIGFILEPATH` to include the new path. e.g. in C Shell:

             setenv MSCONFIGFILEPATH "newpath"

   4. If you don't want to create a new Empress Password file, and want to use the Operating system's current password file for Empress Connectivity (ODBC) Server, set the SECURITY_PASSWORD_FILE as follows:

              SECURITY_PASSWORD_FILE=/etc/password

      Refer to [Password File Utility] for more information.

4. Optionally create the Empress Connectivity (ODBC) Server Configuration File if you need to change the defaults of Connectivity (ODBC) Server Configuration.

5. Start the Empress Connectivity (ODBC) Server by using the following command on the server side.

      $EMPRESSPATH/bin/empsvadm odbc_server_name start -f odbc_server_configuration_file

The odbc_sever_name is the name of the Connectivity (ODBC) Server, set in Network Server Configuration File as `NAME`. The default Connectivity (ODBC) Server name is empodbcsv.
(Note that you need to specify the `-f odbc_server_configuration_file` only if you have created Empress Connectivity (ODBC) Server Configuration File in previous step)

> Note: Empress only allows one Empress Connectivity (ODBC) Server to run on each server machine.

It is suggested that the database administrator starts the Empress Connectivity (ODBC) Server on the server platform. This is necessary to maintain a level of protection for the Empress RDBMS files and prevent unauthorized access by ODBC client applications to Empress RDBMS files belonging to other users.

The administration of Empress Connectivity (ODBC) Server is explained in a separate manual. Please refer to [Empress Servers] for a complete information about usage of Empress Servers and specifically Empress ODBC (Connectivity) Server.

## 2.5 Setting Up the Empress Connectivity (ODBC) Server On Windows

Empress connectivity server administrator can use Empress Connectivity (ODBC) Server Administration Utility to configure Empress connectivity server.
Empress connectivity server can be started in Empress Connectivity (ODBC) Server Administration Utility, Windows service control panel and DOS prompt.
In order to start Empress connectivity server in Empress Connectivity (ODBC) Server Administration Utility or Windows service control panel, the system requires

1. The "ADMINISTRATOR" field in Network configuration in Empress Connectivity (ODBC) Server Administration Utility must be set to "SYSTEM" and "PASSWORD" field must be set to empty.
2. Inside user configuration of Empress Connectivity (ODBC) Server Administration Utility, there must be a user named "SYSTEM" with empty password.

During the system installation, the above setting will be created by default.
To start Connectivity (ODBC) Server through dos prompt, please reference Setting Up the Empress Connectivity (ODBC) Server On UNIX.

## 2.6 Where to Find Additional Information

"Microsoft ODBC 3.0 Programmer's Reference and SDK Guide" contains complete interface and development guide for ODBC 3.0 Standard.

## 2.7 Sample Application Written in C

To demonstrate some of the capabilities of the Empress ODBC Interface, Empress provides a sample ODBC program which is included in the product CD. This file will be copied to the directory

     $EMPRESSPATH/odbccl/example

after the successful Empress Installation.

Here are the basic steps for each application:

1. Initializing the ODBC environment and connecting to the Data Source.
2. Processing one or more SQL statements.
3. Terminating the connection.

A part of the example program is as follows:

    main () {

        short   i;
        char    valsr[STRING_LEN];
        long    vallr;
        RETCODE err;

        /* Environment allocation */
        check("SQLAllocEnv",SQLAllocEnv(&henv));

         /* Data Base Connection allocation */
         check("SQLAllocConnect",SQLAllocConnect(henv, &hdbc));

          /* Establish a connection to a Data Source */
          /* NOTE: You must specify [empress] data source section in odbc.ini file */
         check("SQLConnect",SQLConnect(hdbc, (UCHAR FAR *)"empress", SQL_NTS,
                               (UCHAR FAR *)LOGINNAME, SQL_NTS, (UCHAR FAR *)"", SQL_NTS));

         /* Statement allocation */
         check("SQLAllocStmt",SQLAllocStmt(hdbc, &hstmt));

         /* Create a test table t with couple records */
         check("create t (ID longinteger, name char)",SQLExecDirect(hstmt,
                (UCHAR FAR *)"create t (ID longinteger, name char)", SQL_NTS));
         check("insert into t values (1, 'ana')",SQLExecDirect(hstmt,
                (UCHAR FAR *)"insert into t values (1, 'ana')", SQL_NTS));
         check("insert into t values (2, 'fred')",SQLExecDirect(hstmt,
                (UCHAR FAR *)"insert into t values (2, 'fred')", SQL_NTS));
         check("insert into t values (3, 'bill')",SQLExecDirect(hstmt,
                (UCHAR FAR *)"insert into t values (3, 'bill')", SQL_NTS));

         check("SQLBindCol",SQLBindCol(hstmt, 1, SQL_C_LONG, &vallr, 0, &cbLenL));

         /* Assign the storage and data type for the second column in a result set */
         check("SQLBindCol",SQLBindCol(hstmt, 2, SQL_C_CHAR, valsr, STRING_LEN, &cbLenC));

         /* retrieve records from table t */
         check("select from t",SQLExecDirect(hstmt, (UCHAR FAR *)"select from t", SQL_NTS));

         i=1;
         while (1)
            {
                 /* Fetch records */
                 check("SQLFetch", err = SQLFetch(hstmt));
                 if (err) break;

                 /* print table attributes from each fetched row */
                 printf("Row %d .. ID: %ld Name: \"%s\" \n", i++,vallr,valsr);
            }

         /* Drop a test table t */
         check("drop table t",SQLExecDirect(hstmt, (UCHAR FAR *)"drop table t", SQL_NTS));

         /* Free Statement allocation */
         check("SQLFreeStmt",SQLFreeStmt(hstmt, SQL_DROP));

         /* Disconnect status */
         check("SQLDisconnect",SQLDisconnect(hdbc));

         /* Free Data Base Connection allocation */
         check("SQLFreeConnect",SQLFreeConnect(hdbc));

         /* Free Environment allocation */
         check("SQLFreeEnv",SQLFreeEnv(henv));

        return 0;
    }

A good programming practice is to check the return value after each call to the ODBC function. In general, the return value can be one of the following:

    SQL_SUCCESS
    SQL_NO_DATA_FOUND
    SQL_INVALID_HANDLE
    SQL_NEED_DATA
    SQL_SUCCESS_WITH_INFO
    SQL_ERROR
    SQL_STILL_EXECUTING

The ODBC function call check should involve calling `SQLError` to retrieve possible additional information.

The test program can be modified to check other SQL commands or ODBC API function calls.