This chapter describes the particulars of establishing an ODBC connection for use as part of a Caché client. Its topics include:

• Connection Requirements
• Caché ODBC Driver
• Caché SQL Server
• Installation (Windows)
• Installation (UNIX)
• Defining Data Sources for ODBC Clients
• Supported APIs
• Error Logging
• Troubleshooting

A growing number of application and development tools in Windows environments rely on ODBC to provide access to external databases. The Caché ODBC Driver enables ODBC-compatible Windows applications to connect to data on the Caché SQL Server using Structured Query Language (SQL).

The Caché ODBC Driver supports ODBC version 2.5. For more information on ODBC, see Microsoft ODBC 2.0 Programmer's Reference and SDK Guide. If you refer to Microsoft ODBC 3.0 Software Development Kit and Programmer's Reference  (Microsoft Press, 1997), remember that Caché support is for version 2.5 (not 3.0).

The Caché SQL Server provides high-performance access to Caché databases via SQL using any ODBC-compliant application or development tool.

It is important that both your client and server systems be configured properly, so that you can connect to the Caché SQL Server from your client application. The requirements for an Caché SQL Server connection are:

• An ODBC client application
• The Caché ODBC Driver
• The Caché SQL Server
• Networking Software (TCP)

InterSystems' Caché ODBC Driver runs on the same system as a client application. It manages communications between the client application and the Caché SQL Server by accepting ODBC application programming interfaces (APIs) calls. This provides support for client applications running on Windows 95, 98, 2000, or NT.

The Caché super server handles all connections and processing for the Caché SQL Server. By default, the super server accepts connections on port 1972. It automatically creates a Caché server process whenever an ODBC client logs into Caché.

Note that if you change the port number of the super server, clients must use the matching port number in the DSN setup on their machines.

The default user name and password for Caché provide a basic way to log in to the database and get started. The default user name is "_SYSTEM" (upper case) and "" is its password.

When you install Caché, the installation process automatically places the ODBC driver on your system. To configure it, use the ODBC Setup utility from the Windows Control Panel, which is where you define data sources for your ODBC clients. A data source specifies how to connect to Caché. Once you define a data source, you can use it any time you connect to the server from your application tool.

If you wish to install the ODBC driver independent of the rest of Caché, you can do so by going through the Custom Custom menu in Caché installation process.

Each individual Caché namespace is a separate data source. For example, if you plan to use both the EMPLOYEE and VENDOR namespaces, you must add each as a data source. You can add or modify data sources during or after installation. Specifically, there is one data source for each Server Master/TCP Port/Caché namespace combination. Each data source accepts the following information:

To run ODBC on UNIX, you need an ODBC driver manager. A possible ODBC driver manager is iODBC from www.iodbc.org; all the examples in this section use this.

The Caché ODBC driver looks for configuration information first in the home directory indicated by the HOME environment variable (for .odbc.ini) and then checks for the file /etc/odbc.ini.

This distribution consists of ODBCdriverpack.tar.Z

To unpack this, run the following command:

compress -d ODBCdriverpack.tar.Z tar xvf ODBCdriverpack.tar

This creates a directory that contains the following files:

• odbcuserguide.txt -- these instructions
• select.c -- a sample ODBC program that will prompt you for a select statement.
• odbc.ini -- a sample ODBC ini file that should be copied to .odbc.ini in your $HOME directory (NOTE THE INITIAL DOT), you will have to modify it with path of driver. Another possibility is to copy it to /etc/odbc.ini and modify the path of the driver.

This directory also contains the following subdirectories:

• decunix -- True64 DEC
• UNIX hp11 -- the HP 11 UNIX
• OS linux -- RedHat Linux 6.0 and above
• ppc - IBM AIX
• solaris -- the SUN Sparc Solaris 2.6 and above OS (not ultrasparc or Intel)
• usparc -- SUN 2.7 on ultrasparc

Each of these directories contains a file named CacheODBC.so, which is a driver for the relevant operating environment.

First, install the iODBC driver manager, then put CacheODBC.so from the appropriate operating environment where you want it.

Then modify .odbc.ini in your $HOME directory to point to the right path.

The run select Cache (the first argument to select is the DSN name)

select Cache

Enter a SQL SELECT statement, eg.

select * from iscperson

Note that, on Linux, you must statically link ODBC applications to iODBC to prevent difficults when calling both SQLDisconnect and SQLFreeConnect. This circumvents a bug in Linux that results in improper file cleanup after calling dlclose. Linking statically with iODBC seems to get around this bug.

Static linking however means that gethostbyname ceases to work so you must enter Internet and Intranet addresses as dotted-decimal, for example, 192.10.202.45.

You also need a .odbc.ini file (note the beginning period), which goes in the directory specified by the $HOME environment variable.

To set the value of this variable in the Bourne, Korn, or Bash shells, the command is:

% HOME=XXX export HOME

For the C-shell, it is:

setenv HOME XXXX

The format of the file itself is:

;

; odbc.ini ; [ODBC Data Sources] Cache = Cache

[Cache] Driver = /myinstallpath/CacheODBC.so

Description = Your description here 

Host = www.xxx.yyy.zzz (must use dotted decimal) 

Namespace = USER 

UID = XXX 

Password = YYY

Port = 1972 

Protocol = TCP

Query Timeout = 1

Trace = off

where

• driver specifies the location of the Caché .so file
• host specifies the TCP/IP address of the machine accepting the connection
• namespace specifies the Caché namespace serving as the data source
• UID specifies the identity of the account associated with the ODBC connection
• password specifies that account's password
• port specifies the connection port, which usually has a value of 1972
• protocol specifies the type of communications protocol in use (almost always TCP)
• query timeout specifies the amount of time that can elapse prior to allowing a query to fail
• trace specifies whether or not the ODBC driver manager is to create a log file (usually disabled with a value of "off")

Place the .odbc.ini in your $HOME directory.

You can find out this directory by doing

echo $HOME

To turn on debugging/tracing the user must set in their environment CACHEODBCTRACE equal to 1.

For example:

On Bourne/Korn shell

CACHEODBCTRACE=1

export CACHEODBCTRACE

On C-Shell

setenv CACHEODBCTRACE

To turn off tracing/debugging, don't set this environment variable, unset this environment variable, or set it equal to 0

CACHEODBCNOHEADER turns off the generation of header information in the log file. Usually header information is always printed when the driver is loaded. This environment variable turns off the printing of the header information.

CACHEODBCNOHEADER=1 # turn off header generation CACHEODBCNOHEADER=0 # turn on header generation

The default is that header generation is on.

CACHEODBCTRACEFILE=tracefile

allows one to put the trace file in a different place, for example,

CACHEODBCTRACEFILE=/home/myuser/myapp/CacheODBC.log

you can even change the log file name, for example,

CACHEODBCTRACEFILE=/home/myuser/myapp/CacheODBC.113.log

This can be useful for programatically naming a log file according to the process that starts it, for example, one can make the pid part of the log file name.

The default is that CacheODBC.log is generated in the current directory.

Note that using CACHEODBCTRACEFILE one must specify the file name and not just a directory.

A data source specifies all the information listed above. To define a data source, use the ODBC Administrator from the Windows Control Panel to define a data source for an ODBC client:

1. Double-click the ODBC icon (the 32-bit ODBC icon on Windows 95/98) in the Windows Control Panel to display the ODBC Data Source Administrator panel. This panel has tabs for user, system and file data sources (DSNs).
2. Select the type of data source (DSN) you want to define:

   User DSN	Data source is only visible to you and can only be used on the current machine.
   System DSN	Data source is visible to all users on this machine. (This includes NT services if you are running Windows NT.)
   File DSN	Data source can be shared by users who have the same Cache SQL ODBC driver installed.

3. Click Add to display the Create New Data Source panel.
4. Highlight InterSystems Caché ODBC and click Finish, which displays the Caché ODBC Data Source Setup dialog box.
5. Complete the input for this screen, as described in "Installation (Windows)" or its online help.
6. Click OK to return to the data sources dialog box.
7. Repeat steps 2-6 to add an additional data source, or click Close to return to the Control Panel.

In Visual Basic and other Microsoft products that use Database variables, you can override the user ID and password associated with the data source. When a user attempts to connect to the server, the API verifies the user as follows:

1. Checks the ODBC Administrator. If the API finds a user ID and password in the ODBC Administrator, the user never sees a logon screen unless the developer override (step 2) is present.
2. Checks for a line such as the following in the Connect property of the Data Control and Database variable:
   

   
   ODBC; DS=ACCOUNT; UID=jsk; PWD=<ENTER>

   
   
   In this example, ACCOUNT is the data source, and jsk is the user ID. When a NULL character or <ENTER> follows PWD=, the password is taken from the data source. If an actual password is specified here, no logon screen appears at connection time.

This section describes the supported conformance levels for the Caché ODBC Driver, the functions included in each, and the compliance of each function.

The Caché ODBC API supports three conformance levels for API functionality:

• Core functionality -- These functions correspond to the functions in the X/Open and SQL Access Group Call Level Interface Specification. This includes basic connection management, preparing and executing SQL statements, data retrieval from result sets, transaction management, and error retrieval.
• Level 1 functionality -- These functions include core functionality, as well as more sophisticated connection management, additional result set functionality, and retrieving information about drivers and data sources.
• Level 2 functionality -- These functions include core and Level 1 functionality, as well as sending and receiving arrays of parameters and result column values, support for scrollable cursors, retrieval of specific parameter information, and retrieval of SQL statements in native form.

The following tables summarize ODBC functions and the conformance that the Caché ODBC 3.1 Driver provides for them:

Table 2-1: ODBC Core Conformance by Function 

Function Name	Purpose	Compliance
SQLAllocConnect	Obtains a connection handle.	Full
SQLAllocEnv	Obtains an environment handle. One environment handle is used for one or more connections.	Full
SQLAllocStmt	Allocates a statement handle.	Full
SQLBindCol	Assigns storage for a result column and specifies the data type.	Full
SQLCancel	Cancels an SQL statement.	Partial
SQLColAttributes	Describes attributes of a column in the result set.	Full
SQLConnect	Connects to a specific driver by data source, user ID, and password.	Full
SQLDescribeCol	Describes a column in the result set.	Full
SQLDisconnect	Closes the connection.	Full
SQLError	Returns error and state.	Full
SQLExecDirect	Executes a statement.	Full
SQLExecute	Executes a prepared statement.	Full
SQLFetch	Returns a single row from the result set.	Full
SQLFreeConnect	Releases the connection handle.	Full
SQLFreeEnv	Releases the environment handle.	Full
SQLFreeStmt	Ends statement processing and closes the associated cursor, discards pending results, and, optionally, frees all resources associated with the statement handle.	Full
SQLGetCursorName	Returns the cursor name associated with a statement handle.	Full
SQLNumResultCols	Returns the number of columns in the result set.	Full
SQLPrepare	Prepares an SQL statement for later execution.	Full
SQLRowCount	Returns the number of rows affected by an insert, update, or delete request.	Full
SQLSetCursorName	Specifies a cursor name.	Full
SQLSetParam	Assigns storage for a parameter in an SQL statement.	Full
SQLTransact	Commits or rolls back a transaction.	Full

Table 2-2: ODBC Level 1 Conformance by Function 

Function Name	Purpose	Compliance
SQLBindParameter	Bind a parameter to a place marker ("?") in the SQL statement.	Full
SQLColumns	Returns a result set containing column descriptions for the given table.	Full
SQLDriverConnect	Connects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialogs for the user.	Full
SQLGetConnectOption	Returns the value of a connection option. Implemented for options supported by SQLSetConnectOption. Returns S1C00 for other options.	Full
SQLGetData	Returns part or all of one column of one row of a result set. (Useful for long data values.)	Full
SQLGetFunctions	Returns supported driver functions.	Full
SQLGetInfo	Returns information about a specific driver and data source.	Full
SQLGetStmtOption	Returns the value of a statement option. Implemented for options supported by SQLSetStmtOptions. Returns S1C00 for other options.	Full
SQLGetTypeInfo	Returns a result set containing the descriptions of the system-defined ADTs.	Full
SQLParamData	Returns the storage value assigned to a parameter for which data will be sent at execution time (Useful for long data values)	Full
SQLPutData	Sends part or all of a data value for a parameter (useful for long data values).	Full
SQLSetConnectOption	Sets a connection option. See Table 2-4: Notes on Partial Compliance, on page 15 for more information.	Partial
SQLSetStmtOption	Sets a statement option. See Table 2-4: Notes on Partial Compliance, on page 15 for more information.	Partial
SQLSpecialColumns	Retrieves information about: (1) the optimal set of columns that uniquely identifies a row in a specified table and (2) those columns that are automatically updated when a transaction updates any value in the row.	Full
SQLStatistics	Retrieves statistics about a single table and the list of indexes associated with the table.	Full
SQLTables	Returns the list of table names stored in a specific data source as a result set.	Full

Table 2-3: ODBC Level 2 Conformance by Function 

Function Name	Purpose	Compliance
SQLBrowseConnect	Returns successive levels of connection attributes and valid attribute values. When a value has been specified for each connection attribute, connects to the data source.	None
SQLColumnPrivileges	Returns a list of columns and associated privileges for one or more tables.	Full
SQLDataSources	Returns a list of available data sources. (Implemented by driver manager.)	Full
SQLDescribeParam	Returns the description for a specific parameter in a statement.	Full
SQLDrivers	Provides a listing of driver descriptions and their attribute keywords. (Implemented solely by the driver manager.)	Full
SQLExtendedFetch	Returns multiple result rows. Supports SQL_FORWARD_ONLY cursor, with column- or row-wise binding.	Partial
SQLForeignKeys	Returns a list of column names that comprise foreign keys, if they exist for a specified table.	Full
SQLMoreResults	Determines whether there are more result sets available and, if so, initializes processing for the next result set. See Table 2-4: Notes on Partial Compliance, on page 15 for more information.	Partial
SQLNativeSql	Returns the text of an SQL statement as translated by the driver.	Full
SQLNumParams	Returns the number of parameters in a statement.	Full
SQLParamOptions	Specifies the use of multiple values for parameters.	Full
SQLPrimaryKeys	Returns the list of one or more column names that comprise the primary key for a table.	Full
SQLProcedureColumns	Returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures.	Full
SQLProcedures	Returns the list of procedure names stored in a specific data source.	Full
SQLSetPos	Positions a cursor within a fetched block of data.	None
SQLSetScrollOptions	Sets options that control cursor behavior.	None
SQLTablePrivileges	Returns a list of tables and the privileges associated with each table.	Full

For certain functions that provide partial conformance, the following table describes the nature of that conformance:

Table 2-4: Notes on Partial Compliance 

Function Name	Compliance
SQLCancel	Does not support asynchronous queries.
SQLSetConnectOption	The following options are not supported: SQL_PACKET_SIZE SQL_QUIET_MODE SQL_TRANSLATE_DLL SQL_TRANSLATE_OPTION For the SQL_TXN_ISOLATION option, the following values are supported: SQL_TXN_READ_UNCOMMITTED SQL_TXN_READ_COMMITTED
SQLSetStmtOption	The following options are not supported: SQL_ASYNC_ENABLE SQL_KEYSET_SIZE SQL_MAX_LENGTH SQL_NOSCAN SQL_RETRIEVE_DATA SQL_SIMULATE_CURSOR SQL_USE_BOOKMARKS For the SQL_CONCURRENCY option, supported values are: SQL_CONCUR_READ_ONLY and SQL_CONCUR_LOCK; other values substitute SQL_CONCUR_LOCK. For the SQL_CURSOR_TYPE option, the supported value is SQL_CURSOR FORWARD_ONLY; other values substitute this value.
SQLMoreResults	Always returns SQL_NO_DATA_FOUND. Caché SQL supports only one result set.

This section gives an overview of common problems and approaches for solving them. It describes several kinds of issues:

• Connection Problems
• Performance Problems
• Miscellaneous Issues

Connection problems can be due to a number of conditions. If you have problems connecting to the server, check that you have completed each step in the following list correctly.

Is the data source defined correctly?

It should have:

• A valid TCP port number
• A valid Caché namespace
• A valid Caché SQL username/password

For more information on the fields in the data source definition window, see "Defining Data Sources for ODBC Clients" .

If you were prompted for them, did you enter your Caché SQL user name and password correctly?

You may want to go through the login process again if you are uncertain.

Is network software running on the client?

If it is not, an error occurs when you try to log on to the server. Also, be sure that the TCP directory is in the path.

Are you using the correct version for all of your software components?

Caché may be using a DLL that another program has updated.

Are the server, Caché itself, and the server machine running?

If you are unable to log on and you are sure that you have entered information correctly, you can check the connection by:

• Issuing a ping command to the server machine. Use of this command differs among network products; see your network software documentation
• Determining if the requested port is available.
• Ensuring that your TCP connection supports WinSockets 1.1.

Are your network cables connected properly?

Likewise, make sure that all relevant hardware has power.

Is the database itself corrupt?

If you are unable to connect to a database that you were able to access previously and all other connection issues seem to be all right, the database itself may be corrupt. Try connecting to the same database from another application product that you have used successfully in the past. If that doesn't work, ask your system manager to check the database.

Do you have enough conventional memory?

If possible, test the connection with an alternate client PC.

Is the SQL statement valid?

Ensure that the names of the columns and base tables in the statement are valid and that any queries follow Caché SQL rules.

Did you select ODBC (or SQL Drivers) as the application data source?

This is necessary for the connection to succeed.

A slow response does not always indicate an error. Depending on network traffic, the quantity of data you are retrieving, and other activity on the server system, you may need to wait a while for your query to complete. If you have this problem often, you may want to restrict the data you retrieve by adjusting your SELECT statement, or using other mechanisms your application tool provides.

This section describes several commonly occurring errors and how to remedy them:

1. S1000 Error. This is a general ODBC error code which usually reflects a problem creating or compiling a cached query on the Caché SQL Server. You may also receive an SQL error message. Possible reasons:

1. 400 Error. This is a fatal error on the server. Possible reasons:

1. 10000 (or higher) Error. These are WinSock library errors. This may be caused because the WinSock (TCP) directory is not in the path.

This section describes how to perform error logging, both from a client application and in the Caché ODBC driver.

The ODBC Driver Manager allows you to trace calls made by your application to ODBC functions:

1. Select the Tracing tab from the ODBC Data Source Administrator.
2. Select when to trace from the following radio buttons on Tracing panel:

1. Specify the name of your trace file in the Log file Path box. The default trace file is \SQL.LOG.

The Caché SQL ODBC Driver allows you to trace Caché SQL ODBC Client activity.

1. Click on the ODBC Log checkbox in the DSN Setup dialog. Note that this enables system-wide logging, not DSN-specific logging.
2. Check the log file, CacheODBC.log in your \WINDOWS or \WINNT directory.