ODBC drivers

If you design reports off ODBC data sources (on Windows), you must set up the corresponding data sources on the Job Server and Page Server machines. In addition, you must ensure that each server is set up properly for ODBC. During the installation, Crystal Enterprise installs ODBC drivers for UNIX, creates configuration files and templates related to ODBC reporting, and sets up the required ODBC environment variables. This section discusses the installed environment, along with the information that you need to edit.

Note:    

• Detailed documentation covering the various ODBC drivers is included in the Merant Connect ODBC Reference (odbcref.pdf). This is installed below the crystal/enterprise/platform/odbc directory; it is also located in the doc directory of your product distribution.
• If you report off DB2 using ODBC, your database administrator must first bind the UNIX version of the driver to every database that you report against (and not just each database server). The bind packages are installed below the crystal/enterprise/platform/odbc/lib directory; their filenames are iscsso.bnd, iscswhso.bnd, isrrso.bnd, isrrwhso.bnd, isurso.bnd, and isurwhso.bnd. Because Crystal Reports runs on Windows, ensure also that the Windows version of the driver has been bound to each database.

ODBC environment variables

The environment variables related to ODBC reporting are: the library path that corresponds to your operating system (LD_LIBRARY_PATH on Sun Solaris, LIBPATH on IBM AIX, and so on), ODBC_HOME, and ODBCINI. The Crystal Enterprise installation includes a file called env.csh that is sourced automatically every time you start the Crystal Enterprise servers with the CCM. Thus, the environment for the Job Server and Page Server is set up automatically:

• The INSTALL_ROOT/crystal/enterprise/platform/odbc/lib directory of your installation is added to the library path environment variable.
• The ODBC_HOME environment variable is set to the INSTALL_ROOT/crystal/enterprise/platform/odbc directory of your installation.
• The ODBCINI environment variable is defined as the path to the .odbc.ini file that was created by the Crystal Enterprise installation.

Modify the environment variables in the env.csh script only if you have customized your configuration of ODBC. The main ODBC configuration file that you need to modify is the system information file.

Working with the ODBC system information file

The system information file (.odbc.ini) is created in the HOME directory of the user account under which you installed Crystal Enterprise (typically the crystal user account). In this file, you define each of the ODBC data sources (DSNs) that the Job Server and Page Server need in order to process your reports. The Crystal Enterprise installation completes most of the required information—such as the location of the ODBC directory and the name and location of each installed ODBC driver—and shows where you need to provide additional information.

Tip:    A template of the system information file is installed to INSTALL_ROOT/crystal/defaultodbc.ini

The following example shows the contents of a system information file that defines a single ODBC DSN for servers running on UNIX. This DSN allows the Job Server and Page Server to process reports based on a System DSN (on Windows) called CRDB2:

[ODBC Data Sources]
CRDB2=MERANT 3.70 DB2 ODBC Driver

[CRDB2]
Driver=/opt/crystal/enterprise/platform/odbc/lib/crdb216.so
Description=MERANT 3.70 DB2 ODBC Driver
Database=myDB2server
LogonID=username

[ODBC]
Trace=0
TraceFile=odbctrace.out
TraceDll=/opt/crystal/enterprise/platform/odbc/lib/odbctrac.so
InstallDir=/opt/crystal/enterprise/platform/odbc

As shown in the example above, the system information file is structured in three major sections:

• The first section, denoted by [ODBC Data Sources], lists all the DSNs that are defined later in the file. Each entry in this section is provided as dsn=driver, and there must be one entry for every DSN that is defined in the file. The value of dsn must correspond exactly to the name of the System DSN (on Windows) that the report was based off.
• The second section sequentially defines each DSN that is listed in the first section. The beginning of each definition is denoted by [dsn]. In the example above, [CRDB2] marks the beginning of the single DSN that is defined in the file.

  Each DSN is defined through a number of option=value pairs. The options that you must define depend upon the ODBC driver that you are using. These pairs essentially correspond to the Name=Data pairs that Windows stores for each System DSN in the registry:

  \\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\odbc.ini\dsn

  However, the options for a particular ODBC driver on UNIX may not correspond by name to the options available for a Windows version of the same driver. For example, some Windows drivers store a UID value in the registry, and on UNIX you may need to specify this value with the LogonID option.

  Note:    For detailed documentation on each ODBC driver, see the Merant Connect ODBC Reference (odbcref.pdf). The PDF is installed below the crystal/enterprise/platform/odbc directory; it is also located in the doc directory of your product distribution.

• The final section of the file, denoted by [ODBC], includes ODBC tracing information. You need not modify this section.

When the installation creates the system information file, it completes some fields and sets up a number of default DSNs—one for each of the installed ODBC drivers. The standard options that are commonly required for each driver are included in the file (Database=, LogonID=, and so on). Edit the file and provide the corresponding values that are specific to your reporting environment.

This example shows the entire contents of a system information file created when Crystal Enterprise was installed to the /usr/local directory.

[ODBC Data Sources]
CRDB2=MERANT 3.70 DB2 ODBC Driver
CRINF_CL=MERANT 3.70 Informix Dynamic Server ODBC Driver
CROR8=MERANT 3.70 Oracle8 ODBC Driver
CRSS=MERANT 3.70 SQL Server ODBC Driver
CRSYB=MERANT 3.70 Sybase ASE ODBC Driver
CRTXT=MERANT 3.70 Text ODBC Driver

[CRDB2]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/crdb216.so
Description=MERANT 3.70 DB2 ODBC Driver
Database=
LogonID=

[CRINF_CL]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/crifcl16.so
Description=MERANT 3.70 Informix Dynamic Server ODBC Driver
ServerName=
HostName=
PortNumber=
Database=
LogonID=

[CROR8]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/cror816.so
Description=MERANT 3.70 Oracle8 ODBC Driver
ServerName=
ProcedureRetResults=1
LogonID=

[CRSS]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/crmsss16.so
Description=MERANT 3.70 SQL Server ODBC Driver
Address=
Database=
QuotedId=Yes
LogonID=

[CRSYB]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/crase16.so
Description=MERANT 3.70 Sybase ASE ODBC Driver
NetworkAddress=
Database=
LogonID=

[CRTXT]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/crtxt16.so
Description=MERANT 3.70 Text ODBC Driver
Database=

[ODBC]
Trace=0
TraceFile=odbctrace.out
TraceDll=/usr/local/crystal/enterprise/platform/odbc/lib/odbctrac.so
InstallDir=/usr/local/crystal/enterprise/platform/odbc

Adding a DSN to the default ODBC system information file

When you need to add a new DSN to the installed system information file (.odbc.ini) file, first add the new DSN to the bottom of the [ODBC Data Sources] list. Then add the corresponding [dsn] definition just before the [ODBC] section.

For example, suppose that you have a Crystal report that uses ODBC drivers to report off your Oracle8 database. The report is based off a System DSN (on Windows) called SalesDB. To create the corresponding DSN, first append this line to the [ODBC Data Sources] section of the system information file:

SalesDB=MERANT 3.70 Oracle8 ODBC Driver

Then define the new DSN by adding the following lines just before the system information file's [ODBC] section:

[SalesDB]
Driver=/usr/local/crystal/enterprise/platform/odbc/lib/cror816.so
Description=MERANT 3.70 Oracle8 ODBC Driver
ServerName=MyServer
ProcedureRetResults=1
LogonID=MyUserName

Once you have added this information, the new DSN is available to the Job Server and Page Server, so they can process reports that are based off the SalesDB System DSN (on Windows).