Copyright: , 1996 INTERSOLV


Contents, Btrieve Driver Help
	DataDirect ODBC  Btrieve Driver
About the Btrieve Driver

System Requirements

Managing Databases

Installing Btrieve to Process Transactions

Configuring Data Sources

Defining Table Structure

Connecting to Btrieve Using a Connection String

Data Types

Indexes

Select Statement

Create and Drop Index Statements

Isolation and Lock Levels Supported

ODBC Conformance Level

Number of Connections and Statements Supported

Copyright


About the Btrieve Driver

The Btrieve driver supports Btrieve version 5.0 and higher.  The driver executes SQL statements directly on Btrieve databases.

The driver filename is IVBTRnn.DLL.


Copyright
Copyright 1996 INTERSOLV Inc. All rights reserved. INTERSOLV is a registered trademark, and DataDirect is a trademark of INTERSOLV, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


Btrieve System Requirements
System Requirements

To access a Btrieve database, you must be using one of the following software packages:

Btrieve Developer’s Kit for Windows NT

Btrieve Developer’s Kit for Windows NT/Windows 95

Btrieve for Windows NT (Server Edition)

Btrieve Workstation Software for Windows NT/Windows 95

Before you attempt to access Btrieve files, you must incorporate existing Btrieve files into a Scalable SQL database.  To do this, see Defining Table Structure.

If you attempt to configure a data source and you do not have WBTRV32.DLL on your path or in your Windows NT \SYSTEM32 or Windows \SYSTEM directory, the following message appears:

The setup routines for the INTERSOLV Btrieve ODBC driver could not be loaded.  You may be low on memory and need to quit a few applications.

Note: The Btrieve driver may experience problems if the Btrieve Microkernal Engine’s communication buffer size is smaller than the Btrieve driver’s Array Size attribute. You can increase the communication buffer size with the BTI Database Setup Utility. You can decrease the array size option when you configure a data source using the ODBC Btrieve Setup dialog, or when passing a connection string.


Managing Databases in Btrieve
Managing Databases

If you already use Scalable SQL, the Btrieve driver can access your Scalable SQL databases directly.  If not, your Btrieve files must be incorporated into a Scalable SQL database.

A Scalable SQL database is composed of data files that contain your records and data dictionary files that describe the database.  The data files are Btrieve files.  The data dictionary files are special Btrieve files that contain descriptions of the data files, views, fields, and indexes in your database.

All Btrieve files in a Scalable SQL database must reside in the same directory.  In addition to the Btrieve data files, the three data dictionary files, FILE.DDF, FIELD.DDF, and INDEX.DDF, also must be in the directory.

Incorporating a Btrieve file into a Scalable SQL database does not change the Btrieve file in any way.  You can continue to access the file directly with any existing Btrieve application.


Installing Btrieve to Process Transactions

The Btrieve driver supports transactions.  A transaction is a series of database changes that is treated as a single unit.  In applications that don't use transactions, the Btrieve driver immediately executes Insert, Update, and Delete statements on the database files and the changes are automatically committed when the SQL statement is executed.  You cannot undo these changes.  In applications that use transactions, the Btrieve driver holds inserts, updates, and deletes until you issue a COMMIT or ROLLBACK.  A COMMIT saves the changes to the database file; a ROLLBACK undoes the changes.

Transactions affect the removal of record locking.  All locks are removed when SQLTransact is called with the Commit or Rollback option to end the active transaction.

To use the Btrieve driver's transaction processing capabilities, you must change the required settings for Btrieve in the BTI.INI file.  Edit your BTI.INI file to contain the following options setting in the [btrieve] section:

options=/m:64 /p:4096 /t:c:\windows_directory\bbt.trn /n:12

This setting specifies the following options:

/m	The total size, in kilobytes, of Btrieve's data area.  64 is the maximum value that can be specified.
/p	The maximum page size of any Btrieve file you want to access.  4096 is the maximum value that can be specified.
/t	The pathname to the transaction control file used to process transactions.  In a network environment, the transaction control file should be on a network disk drive, and all users who start Btrieve should use the same file.
/n	The maximum number of files involved per transaction.  This number must not exceed 18.
The complete set of options are documented in the Btrieve for Windows NT Installation and Operations Manual, in the section "Starting the Record Manager."

For example, to have a maximum of 16 files per transaction and to place the transaction control file in F:\BTRANS\BTRIEVE.TRN, use these settings in the options specification:

/n:16 /t:f:\btrans\btrieve.trn

Add a [brequestDPMI] section to the appropriate INI file to initialize the requester DLL.  For example,

[brequestDPMI]
datalength=4096
chkparms=no
local={yes | no}
tasks=10

The datalength setting must be at least as large as the largest record to be accessed.

Set local=no if you are accessing network files and WBTRCALL.DLL is the networked version.  Set local=yes if you are accessing local files and the non-networked version of WBTRCALL.DLL is on your disk.  If you want to access both network and local files, you must have the networked version of WBTRCALL.DLL, rename the local version of WBTRCALL.DLL to WBTRLOCL.DLL, and set local=yes.  If you are using a requester with a version earlier than 6.0A, the local=yes line must not have anything after the yes (no spaces, no comment).


Configuring Btrieve Data Sources
Configuring Data Sources

To configure a Btrieve 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 Btrieve and click OK.

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

The Btrieve ODBC Setup dialog box appears.

3	Specify a data source name and database directory. Specifying a database description is optional.

4	To configure additional, optional settings for the data source, click Advanced. The Advanced dialog box appears.

5	To define table structure, click Define. See Defining Table Structure for step-by-step instructions.

6	To perform a translation of your data from one character set to another, click Translate. 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.

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.


Defining Btrieve Table Structure
Defining Table Structure

Since Btrieve does not store any column information in the data file, you must define its structure through the Btrieve setup dialog box, which you can access through the ODBC Administrator.

To define the structure of a file, take the following steps:

1	Click Define in the setup dialog box.  The Define File dialog box appears.

2	Select the file you want to define and click OK.  The Define Table dialog box appears.

In this dialog box, Database Name and File display the name of the directory containing the Scalable SQL data dictionary files and the name of the file, respectively.

3	In the Table Information section of this dialog box, type the name of the table to be returned by SQLTables.  The name can be up to 20 characters and may not be the same as another defined table in the database.  This field is required.

4	The Column Information section of this dialog box lets you add, modify, and delete columns. The box at the top of this section displays the defined columns. To add a column definition, do the following:

a	In the Name box, type the name of the column.

b	In the Type box, open the drop-down list and select the column's data type.

c	In the Length box, type the column's length, if applicable.

d	In the Scale box, type the column's scale, if applicable.

e	Click Add.

To modify a column's definition, select the column you want to modify, type the new definition in the appropriate boxes (refer to steps 4a-d), and click Modify.

To remove a column's definition, select the column you want to remove and click Remove.

5	Click OK to define the table.


Connecting to Btrieve 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 Btrieve is

DSN=BTRIEVE FILES;DB=EMP;UID=JOHN;PWD=XYZZY

The following table lists 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 Btrieve data source configuration in ODBC.INI.  Examples include "Accounting" or "Btrieve Files."
Database (DB)	Directory in which the Btrieve files and the data dictionary files (.DDF) are stored.  Data dictionary files describe the structure of Btrieve data.  These files must exist to connect with the driver.
UndefinedTable (UT)	UndefinedTable={PROMPT | ERROR} Determines whether the driver should prompt the user when it encounters a table for which it has no structure information.  Set this option to PROMPT to prompt the user; set it to ERROR to return an error.  The initial default is to return an error.
FileOpenCache (FOC)	Determines the maximum number of unused file opens to cache.  For example, when FileOpenCache=4, and a user opens and closes 4 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 using 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 initial default is 0, which means no file open caching.
LogonID (UID)	Default logon ID used to connect to your Btrieve 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 that you must enter if your Scalable SQL data dictionary files have security restrictions imposed.
Version (VER)	Version={5 | 6} The version of Btrieve you want to access.  Valid values are 5 and 6.  The initial default is 5.
IntlSort (IS)	IntlSort={0 | 1} 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).
ModifySQL (MS)	ModifySQL={0 | 1}.  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.
DeferQueryEvaluation (DQ)	DeferQueryEvaluation={0 | 1}. Determines when a query is evaluated--after all records are read or each time a record is fetched. If DeferQueryEvaluation=0, the driver generates a result set when the first record is fetched.  The driver reads all records, evaluates each one against the Where clause, and compiles a result set containing the records that satisfy the search criteria.  This process slows performance when the first record is fetched, but activity performed on the result set after this point is much faster because the result set has already been created.  You do not see any additions, deletions, or changes in the database that occur while working with this result set. If DeferQueryEvaluation=1 (the default), the driver evaluates the query each time another record is fetched and stops reading through the records when it finds one that matches the search criteria.  This setting avoids the slowdown while fetching the first record, but each fetch takes longer because of the evaluation taking place. The data you retrieve reflects the latest changes to the database.  However, a result set is still generated if the query is a Union of multiple Select statements, if it contains the Distinct keyword, or if it has an Order By or Group By clause.
DataFileExtension (DFE).	String of three or fewer characters that specifies the file extension to use for data files. The default DataFileExtension value is DTA. The DataFileExtension value is used for all CREATE TABLE statements. Sending a CREATE TABLE using an extension other than the DataFileExtension value causes an error.  In other SQL statements such as SELECT or INSERT, users can specify an extension other than the DataFileExtension value. The DataFileExtension value is used when no extension is specified.
ArraySize (AS)	Enables the driver to retrieve an array of records from the Btrieve engine and in most cases results in better performance for the application. The value of ArraySize is the number of bytes in the array. The default ArraySize is 4,096 bytes and the maximum is 65,535 bytes.


Btrieve Data Types
Data Types

The following table shows how the Btrieve data types map to the standard ODBC data types.  The Btrieve data types are used when you incorporate Btrieve files into a Scalable SQL database.

Btrieve	ODBC
Autoincrement(2)	SQL_SMALLINT
Autoincrement(4)	SQL_INTEGER
Bfloat(4)	SQL_REAL
Bfloat(8)	SQL_DOUBLE
Char	SQL_CHAR
Date	SQL_DATE
Decimal	SQL_DECIMAL
Float(4)	SQL_REAL
Float(8)	SQL_DOUBLE
Integer(1)	SQL_TINYINT
Integer(2)	SQL_SMALLINT
Integer(4)	SQL_INTEGER
Logical(1)	SQL_BIT
Logical(2)	SQL_BIT
Lstring	SQL_VARCHAR
Money	SQL_DECIMAL
Note	SQL_LONGVARCHAR
Numeric	SQL_NUMERIC
Numericsts	SQL_NUMERIC
Time	SQL_TIME
Zstring	SQL_VARCHAR


Btrieve Indexes
Indexes

Note  If you define an index using the INTERSOLV Btrieve driver, the index will not have the restrictions discussed here.For query optimization, the Btrieve driver does not use:

indexes containing all-segment-null keys or any-segment-null keys.

any index key that is marked case-insensitive.

any index keys where the data type of the index key does not match the data type of the field. The one exception is if the index key is declared as an unsigned integer and the field in the file is declared as signed integer, or vice versa, then the driver assumes the field contains only unsigned quantities and uses the index. Note that this can lead to incorrect results if the field in fact does contain signed quantities.

The Btrieve driver only uses an alternate-collating-sequence (ASC) index key for equality lookups. Additionally, if an ASC key is part of a segmented index, the other index segments are not used for query optimization unless the WHERE clause contains an equality condition for the ASC key.


Btrieve Column Names
Column Names

Column names in SQL statements can be up to 20 characters long.  If column names are in all lowercase, a combination of upper and lowercase, contain blank spaces, or are reserved words, they must be surrounded by the grave character ( ` ) (ASCII 96).  For example,

SELECT `name` FROM emp


Select Statement for Btrieve
Select Statement

You use the SQL Select statement to specify the columns and records to be read.  Btrieve Select statements support all the Select statement clauses. The following topics describe SQL statement information that is specific to the Btrieve driver.

ROWID Psuedo-Column

ALTER TABLE Statement

Create and Drop Index Statements for Btrieve


Btrieve Tables and ROWID Psuedo-Column
ROWID Psuedo-Column

Each Btrieve record contains a special column named ROWID. This field contains a unique number that indicates the record's sequence in the database. You can use the 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 tyou want to update to ensure that you are updating the correct record and no other. For example

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

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

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

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

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


Alter Table Statement

The Btrieve driver supports the Alter Table statement to add one or more columns to the table, or to delete (drop) a single column.

The Alter Table statement has the form:

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

table_name is the name of the table for which you are adding or dropping columns.

column_name assigns a name to the column you are adding or specifies the column you are dropping. See Column Names for more information.

data_type specifies the native data type of each column you add. See Btrieve Data Types for more information.

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

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

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

ALTER TABLE emp DROP startdate

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


Create and Drop Index Statements for Btrieve
Create and Drop Index Statements

The Btrieve driver supports SQL statements to create and delete indexes. The Create Index statement is used to create indexes and the Drop Index is used to delete indexes.

Create Index

The Create Index statement for Btrieve files has the form:

CREATE [UNIQUE] INDEX index_name ON table_name
([field_name [ASC | DESC] [,field_name [ASC | DESC]]...)

UNIQUE means that Btrieve does not let you insert two records with the same index values.

index_name is the name of the index.

table_name is the name of the table on which the index is to be created.

ASC tells Btrieve to create the index in ascending order.  DESC tells Btrieve to create the index in descending order.  By default, indexes are created in ascending order.

For example,

CREATE INDEX lname ON emp (last_name)

Drop Index

The form of the Drop Index statement is

DROP INDEX table_name.index_name

table_name is the name of the table from which the index is to be dropped.

index_name is the name of the index.

For example,

DROP INDEX emp.lname


Isolation and Lock Levels Supported by Btrieve
Isolation and Lock Levels Supported

Btrieve supports isolation level 1 (read committed) only.  Btrieve supports page-level locking.


Btrieve ODBC Conformance Level
ODBC Conformance Level

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

The driver supports the minimum SQL grammar.


Number of Connections and Statements Supported by Btrieve
Number of Connections and Statements Supported

Btrieve files support a single connection and multiple statements per connection.


Btrieve ODBC Setup Dialog

Use the Btrieve ODBC Setup dialog to create new Btrieve data sources or configure existing data sources.

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

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

Database Directory:  The full pathname of the directory that contains the Btrieve files and the data dictionary files (.DDF). Data dictionary files describe the structure of Btrieve data. If no directory is specified, the current working directory is used.

OK

Creates or modifies the current data source using the options you specify.

Cancel

Exits the ODBC Setup dialog without creating or modifying a data source.

Advanced

Displays the Advanced Dialog which offers additional configuration options for this data source.


Advanced Dialog

The Advanced data source settings are optional.

Default Logon ID:  The default logon ID used to connect to your Btrieve database.  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 a connection string.

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.

Array Size:  A value that enables the driver to retrieve an array of records from the Btrieve engine and in most cases results in better performance for the application. The Array Size value is the number of bytes in the array. The default value is 4096 bytes and the maximum is 65535 bytes.

Data File Extension:  Specifies the file extension to use for data files. The default Data File Extension setting is DTA. The Data File Extension setting cannot be greater than three characters. The Data File Extension setting is used for all CREATE TABLE statements. Sending a CREATE TABLE using an extension other than the Data File Extension setting causes an error.

In other SQL statements such as SELECT or INSERT, users can specify an extension other than the Data File Extension setting. The Data File Extension setting is used when no extension is specified.

Action for Undefined Tables:  A setting to indicate whether the driver should prompt the user when it encounters a table for which it has no structure information.  Set the Prompt for Definition radio button to prompt the user; set the Return Error Condition radio button (the default) to return an error.

Version: Indicates the Btrieve version. Choose the Btrieve 6.x radio button when using the Btrieve driver for Windows NT and Windows 95.

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 ASCII sort order.  ASCII sort order is case-sensitive, where uppercase letters precede lowercase letters (B precedes a).

Define

Displays the Define File dialog box to let you select a definition file, and then displays the Define Table dialog box.

Translate

Displays the Select Translator dialog box to allow you to perform a translation of your data from one character set to another. Choose the INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

Close

Returns to the Btrieve ODBC Setup dialog box where you can click the OK button to write these settings to the ODBC.INI file.


Btrieve Define File Dialog
Define Table Dialog

Because Btrieve does not store any column information in the data file, you must define the structure of the data file using the Btrieve ODBC Setup dialog box.

To define the structure of a file, take the following steps:

1	Click Define in the setup dialog box.  The Define File dialog box appears.

2	Select the file you want to define and click OK.  The Define Table dialog box appears.

In this dialog box, Database Name and File display the name of the directory containing the Scalable SQL data dictionary files and the name of the file, respectively.

3	In the Table Information section of this dialog box, type the name of the table to be returned by SQLTables.  The name can be up to 20 characters and may not be the same as another defined table in the database.  This field is required.

4	The Column Information section of this dialog box lets you add, modify, and delete columns.  The box at the top of this section displays the defined columns.  To add a column definition, do the following:

a	In the Name box, type the name of the column.

b	In the Type box, open the drop-down list and select the column's data type.

c	In the Length box, type the column's length, if applicable.

d	In the Scale box, type the column's scale, if applicable.

e	Click Add.

To modify a column's definition, select the column you want to modify, type the new definition in the appropriate boxes (refer to steps 4a-d), and click Modify.

To remove a column's definition, select the column you want to remove and click Remove.

5	Click OK to define the table.