OS/2 Shareware BBS: 10 Tools/10-Tools.zip/odbc_hlp.zip/odbc_doc.zip/ivdrv08.inf

OS/2 Help File | 1996-01-09 | 137KB | 5,071 lines

ΓòÉΓòÉΓòÉ 1. About INTERSOLV DataDirect Database Drivers ΓòÉΓòÉΓòÉ

The INTERSOLV DataDirect ODBC drivers are compliant with the Microsoft Open
Database Connectivity (ODBC) specification.  ODBC is a specification for an
application program interface (API) that enables applications to access
multiple database management systems using Structured Query Language (SQL).

ODBC permits maximum interoperability--a single application can access many
different database management systems.  This enables an ODBC developer to
develop, compile, and ship an application without targeting a specific type of
data source.  Users can then add the database drivers, which link the
application to the database management systems of their choice.


ΓòÉΓòÉΓòÉ 1.1. Environment-Specific Information ΓòÉΓòÉΓòÉ

On OS/2 systems, the ODBC drivers are 32-bit drivers.  The required network
software supplied by your database system vendors must be 32-bit compliant.
Consult the System Requirements topic for specific requirements for each
relational database driver.

OS/2 version 2.x is required to use the ODBC drivers.

ODBC.INI

OBDC.INI is an operating system binary INI file located in the OS/2 system
directory.  The structure of this file is described in the OBDC.INI topic.
Since this file is binary, you cannot edit it with a text editor.

Starting the ODBC Administrator

The Configuring Data Sources topic in each driver section instructs you to
start the ODBC Administrator.  To start the ODBC Administrator under OS/2,
double-click the ODBC Administrator icon. If you are using ODBC Pack, this icon
is in the ODBC Pack folder.  If you are using the INTERSOLV DataDirect
Developers Toolkit, this icon is in the folder for that product.

Driver Names

The file extension for the ODBC drivers is .DLL.  This indicates that they are
dynamic link libraries.  You must modify the CONFIG.SYS file to add the fully
qualified path to the environment variable LIBPATH. For example, if you install
the ODBC drivers in the directory C:\ODBC, then the fully qualified path is
C:\ODBC. This enables your applications to dynamically load the ODBC drivers at
runtime.


ΓòÉΓòÉΓòÉ 1.2. Supported ODBC Functions ΓòÉΓòÉΓòÉ

This topic lists the ODBC API functions that the database drivers support.

All database drivers are ODBC Level 1-compliant--they support all ODBC Core and
Level 1 functions.  A limited set of Level 2 functions is also supported. The
drivers support the functions listed in the following table.  Any additions to
these supported functions or differences in the support of specific functions
are listed in the "ODBC Conformance Level" topic for each individual driver.

Core Functions
SQLAllocConnect
SQLAllocEnv
SQLAllocStmt
SQLBindCol
SQLBindParameter*
SQLCancel
SQLColAttributes
SQLConnect
SQLDescribeCol
SQLDisconnect
SQLDrivers*
SQLError
SQLExecDirect
SQLExecute
SQLFetch
SQLFreeConnect
SQLFreeEnv
SQLFreeStmt
SQLGetCursorName
SQLNumResultCols
SQLPrepare
SQLRowCount
SQLSetCursorName
SQLTransact

Level 1 Functions
SQLColumns
SQLDriverConnect
SQLGetConnectOption
SQLGetData
SQLGetFunctions
SQLGetInfo
SQLGetStmtOption
SQLGetTypeInfo
SQLParamData
SQLPutData
SQLSetConnectOption
SQLSetStmtOption
SQLSpecialColumns
SQLStatistics
SQLTables

Level 2 Functions
SQLDataSources
SQLExtendedFetch (forward scrolling only)
SQLMoreResults
SQLNativeSql
SQLNumParams
SQLParamOptions*
SQLSetScrollOptions


ΓòÉΓòÉΓòÉ 1.3. ODBC.INI ΓòÉΓòÉΓòÉ

ODBC.INI Examples

On OS/2 systems, ODBC.INI resides in the same directory that is pointed to by
the USER_INI environment variable in your CONFIG.SYS file. ODBC.INI a binary
file that you can maintain by the ODBC Administrator or an INI editor.

An INI file contains a [section_name] heading followed by optional
attribute=value pairs, called entries. The section name and attributes are case
sensitive on OS/2. You cannot put comments in the ODBC.INI file.

The ODBC.INI format, as specified by the Microsoft Open Database Connectivity
(ODBC) specification, is as follows:

[ODBC Data Sources]

Lists data sources available to ODBC.

ds_name1=driver_desc1

ds_name2=driver_desc2

 . .

Lists each data source name followed by a description.

[ds_name1]

Defines the actual ODBC Driver source for example, Oracle.

Driver=path\dll

Defines the path to the driver DLL.

Description=desc

Briefly describes the data source

 . . .

[ds_name2]

Driver=path\dll

Description=desc

 . . .

The [ODBC Data Sources] section is mandatory.  It provides the driver manager
with a list of data sources that are supported for your connection requests.
You can change the names in this list, but each entry must match its
corresponding [ds_name] section in ODBC.INI.

The [ds_name] sections contain a Driver= specification, which points to the
location of the installed driver, as well as a Description= specification that
describes the driver.  If you change the location of a driver, you can change
the Driver= specification to match the new location.  You can also use just the
name of the driver, and the driver manager will attempt to locate the driver
based on information obtained from your environment.

You might need to assign other entries depending on the data source you are
configuring.  The "Connecting to a Data Source Using a Connection String" topic
for each individual driver lists the attributes that you can set.  Use the ODBC
Administrator program to modify ODBC.INI in all environments that provide this
interface. This protects the file from becoming corrupted or nonfunctional.


ΓòÉΓòÉΓòÉ 1.4. ODBC.INI Examples ΓòÉΓòÉΓòÉ

The following example shows an ODBC.INI file as defined by the ODBC
specification:

;---------------------------------------------------

; ODBC.INI - INTERSOLV ODBC Driver Manager INI File

;---------------------------------------------------

[ODBC Data Sources]

ivss=INTERSOLV SQL Server

ivor7=INTERSOLV Oracle

[SQL Server]

Driver=ivss08.dll

Description=INTERSOLV SQL Server driver

ServerName=alice

LogonID=test

[Oracle]

Driver=ivor708.dll

Description=INTERSOLV Oracle driver

ServerName=t:magna:V7

LogonID=test


ΓòÉΓòÉΓòÉ 1.5. Error Messages ΓòÉΓòÉΓòÉ

Error messages may come from

Γûá An ODBC driver
Γûá The database system
Γûá The driver manager

An error reported on an ODBC driver has the following format:

[vendor] [ODBC_component] message

ODBC_component is the component in which the error occurred.  For example, an
error message from INTERSOLV's SQL Server driver would look like this:

[INTERSOLV] [ODBC SQL Server driver] Login incorrect.

If you get this type of error, check the last ODBC call your application made
for possible problems or contact your ODBC application vendor.

An error that occurs in the data source includes the data source name, in the
following format:

[vendor] [ODBC_component] [data_source] message

With this type of message, ODBC_component is the component that received the
error from the data source indicated.  For example, you may get the following
message from an Oracle data source:

[INTERSOLV] [ODBC Oracle driver] [Oracle] ORA-0919: specified length too long
for CHAR column

If you get this type of error, you did something incorrectly with the database
system.  Check your database system documentation for more information or
consult your database administrator.  In this example, you would check your
Oracle documentation.

The driver manager is a DLL that establishes connections with drivers, submits
requests to drivers, and returns results to applications.  An error that occurs
in the driver manager has the following format:

[vendor] [ODBC DLL] message

vendor can be Microsoft or INTERSOLV.  For example, an error from the Microsoft
driver manager might look like this:

[Microsoft] [ODBC DLL] Driver does not support this function


ΓòÉΓòÉΓòÉ 2. SQL for Flat-File Drivers ΓòÉΓòÉΓòÉ

To enable your application to be portable across other databases, you can use
SQL statements with the flat-file drivers (dBASE and Text).  The database
drivers parse SQL statements and translate them into a form that the database
can understand.

The following SQL statements let you read, insert, update, and delete records
from a database, create new tables, and drop existing tables:

Γûá  Select Statement
Γûá  Create Table Statement
Γûá  Drop Table Statement
Γûá  Insert Statement
Γûá  Update Statement
Γûá  Delete Statement

The following topics describe specific elements of the SQL statements
supported:

Γûá  Aggregate Functions
Γûá  SQL Expressions
Γûá  Reserved Keywords


ΓòÉΓòÉΓòÉ 2.1. Select Statement ΓòÉΓòÉΓòÉ

The form of the Select statement supported by the flat-file drivers is

SELECT [DISTINCT] {* | column_expression, ...}
FROM table_names [table_alias] ...
[ WHERE expr1 rel_operator expr2 ]
[ GROUP BY {column_expression, ...} ][ HAVING expr1 rel_operator expr2 ]
[ UNION [ALL] (SELECT...) ]
[ ORDER BY {sort_expression [DESC | ASC]}, ... ]
[ FOR UPDATE [OF {column_expression, ...}] ]

Click any keyword to see a description of its statement clause.


ΓòÉΓòÉΓòÉ 2.2. Select Clause ΓòÉΓòÉΓòÉ

Follow Select with a list of column expressions you want to retrieve or an
asterisk (*) to retrieve all fields.

SELECT [DISTINCT] {* | column_expression, ...}

column_expression can be simply a field name (for example, LAST_NAME).  More
complex expressions may include mathematical operations or string manipulation
(for example, SALARY * 1.05).  See SQL Expressions for more information.

Separate multiple column expressions with commas (for example, LAST_NAME,
FIRST_NAME, HIRE_DATE).

Field names can be prefixed with the table name or alias.  For example,
EMP.LAST_NAME or E.LAST_NAME, where E is the alias for the table EMP.

The Distinct operator can precede the first column expression.  This operator
eliminates duplicate rows from the result of a query.  For example,

SELECT DISTINCT dep FROM emp

Select statements can also include aggregate functions.


ΓòÉΓòÉΓòÉ 2.2.1. Aggregate Functions ΓòÉΓòÉΓòÉ

Aggregate functions can also be a part of a Select clause.  Aggregate functions
return a single value from a set of records. An aggregate can be used with a
field name (for example, AVG(SALARY)) or in combination with a more complex
column expression (for example, AVG(SALARY * 1.07)).  The column expression can
be preceded by the Distinct operator.  The Distinct operator eliminates
duplicate values from an aggregate expression. For example,

COUNT (DISTINCT last_name)

In this example, only distinct last name values are counted.

The following are valid aggregates:

Aggregate Returns

SUM       The total of the values in a numeric field expression.  For example,
          SUM(SALARY) returns the sum of all salary field values.

AVG       The average of the values in a numeric field expression.  For
          example, AVG(SALARY) returns the average of all salary field values.

COUNT     The number of values in any field expression.  For example,
          COUNT(NAME) returns the number of name values.  When using COUNT with
          a field name, COUNT returns the number of non-null field values.  A
          special example is COUNT(*), which returns the number of records in
          the set, including records with null values.

MAX       The maximum value in any field expression.  For example, MAX(SALARY)
          returns the maximum salary field value.

MIN       The minimum value in any field expression.  For example, MIN(SALARY)
          returns the minimum salary field value.


ΓòÉΓòÉΓòÉ 2.2.2. From Clause ΓòÉΓòÉΓòÉ

The From clause indicates the tables that will be used in the Select statement.
The format of the From clause is

FROM table_names [table_alias]

table_names can be one or more simple table names in the current working
directory or complete pathnames.

table_alias is a name used to refer to this table in the rest of the Select
statement.  Database field names may be prefixed by the table alias. Given the
table specification

FROM emp E

you may refer to the LAST_NAME field as E.LAST_NAME.  Table aliases must be
used if the Select statement joins a table to itself.  For example,

SELECT * FROM emp E, emp F WHERE E.mgr_id = F.emp_id

The equal sign (=) includes only matching rows in the results.  If you are
joining more than one table, you may use LEFT OUTER JOIN, which includes
nonmatching rows in the first table you name. For example,

SELECT * FROM T1 LEFT OUTER JOIN T2 on T1.key = T2.key


ΓòÉΓòÉΓòÉ 2.2.3. Where Clause ΓòÉΓòÉΓòÉ

The Where clause specifies the conditions that records must meet to be
retrieved.  The Where clause contains conditions in the form:

WHERE expr1 rel_operator expr2

expr1 and expr2 may be field names, constant values, or expressions.

rel_operator is the relational operator that links the two expressions.  See
SQL Expressions for more information.

For example, the following Select statement retrieves the names of employees
that make at least $20,000.

SELECT last_name,first_name FROM emp WHERE salary >= 20000


ΓòÉΓòÉΓòÉ 2.2.4. Group By Clause ΓòÉΓòÉΓòÉ

The Group By clause specifies the names of one or more fields by which the
returned values should be grouped.  This clause is used to return a set of
aggregate values.

It has the following form:

GROUP BY column_expressions

column_expressions must match the column expression used in the Select clause.
A column expression can be one or more field names of the database table,
separated by a comma (,) or one or more expressions, separated by a comma (,).
See SQL Expressions for more information.

The following example sums the salaries in each department.

SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id

This statement returns one row for each distinct department ID.  Each row
contains the department ID and the sum of the salaries of the employees in the
department.


ΓòÉΓòÉΓòÉ 2.2.5. Having Clause ΓòÉΓòÉΓòÉ

The Having clause enables you to specify conditions for groups of records ( for
example, display only the departments that have salaries totaling more than
$200,000).  This clause is valid only if you have already defined a Group By
clause.

It has the following form:

HAVING expr1 rel_operator expr2

expr1 and expr2 can be field names, constant values, or expressions. These
expressions do not have to match a column expression in the Select clause.

rel_operator is the relational operator that links the two expressions.  See
SQL Expressions for more information.

The following example returns only the departments whose sums of salaries are
greater than $200,000:

SELECT dept_id, sum(salary) FROM emp
GROUP BY dept_id HAVING sum(salary) > 200000


ΓòÉΓòÉΓòÉ 2.2.6. Union Operator ΓòÉΓòÉΓòÉ

The Union operator combines the results of two Select statements into a single
result.  The single result is all of the returned records from both Select
statements.  By default, duplicate records are not returned.  To return
duplicate records, use the All keyword (UNION ALL).  The form is

SELECT statement
UNION [ALL]
SELECT statement

When using the Union operator, the select lists for each Select statement must
have the same number of column expressions with the same data types and must be
specified in the same order.  For example,

SELECT last_name, salary, hire_date FROM emp
UNION
SELECT name, pay, birth_date FROM person

This example has the same number of column expressions, and each column
expression, in order, has the same data type.

The following example is not valid because the data types of the column
expressions are different (SALARY from EMP has a different data type than
LAST_NAME from RAISES).  This example does have the same number of column
expressions in each Select statement but the expressions are not in the same
order by data type.

SELECT last_name, salary FROM emp
UNION
SELECT salary, last_name FROM raises


ΓòÉΓòÉΓòÉ 2.2.7. Order By Clause ΓòÉΓòÉΓòÉ

The Order By clause indicates how the records are to be sorted.  The form is

ORDER BY {sort_expression [DESC | ASC]}, ...

sort_expression can be field names, expressions, or the positional number of
the column expression to use.

The default is to perform an ascending (ASC) sort.

For example, to sort by LAST_NAME you could use either of the following Select
statements:

SELECT emp_id, last_name, first_name FROM emp
ORDER BY last_name

or

SELECT emp_id, last_name, first_name FROM emp
ORDER BY 2

In the second example, LAST_NAME is the second column expression following
Select, so Order By 2 sorts by LAST_NAME.


ΓòÉΓòÉΓòÉ 2.2.8. For Update Of Clause ΓòÉΓòÉΓòÉ

The For Update Of clause locks the records of the database table selected by
the Select statement.  The form is

FOR UPDATE [OF column_expressions]

column_expressions is a list of field names in the database table that you
intend to update, separated by a comma (,). Note that column_expressions is
optional.

The following example returns all records in the employee database that have a
SALARY field value of more than $20,000.  When each record is fetched, it is
locked.  If the record is updated or deleted, the lock is held until you commit
the change.  Otherwise, the lock is released when you fetch the next record.

SELECT * FROM emp WHERE salary > 20000
FOR UPDATE OF last_name, first_name, salary


ΓòÉΓòÉΓòÉ 2.3. SQL Expressions ΓòÉΓòÉΓòÉ

Expressions are used in the Where clauses, Having clauses, Order By clauses,
Select statements.

Expressions enable you to use mathematical operations as well as character
string and date manipulation operators to form complex database queries.

The most common expression is a simple field name. You can combine a field name
with other expression elements.

Other valid expression elements are as follows:

ΓûáField names

ΓûáConstants

ΓûáExponential notation

ΓûáNumeric operators

ΓûáCharacter operators

ΓûáDate operators

ΓûáRelational operators

ΓûáLogical operators

ΓûáFunctions

The order in which these elements are evaluated is described in Operator
Precedence.


ΓòÉΓòÉΓòÉ 2.3.1. Constants ΓòÉΓòÉΓòÉ

Constants are values that do not change.  For example, in the expression PRICE
* 1.05, the value 1.05 is a constant.

You must enclose character constants in pairs of single (') or double quotation
marks (").  To include a single quotation mark in a character constant enclosed
by single quotation marks, use two single quotation marks together (for
example, 'Don''t').  Similarly, if the constant is enclosed by double quotation
marks, use two double quotation marks to include one.

You must enclose date and time constants in braces ({}), for example,
{01/30/89} and {12:35:10}.  The form for date constants is MM/DD/YY or
MM/DD/YYYY.  The form for time constants is HH:MM:SS.

The logical constants are .T. and 1 for True and .F. and 0 for False.  For
portability, use 1 and 0.


ΓòÉΓòÉΓòÉ 2.3.2. Exponential Notation ΓòÉΓòÉΓòÉ

You may include exponential notation. For example,

SELECT col1, 3.4E+& FROM table1 WHERE calc < 3.4E-6 * col2


ΓòÉΓòÉΓòÉ 2.3.3. Numeric Operators ΓòÉΓòÉΓòÉ

You may include the following operators in numeric expressions:

Operator  Meaning
 +        Addition
 -        Subtraction
 *        Multiplication
 /        Division
 **       Exponeniation
 ^        Exponentiation

The following table shows examples of numeric expressions.  For these examples,
assume SALARY is 20000.

Example             Resulting value
salary + 10000      30000
salary * 1.1        22000
2 ** 3              8

You can precede numeric expressions with a unary plus (+) or minus (-). For
example,

-(salary * 1.1) is -22000.


ΓòÉΓòÉΓòÉ 2.3.4. Character Operators ΓòÉΓòÉΓòÉ

Character expressions may include the following operators:

Operator  Meaning
 +        Concatenation keeping trailing blanks.
 -        Concatenation moving trailing blanks to the end.

The following table shows examples of character expressions.  In the examples,
LAST_NAME is 'JONES  ' and FIRST_NAME is 'ROBERT   '.

Example                       Resulting value
first_name + last_name        'ROBERT   JONES'
first_name - last_name        'ROBERTJONES    '

Note:   Some flat-file drivers return character data with trailing blanks as
shown in the table.  However, you cannot rely on the driver to return blanks.
Therefore, if you want an expression that works with drivers that do and do not
return trailing blanks, use the TRIM function before concatenating strings to
make the expression portable.  For example:

TRIM(first_name) + '' + TRIM(last_name)


ΓòÉΓòÉΓòÉ 2.3.5. Date Operators ΓòÉΓòÉΓòÉ

You may include the following operators in date expressions:

Operator  Meaning
+         Add a number of days to a date to produce a new date.
-         The number of days between two dates, or subtract a number of days
          from a date to produce a new date.

The following chart shows examples of date expressions.  In these examples,
hire_date is {01/30/90}.

Example             Resulting value
hire_date + 5       {02/04/90}
hire_date - {01/01/90} 29
hire_date - 10      {01/20/90}


ΓòÉΓòÉΓòÉ 2.3.6. Relational Operators ΓòÉΓòÉΓòÉ

The relational operators separating the two expressions may be any one of the
following:

Operator  Meaning

=         Equal

<>        Not Equal

>         Greater Than

>=        Greater Than or Equal

<         Less Than

<=        Less Than or Equal

Like      Matching a pattern

Not Like  Not matching a pattern

Is Null   Equal to Null

Is Not Null Not Equal to Null

Between   Range of values between a lower and upper bound.

In        A member of a set of specified values or a member of a subquery.

Exists    True if a subquery returned at least one record.

Any       Compares a value to each value returned by a subquery.  Any must be
          prefaced by =, <>, >, >=, <, or <=. =Any is equivalent to In.

All       Compares a value to each value returned by a subquery. All must be
          prefaced by =, <>, >, >=, <, or <=.

The following list shows some examples of relational operators:

salary <= 40000
dept = 'D101'
hire_date > {01/30/89}
salary + commission >= 50000
last_name LIKE 'Jo%'
salary IS NULL
salary BETWEEN 10000 AND 20000
WHERE salary = ANY (SELECT salary FROM emp WHERE dept = 'D101')
WHERE salary > ALL (SELECT salary FROM emp WHERE dept = 'D101')


ΓòÉΓòÉΓòÉ 2.3.7. Logical Operators ΓòÉΓòÉΓòÉ

Two or more conditions may be combined to form more complex criteria.  When two
or more conditions are present, they must be related by AND or OR. For example,

salary = 40000 AND exempt = 1

The logical NOT operator is used to reverse the meaning. For example,

NOT (salary = 40000 AND exempt = 1)


ΓòÉΓòÉΓòÉ 2.3.8. Operator Precedence ΓòÉΓòÉΓòÉ

As expressions become more complex, the order in which the expressions are
evaluated becomes important.  The following table shows the order in which the
operators are evaluated.  The operators in the first line are evaluated first,
then those in the second line, and so on.  Operators in the same line are
evaluated left to right in the expression.

Order     Operator
1         Unary -, Unary +
2         **
3         *, /
4         +, -
5         =, <>, <, <=, >, >=, Like, Not Like, Is Null, Is Not Null, Between,
          In, Exists, Any, All
6         Not
7         AND
8         OR

The following example shows the importance of precedence:

WHERE salary > 40000 OR
hire_date > {01/30/89} AND
dept = 'D101'

Because AND is evaluated first, this query retrieves employees in department
D101 hired after January 30, 1989, as well as every employee making more than
$40,000, no matter what department or hire date.

To force the clause to be evaluated in a different order, use parentheses to
enclose the conditions to be evaluated first.  For example,

WHERE (salary > 40000 OR hire_date > {01/30/89})
AND dept = 'D101'

retrieves employees in department D101 that either make more than $40,000 or
were hired after January 30, 1989.


ΓòÉΓòÉΓòÉ 2.3.9. Functions ΓòÉΓòÉΓòÉ

The flat-file drivers support a number of functions that you may use in
expressions.  In the following topics, the functions are grouped according to
the type of result they return.

Functions that Return Character Strings

Functions that Return Numbers

Functions that Return Dates


ΓòÉΓòÉΓòÉ 2.3.10. Functions that Return Character Strings ΓòÉΓòÉΓòÉ

The following functions return character string values:

Function  Description

CHR       Converts an ASCII code into a one-character string. For example,
          CHR(67) returns C.

RTRIM     Removes trailing blanks from a string For example, RTRIM('ABC  ')
          returns ABC.

TRIM      Removes trailing blanks from a string. For example, TRIM('ABC  ')
          returns ABC.

LTRIM     Removes leading blanks from a string. For example, LTRIM('  ABC')
          returns ABC.

UPPER     Changes each letter of a string to uppercase.  For example,
          UPPER('Allen') returns ALLEN.

LOWER     Changes each letter of a string to lowercase.  For example,
          LOWER('Allen') returns allen.

LEFT      Returns leftmost characters of a string.  For example,
          LEFT('Mattson',3) returns Mat.

RIGHT     Returns rightmost characters of a string.  For example,
          RIGHT('Mattson',4) returns tson.

SUBSTR    Returns a substring of a string.  Parameters are the string, the
          first character to extract, and the number of characters to extract
          (optional).  For example, SUBSTR('Conrad',2,3) returns onr. Also,
          SUBSTR('Conrad',2) returns onrad.

SPACE     Generates a string of blanks. For example, SPACE(5) returns '    '.

DTOC      Converts a date to a character string. An optional second parameter
          determines the format of the result: For example:

          0 (the default) returns MM/DD/YY

          1 returns DD/MM/YY

          2 returns YY/MM/DD

          10 returns MM/DD/YYYY

          11 returns DD/MM/YYYY

          12 returns YYYY/MM/DD

          An optional third parameter specifies the date separator character.
          If not specified, a slash (/) is used. For example:

          DTOC({01/30/89}) returns 01/30/89

          DTOC({01/30/89}, 0) returns 01/30/89

          DTOC({01/30/89}, 1) returns 30/01/89

          DTOC({01/30/89}, 2,'-') returns 89-01-30

DTOS      Converts a date to a character string using the format YYYYMMDD.

          DTOS({01/23/90}) returns 19900123.

IIF       Returns one of two values. Parameters are a logical expression, the
          true value, and the false value.  If the logical expression evaluates
          to True, the function returns the true value. Otherwise, it returns
          the false value. For example, IIF(salary>20000,'BIG','SMALL') returns
          BIG if  SALARY is greater than 20000.  If not, it returns SMALL.

STR       Converts a number to a character string.  Parameters are the number,
          the total number of output characters (including the decimal point),
          and optionally the number of digits to the right of the decimal
          point.

          STR(12.34567,4) returns 12

          STR(12.34567,4,1) returns 12.3

          STR(12.34567,6,3) returns 12.346

STRVAL    Converts a value of any type to a character string.

          STRVAL('Woltman') returns Woltman

          STRVAL({12/25/53}) returns 12/25/53

          STRVAL (5 * 3) returns 15

          STRVAL (4 = 5) returns 'False'

TIME      Returns the time of day as a string. For example, at 9:49 PM, TIME()
          returns 21:49:00

USERNAME  Returns the name in the UID attribute as a character string.  If
          UID=Allen, USERNAME() returns Allen.


ΓòÉΓòÉΓòÉ 2.3.11. Functions that Return Numbers ΓòÉΓòÉΓòÉ

The following functions return number values:

Function  Description

MOD       Divides two numbers and returns the remainder of the division.  For
          example, MOD(10,3) returns 1

LEN       Returns the length of a string. For example, LEN('ABC') returns 3

MONTH     Returns the month part of a date. For example, MONTH({01/30/89})
          returns 1

DAY       Returns the day part of a date. For example, DAY({01/30/89}) returns
          30

YEAR      Returns the year part of a date. For example, YEAR({01/30/89})
          returns 1989

MAX       Returns the larger of two numbers. For example, MAX(66,89) returns 89

MIN       Returns the smaller of two numbers. For example, MIN(66,89) returns
          66

POW       Raises a number to a power. For example, POW(7,2) returns 49

INT       Returns the integer part of a number. For example, INT(6.4321)
          returns 6

ROUND     Rounds a number. For example, ROUND(123.456, 0) returns 123.
          ROUND(123.456, 2) returns 123.46. ROUND(123.456, -2) returns 100.

NUMVAL    Converts a character string to a number.  If the character string is
          not a valid number, a zero is returned.  For example, NUMVAL('123')
          returns the number 123

VAL       Converts a character string to a number.  If the character string is
          not a valid number, a zero is returned.  For example, VAL('123')
          returns the number 123.


ΓòÉΓòÉΓòÉ 2.3.12. Functions that Return Dates ΓòÉΓòÉΓòÉ

The following functions return date values:

Function  Description

DATE      Returns today's date. If today is 12/25/79, DATE() returns {12/25/79}

TODAY     Returns today's date. If today is 12/25/79, TODAY() returns
          {12/25/79}

DATEVAL   Converts a character string to a date. DATEVAL('01/30/89') returns
          {01/30/89}

CTOD      Converts a character string to a date.  An optional second parameter
          specifies the format of the character string:  0 (the default)
          returns MM/DD/YY, 1 returns DD/MM/YY, and 2 returns YY/MM/DD.
          CTOD('01/30/89') returns {01/30/89}  CTOD('01 /30/89',1) returns
          {30/01/89}

The following examples use some of the number and date functions.

Retrieve all employees that have been with the company at least 90 days:

SELECT first_name, last_name FROM emp
WHERE DATE() - hire_date >= 90

Retrieve all employees hired in January of this year or last year:

SELECT first_name, last_name FROM emp
WHERE MONTH(hire_date) = 1 AND (YEAR(hire_date) = YEAR(DATE())
OR YEAR(hire_date) = YEAR(DATE()) - 1)


ΓòÉΓòÉΓòÉ 2.4. Create Table Statement ΓòÉΓòÉΓòÉ

The Create Table statement is used to create database files.  The form of the
Create Table statement is

CREATE TABLE filename (col_definition[,col_definition, ...])

filename can be a simple filename or a full pathname.  A simple filename is
preferred for portability to other SQL data sources.  If it is a simple
filename, the file is created in the directory you specified as the database
directory in the connection string.  If you did not specify a database
directory in the connection string, the file is created in the directory you
specified as the database directory in ODBC.INI.  If you did not specify a
database directory in either place, the file is created in the current working
directory at the time you connected to the driver.

col_definition is the column name, followed by the data type, followed by an
optional column constraint definition.  Values for column names are database
specific.  The data type specifies a column's data type.

The only column constraint definition currently supported by some flat-file
drivers is "not null."  Not all flat-file tables support "not null" columns.
In the cases where not null is not supported, this restriction is ignored and
the driver returns a warning if "not null" is specified for a column.  The "not
null" column constraint definition is allowed in the driver so that you can
write a database-independent application (and not be concerned about the driver
raising an error on a Create Table statement with a "not null" restriction).

A sample Create Table statement to create an employee database table is

CREATE TABLE emp (last_name CHAR(20) NOT NULL,
first_name CHAR(12) NOT NULL,
salary NUMERIC (10,2) NOT NULL,
hire_date DATE NOT NULL)


ΓòÉΓòÉΓòÉ 2.5. Drop Table Statement ΓòÉΓòÉΓòÉ

The Drop Table statement is used to delete database files.  The form of the
Drop Table statement is

DROP TABLE filename

filename may be a simple filename (EMP) or a full pathname.  A simple filename
is preferred for portability to other SQL data sources.  If it is a simple
filename, the file is dropped from the directory you specified as the database
directory in the connection string.  If you did not specify a database
directory in the connection string, the file is deleted from the directory you
specified as the database directory in ODBC.INI.  If you did not specify a
database directory in either of these places, the file is dropped from the
current working directory at the time you connected to the driver.

A sample Drop Table statement to delete the employee database table is

DROP TABLE emp


ΓòÉΓòÉΓòÉ 2.6. Insert Statement ΓòÉΓòÉΓòÉ

The SQL Insert statement is used to add new records to a database table. With
it, you can specify either of the following:

Γûá  A list of values to be inserted as a new record.

Γûá  A Select statement that copies data from another table to be inserted as a
set of new records.

The form of the Insert statement is

INSERT INTO table_name [(col_name, ...)]
{VALUES (expr, ...) | select_statement}

table_name may be a simple filename or a full pathname.  A simple filename is
preferred for portability to other SQL data sources.

col_name is an optional list of column names giving the name and order of the
columns whose values are specified in the Values clause.  If you omit col_name,
the value expressions (expr) must provide values for all columns defined in the
file and must be in the same order that the columns are defined for the file.

expr is the list of expressions giving the values for the columns of the new
record.  Usually, the expressions are constant values for the columns.
Character string values must be enclosed in single or double quotation marks,
date values must be enclosed in braces {}, and logical values that are
characters must be enclosed in periods (for example, .T. or .F.).

An example of an Insert statement that uses a list of expressions is

INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date)
VALUES ('Smith', 'John', 'E22345', 27500, {4/6/91})

Each Insert statement adds one record to the database table.  In this case, a
record has been added to the employee database table, EMP. Values are specified
for five columns.  The remaining columns in the table are assigned a blank
value, meaning Null.

In addition, this statement supports subqueries, which allows applications to
select rows from one table and insert them into another. The form of an Insert
statement that supports subqueries is:

INSERT INTO table_name [(col_name, ...)] query_expression

table_name may be a simple filename or a full pathname.  A simple filename is
preferred for portability to other SQL data sources.

col_name is an optional list of column names giving the name and order of the
columns whose values are specified in the Values clause.  If you omit

col_name, the value expressions (expr) must provide values for all columns
defined in the file and must be in the same order that the columns are defined
for the file.

expr is the list of expressions giving the values for the columns of the new
record.  Usually, the expressions are constant values for the columns.
Character string values must be enclosed in single or double quotation marks,
date values must be enclosed in braces {}, and logical values that are
characters must be enclosed in periods (for example, .T. or .F.).

An example of an Insert statement that uses a list of expressions is

INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date)
VALUES ('Smith', 'John', 'E22345', 27500, {4/6/91})

Each Insert statement adds one record to the database table.  In this case a
record has been added to the employee database table, EMP.  Values are
specified for five columns. The remaining columns in the table are assigned a
blank value, meaning Null.

select_statement is a query that returns values for each col_name value
specified in the column name list. Using a Select statement instead of a list
of value expressions lets you select a set of rows from one table and insert it
into another table using a single Insert statement.

An example of an Insert statement that uses a Select statement is:

INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary)
SELECT first_name, last_name, emp_id, dept, salary from emp
    WHERE dept = 'D050'

In this type of Insert statement, the number of columns to be inserted must
match the number of columns in the Select statement. The list of columns to be
inserted must corresond to the columns in the Select statement just as it would
to a list of value expressions in the other type of Insert statement. That is,
the first column inserted corresponds to the first column selected; the second
inserted to the second, etc.

The size and data type of these corresponding columns must be compatible. Each
column in the Select list should have a data type that the ODBC driver accepts
on a regular Insert/Update of the corresponding column in the Insert list.
Values are truncated when the size of the value in the Select list column is
greater than the size of the corresponding Insert list column.

The select_statement is evaluated before any values are inserted. This query
cannot be made on the table into which values are inserted.


ΓòÉΓòÉΓòÉ 2.7. Update Statement ΓòÉΓòÉΓòÉ

The SQL Update statement is used to change records in a database file.  The
form of the Update statement supported for flat-file drivers is

UPDATE filename SET col_name = expr, ...
[ WHERE { conditions | CURRENT OF cursor_name } ]

filename may be a simple filename or a full pathname.  A simple filename is
preferred for portability to other SQL data sources.

col_name is the name of a column whose value is to be changed. Several columns
can be changed in one statement.

expr is the new value for the column.  The expression can be a constant value
or a subquery.  Character string values must be enclosed with single or double
quotation marks, date values must be enclosed by braces {}, and logical values
that are letters must be enclosed by periods (for example, .T. or .F.).
Subqueries must be enclosed in parentheses.

The Where clause is any valid clause.  It determines which records are to be
updated.

The Where Current Of cursor_name clause can be used only by developers coding
directly to the ODBC API.  It causes the row at which cursor_name is positioned
to be updated.  This is called a "positioned update."  You must first execute a
Select...For Update statement with a named cursor and fetch the row to be
updated.

An example of an Update statement on the employee table is

UPDATE emp SET salary=32000, exempt=1
WHERE emp_id = 'E10001'

The Update statement changes every record that meets the conditions in the
Where clause.  In this case the salary and exempt status is changed for all
employees having the employee ID E10001.  Since employee IDs are unique in the
employee table, only one record is updated.

An example using a subquery is

UPDATE emp SET salary = (SELECT avg(salary) from emp)
WHERE emp_id = 'E10001'

In this case, the salary is changed to the average salary in the company for
the employee having employee ID E10001.


ΓòÉΓòÉΓòÉ 2.8. Delete Statement ΓòÉΓòÉΓòÉ

The SQL Delete statement is used to delete records from a database table. The
form of the Delete statement supported for flat-file drivers is

DELETE FROM filename
[ WHERE { conditions | CURRENT OF cursor_name } ]

filename may be a simple filename or a full pathname.  A simple filename is
preferred for portability to other SQL data sources.

The Where clause is any valid clause.  It determines which records are to be
deleted.  If you include only the keyword Where, all records in the table are
deleted but the file is left intact.

The Where Current Of cursor_name clause can be used only by developers coding
directly to the ODBC API.  It causes the row at which cursor_name is positioned
to be deleted.  This is called a "positioned delete."  You must first execute a
Select...For Update statement with a named cursor and fetch the row to be
deleted.

An example of a Delete statement on the employee table is

DELETE FROM emp WHERE emp_id = 'E10001'

Each Delete statement removes every record that meets the conditions in the
Where clause.  In this case every record having the employee ID E10001 is
deleted.  Since employee IDs are unique in the employee table, at most one
record is deleted.


ΓòÉΓòÉΓòÉ 2.9. Reserved Keywords ΓòÉΓòÉΓòÉ

The following words are reserved for use in SQL statements. If they are used
for file or column names in a database that you use, you must enclose them in
quotation marks in any SQL statement where they appear as file or column names.

Γûá ALL
Γûá AND
Γûá BETWEEN
Γûá COMPUTE
Γûá CROSS
Γûá DISTINCT
Γûá FOR
Γûá FROM
Γûá FULL
Γûá GROUP
Γûá HAVING
Γûá HERE
Γûá INNER
Γûá INTO
Γûá LEFT
Γûá LIKE
Γûá NATURAL
Γûá NOT
Γûá NULL
Γûá ON
Γûá OPTIONS
Γûá OR
Γûá ORDER
Γûá RIGHT


ΓòÉΓòÉΓòÉ 3. DB2/2 Driver ΓòÉΓòÉΓòÉ

The DB2/2 driver supports the use of the Client Application Enabler (CAE) 1.2
interfaces. Note that the previous DB2/2 driver, QEDBMnn.DLL, is not supported
in this release of INTERSOLV ODBC drivers.

The DB2/2 driver supports the following database systems:

Γûá  DB2/2 for OS/2 (formerly Database Manager for OS/2)
Γûá  DB2/6000
Γûá  SQL/400 (through the DDCS/2 or DDCS/6000 gateway)
Γûá  MVS DB2 (through the DDCS/2 or DDCS/6000 gateway)
Γûá  SQL/DS (through the DDCS/2 or DDCS/6000 gateway)

Note:  The driver filename has changed from QEDBMnn.DLL to IVDB2nn.DLL. The
previous version of this driver does not support all of these database systems
on OS/2.


ΓòÉΓòÉΓòÉ 3.1. DB2/2 System Requirements ΓòÉΓòÉΓòÉ

System requirements are as follows:

Γûá  The DB2/2 Server must be installed as the Server Version for remote clients
to use it. The Local Version supports only OS/2 applications running on the
server.

Γûá  To use the DDCS/2 gateway, you must install the DDCS/2 Multi-User Edition
for remote clients to use it. The Single-User Version supports only OS/2
applications running on the server.

Γûá  For remote database access, DB2/2 requires either Communications Manager
from Extended Services version 1.0 (or higher), Communications Manager/2
version 1.0 (or higher), Network Transport Services/2 version 1.0 (or higher),
or LAN Services.


ΓòÉΓòÉΓòÉ 3.2. Configuring DB2/2 Data Sources ΓòÉΓòÉΓòÉ

To configure a DB2/2 data source, do the following:

 1. Start the ODBC Administrator by double-clicking the ODBC icon in the the
    Windows Control Panel.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV DB2/2 and click OK.

 3. If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 4. Specify values as follows:

   Data Source Name:
             A string that identifies this DB2/2 data source configuration in
             ODBC.INI. Examples include "Accounting" or "DB2/2-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "DB2/2 on Server #1."

   Database Name:
             The name of the database to which you want to connect by default.

             The following values are optional:

   Owner of Catalog Tables:
             On most DB2/2 systems, SYSIBM is the owner of the catalog system
             tables.  If you have read access to the system tables, you do not
             need to change this option.

             If you do not have read access, the system administrator must
             create a view of the system tables in another account and give you
             permission to use that view.  In this case, specify the
             Authorization ID for the account that owns the views of the system
             tables.

             If you are using DDCS/2 to connect to a SQL/400 database, specify
             a comma-separated list of the Collection IDs to which you want to
             have access in your ODBC application.

             If you are using DDCS/2 to connect to a SQL/DS database, specify
             SYSTEM as the owner.

   Groups:
             A value to determine which tables you can access.  Your system
             administrator may have placed you in a "group" of users and
             granted table access to the entire group.  If this is the case,
             specify the names of any groups to which you belong; separate each
             name with a comma.  Alternatively, specify the word ALL so that
             you see all table names even if you cannot access the table.

   Default Authorization ID:
             The default Logon ID used to connect to your DB2/2 database.  A
             Logon ID is required only if security is enabled on your database.
             Your ODBC application may override this value or you may change
             this value in the logon dialog box.

   Cursor Behavior:
             Select Preserve if you want cursors to be held at the current
             position when the transaction ends.  Otherwise, leave this set to
             Close.  Selecting Preserve may impact the performance of your
             database operations.  This setting does not apply to SQL/DS.

             When you select Preserve, the driver returns SQL_CB_PRESERVE from
             SQLGetInfo (SQL_CURSOR_COMMIT_BEHAVIOR). But only Select
             statements and prepared Update or Delete . . . Where Current of
             Cursor statements are preserved when the transaction ends. All
             other prepared statements are closed and deleted.

 5. You must catalog any database you want to access.  To do so, click the
    Catalog Db button.  The Catalog Remote Database dialog box appears.

    In this dialog box, type an alias name, the name of the database, and the
    name of the remote node (like an alias for the remote node where the server
    is installed and the protocol used to access it).  Click Add.  The alias
    name you added will be listed in the Database Name drop-down list in the
    logon dialog box when you exit this dialog box.

    This dialog box also lets you uncatalog remote databases.  To do this,
    select the alias you want to delete from the Alias Name drop-down list and
    click Delete.

 6. Click Catalog Node to provide information about the remote site for the
    DB2/2 databases. In the Catalog Node dialog box, select a node name (only
    names of previously cataloged nodes are available, the name of the server
    or number of the adapter (for NetBIOS nodes), the name of the remote node
    where the DB2 server is installed, and the type of communication protocol
    to use to access the node. Optionally, enter a comment. Click Add.

 7. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

    The translators that are listed in this dialog box are determined by the
    values listed in the ODBC Translators section of your ODBCINST.INI file.

 8. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 3.3. DB2/2 and DDCS/2 Gateway ΓòÉΓòÉΓòÉ

Access to DB2/2 databases, or other databases via the DDCS/2 gateway, requires
that you bind to tables and grant privileges to all users. These processes are
described in the following topics.

Binding

Before you can access a table, you must bind to the database.  You must bind to
every database you intend to access.  To do this, enter the following commands
on the server:

SQLBIND QECSV1.BND database_name
SQLBIND QERRV1.BND database_name
SQLBIND QEURV1.BND database_name
SQLBIND QECSWHV1.BND database_name
SQLBIND QERRWHV1.BND database_name
SQLBIND QEURWHV1.BND database_name

You may need to copy the bind files from the installation directory to the
server.

database_name is the name of the database you want to access.

Note for SQL/DS Users:  For binding to SQL/DS databases , enter only the first
three of these commands.  The files QECSWHV1.BND, QERRWHV1.BND, and
QEURWHV1.BND will not work with SQL/DS.

For SQL/400 Users

If you receive the error

`QSYS.QE' is an undefined name

you must create a collection named QE.  To do this, enter the following command
on the host AS/400:

CREATE COLLECTION QE

Then issue the SQLBIND commands again.

Granting Privileges

Users must be granted the necessary privileges before they can access a
database or table through a gateway.

DB2/2 users are assigned an Authorization ID.  There are three categories of
Authorization IDs:  administrator, local administrator, and user.  The third
category, user, must be granted the privilege to access DB2/2 tables.

To grant the necessary privileges, an administrator must run Query Manager or
the DBM Command Line Interface and execute the appropriate GRANT statement.

For DB2/2 databases and databases that reside on DDCS/2 connecting to DB2:

GRANT EXECUTE, BIND ON PROGRAM QE.QECSV1 TO authorization_list
GRANT EXECUTE, BIND ON PROGRAM QE.QERRV1 TO authorization_list
GRANT EXECUTE, BIND ON PROGRAM QE.QEURV1 TO authorization_list
GRANT EXECUTE, BIND ON PROGRAM QE.QECSWHV1 TO authorization_list
GRANT EXECUTE, BIND ON PROGRAM QE.QERRWHV1 TO authorization_list
GRANT EXECUTE, BIND ON PROGRAM QE.QEURWHV1 TO authorization_list

For databases that reside on the DDCS/2 gateway connecting to SQL/DS:

GRANT RUN ON QE.QECSV1 TO authorization_list
GRANT RUN ON QE.QERRV1 TO authorization_list
GRANT RUN ON QE.QEURV1 TO authorization_list
GRANT RUN ON QE.QECSWHV1 TO authorization_list
GRANT RUN ON QE.QERRWHV1 TO authorization_list
GRANT RUN ON QE.QEURWHV1 TO authorization_list

For databases that reside on the DDCS/2 gateway connecting to SQL/400:

GRANT RUN ON PROGRAM QE.QECSV1 TO authorization_list
GRANT RUN ON PROGRAM QE.QERRV1 TO authorization_list
GRANT RUN ON PROGRAM QE.QEURV1 TO authorization_list
GRANT RUN ON PROGRAM QE.QECSWHV1 TO authorization_list
GRANT RUN ON PROGRAM QE.QERRWHV1 TO authorization_list
GRANT RUN ON PROGRAM QE.QEURWHV1 TO authorization_list

authorization_list is a list of comma-separated Authorization IDs or Group IDs,
or PUBLIC if you want to grant access to all users.


ΓòÉΓòÉΓòÉ 3.4. DB2/6000 and DDCS/6000 Gateway ΓòÉΓòÉΓòÉ

Before you can access a table via DB2/6000 or the DDCS/6000 gateway, you must
bind to the database and grant each user access to it.  To do this, enter the
following commands on the client computer from the DBM Command Line Interface:

BIND QECSV1.BND BLOCKING ALL GRANT authorization_list
BIND QERRV1.BND BLOCKING ALL GRANT authorization_list
BIND QEURV1.BND BLOCKING ALL GRANT authorization_list
BIND QECSWHV1.BND BLOCKING ALL GRANT authorization_list
BIND QERRWHV1.BND BLOCKING ALL GRANT authorization_list
BIND QEURWHV1.BND BLOCKING ALL GRANT authorization_list

authorization_list is a list of comma-separated Authorization IDs or Group IDs,
or PUBLIC if you want to grant access to all users.

Note for SQL/DS Users:  For binding to SQL/DS databases , enter only the first
three of these commands.  The files QECSWHV1.BND, QERRWHV1.BND, and
QEURWHV1.BND will not work with SQL/DS.

For SQL/400 Users

If you receive the error

`QSYS.QE' is an undefined name

you must create a collection named QE.  To do this, enter the following command
on the host AS/400:

CREATE COLLECTION QE

Then issue the BIND commands again.


ΓòÉΓòÉΓòÉ 3.5. Connecting to DB2/2 Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

 1. Type the name of the remote database or select the name of the remote
    database from the Database Name drop-down list.

    You must have cataloged any database you want to access from the client.
    (See the topic Configuring Data Sources for information on how to do this.)

 2. If required, type your user name (authorization ID).

 3. If required, type your password.

    If you leave your user name and password blank, the ODBC application
    assumes you have already logged on using SQLLOGN2 (under DOS) or using User
    Profile Management (under OS/2).  If you have not, the application returns
    an error. You must either type your user name and password in the dialog
    box or log on using SQLLOGN2 and STARTDRQ (under DOS) or using User Profile
    Management (under OS/2).  See the IBM Extended Services for OS/2 Guide to
    DB2/2 Clients for more information.

 4. Click OK to complete the logon and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 3.6. Connecting to DB2/2 Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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/2 is

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

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

Attribute
          Description

DataSourceName (DSN)
          Identifies a DB2/2 data source configuration in ODBC.INI.  Examples
          include "Accounting" or "DB2/2-Serv1."

Database (DB)
          Name of the database to which you want to connect.

Sysibm (SI)
          On most DB2/2 systems, SYSIBM is the owner of the catalog system
          tables.  If you have read access to the system tables, you do not
          need to change this option.

          If  you do not have read access, the database administrator must
          create a view of the system tables in another account and give you
          permission to use that view.  In this case, specify the Authorization
          ID for the account that owns the views of the system tables. If you
          are using DDCS/2 to connect to a SQL/400 database, specify a
          comma-separated list of the Collection IDs to which you want to have
          access in your ODBC application. If you are using DDCS/2 to connect
          to a SQL/DS database, specify SYSTEM as the owner.

Groups (GRP)
          Determines which tables you can access.  Your system administrator
          may have placed you in a "group" of users and granted table access to
          the entire group. If this is the case, set Groups to the names of any
          groups to which you belong; separate each name with a comma.
          Alternatively, Groups=ALL lets your application see all table names
          even if you cannot access the table.

LogonID (UID)
          Default logon ID used to connect to your DB2/2 database.  A logon ID
          is required only if security is enabled on your database.  If so,
          contact your system administrator to get your logon ID.

Password (PWD)
          Password.

CursorBehavior (CB)
          CursorBehavior={0 | 1}. Determines whether cursors are preserved or
          closed at the end of each transaction.  Valid values are 1 (preserve)
          and 0 (close, the initial default).  Set this attribute to 1 if you
          want cursors to be held at the current position when the transaction
          ends.  Doing so may impact the performance of your database
          operations. This attribute is not valid for SQL/DS.

          When you select Preserve, the driver returns SQL_CB_PRESERVE from
          SQLGetInfo (SQL_CURSOR_COMMIT_BEHAVIOR). But only Select statements
          and prepared Update or Delete . . . Where Current of Cursor
          statements are preserved when the transaction ends. All other
          prepared statements are closed and deleted.


ΓòÉΓòÉΓòÉ 3.7. DB2/2 Data Types ΓòÉΓòÉΓòÉ

The DB2/2 data types map to the standard ODBC data types as follows:

DB2/2                  ODBC

Char                   SQL_CHAR

Char() for Bit Data    SQL_BINARY

Date                   SQL_DATE

Decimal                SQL_DECIMAL

Float                  SQL_DOUBLE

Integer                SQL INTEGER

Long Varchar (1)       SQL_LONGVARCHAR

Long Varchar for Bit Data (1) SQL_LONGVARBINARY

Smallint               SQL_SMALLINT

Time                   SQL_TIME

Timestamp              SQL_TIMESTAMP

Varchar (2)            SQL_VARCHAR

Varchar (3)            SQL_LONGVARCHAR

Varchar() for Bit Data (2) SQL_VARBINARY

Varchar() for Bit Data (3) SQL_LONGVARBINARY

(1) Not supported for SQL/400

(2) For DB2/2, DB2/6000, and SQL/400; SQL/DS and MVS DB2 up to 254

(3) For SQL/DS and MVS DB2 over 254

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


ΓòÉΓòÉΓòÉ 3.8. Isolation and Lock Levels Supported by DB2/2 ΓòÉΓòÉΓòÉ

DB2/2 supports isolation levels 0 (read uncommitted), 1 (read committed), and 2
(repeatable read).  It supports record-level locking.


ΓòÉΓòÉΓòÉ 3.9. DB2/2 ODBC Conformance Level ΓòÉΓòÉΓòÉ

The DB2/2 driver supports the Core, Level 1, and Level 2 API functions listed
in Supported ODBC Functions.  The driver supports the minimum SQL grammar.


ΓòÉΓòÉΓòÉ 3.10. Number of Connections and Statements Supported by DB2/2 ΓòÉΓòÉΓòÉ

The DB2/2 family of database systems supports a single connection and multiple
statements per connection.


ΓòÉΓòÉΓòÉ 4. dBASE Driver ΓòÉΓòÉΓòÉ

The dBASE driver supports

Γûá  dBASE III, dBASE IV and dBASE V files
Γûá  Clipper files
Γûá  FoxPro and FoxBASE tables and indexes

Note:  The dBASE driver for OS/2 does not support dBASE II.

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

The driver filename is IVDBFnn.DLL.


ΓòÉΓòÉΓòÉ 4.1. Configuring dBASE Data Sources ΓòÉΓòÉΓòÉ

To configure a dBASE data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV dBASEFile and click OK.

    If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 3. Specify values as follows:

   Data Source Name:
             A string that identifies this dBASE data source configuration in
             ODBC.INI. Examples include "Accounting" or "dBASE Files."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "dBASE files in C:\ACCOUNTS."

   Database Directory:
             A path specification to the directory that contains the database
             files.  If none is specified, the current working directory is
             used.

             The following values are optional:

   Create Type:
             The type of table or index to be created on a Create Table or
             Create Index statement.  Select dBASE III, dBASE IV, dBASE V,
             Clipper, FoxBASE, FoxPro1, or FoxPro25.  The default is dBASE V.

   Lock Compatibility:
             The locking scheme the driver uses when locking records.  Select
             dBASE, Q+E, Q+EVirtual, Clipper, or Fox.  The default is dBASE.
             These values determine locking support as follows:

             Γûá  dBASE specifies Borland-compatible locking.

             Γûá  Q+E specifies that locks be placed on the actual bytes occupied
             by the record.  Only applications that use the dBASE driver can
             read and write to the database.  Other applications are locked out
             of the table completely (they cannot even read other records).
             This locking is compatible with earlier versions of Q+E products.

             Γûá  Q+EVirtual specifies that locks be placed on bytes beyond the
             physical end-of-file.  Q+EVirtual is the same as Q+E except that
             other applications can open the table and read the data. The
             advantage of using a Q+E locking scheme over dBASE locking is
             that, on Inserts and Updates, Q+E locks only individual index
             tags, while dBASE locks the entire index.

             Γûá  Clipper specifies Clipper-compatible locking.

             Γûá  Fox specifies FoxPro- and FoxBASE-compatible locking.

             If you are accessing a table with an application that uses the
             dBASE driver, your locking scheme does not have to match the
             Create Type. However, if you are accessing a table with two
             applications, and only one uses the dBASE driver, set your locking
             scheme to match the other application.  For example, you do not
             have to set this value to Fox to work with a FoxPro table.  But if
             you are using a FoxPro application simultaneously with an
             application using the dBASE driver on the same set of tables, set
             this value to Fox to ensure that your data does not get corrupted.

   Locking:
             The level of locking for the database file (FILE, RECORD, or
             NONE).  FILE locks all of the records in the table.  RECORD (the
             default) locks only the records affected by the statement.  NONE
             offers the best performance but is intended only for single-user
             environments.

   File Open Cache:
             A numeric value to specify the maximum number of unused file opens
             to cache. For example, the value 4 specifies that when a user
             opens and closes four tables, the tables are not actually closed.
             The driver keeps them open so that if another query uses one of
             these tables, the driver does not have to perform another open,
             which is expensive.  The advantage of file open caching is
             increased performance.  The disadvantage is that a user who
             specifies file locking on open may get a locking conflict even
             though no one appears to have the file open.  The default is 0,
             which means no file open caching.

   Cache Size:
             The number of 64K blocks the driver uses to cache database
             records.  The higher the number, the better the performance.  The
             maximum number of blocks you can set depends on the system memory
             available.  If the cache size is greater than 0, when browsing
             backwards, you will not be able to see updates made by other users
             until you reexecute the Select statement. The default is 4.

   International Sort:
             A setting to indicate the order in which records are retrieved
             when you issue a Select statement with an Order By clause.  Select
             this check box to use the international sort order as defined by
             your operating system.  International sort order is
             case-insensitive (a precedes B); the sorting of accented
             characters is also affected (see your operating system
             documentation).  Leave this box blank to use the ASCII sort order.
             ASCII sort order is case-sensitive, where uppercase letters
             precede lowercase letters (B precedes a).

   Use Long Names:
             A setting that enables the driver to use long filenames as table
             names. If you select this check box, the maximum table name length
             is 128 characters.

   Use Long Qualifiers:
             A setting that enables the driver to use long pathnames as table
             qualifiers. When you set this check box, pathnames can be up to
             249 characters. The default length for pathnames is 128
             characters.

 4. Click Define to define the index attributes for your data files. See
    Defining Index Attributes for step-by-step instructions.

 5. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

    The translators that are listed in this dialog box are determined by the
    values listed in the ODBC Translators section of your ODBCINST.INI file.

 6. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 4.2. Defining Index Attributes for dBASE ΓòÉΓòÉΓòÉ

Since dBASE lets you create index files that have different names than their
corresponding data files, you must tell the driver what index files are
associated with the dBASE file.  The driver updates the indexes for you, which
ensures that they match the records in the dBASE file.  The driver also makes
indexes available for optimizing queries.  It is not necessary to mark
production .MDX files or structured .CDX files as maintained, as the driver
maintains them for you.  However, you may wish to use this method to mark a tag
as unique.

To define the index files that are associated with a dBASE file, take the
following steps:

 1. Click Define in the dBASE setup dialog box, which you can access through
    the ODBC Administrator.  The Define File dialog box appears.

 2. Select a dBASE file and click OK to define the special indexes using the
    Define Table dialog box.

 3. The upper section of the dialog box displays the directory name and
    filename that contains the data file.  Under Windows, if this file is
    stored in the IBM PC character set, select the OEM to ANSI Translation box.

 4. The lower section of the dialog box displays the index information for the
    data file.  The Index File drop-down list lets you select any index file in
    the database directory.  If the index file is in a different directory, you
    must provide the full pathname.

   Select the Maintain check box to associate this index file with your dBASE
   file.

   To specify that an index file is unique, select the Unique check box that
   appears at the right of the index filename.

 5. If the selected index has an .MDX or .CDX extension, you cannot mark the
    index file as unique.  Instead, you may mark the tags within the index as
    unique.  To do so, select the tag name in the Tag drop-down list and select
    the Unique check box that appears at the right of the tag name.

 6. Click OK to save this information, or press Cancel.


ΓòÉΓòÉΓòÉ 4.3. Connecting to dBASE Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to the ODBC.INI file.

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 following table gives the long and short names for each attribute, as well
as a description.

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

Attribute
          Description

DataSourceName (DSN)
          String that identifies a dBASE data source configuration in ODBC.INI.
          Examples include "Accounting" or "dBASE Files."

Database (DB)
          Directory in which the dBASE files are stored.

CreateType (CT)
          CreateType={dBASE3 | dBASE4 | dBASE5 | Clipper | FoxBASE | FoxPro1 |
          FoxPro25}.Type of table or index to be created on a Create Table or
          Create Index statement.  Options are dBASE2, dBASE3, dBASE4, dBASE5
          (the initial default), Clipper, FoxBASE, FoxPro1, and FoxPro25.

LockCompatibility (LCOMP)
          LockCompatibility= {Q+E | Q+EVirtual | dBASE | Clipper | Fox}.Locking
          schemes to be used in your dBASE tables.  Options are Q+E,
          Q+EVirtual, dBASE, Clipper, and Fox.

          Γûá  LockCompatibility=Q+E specifies that locks be placed on the actual
          bytes occupied by the record.  Only applications that use the dBASE
          driver can read and write to the database.  Other applications are
          locked out of the table completely (they cannot even read other
          records).  This locking is compatible with earlier versions of Q+E
          products.

          Γûá  LockCompatibility=Q+EVirtual specifies that locks be placed on
          bytes beyond the physical end-of-file.  Q+EVirtual is the same as Q+E
          except that other applications can open the table and read the data.

          The advantage of using a Q+E locking scheme over dBASE locking is
          that, on Inserts and Updates, Q+E locks only individual index tags,
          while dBASE locks the entire index.

          Γûá  LockCompatibility=dBASE specifies Borland-compatible locking.
          This is the initial default.

          Γûá  LockCompatibility=Clipper specifies Clipper-compatible locking.

          Γûá  LockCompatibility=Fox specifies FoxPro- and FoxBASE-compatible
          locking.

          If you are accessing a table with an application that uses the dBASE
          driver, your locking scheme does not have to match the Create Type.
          However, if you are accessing a table with two applications, and only
          one uses the dBASE driver, set your locking scheme to match the other
          application.  For example, you don't have to set LCOMP=Fox to work
          with a FoxPro table.  But if you are using a FoxPro application
          simultaneously with an application using the dBASE driver on the same
          set of tables, set LCOMP=Fox to ensure that your data does not get
          corrupted.

Locking (LCK)
          Locking={NONE | RECORD | FILE}. Level of locking for the database
          tables. Options are NONE, RECORD, and FILE.

          Locking=NONE offers the best performance but is intended only for
          single-user environments.

          Locking=RECORD locks only the records affected by the statement. This
          is the initial default.

          Locking=FILE locks all of the records in the table.FileOpenCache
          (FOC)The maximum number of unused file opens to cache.  For example,
          when FileOpenCache=4, and a user opens and closes four files, the
          files are not actually closed.  The driver keeps them open so that if
          another query uses one of these files, the driver does not have to
          perform another open, which is expensive.  The advantage of using
          file open caching is increased performance. The disadvantage is that
          a user who tries to open the file exclusively may get a locking
          conflict even though no one appears to have the file open.  The
          initial default is 0.

CacheSize (CSZ)
          Number of 64K blocks the driver uses to cache database records.  The
          higher the number, the better the performance.  The maximum number of
          blocks you can set depends on the system memory available.  If the
          cache size is greater than 0, when browsing backwards, you will not
          be able to see updates made by other users until you reexecute the
          Select statement. The default is 4.

IntlSort (IS)
          IntlSort={0 | 1}. Number that specifies the order that records are
          retrieved when you issue a Select statement with an Order By clause.
          If IntlSort=1, the driver uses the international sort order as
          defined by your operating system. The sort is case-insensitive (a
          precedes B); the sorting of accented characters is also affected (see
          your operating system documentation).  If IntlSort=0 (the initial
          default), the driver uses the ASCII sort order, where uppercase
          letters precede lowercase letters (B precedes a).

ModifySQL (MS)
          ModifySQL={0 | 1}. Provided for backward compatibility with earlier
          versions of Q+E products.  It determines whether the driver modifies
          SQL statements to conform to ODBC specifications or passes the SQL
          statement directly to dBASE. Specify ModifySQL=1 to have the driver
          modify the SQL statement to conform to ODBC specifications.  Specify
          ModifySQL=0 to have the driver understand SQL dialects found in
          earlier drivers. The default is 1.

Compatibility (COMP)
          Compatibility={0 | 1}. Provided for backward compatibility with
          earlier versions of Q+E products.  Use Compatibility=DBASE for
          backward compatibility; use Compatibility=ANSI for portability.  The
          default is ANSI.

UltraSafeCommit (USF)
          UltraSafeCommit={0 | 1}. Specifies when the driver flushes the file
          cache. If UltraSafeCommit=1, the driver updates directory entries
          after each Commit. This decreases performance.  If UltraSafeCommit=0
          (the default) the driver updates the directory entry when the file is
          closed.  In this case, a machine "crash" before closing the file
          causes newly inserted records to be lost.

UseLongNames (ULN):
          UseLongNames={0 | 1}. Enables the driver to use long filenames as
          table names. The default is 0, do not use long filenames. When
          UseLongNames=1, the driver uses long filenames, and the maximum table
          name length is 128 characters.

UseLongQualifiers (ULQ):
          UseLongQualifiers={0 | 1}. Enables the driver to use long pathnames
          as table qualifiers. The default is 0, do not use long pathnames. The
          default length for pathnames is 128 characters. When
          UseLongQualifiers=1, pathnames can be up to 249 characters.


ΓòÉΓòÉΓòÉ 4.4. dBASE Data Types ΓòÉΓòÉΓòÉ

The following table shows how dBASE data types map to the standard ODBC data
types.  These dBASE data types can be used in a Create Table statement.

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.

dBASE          ODBC
Binary (1)     SQL_LONGVARBINARY
Char (2)       SQL_CHAR
Date (3)       SQL_DATE
Float (4)      SQL_DECIMAL
General (5)    SQL_LONGVARBINARY
Logical        SQL_BIT
Memo (3)       SQL_LONGVARCHAR
Numeric        SQL_DECIMAL
Picture (6)    SQL_LONGVARBINARY

 1. dBASE V only

 2. 254 characters maximum (1024 for Clipper)

 3. dBASE III, IV, FoxPro, FoxBASE, and Clipper

 4. dBASE IV only

 5. FoxPro 2.5 and dBASE V only

 6. FoxPro, FoxBASE, and Clipper
h2 res=26100.Column Names

The maximum length of a column name is 10 characters.  A column name can
contain alphanumeric characters and the hyphen character (-). The first
character must be a letter (a through z).


ΓòÉΓòÉΓòÉ 4.5. Select Statement for dBASE ΓòÉΓòÉΓòÉ

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.  This
topic describes the information that is specific to dBASE, which is ROWID.

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.


ΓòÉΓòÉΓòÉ 4.6. Create Index Statement for dBASE ΓòÉΓòÉΓòÉ

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

Γûá  dBASE II or III (.NDX)
Γûá  dBASE IV (.MDX)
Γûá  Clipper (.NTX)
Γûá  FoxBASE (.IDX)
Γûá  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]] ...)

index_name is the name of the index file.  For FoxPro 2.5 and dBASE IV, this is
a tag, which is required to identify the indexes in an index file.  Each index
for a table must have a unique name.

UNIQUE means that the driver creates an ANSI-style unique index over the column
and ensures uniqueness of the keys.  Use of unique indexes improves
performance.  ANSI-style unique indexes are different from dBASE-style unique
indexes.  With ANSI-style unique indexes, you receive an error message when you
try to insert a duplicate value into an indexed field.  With dBASE-style unique
indexes, you do not see an error message when you insert a duplicate value into
an indexed field.  This is because only one key is inserted in the index file.

base_table_name is the name of the database file whose index is to be created.
The .DBF extension is not required, the driver automatically adds it if it is
not present.  By default, dBASE IV index files are named base_table_name.MDX
and FoxPro 2.5 indexes are named base_table_name.CDX.

field_name is a name of a column in the dBASE table.  You can substitute a
valid dBASE-style index expression for the list of field names.

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 following table 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

ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
ΓöéCreate         dBASE      DESC     Max Size of  Max Size of  Production/    Γöé
ΓöéType/Ext.      UNIQUE              Key Column   Column Spec. Structural     Γöé
Γöé                                                             Indexes        Γöé
Γöé                                                                            Γöé
ΓöédBASE II       No         No       100          99           No             Γöé
Γöé.NDX                                                                        Γöé
Γöé                                                                            Γöé
ΓöédBASE III      Yes        No       100          219          No             Γöé
Γöé.NDX                                                                        Γöé
Γöé                                                                            Γöé
ΓöéClipper        Yes        Yes      250          255          No             Γöé
Γöé.NTX                                                                        Γöé
Γöé                                                                            Γöé
ΓöéFoxBASE        Yes        No       100          219          No             Γöé
Γöé.IDX*                                                                       Γöé
Γöé                                                                            Γöé
ΓöédBASE IV and V Yes        Yes      100          220          Yes            Γöé
Γöé.MDX                                                                        Γöé
Γöé                                                                            Γöé
ΓöéFoxPro         Yes        Yes      240          255          No             Γöé
Γöé2.5 .IDX**                                                                  Γöé
Γöé                                                                            Γöé
ΓöéFoxPro         Yes        Yes      240          255          Yes            Γöé
Γöé2.5 .CDX                                                                    Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

*These IDX indexes are also created as the default for FoxPro 1.0.

**Compact IDX indexes have the same internal structure as a tag in a CDX file.
These indexes can be created if the IDX extension is included with the index
name in the Create Index statement.


ΓòÉΓòÉΓòÉ 4.7. Drop Index Statement for dBASE ΓòÉΓòÉΓòÉ

The syntax for dropping a dBASE index is as follows:

DROP INDEX table_name.index_name

table_name is the name of the dBASE file without the extension.

For FoxPro 2.5 and dBASE IV, index_name is the tag.  Otherwise, index_name is
the name of the index file without the extension.

To drop the index EMPHIRE.NDX, issue the following statement:

DROP INDEX emp.emphire


ΓòÉΓòÉΓòÉ 4.8. Pack Statement for dBASE ΓòÉΓòÉΓòÉ

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

filename is the name of the dBASE file to be packed.  The .DBF extension is not
required; the driver automatically adds the extension if it is not present.
For example,

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


ΓòÉΓòÉΓòÉ 4.9. dBASE 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.

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=)
Γûá  The setup dialog box

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.

Using Locks on Local Files

If you use database locking and are accessing files locally (not on a network),
run the DOS utility SHARE.EXE before running Windows.  If you add SHARE.EXE to
your AUTOEXEC.BAT file, it runs automatically each time you boot your computer.

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).  If you are accessing a dBASE file locally, the
limit depends on the buffer space allocated when SHARE.EXE was loaded (see your
DOS documentation).  If you are exceeding the number of locks available, you
may want to switch to file locking.

How Transactions Affect Record Locks

When an Update or Delete statement is executed, 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 executed.

When a Select...For Update statement is executed, 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.


ΓòÉΓòÉΓòÉ 4.10. Isolation and Lock Levels Supported by dBASE ΓòÉΓòÉΓòÉ

dBASE supports isolation level 1.  It supports both file- and record-level
locking.


ΓòÉΓòÉΓòÉ 4.11. dBASE ODBC Conformance Level ΓòÉΓòÉΓòÉ

The dBASE driver supports the Core, Level 1, and Level 2 API functions listed
in Supported ODBC Functions.  The dBASE driver also supports backward and
random fetching in SQLExtendedFetch.

The driver supports the minimum SQL grammar.


ΓòÉΓòÉΓòÉ 4.12. Number of Connections and Statements Supported by dBASE ΓòÉΓòÉΓòÉ

dBASE supports multiple connections and multiple statements per connection.


ΓòÉΓòÉΓòÉ 5. INFORMIX 5 Driver ΓòÉΓòÉΓòÉ

The INFORMIX 5 driver supports multiple connections to the INFORMIX 5 database.

The driver filename is IVINF5nn.DLL.


ΓòÉΓòÉΓòÉ 5.1. INFORMIX 5 System Requirements ΓòÉΓòÉΓòÉ

You need the following software:

Γûá  INFORMIX-ESQL/C for OS/2 Version 5.01.SC1B1 or higher

Γûá  INFORMIX-INET for OS/2 Version 5.01.SC1B1 or higher (if you are running on a
client/server environment).

Γûá  INFORMIX-SE Version 5.0x, 6.0 or INFORMIX-OnLine Version 5.0x, 6.0

ISQLI501.DLL along with other required DLLs must be in directories listed in
the LIBPATH specification in your CONFIG.SYS file.

The environment variable INFORMIXDIR must be set to the directory where you
have installed the INFORMIX client. For example:

SET INFORMIXDIR=C:\INFORMIX

This line can be added to your CONFIG.SYS file.


ΓòÉΓòÉΓòÉ 5.2. Configuring INFORMIX Data Sources ΓòÉΓòÉΓòÉ

To configure an INFORMIX 5 data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV INFORMIX5 and click OK.

 3. If you are configuring an existing data source, select the data source name
    and click Setup.

 4. The setup dialog box appears.

 5. Specify values as follows:

   Data Source Name:
             A string that identifies this INFORMIX data source configuration
             in ODBC.INI.  Examples include "Accounting" or "INFORMIX-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "INFORMIX 5 files on Server number 1."

   Database Name:
             The name of the database to which you want to connect by default.

             The following values are optional:

   Database List:
             The list of databases that will be displayed in the logon dialog
             box. If Get DB List From Informix is set to 1, the l ist of
             databases that will be displayed in the logon dialog box is
             created from the database list returned for INFORMIX at logon
             time. When you specify databases, separate the names with commas.

   Default User Name:
             The name of the user as specified on the INFORMIX 5 server.

   Host Name:
             The name of the machine on which the INFORMIX 5 server resides.

   Service Name:
             The name of the service as it appears on the host machine.  This
             service is assigned by the system administrator.  The name you
             specify is displayed in the INFORMIX Server Options dialog box.

   Protocol Type:
             The protocol used to communicate with the server. Specify one or
             more values; separate the names with commas.  Values can be FTP,
             IPX, LANMAN, TCP-IP. This list is displayed in the Logon Options
             dialog box.

   Yield Proc:
              (This attribute is not available in OS/2.) A numeric value that
             determines whether you can work in other applications when
             INFORMIX 5 is busy.

   Cursor Behavior:
             Select Preserve if you want cursors to be held at the current
             position when the transaction ends.  Otherwise, leave this set to
             Close.  Selecting Preserve may impact the performance of your
             database operations.

   Enable Scrollable Cursors:
             A value that determines whether the driver can use scrollable
             cursors. The initial default value is 0 (no use of scrollable
             cursors). The INFORMIX driver can use scollable cursors only if
             there are no long columns (SQL_LONGVARCHAR or SQL_LONGVARBINARY)
             in a Select list. If you set this option to use scrollable cursors
             (EnableScrollableCursors=1), you must not include long columns in
             the Select list.

   Get DB List from Informix:
             A value that determines whether the driver requests the database
             list to be returned from the INFORMIX server or from the database
             list that the user entered at driver setup.

             When set to 1, the default, the driver requests the database list
             from the INFORMIX server. When set to 0, it uses the list entered
             by the user at driver setup.

 6. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

 7. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 5.3. Connecting to INFORMIX Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

 1. Type the name of the database you want to access or select the name from
    the Database Name drop-down list. This list displays names returned from
    the INFORMIX server. Or, if you set the Get DB List from Informix option to
    0 in the Setup dialog box, this list displays the names you specified.

 2. Type the name of the server (host name) on which INFORMIX 5 resides.

 3. If required, type your user name as specified on the INFORMIX 5 server.

 4. If required, type your password.

 5. Optionally, click Options to display the INFORMIX Server Options dialog
    box. This dialog box enables you to change the Service Name and Protocol
    Type that you specified in the setup dialog box.  Click OK to accept any
    changes you make.

 6. Click OK to complete the logon and to update these values in ODBC.INI.


ΓòÉΓòÉΓòÉ 5.4. Connecting to INFORMIX Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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 5 is

DSN=INFORMIX TABLES;DB=PAYROLL

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

Attribute
          Description

DataSourceName (DSN)
          String that identifies an INFORMIX data source configuration in
          ODBC.INI. Examples include "Accounting" or "INFORMIX-Serv1."

Database (DB)
          Name of the database to which you want to connect.

HostName (HOST)
          Name of the machine on which the INFORMIX 5 server resides.

LogonID (UID)
          Your user name as specified on the INFORMIX 5 server.

Password (PWD)
          A password.

Service (SERV)
          Name of the service as it appears on the host machine.  This service
          is assigned by the system administrator.

Protocol (PRO)
          Protocol={FTP | IPX | LANMAN | TCP-IP}. Protocol used to communicate
          with the server.  Values can be FTP, IPX, LANMAN, or TCP-IP.  You can
          specify one or more values; separate the names with commas.

YieldProc (YLD)
          (This attribute is not available in OS/2.) A value that determines if
          you can work in other applications when INFORMIX 5 is busy.

CursorBehavior (CB)
          CursorBehavior={0 | 1}. Determines whether cursors will be preserved
          or closed at the end of each transaction.  Valid values are 1
          (preserve) and 0 (close, the initial default).  Set this attribute to
          1 if you want cursors to be held at the current position when the
          transaction ends.  The value CursorBehavior=1 may impact the
          performance of your database operations.

EnableScrollableCursors (ESC).
          EnableScrollable Cursors={0 | 1}. Determines whether the driver can
          use scrollable cursors. The initial default value is 0 (no use of
          scrollable cursors). The INFORMIX driver can use scollable cursors
          only if there are no long columns (SQL_LONGVARCHAR or
          SQL_LONGVARBINARY) in a Select list. If you set this option to use
          scrollable cursors (EnableScrollableCursors=1), you must not include
          long columns in the Select list.

GetDBListfromInformix (GDBLFI)
          GetDBListFromInformix={0 | 1}. Determines whether the driver requests
          the database list to be returned from the INFORMIX server or from the
          database list that the user entered at driver setup.

          When set to 1, the default, the driver requests the database list
          from the INFORMIX server. When set to 0, it uses the list entered by
          the user at driver setup.


ΓòÉΓòÉΓòÉ 5.5. INFORMIX Data Types ΓòÉΓòÉΓòÉ

The INFORMIX 5 data types map to the standard ODBC data types as follows:

INFORMIX 5 ODBC
Byte*     SQL_LONGVARBINARY
Char      SQL_CHAR
Date      SQL_DATE
Datetime year to fraction(5) SQL_TIMESTAMP
Decimal   SQL_DECIMAL
Float     SQL_DOUBLE
Integer   SQL_INTEGER
Money     SQL_DECIMAL
Serial    SQL_INTEGER
Smallfloat SQL_REAL
Smallint  SQL_SMALLINT
Text*     SQL_LONGVARCHAR
Varchar*  SQL_VARCHAR

* Not supported for Standard Engine Databases.

Note:  The Interval data type is not supported.  Existing columns of this type
are mapped to the ODBC SQL_CHAR data type.


ΓòÉΓòÉΓòÉ 5.6. Isolation and Lock Levels Supported by INFORMIX ΓòÉΓòÉΓòÉ

INFORMIX 5 supports isolation levels 0 (read uncommitted), 1 (read committed),
and 3 (serializable).  The default is 1.

INFORMIX 5 also supports an alternative isolation level 1, called cursor
stability.  Your ODBC application can use this isolation level by calling
SQLSetConnectOption (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 5 supports page-level locking.


ΓòÉΓòÉΓòÉ 5.7. INFORMIX ODBC Conformance Level ΓòÉΓòÉΓòÉ

The INFORMIX 5 driver supports the Core, Level 1, and Level 2 API functions
listed in Supported ODBC Functions.  In addition, the following Level 2
functions are supported:

Γûá  SQLBrowseConnect
Γûá  SQLProcedures

The driver supports the core SQL grammar.


ΓòÉΓòÉΓòÉ 5.8. Number of Connections and Statements Supported ΓòÉΓòÉΓòÉ

The INFORMIX 5 database system supports multiple connections and multiple
statements per connection.


ΓòÉΓòÉΓòÉ 6. INGRES 6.4/04 Driver ΓòÉΓòÉΓòÉ

The INGRES driver supports INGRES Release 6.4/04 and higher. The driver
filename is IVING4nn.DLL.


ΓòÉΓòÉΓòÉ 6.1. INGRES System Requirements ΓòÉΓòÉΓòÉ

To access INGRES databases from your client workstation you must have the
INGRES/Net or INGRES/Star product installed on both your client and server
nodes.

To access INGRES, you must have Networking for OS/2 Release 6.4/04
(net.os2/02). All required DLLs must reside in directories that are listed  in
the LIBPATH specification in your CONFIG.SYS file.


ΓòÉΓòÉΓòÉ 6.2. Configuring INGRES Data Sources ΓòÉΓòÉΓòÉ

To configure an INGRES data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV INGRES 6.4/04 and click OK.

    If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 3. Specify values as follows:

   Data Source Name:
             A string that identifies this INGRES data source configuration in
             ODBC.INI. Examples include "Accounting" or "INGRES-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "INGRES on Server number 1."

   Server Name:
             The name of the virtual node that you defined using the INGRES
             NETU utility. This virtual node tells INGRES which system to call,
             how to call it, and the user's name and password.

   Database Name:
             The name of the database to which you want to connect by default.

             The following values are optional:

   Server List:
             The list of servers (virtual nodes) that will be available in the
             Logon dialog box.  Separate the server names with commas.

   Database List:
             The list of databases that will be available in the logon dialog
             box. Separate the names with commas.

   Default User Name:
             The default user name used to connect to your INGRES database.  A
             user name is required only if security is enabled on your
             database.  Your ODBC application may override this value or you
             may override this value in the logon dialog box.

   Options:
             Any flag allowed on the INGRES SQL command line.  Some examples
             are

             Γûá  -l (locks the database exclusively)

             Γûá  -u (logs on as username)

             Γûá  +w or -w (waits/doesn't wait for the database if a user has
             already opened it exclusively)

             Γûá  +U or -U (enables/disables user updating of the system tables
             and locks the database exclusively)

             Γûá  +Y or -Y (enables/disables user updating of the system tables
             but does not lock the database exclusively)

   Yield Proc:
             This attribute is not available under OS/2.  A numeric value that
             determines whether you can work in other applications when INGRES
             is busy.

   Repeated Cache Size
             A number that determines whether all Update and Insert statements
             are to be executed as repeated statements. This attribute improves
             the performance of applications that repeat the same SQL
             statement. When set to 0, the initial default, no statements are
             repeated. The recommended setting for this attribute is 100
             (RepeatedCacheSize=100).

             To repeat a single statement rather than all statements, use the
             INGRES REPEATED syntax.

   Repeated Selects
             A number that determines whether the driver optimizes Select
             statements or executes them as repeated queries. When set to 0,
             the initial default, the driver executes all Select statements as
             it did in previous versions of the product. When set to 1, the
             driver optimizes Select statements that return only one result
             row. When set to 2, the driver executes all Select statements as
             repeated queries. If this attribute is set to 1 or 2, the
             RepeatedCacheSize attribute must be set to greater than zero.
             Setting this option to 1 or 2 has no effect on Select statements
             containing a For Update clause. These settings also limit the
             driver to one active statement and one active connection.

   Value Replacement for Repeated Selects
             A number that determines whether the driver substitutes parameters
             for hardcoded values in repeated statements. This option is
             convenient in applications that do not use dynamic parameters.

             When set to 0, the initial default, the driver does not substitute
             parameters. When set to 1, the driver substitutes parameters for
             hardcoded values and the RepeatedCacheSize attribute must be
             greater than zero or the REPEATED INGRES keyword must be used.

             This option has no effect upon Select statements that contain a
             For Update clause that requires a cursor or upon statements that
             already use parameter markers.

             This attribute supports only a subset of standard ODBC SQL
             grammer. It is intended for performance and does not utilize a
             full SQL parser. For example, subselects are not supported and use
             of "is NULL" for columns other than character columns is not
             supported. Click Translate to perform a translation of your data
             from one character set to another. The Select Translator dialog
             box appears in which you can select a translator to peform the
             translation. INTERSOLV provides a translator named INTERSOLV OEM
             ANSI that translates your data from the IBM PC character set to
             the ANSI character set. Click OK to leave this dialog box and
             perform the translation. Click OK to write these values to
             ODBC.INI.  These values are now the defaults when you connect to
             the data source.  You can change the defaults by configuring your
             data source again.  You can override the defaults by connecting to
             the data source using a connection string with alternate values.


ΓòÉΓòÉΓòÉ 6.3. Connecting to INGRES Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

 1. Type the server name of the computer containing the INGRES database you
    want to access or select the name from the Server Name drop-down list,
    which displays the server names you specified in the setup dialog box.
    Server Name must be a valid virtual node that has been added using the
    INGRES NETU utility.

 2. Type the name of the database to which you want to connect or select the
    name from the Database Name drop-down list, which displays the names you
    specified in the setup dialog box.

 3. If required, type your user name.

 4. Type any options for the connection.  The options can be any flags allowed
    on the INGRES SQL command line.

 5. Click OK to log on to INGRES and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 6.4. Connecting to INGRES Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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 INGRES is

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

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

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

Attribute
          Description

DataSourceName (DSN)A string that identifies an INGRES data source
configuration in ODBC.INI.  Examples include "Accounting" or "INGRES-Serv1."

ServerName (SRVR)
          The name of the virtual node that you defined using the INGRES NETU
          utility. This virtual node tells INGRES which system to call, how to
          call it, and the user's name and password.

Database (DB)
          The name of the database to which you want to connect.

LogonID (UID)
          The default logon ID (user name) used to connect to your INGRES
          database.  A logon ID is required only if security is enabled on your
          database.  If so, contact your system administrator to get your logon
          ID.

Options (OPTS)
          The flags allowed on the INGRES SQL command line.  Some examples are

          Γûá  -l (locks the database exclusively)

          Γûá  -u (logs on as username)

          Γûá  -w or -w (waits/doesn't wait for the database if someone has
          already opened it exclusively)

          Γûá  +U or -U (enables/disables user updating system tables and locks
          the database exclusively)

          Γûá  +Y or -Y (enables/disables user updating system tables but does
          not lock the database exclusively)

YieldProc (YLD)
          A value that determines if you can work in other applications when
          INGRES is busy.  This attribute is not available under OS/2.

RepeatedCacheSize (RCS)
          A number that determines whether all Update and Insert statements are
          to be executed as repeated statements. This attribute improves the
          performance of applications that repeat the same SQL statement. When
          set to 0, the initial default, no statements are repeated. The
          recommended setting for this attribute is 100
          (RepeatedCacheSize=100).

          To repeat a single statement rather than all statements, use the
          INGRES REPEATED syntax.

RepeatedSelects (RS)
          RepeatedSelects={0 | 1 | 2}. A number that determines whether the
          driver optimizes Select statements or executes them as repeated
          queries. When set to 0, the initial default, the driver executes all
          Select statements as it did in previous versions of the product. When
          set to 1, the driver optimizes Select statements that return only one
          result row. When set to 2, the driver executes all Select statements
          as repeated queries. If this attribute is set to 1 or 2, the
          RepeatedCacheSize attribute must be set to greater than zero. Setting
          this option to 1 or 2 has no effect on Select statements containing a
          For Update clause. These settings also limit the driver to one active
          statement and one active connection.

ValueReplacement (VR)
          ValueReplacement={0 | 1}. A number that determines whether the driver
          substitutes parameters for hardcoded values in repeated statements.
          This option is convenient in applications that do not use dynamic
          parameters.

          When set to 0, the initial default, the driver does not substitute
          parameters. When set to 1, the driver substitutes parameters for
          hardcoded values and the RepeatedCacheSize attribute must be greater
          than zero or the REPEATED INGRES keyword must be used.

          This option has no effect upon Select statements that contain a For
          Update clause that requires a cursor or upon statements that already
          use parameter markers.

          This attribute supports only a subset of standard ODBC SQL grammer.
          It is intended for performance and does not utilize a full SQL
          parser. For example, subselects are not supported and use of "is
          NULL" for columns other than character columns is not supported.


ΓòÉΓòÉΓòÉ 6.5. INGRES Data Types ΓòÉΓòÉΓòÉ

INGRES data types map to the standard ODBC data types as follows:


INGRES    ODBC
Char      SQL_CHAR
Date      SQL_TIMESTAMP
Float     SQL_DOUBLE
Float4    SQL_REAL
Integer   SQL_INTEGER
Integer1  SQL_TINYINT
Money     SQL_DECIMAL
Smallint  SQL_SMALLINT
Varchar   SQL_VARCHAR

Note that INGRES date values do not directly map to the ODBC type
SQL_TIMESTAMP. If interval data is placed in date columns, then the driver
raises an error when attempting to read the value.


ΓòÉΓòÉΓòÉ 6.6. Isolation and Lock Levels Supported by INGRES ΓòÉΓòÉΓòÉ

INGRES supports isolation levels 1 (read committed, the default) and 3
(serializable).  INGRES supports page-level locking.


ΓòÉΓòÉΓòÉ 6.7. INGRES ODBC Conformance Level ΓòÉΓòÉΓòÉ

The INGRES driver supports the Core, Level 1, and Level 2 API functions listed
in Supported ODBC Functions.  In addition, the SQLBrowseConnect Level 2
function is supported.

The driver supports the minimum SQL grammar.


ΓòÉΓòÉΓòÉ 6.8. Number of Connections and Statements Supported by INGRES ΓòÉΓòÉΓòÉ

The INGRES 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.


ΓòÉΓòÉΓòÉ 7. MicroDecisionware Driver ΓòÉΓòÉΓòÉ

The MicroDecisionware driver supports the IBM Database 2 (DB2) and InfoHub
(formerly Teradata) database systems.  The MicroDecisionware Database Gateway
makes an InfoHub database resemble a DB2 database. These topics refer to all
databases accessed through the MicroDecisionware Gateway as DB2 databases.
Catalog functions are not currently supported for InfoHub.

The driver filename is IVMDInn.DLL.

System Requirements

Configuring Data Sources

Connecting to DB2 Using a Logon Dialog Box

Connecting to DB2 Using a Connection String

Data Types

Isolation and Lock Levels Supported

ODBC Conformance Level

Number of Connections and Statements Supported


ΓòÉΓòÉΓòÉ 7.1. System Requirements ΓòÉΓòÉΓòÉ

You must have the appropriate DB-Library and Net-Library installed to gain
access to MDI DB2 databases.

Your database must support catalog stored procedures.

For OS/2, you must have Net-Library version 2.0 (32-bit) or higher, which is
required to set up your network environment. Contact a Sybase SQL Server vendor
for more information on Net-Library.

Also, you must have DB-Library version 4.6 (32-bit) or higher, available from
Sybase.

All DLLs from Net-Library and DB-Library must be in directories listed in the
LIBPATH environment variable in your CONFIG.SYS file.


ΓòÉΓòÉΓòÉ 7.2. Configuring Data Sources ΓòÉΓòÉΓòÉ

To configure a MicroDecisionware DB2 data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV MDI and click OK.

 3. If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 4. Specify values as follows:

Data Source Name:  A string that identifies this MicroDecisionware data source
configuration in ODBC.INI. Examples include "Accounting" or "MicroDecisionware
Serv1."

Description:  An optional long description of a data source name.  For example,
"My Accounting Database" or "MicroDecisionware on Server number 1."

Server Name:  The name of the OS/2 server running the MicroDecisionware
Database Gateway.  This value will appear as the initial default in the logon
dialog box.

The following values are optional:

Server List:  The list of servers that will appear in the logon dialog box.
Separate the server names with commas.

Default Authorization ID:  The default authorization ID used to connect to your
DB2 database.  This ID is case-sensitive.  An authorization ID is required only
if security is enabled on your database.  Your ODBC application may override
this value or you may override this value in the logon dialog box.

Groups:  A list of the groups to which you belong, used to determine which
tables you can access.  Separate each group name with a comma.  Alternatively,
enter the word ALL so that you can see all table names even if you cannot
access the table.

Application Name:  The name used to identify your application.

Workstation ID:  The workstation ID used by the MicroDecisionware Database
Gateway.

Cancel Behavior:  A value to specify how a previously executed statement should
be canceled.

 1. 0 fetches all remaining records if the statement was a Select.

 2. 1 cancels the statement by calling dbcancel.  This is the default.

 3. {bmc bullet.bmp}2 closes the connection to the server for the statement.

Yield Proc:  A numeric value that determines whether you can work in other
applications when the gateway is busy.  This attribute is useful to users of
ODBC applications.  Valid values are:

 1. 0 (peek and dispatch), which causes the driver to check the Windows message
    queue and send any messages to the appropriate Windows application.

 2. 1 (no yielding, the default), which does not let you work in other
    applications.

 3. 3 (dispatch via Windows Yield function), which turns control over to the
    Windows kernel.  The Windows kernel checks the message queue and sends any
    messages to the appropriate application window.

It is recommended that you use the value 1.  The values 0 and 3 do not work
with all Windows applications.

Owner of Catalog Tables:  The owner of views of the system tables.  The tables
themselves are owned by SYSIBM.  If you have read access to the system tables,
this option does not have to be changed .  If you do not have read access, the
database administrator must create a view of the system tables in another
account and give you permission to use the view.  Enter the authorization ID
for the account that owns the views for the system tables.

Tablespace to Create Tables in:  The name of the tablespace in which new tables
are stored.  For example, DSNDB04.

DB2 Site Allows User to Log On More Than Once: A check box that specifies that
your site lets a user log on to DB2 more than once. Selecting this check box
may improve performance.

NETAPI.DLL Library Available:  A check box that specifies that the driver can
use NETAPI.DLL to get the name of your workstation.  Most major PC networks
support this feature.  If your network does not support this capability,
deselect this option.  If you supply a workstation ID, this setting is ignored.

Use Catalog Stored Procedures:  A check box that specifies whether the driver
uses Catalog Stored Procedures or direct SQL to obtain system catalog
information.

Click Translate to perform a translation of your data from one character set to
another. The Select Translator dialog box appears in which you can select a
translator to peform the translation. INTERSOLV provides a translator named
INTERSOLV OEM ANSI that translates your data from the IBM PC character set to
the ANSI character set. Click OK to leave this dialog box and perform the
translation.

The translators that are listed in this dialog box are determined by the values
listed in the ODBC Translators section of your ODBCINST.INI file.

5Click OK to write these values to ODBC.INI.  These values are now the defaults
when you connect to the data source.  You can change the defaults by
configuring your data source again.  You can override the defaults by
connecting to the data source using a connection string with alternate values.


ΓòÉΓòÉΓòÉ 7.3. Connecting to DB2 Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

 1. Type the name of the OS/2 server running the Database Gateway you wish to
    access or select the name from the Gateway Server box, which displays the
    server names specified in the Setup dialog box.

 2. If required, type your Authorization ID (case-sensitive).

 3. If required, type your password for the system (case-sensitive).

 4. Click OK to complete the logon and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 7.4. Connecting to DB2 Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI .  These values are not written to ODBC.INI.

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 the MicroDecisionware Database Gateway is

DSN=DB2 VIA MDI;SRVR=IVSRVR;UID=JOHN;PWD=XYZZY;APP=Q4

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

DataSourceName (DSN)
          A string that identifies a MicroDecisionware data source
          configuration in ODBC.INI.  Examples include "Accounting" or
          "MicroDecisionware Serv1."

ServerName (SRVR)
          The name of the OS/2 server running the MicroDecisionware Database
          Gateway.

LogonID (UID)
          The default logon ID (authorization ID) used to connect to your DB2
          database.  This ID is case-sensitive.  A logon ID is required only if
          security is enabled on your database.  If so, contact your system
          administrator to get your logon ID.

Password (PWD)
          A case-sensitive password.

Groups (GRP)
          A list of the group to which you belong, used to determine which
          tables you can access.  Set Groups to the names of any groups to
          which you belong; separate each name with a comma.  Groups=ALL lets
          your application see all table names even if you cannot access the
          table.

ApplicationName (APP)
          The name used to identify your application.

WorkstationID (WKID)
          The workstation ID used by the MicroDecisionware Database Gateway.

Cancel (CAN)
          Cancel={0 | 1 | 2}. A value that specifies how a previously executed
          statement should be canceled.

          {bmc bullet.bmp}Cancel=0 fetches all of the remaining records if the
          statement was a Select.

          {bmc bullet.bmp}Cancel=1 cancels the statement by calling dbcancel.
          Set Cancel=1 if dbcancel is supported in your client/server
          configuration.  This is the initial default.

          {bmc bullet.bmp}Cancel=2 closes the connection to the server for the
          statement.  Set Cancel=2 only if dbcancel is not supported for your
          configuration and the performance of fetching all remaining records
          is unacceptable.

Sysibm (SI)
          The authorization ID for the system tables.  The initial default is
          SYSIBM.

CreateDB (CDB)
          The name of the tablespace in which new tables are stored.  For
          example, CreateDB=DSNDB04.

MultipleLogon (MULT)
          MultipleLogon={0 | 1}. A value that specifies whether the DB2 site
          lets users log on more than once.  Set this attribute to 1 if this is
          the case; 0 otherwise.

Netapi (NAPI)
          Netapi={0 | 1}. A value that specifies whether NETAPI.DLL is
          available.  Most major PC networks support this feature. Netapi=0
          indicates it is not available; Netapi=1 indicates it is available.
          If you supply a value for the attribute WorkstationID, this attribute
          is ignored.

YieldProc (YLD)
          This option is not available in OS/2.

ModifySQL (MS)
          ModifySQL={0 | 1}. This attribute is provided for backward
          compatibility.  Specify ModifySQL=0 to have the driver understand SQL
          dialects found in earlier drivers.  Specify ModifySQL=1 (the default)
          to have the driver modify the SQL statement to conform to ODBC
          specifications.

UseCatalogStored Procedures (UCSP)
          UseCatalogStored Procedures={0 | 1}. This attribute determines
          whether the driver uses Catalog Stored Procedures or direct SQL to
          obtain system catalog information. If set to 0, the driver uses
          direct SQL. The initial default is 0.

ProfileName (PN)
          For InfoHub only. This attribute is the InfoHub profile name. If you
          specify a name, the driver issues a "Use profilename" statement at
          data source connect time.


ΓòÉΓòÉΓòÉ 7.5. Data Types ΓòÉΓòÉΓòÉ

The DB2 data types are mapped to the standard ODBC data types as follows:

DB2       ODBC
Char      SQL_CHAR
Char() For Bit Data SQL_BINARY
Date      SQL_DATE
Decimal   SQL_DECIMAL
Float     SQL_DOUBLE
Integer   SQL_INTEGER
Long Varchar SQL_LONGVARCHAR
Long Varchar For Bit Data SQL_LONGVARBINARY
Smallint  SQL_SMALLINT
Time      SQL_TIME
Timestamp SQL_TIMESTAMP
Varchar   SQL_VARCHAR
Varchar() For Bit Data SQL_VARBINARY

Note:  The Graphic, Vargraphic, and Long Vargraphic data types are not
supported.  If you create DB2 tables using these types, you will not be able to
read the records in those tables.


ΓòÉΓòÉΓòÉ 7.6. Isolation and Lock Levels Supported ΓòÉΓòÉΓòÉ

DB2 supports isolation levels 1 (repeatable read) and 2 (read committed, the
default).  DB2 supports page-level locking.


ΓòÉΓòÉΓòÉ 7.7. ODBC Conformance Level ΓòÉΓòÉΓòÉ

The MicroDecisionware driver supports the Core, Level 1, and Level 2 API
functions listed in Supported ODBC Functions.  The driver supports the minimum
SQL grammar.


ΓòÉΓòÉΓòÉ 7.8. Number of Connections and Statements Supported ΓòÉΓòÉΓòÉ

If your site does not allow multiple connections, the MicroDecisionware gateway
supports a single connection and one statement per connection.

If your site allows multiple connections and SQL_AUTOCOMMIT is 1, the
MicroDecisionware gateway supports multiple connections and multiple statements
per connection.

If your site allows multiple connections and SQL_AUTOCOMMIT is 0, the
MicroDecisionware gateway supports multiple connections and one statement per
connection.


ΓòÉΓòÉΓòÉ 8. Oracle Drivers ΓòÉΓòÉΓòÉ

The Oracle driver supports the Oracle 7 database system.

Two separate Oracle drivers support the Oracle 6 and Oracle 7 database systems.
The driver filename for Oracle 6 is IVORAnn.DLL, and for Oracle 7, IVOR7nn.DLL.


ΓòÉΓòÉΓòÉ 8.1. Oracle System Requirements ΓòÉΓòÉΓòÉ

The Oracle SQL*Net product is required to access remote Oracle databases.

The following line must be included in your CONFIG.SYS file:

SET CONFIG=C:\ORACLE\CONFIG.ORA

Oracle 6 requires ORA6O32.DLL. Oracle 7 requires ORA7O32.DLL. These 32-bit DLLs
are supplied by Oracle along with other required DLLs. All the DLLs must be in
directories that are included in the LIBPATH specification in your CONFIG.SYS
file.


ΓòÉΓòÉΓòÉ 8.2. Configuring Oracle Data Sources ΓòÉΓòÉΓòÉ

To configure an Oracle data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV Oracle or INTERSOLV Oracle7 and click
    OK.

    If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 3. Specify values as follows:

   Data Source Name:
             A string that identifies this Oracle data source configuration in
             ODBC.INI. Examples include "Accounting" or "Oracle-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "Oracle on Server number 1."

   Server Name:
             The SQL*Net connection string designating the server and database
             to be accessed.  The information required varies depending on the
             SQL*Net driver you are using.  The section "Connecting to Oracle
             Using a Connection String" gives the format of the SQL*Net
             connection string.

             The following values are optional:

   Server List:
             The list of SQL*Net connection strings that will appear in the
             logon dialog box.  Separate the strings with commas.  If the
             SQL*Net connection string contains a comma, enclose it in
             quotation marks; for example, "Serv,1", "Serv,2", "Serv,3."

   Default User Name:
             The default user name used to connect to your Oracle database.  A
             default user name is required only if security is enabled on your
             database.  Your ODBC application may override this value or you
             may override this value in the logon dialog box or connection
             string.

   Lock Time Out:
             A value that specifies whether Oracle should wait for a lock to be
             freed before raising an error when processing a Select...For
             Update Of statement. Values can be -1 (wait forever) or 0 (don't
             wait).  The default is -1.

   Array Size:
             The number of bytes the driver uses for fetching multiple rows.
             Values can be 0 to 65536; the default is 60000. Larger values
             increase throughput by reducing the number of times the driver
             fetches data across the network. Smaller values increase response
             time, as there is less of a delay waiting for the server to
             transmit data.

   Catalog Comments:
             Check this box if you want to retrieve the contents of the
             COMMENTS column in your Oracle tables.  Doing so may impact the
             performance of your queries.

 4. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

 5. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 8.3. Connecting to Oracle Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

 1. Type the SQL*Net connection string of the computer containing the Oracle
    database tables you want to access or select the string from the Server
    Name drop-down list, which displays the names you specified in the setup
    dialog box.

 2. If required, type your Oracle user name.

 3. If required, type your Oracle password.

 4. Click OK to log on to the Oracle database installed on the server you
    specified and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 8.4. Connecting to Oracle Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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:IVSRVR;UID=JOHN;PWD=XYZZY

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

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

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

DataSourceName (DSN)
          A string that identifies an Oracle data source configuration in
          ODBC.INI. Examples include "Accounting" or "Oracle-Serv1."

LogonID (UID)
          The logon ID (user name) that the application uses to connect to your
          Oracle database. A logon ID is required only if security is enabled
          on your database. If so, contact your system administrator to get
          your logon ID.

Password (PWD)
          Your password.

LockTimeOut (LTO)
          A value that specifies whether Oracle should wait for a lock to be
          freed before raising an error when processing a Select...For Update
          Of statement. Values can be -1 (wait forever, the initial default) or
          0 (don't wait).

ArraySize (AS)
          The number of bytes the driver uses for fetching multiple rows.
          Values can be 0 to 65536.  The initial default is 60000.  Larger
          values increase throughput by reducing the number of times the driver
          fetches data across the network. Smaller values increase response
          time, as there is less of a delay waiting for the server to transmit
          data.

ServerName (SRVR)
          The SQL*Net connection string designating the server and database to
          be accessed.  The information required varies depending on the
          SQL*Net driver you are using.  The SQL*Net connection string has the
          following form:

                    driver_prefix:computer_name[:sid]

          driver_prefix is a letter identifying the network protocol being
          used.  The driver prefix can be as follows:  P (named pipes), X
          (SPX), B (NetBIOS), T (TCP/IP), D (DECNet), A (Oracle Async), or AT (
          AppleTalk).  Check your Oracle documentation for other protocols.

          computer_name is the name of the Oracle Listener on your network.

          sid is the Oracle System Identifier and refers to the instance of
          Oracle running on the host.  This item is required when connecting to
          systems that support more than one instance of an Oracle database.

          If the SQL*Net connection string contains semicolons, enclose it in
          quotation marks.  See your SQL*Net documentation for more
          information.

CatalogComments (CC)
          CatalogComments={0 | 1}. A value that specifies whether the driver
          returns the contents of the COMMENTS column for catalog functions.
          CatalogComments=1 returns COMMENTS. Retrieving the COMMENTS column
          may reduce the performance of your data catalog operations.
          CatalogComments=0 does not return COMMENTS (the initial default).


ΓòÉΓòÉΓòÉ 8.5. Oracle 6 Data Types ΓòÉΓòÉΓòÉ

The Oracle 6 data types are mapped to the standard ODBC data types as follows:

Oracle 6  ODBC
Char      SQL_VARCHAR
Date      SQL_TIMESTAMP
Long      SQL_LONGVARCHAR
Long Raw  SQL_LONGVARBINARY
Number    SQL_DOUBLE
Number(p,s) SQL_DECIMAL
Raw       SQL_VARBINARY


ΓòÉΓòÉΓòÉ 8.6. Oracle 7 Data Types ΓòÉΓòÉΓòÉ

The Oracle 7 data types are mapped to the standard ODBC data types as follows:

Oracle 7  ODBC
Char      SQL_CHAR
Date      SQL_TIMESTAMP
Long      SQL_LONGVARCHAR
Long Raw  SQL_LONGVARBINARY
Number    SQL_DOUBLE
Number(p,s) SQL_DECIMAL
Raw       SQL_VARBINARY
Varchar2  SQL_VARCHAR


ΓòÉΓòÉΓòÉ 8.7. Isolation and Lock Levels Supported ΓòÉΓòÉΓòÉ

Oracle 6 and 7 both support isolation level 2 (repeatable read) only. Both
versions support record-level locking.


ΓòÉΓòÉΓòÉ 8.8. ODBC Conformance Level ΓòÉΓòÉΓòÉ

Both Oracle drivers supports the Core, Level 1, and Level 2 API functions
listed in Supported ODBC Functions.

The Oracle 6 driver also supports the Level 2 function SQLBrowseConnect.

The Oracle 7 driver also supports the following Level 2 functions:

Γûá  SQLBrowseConnect
Γûá  SQLProcedures
Γûá  SQLPrimaryKeys

Both drivers support the core SQL grammar.


ΓòÉΓòÉΓòÉ 8.9. Number of Connections and Statements Supported ΓòÉΓòÉΓòÉ

Oracle 6 and Oracle 7 support multiple connections and multiple statements per
connection.


ΓòÉΓòÉΓòÉ 9. SQL Server Driver ΓòÉΓòÉΓòÉ

The SQL Server driver supports the SQL Server database system available from
Microsoft and Sybase, Inc.  It also supports the Sybase Net gateway to DB2.

The driver filename is IVSSnn.DLL.


ΓòÉΓòÉΓòÉ 9.1. SQL Server System Requirements ΓòÉΓòÉΓòÉ

You must install the Sybase Open Client-Library and the appropriate Sybase
Net-Library to gain access to Microsoft SQL Server or Sybase SQL Server
databases.

SYBPING is a tool that is provided to test connectivity from your client
workstation to the database server (servers that are added through SQLEdit).
Use this tool to test your connection.

SQLEdit is a tool that allows you to define servers and adds them to SQL.INI.

Set the environment variable SYBASE to the directory where you installed the
System 10 client. For example, set SYBASE=C:\SQL10. You set this environment
variable in the config.sys file.


ΓòÉΓòÉΓòÉ 9.2. Configuring SQL Server Data Sources ΓòÉΓòÉΓòÉ

To configure a SQL Server data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV SQLServer and click OK.

    If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 3. Specify values as follows:

   Data Source Name:
             A string that identifies this SQL Server data source configuration
             in ODBC.INI.  Examples include "Accounting" or "SQL Server-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "SQL Server on Server number 1."

   Server Name:
             The name of the server that contains the desired database.

   Database Name:
             The name of the database to which you want to connect by default.
             If you do not specify a value, the default database defined by SQL
             Server is used.

   The following values are optional:

   Server List:
             A comma-separated list of servers that will appear in the logon
             dialog box.

   Database List:
             The databases that will be available in the SQL Server Logon
             Options dialog box.  Separate the names with commas.

   Default Logon ID:
             The default logon ID used to connect to your SQL Server database.
             This ID is case-sensitive.  A logon ID is required only if
             security is enabled on your database.  Your ODBC application may
             override this value or you may override this value in the logon
             dialog box or connection string.

   Language:
             The national language to be used by the client.  The default is
             English.

   Application Name:
             The name SQL Server uses to identify your application.

   Workstation ID:
             The workstation ID used by the client.

   Cursor Cache Size:
             The number of cursors the cursor cache can hold. The driver
             creates a cache of statements; each statement represents an open
             connection to SQL Server.  The cursor cache increases performance
             but uses database resources.  The default is 1 (one cursor).

   Yield Proc:
             (This attribute is not available in OS/2.) A numeric value that
             determines whether you can work in other applications when SQL
             Server is busy.

   Character Conversion:
             This value controls the character set conversion between SQL
             Server (version 4.8 or later) and a client application.  If you
             omit this value, no character conversion takes place on your
             server. Common values include iso-1 for ISO-8859-1, cp850 for Code
             Page 850, roman8 for Roman8 character set, and SJIS for a Japanese
             character set. See your SQL Server documentation for a complete
             list of values.

   Cancel Behavior:
             A value that specifies how a previously executed statement should
             be canceled.  Valid values are

             Γûá  0 fetches all of the remaining records if the statement was a
             Select.

             Γûá  1 cancels the statement by calling dbcancel.  This is the
             default and should be used if dbcancel is supported in your
             client/server configuration.

             Γûá  2 closes the connection to the server for the statement.  Use
             this value only if dbcancel is not supported for your
             configuration and the performance of fetching all remaining
             records is unacceptable.

   Using Gateway:
             Select this check box if you are using Sybase Net-Gateway to
             access a DB2 database with this data source.

   NETAPI.DLL Library Available:
             The driver uses NETAPI.DLL to get the name of your workstation.
             Most major PC networks support this feature.  If your network
             supports this capability, select this option.  If you supply a
             workstation ID, this field is ignored.

   Two-Phase Commit:
             This check box, when selected, enables you to have two active
             statements within a transaction, using the SQL Server two-phase
             commit services. The active statements may deadlock if they
             reference the same SQL Server table.

 4. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

 5. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 9.3. Connecting to SQL Server Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.
For SQL Server, the dialog box is as follows:

In this dialog box, do the following:

 1. Type the name of the server containing the SQL Server database tables you
    want to access (case-sensitive) or select the name from the Server Name
    drop-down list, which displays the server names you specified in the setup
    dialog box.

 2. If required, type your case-sensitive login ID.

 3. If required, type your case-sensitive password for the system.

 4. Optionally, click Options to display the SQL Server Logon Options dialog
    box and specify the initial SQL Server database to connect to and the name
    of your workstation.

 5. Click OK to log on to the SQL Server database installed on the server you
    specified and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 9.4. Connecting to SQL Server Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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 SQL Server is

DSN=Accounting;DB=PAYROLL;UID=JOHN;PWD=XYZZY

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

DataSourceName (DSN)
          A string that identifies a SQL Server data source configuration in
          ODBC.INI. Examples include "Accounting" or "SQL Server-Serv1."

ServerName (SRVR)
          The name of the server containing the SQL Server tables you want to
          access.

Database (DB)
          The name of the database to which you want to connect.

LogonID (UID)
          The case-sensitive logon ID used to connect to your SQL Server
          database.  A logon ID is required only if security is enabled on your
          database.  If so, contact your system administrator to get your logon
          ID.

Password (PWD)
          A case-sensitive password.

Language (LANG)
          The national language to be used by the client.  The initial default
          is English.

ApplicationName (APP)
          The name SQL Server uses to identify your application.

WorkstationID (WKID)
          The workstation ID used by the client.

CursorCacheSize (CCS)
          The number of cursors the cursor cache can hold.  The driver creates
          a cache of statements; each statement represents an open connection
          to SQL Server.  The cursor cache increases performance but uses
          database resources.  The initial default is 1.

YieldProc (YLD)
          (This attribute not available for OS/2.) A numeric value that
          determines if you can work in other applications when SQL Server is
          busy.

CharConv (CC)
          A value that controls the character set conversion between SQL Server
          (version 4.8 or later) and a client application.  Common values
          include iso-1 for ISO-8859-1, cp850 for Code Page 850, roman8 for the
          Roman8 character set, and SJIS for a Japanese character set.  See
          your SQL Server documentation for a complete list of values.

Cancel (CAN)
          Cancel={0 | 1 | 2}. A value that specifies how a previously executed
          statement should be canceled.  Valid values are

          Γûá  Cancel=0 fetches all remaining records if the statement was a
          Select.

          Γûá  Cancel=1 cancels the statement by calling dbcancel. Set Cancel=1
          if dbcancel is supported in your client/server configuration.  This
          is the initial default.

          Γûá  Cancel=2 closes the connection to the server for the statement.
          Set Cancel=2 only if dbcancel is not supported for your configuration
          and the performance of fetching all remaining records is
          unacceptable.

Gateway (GW)
          Gateway={0 | 1}. A value that specifies whether you are using the
          Sybase Net-Gateway to access a DB2 database with this data source.
          Set Gateway=1 if this is the case.  Otherwise, set Gateway=0 (the
          initial default).

TwoPhaseCommit (TPC)
          TwoPhaseCommit={0 | 1}. This attribute lets you have two or more
          active statements within a transaction, using the SQL Server
          two-phase commit services. Set TwoPhaseCommit=1 to use two-phase
          commit.  The active statements may deadlock if they reference the
          same SQL Server table.  Otherwise, set TwoPhaseCommit=0 (the initial
          default).

Netapi (NAPI)
          Netapi={0 | 1}. A value that specifies whether NETAPI.DLL is
          available. Netapi=0, the initial default, indicates it is not
          available; Netapi =1 indicates it is available.  If you supply a
          value for the WorkstationID attribute, this attribute is ignored.

ModifySQL (MS)
          ModifySQL={0 | 1}. This attribute is provided for backward
          compatibility. It determines whether the driver modifies SQL
          statements to conform to ODBC specifications or passes the SQL
          statement directly to SQL Server. Specify ModifySQL=1 to have the
          driver modify the SQL statement to conform to ODBC specifications.
          Specify ModifySQL=0 to have the driver understand SQL dialects found
          in earlier drivers.  The default is 1.


ΓòÉΓòÉΓòÉ 9.5. SQL Server Data Types ΓòÉΓòÉΓòÉ

The SQL Server data types are mapped to the standard ODBC data types as
follows:

SQL Server     ODBC Data Type
binary         SQL_BINARY
bit            SQL_BIT
char           SQL_CHAR
datetime       SQL_TIMESTAMP
float          SQL_FLOAT
image          SQL_LONGVARBINARY
int            SQL_INTEGER
money          SQL_DECIMAL
real           SQL_REAL
smalldatetime  SQL_TIMESTAMP
smallint       SQL_SMALLINT
smallmoney     SQL_DECIMAL
sysname        SQL_VARCHAR
text           SQL_LONGVARCHAR
timestamp      SQL_VARBINARY
tinyint        SQL_TINYINT
varbinary      SQL_VARBINARY
varchar        SQL_VARCHAR


ΓòÉΓòÉΓòÉ 9.6. Isolation and Lock Levels Supported ΓòÉΓòÉΓòÉ

SQL Server supports isolation level 1 (read committed) and 3 (serializable).
SQL Server supports page-level locking.


ΓòÉΓòÉΓòÉ 9.7. ODBC Conformance Level ΓòÉΓòÉΓòÉ

The SQL Server driver supports the Core, Level 1, and Level 2 API functions
listed in Supported ODBC Functions.  In addition, the following Level 2
functions are supported:

Γûá  SQLBrowseConnect
Γûá  SQLColumnPrivileges
Γûá  SQLForeignKeys
Γûá  SQLPrimaryKeys
Γûá  SQLProcedureColumns
Γûá  SQLProcedures
Γûá  SQLTablePrivileges

The driver supports the minimum SQL grammar.


ΓòÉΓòÉΓòÉ 9.8. Number of Connections and Statements Supported ΓòÉΓòÉΓòÉ

The SQL Server database system supports multiple connections.  With two-phased
commit, SQL Server supports multiple statements per connection. Otherwise, SQL
Server supports a single statement per connection if SQL_AUTOCOMMIT is 0 and
multiple statements per connection if SQL_AUTOCOMMIT is 1.


ΓòÉΓòÉΓòÉ 10. SQLBase Driver ΓòÉΓòÉΓòÉ

The SQLBase driver supports the Gupta SQLBase database system in the Windows
and OS/2 environments.  It also supports Gupta's SQLHost gateway to DB2.

The driver filename is IVGUPnn.DLL.


ΓòÉΓòÉΓòÉ 10.1. SQLBase System Requirements ΓòÉΓòÉΓòÉ

The SQLBase driver requires the SQLBase client DLLs SQLOS22.DLL and SQLGCI.DLL.
It also requires one or more SQLBase communication files (such as SQLPIPE.DLL)
to provide access to local and remote SQLBase databases.

The SQLBase driver is a 32-bit DLL and requires version 5.2 or greater of
SQLBase client DLLs.


ΓòÉΓòÉΓòÉ 10.2. Configuring SQLBase Data Sources ΓòÉΓòÉΓòÉ

To configure a SQLBase data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV SQLBase and click OK.

 3. If you are configuring an existing data source, select the data source name
    and click Setup.

 4. The setup dialog box appears.

 5. Specify values as follows:

   Data Source Name:
             A string that identifies this SQLBase data source configuration in
             ODBC.INI. Examples include "Accounting" or "SQLBase-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "SQLBase on Server number 1."

   Database Name:
             The name of the database to which you want to connect by default.

             The following values are optional:

   Server Name:
             The name of the server that contains the desired database.
             Specify the word LOCAL if you are using the local server.

             The server name is not required to log on.  The logon dialog box
             appears more quickly if you do not specify a server name.  If you
             do specify a server name, the Database Name drop-down list in the
             logon dialog box is populated with the names of the databases
             available on that server.

   Server List:
             A comma-separated list of servers that will appear in the Logon
             dialog box.  Specify LOCAL to add the local server to the list.

   Default User Name:
             The default user name used to connect to your SQLBase database.  A
             user name is required only if security is enabled on your
             database.  Your ODBC application may override this value or you
             may override this value in the logon dialog box or connection
             string.

   Cursor Cache Size:
             The number of cursors the cursor cache can hold. The default is 6.

   Yield Proc:
             (This attribute is not available in OS/2.) A numeric value that
             determines whether you can work in other applications when SQLBase
             is busy.

   Input Message Size:
             The number of bytes in the input message buffer.  The default is
             determined by SQLBase.  Increasing this value retrieves more
             records across the network in a single fetch.

   Lock Time Out:
             The number of seconds SQLBase should wait for a lock to be freed
             before raising an error.  Values can be -1 (wait forever) to 1800;
             the default is 300.

   No Recovery:
             Select this check box to disable transaction recovery. Selecting
             this box is dangerous because your database can become
             inconsistent in the event of a system crash.

   Release Plan:
             A number that determines whether a lock is maintained on a table
             when the cursors accessing the table are freed. Freeing the lock
             on the table results in a request to the server, which can
             decrease performance. No locks are freed when this option is set
             to the default, 0. Locks are freed when this option is set to 1.

 6. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

 7. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 10.3. Connecting to SQLBase Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a logon dialog box when you are connecting to a
data source.

In the logon dialog box, do the following:

 1. Optionally, type the name of the server containing the SQLBase database
    tables you want to access or select the name from the Server Name drop-down
    list, which displays the server names you specified in the Setup dialog
    box. Type the word LOCAL to access a local SQLBase database.

 2. Type the name of the database you want to access.  If you specified a
    server name, you can select the name from the Database Name drop-down list.

 3. If required, type your user name.

 4. If required, type your password.

 5. Click OK to complete the logon and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 10.4. Connecting to SQLBase Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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 SQLBase is

DSN=SQLBASE TABLES;DB=PAYROLL;UID=JOHN;PWD=XYZZY

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

Attribute
          Description

DataSourceName (DSN)
          A string that identifies a SQLBase data source configuration in
          ODBC.INI. Examples include "Accounting" or "SQLBase-Serv1."

Database (DB)
          The name of the database to which you want to connect.

LogonID (UID)
          The default logon ID (user name) used to connect to your SQLBase
          database. If so, contact your system administrator to get your logon
          ID.  A logon ID is required only if security is enabled on your
          database.

ServerName (SRVR)
          The name of the server containing the SQLBase database tables you
          want to access.  Specify ServerName=LOCAL if you are using the local
          server.

Servers (SRVRLIST)
          A comma-separated list of servers with which to prompt the user in a
          Logon dialog box.  Specify LOCAL to add the local server to the list.

Password (PWD)
          A case-sensitive password.

CursorCacheSize (CCS)
          The number of cursors the cursor cache can hold.  The cursor cache
          increases performance and uses few database resources.  The initial
          default is 6.

InputMessageSize (IMS)
          The number of bytes of the input message buffer.  Increasing this
          value retrieves more records across the network in a single fetch.
          The initial default is determined by SQLBase.

LockTimeOut (LTO)
          The number of seconds SQLBase should wait for a lock to be freed
          before raising an error.  Values can be -1 to 1800; -1 means wait
          forever.  The initial default is 300.

DB2IsolationLevel (DIL)
          DB2IsolationLevel={CS | RR}. The DB2 isolation level SQLHost is
          using, provided for use with SQLHost connections.  Set
          DB2IsolationLevel=CS when DB2 is using the Cursor Stability isolation
          level; DB2IsolationLevel=RR (the default) when DB2 is using
          Repeatable Read.

YieldProc (YLD)
          (This attribute is not available in OS/2.) This option, which
          determines if you can work in other applications when SQLBase is
          busy.

NoRecovery (NR)
          NoRecovery={0 | 1}. A value that enables or disables transaction
          recovery. NoRecovery=0 (the initial default) enables recovery;
          NoRecovery=1 disables recovery.  NoRecovery=1 improves performance
          but is dangerous because your database can become inconsistent in the
          event of a system crash.  See your SQLBase documentation for
          information on this option.

ReleasePlan (RP)
          ReleasePlan={0 | 1}. A number that determines whether a lock is
          maintained on a table when the cursors accessing the table are freed.
          Freeing the lock on the table results in a request to the server,
          which can decrease performance.

          When set to 0, the default, no locks on tables are freed. When set to
          1, locks are freed.


ΓòÉΓòÉΓòÉ 10.5. SQLBase Data Types ΓòÉΓòÉΓòÉ

The SQLBase data types are mapped to the standard ODBC data types as follows:

SQLBase             ODBC
Char                SQL_VARCHAR
Date                SQL_DATE
Decimal             SQL_DECIMAL
Double Precision    SQL_DOUBLE
Integer             SQL_INTEGER
Long Varchar        SQL_LONGVARCHAR
Number              SQL_DOUBLE
Real                SQL_REAL
Smallint            SQL_SMALLINT
Time                SQL_TIME
Timestamp           SQL_TIMESTAMP
Varchar             SQL_VARCHAR

Note:   The SQLBase Real data type is replaced by Float(21) when using DB2 via
the SQLHost gateway.  The Graphic, Vargraphic, and Long Vargraphic DB2 data
types are not supported.


ΓòÉΓòÉΓòÉ 10.6. Isolation and Lock Levels Supported by SQLBase ΓòÉΓòÉΓòÉ

SQLBase supports isolation levels 1 (read committed, the default) and 3
(serializable).  SQLBase also supports an alternative isolation level 1 called
cursor stability.  Your ODBC application can use this isolation level by
calling SQLSetConnectOption (1040,1).

SQLBase supports page-level locking.


ΓòÉΓòÉΓòÉ 10.7. SQLBase ODBC Conformance Level ΓòÉΓòÉΓòÉ

The SQLBase driver supports the Core, Level 1, and Level 2 API functions listed
in Supported ODBC Functions.  In addition, the following Level 2 functions are
supported:

Γûá SQLBrowseConnect
Γûá SQLColumnPrivileges
Γûá SQLForeignKeys
Γûá SQLPrimaryKeys
Γûá SQLSetPos
Γûá SQLTablePrivileges

The driver also supports backward and random fetching in SQLExtendedFetch. It
supports the core SQL grammar.


ΓòÉΓòÉΓòÉ 10.8. Number of Connections and Statements Supported by SQLBase ΓòÉΓòÉΓòÉ

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


ΓòÉΓòÉΓòÉ 11. Sybase System 10 Driver ΓòÉΓòÉΓòÉ

The Sybase System 10 driver supports the SQL Server System 10 database system
from Sybase.

The driver filename is IVSYBnn.DLL.


ΓòÉΓòÉΓòÉ 11.1. Sybase System 10 System Requirements ΓòÉΓòÉΓòÉ

You must install the Sybase Open Client-Library and the appropriate Sybase
Net-Library to gain access to System 10.

SYBPING is a tool that is provided to test connectivity from your client
workstation to the database server (servers that are added through SQLEdit).
Use this tool to test your connection.

SQLEdit is a tool that allows you to define servers and adds them to SQL.INI.

Set the environment variable SYBASE to the directory where you installed the
System 10 client. For example, set SYBASE=C:\SQL10. You set this environment
variable in the config.sys file.


ΓòÉΓòÉΓòÉ 11.2. Configuring Sybase System 10 Data Sources ΓòÉΓòÉΓòÉ

To configure a System 10 data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV Sybase System 10 and click OK.

    If you are configuring an existing data source, select the data source name
    and click Setup.

    The setup dialog box appears.

 3. Specify values as follows:

   Data Source Name:
             A string that identifies this Sybase System 10 data source
             configuration in ODBC.INI.  Examples include "Accounting" or
             "Sys10-Serv1."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Database" or "System 10 on Server number 1."

   Server Name:
             The name of the server that contains the System 10 tables you want
             to access.  If not supplied, the server name in the DSQUERY
             environment variable is used.

             The following values are optional:

   Server List:
             The list of servers that appear in the logon dialog box.  Separate
             the server names with commas.

   Database Name:
             The name of the database to which you want to connect by default.
             If you do not specify a value, the default is the database defined
             by the  system administrator for each user.

   Database List:
             The databases that appear in the logon dialog box. Separate the
             names with commas.

   Default Logon ID:
             The default logon ID used to connect to your System 10 database.
             This ID is case-sensitive.  A logon ID is required only if
             security is enabled for the database you are connecting to.  Your
             ODBC application may override this value or you may override this
             value in the logon dialog box or connection string.

   Interfaces File:
             The pathname of the interfaces file.  The default is the normal
             Sybase interfaces file.

   Password Encryption:
              A number that determines whether password encryption can be
             performed from the Open Client Library to the server
             (PasswordEncryption=1). When set to 0, the default, this cannot be
             done.

   Charset:
             The name of a character set corresponding to a subdirectory in
             $SYBASE/charsets.  The default is the setting on the System 10
             server.

   Workstation ID:
             The workstation ID used by the client.

   Language:
             The national language corresponding to a subdirectory in
             $SYBASE/locales. The default is English.

   Application Name:
             The name used by System 10 to identify your application.

   Yield Proc:
             This attribute is not available under OS/2. A numeric value that
             determines whether you can work in other applications when Sybase
             System 10 is busy.

   Optimize Prepare:
             A value of 0, 1, or 2 that determines whether stored procedures
             are created on the server for every call to SQLPrepare.

             When set to 0, stored procedures are created for every call to
             SQLPrepare. This setting can diminish performance.

             When set to 1, the initial default, the driver creates stored
             procedures only if the statement contains parameters. Otherwise,
             the statement is cached and executed directly at SQLExecute time.

             When set to 2, the driver never creates stored procedures.

   Array Size:
             The number of rows the driver retrieves from the server for each
             fetch. This is not the number of rows given to the user. The
             default is 10 rows.

   Select Method:
             A value of 0 or 1 that determines whether database cursors are
             used for Select statements. When set to 0, the default, database
             cursors are used. When set to 1, Select statements are executed
             directly, without using database cursors. A setting of 1 limits
             the data source to one active statement and one active connection.

   Packet Size:
             A number that determines the number of bytes per network packet
             transferred from the database server to the client. The correct
             setting of this attribute can improve performance.

             When set to 0, the initial default, the driver uses the default
             packet size as specified in the System 10 server configuration.

             When set to -1, the driver computes the maximum allowable packet
             size on the first connect to the data source and save the value in
             the odbc.ini file.

             When set to x, an integer from 1 to 10, which indicates a multiple
             of 512 bytes (for example, Packet Size=6 means to set the packet
             size to 6 * 512 = 3072 bytes).

             For you to take advantage of this connection attribute, you must
             configure the System 10 server for a maximum network packet size
             greater than or equal to the value you specified for Packet Size.
             For example,

                          sp_configure "maximum network packet size", 5120
                          reconfigure
                          Restart System 10 Server

             Note that the ODBC specification specifies a connect option,
             SQL_PACKET_SIZE, that offers this same functionality. To avoid
             conflicts  with applications that may set both the connection
             string attribute and the ODBC connect option, they have been
             defined as mutually exclusive. If PacketSize is specified, you
             will receive a message "Driver Not Capable" if you attempt to call
             SQL_PACKET_SIZE. If you do not set Packet Size, then application
             calls to SQL_PACKET_SIZE are accepted by the driver.

 4. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

 5. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.


ΓòÉΓòÉΓòÉ 11.3. Connecting to System 10 Using a Logon Dialog Box ΓòÉΓòÉΓòÉ

Some ODBC applications display a Logon dialog box when you are connecting to a
data source.  In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

 1. Type the case-sensitive name of the server containing the System 10
    database tables you want to access or select the name from the Server Name
    drop-down list, which displays the server names you specified in the setup
    dialog box.

 2. If required, type your case-sensitive login ID.

 3. If required, type your case-sensitive password for the system.

 4. Type the name of the database you want to access (case-sensitive) or select
    the name from the Database drop-down list, which displays the names you
    specified in the setup dialog box.

 5. Click OK to complete the logon and to update the values in ODBC.INI.


ΓòÉΓòÉΓòÉ 11.4. Connecting to System 10 Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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 System 10 is

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

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

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 ODBC.INI.  If you specified a value for the attribute when configuring the
data source, that value is your default.

Attribute
          Description

DataSourceName (DSN)
          A string that identifies a single connection to a System 10 database.
          Examples include "Accounting" or "Sys10-Serv1."

ServerName (SRVR)
          The name of the server containing the System 10 tables you want to
          access. If not supplied, the initial default is the server name in
          the DSQUERY environment variable.

LogonID (UID)
          The default logon ID used to connect to your System 10 database.
          This ID is case-sensitive.  A logon ID is required only if security
          is enabled on your database.  If so, contact your system
          administrator to get your logon ID.

Password (PWD)
          A case-sensitive password.

Database (DB)
          The name of the database to which you want to connect.

Language (LANG)
          The national language corresponding to a subdirectory in
          $SYBASE/locales.

Charset (CS)
          The name of a character set corresponding to a subdirectory in
          $SYBASE/charsets.

WorkstationID (WKID)
          The workstation ID used by the client.

ApplicationName (APP)
          The name used by System 10 to identify your application.

InterfacesFile (IFILE)
          The pathname to the interfaces file.

ArraySize (AS)
          The number of rows the driver retrieves from the server for a fetch.
          This is not the number of rows given to the user.  This increases
          performance by reducing network traffic.  The initial default is 10
          rows.

YieldProc (YLD)
          A numeric value that determines whether you can work in other
          applications when Sybase System 10 is busy.  This attribute is not
          available in OS/2.

OptimizePrepare (OP)
          OptimizePrepare={0 | 1 | 2}. A value that determines whether stored
          procedures are created on the server for every call to SQLPrepare.

          When set to 0, stored procedures are created for every call to
          SQLPrepare. This setting can result in bad performance.

          When set to 1, the initial default, the driver creates stored
          procedures only if the statement contains parameters. Otherwise, the
          statement is cached and executed directly at SQLExecute time.

          When set to 2, the driver never creates stored procedures.

SelectMethod (SM)
          SelectMethod={0 | 1}. A value that determines whether database
          cursors are used for Select statements. When set to 0, the initial
          default, database cursors are used. In some cases performance
          degradation can ocur when performing large numbers of sequential
          Select statements because of the amount of overhead associated with
          creating database cursors. When set to 1, Select statements are
          executed directly without using database cursors. When set to 1, the
          data source is limited to one active statement and one active
          connection.

Password Encryption (PE)
          Password Encryption={0 | 1}. A number that determines whether
          password encryption can be performed from the Open Client Library to
          the server (PasswordEncryption=1). When set to 0, the default, this
          cannot be done.

PacketSize (PS)
          PacketSize={-1 | 0 | x}. A number that determines the number of bytes
          per network packet transferred from the database server to the
          client. The correct setting of this attribute can improve
          performance.

          When set to 0, the initial default, the driver uses the default
          packet size as specified in the System 10 server configuration.

          When set to -1, the driver computes the maximum allowable packet size
          on the first connect to the data source and save the value in the
          odbc.ini file.

          When set to x, an integer from 1 to 10, which indicates a multiple of
          512 bytes (for example, PacketSize=6 means to set the packet size to
          6 * 512 = 3072 bytes).

          For you to take advantage of this connection attribute, you must
          configure the System 10 server for a maximum network packet size
          greater than or equal to the value you specified for PacketSize. For
          example,

                    sp_configure "maximum network packet size", 5120
                    reconfigure
                    Restart System 10 Server

          Note that the ODBC specification specifies a connect option,
          SQL_PACKET_SIZE, that offers this same functionality. To avoid
          conflicts with applications that may set both the connection string
          attribute and the ODBC connect option, they have been defined as
          mutually exclusive. If PacketSize is specified, you will receive a
          message "Driver Not Capable" if you attempt to call SQL_PACKET_SIZE.
          If you do not set PacketSize, then application calls to
          SQL_PACKET_SIZE are accepted by the driver.


ΓòÉΓòÉΓòÉ 11.5. Sybase System 10 Data Types ΓòÉΓòÉΓòÉ

The System 10 data types are mapped to the standard ODBC data types as follows:


System 10      ODBC
binary         SQL_BINARY
bit            SQL_BIT
char           SQL_CHAR
datetime       SQL_TIMESTAMP
decimal        SQL_DECIMAL
float          SQL_FLOAT
image          SQL_LONGVARBINARY
int            SQL_INTEGER
money          SQL_DECIMAL
numeric        SQL_NUMERIC
real           SQL_REAL
smalldatetime  SQL_TIMESTAMP
smallint       SQL_SMALLINT
smallmoney     SQL_DECIMAL
sysname        SQL_VARCHAR
text           SQL_LONGVARCHAR
timestamp      SQL_VARBINARY
tinyint        SQL_TINYINT
varbinary      SQL_VARBINARY
varchar        SQL_VARCHAR

Note:  The nchar, nvarchar, sensitivity, and sensitivity_boundary data types
are not supported.


ΓòÉΓòÉΓòÉ 11.6. Isolation and Lock Levels Supported by Sybase System 10 ΓòÉΓòÉΓòÉ

System 10 supports isolation levels 1 (read committed, the default) and 3
(serializable).  It supports page-level locking.


ΓòÉΓòÉΓòÉ 11.7. Sybase System 10 ODBC Conformance Level ΓòÉΓòÉΓòÉ

The Sybase System 10 driver supports the Core, Level 1, and Level 2 API
functions listed in Supported ODBC Functions.  In addition, the following Level
2 functions are supported:

Γûá  SQLBrowseConnect
Γûá  SQLColumnPrivileges
Γûá  SQLForeignKeys
Γûá  SQLPrimaryKeys
Γûá  SQLProcedureColumns
Γûá  SQLProcedures
Γûá  SQLTablePrivileges

The driver supports the minimum SQL grammar.


ΓòÉΓòÉΓòÉ 11.8. Number of Connections and Statements Supported by Sybase System 10 ΓòÉΓòÉΓòÉ

The System 10 database system supports multiple connections and multiple
statements per connection.


ΓòÉΓòÉΓòÉ 12. Text Driver ΓòÉΓòÉΓòÉ

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

The driver filename is IVTXTnn.DLL.

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.
However, you may not execute Update or Delete statements.


ΓòÉΓòÉΓòÉ 12.1. Common Text File Formats ΓòÉΓòÉΓòÉ

Some common formats for text files are listed in the following table:

Format
          Description

Comma-separated values
          Commas separate column values, and each line is a separate record.
          Column values can vary in length.  These files often have the .CSV
          extension.

Tab-separated values
          Tabs separate column values, and each line is a separate record.
          Column values can vary in length.

Character-separated values
          Any printable character except single or double quotation marks can
          separate column values, and each line is a separate record. Column
          values can vary in length.

Fixed
          No character separates column values.  Instead, values start at the
          same position and have the same length in each line.  The values
          appear in fixed columns if you display the file.  Each line is a
          separate record.

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


ΓòÉΓòÉΓòÉ 12.2. Configuring Text Data Sources ΓòÉΓòÉΓòÉ

To configure a Text data source, do the following:

 1. Start the ODBC Administrator.  A list of data sources appears.

 2. If you are configuring a new data source, click Add.  A list of installed
    drivers appears.  Select INTERSOLV TextFile and click OK.

    If you are configuring an existing data source, select the data source name
    and click Setup.

    The Setup dialog box appears.

 3. Specify values as follows:

   Data Source Name:
             A string that identifies this Text data source configuration in
             ODBC.INI. Examples include "Accounting" or "Text Files."

   Description:
             An optional long description of a data source name. For example,
             "My Accounting Files" or "My Text Files in the Accounting
             Directory."

   Database Directory:
             The directory in which the text files are stored.  If none is
             specified, the current working directory is used.

             The following values are optional:

   Rows to Scan:
             The number of rows in a text file that the driver scans to
             determine the data types in the file.  If the value is 0, all rows
             in the file are scanned. The default is 25.

   Default Table Type:
             The type of text file:  comma-separated, tab-separated,
             character-separated, or fixed length.  This value tells the driver
             the default type, which is used when creating a new table and
             opening an undefined table.

   Delimiter Character:
             The character used as a delimiter for character-separated files.
             It can be any printable character.  The default is a comma (,).

   Action for Undefined Tables:
             Two radio buttons that indicate what action the driver should take
             when it encounters a file that has not been defined.  Set the
             Prompt for Definition radio button, if you want the driver to
             prompt the user when it encounters a file whose format is not
             defined.  Otherwise, set the Guess Definition radio button; in
             this case, the driver guesses the file's format.

   Return Additional Tables:
             Select this check box to tell the driver to return files you have
             defined in functions like SQLTables, SQLColumns, etc.  and files
             with a given extension. In Extension List, specify a
             comma-separated list of the extensions.  To have files with no
             extensions returned, specify NONE.  For example, if some of your
             files have the extensions TXT and CSV and others have no
             extension, specify TXT,CSV,NONE.

             By default, when an application requests a list of tables, only
             files that have been defined are returned.

   File Open Cache:
             A numeric value to specify the maximum number of unused file opens
             to cache. For example, the value 4 specifies that when a user
             opens and closes four tables, the tables are not actually closed.
             The driver keeps them open so that if another query uses one of
             these tables, the driver does not have to perform another open,
             which is expensive.  The advantage of file open caching is
             increased performance.  The disadvantage is that a user who
             specifies file locking on open may get a locking conflict even
             though no one appears to have the file open.  The default is 0,
             which means no file open caching.

   Cache Size:
             The number of 64K blocks the driver uses to cache database
             records.  The higher the number, the better the performance.  The
             maximum number of blocks you can set depends on the system memory
             available.  If the cache size is greater than 0, when browsing
             backwards, you will not be able to see updates made by other users
             until you reexecute the Select statement. The default is 4.

   Column Names in First Line:
             Select this check box to tell the driver to look for column names
             in the first line of the file.

   International Sort:
             A setting to indicate the order in which records are retrieved
             when you issue a Select statement with an Order By clause.  Select
             this check box to use the international sort order as defined by
             your operating system.  International sort order is
             case-insensitive (a precedes B); the sorting of accented
             characters is also affected (see your operating system
             documentation).  Leave this box blank to use the ASCII sort order.
             ASCII sort order is case-sensitive, where uppercase letter precede
             lowercase letters (B precedes a).

             Note:   The Rows to Scan, Default Table Type, Delimiter Character,
             and Column Names in First Line settings apply only to tables not
             previously defined.  These fields also determine the attributes of
             new tables created with the Create Table statement.

   Use Long Qualifiers:
             A setting to enable the driver to use long pathnames as table
             qualifiers. When you set this check box, pathnames can be up to
             255 characters. The default length for pathnames is 128
             characters.

 4. Click Translate to perform a translation of your data from one character
    set to another. The Select Translator dialog box appears in which you can
    select a translator to peform the translation. INTERSOLV provides a
    translator named INTERSOLV OEM ANSI that translates your data from the IBM
    PC character set to the ANSI character set. Click OK to leave this dialog
    box and perform the translation.

 5. Click OK to write these values to ODBC.INI.  These values are now the
    defaults when you connect to the data source.  You can change the defaults
    by configuring your data source again.  You can override the defaults by
    connecting to the data source using a connection string with alternate
    values.

Click Define in this dialog box to define the structure of your text files.
Defining Table Structure discusses how to define the column names and data
types in a table.


ΓòÉΓòÉΓòÉ 12.3. Defining Text Table Structure ΓòÉΓòÉΓòÉ

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

Define the structure of a file as follows:

 1. Display the Define Table dialog box.
 2. Specify table information.
 3. Specify column information.
 4. Define the table.

Display the Define Table Dialog Box

 1. Click Define in the ODBC Text Driver Setup dialog box, which you can access
    through the ODBC Administrator.  The Define File dialog box appears.

 2. Select the correct file and click OK to define the file's structure using
    the Define Table dialog box.

    In the Define Table dialog box, Database Name and File display the name of
    the database directory that contains the file and the name of the file you
    previously selected, respectively.

Specify Table Information

In the Table Information section of this dialog box, specify information about
the overall structure of the file:

 1. In the Table box, type a table name.  Values may be up to 32 characters in
    length and may not 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. Select the Column Names in First Line check box if the first line of the
    file contains column names.  Otherwise, do not select this check box.

 3. Open the Table Type box and select a table type:  comma, tab, fixed, or
    character.

 4. If the table type is character-separated, type the character that separates
    the values in the Delimiter Character box.

 5. If you are using Windows, select the OEM to ANSI Translation check box to
    tell the driver that the data is stored in the IBM PC character set.  If
    your data is stored in the ANSI character set, do not select this check
    box.  This changes the display format only; the data is not converted.

Specify Column Information

In the Column Information section, define the types and names of the columns in
the table.  The box in the upper-left corner of this section lists the defined
columns.

If you specified a comma-separated, tab-separated, or character-separated table
type, the Guess button appears in this section.  Click Guess to tell the driver
to guess the fields.  The driver then displays what it thinks the fields are.
You can then modify the field definitions by specifying values in the Name,
Type, Mask, Precision, and Scale boxes (as appropriate) and clicking Modify.
If you do not want the driver to guess the fields, take the following steps to
define the fields:

 1. In the Name box, type the name of the field.

 2. Open the Type drop-down list and select the data type of the field. If the
    field type is date, then you must select a date mask for the field or type
    one in.  See the following section, "Date Masks," for more information.

 3. In the Precision box, type the precision of the field.

 4. In the Scale box, type the scale of the field.

    The precision and scale values determine how numeric data is to be
    returned.

 5. If you specified a fixed-length table type, you may specify the length and
    offset in the Length and Offset boxes, or you can specify a parse string.
    The length is the number of bytes the data takes up in storage; the offset
    is the number of bytes from the start of the table to the start of the
    field.

 6. Click Add to add this field definition to the list box.

To modify the selected field in the list box, click Modify.

To remove the selected field in the list box, click Remove.

If you specified a fixed-length table type, the Parse button is displayed. If
you have not entered values in the length and offset boxes, click Parse to
define the columns of the table.  The Parse Table dialog box appears.

This dialog box displays the first line of the file.  You must mark where each
field begins and ends by enclosing it in brackets.  These brackets indicate the
position and length of each field value in the record.  You must do this for
all the fields in the table.  Click OK when you are done.

Define the Table

Click OK to define the table.


ΓòÉΓòÉΓòÉ 12.4. Date Masks for Text Driver ΓòÉΓòÉΓòÉ

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 following table lists the symbols to use when specifying the date mask:

Symbol
          Description

m
          Output the month's number (1-12).

mm
          Output a leading zero if the month number is less than 10.

mmm, Mmm, MMM
          Output the three-letter abbreviation for the month depending on the
          case of the M's (that is, jan, Jan, JAN).

mmmm, Mmmm, MMMM
          Output the full month name depending on the case of the M's (that is,
          january, January, JANUARY).

d
          Output the day number (1-31).

dd
          Output a leading zero if the day number is less than 10.

ddd, Ddd, DDD
          Output the three-letter day abbreviation depending on the case of the
          D's (that is, mon, Mon, MON).

dddd, Dddd, DDDD
          Output the day depending on the case of the D's (that is, monday,
          Monday, MONDAY).

yy
          Output the last two digits of the year.

yyyy
          Output the full four digits of the year.

J
          Output the Julian value for the date.  The Julian value is the number
          of days since 4712 BC.

 / - .  :  , (space)
          Output special characters used to separate the parts of a date.

 /
          Output the next character.  For example, if the mask is mm/dd/yyyy
          /A/D, the value appears as 10/01/1993 AD in the text file.

"string", 'string'
          Output the string in the text file.

          The following table shows some example masks and output when applied
          to the date value "1993-10-01".

   Mask
             Value

   yyyy-mm-dd
             1993-10-01

   m/d/yy
             10/1/93

   Ddd, Mmm dd, yyyy
             Fri, Oct 01, 1993


ΓòÉΓòÉΓòÉ 12.5. Connecting to a Text Database Using a Connection String ΓòÉΓòÉΓòÉ

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 ODBC.INI
section 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 ODBC.INI.  These values are not written to ODBC.INI.

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 following list gives the long and short names for each attribute, as well
as a description.

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

Attribute
          Description

DataSourceName (DSN)
          A string that identifies a Text data source configuration in
          ODBC.INI. Examples include "Accounting" or "Text Files."

Database (DB)
          The directory in which the text files are stored.

ScanRows (SR)
          The number of rows in a text file that the driver scans to determine
          the column types in the file.  If the value is 0, all rows in the
          file are scanned. The initial default is 25.

TableType (TT)
          TableType={Comma | Tab | Character | Fixed}. The Text driver supports
          four types: comma-separated, tab-separated, character-separated, and
          fixed length.  Setting this value tells the driver the default type,
          which is used when creating a new table and opening an undefined
          table.

Delimiter (DC)
          The character used as a delimiter for character-separated files.  It
          can be any printable character except a single or double quote.  The
          initial default is a comma (,).

UndefinedTable (UT)
          The Text driver can perform two operations when it encounters a file
          that has not been defined. UndefinedTable=PROMPT tells the driver to
          display a dialog box that allows the user to describe the file's
          format.  UndefinedTable=GUESS tells the driver to guess the file's
          format.  This is the initial default.

ExtraExtensions (EE)
          A list of additional filename extensions to be recognized as text
          tables. When an application requests a list of tables, only files
          that have been defined are returned.  To have the driver also return
          names of undefined files, specify a comma-separated list of file
          extensions.  To specify files with no extension, use the keyword
          NONE.

FileOpenCache (FOC)
          The maximum number of unused file opens to cache.  For example, when
          FileOpenCache=4, and a user opens and closes four files, the files
          are not actually closed.  The driver keeps them open so that if
          another query uses one of these files, the driver does not have to
          perform another open, which is expensive.  The advantage of using
          file open caching is increased performance. The disadvantage is that
          a user who tries to open the file exclusively may get a locking
          conflict even though no one appears to have the file open.  The
          initial default is 0.

CacheSize (CSZ)
          The number of 64K blocks the driver uses to cache database records.
          The higher the number, the better the performance.  The maximum
          number of blocks you can set depends on the system memory available.
          If the cache size is greater than 0, when browsing backwards, you
          will not be able to see updates made by other users until you
          reexecute the Select statement. The default is 4.

FirstLineNames (FLN)
          FirstLineNames={0 | 1}. A value that determines whether the driver
          looks for column names in the first line of the file.  If
          FirstLineNames=1, the driver looks for column names in the first line
          of the file.  If FirstLineNames=0 (the initial default), the first
          line is interpreted as the first record in the file.

IntlSort (IS)
          IntlSort={0 | 1}. A value that determines the order that records are
          retrieved when you issue a Select statement with an Order By clause.
          If IntlSort=1, the driver uses the international sort order as
          defined by your operating system.  The sort is case-insensitive (a
          precedes B); the sorting of accented characters is also affected (see
          your operating system documentation). If IntlSort=0 (the initial
          default), the driver uses the ASCII sort order, where uppercase
          letters precede lowercase letters (B precedes a).

UseLongQualifiers (ULQ):
          UseLongQualifiers={0 | 1}. Enables the driver to use long pathnames
          as table qualifiers. The default is 0, do not use long pathnames. The
          default length for pathnames is 128 characters. When
          UseLongQualifiers=1, pathnames can be up to 255 characters.

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


ΓòÉΓòÉΓòÉ 12.6. Text Data Types ΓòÉΓòÉΓòÉ

The text file data types are mapped to the standard ODBC data types as follows:


Text      ODBC
Numeric   SQL_NUMERIC
Date      SQL_DATE
Varchar   SQL_VARCHAR


ΓòÉΓòÉΓòÉ 12.7. Text 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 SQL for
Flat-File Drivers.


ΓòÉΓòÉΓòÉ 12.8. Text ODBC Conformance Level ΓòÉΓòÉΓòÉ

The Text driver supports the Core, Level 1, and Level 2 API functions listed in
Supported ODBC Functions.  It also supports backward and random fetching in
SQLExtendedFetch.

The driver supports the minimum SQL grammar.


ΓòÉΓòÉΓòÉ 12.9. Number of Connections and Statements Supported by Text ΓòÉΓòÉΓòÉ

Text files support multiple connections and multiple statements per connection.