Appendix C: Connect ODBC UNIX Help

C.1 The UNIX Environment

The supported UNIX platforms are: Solaris, AIX, and HP-UX. System requirements are:

Solaris:

• Sun SPARCstation
• Solaris 2.51 or 2.6 operating system
• 25 MB of available hard disk space

AIX:

• AIX 4.2 or 4.3 operating system
• 25 MB of available hard disk space

HP-UX cFront Enabled:

• HP-UX 10.10 or 10.20 operating system
• 25 MB of available hard disk space
• cFront compiled application

HP-UX aCC Enabled:

• HP-UX 10.20 operating system
• 25 MB of available hard disk space
• aCC compiled application

HP-UX 11 aCC Enabled:

• HP-UX 11 operating system
• 25 MB of available hard disk space
• HP-UX 11 aCC compiled application

C.1.1 The System Information File (.odbc.ini)

In the UNIX environment, there is no ODBC Administrator. To configure a data source, you must edit the system information file, a plain text file that is normally located in the user's $HOME directory and is usually called .odbc.ini. This file is maintained using any text editor to define data source entries as described in the section Configuring and Connecting to Data Sources for each driver. A sample file odbc.ini is located in the driver installation directory

UNIX support of the database drivers also permits the use of a centralized system information file that a system administrator can control. This is accomplished by setting the environment variable ODBCINI to point to the fully qualified pathname of the centralized file.

Set this variable as follows:

C shell:

setenv ODBCINI /opt/odbc/system_odbc.ini

Bourne shell and Korn shell:

ODBCINI=/opt/odbc/system_odbc.ini;export ODBCINI

The search order for the location of the system information file is as follows:

1. Check ODBCINI

2. Check $HOME for .odbc.ini

There must be an [ODBC] section in the system information file that includes the InstallDir keyword. The value of this keyword must be the path to the directory under which the /lib and /messages directories are contained. For example, if you choose the default install directory, then the following line must be in the [ODBC] section:

InstallDir=/opt/odbc

C.1.1.1 Sample System Information File for Solaris

In the following sample, xx represents the driver number:

[ODBC Data Sources]
   Oracle7=Sample Oracle dsn
   dBase=Sample dBASE dsn
   Sybase=Sample Sybase dsn
   Informix=Sample Infofrmix dsn
   OpenIngres=Sample OpenIngres dsn
   DB2=Sample DB2 dsn
   Text=Sample Text file dsn 

[dBase]
   Driver=/opt/odbc/lib/ivdbfxx.so
   Description=dBase
   Database=/opt/odbc/demo 

[Sybase]
   Driver=/opt/odbc/lib/ivsybxx.so
   Description=Sybase
   Database=odbc
   ServerName=SYBASE
   LogonID=odbc01
   Password=odbc01
   OptimizePrepare=2
   SelectMethod=1 

[Oracle7]
   Driver=/opt/odbc/lib/ivor7xx.so
   Description=Oracle7
   ServerName=oraclehost
   LogonID=odbc01
   Password=odbc01

[Informix]
   Driver=/opt/odbc/lib/ivinfxx.so
   Description=Informix7
   Database=odbc
   HostName=informixhost
   LogonID=odbc01
   Password=odbc01  
 
[DB2]
   Driver=/opt/odbc/lib/ivdb2xx.so
   Description=DB2
   Database=ODBC 

[OpenIngres]
   Driver=/opt/odbc/lib/ivoingxx.so
   ServerName=ingreshost
   Database=odbc
   LogonID=odbc01
   Password=odbc01 

[Text]
   Driver=/opt/odbc/lib/ivtxtxx.so
   Description=Text driver
   Database=/opt/odbc/demo 

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

C.1.2 Environment Variables

Connect ODBC drivers require several environment variables to be set.

C.1.2.1 Required Environment Variables

Most of the variables can be set by executing the appropriate shell script located in the ODBC home directory.

Before attempting to use ODBC-enabled applications, execute the following command to initialize your environment:

C shell (and related shell):

% source odbc.csh

Bourne shell (and related shell):

$ . odbc.sh

Executing these scripts will set the appropriate library search path environment variable (LD_LIBRARY_PATH on Solaris, SHLIB_PATH on HP/UX, or LIBPATH on AIX).

The library search path environment variables are required to be set so that the ODBC core components and drivers can be located at the time of execution.

C.1.2.2 Optional Environment Variables

Many of the Connect ODBC drivers must have environment variables set as required by the database client components used by the drivers. Consult the system requirements in each of the individual driver sections for additional information pertaining to individual driver requirements.

ODBCINI is an optional environment variable that all Connect ODBC drivers will recognize. ODBCINI is used to locate a system information file other than the default file and is described in detail in the section The System Information File (.odbc.ini).

C.1.3 Using Double-Byte Character Sets

Connect ODBC drivers are capable of using double-byte character sets. The drivers normally use the character set defined by the default locale "C" unless explicitly pointed to another character set. The default locale "C" corresponds to the 7-bit ASCII character set in which only characters from ISO 8859-1 are valid. Use the following procedure to set the locale to a different character set.

1. Add the following line at the very beginning of applications that use double-byte character sets:

   setlocale (LC_ALL, "");
   

   This is a standard UNIX function. It selects the character set indicated by the environment variable LANG as the one to be used by X/Open compliant character handling functions. If this line is not present, or if LANG is either not set or is set to NULL, the default locale "C" is used.

2. Set the LANG environment variable to the appropriate character set. The UNIX command locale -a can be used to display all supported character sets on your system.

For more information, see the man pages for "locale" and "setlocale."

C.1.3.1 The ivtestlib Tool

The ivtestlib tool is provided to help diagnose configuration problems (such as environment variables not correctly set or missing DBMS client components) in the UNIX environment. This command will attempt to load a specified ODBC driver and will print out all available error information if the load fails.

On HP-UX, for example, if a driver is installed in /opt/odbc, the command:

ivtestlib /opt/odbc/lib/ivinfxx.sl

(where xx represents the driver number) will attempt to load the INFORMIX driver. If the driver cannot be loaded, ivtestlib will return an error message explaining why.

Note: On Solaris and AIX, the full path to the driver does not have to be specified for ivtestlib. The HP-UX version of ivtestlib, however, requires the full path.

C.1.4 Translators

INTERSOLV provides a sample translator named INTERSOLV OEM ANSI that translates provides a framework for coding a translation library.

You must add the TranslationSharedLibrary keyword to the data source section of the system information file to perform a translation. Adding the TranslationOption keyword is optional.

C.2 Connect ODBC for DB2

C.2.1 System Requirements

The following software is required for the client side:

Solaris

• IBM DB2 Client Application Enabler (CAE) for Solaris, version 2.12 or higher
• IBM DB2 Software Development Kit (DB2 SDK) for Solaris, version 2.12 or higher

HP-UX

• IBM DB2 Client Application Enabler (CAE) for HP-UX, version 2.12 or higher
• IBM DB2 Software Development Kit (DB2 SDK) for HP-UX, version 2.12 or higher

AIX

• IBM DB2 Client Application Enabler (CAE) for AIX, version 5.0 or higher
• IBM DB2 Software Development Kit (DB2 SDK) for AIX, version 5.0 or higher

The following software is required for the server side:

• For all UNIX versions, the DB2 Server must be installed as the Server Version (not the Local Version)

C.2.2 DB2 Binding and Priviliges

Access to DB2 requires that you bind and grant privileges to the INTERSOLV bind files, a process described in this section.

Enter the DB2 command line processor by typing db2 from a shell prompt.

Note: The DB2 command processor prompt is db2=>.

Once inside the DB2 command line processor, the first step is to connect your DB2 database using the following syntax:

db2=> CONNECT TO <database_name> USER <userid> USING <password>

C.2.2.1 Binding

The next step is to bind the INTERSOLV SQL files to the database. You may choose to use special options on the BIND command, based on your installation. Consult the manual Command Reference in the DB2 manual set for a detailed list of BIND options. To bind the files, enter the commands listed in the following sections. To exit the DB2 command processor, enter the verb quit.

Solaris

db2=> BIND iscsso.BND blocking all grant public
db2=> BIND isrrso.BND blocking all grant public
db2=> BIND isurso.BND blocking all grant public
db2=> BIND iscswhso.BND blocking all grant public
db2=> BIND isrrwhso.BND blocking all grant public
db2=> BIND isurwhso.BND blocking all grant public

HP-UX

db2=> BIND iscshp.BND blocking all grant public
db2=> BIND isrrhp.BND blocking all grant public
db2=> BIND isurhp.BND blocking all grant public
db2=> BIND iscswhhp.BND blocking all grant public
db2=> BIND isrrwhhp.BND blocking all grant public
db2=> BIND isurwhhp.BND blocking all grant public

AIX

db2=> BIND iscsax.BND blocking all grant public
db2=> BIND isrrax.BND blocking all grant public
db2=> BIND isurax.BND blocking all grant public
db2=> BIND iscswhax.BND blocking all grant public
db2=> BIND isrrwhax.BND blocking all grant public
db2=> BIND isurwhax.BND blocking all grant public

C.2.3 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for DB2 is:

DSN=DB22 TABLES;DB=PAYROLL;UID=JOHN;PWD=XYZZY;GRP=ACCTNG

The following table gives the long and short names for each attribute, as well as a description.

The defaults listed in the table below are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

C.2.4 Data Types

The following table shows how the DB2 data types map to the standard ODBC data types.

Note: The Graphic, Vargraphic, and Long Vargraphic data types are not supported.

C.2.5 Isolation and Lock Levels Supported

DB2 supports isolation levels 0 (read uncommitted), 1 (read committed), and 2 (repeatable read). It supports record-level locking. See Appendix D Locking and Isolation Levels in the Connect ODBC Reference for a discussion of these topics.

C.2.6 ODBC Conformance Level

The DB2 driver supports the functions listed in Appendix C, ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following X/Open functions are supported:

• SQLProcedures
• SQLProcedureColumns

The driver supports the minimum SQL grammar.

C.2.7 Number of Connections and Statements Supported

The DB2 database system supports a single connection and multiple statements per connection.

C.3 Connect ODBC for dBASE

C.3.1 System Requirements

The dBASE driver supports dBASE IV and V in the UNIX environment.

The dBASE driver runs the SQL statements directly on dBASE- compatible files. You do not need to own dBASE products to access these files.

See the README file shipped with your INTERSOLV DataDirect product for the file name of the dBASE driver.

C.3.2 Defining Index Attributes on UNIX Platforms

Index files for dBASE contain index tags for each index that exists for a database file. These index tags can be marked as unique, that is, the driver will ensure that no duplicate values exist for the columns that define the index tag. The unique attribute is not natively supported by the dBASE or FoxPro products. The enforcement and recognition of the unique attribute is an extension of the INTERSOLV dBASE driver. The driver must be notified that index tags are unique. No configuration is needed for unique indexes that were created using the INTERSOLV dBASE driver. When using files that were not created with the INTERSOLV dBASE driver, you must define unique index tags as explained below.

In the directory where the database and index files are located, use any plain text editor, such as vi, to define or edit the QEDBF.INI as follows:

1. Create a [filename] section where filename is the name of the database file. This entry is case sensitive and the file extension should be included.

2. In the [filename] section, specify the number of unique indexes on the file (NUMUNIQUE=) and the index specifications (UNIQUE#=index_filename,index_tag). The index_tag can be determined by calling the ODBC function SQLStatistics and examining the INDEX_NAME result column.

   For example, to define two unique indexes on the accts.dbf table, the QEDBF.INI would be defined as:

   [accts.dbf]
   

   NUMUNIQUE=2
   UNIQUE0=accts.mdx,ACCT_NAME
   UNIQUE1=accts.mdx,ACCT_ID
   

C.3.3 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for dBASE is:

DSN=DBASE FILES;LCK=NONE;IS=0

The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

C.3.4 Data Types

The table below shows how dBASE data types map to the standard ODBC data types. These dBASE data types can be used in a Create Table statement. For the syntax of the Create Table statement, see Appendix A SQL for Flat-File Drivers in the Connect ODBC Reference.

Note: A few products can create dBASE files with numbers that do not conform to the precision and scale of the Number column. For example, these products can store 100000 in a column declared as NUMBER(5,2). When this occurs, the dBASE driver displays error 1244, "Unsupported decimal format." To remedy this situation, multiply the nonconforming column by 1, which converts it to the Float data type. For example:

SELECT BADCOL * 1 FROM BADFILE

BADCOL * 1 is evaluated as an expression and is returned as a float value.

C.3.5 Column Names

Column names in SQL statements (such as, Select, Insert, etc.) can be up to ten characters long. A column name can contain alphanumeric characters and the hyphen character (-). The first character must be a letter (a through z).

C.3.6 SELECT Statement

You use a SQL SELECT statement to specify the columns and records to be read. dBASE SELECT statements support all of the SELECT statement clauses as described in Appendix A SQL for Flat-File Drivers in the Connect ODBC Reference. This section describes the information that is specific to dBASE, which is Rowid.

C.3.6.1 Rowid Pseudo-Column

Each dBASE record contains a special column named Rowid. This field contains a unique number that indicates the record's sequence in the database. For example, a table that contains 50 records has Rowid values from 1 to 50 (if no records are marked deleted). You can use Rowid in WHERE and SELECT clauses.

Rowid is particularly useful when you are updating records. You can retrieve the Rowid of the records in the database along with the other field values. For example:

SELECT last_name, first_name, salary, rowid FROM 
emp

Then you can use the Rowid of the record that you want to update to ensure that you are updating the correct record and no other. For example:

UPDATE emp set salary = 40000 FROM emp WHERE 
rowid=21

The fastest way of updating a single row is to use a WHERE clause with the Rowid. You cannot update the Rowid column.

SELECT statements that use the Rowid pseudo-column in the WHERE clause achieve maximum performance only for exact equality matches. If you use range scans instead of exact equality matches, a full table scan is performed. For example:

SELECT * FROM emp WHERE rowid=21    //fast search

SELECT * FROM emp WHERE rowid <=25  //full table 
scan

C.3.7 ALTER TABLE Statement

The dBASE driver supports the ALTER TABLE statement to add one or more columns to a table or to delete (drop) a single column.

The ALTER TABLE statement has the form:

ALTER TABLE table_name {ADD column_name data_type 
| ADD

(column_name data_type [, column_name data_type] 
... ) |
DROP [COLUMN] column_name}

where:

For example, to add two columns to the emp table,

ALTER TABLE emp (ADD startdate date, dept char 
(10))

You cannot add columns and drop columns in a single statement, and you can drop only one column at a time. For example, to drop a column,

ALTER TABLE emp DROP startdate

The ALTER TABLE statement fails if you attempt to drop a column upon which other objects, such as indexes or views, are dependent.

C.3.8 CREATE INDEX Statement

The type of index you create is determined by the value of the CreateType attribute, which you set in the system information file or as a connection string option. The index can be

• dBASE (.MDX)
• FoxPro (.CDX)

The syntax for creating an index is

CREATE [UNIQUE] INDEX index_name ON base_table_name 

(field_name [ASC | DESC] [,field_name [ASC | DESC]] ...) 

where:

ASC tells dBASE to create the index in ascending order. DESC tells dBASE to create the index in descending order. By default, indexes are created in ascending order. You cannot specify both ASC and DESC orders within a single CREATE INDEX statement. For example, the following statement is invalid:

CREATE INDEX emp_i ON emp (last_name ASC, emp_id DESC)

The table below shows the attributes of the different index files supported by the dBASE driver. For each type supported, it provides the following details:

• Whether dBASE-style unique indexes are supported

• Whether descending order is supported

• The maximum size supported for key columns

• The maximum size supported for the column specification in the CREATE INDEX statement

• Whether production/structural indexes are supported

C.3.9 DROP INDEX Statement

The syntax for dropping an index is as follows:

DROP INDEX table_name.index_name

where:

C.3.10 PACK Statement

When records are deleted from a dBASE file, they are not removed from the file. Instead, they are marked as having been deleted. Also, when memo fields are updated, space may be wasted in the files. To remove the deleted records and free the unused space from updated memo fields, you must use the PACK statement. It has the following form:

PACK filename

where:

PACK emp

You cannot pack a file that is opened by another user, and you cannot use the PACK statement in manual commit mode.

For the specified file, the PACK statement does the following:

• Removes all deleted records from the file

• Removes the entries for all deleted records from .CDX and .MDX files having the same name as the file

• Compresses unused space in the memo (.DBT or .FPT) file

C.3.11 Locking

With the dBASE driver, you can build and run applications that share dBASE database files on a network. Whenever more than one user is running an application that accesses a shared database file, the applications should lock the records that are being changed. Locking a record prevents other users from locking, updating, or deleting the record.

C.3.11.1 Levels of Database Locking

The dBASE driver supports three levels of database locking: NONE, RECORD, and FILE. You can set these levels in the connection string (LCK=).

No locking offers the best performance but is intended only for single-user environments.

With record or file locking, the system locks the database tables during INSERT, UPDATE, DELETE, or SELECT...FOR UPDATE statements. The locks are released when the user commits the transaction. The locks prevent other users from modifying the locked objects, but they do not lock out readers.

With record locking, only records affected by the statement are locked. Record locking provides better concurrency with other users who also want to modify the table.

With file locking, all the records in the table are locked. File locking has lower overhead and may work better if records are modified infrequently, if records are modified primarily by one user, or if a large number of records are modified.

C.3.11.2 Limit on Number of Locks

There is a limit on the number of locks that can be placed on a file. If you are accessing a dBASE file from a server, the limit depends on the server (see your server documentation).

C.3.11.3 How Transactions Affect Record Locks

When an UPDATE or DELETE statement is run, the driver locks the records affected by that statement. The locks are released after the driver commits the changes. Under manual commit mode, the locks are held until the application commits the transaction. Under autocommit mode, the locks are held until the statement is run.

When a SELECT...FOR UPDATE statement is run, the driver locks a record only when the record is fetched. If the record is updated, the driver holds the lock until the changes are committed. Otherwise, the lock is released when the next record is fetched.

C.3.12 Isolation and Lock Levels Supported

dBASE supports isolation level 1. It supports both file- and record-level locking. For a discussion of these topics, see Appendix D Locking and Isolation Levels in the Connect ODBC Reference.

C.3.13 ODBC Conformance Level

The dBASE driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following function is supported: SQLSetPos.

The dBASE driver also supports backward and random fetching in SQLExtendedFetch and SQLFetchScroll. The driver supports the minimum SQL grammar.

C.3.14 Number of Connections and Statements Supported

dBASE supports multiple connections and multiple statements per connection.

C.4 Connect ODBC for INFORMIX

C.4.1 System Requirements

The environment variable INFORMIXDIR must be set to the directory where you have installed the INFORMIX client, using the following command:

C shell:

setenv INFORMIXDIR /databases/informix

Bourne shell and Korn shell:

INFORMIXDIR=/databases/informix;export INFORMIXDIR

In addition, the INFORMIXSERVER variable must be set to the name of the INFORMIX server (as defined in your $INFORMIXDIR/ext/sqlhosts file). For further details, refer to the Informix Online Dynamic Server Administrator's Guide, Volume 2 or the Informix UNIX Installation Guide.

INFORMIX:

The INFORMIX driver under UNIX requires INFORMIX-Connect or ESQL-C 7.23.

INFORMIX 9:

To access remote INFORMIX 7.x or 9 databases through the INFORMIX 9 driver, you need INFORMIX-Connect or ESQL-C 9.1.3 for Solaris and HP-UX, and INFORMIX-Connect 9.1.4 for AIX.

C.4.2 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for INFORMIX is:

DSN=INFORMIX TABLES;DB=PAYROLL

The following table gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

C.4.3 Data Types

The table below shows how the INFORMIX data types map to the standard ODBC data types.

C.4.3.1 INFORMIX 9

The table below shows how the INFORMIX 9 data types map to the standard ODBC data types. These types are in addition to the INFORMIX data types described in the previous table.

The INFORMIX 9 driver does not support any complex data types (for example, set, multiset, list, and named/unnamed abstract types). When the driver encounters a complex type it will return an Unknown Data Type error (SQL State HY000).

C.4.4 Isolation and Lock Levels Supported

If connected to an Online Server, INFORMIX supports isolation levels 0 (read uncommitted), 1 (read committed), and 3 (serializable). The default is 1. The Standard Engine supports isolation level 0 (read uncommitted) only.

INFORMIX also supports an alternative isolation level 1, called cursor stability. Your ODBC application can use this isolation level by calling SQLSetConnectAttr (1040,1).

Additionally, if transaction logging has not been enabled for your database, then transactions are not supported by the driver (the driver is always in auto-commit mode).

INFORMIX supports page-level and row-level locking.

See Appendix D Locking and Isolation Levels in the Connect ODBC Reference for a discussion of these topics.

C.4.5 ODBC Conformance Level

The INFORMIX driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following X/Open functions are supported:

• SQLProcedures
• SQLColumnPrivileges
• SQLTablePrivileges
• SQLPrimaryKeys
• SQLForeignKeys
• SQLProcedureColumns

The driver also supports scrollable cursors with SQLExtendedFetch or SQLFetchScroll if the connection attribute EnableScrollableCursors is set to 1. The driver supports the core SQL grammar.

C.4.6 Number of Connections and Statements Supported

The INFORMIX driver supports multiple connections and multiple statements per connection to the INFORMIX database system.

C.5 Connect ODBC for OpenIngres

C.5.1 System Requirements

The following section lists requirements for both OpenIngres and OpenIngres 2.

OpenIngres:

To access OpenIngres databases from your client workstation you must have the OpenIngres/Net Release 1.2 product (int.wnt/03 or higher) installed on your client node.

OpenIngres 2:

To access OpenIngres 2 databases from your client workstation you must have CA OpenIngres Net version 2.0 or greater installed on your client node.

OpenIngres and OpenIngres 2:

The remote or host OpenIngres databases must be INGRES 1.2 or later.

You must have the environment variable II_SYSTEM set to the directory above the directory where you installed the INGRES client.

For example, if you have installed your INGRES product in /databases/ingres, use:

C shell:

setenv II_SYSTEM /databases

Bourne shell; Korn-shell:

II_SYSTEM=/databases;export II_SYSTEM

C.5.2 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for OpenIngres is:

DSN=INGRES TABLES;SRVR=QESERV;DB=PAYROLL;UID=JOHN

The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

C.5.3 Data Types

The table below shows how OpenIngres data types map to the standard ODBC data types.

Note: OpenIngres Date values do not directly map to the ODBC type SQL_TYPE_TIMESTAMP. If interval data is placed in Date columns, then the driver raises an error when attempting to read the value.

C.5.4 Isolation and Lock Levels Supported

OpenIngres supports isolation level 1 (read committed, the default). OpenIngres supports page-level locking. For a discussion of these topics, see Appendix D Locking and Isolation Levels in the Connect ODBC Reference.

C.5.5 ODBC Conformance Level

The OpenIngres driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. SQLProcedures and SQLProcedureColumns are supported unless SQLGrammar=1.

The driver supports the minimum SQL grammar.

C.5.6 Number of Connections and Statements Supported

The OpenIngres database system supports multiple connections and multiple statements per connection.

Note that if you set the RepeatedSelects connection string attribute to 1 or 2, the driver is limited to one active statement and one active connection.

C.6 Connect ODBC for Oracle

C.6.1 System Requirements

Both Oracle and Oracle8 client information for UNIX is listed below.

Oracle and Oracle8:

Before you can use the Oracle data source, you must have the Oracle SQL*Net or Net8 drivers you plan to use installed on your workstation in the $ORACLE_HOME source tree. ORACLE_HOME is an environment variable created by the Oracle installation process that identifies the location of your Oracle client components.

Oracle refers to the runtime Oracle component as "Oracle RDBMS." From the Oracle RDBMS product, the Oracle driver depends on the executables in $ORACLE_HOME/bin and the interface libraries in $ORACLE_HOME/rdbms/lib.

Set the environment variable ORACLE_HOME to the directory where you installed the Oracle RDBMS, SQL*Net, or Net8 product. For example:

C shell:

setenv ORACLE_HOME /databases/oracle

Bourne shell and Korn shell:

ORACLE_HOME=/databases/oracle;export ORACLE_HOME

Building the required Oracle7 SQL*Net Shared Library:

The Oracle driver requires a one-time site linking to build an Oracle7 SQL*Net driver on AIX and, for Oracle7.1 only, on Solaris and HP-UX. This site linking binds your unique Oracle7 SQL*Net configuration into the file, which is used by the Oracle driver to access local and remote Oracle databases.

Before you build the Oracle7 SQL*Net shared library, install Oracle and set the environment variable ORACLE_HOME to the directory where you installed Oracle. Connect ODBC provides a script, genclntsh, that builds the Oracle7 SQL*Net driver. This script is in the scr/oracle directory.

The following command builds the Oracle7 SQL*Net shared library:

genclntsh

Building the Required Oracle Net8 Shared Library on Solaris:

Under Oracle8.0.3 or 8.0.4 for Solaris, the Oracle8 driver requires a one-time site linking to build a replacement Oracle Net8 driver. This site linking binds your unique Oracle Net8 configuration into the file, which is used by the Oracle driver to access local and remote Oracle databases.

The Oracle8 driver requires the shared library libclntsh.so, which is built by the Oracle script genclntsh. The genclntsh script provided by Oracle causes an error resulting from the undefined symbol slpmprodstab. Oracle8 users must therefore use the genclntsh8 script provided with Connect ODBC to build a replacement libclntsh.so. This script, in the scr/oracle directory, places the new libclntsh.so in ../../lib, which is your
$ODBC_HOME/lib directory; it does not overwrite the original libclntsh.so in the $ORACLE_HOME/lib directory.

Before you build the Oracle Net8 shared library, install Oracle and set the environment variable ORACLE_HOME to the directory where you installed Oracle.

The following command builds the Oracle Net8 shared library:

genclntsh8

Warning: Oracle8 users will have the original libclntsh.so library in the $ORACLE_HOME/lib directory. Therefore, the $ODBC_HOME/lib directory, containing the correct library, must be on the LD_LIBRARY_PATH before $ORACLE_HOME/lib. Otherwise, the original Oracle library will be loaded, resulting in the unresolved symbol error.

C.6.2 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for Oracle is:

DSN=Accounting;SRVR=X:QESRVR;UID=JOHN;PWD=XYZZY

If the server name contains a semicolon, enclose it in quotation marks:

DSN=Accounting;SRVR="X:QE;SRVR";UID=JOHN;PWD=XYZZY

The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

C.6.2.1 Connecting via Operating System Parameters

Oracle has a feature that allows you to connect to Oracle via the operating system user name and password. To connect, use a slash ( / ) for the user name and leave the password blank. To configure the Oracle server/client, refer to the Oracle server documentation.

driver_prefix:computer_name[:sid]

database_name

TNSNAME

C.6.3 Oracle Data Types

The table below shows how the Oracle data types are mapped to the standard ODBC data types.

Oracle8:

The table below shows how the Oracle8 data types are mapped to the standard ODBC data types. These are in addition to the Oracle data types described above.

The Oracle8 driver does not support any Abstract Data Types. When the driver encounters an Abstract Data Type during data retrieval, it will return an Unknown Data Type error (SQL State HY000). It also does not support asynchronous operations, due to constraints in the current Oracle8 client.

C.6.4 Stored Procedure Results

When the option Procedure Returns Results is active, the driver returns result sets from stored procedures/functions. In addition, SQLGetInfo(SQL_MULT_RESULTS_SETS) will return "Y" and SQLGetInfo(SQL_BATCH_SUPPORT) will return SQL_BS_SELECT_PROC. If this option is on and you execute a stored procedure that does not return result sets, you will incur a small performance penalty.

This feature requires that stored procedures be in a certain format. First, a package must be created to define all of the cursors used in the procedure, then the procedure can be created using the new cursor. For example:

Create or replace package GEN_PACKAGE as CURSOR G1 is select 
CHARCOL from GTABLE2;
type GTABLE2CHARCOL is ref cursor return G1%rowtype;
end GEN_PACKAGE;

Create or replace procedure GEN_PROCEDURE1 (rset IN OUT GEN_PACKAGE.GTABLE2CHARCOL, icol INTEGER) as begin
open rset for select CHARCOL from GTABLE2 where INTEGERCOL <= icol order by INTEGERCOL; end;

For more information consult your Oracle SQL manual.

C.6.5 Isolation and Lock Levels Supported

Oracle supports isolation level 1 (read committed) and isolation level 3 (serializable isolation--if the server version is Oracle7.3 or greater or Oracle8.x). Oracle supports record-level locking.

For a discussion of these topics, see Appendix D Locking and Isolation Levels in the Connect ODBC Reference.

C.6.6 ODBC Conformance Level

The Oracle driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. The Oracle driver also supports SQLDescribeParam if EnableDescribeParam=1. If EnableScrollableCursors=1, it supports SQLSetPos and also scrollable cursors with SQLFetchScroll and SQLExtendedFetch.

The Oracle driver supports the following X/Open level functions:

• SQLProcedures
• SQLProcedureColumns
• SQLPrimaryKeys
• SQLForeignKeys
• SQLTablePrivileges
• SQLColumnPrivileges

The driver supports the core SQL grammar.

C.6.7 Number of Connections and Statements Supported

The Oracle driver supports multiple connections and multiple statements per connection.

C.7 Connect ODBC for Sybase

C.7.1 System Requirements

Before you can use the System data source, you must have the Sybase Open Client Net-Libraries you plan to use installed on your workstation in the $SYBASE source tree.

Set the environment variable SYBASE to the directory where you installed the System client, using:

C-shell:

setenv SYBASE /databases/sybase

Bourne shell and Korn shell:

SYBASE=/databases/sybase;export SYBASE

You must include the directory containing the System client-shared libraries in the environment variable LD_LIBRARY_PATH (on Solaris), LIBPATH (on AIX), and SHLIB_PATH (on HP-UX):

C-shell:

setenv LD_LIBRARY_PATH /databases/sybase/
lib:$LD_LIBRARY_PATH

Bourne shell and Korn shell:

LD_LIBRARY_PATH=/databases/sybase/
lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH

In non-DCE environments, users should use the ivsybxx Sybase driver that requires the library libct. For DCE environments, users should use the ivsyb11xx Sybase driver that requires the Sybase 11.1 client library libct_r.

C.7.2 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for Sybase is:

DSN=SYS10 TABLES;SRVR=QESRVR;DB=PAYROLL;UID=JOHN;PWD=XYZZY

The following table gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

sp_configure "maximum network packet size", 
5120

reconfigure

Restart Sybase Server

C.7.3 Data Types

The following table shows how the Sybase data types are mapped to the standard ODBC data types.

C.7.4 Isolation and Lock Levels Supported

Sybase supports isolation levels 0 (if the server version is 11 or higher), 1 (read committed, the default), and 3 (serializable). It supports page-level locking. See Appendix D Locking and Isolation Levels in the Connect ODBC Reference for a discussion of these topics.

C.7.5 ODBC Conformance Level

The Sybase driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following functions are supported:

• SQLColumnPrivileges
• SQLForeignKeys
• SQLPrimaryKeys
• SQLProcedureColumns
• SQLProcedures
• SQLTablePrivileges

The driver supports the minimum SQL grammar.

C.7.6 Number of Connections and Statements Supported

The Sybase database system supports multiple connections and multiple statements per connection. If SelectMethod=1, Sybase data sources are limited to one active statement in manual commit mode.

C.8 Connect ODBC for Text

The Text driver supports ASCII text files. These files can be printed directly or edited with text editors or word processors, because none of the data is stored in a binary format.

See the README file shipped with your INTERSOLV DataDirect product for the file name of the text driver.

The Text driver executes SQL statements directly on the text files. The driver supports Insert statements, and inserts the record at the end of the file. You can execute Update and Delete statements conditionally.

C.8.1 Formats for Text Files

Some common formats for text files are listed in the table below.

Comma-, tab-, and character-separated files are called character-delimited files because values are separated by a special character.

C.8.2 Defining Table Structure on UNIX Platforms

Because text files do not all have the same structure, the driver provides the option to define the structure of an existing file. Although defining the structure is not mandatory, because the driver can attempt to guess the names and types of the columns, this feature is extremely useful.

To define the structure of a text file, you create a QETXT.INI file using any plain text editor, such as vi. The filename must be in uppercase. All of the tables you wish to define are specified in the QETXT.INI file. When you specify table attributes in QETXT.INI, you override the attributes specified in the system information file or in the connection string.

Define the QETXT.INI file as follows:

1. Create a [Defined Tables] section and list all of the tables you are defining. Specify the text filename (in either upper- or lowercase, depending on the file) followed by the name you want to give the table, for example:

   emptxt.txt=EMP 
   

   Table names can be up to 32 characters in length and cannot be the same as another defined table in the database. This name is returned by SQLTables. By default, it is the filename without its extension.

2. For each table listed in the [Defined Tables] section, you must specify the text file (FILE=), the table type (TT=), whether the first line of the file contains column names (FLN=), and the delimiter character (DC=).

   Specify the text filename. For example:

   FILE=emptxt.txt 
   

   To define the table type, specify how the fields are separated (comma, tab, fixed, or character). For example:

   TT=COMMA 
   

   If the table type is CHARACTER, specify the delimiter character. For example, if the fields are separated by semicolons,

   DC=;
   

   Specify whether the first line of the file contains column names, using 1 for yes and 0 for no. For example:

   FLN=0
   

3. Define the fields in the table, beginning with FIELD1. For each field, specify the field name, field type, precision, scale, length, offset (for fixed tables), and date/time mask. See the DATE MASKS section for information about masks.

   Separate the values with commas. For example, to define 2 fields,

   FIELD1=EMP_ID,VARCHAR,6,0,6,0,
   

   FIELD2=HIRE_DATE,DATE,10,0,10,0,m/d/yy
   

4. Save the file as QETXT.INI. The driver looks for this file in the directory specified by the "Database" attribute in odbc.ini, or in the current directory.

C.8.2.1 Example of QETXT.INI

The following is an example of a QETXT.INI file. This file defines the structure of the emptext.txt file, which is a sample data file shipped with the DataDirect ODBC Text file.

[Defined Tables]

emptext.txt=EMP

[EMP]
FILE=emptext.txt
FLN=1
TT=Comma
Charset=ANSI
FIELD1=FIRST_NAME,VARCHAR,10,0,10,0,
FIELD2=LAST_NAME,VARCHAR,9,0,9,0,
FIELD3=EMP_ID,VARCHAR,6,0,6,0,
FIELD4=HIRE_DATE,DATE,10,0,10,0,m/d/yy
FIELD5=SALARY,NUMERIC,8,2,8,0,
FIELD6=DEPT,VARCHAR,4,0,4,0,
FIELD7=EXEMPT,VARCHAR,6,0,6,0,
FIELD8=INTERESTS,VARCHAR,136,0,136,0,

C.8.3 Date Masks

Date masks tell the driver how a date is stored in a text file. When a value is inserted into a text file, the date is formatted so that it matches the mask. When reading a text file, the driver converts the formatted date into a date data type.

The table below lists the symbols to use when specifying the date mask.

The table below shows some example date values, masks, and how the date appears in the text file.

C.8.4 Configuring and Connecting to Data Sources

To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for text files is:

DSN=TEXT FILES;TT=CHARACTER;DC=&

The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

Note: The ScanRows, TableType, Delimiter, FirstLineNames, and Charset attributes apply to tables that have not been defined. These attributes also determine the characteristics of new tables created with the Create Table statement.

C.8.5 Data Types

The table below shows how the text file data types are mapped to the standard ODBC data types.

C.8.6 SELECT Statement

You use the SQL SELECT statement to specify the columns and records to be read. The driver supports all SELECT statement clauses as described in Appendix A SQL for Flat-File Drivers in the Connect ODBC Reference.

C.8.7 ALTER TABLE Statement

The Text driver supports the ALTER TABLE statement to add one or more columns to a table or to delete (drop) a single column.

The ALTER TABLE statement has the form:

ALTER TABLE table_name

{ADD column_name data_type
| ADD (column_name data_type
[,column_name data_type] ...)
| DROP [COLUMN] column_name
}

where:

For example, to add two columns to the emp table,

ALTER TABLE emp (ADD startdate date, dept 
varchar(10))

You cannot add columns and drop columns in a single statement, and you can drop only one column at a time. For example, to drop a column,

ALTER TABLE emp DROP startdate

The ALTER TABLE statement fails when you attempt to drop a column upon which other objects, such as indexes or views, are dependent.

C.8.8 ODBC Conformance Level

The Text driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following function is supported: SQLSetPos.

The Text driver also supports backward and random fetching in SQLExtendedFetch and SQLFetchScroll. The driver supports the minimum SQL grammar.

C.8.9 Number of Connections and Statements Supported

Text files support multiple connections and multiple statements per connection.