CHAPTER 2: Installation and Set Up

2.1 Software Configuration

	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

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:

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

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.

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.

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.

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.

2.6 Where to Find Additional Information

2.7 Sample Application Written in C

 $EMPRESSPATH/odbccl/example

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;                                                                       
}

SQL_SUCCESS
SQL_NO_DATA_FOUND
SQL_INVALID_HANDLE
SQL_NEED_DATA

SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_STILL_EXECUTING