COBSQL Error Messages | Embedded SQL Statements |
The supported UNIX platforms are: Solaris, AIX, and HP-UX. System requirements are:
Solaris:
AIX:
HP-UX cFront Enabled:
HP-UX aCC Enabled:
HP-UX 11 aCC Enabled:
In the UNIX environment, there is no ODBC Administrator. To configure a data source, you must edit the system information file, a plain text file that is normally located in the user's $HOME directory and is usually called .odbc.ini. This file is maintained using any text editor to define data source entries as described in the section Configuring and Connecting to Data Sources for each driver. A sample file .odbc.ini is located in the driver installation directory.
UNIX support of the database drivers also permits the use of a centralized system information file that a system administrator can control. This is accomplished by setting the environment variable ODBCINI to point to the fully qualified pathname of the centralized file.
Set this variable as follows:
C shell:
setenv ODBCINI /opt/odbc/system_odbc.ini
Bourne shell and Korn shell:
ODBCINI=/opt/odbc/system_odbc.ini;export ODBCINI
The search order for the location of the system information file is as follows:
There must be an [ODBC] section in the system information file that includes the InstallDir keyword. The value of this keyword must be the path to the directory under which the /lib and /messages directories are contained. For example, if you choose the default install directory, then the following line must be in the [ODBC] section:
InstallDir=/opt/odbc
In the following sample, xx
represents the
driver number:
[ODBC Data Sources] Oracle7=Sample Oracle dsn dBase=Sample dBASE dsn Sybase=Sample Sybase dsn Informix=Sample Infofrmix dsn OpenIngres=Sample OpenIngres dsn DB2=Sample DB2 dsn Text=Sample Text file dsn [dBase] Driver=/opt/odbc/lib/ivdbfxx.so Description=dBase Database=/opt/odbc/demo [Sybase] Driver=/opt/odbc/lib/ivsybxx.so Description=Sybase Database=odbc ServerName=SYBASE LogonID=odbc01 Password=odbc01 OptimizePrepare=2 SelectMethod=1 [Oracle7] Driver=/opt/odbc/lib/ivor7xx.so Description=Oracle7 ServerName=oraclehost LogonID=odbc01 Password=odbc01 [Informix] Driver=/opt/odbc/lib/ivinfxx.so Description=Informix7 Database=odbc HostName=informixhost LogonID=odbc01 Password=odbc01 [DB2] Driver=/opt/odbc/lib/ivdb2xx.so Description=DB2 Database=ODBC [OpenIngres] Driver=/opt/odbc/lib/ivoingxx.so ServerName=ingreshost Database=odbc LogonID=odbc01 Password=odbc01 [Text] Driver=/opt/odbc/lib/ivtxtxx.so Description=Text driver Database=/opt/odbc/demo [ODBC] Trace=0 TraceFile=odbctrace.out TraceDll=/opt/odbc/lib/odbctrac.so InstallDir=/opt/odbc
Connect ODBC drivers require several environment variables to be set.
Most of the variables can be set by executing the appropriate shell script located in the ODBC home directory.
Before attempting to use ODBC-enabled applications, execute the following command to initialize your environment:
C shell (and related shell):
% source odbc.csh
Bourne shell (and related shell):
$ . odbc.sh
Executing these scripts will set the appropriate library search path environment variable (LD_LIBRARY_PATH on Solaris, SHLIB_PATH on HP/UX, or LIBPATH on AIX).
The library search path environment variables are required to be set so that the ODBC core components and drivers can be located at the time of execution.
Many of the Connect ODBC drivers must have environment variables set as required by the database client components used by the drivers. Consult the system requirements in each of the individual driver sections for additional information pertaining to individual driver requirements.
ODBCINI is an optional environment variable that all Connect ODBC drivers will recognize. ODBCINI is used to locate a system information file other than the default file and is described in detail in the section The System Information File (.odbc.ini).
Connect ODBC drivers are capable of using double-byte character sets. The drivers normally use the character set defined by the default locale "C" unless explicitly pointed to another character set. The default locale "C" corresponds to the 7-bit ASCII character set in which only characters from ISO 8859-1 are valid. Use the following procedure to set the locale to a different character set.
setlocale (LC_ALL, "");
This is a standard UNIX function. It selects the character set indicated by the environment variable LANG as the one to be used by X/Open compliant character handling functions. If this line is not present, or if LANG is either not set or is set to NULL, the default locale "C" is used.
locale -a
can be used to display all
supported character sets on your system. For more information, see the man pages for "locale" and "setlocale."
The ivtestlib tool is provided to help diagnose configuration problems (such as environment variables not correctly set or missing DBMS client components) in the UNIX environment. This command attempts to load a specified ODBC driver and prints out all available error information if the load fails.
On HP-UX, for example, if a driver is installed in /opt/odbc, the command:
ivtestlib /opt/odbc/lib/ivinfxx.sl
(where xx represents the driver number) attempts to load the INFORMIX driver. If the driver cannot be loaded, ivtestlib returns an error message explaining why.
Note: On Solaris and AIX, the full path to the driver does not have to be specified for ivtestlib. The HP-UX version of ivtestlib, however, requires the full path.
MERANT provides a sample translator that provides a framework for coding a translation library.
You must add the TranslationSharedLibrary keyword to the data source section of the system information file to perform a translation. Adding the TranslationOption keyword is optional.
Keyword |
Definition |
---|---|
TranslationSharedLibrary |
Full path of translation library |
TranslationOption |
ASCII representation of the 32-bit integer translation option |
Please consult DataDirect Connect documentation for further information.
The following software is required for the client side:
Solaris
HP-UX
AIX
The following software is required for the server side:
Access to DB2 requires that you bind and grant privileges to the DataDirect bind files, a process described in this section.
Enter the DB2 command line processor by typing db2
from a
shell prompt.
Note: The DB2 command processor prompt is db2=>.
Once inside the DB2 command line processor, the first step is to connect your DB2 database using the following syntax:
db2=> CONNECT TO <database_name> USER <userid> USING <password>
The next step is to bind the DataDirect SQL files to the database. You
may choose to use special options on the BIND command, based on your
installation. Consult the manual Command Reference in the
DB2 manual set for a detailed list of BIND options. To
bind the files, enter the commands listed in the following sections. To
exit the DB2 command processor, enter the verb quit
.
Solaris
db2=> BIND iscsso.BND blocking all grant public db2=> BIND isrrso.BND blocking all grant public db2=> BIND isurso.BND blocking all grant public db2=> BIND iscswhso.BND blocking all grant public db2=> BIND isrrwhso.BND blocking all grant public db2=> BIND isurwhso.BND blocking all grant public
HP-UX
db2=> BIND iscshp.BND blocking all grant public db2=> BIND isrrhp.BND blocking all grant public db2=> BIND isurhp.BND blocking all grant public db2=> BIND iscswhhp.BND blocking all grant public db2=> BIND isrrwhhp.BND blocking all grant public db2=> BIND isurwhhp.BND blocking all grant public
AIX
db2=> BIND iscsax.BND blocking all grant public db2=> BIND isrrax.BND blocking all grant public db2=> BIND isurax.BND blocking all grant public db2=> BIND iscswhax.BND blocking all grant public db2=> BIND isrrwhax.BND blocking all grant public db2=> BIND isurwhax.BND blocking all grant public
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 DB2 is:
DSN=DB22 TABLES;DB=PAYROLL;UID=JOHN;PWD=XYZZY;GRP=ACCTNG
The following table gives the long and short names for each attribute, as well as a description.
The defaults listed in the table below are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Attribute |
Description |
---|---|
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards. |
CursorBehavior (CB) |
CursorBehavior={0 | 1}. This attribute determines whether cursors are preserved or closed at the end of each transaction. The initial default is 0 (close). 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 CursorBehavior=1, 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. |
Database (DB) |
The name of the database to which you want to connect. |
DataSourceName (DSN) |
A string that identifies a DB2 data source configuration in the
system information file. Examples include "Accounting" or |
Groups (GRP) |
A value that 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) |
The default logon ID used to connect to your DB2 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. For DB2 Common Server on UNIX, normal UNIX security is used. The LogonID value is your UNIX user ID. |
Password (PWD) |
Password. |
Sysibm (SI) |
On most DB2 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. |
The following table shows how the DB2 data types map to the standard ODBC data types.
DB2 |
ODBC |
---|---|
Char |
SQL_CHAR |
Char() for Bit Data |
SQL_BINARY |
Date |
SQL_TYPE_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_TYPE_TIME |
Timestamp |
SQL_TYPE_TIMESTAMP |
Varchar |
SQL_VARCHAR |
Varchar() for Bit Data |
SQL_VARBINARY |
Note: The Graphic, Vargraphic, and Long Vargraphic data types are not supported.
DB2 supports isolation levels 0 (read uncommitted), 1 (read committed), and 2 (repeatable read). It supports record-level locking. See Appendix D Locking and Isolation Levels in the Connect ODBC Reference for a discussion of these topics.
The DB2 driver supports the functions listed in Appendix C, ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following X/Open functions are supported:
The driver supports the minimum SQL grammar.
The DB2 database system supports a single connection and multiple statements per connection.
The dBASE driver supports dBASE IV and V in the UNIX environment.
The dBASE driver runs the SQL statements directly on dBASE-compatible files. You do not need to own dBASE products to access these files.
See the README file shipped with your MERANT DataDirect product for the file name of the dBASE driver.
Index files for dBASE contain index tags for each index that exists for a database file. These index tags can be marked as unique, that is, the driver will ensure that no duplicate values exist for the columns that define the index tag. The unique attribute is not natively supported by the dBASE or FoxPro products. The enforcement and recognition of the unique attribute is an extension of the DataDirect dBASE driver. The driver must be notified that index tags are unique. No configuration is needed for unique indexes that were created using the DataDirect dBASE driver. When using files that were not created with the DataDirect dBASE driver, you must define unique index tags as explained below.
In the directory where the database and index files are located, use any plain text editor, such as vi, to define or edit the QEDBF.INI as follows:
filename
] section where filename
is the name of the database file. This entry is case sensitive and the
file extension should be included.
filename
] section, specify the number
of unique indexes on the file (NUMUNIQUE=
) and the index
specifications (UNIQUE#=
index_filename,index_tag
).
The index_tag
can be determined by calling the
ODBC function SQLStatistics and examining the INDEX_NAME result column.
For example, to define two unique indexes on the accts.dbf table, the QEDBF.INI would be defined as:
[accts.dbf]
NUMUNIQUE=2 UNIQUE0=accts.mdx,ACCT_NAME UNIQUE1=accts.mdx,ACCT_ID
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.
The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Attribute |
Description |
---|---|
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards. |
CacheSize (CSZ) |
The number of 64KB blocks the driver uses to cache database records. The higher the number of blocks, 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 run the Select statement again. The initial default is 4. |
CreateType (CT) |
CreateType={dBASE4 | dBASE5 | FoxPro30}. The type of table or index to be created on a CREATE TABLE or CREATE INDEX statement. The initial default is dBASE5. |
Database (DB) |
The directory in which the dBASE files are stored. |
DataFileExtension (DFE) |
String of three or fewer characters that specifies the file extension to use for data files. The initial default value is DBF. This value cannot be an extension the driver already uses, such as MDX or CDX. This value is used for all Create Table statements. Sending a Create Table using an extension other than the value specified for this attribute causes an error. In other SQL statements, such as Select or Insert, users can specify an extension other than the one specified for this attribute. The DataFileExtension value is used when no extension is specified. |
DataSourceName |
A string that identifies a dBASE data source configuration in the system information file. Examples include "Accounting" or "dBASE Files." |
ExtensionCase (EC)
|
ExtensionCase={LOWER | UPPER}. This attribute specifies whether upper- or lower-case file extensions are accepted. If ExtensionCase=Lower, lower-case extensions are accepted. If ExtensionCase=Upper (the default), upper-case extensions are accepted. |
FileOpenCache (FOC) |
The maximum number of used file handles 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. |
IntlSort (IS) |
IntlSort={0 | 1}. This attribute determines the order that records are retrieved when you issue a Select statement with an Order By clause. If IntlSort=0 (the initial default), the driver uses the ASCII sort order. This order sorts items alphabetically with upper-case letters preceding lower-case letters. For example, "A, b, C" would be sorted as "A, C, b." If IntlSort=1, the driver uses the international sort order as defined by your operating system. This order is always alphabetic, regardless of case; the letters from the previous example would be sorted as "A, b, C." See your operating system documentation concerning the sorting of accented characters. |
LockCompatibility (LCOMP) |
LockCompatibility={Q+E | Q+EVirtual | dBASE | Clipper |
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}. This attribute determines the level of locking for the database tables. 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. |
ModifySQL (MS) |
ModifySQL={0 | 1}. This attribute is provided for backward compatibility with earlier versions of MERANT 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. |
UltraSafeCommit (USF) |
UltraSafeCommit={0 | 1}. This attribute 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}. This attribute specifies whether the driver uses long filenames as table names. The default is 0, do not use long filenames. If UseLongNames=1, the driver uses long filenames. The maximum table name length is specific to the environment in which you are running. |
UseLongQualifiers (ULQ)
|
UseLongQualifiers={0 | 1}. This attribute specifies whether the driver uses long path names as table qualifiers. The default is 0, do not use long path names (the default length of path names is 128 characters). If UseLongQualifiers=1, the driver uses long path names (up to 255 characters). |
The table below shows how dBASE data types map to the standard ODBC data types. These dBASE data types can be used in a Create Table statement. For the syntax of the Create Table statement, see Appendix A SQL for Flat-File Drivers in the Connect ODBC Reference.
dBASE |
ODBC |
---|---|
Binary1 |
SQL_LONGVARBINARY |
Char2 |
SQL_CHAR |
Date |
SQL_TYPE_DATE |
Float |
SQL_DECIMAL |
General1 |
SQL_LONGVARBINARY |
Logical |
SQL_BIT |
Memo |
SQL_LONGVARCHAR |
Numeric |
SQL_DECIMAL |
1 dBASE V only 2 254 characters maximum |
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.
Column names in SQL statements (such as Select, Insert, etc.) can be up to ten characters long. A column name can contain alphanumeric characters and the hyphen character (-). The first character must be a letter (a through z).
You use a SQL SELECT statement to specify the columns and records to be read. dBASE SELECT statements support all of the SELECT statement clauses as described in Appendix A SQL for Flat-File Drivers in the Connect ODBC Reference. This section describes the information that is specific to dBASE, which is Rowid.
Each dBASE record contains a special column named Rowid. This field contains a unique number that indicates the record's sequence in the database. For example, a table that contains 50 records has Rowid values from 1 to 50 (if no records are marked deleted). You can use Rowid in WHERE and SELECT clauses.
Rowid is particularly useful when you are updating records. You can retrieve the Rowid of the records in the database along with the other field values. For example:
SELECT last_name, first_name, salary, rowid FROM emp
Then you can use the Rowid of the record that you want to update to ensure that you are updating the correct record and no other. For example:
UPDATE emp set salary = 40000 FROM emp WHERE rowid=21
The fastest way of updating a single row is to use a WHERE clause with the Rowid. You cannot update the Rowid column.
SELECT statements that use the Rowid pseudo-column in the WHERE clause achieve maximum performance only for exact equality matches. If you use range scans instead of exact equality matches, a full table scan is performed. For example:
SELECT * FROM emp WHERE rowid=21 //fast search
SELECT * FROM emp WHERE rowid <=25 //full table scan
The dBASE driver supports the ALTER TABLE statement to add one or more columns to a table or to delete (drop) a single column.
The ALTER TABLE statement has the form:
ALTER TABLEtable_name
{ADDcolumn_name
data_type
| ADD
(column_name
data_type
[,column_name
data_type
] ... ) | DROP [COLUMN]column_name
}
where:
table_name |
is the name of the table to 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. |
data_type |
specifies the native data type of each column you add. |
For example, to add two columns to the emp table,
ALTER TABLE emp (ADD startdate date, dept char (10))
You cannot add columns and drop columns in a single statement, and you can drop only one column at a time. For example, to drop a column,
ALTER TABLE emp DROP startdate
The ALTER TABLE statement fails if you attempt to drop a column upon which other objects, such as indexes or views, are dependent.
The type of index you create is determined by the value of the CreateType attribute, which you set in the system information file or as a connection string option. The index can be
The syntax for creating an index is
CREATE [UNIQUE] INDEX index_name ON base_table_name
(field_name [ASC | DESC] [,field_name [ASC | DESC]] ...)
where:
index_name |
is the name of 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 index files are named base_table_name.MDX and FoxPro 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 table below shows the attributes of the different index files supported by the dBASE driver. For each type supported, it provides the following details:
Create |
dBASE |
DESC |
Max Size of Key Column |
Max Size of Column Spec. |
Production/Structural Indexes |
Supports FOR Expressions |
---|---|---|---|---|---|---|
dBASE III .NDX |
Yes |
No |
100 |
219 |
No |
No |
dBASE IV, V .MDX |
Yes |
Yes |
100 |
220 |
Yes |
Yes |
Clipper .NTX |
Yes |
Yes |
250 |
255 |
No |
Yes |
FoxPro .IDX* |
Yes |
Yes |
240 |
255 |
No |
Yes |
FoxPro .CDX |
Yes |
Yes |
240 |
255 |
Yes |
Yes |
* 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. |
The syntax for dropping an index is as follows:
DROP INDEX table_name.index_name
where:
table_name |
is the name of the dBASE file without the extension. |
When records are deleted from a dBASE file, they are not removed from the file. Instead, they are marked as having been deleted. Also, when memo fields are updated, space may be wasted in the files. To remove the deleted records and free the unused space from updated memo fields, you must use the PACK statement. It has the following form:
PACK filename
where:
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:
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.
The dBASE driver supports three levels of database locking: NONE, RECORD, and FILE. You can set these levels in the connection string (LCK=).
No locking offers the best performance but is intended only for single-user environments.
With record or file locking, the system locks the database tables during INSERT, UPDATE, DELETE, or SELECT...FOR UPDATE statements. The locks are released when the user commits the transaction. The locks prevent other users from modifying the locked objects, but they do not lock out readers.
With record locking, only records affected by the statement are locked. Record locking provides better concurrency with other users who also want to modify the table.
With file locking, all the records in the table are locked. File locking has lower overhead and may work better if records are modified infrequently, if records are modified primarily by one user, or if a large number of records are modified.
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).
When an UPDATE or DELETE statement is run, the driver locks the records affected by that statement. The locks are released after the driver commits the changes. Under manual commit mode, the locks are held until the application commits the transaction. Under autocommit mode, the locks are held until the statement is run.
When a SELECT...FOR UPDATE statement is run, the driver locks a record only when the record is fetched. If the record is updated, the driver holds the lock until the changes are committed. Otherwise, the lock is released when the next record is fetched.
dBASE supports isolation level 1. It supports both file- and record-level locking. For a discussion of these topics, see Appendix D Locking and Isolation Levels in the Connect ODBC Reference.
The dBASE driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following function is supported: SQLSetPos.
The dBASE driver also supports backward and random fetching in SQLExtendedFetch and SQLFetchScroll. The driver supports the minimum SQL grammar.
dBASE supports multiple connections and multiple statements per connection.
The environment variable INFORMIXDIR must be set to the directory where you have installed the INFORMIX client, using the following command:
C shell:
setenv INFORMIXDIR /databases/informix
Bourne shell and Korn shell:
INFORMIXDIR=/databases/informix;export INFORMIXDIR
In addition, the INFORMIXSERVER variable must be set to the name of the INFORMIX server (as defined in your $INFORMIXDIR/ext/sqlhosts file). For further details, refer to the Informix Online Dynamic Server Administrator's Guide, Volume 2 or the Informix UNIX Installation Guide.
INFORMIX:
The INFORMIX driver under UNIX requires INFORMIX-Connect or ESQL-C 7.23.
INFORMIX 9:
To access remote INFORMIX 7.x or 9 databases through the INFORMIX 9 driver, you need INFORMIX-Connect or ESQL-C 9.1.3 for Solaris and HP-UX, and INFORMIX-Connect 9.1.4 for AIX.
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 INFORMIX is:
DSN=INFORMIX TABLES;DB=PAYROLL
The following table gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.
The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Attribute |
Description |
---|---|
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards. |
CancelDetect |
Lets you cancel long-running queries in threaded applications. Select a value to determine how often the driver checks whether a request has been canceled using SQLCancel. For example, if CDI=5, then for every pending request, the driver checks every five seconds to see whether the user has canceled execution of the query using SQLCancel. The default is 0, which means that requests will not be canceled until a request has completed execution. |
CursorBehavior (CB) |
CursorBehavior={0 | 1}. This attribute determines whether cursors will be preserved or closed at the end of each transaction. The initial default is 0 (close). 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. |
Database (DB) |
The name of the database to which you want to connect. |
DataSourceName (DSN) |
A string that identifies an INFORMIX data source configuration in the system information file. Examples include "Accounting" or "INFORMIX-Serv1." |
EnableInsert |
EnableInsertCursors={0 | 1}. Determines whether the driver can use Insert cursors during parametrized inserts. The initial default value is 1 (driver uses Insert cursors). Using Insert cursors improves performance during multiple Insert operations using the same statement. This option enables insert data to be buffered in memory before being written to disk. When EnableInsertCursors=0, the driver does not use Insert cursors. |
EnableScrollable |
EnableScrollableCursors={0 | 1}. This attribute determines whether the driver provides scrollable cursors. The initial default value is 0 (no use of scrollable cursors). The INFORMIX driver can use scrollable 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. |
GetDBListFrom |
GetDBListFromInformix={0 | 1}. This attribute 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 initial default, the driver requests the database list from the INFORMIX server. When set to 0, it uses the list that was entered by the user at driver setup. |
HostName (HOST) |
The name of the machine on which the INFORMIX server resides. |
LogonID (UID) |
Your user name as specified on the INFORMIX server. |
Password (PWD) |
A password. |
ServerName |
The name of the server running the INFORMIX database. |
Service (SERV) |
The name of the service as it appears on the host machine. This service is assigned by the system administrator. |
UseDefaultLogin (UDL) |
UseDefaultLogin={0 | 1}. Specify 1 to read the Logon ID and Password directly from the INFORMIX registry. The default is 0; that is, logon information is read from the system information file, the connection string, or the Logon to INFORMIX dialog box. |
The table below shows how the INFORMIX data types map to the standard ODBC data types.
INFORMIX 5 and 7 |
ODBC |
---|---|
Byte1 |
SQL_LONGVARBINARY |
Char |
SQL_CHAR |
Date |
SQL_TYPE_DATE |
Datetime year to fraction(5) |
SQL_TYPE_TIMESTAMP |
Datetime year to fraction(f)2 |
SQL_TYPE_TIMESTAMP |
Datetime year to second |
SQL_TYPE_TIMESTAMP |
Datetime year to day |
SQL_TYPE_DATE |
Datetime hour to second |
SQL_TYPE_TIME |
Datetime hour to fraction(f)2 |
SQL_TYPE_TIME |
Decimal |
SQL_DECIMAL |
Float |
SQL_DOUBLE |
Integer |
SQL_INTEGER |
Interval year(p) to year |
SQL_INTERVAL_YEAR |
Interval year(p) to month |
SQL_INTERVAL_YEAR_TO_MONTH |
Interval month(p) to month |
SQL_INTERVAL_MONTH |
Interval day(p) to day |
SQL_INTERVAL_DAY |
Interval day(p) to hour |
SQL_INTERVAL_DAY_TO_HOUR |
Interval day(p) to minute |
SQL_INTERVAL_DAY_TO_MINUTE |
Interval day(p) to second |
SQL_INTERVAL_DAY_TO_SECOND |
Interval day(p) to fraction(f)2 |
SQL_INTERVAL_DAY_TO_SECOND |
Interval hour(p) to hour |
SQL_INTERVAL_HOUR |
Interval hour(p) to minute |
SQL_INTERVAL_HOUR_TO_MINUTE |
Interval hour(p) to second |
SQL_INTERVAL_HOUR_TO_SECOND |
Interval hour(p) to fraction(f)2 |
SQL_INTERVAL_HOUR_TO_SECOND |
Interval minute(p) to minute |
SQL_INTERVAL_MINUTE |
Interval minute(p) to second |
SQL_INTERVAL_MINUTE_TO_SECOND |
Interval minute(p) to fraction(f)1 |
SQL_INTERVAL_MINUTE_TO_SECOND |
Interval second(p) to second |
SQL_INTERVAL_SECOND |
Interval second(p) to fraction(f)1 |
SQL_INTERVAL_SECOND |
Interval fraction to fraction(f)2 |
SQL_VARCHAR |
Money |
SQL_DECIMAL |
Serial |
SQL_INTEGER |
Smallfloat |
SQL_REAL |
Smallint |
SQL_SMALLINT |
Text1 |
SQL_LONGVARCHAR |
Varchar1 |
SQL_VARCHAR |
1 Not supported for Standard Engine Databases 2 Fraction(f) types are mapped to fraction(5) in the driver. The precision is type dependent and the scale is 5. |
The table below shows how the INFORMIX 9 data types map to the standard ODBC data types. These types are in addition to the INFORMIX data types described in the previous table.
INFORMIX 9 |
ODBC |
---|---|
Blob |
SQL_LONGVARBINARY |
Boolean |
SQL_BIT |
Clob |
SQL_LONGVARCHAR |
Int8 |
SQL_BIGINT |
Lvarchar |
SQL_VARCHAR |
Serial8 |
SQL_BIGINT |
The INFORMIX 9 driver does not support any complex data types (for example, set, multiset, list, and named/unnamed abstract types). When the driver encounters a complex type it will return an Unknown Data Type error (SQL State HY000).
If connected to an online server, INFORMIX supports isolation levels 0 (read uncommitted), 1 (read committed), and 3 (serializable). The default is 1. The Standard Engine supports isolation level 0 (read uncommitted) only.
INFORMIX also supports an alternative isolation level 1, called cursor stability. Your ODBC application can use this isolation level by calling SQLSetConnectAttr (1040,1).
Additionally, if transaction logging has not been enabled for your database, then transactions are not supported by the driver (the driver is always in auto-commit mode).
INFORMIX supports page-level and row-level locking.
See Appendix D Locking and Isolation Levels in the Connect ODBC Reference for a discussion of these topics.
The INFORMIX driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following X/Open functions are supported:
The driver also supports scrollable cursors with SQLExtendedFetch or SQLFetchScroll if the connection attribute EnableScrollableCursors is set to 1. The driver supports the core SQL grammar.
The INFORMIX driver supports multiple connections and multiple statements per connection to the INFORMIX database system.
The following section lists requirements for both OpenIngres and OpenIngres 2.
OpenIngres:
To access OpenIngres databases from your client workstation you must have the OpenIngres/Net Release 1.2 product (int.wnt/03 or higher) installed on your client node.
OpenIngres 2:
To access OpenIngres 2 databases from your client workstation you must have CA OpenIngres Net version 2.0 or greater installed on your client node.
OpenIngres and OpenIngres 2:
The remote or host OpenIngres databases must be INGRES 1.2 or later.
You must have the environment variable II_SYSTEM set to the directory above the directory where you installed the INGRES client.
For example, if you have installed your INGRES product in /databases/ingres, use:
C shell:
setenv II_SYSTEM /databases
Bourne shell; Korn-shell:
II_SYSTEM=/databases;export II_SYSTEM
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 OpenIngres is:
DSN=INGRES TABLES;SRVR=QESERV;DB=PAYROLL;UID=JOHN
The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.
The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Attribute |
Description |
---|---|
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards. |
Database (DB) |
The name of the database to which you want to connect. |
DataSourceName (DSN) |
A string that identifies an OpenIngres data source configuration in the system information file. Examples include "Accounting" or "INGRES-Serv1." |
DefaultLongData |
An integer value that specifies, in 1024-byte multiples, the maximum amount of data that will be transferred to the client for unbound long data result columns. The default is 1024 (DefaultLongDataBuffLen=1024); that is, 1024 * 1024 = 1 MB. |
EnableSelectLoop |
EnableSelectLoop={0 | 1}. This attribute enables the retrieval of multiple rows using the select loop model instead of cursors. The default is 0; that is, use cursors. Specify 1 to use select loops. |
LockMode (LM)
|
LockMode={0 | 1 | 2}. Allows you to select row, page, or table locking. Options are Row (0), Page (1), or Table (2). The default is page locking (1). |
LogonID (UID) |
The default logon ID (user name) used to connect to your OpenIngres 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 OpenIngres SQL command line. Some examples are
|
RepeatedCache |
An integer value that determines whether all Update and Insert statements are to be run as repeated statements. This attribute improves the performance of applications that repeat the same set of SQL statements. 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 OpenIngres Repeated syntax. |
RepeatedSelects (RS) |
RepeatedSelects={0 | 1 | 2}. This attribute determines whether the driver optimizes Select statements or runs them as repeated queries. When set to 0, the initial default, the driver runs 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 runs 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:
|
ServerName (SRVR) |
The name of the virtual node that you defined using the OpenIngres NETU utility. This virtual node tells OpenIngres which system to call, how to call it, and the user's name and password. |
SQLGrammar (SG) |
SQLGrammar={0 | 1}. Provides the ability to access data sources using OpenSQL. The default is 0; that is, INGRES SQL. |
ValueReplacement |
ValueReplacement={0 | 1}. This attribute determines whether the driver substitutes parameters for hard-coded 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 parameter markers for hard-coded values and the RepeatedCacheSize attribute must be greater than zero or the Repeated OpenIngres 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 grammar. 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. |
The table below shows how OpenIngres data types map to the standard ODBC data types.
OpenIngres |
ODBC |
---|---|
Byte* |
SQL_BINARY |
Byte varying* |
SQL_VARBINARY |
Char |
SQL_CHAR |
Date |
SQL_TYPE_TIMESTAMP |
Float |
SQL_DOUBLE |
Float4 |
SQL_REAL |
Integer |
SQL_INTEGER |
Integer1 |
SQL_TINYINT |
Long byte* |
SQL_LONGVARBINARY |
Long varchar* |
SQL_LONGVARCHAR |
Money* |
SQL_DECIMAL |
Smallint |
SQL_SMALLINT |
Varchar |
SQL_VARCHAR |
* Not supported by OpenSQL. |
Note: OpenIngres Date values do not directly map to the ODBC type SQL_TYPE_TIMESTAMP. If interval data is placed in Date columns, then the driver raises an error when attempting to read the value.
OpenIngres supports isolation level 1 (read committed, the default). OpenIngres supports page-level locking. For a discussion of these topics, see Appendix D Locking and Isolation Levels in the Connect ODBC Reference.
The OpenIngres driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. SQLProcedures and SQLProcedureColumns are supported unless SQLGrammar=1.
The driver supports the minimum SQL grammar.
The OpenIngres database system supports multiple connections and multiple statements per connection.
Note that if you set the RepeatedSelects connection string attribute to 1 or 2, the driver is limited to one active statement and one active connection.
Both Oracle and Oracle8 client information for UNIX is listed below.
Oracle and Oracle8:
Before you can use the Oracle data source, you must have the Oracle SQL*Net or Net8 drivers you plan to use installed on your workstation in the $ORACLE_HOME source tree. ORACLE_HOME is an environment variable created by the Oracle installation process that identifies the location of your Oracle client components.
Oracle refers to the run-time Oracle component as "Oracle RDBMS." From the Oracle RDBMS product, the Oracle driver depends on the executables in $ORACLE_HOME/bin and the interface libraries in $ORACLE_HOME/rdbms/lib.
Set the environment variable ORACLE_HOME to the directory where you installed the Oracle RDBMS, SQL*Net, or Net8 product. For example:
C shell:
setenv ORACLE_HOME /databases/oracle
Bourne shell and Korn shell:
ORACLE_HOME=/databases/oracle;export ORACLE_HOME
Building the required Oracle7 SQL*Net Shared Library:
The Oracle driver requires a one-time site linking to build an Oracle7 SQL*Net driver on AIX and, for Oracle7.1 only, on Solaris and HP-UX. This site linking binds your unique Oracle7 SQL*Net configuration into the file, which is used by the Oracle driver to access local and remote Oracle databases.
Before you build the Oracle7 SQL*Net shared library, install Oracle and set the environment variable ORACLE_HOME to the directory where you installed Oracle. Connect ODBC provides a script, genclntsh, that builds the Oracle7 SQL*Net driver. This script is in the scr/oracle directory.
The following command builds the Oracle7 SQL*Net shared library:
genclntsh
Building the Required Oracle Net8 Shared Library on Solaris:
Under Oracle8.0.3 or 8.0.4 for Solaris, the Oracle8 driver requires a one-time site linking to build a replacement Oracle Net8 driver. This site linking binds your unique Oracle Net8 configuration into the file, which is used by the Oracle driver to access local and remote Oracle databases.
The Oracle8 driver requires the shared library libclntsh.so,
which is built by the Oracle script genclntsh. The genclntsh script
provided by Oracle causes an error resulting from the undefined symbol
slpmprodstab. Oracle8 users must therefore use the genclntsh8 script
provided with Connect ODBC to build a replacement libclntsh.so.
This script, in the scr/oracle directory, places the new libclntsh.so
in ../../lib, which is your
$ODBC_HOME/lib directory; it does not overwrite the original
libclntsh.so in the $ORACLE_HOME/lib directory.
Before you build the Oracle Net8 shared library, install Oracle and set the environment variable ORACLE_HOME to the directory where you installed Oracle.
The following command builds the Oracle Net8 shared library:
genclntsh8
Warning: Oracle8 users will have the original libclntsh.so library in the $ORACLE_HOME/lib directory. Therefore, the $ODBC_HOME/lib directory, containing the correct library, must be on the LD_LIBRARY_PATH before $ORACLE_HOME/lib. Otherwise, the original Oracle library will be loaded, resulting in the unresolved symbol error.
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 Oracle is:
DSN=Accounting;SRVR=X:QESRVR;UID=JOHN;PWD=XYZZY
If the server name contains a semicolon, enclose it in quotation marks:
DSN=Accounting;SRVR="X:QE;SRVR";UID=JOHN;PWD=XYZZY
The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.
The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Oracle has a feature that allows you to connect to Oracle via the operating system user name and password. To connect, use a slash ( / ) for the user name and leave the password blank. To configure the Oracle server/client, refer to the Oracle server documentation.
Attribute |
Description |
---|---|
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread-safety standards. When you specify ApplicationUsingThreads=1,
SQLGetInfo(SQL_ASYNC_MODE) |
ArraySize (AS) |
The number of bytes the driver uses for fetching multiple rows. Values can be an integer from 0 to 65,536. The initial default is 60,000. 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. |
CatalogOptions (CO) |
CatalogOptions={0 | 1}. Specifies whether the result column REMARKS for the catalog functions SQLTables and SQLColumns and COLUMN_DEF for the catalog function SQLColumns have meaning for Oracle. If you want to obtain the actual default value, set CO=1. The default is 0. |
DataSourceName (DSN) |
A string that identifies an Oracle data source configuration in the system information file. Examples include "Accounting" or "Oracle-Serv1." |
DefaultIsolation |
DefaultIsolationLevel=string values. Specifies the default isolation level for concurrent transactions. The values are SQL_TXN_READ_COMMITTED and SQL_TXN_READ_SERIALIZABLE. The default is SQL_TXN_READ_COMMITTED. |
EnableDescribe |
EnableDescribeParam={0 | 1}. Enables the ODBC API function SQLDescribeParam, which results in all parameters being described with a data type of SQL_VARCHAR. This option should be set to 1 when using Microsoft Remote Data Objects (RDO) to access data. The default is 0. |
EnableScrollable |
EnableScrollableCursors={0 | 1}. Enables scrollable cursors for the data source. Both Keyset and Static cursors are enabled. This option may need to be set to 1 when using Microsoft Foundation Classes for database access. The default is 0. |
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 statement. Values can be -1 (wait forever, the initial default) or 0 (do not wait). |
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. To use your operating system user name, see the section Connecting via Operating System Parameters. |
PacketSize (PS) |
PacketSize={1024 | 2048 | 4096 | 8192}. A value that controls the packet size for TCP/IP connections. Any values other than 1024, 2048, 4096, or 8192 are ignored. This value is used only when the ServerName attribute (described above) is set to T for TCP/IP. |
Password (PWD) |
The password that the application uses to connect to your Oracle database. To use your operating system password, see the section Connecting via Operating System Parameters. |
ProcedureRet |
ProcedureRetResults={0 | 1}. Values are Off (0) and On (1). The default is 0. When the option is on, the driver will return result sets from stored procedures/functions. If this option is on and you execute a stored procedure that does not return result sets, you will incur a small performance penalty. See the section Stored Procedure Results. |
ServerName (SRVR) |
The client connection string designating the server and database to be accessed. The information required varies depending on the client driver that you are using. For Oracle7Oracle8 remote servers, the SQL*Net connection string has the following form: driver_prefix:computer_name[:sid] driver_prefix identifies 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), AT (AppleTalk), or TNS (SQL*Net 2.0). 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. For local servers, the SQL*Net connection string has the form: database_name database_name identifies your Oracle database. If the SQL*Net connection string contains semicolons, enclose it in quotation marks. See your SQL*Net documentation for more information. Oracle8: For Oracle8 remote servers, the Net8 Client connection string has the following form: TNSNAME TNSNAME is the alias name of the Oracle Listener on your network. If the Net8 Client connection string contains semicolons, enclose it in quotation marks. See your Net8 Client documentation for more information. |
The table below shows how the Oracle data types are mapped to the standard ODBC data types.
Oracle |
ODBC |
---|---|
Char |
SQL_CHAR |
Date |
SQL_TYPE_TIMESTAMP |
Long |
SQL_LONGVARCHAR |
Long Raw |
SQL_LONGVARBINARY |
Number |
SQL_DOUBLE |
Number(p,s) |
SQL_DECIMAL |
Raw |
SQL_VARBINARY |
Varchar2 |
SQL_VARCHAR |
Oracle8:
The table below shows how the Oracle8 data types are mapped to the standard ODBC data types. These are in addition to the Oracle data types described above.
Oracle8 |
ODBC |
---|---|
Bfile |
SQL_LONGVARBINARY* |
Blob |
SQL_LONGVARBINARY |
Clob |
SQL_LONGVARCHAR |
* Read-Only |
The Oracle8 driver does not support any Abstract Data Types. When the driver encounters an Abstract Data Type during data retrieval, it will return an Unknown Data Type error (SQL State HY000). It also does not support asynchronous operations, due to constraints in the current Oracle8 client.
When the option Procedure Returns Results is active, the driver returns result sets from stored procedures/functions. In addition, SQLGetInfo(SQL_MULT_RESULTS_SETS) will return "Y" and SQLGetInfo(SQL_BATCH_SUPPORT) will return SQL_BS_SELECT_PROC. If this option is on and you execute a stored procedure that does not return result sets, you will incur a small performance penalty.
This feature requires that stored procedures be in a certain format. First, a package must be created to define all of the cursors used in the procedure, then the procedure can be created using the new cursor. For example:
Create or replace package GEN_PACKAGE as CURSOR G1 is select CHARCOL from GTABLE2; type GTABLE2CHARCOL is ref cursor return G1%rowtype; end GEN_PACKAGE;
Create or replace procedure GEN_PROCEDURE1 (rset IN OUT GEN_PACKAGE.GTABLE2CHARCOL, icol INTEGER) as begin open rset for select CHARCOL from GTABLE2 where INTEGERCOL <= icol order by INTEGERCOL; end;
For more information consult your Oracle SQL manual.
Oracle supports isolation level 1 (read committed) and isolation level 3 (serializable isolation-if the server version is Oracle7.3 or greater or Oracle8.x). Oracle supports record-level locking.
For a discussion of these topics, see Appendix D Locking and Isolation Levels in the Connect ODBC Reference.
The Oracle driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. The Oracle driver also supports SQLDescribeParam if EnableDescribeParam=1. If EnableScrollableCursors=1, it supports SQLSetPos and also scrollable cursors with SQLFetchScroll and SQLExtendedFetch.
The Oracle driver supports the following X/Open level functions:
The driver supports the core SQL grammar.
The Oracle driver supports multiple connections and multiple statements per connection.
Before you can use the System data source, you must have the Sybase Open Client Net-Libraries you plan to use installed on your workstation in the $SYBASE source tree.
Set the environment variable SYBASE to the directory where you installed the System client, using:
C-shell:
setenv SYBASE /databases/sybase
Bourne shell and Korn shell:
SYBASE=/databases/sybase;export SYBASE
You must include the directory containing the System client-shared libraries in the environment variable LD_LIBRARY_PATH (on Solaris), LIBPATH (on AIX), and SHLIB_PATH (on HP-UX):
C-shell:
setenv LD_LIBRARY_PATH /databases/sybase/ lib:$LD_LIBRARY_PATH
Bourne shell and Korn shell:
LD_LIBRARY_PATH=/databases/sybase/ lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
In non-DCE environments, users should use the ivsybxx Sybase driver that requires the library libct. For DCE environments, users should use the ivsyb11xx Sybase driver that requires the Sybase 11.1 client library libct_r.
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 Sybase is:
DSN=SYS10 TABLES;SRVR=QESRVR;DB=PAYROLL;UID=JOHN;PWD=XYZZY
The following table gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.
The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Attribute |
Description |
---|---|
ApplicationName (APP) |
The name used by Sybase to identify your application. |
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards. |
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 50 rows. |
Charset (CS) |
The name of a character set corresponding to a subdirectory in $SYBASE/charsets. |
CursorCacheSize (CCS) |
The number of connections that the connection cache can hold. The initial default value for CursorCacheSize is 1 (one cursor). To set the connection cache, you must set the SelectMethod attribute to 1. Increasing the connection cache may increase performance of some applications but requires additional database resources. |
Database (DB) |
The name of the database to which you want to connect. |
DataSourceName (DSN) |
A string that identifies a single connection to a Sybase database. Examples include "Accounting" or "Sys10-Serv1." |
DefaultLongData |
An integer value that specifies, in 1024-byte multiples, the maximum length of data fetched from a TEXT or IMAGE column. The default is DefaultLongDataBuffLen=1024. You will need to increase this value if the total size of any long data exceeds 1 megabyte. |
DirectoryService |
A string that indicates which Directory Service Provider the Sybase Open Client uses when connecting with this data source. The available Directory Service Providers can be found using the OpenClient/OpenServer Configuration Utility that is installed with Sybase Open Client version 11.1 or higher. If the client is not using Open Client version 11.1 or higher, this option is ignored. |
EnableQuoted |
EnableQuotedIdentifiers={0 | 1}. Specify 1 to allow support of quoted identifiers. The default is 0. |
InitializationString (IS) |
InitializationString={<Sybase set commands>;...}. Supports the execution of Sybase commands at connect time. Multiple commands must be separated by semicolons. |
InterfacesFile (IFILE) |
The path name to the interfaces file. |
Language (LANG) |
The national language corresponding to a subdirectory in $SYBASE/locales. |
LogonID (UID) |
The default logon ID used to connect to your Sybase 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. |
OptimizePrepare (OP) |
OptimizePrepare={0 | 1 | 2}. This attribute 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 processing static statements. When set to 1, the initial default, the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and run directly at SQLExecute time. When set to 2, the driver never creates stored procedures. |
PacketSize (PS) |
PacketSize={-1 | 0 | When set to 0, the initial default, the driver uses the default packet size as specified in the Sybase server configuration. When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in the system information file. When set to For you to take advantage of this connection attribute, you must configure the Sybase 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 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. |
Password (PWD) |
A case-sensitive password. |
PasswordEncryption (PE) |
PasswordEncryption={0 | 1}. A value that determines whether password encryption can be performed from the Open Client Library to the server. When set to 0, the initial default, this cannot be done. When set to 1, password encryption is enabled. |
RaiseErrorPosition |
RaiseErrorPositionBehavior={0 | 1}. A value that specifies when the error is returned and where the cursor is positioned when raiserror is encountered. When set to 0 (the default), raiserror is handled separately from surrounding statements. The error is returned when raiserror is processed via SQLExecute, SQLExecDirect, or SQLMoreResults. The result set is empty. When set to 1 (MS compatible), raiserror is handled with the next statement. The error is returned when the next statement is processed; the cursor is positioned on the first row of subsequent result set. This could result in multiple raiserrors being returned on a single execute. |
SecurityService |
A string that indicates which Security Service Provider the Sybase Open Client uses when connecting with this data source. The available Security Service Providers can be found using the OpenClient/OpenServer Configuration Utility that is installed with Sybase Open Client version 11.1 or higher. If the client is not using Open Client version 11.1 or higher, this option is ignored. |
SelectMethod (SM) |
SelectMethod={0 | 1}. This attribute 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 occur 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 run directly without using database cursors. When set to 1, the data source is limited to one active statement. |
ServerName (SRVR) |
The name of the server containing the Sybase tables you want to access. If not supplied, the initial default is the server name in the DSQUERY environment variable. On UNIX, the name of a server from your $SYBASE/interfaces file. |
WorkstationID (WKID) |
The workstation ID used by the client. |
The following table shows how the Sybase data types are mapped to the standard ODBC data types.
Sybase |
ODBC |
---|---|
binary |
SQL_BINARY |
bit |
SQL_BIT |
char |
SQL_CHAR |
datetime |
SQL_TYPE_TIMESTAMP |
decimal |
SQL_DECIMAL |
float |
SQL_FLOAT |
image |
SQL_LONGVARBINARY |
int |
SQL_INTEGER |
money |
SQL_DECIMAL |
numeric |
SQL_NUMERIC |
real |
SQL_REAL |
smalldatetime |
SQL_TYPE_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 |
Sybase supports isolation levels 0 (if the server version is 11 or higher), 1 (read committed, the default), and 3 (serializable). It supports page-level locking. See Appendix D Locking and Isolation Levels in the Connect ODBC Reference for a discussion of these topics.
The Sybase driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following functions are supported:
The driver supports the minimum SQL grammar.
The Sybase database system supports multiple connections and multiple statements per connection. If SelectMethod=1, Sybase data sources are limited to one active statement in manual commit mode.
The Text driver supports ASCII text files. These files can be printed directly or edited with text editors or word processors, because none of the data is stored in a binary format.
See the README file shipped with your MERANT DataDirect product for the file name of the text driver.
The Text driver executes SQL statements directly on the text files. The driver supports Insert statements, and inserts the record at the end of the file. You can execute Update and Delete statements conditionally.
Some common formats for text files are listed in the table below.
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. |
Stream |
No character separates column values nor records. The table is one long stream of bytes. |
Comma-, tab-, and character-separated files are called character-delimited files because values are separated by a special character.
Because text files do not all have the same structure, the driver provides the option to define the structure of an existing file. Although defining the structure is not mandatory, because the driver can attempt to guess the names and types of the columns, this feature is extremely useful.
To define the structure of a text file, you create a QETXT.INI file using any plain text editor, such as vi. The filename must be in upper-case. All of the tables you wish to define are specified in the QETXT.INI file. When you specify table attributes in QETXT.INI, you override the attributes specified in the system information file or in the connection string.
Define the QETXT.INI file as follows:
emptxt.txt=EMP
Table names can be up to 32 characters in length and cannot be the same as another defined table in the database. This name is returned by SQLTables. By default, it is the filename without its extension.
Specify the text filename. For example:
FILE=emptxt.txt
To define the table type, specify how the fields are separated (comma, tab, fixed, or character). For example:
TT=COMMA
If the table type is CHARACTER, specify the delimiter character. For example, if the fields are separated by semicolons,
DC=;
Specify whether the first line of the file contains column names, using 1 for yes and 0 for no. For example:
FLN=0
Separate the values with commas. For example, to define 2 fields,
FIELD1=EMP_ID,VARCHAR,6,0,6,0,
FIELD2=HIRE_DATE,DATE,10,0,10,0,m/d/yy
The following is an example of a QETXT.INI file. This file defines the structure of the emptext.txt file, which is a sample data file shipped with the DataDirect ODBC Text file.
[Defined Tables]
emptext.txt=EMP [EMP] FILE=emptext.txt FLN=1 TT=Comma Charset=ANSI FIELD1=FIRST_NAME,VARCHAR,10,0,10,0, FIELD2=LAST_NAME,VARCHAR,9,0,9,0, FIELD3=EMP_ID,VARCHAR,6,0,6,0, FIELD4=HIRE_DATE,DATE,10,0,10,0,m/d/yy FIELD5=SALARY,NUMERIC,8,2,8,0, FIELD6=DEPT,VARCHAR,4,0,4,0, FIELD7=EXEMPT,VARCHAR,6,0,6,0, FIELD8=INTERESTS,VARCHAR,136,0,136,0,
Date masks tell the driver how a date is stored in a text file. When a value is inserted into a text file, the date is formatted so that it matches the mask. When reading a text file, the driver converts the formatted date into a date data type.
The table below lists the symbols to use when specifying the date mask.
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 Ms (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 Ds (that is, mon, Mon, MON). |
dddd, Dddd, DDDD |
Output the day depending on the case of the Ds (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) |
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 table below shows some example date values, masks, and how the date appears in the text file.
Date |
Mask |
Value |
---|---|---|
1993-10-01 |
yyyy-mm-dd |
1993-10-01 |
|
m/d/yy |
10/1/93 |
|
Ddd, Mmm dd, yyyy |
Fri, Oct 01, 1993 |
To configure a data source, you must use the data source entries listed in the table below. For details on configuring the system information file, see the section The UNIX Environment.
If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information file to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information file. These values are not written to the system information 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 text files is:
DSN=TEXT FILES;TT=CHARACTER;DC=&
The table below gives the long and short names for each attribute, as well as a description. The system information file accepts only long names for attributes.
The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information file. If you specified a value for the attribute when configuring the data source, that value is your default.
Attribute |
Description |
---|---|
AllowUpdateAnd |
AllowUpdateAndDelete={0 | 1}. Specifies whether a data source allows Update and Delete statements. The default is 0. Because Update and Delete statements cause immediate changes to a table, only one connection at a time can operate on a table. When this option is set, tables are opened exclusively by the current connection. Each update and delete on a text file can cause significant changes to the file, and performance may be poor. Consider a more appropriate database form if performance is a significant factor. |
ApplicationUsing |
ApplicationUsingThreads={0 | 1}. A setting that ensures that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread safety standards. |
CacheSize (CSZ) |
The number of 64K blocks the driver uses to cache database records. The higher the number of blocks, 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 run the Select statement again. The initial default is 4. |
Database (DB) |
The directory in which the text files are stored. |
DataFileExtension (DFE) |
Specifies the file extension to use for data files. The default Data File Extension setting is TXT. 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. |
DataSourceName (DSN) |
A string that identifies a Text data source configuration in the system information file. Examples include "Accounting" or "Text Files." |
DecimalSymbol (DS) |
DecimalSymbol={. | ,}. Specifies the decimal separator used when data is stored. The international decimal symbol (.) must be used in DML statements and parameter buffers. |
Delimiter (DC) |
The character used as a delimiter for character-separated files. It can be any printable character except single or double quotes. The initial default is a comma (,). |
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. |
FirstLineNames (FLN) |
FirstLineNames={0 | 1}. This attribute 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}. This attribute determines the order that records are retrieved when you issue a Select statement with an Order By clause. If IntlSort=0 (the initial default), the driver uses the ASCII sort order. This order sorts items alphabetically with upper-case letters preceding lower-case letters. For example, "A, b, C" would be sorted as "A, C, b." If IntlSort=1, the driver uses the international sort order as defined by your operating system. This order is always alphabetic, regardless of case; the letters from the previous example would be sorted as "A, b, C." See your operating system documentation concerning the sorting of accented characters. |
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 | Stream}. The Text driver supports four types: comma-separated, tab-separated, character-separated, fixed length, and stream. Setting this value tells the driver the default type, which is used when creating a new table and opening an undefined table. |
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. |
Note: The ScanRows, TableType, Delimiter, FirstLineNames, and Charset attributes apply to tables that have not been defined. These attributes also determine the characteristics of new tables created with the Create Table statement.
The table below shows how the text file data types are mapped to the standard ODBC data types.
Text |
ODBC |
---|---|
Numeric |
SQL_NUMERIC |
Date |
SQL_TYPE_DATE |
Varchar |
SQL_VARCHAR |
You use the SQL SELECT statement to specify the columns and records to be read. The driver supports all SELECT statement clauses as described in Appendix A SQL for Flat-File Drivers in the Connect ODBC Reference.
The Text driver supports the ALTER TABLE statement to add one or more columns to a table or to delete (drop) a single column.
The ALTER TABLE statement has the form:
ALTER TABLE table_name
{ADDcolumn_name
data_type
| ADD (column_name
data_type
[,column_name
data_type
] ...) | DROP [COLUMN]column_name
}
where:
table_name |
is the name of the table to 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. |
data_type |
specifies the native data type of each column you add. |
For example, to add two columns to the emp table,
ALTER TABLE emp (ADD startdate date, dept varchar(10))
You cannot add columns and drop columns in a single statement, and you can drop only one column at a time. For example, to drop a column,
ALTER TABLE emp DROP startdate
The ALTER TABLE statement fails when you attempt to drop a column upon which other objects, such as indexes or views, are dependent.
The Text driver supports the functions listed in Appendix C ODBC API and Scalar Functions in the Connect ODBC Reference. In addition, the following function is supported: SQLSetPos.
The Text driver also supports backward and random fetching in SQLExtendedFetch and SQLFetchScroll. The driver supports the minimum SQL grammar.
Text files support multiple connections and multiple statements per connection.
Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
COBSQL Error Messages | Embedded SQL Statements |