Caché SQL is automatically installed as part of a Caché
installation. After Caché is configured, Caché SQL does not
typically require any additional configuration or setup. Most of the Caché
SQL configuration options deal with the operation of DDL (how different flavors
of DDL map to Caché) and with management of cached queries.
For Windows systems, there is an
“SQL client only” installation
option available that installs only the client access (ODBC driver) components
of Caché.
You can view and modify the various Caché SQL configuration settings
using the Advanced tab of the Caché Configuration Manager and expanding
the
SQL folder in the options tree:
SQL Section of the Caché Configuration Manager
By default, Caché performs a full ODBC installation with a standard
installation. This section describes how to set up and configure the ODBC
driver. The architecture of an ODBC system has five parts:
-
The client application—An application makes calls according
to Microsoft's
ODBC
API. ODBC calls establish a connection from the client to a
Data
Source Name (DSN), which, for Caché, is a mapping that specifies
a particular namespace for a particular Caché installation, as well
as other attributes. For a client application to connect to a particular DSN,
that DSN must be registered with the ODBC driver manager.
-
The ODBC driver manager—The driver manager accepts calls
from applications using the ODBC API and hands them off to a registered client
driver, such as the Caché client driver. The driver manager also performs
any necessary tasks so that the client application can communicate with the
client driver and, ultimately, the database server. The Windows driver manager
is created by Microsoft and included as part of the operating system; for
UNIX,
several are available.
-
The ODBC client driver—A database-specific application
that accepts calls from a client application through the ODBC driver manager
and provides communication to the database server. It also performs the range
of ODBC-related data conversions that the application may request.
-
The database server—The actual database ultimately receiving
the calls from the ODBC client application. It can be on the same or a different
machine than the ODBC client driver from which it is receiving calls. Each
Caché database can support multiple DSNs, each of which can support
multiple connections.
-
An initialization file—A set of configuration information
for the driver manager; depending on the operating system, it may also contain
client driver information. On UNIX, this is an actual file, frequently called
odbc.ini.
On Windows, it is a registry entry.
In practice, a connection is established as follows:
-
A client application includes ODBC calls that attempt to connect
to a particular DSN. A client application is linked to an ODBC driver manager,
which accepts the calls.
-
The ODBC driver manager reads the Caché ODBC initialization
file to obtain the location of the Caché ODBC client driver and load
the client driver into memory.
-
Once loaded into memory, the Caché client driver uses
the Caché ODBC initialization file to locate connection information
for the DSN, including the host on which Caché is running, the name
of the Caché installation, and the namespace containing the data. Using
both the DSN and other information from the initialization file, the client
driver connects to the specified Caché installation and namespace.
-
Having established the connection, the client driver maintains
communications with the Caché database server.
ODBC Environment Variables and Logging
This section describes the ODBC environment variables and what they
do. They are:
To set a UNIX environment variable on the Bourne or Korn shell, use
the following commands:
VARIABLE=value
export VARIABLE
To set a UNIX environment variable on the C-Shell, use either of the
following commands:
setenv VARIABLE value
set VARIABLE=value
To turn off an environment variable in a UNIX shell, run
unset or
set it equal to 0. If you never initially set its value, it remains disabled.
This variable allows you to specify the duration of a timeout for a
default login. Its value is in seconds.
This boolean variable specifies that the driver writes header information
to the log file. Header information is printed when the driver is loaded and
includes build version, date, and time. A value of 1 turns on header generation
and a value of 0 turns off header generation. By default, header generation
is off.
This boolean variable enables the automatic appending of the process
ID number to the log file name. A value of 1 enables appending and a value
of 0 disables it. By default, appending is off.
CACHEODBCTRACE (UNIX Only)
This boolean variable enables client driver logging. The default name
for this file is
CacheODBC.log. On Windows, enable client
driver logging by checking the
ODBC Log check box on
the
DSN setup dialog.
This variable specifies the location and name of the trace file. This
can be useful for placing the trace file in a unique directory or giving it
a unique name. For UNIX, the trace file is generated in the current directory
by default; for Windows, the trace file is generated in
C:/WinNT or
C:/Windows by
default.
Windows Client Management
The tasks you can perform on Windows include:
Windows Client Driver Configuration
Caché ODBC Data Source Setup Dialog
The
InterSystems Caché ODBC Data Source Setup dialog
is used to configure a specific Caché ODBC data source. You then can
use this data source to make an ODBC connection to Caché from an ODBC-aware
application (such as a development tool or report writer). The elements of
this dialog are analogous to those in each section heading of a
UNIX
ODBC initialization file.
The fields within the
Data Source dialog are listed
below and are required unless otherwise specified:
-
Name—Specifies the name of the
DSN.
-
Description—Specifies an optional
description of the DSN.
-
-
Host Port Number—Specifies the
port for connecting to the DSN. The default for Caché is 1972.
-
-
User Name—Specifies the user name
for logging into the DSN. By default, this is
“_SYSTEM” and is
not case sensitive (optional).
-
Password—Specifies the password
for the account specified by the
UID entry. For the SYSTEM
user name, the password is
“sys” and is not case sensitive (optional).
-
ODBC Log—If checked, specifies
the creation of a log file of ODBC client driver activities. This log is for
troubleshooting; you should not turn logging on during normal operation as
it will dramatically slow down ODBC performance. To enable client driver logging
on UNIX, use the
ODBCTRACE variable.
To perform driver manager logging on Windows,
enable
it; on UNIX, configure the
initialization
file.
-
Static Cursors—If checked, enables
the Caché ODBC driver's static cursor support. If this flag is off,
then the cursor support provided by the ODBC Cursor Library will be used.
In general, this flag should be off unless you have a specific reason for
not using the ODBC Cursor Library.
-
Disable Query Timeout—If checked,
causes the ODBC driver to ignore the value of the ODBC query timeout setting.
The ODBC query timeout setting specifies how long a client should wait
for a specific operation to finish. If an operation does not finish within
the specified time, it is automatically cancelled. The ODBC API provides functions
to set this timeout value programmatically. Some ODBC applications, however,
hard-code this value. If you are using an ODBC application that does not allow
you to set the timeout value and the timeout value is too small, you can use
the Disable Query Timeout option to disable timeouts.
-
Unicode SQL Types—If checked, turns
on reporting of a Unicode SQL type (
“SQL_WVARCHAR (-9) SQLType”)
for string data. This allows Microsoft Office 2000 and Visual Basic applications
to allocate the properly sized buffers to hold multibyte data. This functionality
is only relevant if you are working with a multibyte character set, such as
in Chinese, Hebrew, Japanese, or Korean locales. If you are only using single-byte
character set data, do not check this box.
After you have configured an ODBC Data Source, you can use the
Test
Connection button to see if your data source is working correctly.
Enabling Driver Manager Logging on Windows
This section covers the following topics:
Supported Drivers and Platforms
To use the Caché ODBC Driver, you must have an ODBC driver manager
on UNIX. The suppliers of UNIX ODBC driver managers for use with the Caché
ODBC driver are:
-
iODBC — Freeware
with open source code. This is the default driver installed with UNIX Caché.
-
unixODBC —
Freeware with open source code. unixODBC supports a Unicode driver and is
the default driver for many versions of UNIX. For information on using the
unixODBC Unicode driver (
libcacheodbcw), contact InterSystems.
There is also a commercial driver manager available from
DataDirect,
formerly Merant, formerly InterSolve. Note that this product's licensing agreement
allows for connections to it only from DataDirect ODBC drivers (and not the
Caché ODBC driver).
InterSystems currently supports the ODBC narrow interface on all UNIX
platforms through the
libcacheodbc library. Caché
ships with a pre-built iODBC driver manager.
Installing and Configuring a Caché UNIX ODBC Client Driver as
Part of a Full Install
-
During Caché installation, there is the following prompt:
Do you want to install ODBC and SQL Gateway <Yes>?
Answer
“Yes,” which is the default. This ensures that you
have the appropriate ODBC software on your machine.
-
After installation, start Caché with the
ccontrol command:
# ccontrol start <configname>
where
configname is the configuration name that you
chose during installation.
The installation places the following components under the
cachesys directory:
-
-
-
-
-
-
-
-
-
-
-
select—A sample ODBC program already
linked with the iODBC driver manager. To run this program, type in
./select
dsnname and enter a
SELECT statement to connect
to a running Caché, such as
./select samples
SELECT * from Sample.Person
-
-
Installing and Configuring a Stand-alone Caché UNIX ODBC Client
Driver
To create a stand-alone client installation, the steps are:
-
Create the directory where you wish to install the client,
such as
/usr/cacheodbc/:
-
Copy the zipped tar containing the files for client driver
from the mounted CD drive to the directory where you just created with a command
of the form:
# cp <CD-dir>/dist/ODBC/ODBC-<release-code>-<platform>.tar.Z <client-install-dir>
where
CD-dir is the mounted location of the Caché,
release-code and
platform are release-specific and operating-system–specific codes (see next
step),
client-install-dir is the client installation directory
that you created in the previous step.
-
Go to the client installation directory and manually unpack
the
.tar file. From the same directory on the CD where
the
cinstall program located, the
.tar file
is in the
./dist/ODBC/ directory.
To manually
unpack the
.tar file, the procedure is:
# cd <client-install-dir>
# gunzip ODBC-<release-code>-<platform>.tar.Z
# tar xvf ODBC-<release-code>-<platform>.tar
where
release-code is a release-specific code (that
varies among Caché versions and releases) and
platform specifies
the operating system for the driver. Possible values of
platform are:
-
-
decunix5clu—DECUnix Tru64 for clusters
-
-
-
linux—Linux, all supported platforms
-
-
-
-
Run the
ODBCInstall program, which will
be in the directory that you created previously. This program creates the
several sample scripts and configures
cacheodbc.ini under
the
mgr directory. For example:
# pwd
/usr/cacheodbc
# ./ODBCInstall
-
-
The ODBC Initialization File
To establish connections between a client application and an ODBC driver,
the iODBC driver manager uses an initialization file. This file provides information
so that the driver manager can locate and connect to an available DSN, including
the path of the ODBC driver required for that connection. The Caché
ODBC driver,
libcacheodbc.so, references the initialization
file to specify DSN options (such as a timeout duration) when connecting on
UNIX platforms. The file can have any name, but, typically, it is called
.odbc.ini when
it is located in a user's personal directory,
odbc.ini when
located in an ODBC-specific directory; the Caché-specific version of
it is called
cacheodbc.ini and is located in a the
/<cache-install-dir>/mgr directory.
To locate this file, the Caché ODBC driver mimics the search
order of iODBC release 3.0.6. It looks for the file in the following places
(in this order):
-
The file specified by the
ODBCINI environment
variable, if this is defined. When defined, this variable specifies a path
and file, such as:
ODBCINI=/usr/cachesys/cacheodbc.ini
export ODBCINI
-
-
-
The file specified by the system-wide
SYSODBCINI environment
variable, if this is defined. As with
ODBCINI, when defined,
this variable specifies a path and file, such as:
SYSODBCINI=/usr/cachesys/cacheodbc.ini
export SYSODBCINI
-
The file
odbc.ini file located in the
default directory for building the iODBC driver manager (
/etc/),
so that the full path and file name are
/etc/odbc.ini.
The iODBC 3.0.6 driver manager ships with Caché releases, built
as a nonthreaded shared object. The driver manager is used for SQLGateway
(
cgate.so) and with a select program we build for testing
ODBC. Our examples use
cacheodbc.ini in the
mgr directory
which is configured during the install for accessing the samples and user
namespace. You should modify
cacheodbc.ini to add additional
DSNs for use with
cgate.so and iODBC, as Caché
sets the
ODBCINI environment variable to access this file.
If you wish to use a different
odbc.ini file then you
should delete/rename the
cacheodbc.ini file to allow
the driver manager to search the
$HOME or
/etc/odbc.ini paths.
The following is a sample initialization file:
[ODBC Data Sources]
samples=samples
[samples]
Driver = /usr/cachesys/bin/libcacheodbc.so
Description = Cache' 5.x ODBC driver
Host = localhost
Namespace = SAMPLES
UID = _SYSTEM
Password = sys
Port = 1972
Protocol = TCP
Query Timeout = 1
Static Cursors = 0
Trace = off
TraceFile = iodbctrace.log
[Default]
Driver = /usr/cachesys/bin/libcacheodbc.so
This file includes the following variables:
-
ODBC Driver Sources—Lists all DSNs for the file. Each
entry is of the form
“DSNName=SectionHeading”, where
DSNName is
the name specified by the client application and the
SectionHeading specifies
the heading under which DSN information appears in this file.
-
-
Description—Contains an optional
description of the DSN.
-
Host—Specifies the IP address of
the DSN in dotted decimal or dotted quad form, such as
“127.0.0.1”.
-
Namespace—Specifies the namespace
for the DSN.
-
UID—Specifies the user name for logging
into the DSN. By default, this is
“_SYSTEM” and is not case sensitive.
-
Password—Specifies the password for
the account specified by the
UID entry. For the SYSTEM
user name, the password is
“sys” and is not case sensitive.
-
Port—Specifies the port for connecting
to the DSN. The default for Caché is 1972.
-
Protocol—Specifies the protocol for
connecting to the DSN. For Caché, this is always TCP.
-
Query Timeout—If 1, causes the ODBC
driver to ignore the value of the ODBC query timeout setting.
The
ODBC query timeout setting specifies how long a client should wait for a specific
operation to finish. If an operation does not finish within the specified
time, it is automatically cancelled. The ODBC API provides functions to set
this timeout value programmatically. Some ODBC applications, however, hard-code
this value. If you are using an ODBC application that does not allow you to
set the timeout value and the timeout value is too small, you can use the
Disable Query Timeout option to disable timeouts.
-
Static Cursors—If 1, enables the
Caché ODBC driver's static cursor support. If 0, then the cursor support
provided by the ODBC Cursor Library will be used. In general, this flag should
be off (that is, set to 0) unless you have a specific reason for not using
the ODBC Cursor Library.
-
-
TraceFile—If logging is enabled by
the
Trace entry, specifies the location of the driver manager
log file.
Testing a Caché ODBC Client Driver for UNIX
This section describes how to test the UNIX ODBC client driver:
-
Run the
select program to ensure
that the client can read data:
# cd <cache-install-dir>/dev/odbc/samples/select
# ./select.sh
What is the
select.sh script doing? It calls the
select program
that is installed with the kit; the script also provides the
select program
with a particular DSN (
“SAMPLES”). The
select program
is linked to the iODBC driver manager (
libiodbc.so);
the driver manager reads the Caché initialization file (
<cache-install-dir>/mgr/cacheodbc.ini)
to get connection information for the
“SAMPLES” DSN. The driver
manager determines the location of the Caché ODBC client driver and
loads it into memory. The client driver then establishes a TCP/IP connection
to port 1972 and is connected to the Caché
“SAMPLES” namespace
using the DSN information inside the Caché initialization file. Once
the connection is established, the client application runs an SQL
select statement
against the Caché database. Specifically, the statement is
“SELECT
* from Sample.Person WHERE ID < 11”, which obtains the first ten
rows of the
Sample.Person table in the
“SAMPLES” namespace.
To see the application ODBC calls, refer to
select.c.
-
With a client driver that is part of a full Caché installation
(not a stand-alone client), you can also test SQL Gateway access from Caché:
./sqlgateway/gatewaytest.sh
from the same directory as the previous step.
The
gatewaytest.sh script starts a Caché
session and runs the routine
SQLGatewayTest in the
“SAMPLES” namespace.
This application routine then loads a shared object called
cgate.so,
which is linked against the iODBC driver manager. As in the previous example,
the driver manager load the client driver using information from the Caché
initialization file. The client driver then establishes a TCP/IP connection
to port 1972 and is connected to the Caché
“SAMPLES” namespace
using the DSN information inside the Caché initialization file. The
application routine runs the same query as above.
The difference between this example and the simple
select example
above is that in this example, the Caché process making the initial
call is the client application. Typically, a Gateway call from Caché
calls the DSN of another vendor's database.
The
select.sh and
gatewaytest.sh scripts
should work without errors after the installation. To create your own calls,
examine the content of the files for these projects for code that you can
modify for your own needs.
Troubleshooting for Shared Object Dependencies
To validate dependencies on other shared objects and correct any problems,
the process is:
-
Use the
ldd command against an executable
or shared object:
# ldd <cache-install-dir>/bin/libcacheodbc.so
If any dependencies are not found,
ldd displays an
error similar to the following:
libstlport_gcc.so => not found
-
If there are no errors, then all dependencies are valid; if
there are errors, run the following commands to force the shared object loader
to look in the current directory:
# sh
# cd <cache-install-dir>/bin
# LD_LIBRARY_PATH=`pwd`:$LD_LIBRARY_PATH
# export LD_LIBRARY_PATH
The
sh command starts the Bourne shell; the
cd command
changes to the appropriate directory; and the
export command
sets the path to look up shared objects.
-
Once you have added the current directory to the path, run
ldd again
and check for missing dependencies. If any shared objects cannot be found,
add them to the same directory as the ODBC driver.
-
To permanently make shared objects accessible, begin by copying
the shared objects to
/usr/lib or
/usr/local/lib,
depending on your operating system. Consult the documentation for your operating
system to determine which is the appropriate directory and if there are any
special commands for this, such as registering them on Linux with the
ldconfig command.
Compiling and Linking select.c
To compile and link the
select.c sample program:
-
-
Compile the program using
cc, the
UNIX compiler, ensuring that you include any include files. For a default
Caché installation, the command is:
# cc -c select.c -I../../redist/iodbc/include
-
Link the file using
cc, ensuring
that you link to any required libraries. Again, for a default installation,
the command is:
# cc -o select select.o -L../../redist/iodbc -ldl -liodbc
# cc -c select.c -I/usr/local/iodbc/include
# cc -o select select.o -L/usr/local/iodbc/lib -ldl -liodbc
Installing and Configuring the iODBC Driver
If you want to build your own iODBC driver manager to operate under
custom conditions, the process is described below. For all builds, the iODBC
executable and include files should be in the
<cache-install-dir>/dev/odbc/redist/iodbc/ directory.
You need to set
LD_LIBRARY_PATH (
LIBPATH on
AIX) and the include path in order to use these directories to build your
applications.
Note:
The following references are provided as an example. Please check the
documentation provided by iODBC for the correct version number as well as
complete installation instructions and release notes.
The process of building the iODBC driver manager is:
-
-
Log in as user
root and uncompress/unzip
and un-tar the kit in
/tmp or other temporary area:
# gunzip /tmp/libiodbc-3.0.6.tar.gz
# tar xvf libiodbc-3.0.6.tar
-
In the installation directory, configure the iODBC source
code:
# cd /tmp/libiodbc-3.0.6
# ./configure --prefix=/usr/local --with-iodbc-inidir=/etc
This configures iODBC for the default installation directory of
/usr/local and
the default initialization directory of
/etc.
-
Compile the package with the
make command:
-
Optionally, run any self-tests that come with iODBC as follows:
-
Install the test tools, samples, shared objects, data files,
and documentation:
Configuring PHP with Apache and the Driver Manager for libcacheodbc.so
You can use the ODBC functionality of Caché in conjunction with
PHP (PHP: Hypertext Processor, which is a recursive acronym). PHP is a scripting
language that allows developers to create dynamically generated pages. The
process is as follows:
-
Get or have root privileges on the machine where you are performing
the installation.
-
-
-
For unixODBC, download the source kit, install it, and
configure it for use with PHP;
the iODBC instructions may be helpful even for use with unixODBC.
-
-
-
Build PHP and install it.
-
Build the Apache HTTP server, install it, and start it.
-
-
Copy the Caché-specific initialization file,
cacheodbc.ini to
/etc/odbc.ini as
this location functions better with the Apache Web server if the
$HOME environment
variable is not defined.
-
-
Copy the
sample.php file from the Caché
ODBC kit to Apache root directory (that is, the directory where
info.php is
located), and tailor it to your machine for the location of Caché.
You can then run the
sample.php program, which uses the
Caché
“SAMPLES” namespace, by pointing your browser to
http://127.0.0.1/sample.php.
JDBC Client Configuration
The configuration of the JDBC client consists of the following steps:
-
-
Setting up your CLASSPATH environment variable to refer to
this
.jar file.
