Contents

The Visigenic Oracle7 driver conforms to the Open Database Connectivity (ODBC) specification described in the ODBC Programmer's Reference (Version 2.0) for your platform. The Oracle7 driver allows you to connect your ODBC-compliant application to an Oracle7 database.

This help file describes how to set up, configure, and use the Oracle7 driver so that it can connect to a data source. It includes these major sections:

Setting Up the Oracle7 Driver

Adding and Modifying Data Sources

Connecting to a Data Source

Connection String Attributes

ODBC Conformance Levels

Mapping Data Types


Setting Up the Oracle7 Driver

See also:	System Requirements

Setting the ORACLE_HOME Environment Variable (UNIX)

The name of the Oracle7 driver varies by platform:

VSORAC.DLL for Windows

vsorac.so for UNIX. However, for some platforms, the driver has an extension to indicate the platform; for example, the driver for Solaris is named vsorac.so.1.

Although the Oracle7 driver was installed during the installation procedure, you must perform the following setup activities:

Confirm that your system has the appropriate software installed.

Set the ORACLE_HOME environment variable to reflect the directory path. (UNIX)


System Requirements

To use the Oracle7 driver, you must have the following Oracle software installed on your system:

ORACLE7 Server

ORACLE SQL*Net (if you want to access remote databases)

For information on Oracle products, refer to your Oracle documentation set.


Setting the ORACLE_HOME Environment Variable (UNIX)

Set the ORACLE_HOME environment variable to the full path of the directory where your Oracle7 Server software is installed. In the C shell, you can set this variable using the setenv command at the command line or in your .login or .cshrc files. In the Bourne or Korn shells, you can set this variable using the export command at the command line or in your .login or .profile files. If you set this variable at the command line, you must reset it whenever you log into your system. If you set this variable in a file, it is set automatically when you log into your system. For example, if your Oracle directory path is /apps/Oracle, in the C shell you could add the following command line to your .cshrc file:

setenv ORACLE_HOME /apps/Oracle

In the Bourne or Korn shells, you could add the following command line to your .login or .profile file:

ORACLE_HOME/apps/Oracle; export ORACLE_HOME


Adding and Modifying Data Sources

See also:	Adding and Modifying Data Sources for Windows

Adding and Modifying Data Sources for UNIX

A data source identifies a path to data that may include a network library, server, database, and other attributesin this case, the data source is the path to an Oracle7 database. In order to connect to a data source, the Driver Manager looks at your ODBC.INI file for specific connection information.

The ODBC.INI file is an initialization file used by the ODBC Driver Manager and ODBC drivers. This file contains information about each data source and its associated driver. Before you can connect to a data source, its connection information must be added to this file. The following sections contain platform-specific information.


Adding and Modifying Data Sources for Windows

See also:	Adding a data source for Windows

Modifying a data source for Windows

If you're developing on Windows, you add and configure data sources using the ODBC Administrator. The ODBC Administrator then updates your ODBC.INI file, located in the WINDOWS directory, to reflect your data source connection information. As you add data sources, the ODBC Administrator adds the information to this file for youthat is, you should never modify the ODBC.INI file directly.


Adding a data source for Windows

1	To start the ODBC Administrator, double-click the ODBC icon in the Windows Control Panel.  The Data Sources dialog box appears.

2	When you see the Data Source dialog box, click the Add button.  The Add Data Source dialog box appears.

3	Select the Oracle7 driver, and then click OK.  The Visigenic ODBC Oracle Setup dialog box appears.

4	In the Data Source Name box, enter the name of the data source you want to access.  The data source name is defined by youthat is, it can be any name that you choose.

5	In the Description box, enter the description for the driver.  This is an optional field that describes the database driver that the data source connects to. The data source description is defined by youthat is, it can be any string that you choose.

6	In the User Name box, enter your database user name. The user name is your database user id.

7	In the SQL*Net Connect String box, enter the SQL*Net connect string for the Oracle7 Server engine.  The SQL*Net connect string identifies the Oracle7 Server engine that you want to access.

8	Click OK to add this data source.  When you click OK, the Data Sources dialog box appears.  After you click OK, the ODBC Administrator updates your ODBC.INI file. The User Name and SQL*Net connect string that you enter become the default data source connection values for this data source. That is, when you connect to the data source using either a dialog box or connection string, these values become the default entries for the data source connection.

9	Click Add to add another data source or click Close to exit.


Modifying a data source for Windows

1	Invoke the ODBC Administrator.  The Data Sources dialog box appears.

2	In the Data Sources dialog box, select the Oracle data source you want to modify and then click the Setup button.  The Visigenic ODBC Oracle Setup dialog box appears.

3	Modify the applicable data source fields, and then click OK.

When you have finished modifying the information in this dialog box, the ODBC Administrator updates your ODBC.INI file. The fields that make up this dialog box are described in "Adding a data source for Windows.


Adding and Modifying Data Sources for UNIX

See also:	Adding a data source for UNIX

Modifying a data source for UNIX

Visigenic supplies a template for this file named odbc.ini, located in the ODBC root directory. Before using an ODBC driver, you should copy this file to your home directory and rename it .odbc.ini. To connect to a data source, you must add an entry for that data source in your odbc.ini file.


Adding a data source for UNIX

See also:	Adding an Entry for a Data Source

Adding a Data Source Specification Section

.odbc.ini Sample Entry

1	Edit your odbc.ini file using a text editor such as vi.  If you do not have this file in your home directory, copy the default odbc.ini file from the ODBC root directory and change the name to odbc.ini.

2	Under the [ODBC Data Sources] section, add an entry for your data source.

3	After the [ODBC Data Sources] section, add an entry for each data source that you specified.  Each data source listed in the ODBC Data Sources section of your odbc.ini file requires a data source specification.


Adding an Entry for a Data Source

Each entry in the Data Sources section of the .odbc.ini file lists the data source and a description of the driver that the data source uses. Use the following format for data source entries:

[ODBC Data Sources]
data-source-name=driver-description

Each entry lists the following information:

data-source-name identifies the data source that the Oracle7 driver connects to.  The data-source-name is defined by you---that is, it can be any name that you choose.

driver-description describes the driver that the data source connects to.  This is an optional field.

For example, to define an employees data source that uses the Oracle7 driver to access an Oracle database, you would make the following entry in the ODBC Data Sources section of your odbc.ini file:

[ODBC Data Sources]
employees=Visigenic Oracle7 Server Driver


Adding a Data Source Specification Section

Use the following format for Oracle7 data source specification entries:

[data-source-name]
Driver=driver-path
Server=sql-net-string
UID=user-name

Each entry lists the following information:

data-source-name is the name of the data source, as specified in the ODBC Data Sources section of your .odbc.ini file.

driver-path is the full path to the Oracle7 Server driver.

sql-net-string is the SQL*Net connect string for the Oracle7 Server you want to access.

user-name is your database user id.

The data-source-name and driver-path are required fields---all other fields are optional.  The sql-net-string and user-name that you enter become the default data source connection values for this data source. If you do not specify the sql-net-string and user-name when you connect to the data source, the values in the odbc.ini data source specification entry are used to make the connection.

Note	The section name for the data source specification must match the data source name listed in the ODBC Data Sources section of your odbc.ini file.

For example, the user Cindy's entry for an Oracle7 Server called employees on the UNIX host mickey using TCP/IP would look like the following:

[Employees]
Driver=/opt/odbc/drivers/vsorac.so.1
Server=t:mickey:employees
UID=cindy

In this example, the data source and server have the same nameemployees. You may want the server and data source names to be the same so that when you connect to a data source, you also know the specific server that you are connecting to.

Note	Data source names are case-insensitive. That is, employees and Employees refer to the same data source.


.odbc.ini Sample Entry

A complete odbc.ini data source entry for the Employees data source described in "Adding a data source for UNIX" would look like the following:

[ODBC Data Sources]
employees=Visigenic Oracle7 Server Driver

[Employees]
Driver=/opt/odbc/drivers/vsorac.so.1
Server=t:mickey:employees
UID=cindy


Modifying a data source for UNIX

Edit your odbc.ini file using a text editor such as vi.  Open your odbc.ini file and modify the appropriate lines in that file. The sections that make up this file are described in "Adding a data source for UNIX.


Connecting to a Data Source

See also:	Connecting to a data source using a dialog box

Connection String Attributes

An ODBC application can pass connection information in a number of ways. For example, the application may have the driver always prompt the user for connection information. Or the application may expect a connection string that specifies the data source connection. How you connect to a data source depends on the connection method that your ODBC applications uses.

One common way of connecting to a data source is through the Data Source dialog box. If your ODBC application is set up to use a dialog box, that dialog box is displayed and prompts you for the appropriate data source connection information.

Another way is through use of the connection string.


Connecting to a data source using a dialog box

1	When you see the Data Source dialog box, select an Oracle data source and then click OK.  A Connect dialog boxes appears.

2	Fill in the appropriate information for the dialog box, and then click OK.

Once the connection information is verified, your application can access the information that the data source contains using the Oracle7 driver.


Connection String Attributes

Some applications may require a connection string that specifies data source connection information instead of using a dialog box to obtain this information. The connection string is made up of a number of attributes that specify how a driver connects to a data source. An attribute identifies a specific piece of information that the driver needs to know before it can make the appropriate data source connection. Each driver may have a different set of attributes but the connection string format is always the same. A connection string has the following format:

DSN=data-source-name[;SERVER=value] [;PWD=value] [;UID=value]

You must specify the data-source-name. However, all other attributes are optional. If you do not specify an attribute, that attribute defaults to the one that is specified in the data source specifications section (for the data source specified in the connection string) of your ODBC.INI file.

The attributes for the connection string are as follows:

DSN	The data source name.  This name is listed in the ODBC Data Sources section and has its own Data Source Specifications section in the .odbc.ini file.
PWD	The password for the Oracle7 server that you want to access.
SERVER	The SQL*Net connect string for the Oracle7 Server that you want to access.
UID	The Oracle7 Server user name.  Depending on your system, this attribute may not be optional---that is, certain databases and tables may require this attribute for security purposes.
For example, a connection string that connects to the Employees data source using the employees server, mickey host machine, and TCP/IP network would have the following attributes in the connection string:

"DSN=Employees;SERVER=t:mickey:employees;UID=cindy;PWD=secret"


ODBC Conformance Levels

See also:	API Conformance Level

SQL Conformance Level

ODBC defines two types of conformance standards for driversthe API conformance standard and the SQL grammar conformance standard. API conformance refers to the functions that a driver supports. SQL conformance refers to the SQL grammar that the driver supports. Each conformance standard is made up of levels.


API Conformance Level

See also:  Supported Options

The Oracle7 driver supports the Core and Level 1 API. The driver also supports the following Level 2 functions:

SQLBrowseConnect()

SQLDataSources()

SQLDrivers()

SQLForeignKeys()

SQLNativeSql()

SQLNumParams()

SQLPrimaryKeys()

SQLSetScrollOptions()


Supported Options

The driver supports the following options for the SQLGetConnectOption() and SQLSetConnectOption() Level 1 functions:

SQL_ACCESS_MODE (SQLGetConnectOption() only)

SQL_AUTOCOMMIT

SQL_ODBC_CURSORS

SQL_OPT_TRACE

SQL_OPT_TRACEFILE

SQL_TRANSLATE_DLL

SQL_TRANSLATE_OPTION

SQL_TXN_ISOLATION

The driver supports the following options for the SQLGetStmtOption() and SQLSetStmtOption() Level 1 functions:

SQL_BIND_TYPE

SQL_CONCURRENCY

SQL_CURSOR_TYPE

SQL_ROWSET_TYPE

SQL_SIMULATE_CURSOR


SQL Conformance Level

The Oracle7 driver supports the Minimum SQL grammar and Core SQL grammar listed in "SQL Conformance Levels" on page 1-9. The Oracle7 driver also supports the following ODBC extensions to SQL:

Date, time, and timestamp data

Outer joins

The following numeric functions:

abs	floor	pi	sin
ceiling	log	power	sqrt
cos	log10	round	tan
exp	mod	sign	truncate
The following date functions:

curdate	dayofweek	monthname	second
curtime	dayofyear	minute	week
dayname	hour	now	year
dayofmonth	month	quarter
The following string functions:

ascii	left	right	ucase
char	length	rtrim
concat	ltrim	soundex
lcase	replace	substring
The following system functions:

ifnull (UNIX)	user


Mapping Data Types

The Oracle7 Server supports a set of data types. The Oracle7 driver maps these data types to their appropriate ODBC SQL data types. The following table lists the Oracle7 Server data type and its corresponding ODBC SQL data type.

Oracle7 Server Data Type	ODBC SQL Data Type
CHAR	SQL_CHAR
DATE	SQL_TIMESTAMP
FLOAT	SQL_DOUBLE
LONG	SQL_LONGVARCHAR
LONG RAW	SQL_LONGVARBINARY
NUMBER	SQL_DECIMAL
RAW	SQL_BINARY
VARCHAR2	SQL_VARCHAR
Note	ODBC SQL data types do not support the MLSLABEL Oracle7 data type.