═══ 1. About INTERSOLV DataDirect Database Drivers ═══ The INTERSOLV DataDirect ODBC drivers are compliant with the Microsoft Open Database Connectivity (ODBC) specification. ODBC is a specification for an application program interface (API) that enables applications to access multiple database management systems using Structured Query Language (SQL). ODBC permits maximum interoperability--a single application can access many different database management systems. This enables an ODBC developer to develop, compile, and ship an application without targeting a specific type of data source. Users can then add the database drivers, which link the application to the database management systems of their choice. ═══ 1.1. Environment-Specific Information ═══ On OS/2 systems, the ODBC drivers are 32-bit drivers. The required network software supplied by your database system vendors must be 32-bit compliant. Consult the System Requirements topic for specific requirements for each relational database driver. OS/2 version 2.x is required to use the ODBC drivers. ODBC.INI OBDC.INI is an operating system binary INI file located in the OS/2 system directory. The structure of this file is described in the OBDC.INI topic. Since this file is binary, you cannot edit it with a text editor. Starting the ODBC Administrator The Configuring Data Sources topic in each driver section instructs you to start the ODBC Administrator. To start the ODBC Administrator under OS/2, double-click the ODBC Administrator icon. If you are using ODBC Pack, this icon is in the ODBC Pack folder. If you are using the INTERSOLV DataDirect Developers Toolkit, this icon is in the folder for that product. Driver Names The file extension for the ODBC drivers is .DLL. This indicates that they are dynamic link libraries. You must modify the CONFIG.SYS file to add the fully qualified path to the environment variable LIBPATH. For example, if you install the ODBC drivers in the directory C:\ODBC, then the fully qualified path is C:\ODBC. This enables your applications to dynamically load the ODBC drivers at runtime. ═══ 1.2. Supported ODBC Functions ═══ This topic lists the ODBC API functions that the database drivers support. All database drivers are ODBC Level 1-compliant--they support all ODBC Core and Level 1 functions. A limited set of Level 2 functions is also supported. The drivers support the functions listed in the following table. Any additions to these supported functions or differences in the support of specific functions are listed in the "ODBC Conformance Level" topic for each individual driver. Core Functions SQLAllocConnect SQLAllocEnv SQLAllocStmt SQLBindCol SQLBindParameter* SQLCancel SQLColAttributes SQLConnect SQLDescribeCol SQLDisconnect SQLDrivers* SQLError SQLExecDirect SQLExecute SQLFetch SQLFreeConnect SQLFreeEnv SQLFreeStmt SQLGetCursorName SQLNumResultCols SQLPrepare SQLRowCount SQLSetCursorName SQLTransact Level 1 Functions SQLColumns SQLDriverConnect SQLGetConnectOption SQLGetData SQLGetFunctions SQLGetInfo SQLGetStmtOption SQLGetTypeInfo SQLParamData SQLPutData SQLSetConnectOption SQLSetStmtOption SQLSpecialColumns SQLStatistics SQLTables Level 2 Functions SQLDataSources SQLExtendedFetch (forward scrolling only) SQLMoreResults SQLNativeSql SQLNumParams SQLParamOptions* SQLSetScrollOptions ═══ 1.3. ODBC.INI ═══ ODBC.INI Examples On OS/2 systems, ODBC.INI resides in the same directory that is pointed to by the USER_INI environment variable in your CONFIG.SYS file. ODBC.INI a binary file that you can maintain by the ODBC Administrator or an INI editor. An INI file contains a [section_name] heading followed by optional attribute=value pairs, called entries. The section name and attributes are case sensitive on OS/2. You cannot put comments in the ODBC.INI file. The ODBC.INI format, as specified by the Microsoft Open Database Connectivity (ODBC) specification, is as follows: [ODBC Data Sources] Lists data sources available to ODBC. ds_name1=driver_desc1 ds_name2=driver_desc2 . . Lists each data source name followed by a description. [ds_name1] Defines the actual ODBC Driver source for example, Oracle. Driver=path\dll Defines the path to the driver DLL. Description=desc Briefly describes the data source . . . [ds_name2] Driver=path\dll Description=desc . . . The [ODBC Data Sources] section is mandatory. It provides the driver manager with a list of data sources that are supported for your connection requests. You can change the names in this list, but each entry must match its corresponding [ds_name] section in ODBC.INI. The [ds_name] sections contain a Driver= specification, which points to the location of the installed driver, as well as a Description= specification that describes the driver. If you change the location of a driver, you can change the Driver= specification to match the new location. You can also use just the name of the driver, and the driver manager will attempt to locate the driver based on information obtained from your environment. You might need to assign other entries depending on the data source you are configuring. The "Connecting to a Data Source Using a Connection String" topic for each individual driver lists the attributes that you can set. Use the ODBC Administrator program to modify ODBC.INI in all environments that provide this interface. This protects the file from becoming corrupted or nonfunctional. ═══ 1.4. ODBC.INI Examples ═══ The following example shows an ODBC.INI file as defined by the ODBC specification: ;--------------------------------------------------- ; ODBC.INI - INTERSOLV ODBC Driver Manager INI File ;--------------------------------------------------- [ODBC Data Sources] ivss=INTERSOLV SQL Server ivor7=INTERSOLV Oracle [SQL Server] Driver=ivss08.dll Description=INTERSOLV SQL Server driver ServerName=alice LogonID=test [Oracle] Driver=ivor708.dll Description=INTERSOLV Oracle driver ServerName=t:magna:V7 LogonID=test ═══ 1.5. Error Messages ═══ Error messages may come from ■ An ODBC driver ■ The database system ■ The driver manager An error reported on an ODBC driver has the following format: [vendor] [ODBC_component] message ODBC_component is the component in which the error occurred. For example, an error message from INTERSOLV's SQL Server driver would look like this: [INTERSOLV] [ODBC SQL Server driver] Login incorrect. If you get this type of error, check the last ODBC call your application made for possible problems or contact your ODBC application vendor. An error that occurs in the data source includes the data source name, in the following format: [vendor] [ODBC_component] [data_source] message With this type of message, ODBC_component is the component that received the error from the data source indicated. For example, you may get the following message from an Oracle data source: [INTERSOLV] [ODBC Oracle driver] [Oracle] ORA-0919: specified length too long for CHAR column If you get this type of error, you did something incorrectly with the database system. Check your database system documentation for more information or consult your database administrator. In this example, you would check your Oracle documentation. The driver manager is a DLL that establishes connections with drivers, submits requests to drivers, and returns results to applications. An error that occurs in the driver manager has the following format: [vendor] [ODBC DLL] message vendor can be Microsoft or INTERSOLV. For example, an error from the Microsoft driver manager might look like this: [Microsoft] [ODBC DLL] Driver does not support this function ═══ 2. SQL for Flat-File Drivers ═══ To enable your application to be portable across other databases, you can use SQL statements with the flat-file drivers (dBASE and Text). The database drivers parse SQL statements and translate them into a form that the database can understand. The following SQL statements let you read, insert, update, and delete records from a database, create new tables, and drop existing tables: ■ Select Statement ■ Create Table Statement ■ Drop Table Statement ■ Insert Statement ■ Update Statement ■ Delete Statement The following topics describe specific elements of the SQL statements supported: ■ Aggregate Functions ■ SQL Expressions ■ Reserved Keywords ═══ 2.1. Select Statement ═══ The form of the Select statement supported by the flat-file drivers is SELECT [DISTINCT] {* | column_expression, ...} FROM table_names [table_alias] ... [ WHERE expr1 rel_operator expr2 ] [ GROUP BY {column_expression, ...} ][ HAVING expr1 rel_operator expr2 ] [ UNION [ALL] (SELECT...) ] [ ORDER BY {sort_expression [DESC | ASC]}, ... ] [ FOR UPDATE [OF {column_expression, ...}] ] Click any keyword to see a description of its statement clause. ═══ 2.2. Select Clause ═══ Follow Select with a list of column expressions you want to retrieve or an asterisk (*) to retrieve all fields. SELECT [DISTINCT] {* | column_expression, ...} column_expression can be simply a field name (for example, LAST_NAME). More complex expressions may include mathematical operations or string manipulation (for example, SALARY * 1.05). See SQL Expressions for more information. Separate multiple column expressions with commas (for example, LAST_NAME, FIRST_NAME, HIRE_DATE). Field names can be prefixed with the table name or alias. For example, EMP.LAST_NAME or E.LAST_NAME, where E is the alias for the table EMP. The Distinct operator can precede the first column expression. This operator eliminates duplicate rows from the result of a query. For example, SELECT DISTINCT dep FROM emp Select statements can also include aggregate functions. ═══ 2.2.1. Aggregate Functions ═══ Aggregate functions can also be a part of a Select clause. Aggregate functions return a single value from a set of records. An aggregate can be used with a field name (for example, AVG(SALARY)) or in combination with a more complex column expression (for example, AVG(SALARY * 1.07)). The column expression can be preceded by the Distinct operator. The Distinct operator eliminates duplicate values from an aggregate expression. For example, COUNT (DISTINCT last_name) In this example, only distinct last name values are counted. The following are valid aggregates: Aggregate Returns SUM The total of the values in a numeric field expression. For example, SUM(SALARY) returns the sum of all salary field values. AVG The average of the values in a numeric field expression. For example, AVG(SALARY) returns the average of all salary field values. COUNT The number of values in any field expression. For example, COUNT(NAME) returns the number of name values. When using COUNT with a field name, COUNT returns the number of non-null field values. A special example is COUNT(*), which returns the number of records in the set, including records with null values. MAX The maximum value in any field expression. For example, MAX(SALARY) returns the maximum salary field value. MIN The minimum value in any field expression. For example, MIN(SALARY) returns the minimum salary field value. ═══ 2.2.2. From Clause ═══ The From clause indicates the tables that will be used in the Select statement. The format of the From clause is FROM table_names [table_alias] table_names can be one or more simple table names in the current working directory or complete pathnames. table_alias is a name used to refer to this table in the rest of the Select statement. Database field names may be prefixed by the table alias. Given the table specification FROM emp E you may refer to the LAST_NAME field as E.LAST_NAME. Table aliases must be used if the Select statement joins a table to itself. For example, SELECT * FROM emp E, emp F WHERE E.mgr_id = F.emp_id The equal sign (=) includes only matching rows in the results. If you are joining more than one table, you may use LEFT OUTER JOIN, which includes nonmatching rows in the first table you name. For example, SELECT * FROM T1 LEFT OUTER JOIN T2 on T1.key = T2.key ═══ 2.2.3. Where Clause ═══ The Where clause specifies the conditions that records must meet to be retrieved. The Where clause contains conditions in the form: WHERE expr1 rel_operator expr2 expr1 and expr2 may be field names, constant values, or expressions. rel_operator is the relational operator that links the two expressions. See SQL Expressions for more information. For example, the following Select statement retrieves the names of employees that make at least $20,000. SELECT last_name,first_name FROM emp WHERE salary >= 20000 ═══ 2.2.4. Group By Clause ═══ The Group By clause specifies the names of one or more fields by which the returned values should be grouped. This clause is used to return a set of aggregate values. It has the following form: GROUP BY column_expressions column_expressions must match the column expression used in the Select clause. A column expression can be one or more field names of the database table, separated by a comma (,) or one or more expressions, separated by a comma (,). See SQL Expressions for more information. The following example sums the salaries in each department. SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id This statement returns one row for each distinct department ID. Each row contains the department ID and the sum of the salaries of the employees in the department. ═══ 2.2.5. Having Clause ═══ The Having clause enables you to specify conditions for groups of records ( for example, display only the departments that have salaries totaling more than $200,000). This clause is valid only if you have already defined a Group By clause. It has the following form: HAVING expr1 rel_operator expr2 expr1 and expr2 can be field names, constant values, or expressions. These expressions do not have to match a column expression in the Select clause. rel_operator is the relational operator that links the two expressions. See SQL Expressions for more information. The following example returns only the departments whose sums of salaries are greater than $200,000: SELECT dept_id, sum(salary) FROM emp GROUP BY dept_id HAVING sum(salary) > 200000 ═══ 2.2.6. Union Operator ═══ The Union operator combines the results of two Select statements into a single result. The single result is all of the returned records from both Select statements. By default, duplicate records are not returned. To return duplicate records, use the All keyword (UNION ALL). The form is SELECT statement UNION [ALL] SELECT statement When using the Union operator, the select lists for each Select statement must have the same number of column expressions with the same data types and must be specified in the same order. For example, SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay, birth_date FROM person This example has the same number of column expressions, and each column expression, in order, has the same data type. The following example is not valid because the data types of the column expressions are different (SALARY from EMP has a different data type than LAST_NAME from RAISES). This example does have the same number of column expressions in each Select statement but the expressions are not in the same order by data type. SELECT last_name, salary FROM emp UNION SELECT salary, last_name FROM raises ═══ 2.2.7. Order By Clause ═══ The Order By clause indicates how the records are to be sorted. The form is ORDER BY {sort_expression [DESC | ASC]}, ... sort_expression can be field names, expressions, or the positional number of the column expression to use. The default is to perform an ascending (ASC) sort. For example, to sort by LAST_NAME you could use either of the following Select statements: SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name or SELECT emp_id, last_name, first_name FROM emp ORDER BY 2 In the second example, LAST_NAME is the second column expression following Select, so Order By 2 sorts by LAST_NAME. ═══ 2.2.8. For Update Of Clause ═══ The For Update Of clause locks the records of the database table selected by the Select statement. The form is FOR UPDATE [OF column_expressions] column_expressions is a list of field names in the database table that you intend to update, separated by a comma (,). Note that column_expressions is optional. The following example returns all records in the employee database that have a SALARY field value of more than $20,000. When each record is fetched, it is locked. If the record is updated or deleted, the lock is held until you commit the change. Otherwise, the lock is released when you fetch the next record. SELECT * FROM emp WHERE salary > 20000 FOR UPDATE OF last_name, first_name, salary ═══ 2.3. SQL Expressions ═══ Expressions are used in the Where clauses, Having clauses, Order By clauses, Select statements. Expressions enable you to use mathematical operations as well as character string and date manipulation operators to form complex database queries. The most common expression is a simple field name. You can combine a field name with other expression elements. Other valid expression elements are as follows: ■Field names ■Constants ■Exponential notation ■Numeric operators ■Character operators ■Date operators ■Relational operators ■Logical operators ■Functions The order in which these elements are evaluated is described in Operator Precedence. ═══ 2.3.1. Constants ═══ Constants are values that do not change. For example, in the expression PRICE * 1.05, the value 1.05 is a constant. You must enclose character constants in pairs of single (') or double quotation marks ("). To include a single quotation mark in a character constant enclosed by single quotation marks, use two single quotation marks together (for example, 'Don''t'). Similarly, if the constant is enclosed by double quotation marks, use two double quotation marks to include one. You must enclose date and time constants in braces ({}), for example, {01/30/89} and {12:35:10}. The form for date constants is MM/DD/YY or MM/DD/YYYY. The form for time constants is HH:MM:SS. The logical constants are .T. and 1 for True and .F. and 0 for False. For portability, use 1 and 0. ═══ 2.3.2. Exponential Notation ═══ You may include exponential notation. For example, SELECT col1, 3.4E+& FROM table1 WHERE calc < 3.4E-6 * col2 ═══ 2.3.3. Numeric Operators ═══ You may include the following operators in numeric expressions: Operator Meaning + Addition - Subtraction * Multiplication / Division ** Exponeniation ^ Exponentiation The following table shows examples of numeric expressions. For these examples, assume SALARY is 20000. Example Resulting value salary + 10000 30000 salary * 1.1 22000 2 ** 3 8 You can precede numeric expressions with a unary plus (+) or minus (-). For example, -(salary * 1.1) is -22000. ═══ 2.3.4. Character Operators ═══ Character expressions may include the following operators: Operator Meaning + Concatenation keeping trailing blanks. - Concatenation moving trailing blanks to the end. The following table shows examples of character expressions. In the examples, LAST_NAME is 'JONES ' and FIRST_NAME is 'ROBERT '. Example Resulting value first_name + last_name 'ROBERT JONES' first_name - last_name 'ROBERTJONES ' Note: Some flat-file drivers return character data with trailing blanks as shown in the table. However, you cannot rely on the driver to return blanks. Therefore, if you want an expression that works with drivers that do and do not return trailing blanks, use the TRIM function before concatenating strings to make the expression portable. For example: TRIM(first_name) + '' + TRIM(last_name) ═══ 2.3.5. Date Operators ═══ You may include the following operators in date expressions: Operator Meaning + Add a number of days to a date to produce a new date. - The number of days between two dates, or subtract a number of days from a date to produce a new date. The following chart shows examples of date expressions. In these examples, hire_date is {01/30/90}. Example Resulting value hire_date + 5 {02/04/90} hire_date - {01/01/90} 29 hire_date - 10 {01/20/90} ═══ 2.3.6. Relational Operators ═══ The relational operators separating the two expressions may be any one of the following: Operator Meaning = Equal <> Not Equal > Greater Than >= Greater Than or Equal < Less Than <= Less Than or Equal Like Matching a pattern Not Like Not matching a pattern Is Null Equal to Null Is Not Null Not Equal to Null Between Range of values between a lower and upper bound. In A member of a set of specified values or a member of a subquery. Exists True if a subquery returned at least one record. Any Compares a value to each value returned by a subquery. Any must be prefaced by =, <>, >, >=, <, or <=. =Any is equivalent to In. All Compares a value to each value returned by a subquery. All must be prefaced by =, <>, >, >=, <, or <=. The following list shows some examples of relational operators: salary <= 40000 dept = 'D101' hire_date > {01/30/89} salary + commission >= 50000 last_name LIKE 'Jo%' salary IS NULL salary BETWEEN 10000 AND 20000 WHERE salary = ANY (SELECT salary FROM emp WHERE dept = 'D101') WHERE salary > ALL (SELECT salary FROM emp WHERE dept = 'D101') ═══ 2.3.7. Logical Operators ═══ Two or more conditions may be combined to form more complex criteria. When two or more conditions are present, they must be related by AND or OR. For example, salary = 40000 AND exempt = 1 The logical NOT operator is used to reverse the meaning. For example, NOT (salary = 40000 AND exempt = 1) ═══ 2.3.8. Operator Precedence ═══ As expressions become more complex, the order in which the expressions are evaluated becomes important. The following table shows the order in which the operators are evaluated. The operators in the first line are evaluated first, then those in the second line, and so on. Operators in the same line are evaluated left to right in the expression. Order Operator 1 Unary -, Unary + 2 ** 3 *, / 4 +, - 5 =, <>, <, <=, >, >=, Like, Not Like, Is Null, Is Not Null, Between, In, Exists, Any, All 6 Not 7 AND 8 OR The following example shows the importance of precedence: WHERE salary > 40000 OR hire_date > {01/30/89} AND dept = 'D101' Because AND is evaluated first, this query retrieves employees in department D101 hired after January 30, 1989, as well as every employee making more than $40,000, no matter what department or hire date. To force the clause to be evaluated in a different order, use parentheses to enclose the conditions to be evaluated first. For example, WHERE (salary > 40000 OR hire_date > {01/30/89}) AND dept = 'D101' retrieves employees in department D101 that either make more than $40,000 or were hired after January 30, 1989. ═══ 2.3.9. Functions ═══ The flat-file drivers support a number of functions that you may use in expressions. In the following topics, the functions are grouped according to the type of result they return. Functions that Return Character Strings Functions that Return Numbers Functions that Return Dates ═══ 2.3.10. Functions that Return Character Strings ═══ The following functions return character string values: Function Description CHR Converts an ASCII code into a one-character string. For example, CHR(67) returns C. RTRIM Removes trailing blanks from a string For example, RTRIM('ABC ') returns ABC. TRIM Removes trailing blanks from a string. For example, TRIM('ABC ') returns ABC. LTRIM Removes leading blanks from a string. For example, LTRIM(' ABC') returns ABC. UPPER Changes each letter of a string to uppercase. For example, UPPER('Allen') returns ALLEN. LOWER Changes each letter of a string to lowercase. For example, LOWER('Allen') returns allen. LEFT Returns leftmost characters of a string. For example, LEFT('Mattson',3) returns Mat. RIGHT Returns rightmost characters of a string. For example, RIGHT('Mattson',4) returns tson. SUBSTR Returns a substring of a string. Parameters are the string, the first character to extract, and the number of characters to extract (optional). For example, SUBSTR('Conrad',2,3) returns onr. Also, SUBSTR('Conrad',2) returns onrad. SPACE Generates a string of blanks. For example, SPACE(5) returns ' '. DTOC Converts a date to a character string. An optional second parameter determines the format of the result: For example: 0 (the default) returns MM/DD/YY 1 returns DD/MM/YY 2 returns YY/MM/DD 10 returns MM/DD/YYYY 11 returns DD/MM/YYYY 12 returns YYYY/MM/DD An optional third parameter specifies the date separator character. If not specified, a slash (/) is used. For example: DTOC({01/30/89}) returns 01/30/89 DTOC({01/30/89}, 0) returns 01/30/89 DTOC({01/30/89}, 1) returns 30/01/89 DTOC({01/30/89}, 2,'-') returns 89-01-30 DTOS Converts a date to a character string using the format YYYYMMDD. DTOS({01/23/90}) returns 19900123. IIF Returns one of two values. Parameters are a logical expression, the true value, and the false value. If the logical expression evaluates to True, the function returns the true value. Otherwise, it returns the false value. For example, IIF(salary>20000,'BIG','SMALL') returns BIG if SALARY is greater than 20000. If not, it returns SMALL. STR Converts a number to a character string. Parameters are the number, the total number of output characters (including the decimal point), and optionally the number of digits to the right of the decimal point. STR(12.34567,4) returns 12 STR(12.34567,4,1) returns 12.3 STR(12.34567,6,3) returns 12.346 STRVAL Converts a value of any type to a character string. STRVAL('Woltman') returns Woltman STRVAL({12/25/53}) returns 12/25/53 STRVAL (5 * 3) returns 15 STRVAL (4 = 5) returns 'False' TIME Returns the time of day as a string. For example, at 9:49 PM, TIME() returns 21:49:00 USERNAME Returns the name in the UID attribute as a character string. If UID=Allen, USERNAME() returns Allen. ═══ 2.3.11. Functions that Return Numbers ═══ The following functions return number values: Function Description MOD Divides two numbers and returns the remainder of the division. For example, MOD(10,3) returns 1 LEN Returns the length of a string. For example, LEN('ABC') returns 3 MONTH Returns the month part of a date. For example, MONTH({01/30/89}) returns 1 DAY Returns the day part of a date. For example, DAY({01/30/89}) returns 30 YEAR Returns the year part of a date. For example, YEAR({01/30/89}) returns 1989 MAX Returns the larger of two numbers. For example, MAX(66,89) returns 89 MIN Returns the smaller of two numbers. For example, MIN(66,89) returns 66 POW Raises a number to a power. For example, POW(7,2) returns 49 INT Returns the integer part of a number. For example, INT(6.4321) returns 6 ROUND Rounds a number. For example, ROUND(123.456, 0) returns 123. ROUND(123.456, 2) returns 123.46. ROUND(123.456, -2) returns 100. NUMVAL Converts a character string to a number. If the character string is not a valid number, a zero is returned. For example, NUMVAL('123') returns the number 123 VAL Converts a character string to a number. If the character string is not a valid number, a zero is returned. For example, VAL('123') returns the number 123. ═══ 2.3.12. Functions that Return Dates ═══ The following functions return date values: Function Description DATE Returns today's date. If today is 12/25/79, DATE() returns {12/25/79} TODAY Returns today's date. If today is 12/25/79, TODAY() returns {12/25/79} DATEVAL Converts a character string to a date. DATEVAL('01/30/89') returns {01/30/89} CTOD Converts a character string to a date. An optional second parameter specifies the format of the character string: 0 (the default) returns MM/DD/YY, 1 returns DD/MM/YY, and 2 returns YY/MM/DD. CTOD('01/30/89') returns {01/30/89} CTOD('01 /30/89',1) returns {30/01/89} The following examples use some of the number and date functions. Retrieve all employees that have been with the company at least 90 days: SELECT first_name, last_name FROM emp WHERE DATE() - hire_date >= 90 Retrieve all employees hired in January of this year or last year: SELECT first_name, last_name FROM emp WHERE MONTH(hire_date) = 1 AND (YEAR(hire_date) = YEAR(DATE()) OR YEAR(hire_date) = YEAR(DATE()) - 1) ═══ 2.4. Create Table Statement ═══ The Create Table statement is used to create database files. The form of the Create Table statement is CREATE TABLE filename (col_definition[,col_definition, ...]) filename can be a simple filename or a full pathname. A simple filename is preferred for portability to other SQL data sources. If it is a simple filename, the file is created in the directory you specified as the database directory in the connection string. If you did not specify a database directory in the connection string, the file is created in the directory you specified as the database directory in ODBC.INI. If you did not specify a database directory in either place, the file is created in the current working directory at the time you connected to the driver. col_definition is the column name, followed by the data type, followed by an optional column constraint definition. Values for column names are database specific. The data type specifies a column's data type. The only column constraint definition currently supported by some flat-file drivers is "not null." Not all flat-file tables support "not null" columns. In the cases where not null is not supported, this restriction is ignored and the driver returns a warning if "not null" is specified for a column. The "not null" column constraint definition is allowed in the driver so that you can write a database-independent application (and not be concerned about the driver raising an error on a Create Table statement with a "not null" restriction). A sample Create Table statement to create an employee database table is CREATE TABLE emp (last_name CHAR(20) NOT NULL, first_name CHAR(12) NOT NULL, salary NUMERIC (10,2) NOT NULL, hire_date DATE NOT NULL) ═══ 2.5. Drop Table Statement ═══ The Drop Table statement is used to delete database files. The form of the Drop Table statement is DROP TABLE filename filename may be a simple filename (EMP) or a full pathname. A simple filename is preferred for portability to other SQL data sources. If it is a simple filename, the file is dropped from the directory you specified as the database directory in the connection string. If you did not specify a database directory in the connection string, the file is deleted from the directory you specified as the database directory in ODBC.INI. If you did not specify a database directory in either of these places, the file is dropped from the current working directory at the time you connected to the driver. A sample Drop Table statement to delete the employee database table is DROP TABLE emp ═══ 2.6. Insert Statement ═══ The SQL Insert statement is used to add new records to a database table. With it, you can specify either of the following: ■ A list of values to be inserted as a new record. ■ A Select statement that copies data from another table to be inserted as a set of new records. The form of the Insert statement is INSERT INTO table_name [(col_name, ...)] {VALUES (expr, ...) | select_statement} table_name may be a simple filename or a full pathname. A simple filename is preferred for portability to other SQL data sources. col_name is an optional list of column names giving the name and order of the columns whose values are specified in the Values clause. If you omit col_name, the value expressions (expr) must provide values for all columns defined in the file and must be in the same order that the columns are defined for the file. expr is the list of expressions giving the values for the columns of the new record. Usually, the expressions are constant values for the columns. Character string values must be enclosed in single or double quotation marks, date values must be enclosed in braces {}, and logical values that are characters must be enclosed in periods (for example, .T. or .F.). An example of an Insert statement that uses a list of expressions is INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date) VALUES ('Smith', 'John', 'E22345', 27500, {4/6/91}) Each Insert statement adds one record to the database table. In this case, a record has been added to the employee database table, EMP. Values are specified for five columns. The remaining columns in the table are assigned a blank value, meaning Null. In addition, this statement supports subqueries, which allows applications to select rows from one table and insert them into another. The form of an Insert statement that supports subqueries is: INSERT INTO table_name [(col_name, ...)] query_expression table_name may be a simple filename or a full pathname. A simple filename is preferred for portability to other SQL data sources. col_name is an optional list of column names giving the name and order of the columns whose values are specified in the Values clause. If you omit col_name, the value expressions (expr) must provide values for all columns defined in the file and must be in the same order that the columns are defined for the file. expr is the list of expressions giving the values for the columns of the new record. Usually, the expressions are constant values for the columns. Character string values must be enclosed in single or double quotation marks, date values must be enclosed in braces {}, and logical values that are characters must be enclosed in periods (for example, .T. or .F.). An example of an Insert statement that uses a list of expressions is INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date) VALUES ('Smith', 'John', 'E22345', 27500, {4/6/91}) Each Insert statement adds one record to the database table. In this case a record has been added to the employee database table, EMP. Values are specified for five columns. The remaining columns in the table are assigned a blank value, meaning Null. select_statement is a query that returns values for each col_name value specified in the column name list. Using a Select statement instead of a list of value expressions lets you select a set of rows from one table and insert it into another table using a single Insert statement. An example of an Insert statement that uses a Select statement is: INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary) SELECT first_name, last_name, emp_id, dept, salary from emp WHERE dept = 'D050' In this type of Insert statement, the number of columns to be inserted must match the number of columns in the Select statement. The list of columns to be inserted must corresond to the columns in the Select statement just as it would to a list of value expressions in the other type of Insert statement. That is, the first column inserted corresponds to the first column selected; the second inserted to the second, etc. The size and data type of these corresponding columns must be compatible. Each column in the Select list should have a data type that the ODBC driver accepts on a regular Insert/Update of the corresponding column in the Insert list. Values are truncated when the size of the value in the Select list column is greater than the size of the corresponding Insert list column. The select_statement is evaluated before any values are inserted. This query cannot be made on the table into which values are inserted. ═══ 2.7. Update Statement ═══ The SQL Update statement is used to change records in a database file. The form of the Update statement supported for flat-file drivers is UPDATE filename SET col_name = expr, ... [ WHERE { conditions | CURRENT OF cursor_name } ] filename may be a simple filename or a full pathname. A simple filename is preferred for portability to other SQL data sources. col_name is the name of a column whose value is to be changed. Several columns can be changed in one statement. expr is the new value for the column. The expression can be a constant value or a subquery. Character string values must be enclosed with single or double quotation marks, date values must be enclosed by braces {}, and logical values that are letters must be enclosed by periods (for example, .T. or .F.). Subqueries must be enclosed in parentheses. The Where clause is any valid clause. It determines which records are to be updated. The Where Current Of cursor_name clause can be used only by developers coding directly to the ODBC API. It causes the row at which cursor_name is positioned to be updated. This is called a "positioned update." You must first execute a Select...For Update statement with a named cursor and fetch the row to be updated. An example of an Update statement on the employee table is UPDATE emp SET salary=32000, exempt=1 WHERE emp_id = 'E10001' The Update statement changes every record that meets the conditions in the Where clause. In this case the salary and exempt status is changed for all employees having the employee ID E10001. Since employee IDs are unique in the employee table, only one record is updated. An example using a subquery is UPDATE emp SET salary = (SELECT avg(salary) from emp) WHERE emp_id = 'E10001' In this case, the salary is changed to the average salary in the company for the employee having employee ID E10001. ═══ 2.8. Delete Statement ═══ The SQL Delete statement is used to delete records from a database table. The form of the Delete statement supported for flat-file drivers is DELETE FROM filename [ WHERE { conditions | CURRENT OF cursor_name } ] filename may be a simple filename or a full pathname. A simple filename is preferred for portability to other SQL data sources. The Where clause is any valid clause. It determines which records are to be deleted. If you include only the keyword Where, all records in the table are deleted but the file is left intact. The Where Current Of cursor_name clause can be used only by developers coding directly to the ODBC API. It causes the row at which cursor_name is positioned to be deleted. This is called a "positioned delete." You must first execute a Select...For Update statement with a named cursor and fetch the row to be deleted. An example of a Delete statement on the employee table is DELETE FROM emp WHERE emp_id = 'E10001' Each Delete statement removes every record that meets the conditions in the Where clause. In this case every record having the employee ID E10001 is deleted. Since employee IDs are unique in the employee table, at most one record is deleted. ═══ 2.9. Reserved Keywords ═══ The following words are reserved for use in SQL statements. If they are used for file or column names in a database that you use, you must enclose them in quotation marks in any SQL statement where they appear as file or column names. ■ ALL ■ AND ■ BETWEEN ■ COMPUTE ■ CROSS ■ DISTINCT ■ FOR ■ FROM ■ FULL ■ GROUP ■ HAVING ■ HERE ■ INNER ■ INTO ■ LEFT ■ LIKE ■ NATURAL ■ NOT ■ NULL ■ ON ■ OPTIONS ■ OR ■ ORDER ■ RIGHT ═══ 3. DB2/2 Driver ═══ The DB2/2 driver supports the use of the Client Application Enabler (CAE) 1.2 interfaces. Note that the previous DB2/2 driver, QEDBMnn.DLL, is not supported in this release of INTERSOLV ODBC drivers. The DB2/2 driver supports the following database systems: ■ DB2/2 for OS/2 (formerly Database Manager for OS/2) ■ DB2/6000 ■ SQL/400 (through the DDCS/2 or DDCS/6000 gateway) ■ MVS DB2 (through the DDCS/2 or DDCS/6000 gateway) ■ SQL/DS (through the DDCS/2 or DDCS/6000 gateway) Note: The driver filename has changed from QEDBMnn.DLL to IVDB2nn.DLL. The previous version of this driver does not support all of these database systems on OS/2. ═══ 3.1. DB2/2 System Requirements ═══ System requirements are as follows: ■ The DB2/2 Server must be installed as the Server Version for remote clients to use it. The Local Version supports only OS/2 applications running on the server. ■ To use the DDCS/2 gateway, you must install the DDCS/2 Multi-User Edition for remote clients to use it. The Single-User Version supports only OS/2 applications running on the server. ■ For remote database access, DB2/2 requires either Communications Manager from Extended Services version 1.0 (or higher), Communications Manager/2 version 1.0 (or higher), Network Transport Services/2 version 1.0 (or higher), or LAN Services. ═══ 3.2. Configuring DB2/2 Data Sources ═══ To configure a DB2/2 data source, do the following: 1. Start the ODBC Administrator by double-clicking the ODBC icon in the the Windows Control Panel. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV DB2/2 and click OK. 3. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 4. Specify values as follows: Data Source Name: A string that identifies this DB2/2 data source configuration in ODBC.INI. Examples include "Accounting" or "DB2/2-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "DB2/2 on Server #1." Database Name: The name of the database to which you want to connect by default. The following values are optional: Owner of Catalog Tables: On most DB2/2 systems, SYSIBM is the owner of the catalog system tables. If you have read access to the system tables, you do not need to change this option. If you do not have read access, the system administrator must create a view of the system tables in another account and give you permission to use that view. In this case, specify the Authorization ID for the account that owns the views of the system tables. If you are using DDCS/2 to connect to a SQL/400 database, specify a comma-separated list of the Collection IDs to which you want to have access in your ODBC application. If you are using DDCS/2 to connect to a SQL/DS database, specify SYSTEM as the owner. Groups: A value to determine which tables you can access. Your system administrator may have placed you in a "group" of users and granted table access to the entire group. If this is the case, specify the names of any groups to which you belong; separate each name with a comma. Alternatively, specify the word ALL so that you see all table names even if you cannot access the table. Default Authorization ID: The default Logon ID used to connect to your DB2/2 database. A Logon ID is required only if security is enabled on your database. Your ODBC application may override this value or you may change this value in the logon dialog box. Cursor Behavior: Select Preserve if you want cursors to be held at the current position when the transaction ends. Otherwise, leave this set to Close. Selecting Preserve may impact the performance of your database operations. This setting does not apply to SQL/DS. When you select Preserve, the driver returns SQL_CB_PRESERVE from SQLGetInfo (SQL_CURSOR_COMMIT_BEHAVIOR). But only Select statements and prepared Update or Delete . . . Where Current of Cursor statements are preserved when the transaction ends. All other prepared statements are closed and deleted. 5. You must catalog any database you want to access. To do so, click the Catalog Db button. The Catalog Remote Database dialog box appears. In this dialog box, type an alias name, the name of the database, and the name of the remote node (like an alias for the remote node where the server is installed and the protocol used to access it). Click Add. The alias name you added will be listed in the Database Name drop-down list in the logon dialog box when you exit this dialog box. This dialog box also lets you uncatalog remote databases. To do this, select the alias you want to delete from the Alias Name drop-down list and click Delete. 6. Click Catalog Node to provide information about the remote site for the DB2/2 databases. In the Catalog Node dialog box, select a node name (only names of previously cataloged nodes are available, the name of the server or number of the adapter (for NetBIOS nodes), the name of the remote node where the DB2 server is installed, and the type of communication protocol to use to access the node. Optionally, enter a comment. Click Add. 7. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. The translators that are listed in this dialog box are determined by the values listed in the ODBC Translators section of your ODBCINST.INI file. 8. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 3.3. DB2/2 and DDCS/2 Gateway ═══ Access to DB2/2 databases, or other databases via the DDCS/2 gateway, requires that you bind to tables and grant privileges to all users. These processes are described in the following topics. Binding Before you can access a table, you must bind to the database. You must bind to every database you intend to access. To do this, enter the following commands on the server: SQLBIND QECSV1.BND database_name SQLBIND QERRV1.BND database_name SQLBIND QEURV1.BND database_name SQLBIND QECSWHV1.BND database_name SQLBIND QERRWHV1.BND database_name SQLBIND QEURWHV1.BND database_name You may need to copy the bind files from the installation directory to the server. database_name is the name of the database you want to access. Note for SQL/DS Users: For binding to SQL/DS databases , enter only the first three of these commands. The files QECSWHV1.BND, QERRWHV1.BND, and QEURWHV1.BND will not work with SQL/DS. For SQL/400 Users If you receive the error `QSYS.QE' is an undefined name you must create a collection named QE. To do this, enter the following command on the host AS/400: CREATE COLLECTION QE Then issue the SQLBIND commands again. Granting Privileges Users must be granted the necessary privileges before they can access a database or table through a gateway. DB2/2 users are assigned an Authorization ID. There are three categories of Authorization IDs: administrator, local administrator, and user. The third category, user, must be granted the privilege to access DB2/2 tables. To grant the necessary privileges, an administrator must run Query Manager or the DBM Command Line Interface and execute the appropriate GRANT statement. For DB2/2 databases and databases that reside on DDCS/2 connecting to DB2: GRANT EXECUTE, BIND ON PROGRAM QE.QECSV1 TO authorization_list GRANT EXECUTE, BIND ON PROGRAM QE.QERRV1 TO authorization_list GRANT EXECUTE, BIND ON PROGRAM QE.QEURV1 TO authorization_list GRANT EXECUTE, BIND ON PROGRAM QE.QECSWHV1 TO authorization_list GRANT EXECUTE, BIND ON PROGRAM QE.QERRWHV1 TO authorization_list GRANT EXECUTE, BIND ON PROGRAM QE.QEURWHV1 TO authorization_list For databases that reside on the DDCS/2 gateway connecting to SQL/DS: GRANT RUN ON QE.QECSV1 TO authorization_list GRANT RUN ON QE.QERRV1 TO authorization_list GRANT RUN ON QE.QEURV1 TO authorization_list GRANT RUN ON QE.QECSWHV1 TO authorization_list GRANT RUN ON QE.QERRWHV1 TO authorization_list GRANT RUN ON QE.QEURWHV1 TO authorization_list For databases that reside on the DDCS/2 gateway connecting to SQL/400: GRANT RUN ON PROGRAM QE.QECSV1 TO authorization_list GRANT RUN ON PROGRAM QE.QERRV1 TO authorization_list GRANT RUN ON PROGRAM QE.QEURV1 TO authorization_list GRANT RUN ON PROGRAM QE.QECSWHV1 TO authorization_list GRANT RUN ON PROGRAM QE.QERRWHV1 TO authorization_list GRANT RUN ON PROGRAM QE.QEURWHV1 TO authorization_list authorization_list is a list of comma-separated Authorization IDs or Group IDs, or PUBLIC if you want to grant access to all users. ═══ 3.4. DB2/6000 and DDCS/6000 Gateway ═══ Before you can access a table via DB2/6000 or the DDCS/6000 gateway, you must bind to the database and grant each user access to it. To do this, enter the following commands on the client computer from the DBM Command Line Interface: BIND QECSV1.BND BLOCKING ALL GRANT authorization_list BIND QERRV1.BND BLOCKING ALL GRANT authorization_list BIND QEURV1.BND BLOCKING ALL GRANT authorization_list BIND QECSWHV1.BND BLOCKING ALL GRANT authorization_list BIND QERRWHV1.BND BLOCKING ALL GRANT authorization_list BIND QEURWHV1.BND BLOCKING ALL GRANT authorization_list authorization_list is a list of comma-separated Authorization IDs or Group IDs, or PUBLIC if you want to grant access to all users. Note for SQL/DS Users: For binding to SQL/DS databases , enter only the first three of these commands. The files QECSWHV1.BND, QERRWHV1.BND, and QEURWHV1.BND will not work with SQL/DS. For SQL/400 Users If you receive the error `QSYS.QE' is an undefined name you must create a collection named QE. To do this, enter the following command on the host AS/400: CREATE COLLECTION QE Then issue the BIND commands again. ═══ 3.5. Connecting to DB2/2 Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. In the logon dialog box, do the following: 1. Type the name of the remote database or select the name of the remote database from the Database Name drop-down list. You must have cataloged any database you want to access from the client. (See the topic Configuring Data Sources for information on how to do this.) 2. If required, type your user name (authorization ID). 3. If required, type your password. If you leave your user name and password blank, the ODBC application assumes you have already logged on using SQLLOGN2 (under DOS) or using User Profile Management (under OS/2). If you have not, the application returns an error. You must either type your user name and password in the dialog box or log on using SQLLOGN2 and STARTDRQ (under DOS) or using User Profile Management (under OS/2). See the IBM Extended Services for OS/2 Guide to DB2/2 Clients for more information. 4. Click OK to complete the logon and to update the values in ODBC.INI. ═══ 3.6. Connecting to DB2/2 Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for DB2/2 is DSN=DB22 TABLES;DB=PAYROLL;UID=JOHN;PWD=XYZZY;GRP=ACCTNG The following list gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN) Identifies a DB2/2 data source configuration in ODBC.INI. Examples include "Accounting" or "DB2/2-Serv1." Database (DB) Name of the database to which you want to connect. Sysibm (SI) On most DB2/2 systems, SYSIBM is the owner of the catalog system tables. If you have read access to the system tables, you do not need to change this option. If you do not have read access, the database administrator must create a view of the system tables in another account and give you permission to use that view. In this case, specify the Authorization ID for the account that owns the views of the system tables. If you are using DDCS/2 to connect to a SQL/400 database, specify a comma-separated list of the Collection IDs to which you want to have access in your ODBC application. If you are using DDCS/2 to connect to a SQL/DS database, specify SYSTEM as the owner. Groups (GRP) Determines which tables you can access. Your system administrator may have placed you in a "group" of users and granted table access to the entire group. If this is the case, set Groups to the names of any groups to which you belong; separate each name with a comma. Alternatively, Groups=ALL lets your application see all table names even if you cannot access the table. LogonID (UID) Default logon ID used to connect to your DB2/2 database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. Password (PWD) Password. CursorBehavior (CB) CursorBehavior={0 | 1}. Determines whether cursors are preserved or closed at the end of each transaction. Valid values are 1 (preserve) and 0 (close, the initial default). Set this attribute to 1 if you want cursors to be held at the current position when the transaction ends. Doing so may impact the performance of your database operations. This attribute is not valid for SQL/DS. When you select Preserve, the driver returns SQL_CB_PRESERVE from SQLGetInfo (SQL_CURSOR_COMMIT_BEHAVIOR). But only Select statements and prepared Update or Delete . . . Where Current of Cursor statements are preserved when the transaction ends. All other prepared statements are closed and deleted. ═══ 3.7. DB2/2 Data Types ═══ The DB2/2 data types map to the standard ODBC data types as follows: DB2/2 ODBC Char SQL_CHAR Char() for Bit Data SQL_BINARY Date SQL_DATE Decimal SQL_DECIMAL Float SQL_DOUBLE Integer SQL INTEGER Long Varchar (1) SQL_LONGVARCHAR Long Varchar for Bit Data (1) SQL_LONGVARBINARY Smallint SQL_SMALLINT Time SQL_TIME Timestamp SQL_TIMESTAMP Varchar (2) SQL_VARCHAR Varchar (3) SQL_LONGVARCHAR Varchar() for Bit Data (2) SQL_VARBINARY Varchar() for Bit Data (3) SQL_LONGVARBINARY (1) Not supported for SQL/400 (2) For DB2/2, DB2/6000, and SQL/400; SQL/DS and MVS DB2 up to 254 (3) For SQL/DS and MVS DB2 over 254 Note: The Graphic, Vargraphic, and Long Vargraphic data types are not supported. ═══ 3.8. Isolation and Lock Levels Supported by DB2/2 ═══ DB2/2 supports isolation levels 0 (read uncommitted), 1 (read committed), and 2 (repeatable read). It supports record-level locking. ═══ 3.9. DB2/2 ODBC Conformance Level ═══ The DB2/2 driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. The driver supports the minimum SQL grammar. ═══ 3.10. Number of Connections and Statements Supported by DB2/2 ═══ The DB2/2 family of database systems supports a single connection and multiple statements per connection. ═══ 4. dBASE Driver ═══ The dBASE driver supports ■ dBASE III, dBASE IV and dBASE V files ■ Clipper files ■ FoxPro and FoxBASE tables and indexes Note: The dBASE driver for OS/2 does not support dBASE II. The dBASE driver executes the SQL statements directly on dBASE-compatible files. You do not need to own the dBASE product to access these files. The driver filename is IVDBFnn.DLL. ═══ 4.1. Configuring dBASE Data Sources ═══ To configure a dBASE data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV dBASEFile and click OK. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 3. Specify values as follows: Data Source Name: A string that identifies this dBASE data source configuration in ODBC.INI. Examples include "Accounting" or "dBASE Files." Description: An optional long description of a data source name. For example, "My Accounting Database" or "dBASE files in C:\ACCOUNTS." Database Directory: A path specification to the directory that contains the database files. If none is specified, the current working directory is used. The following values are optional: Create Type: The type of table or index to be created on a Create Table or Create Index statement. Select dBASE III, dBASE IV, dBASE V, Clipper, FoxBASE, FoxPro1, or FoxPro25. The default is dBASE V. Lock Compatibility: The locking scheme the driver uses when locking records. Select dBASE, Q+E, Q+EVirtual, Clipper, or Fox. The default is dBASE. These values determine locking support as follows: ■ dBASE specifies Borland-compatible locking. ■ Q+E specifies that locks be placed on the actual bytes occupied by the record. Only applications that use the dBASE driver can read and write to the database. Other applications are locked out of the table completely (they cannot even read other records). This locking is compatible with earlier versions of Q+E products. ■ Q+EVirtual specifies that locks be placed on bytes beyond the physical end-of-file. Q+EVirtual is the same as Q+E except that other applications can open the table and read the data. The advantage of using a Q+E locking scheme over dBASE locking is that, on Inserts and Updates, Q+E locks only individual index tags, while dBASE locks the entire index. ■ Clipper specifies Clipper-compatible locking. ■ Fox specifies FoxPro- and FoxBASE-compatible locking. If you are accessing a table with an application that uses the dBASE driver, your locking scheme does not have to match the Create Type. However, if you are accessing a table with two applications, and only one uses the dBASE driver, set your locking scheme to match the other application. For example, you do not have to set this value to Fox to work with a FoxPro table. But if you are using a FoxPro application simultaneously with an application using the dBASE driver on the same set of tables, set this value to Fox to ensure that your data does not get corrupted. Locking: The level of locking for the database file (FILE, RECORD, or NONE). FILE locks all of the records in the table. RECORD (the default) locks only the records affected by the statement. NONE offers the best performance but is intended only for single-user environments. File Open Cache: A numeric value to specify the maximum number of unused file opens to cache. For example, the value 4 specifies that when a user opens and closes four tables, the tables are not actually closed. The driver keeps them open so that if another query uses one of these tables, the driver does not have to perform another open, which is expensive. The advantage of file open caching is increased performance. The disadvantage is that a user who specifies file locking on open may get a locking conflict even though no one appears to have the file open. The default is 0, which means no file open caching. Cache Size: The number of 64K blocks the driver uses to cache database records. The higher the number, the better the performance. The maximum number of blocks you can set depends on the system memory available. If the cache size is greater than 0, when browsing backwards, you will not be able to see updates made by other users until you reexecute the Select statement. The default is 4. International Sort: A setting to indicate the order in which records are retrieved when you issue a Select statement with an Order By clause. Select this check box to use the international sort order as defined by your operating system. International sort order is case-insensitive (a precedes B); the sorting of accented characters is also affected (see your operating system documentation). Leave this box blank to use the ASCII sort order. ASCII sort order is case-sensitive, where uppercase letters precede lowercase letters (B precedes a). Use Long Names: A setting that enables the driver to use long filenames as table names. If you select this check box, the maximum table name length is 128 characters. Use Long Qualifiers: A setting that enables the driver to use long pathnames as table qualifiers. When you set this check box, pathnames can be up to 249 characters. The default length for pathnames is 128 characters. 4. Click Define to define the index attributes for your data files. See Defining Index Attributes for step-by-step instructions. 5. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. The translators that are listed in this dialog box are determined by the values listed in the ODBC Translators section of your ODBCINST.INI file. 6. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 4.2. Defining Index Attributes for dBASE ═══ Since dBASE lets you create index files that have different names than their corresponding data files, you must tell the driver what index files are associated with the dBASE file. The driver updates the indexes for you, which ensures that they match the records in the dBASE file. The driver also makes indexes available for optimizing queries. It is not necessary to mark production .MDX files or structured .CDX files as maintained, as the driver maintains them for you. However, you may wish to use this method to mark a tag as unique. To define the index files that are associated with a dBASE file, take the following steps: 1. Click Define in the dBASE setup dialog box, which you can access through the ODBC Administrator. The Define File dialog box appears. 2. Select a dBASE file and click OK to define the special indexes using the Define Table dialog box. 3. The upper section of the dialog box displays the directory name and filename that contains the data file. Under Windows, if this file is stored in the IBM PC character set, select the OEM to ANSI Translation box. 4. The lower section of the dialog box displays the index information for the data file. The Index File drop-down list lets you select any index file in the database directory. If the index file is in a different directory, you must provide the full pathname. Select the Maintain check box to associate this index file with your dBASE file. To specify that an index file is unique, select the Unique check box that appears at the right of the index filename. 5. If the selected index has an .MDX or .CDX extension, you cannot mark the index file as unique. Instead, you may mark the tags within the index as unique. To do so, select the tag name in the Tag drop-down list and select the Unique check box that appears at the right of the tag name. 6. Click OK to save this information, or press Cancel. ═══ 4.3. Connecting to dBASE Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to the ODBC.INI file. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for dBASE is DSN=DBASE FILES;LCK=NONE;IS=0 The following table gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN) String that identifies a dBASE data source configuration in ODBC.INI. Examples include "Accounting" or "dBASE Files." Database (DB) Directory in which the dBASE files are stored. CreateType (CT) CreateType={dBASE3 | dBASE4 | dBASE5 | Clipper | FoxBASE | FoxPro1 | FoxPro25}.Type of table or index to be created on a Create Table or Create Index statement. Options are dBASE2, dBASE3, dBASE4, dBASE5 (the initial default), Clipper, FoxBASE, FoxPro1, and FoxPro25. LockCompatibility (LCOMP) LockCompatibility= {Q+E | Q+EVirtual | dBASE | Clipper | Fox}.Locking schemes to be used in your dBASE tables. Options are Q+E, Q+EVirtual, dBASE, Clipper, and Fox. ■ LockCompatibility=Q+E specifies that locks be placed on the actual bytes occupied by the record. Only applications that use the dBASE driver can read and write to the database. Other applications are locked out of the table completely (they cannot even read other records). This locking is compatible with earlier versions of Q+E products. ■ LockCompatibility=Q+EVirtual specifies that locks be placed on bytes beyond the physical end-of-file. Q+EVirtual is the same as Q+E except that other applications can open the table and read the data. The advantage of using a Q+E locking scheme over dBASE locking is that, on Inserts and Updates, Q+E locks only individual index tags, while dBASE locks the entire index. ■ LockCompatibility=dBASE specifies Borland-compatible locking. This is the initial default. ■ LockCompatibility=Clipper specifies Clipper-compatible locking. ■ LockCompatibility=Fox specifies FoxPro- and FoxBASE-compatible locking. If you are accessing a table with an application that uses the dBASE driver, your locking scheme does not have to match the Create Type. However, if you are accessing a table with two applications, and only one uses the dBASE driver, set your locking scheme to match the other application. For example, you don't have to set LCOMP=Fox to work with a FoxPro table. But if you are using a FoxPro application simultaneously with an application using the dBASE driver on the same set of tables, set LCOMP=Fox to ensure that your data does not get corrupted. Locking (LCK) Locking={NONE | RECORD | FILE}. Level of locking for the database tables. Options are NONE, RECORD, and FILE. Locking=NONE offers the best performance but is intended only for single-user environments. Locking=RECORD locks only the records affected by the statement. This is the initial default. Locking=FILE locks all of the records in the table.FileOpenCache (FOC)The maximum number of unused file opens to cache. For example, when FileOpenCache=4, and a user opens and closes four files, the files are not actually closed. The driver keeps them open so that if another query uses one of these files, the driver does not have to perform another open, which is expensive. The advantage of using file open caching is increased performance. The disadvantage is that a user who tries to open the file exclusively may get a locking conflict even though no one appears to have the file open. The initial default is 0. CacheSize (CSZ) Number of 64K blocks the driver uses to cache database records. The higher the number, the better the performance. The maximum number of blocks you can set depends on the system memory available. If the cache size is greater than 0, when browsing backwards, you will not be able to see updates made by other users until you reexecute the Select statement. The default is 4. IntlSort (IS) IntlSort={0 | 1}. Number that specifies the order that records are retrieved when you issue a Select statement with an Order By clause. If IntlSort=1, the driver uses the international sort order as defined by your operating system. The sort is case-insensitive (a precedes B); the sorting of accented characters is also affected (see your operating system documentation). If IntlSort=0 (the initial default), the driver uses the ASCII sort order, where uppercase letters precede lowercase letters (B precedes a). ModifySQL (MS) ModifySQL={0 | 1}. Provided for backward compatibility with earlier versions of Q+E products. It determines whether the driver modifies SQL statements to conform to ODBC specifications or passes the SQL statement directly to dBASE. Specify ModifySQL=1 to have the driver modify the SQL statement to conform to ODBC specifications. Specify ModifySQL=0 to have the driver understand SQL dialects found in earlier drivers. The default is 1. Compatibility (COMP) Compatibility={0 | 1}. Provided for backward compatibility with earlier versions of Q+E products. Use Compatibility=DBASE for backward compatibility; use Compatibility=ANSI for portability. The default is ANSI. UltraSafeCommit (USF) UltraSafeCommit={0 | 1}. Specifies when the driver flushes the file cache. If UltraSafeCommit=1, the driver updates directory entries after each Commit. This decreases performance. If UltraSafeCommit=0 (the default) the driver updates the directory entry when the file is closed. In this case, a machine "crash" before closing the file causes newly inserted records to be lost. UseLongNames (ULN): UseLongNames={0 | 1}. Enables the driver to use long filenames as table names. The default is 0, do not use long filenames. When UseLongNames=1, the driver uses long filenames, and the maximum table name length is 128 characters. UseLongQualifiers (ULQ): UseLongQualifiers={0 | 1}. Enables the driver to use long pathnames as table qualifiers. The default is 0, do not use long pathnames. The default length for pathnames is 128 characters. When UseLongQualifiers=1, pathnames can be up to 249 characters. ═══ 4.4. dBASE Data Types ═══ The following table shows how dBASE data types map to the standard ODBC data types. These dBASE data types can be used in a Create Table statement. Note: A few products can create dBASE files with numbers that do not conform to the precision and scale of the Number column. For example, these products can store 100000 in a column declared as NUMBER(5,2). When this occurs, the dBASE driver displays error 1244, "Unsupported decimal format." To remedy this situation, multiply the nonconforming column by 1, which converts it to the Float data type. For example, SELECT BADCOL * 1 FROM BADFILE BADCOL * 1 is evaluated as an expression and is returned as a float value. dBASE ODBC Binary (1) SQL_LONGVARBINARY Char (2) SQL_CHAR Date (3) SQL_DATE Float (4) SQL_DECIMAL General (5) SQL_LONGVARBINARY Logical SQL_BIT Memo (3) SQL_LONGVARCHAR Numeric SQL_DECIMAL Picture (6) SQL_LONGVARBINARY 1. dBASE V only 2. 254 characters maximum (1024 for Clipper) 3. dBASE III, IV, FoxPro, FoxBASE, and Clipper 4. dBASE IV only 5. FoxPro 2.5 and dBASE V only 6. FoxPro, FoxBASE, and Clipper h2 res=26100.Column Names The maximum length of a column name is 10 characters. A column name can contain alphanumeric characters and the hyphen character (-). The first character must be a letter (a through z). ═══ 4.5. Select Statement for dBASE ═══ You use a SQL Select statement to specify the columns and records to be read. dBASE Select statements support all of the Select statement clauses. This topic describes the information that is specific to dBASE, which is ROWID. ROWID Pseudo-Column Each dBASE record contains a special column named ROWID. This field contains a unique number that indicates the record's sequence in the database. For example, a table that contains 50 records has ROWID values from 1 to 50 (if no records are marked deleted). You can use ROWID in Where and Select clauses. ROWID is particularly useful when you are updating records. You can retrieve the ROWID of the records in the database along with the other field values. For example, SELECT last_name, first_name, salary, rowid FROM emp Then you can use the ROWID of the record that you want to update to ensure that you are updating the correct record and no other. For example, UPDATE emp set salary = 40000 FROM emp WHERE rowid=21 The fastest way of updating a single row is to use a Where clause with the ROWID. You cannot update the ROWID column. ═══ 4.6. Create Index Statement for dBASE ═══ The type of index you create is determined by the value of the CreateType attribute, which you set in the setup dialog box or as a connection string option. The index can be ■ dBASE II or III (.NDX) ■ dBASE IV (.MDX) ■ Clipper (.NTX) ■ FoxBASE (.IDX) ■ FoxPro (.CDX) The syntax for creating an index is CREATE [UNIQUE] INDEX index_name ON base_table_name (field_name [ASC | DESC] [,field_name [ASC | DESC]] ...) index_name is the name of the index file. For FoxPro 2.5 and dBASE IV, this is a tag, which is required to identify the indexes in an index file. Each index for a table must have a unique name. UNIQUE means that the driver creates an ANSI-style unique index over the column and ensures uniqueness of the keys. Use of unique indexes improves performance. ANSI-style unique indexes are different from dBASE-style unique indexes. With ANSI-style unique indexes, you receive an error message when you try to insert a duplicate value into an indexed field. With dBASE-style unique indexes, you do not see an error message when you insert a duplicate value into an indexed field. This is because only one key is inserted in the index file. base_table_name is the name of the database file whose index is to be created. The .DBF extension is not required, the driver automatically adds it if it is not present. By default, dBASE IV index files are named base_table_name.MDX and FoxPro 2.5 indexes are named base_table_name.CDX. field_name is a name of a column in the dBASE table. You can substitute a valid dBASE-style index expression for the list of field names. ASC tells dBASE to create the index in ascending order. DESC tells dBASE to create the index in descending order. By default, indexes are created in ascending order. You cannot specify both ASC and DESC orders within a single Create Index statement. For example, the following statement is invalid: CREATE INDEX emp_i ON emp (last_name ASC, emp_id DESC) The following table shows the attributes of the different index files supported by the dBASE driver. For each type supported, it provides the following details: ■ Whether dBASE-style unique indexes are supported ■ Whether descending order is supported ■ The maximum size supported for key columns ■ The maximum size supported for the column specification in the Create Index statement ■ Whether production/structural indexes are supported ┌────────────────────────────────────────────────────────────────────────────┐ │Create dBASE DESC Max Size of Max Size of Production/ │ │Type/Ext. UNIQUE Key Column Column Spec. Structural │ │ Indexes │ │ │ │dBASE II No No 100 99 No │ │.NDX │ │ │ │dBASE III Yes No 100 219 No │ │.NDX │ │ │ │Clipper Yes Yes 250 255 No │ │.NTX │ │ │ │FoxBASE Yes No 100 219 No │ │.IDX* │ │ │ │dBASE IV and V Yes Yes 100 220 Yes │ │.MDX │ │ │ │FoxPro Yes Yes 240 255 No │ │2.5 .IDX** │ │ │ │FoxPro Yes Yes 240 255 Yes │ │2.5 .CDX │ └────────────────────────────────────────────────────────────────────────────┘ *These IDX indexes are also created as the default for FoxPro 1.0. **Compact IDX indexes have the same internal structure as a tag in a CDX file. These indexes can be created if the IDX extension is included with the index name in the Create Index statement. ═══ 4.7. Drop Index Statement for dBASE ═══ The syntax for dropping a dBASE index is as follows: DROP INDEX table_name.index_name table_name is the name of the dBASE file without the extension. For FoxPro 2.5 and dBASE IV, index_name is the tag. Otherwise, index_name is the name of the index file without the extension. To drop the index EMPHIRE.NDX, issue the following statement: DROP INDEX emp.emphire ═══ 4.8. Pack Statement for dBASE ═══ When records are deleted from a dBASE file, they are not removed from the file. Instead, they are marked as having been deleted. Also, when memo fields are updated, space may be wasted in the files. To remove the deleted records and free the unused space from updated memo fields, you must use the Pack statement. It has the following form: PACK filename filename is the name of the dBASE file to be packed. The .DBF extension is not required; the driver automatically adds the extension if it is not present. For example, PACK emp You cannot pack a file that is opened by another user, and you cannot use the Pack statement in manual commit mode. For the specified file, the Pack statement does the following: ■ Removes all deleted records from the file ■ Removes the entries for all deleted records from .CDX and .MDX files having the same name as the file ■ Compresses unused space in the memo (.DBT or .FPT) file ═══ 4.9. dBASE Locking ═══ With the dBASE driver, you can build and run applications that share dBASE database files on a network. Whenever more than one user is running an application that accesses a shared database file, the applications should lock the records that are being changed. Locking a record prevents other users from locking, updating, or deleting the record. Levels of Database Locking The dBASE driver supports three levels of database locking: NONE , RECORD, and FILE. You can set these levels in ■ The connection string (LCK=) ■ The setup dialog box No locking offers the best performance but is intended only for single-user environments. With record or file locking, the system locks the database tables during Insert, Update, Delete, or Select...For Update statements. The locks are released when the user commits the transaction. The locks prevent other users from modifying the locked objects, but they do not lock out readers. With record locking, only records affected by the statement are locked. Record locking provides better concurrency with other users who also want to modify the table. With file locking, all the records in the table are locked. File locking has lower overhead and may work better if records are modified infrequently, if records are modified primarily by one user, or if a large number of records are modified. Using Locks on Local Files If you use database locking and are accessing files locally (not on a network), run the DOS utility SHARE.EXE before running Windows. If you add SHARE.EXE to your AUTOEXEC.BAT file, it runs automatically each time you boot your computer. Limit on Number of Locks There is a limit on the number of locks that can be placed on a file. If you are accessing a dBASE file from a server, the limit depends on the server (see your server documentation). If you are accessing a dBASE file locally, the limit depends on the buffer space allocated when SHARE.EXE was loaded (see your DOS documentation). If you are exceeding the number of locks available, you may want to switch to file locking. How Transactions Affect Record Locks When an Update or Delete statement is executed, the driver locks the records affected by that statement. The locks are released after the driver commits the changes. Under manual commit mode, the locks are held until the application commits the transaction. Under autocommit mode, the locks are held until the statement is executed. When a Select...For Update statement is executed, the driver locks a record only when the record is fetched. If the record is updated, the driver holds the lock until the changes are committed. Otherwise, the lock is released when the next record is fetched. ═══ 4.10. Isolation and Lock Levels Supported by dBASE ═══ dBASE supports isolation level 1. It supports both file- and record-level locking. ═══ 4.11. dBASE ODBC Conformance Level ═══ The dBASE driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. The dBASE driver also supports backward and random fetching in SQLExtendedFetch. The driver supports the minimum SQL grammar. ═══ 4.12. Number of Connections and Statements Supported by dBASE ═══ dBASE supports multiple connections and multiple statements per connection. ═══ 5. INFORMIX 5 Driver ═══ The INFORMIX 5 driver supports multiple connections to the INFORMIX 5 database. The driver filename is IVINF5nn.DLL. ═══ 5.1. INFORMIX 5 System Requirements ═══ You need the following software: ■ INFORMIX-ESQL/C for OS/2 Version 5.01.SC1B1 or higher ■ INFORMIX-INET for OS/2 Version 5.01.SC1B1 or higher (if you are running on a client/server environment). ■ INFORMIX-SE Version 5.0x, 6.0 or INFORMIX-OnLine Version 5.0x, 6.0 ISQLI501.DLL along with other required DLLs must be in directories listed in the LIBPATH specification in your CONFIG.SYS file. The environment variable INFORMIXDIR must be set to the directory where you have installed the INFORMIX client. For example: SET INFORMIXDIR=C:\INFORMIX This line can be added to your CONFIG.SYS file. ═══ 5.2. Configuring INFORMIX Data Sources ═══ To configure an INFORMIX 5 data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV INFORMIX5 and click OK. 3. If you are configuring an existing data source, select the data source name and click Setup. 4. The setup dialog box appears. 5. Specify values as follows: Data Source Name: A string that identifies this INFORMIX data source configuration in ODBC.INI. Examples include "Accounting" or "INFORMIX-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "INFORMIX 5 files on Server number 1." Database Name: The name of the database to which you want to connect by default. The following values are optional: Database List: The list of databases that will be displayed in the logon dialog box. If Get DB List From Informix is set to 1, the l ist of databases that will be displayed in the logon dialog box is created from the database list returned for INFORMIX at logon time. When you specify databases, separate the names with commas. Default User Name: The name of the user as specified on the INFORMIX 5 server. Host Name: The name of the machine on which the INFORMIX 5 server resides. Service Name: The name of the service as it appears on the host machine. This service is assigned by the system administrator. The name you specify is displayed in the INFORMIX Server Options dialog box. Protocol Type: The protocol used to communicate with the server. Specify one or more values; separate the names with commas. Values can be FTP, IPX, LANMAN, TCP-IP. This list is displayed in the Logon Options dialog box. Yield Proc: (This attribute is not available in OS/2.) A numeric value that determines whether you can work in other applications when INFORMIX 5 is busy. Cursor Behavior: Select Preserve if you want cursors to be held at the current position when the transaction ends. Otherwise, leave this set to Close. Selecting Preserve may impact the performance of your database operations. Enable Scrollable Cursors: A value that determines whether the driver can use scrollable cursors. The initial default value is 0 (no use of scrollable cursors). The INFORMIX driver can use scollable cursors only if there are no long columns (SQL_LONGVARCHAR or SQL_LONGVARBINARY) in a Select list. If you set this option to use scrollable cursors (EnableScrollableCursors=1), you must not include long columns in the Select list. Get DB List from Informix: A value that determines whether the driver requests the database list to be returned from the INFORMIX server or from the database list that the user entered at driver setup. When set to 1, the default, the driver requests the database list from the INFORMIX server. When set to 0, it uses the list entered by the user at driver setup. 6. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. 7. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 5.3. Connecting to INFORMIX Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. In the logon dialog box, do the following: 1. Type the name of the database you want to access or select the name from the Database Name drop-down list. This list displays names returned from the INFORMIX server. Or, if you set the Get DB List from Informix option to 0 in the Setup dialog box, this list displays the names you specified. 2. Type the name of the server (host name) on which INFORMIX 5 resides. 3. If required, type your user name as specified on the INFORMIX 5 server. 4. If required, type your password. 5. Optionally, click Options to display the INFORMIX Server Options dialog box. This dialog box enables you to change the Service Name and Protocol Type that you specified in the setup dialog box. Click OK to accept any changes you make. 6. Click OK to complete the logon and to update these values in ODBC.INI. ═══ 5.4. Connecting to INFORMIX Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for INFORMIX 5 is DSN=INFORMIX TABLES;DB=PAYROLL The following table gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN) String that identifies an INFORMIX data source configuration in ODBC.INI. Examples include "Accounting" or "INFORMIX-Serv1." Database (DB) Name of the database to which you want to connect. HostName (HOST) Name of the machine on which the INFORMIX 5 server resides. LogonID (UID) Your user name as specified on the INFORMIX 5 server. Password (PWD) A password. Service (SERV) Name of the service as it appears on the host machine. This service is assigned by the system administrator. Protocol (PRO) Protocol={FTP | IPX | LANMAN | TCP-IP}. Protocol used to communicate with the server. Values can be FTP, IPX, LANMAN, or TCP-IP. You can specify one or more values; separate the names with commas. YieldProc (YLD) (This attribute is not available in OS/2.) A value that determines if you can work in other applications when INFORMIX 5 is busy. CursorBehavior (CB) CursorBehavior={0 | 1}. Determines whether cursors will be preserved or closed at the end of each transaction. Valid values are 1 (preserve) and 0 (close, the initial default). Set this attribute to 1 if you want cursors to be held at the current position when the transaction ends. The value CursorBehavior=1 may impact the performance of your database operations. EnableScrollableCursors (ESC). EnableScrollable Cursors={0 | 1}. Determines whether the driver can use scrollable cursors. The initial default value is 0 (no use of scrollable cursors). The INFORMIX driver can use scollable cursors only if there are no long columns (SQL_LONGVARCHAR or SQL_LONGVARBINARY) in a Select list. If you set this option to use scrollable cursors (EnableScrollableCursors=1), you must not include long columns in the Select list. GetDBListfromInformix (GDBLFI) GetDBListFromInformix={0 | 1}. Determines whether the driver requests the database list to be returned from the INFORMIX server or from the database list that the user entered at driver setup. When set to 1, the default, the driver requests the database list from the INFORMIX server. When set to 0, it uses the list entered by the user at driver setup. ═══ 5.5. INFORMIX Data Types ═══ The INFORMIX 5 data types map to the standard ODBC data types as follows: INFORMIX 5 ODBC Byte* SQL_LONGVARBINARY Char SQL_CHAR Date SQL_DATE Datetime year to fraction(5) SQL_TIMESTAMP Decimal SQL_DECIMAL Float SQL_DOUBLE Integer SQL_INTEGER Money SQL_DECIMAL Serial SQL_INTEGER Smallfloat SQL_REAL Smallint SQL_SMALLINT Text* SQL_LONGVARCHAR Varchar* SQL_VARCHAR * Not supported for Standard Engine Databases. Note: The Interval data type is not supported. Existing columns of this type are mapped to the ODBC SQL_CHAR data type. ═══ 5.6. Isolation and Lock Levels Supported by INFORMIX ═══ INFORMIX 5 supports isolation levels 0 (read uncommitted), 1 (read committed), and 3 (serializable). The default is 1. INFORMIX 5 also supports an alternative isolation level 1, called cursor stability. Your ODBC application can use this isolation level by calling SQLSetConnectOption (1040,1). Additionally, if transaction logging has not been enabled for your database, then transactions are not supported by the driver (the driver is always in auto-commit mode). INFORMIX 5 supports page-level locking. ═══ 5.7. INFORMIX ODBC Conformance Level ═══ The INFORMIX 5 driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. In addition, the following Level 2 functions are supported: ■ SQLBrowseConnect ■ SQLProcedures The driver supports the core SQL grammar. ═══ 5.8. Number of Connections and Statements Supported ═══ The INFORMIX 5 database system supports multiple connections and multiple statements per connection. ═══ 6. INGRES 6.4/04 Driver ═══ The INGRES driver supports INGRES Release 6.4/04 and higher. The driver filename is IVING4nn.DLL. ═══ 6.1. INGRES System Requirements ═══ To access INGRES databases from your client workstation you must have the INGRES/Net or INGRES/Star product installed on both your client and server nodes. To access INGRES, you must have Networking for OS/2 Release 6.4/04 (net.os2/02). All required DLLs must reside in directories that are listed in the LIBPATH specification in your CONFIG.SYS file. ═══ 6.2. Configuring INGRES Data Sources ═══ To configure an INGRES data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV INGRES 6.4/04 and click OK. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 3. Specify values as follows: Data Source Name: A string that identifies this INGRES data source configuration in ODBC.INI. Examples include "Accounting" or "INGRES-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "INGRES on Server number 1." Server Name: The name of the virtual node that you defined using the INGRES NETU utility. This virtual node tells INGRES which system to call, how to call it, and the user's name and password. Database Name: The name of the database to which you want to connect by default. The following values are optional: Server List: The list of servers (virtual nodes) that will be available in the Logon dialog box. Separate the server names with commas. Database List: The list of databases that will be available in the logon dialog box. Separate the names with commas. Default User Name: The default user name used to connect to your INGRES database. A user name is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the logon dialog box. Options: Any flag allowed on the INGRES SQL command line. Some examples are ■ -l (locks the database exclusively) ■ -u (logs on as username) ■ +w or -w (waits/doesn't wait for the database if a user has already opened it exclusively) ■ +U or -U (enables/disables user updating of the system tables and locks the database exclusively) ■ +Y or -Y (enables/disables user updating of the system tables but does not lock the database exclusively) Yield Proc: This attribute is not available under OS/2. A numeric value that determines whether you can work in other applications when INGRES is busy. Repeated Cache Size A number that determines whether all Update and Insert statements are to be executed as repeated statements. This attribute improves the performance of applications that repeat the same SQL statement. When set to 0, the initial default, no statements are repeated. The recommended setting for this attribute is 100 (RepeatedCacheSize=100). To repeat a single statement rather than all statements, use the INGRES REPEATED syntax. Repeated Selects A number that determines whether the driver optimizes Select statements or executes them as repeated queries. When set to 0, the initial default, the driver executes all Select statements as it did in previous versions of the product. When set to 1, the driver optimizes Select statements that return only one result row. When set to 2, the driver executes all Select statements as repeated queries. If this attribute is set to 1 or 2, the RepeatedCacheSize attribute must be set to greater than zero. Setting this option to 1 or 2 has no effect on Select statements containing a For Update clause. These settings also limit the driver to one active statement and one active connection. Value Replacement for Repeated Selects A number that determines whether the driver substitutes parameters for hardcoded values in repeated statements. This option is convenient in applications that do not use dynamic parameters. When set to 0, the initial default, the driver does not substitute parameters. When set to 1, the driver substitutes parameters for hardcoded values and the RepeatedCacheSize attribute must be greater than zero or the REPEATED INGRES keyword must be used. This option has no effect upon Select statements that contain a For Update clause that requires a cursor or upon statements that already use parameter markers. This attribute supports only a subset of standard ODBC SQL grammer. It is intended for performance and does not utilize a full SQL parser. For example, subselects are not supported and use of "is NULL" for columns other than character columns is not supported. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 6.3. Connecting to INGRES Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. In the logon dialog box, do the following: 1. Type the server name of the computer containing the INGRES database you want to access or select the name from the Server Name drop-down list, which displays the server names you specified in the setup dialog box. Server Name must be a valid virtual node that has been added using the INGRES NETU utility. 2. Type the name of the database to which you want to connect or select the name from the Database Name drop-down list, which displays the names you specified in the setup dialog box. 3. If required, type your user name. 4. Type any options for the connection. The options can be any flags allowed on the INGRES SQL command line. 5. Click OK to log on to INGRES and to update the values in ODBC.INI. ═══ 6.4. Connecting to INGRES Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for INGRES is DSN=INGRES TABLES;SRVR=IVSERV;DB=PAYROLL;UID=JOHN The following list gives the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN)A string that identifies an INGRES data source configuration in ODBC.INI. Examples include "Accounting" or "INGRES-Serv1." ServerName (SRVR) The name of the virtual node that you defined using the INGRES NETU utility. This virtual node tells INGRES which system to call, how to call it, and the user's name and password. Database (DB) The name of the database to which you want to connect. LogonID (UID) The default logon ID (user name) used to connect to your INGRES database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. Options (OPTS) The flags allowed on the INGRES SQL command line. Some examples are ■ -l (locks the database exclusively) ■ -u (logs on as username) ■ -w or -w (waits/doesn't wait for the database if someone has already opened it exclusively) ■ +U or -U (enables/disables user updating system tables and locks the database exclusively) ■ +Y or -Y (enables/disables user updating system tables but does not lock the database exclusively) YieldProc (YLD) A value that determines if you can work in other applications when INGRES is busy. This attribute is not available under OS/2. RepeatedCacheSize (RCS) A number that determines whether all Update and Insert statements are to be executed as repeated statements. This attribute improves the performance of applications that repeat the same SQL statement. When set to 0, the initial default, no statements are repeated. The recommended setting for this attribute is 100 (RepeatedCacheSize=100). To repeat a single statement rather than all statements, use the INGRES REPEATED syntax. RepeatedSelects (RS) RepeatedSelects={0 | 1 | 2}. A number that determines whether the driver optimizes Select statements or executes them as repeated queries. When set to 0, the initial default, the driver executes all Select statements as it did in previous versions of the product. When set to 1, the driver optimizes Select statements that return only one result row. When set to 2, the driver executes all Select statements as repeated queries. If this attribute is set to 1 or 2, the RepeatedCacheSize attribute must be set to greater than zero. Setting this option to 1 or 2 has no effect on Select statements containing a For Update clause. These settings also limit the driver to one active statement and one active connection. ValueReplacement (VR) ValueReplacement={0 | 1}. A number that determines whether the driver substitutes parameters for hardcoded values in repeated statements. This option is convenient in applications that do not use dynamic parameters. When set to 0, the initial default, the driver does not substitute parameters. When set to 1, the driver substitutes parameters for hardcoded values and the RepeatedCacheSize attribute must be greater than zero or the REPEATED INGRES keyword must be used. This option has no effect upon Select statements that contain a For Update clause that requires a cursor or upon statements that already use parameter markers. This attribute supports only a subset of standard ODBC SQL grammer. It is intended for performance and does not utilize a full SQL parser. For example, subselects are not supported and use of "is NULL" for columns other than character columns is not supported. ═══ 6.5. INGRES Data Types ═══ INGRES data types map to the standard ODBC data types as follows: INGRES ODBC Char SQL_CHAR Date SQL_TIMESTAMP Float SQL_DOUBLE Float4 SQL_REAL Integer SQL_INTEGER Integer1 SQL_TINYINT Money SQL_DECIMAL Smallint SQL_SMALLINT Varchar SQL_VARCHAR Note that INGRES date values do not directly map to the ODBC type SQL_TIMESTAMP. If interval data is placed in date columns, then the driver raises an error when attempting to read the value. ═══ 6.6. Isolation and Lock Levels Supported by INGRES ═══ INGRES supports isolation levels 1 (read committed, the default) and 3 (serializable). INGRES supports page-level locking. ═══ 6.7. INGRES ODBC Conformance Level ═══ The INGRES driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. In addition, the SQLBrowseConnect Level 2 function is supported. The driver supports the minimum SQL grammar. ═══ 6.8. Number of Connections and Statements Supported by INGRES ═══ The INGRES database system supports multiple connections and multiple statements per connection. Note that if you set the RepeatedSelects connection string attribute to 1 or 2, the driver is limited to one active statement and one active connection. ═══ 7. MicroDecisionware Driver ═══ The MicroDecisionware driver supports the IBM Database 2 (DB2) and InfoHub (formerly Teradata) database systems. The MicroDecisionware Database Gateway makes an InfoHub database resemble a DB2 database. These topics refer to all databases accessed through the MicroDecisionware Gateway as DB2 databases. Catalog functions are not currently supported for InfoHub. The driver filename is IVMDInn.DLL. System Requirements Configuring Data Sources Connecting to DB2 Using a Logon Dialog Box Connecting to DB2 Using a Connection String Data Types Isolation and Lock Levels Supported ODBC Conformance Level Number of Connections and Statements Supported ═══ 7.1. System Requirements ═══ You must have the appropriate DB-Library and Net-Library installed to gain access to MDI DB2 databases. Your database must support catalog stored procedures. For OS/2, you must have Net-Library version 2.0 (32-bit) or higher, which is required to set up your network environment. Contact a Sybase SQL Server vendor for more information on Net-Library. Also, you must have DB-Library version 4.6 (32-bit) or higher, available from Sybase. All DLLs from Net-Library and DB-Library must be in directories listed in the LIBPATH environment variable in your CONFIG.SYS file. ═══ 7.2. Configuring Data Sources ═══ To configure a MicroDecisionware DB2 data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV MDI and click OK. 3. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 4. Specify values as follows: Data Source Name: A string that identifies this MicroDecisionware data source configuration in ODBC.INI. Examples include "Accounting" or "MicroDecisionware Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "MicroDecisionware on Server number 1." Server Name: The name of the OS/2 server running the MicroDecisionware Database Gateway. This value will appear as the initial default in the logon dialog box. The following values are optional: Server List: The list of servers that will appear in the logon dialog box. Separate the server names with commas. Default Authorization ID: The default authorization ID used to connect to your DB2 database. This ID is case-sensitive. An authorization ID is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the logon dialog box. Groups: A list of the groups to which you belong, used to determine which tables you can access. Separate each group name with a comma. Alternatively, enter the word ALL so that you can see all table names even if you cannot access the table. Application Name: The name used to identify your application. Workstation ID: The workstation ID used by the MicroDecisionware Database Gateway. Cancel Behavior: A value to specify how a previously executed statement should be canceled. 1. 0 fetches all remaining records if the statement was a Select. 2. 1 cancels the statement by calling dbcancel. This is the default. 3. {bmc bullet.bmp}2 closes the connection to the server for the statement. Yield Proc: A numeric value that determines whether you can work in other applications when the gateway is busy. This attribute is useful to users of ODBC applications. Valid values are: 1. 0 (peek and dispatch), which causes the driver to check the Windows message queue and send any messages to the appropriate Windows application. 2. 1 (no yielding, the default), which does not let you work in other applications. 3. 3 (dispatch via Windows Yield function), which turns control over to the Windows kernel. The Windows kernel checks the message queue and sends any messages to the appropriate application window. It is recommended that you use the value 1. The values 0 and 3 do not work with all Windows applications. Owner of Catalog Tables: The owner of views of the system tables. The tables themselves are owned by SYSIBM. If you have read access to the system tables, this option does not have to be changed . If you do not have read access, the database administrator must create a view of the system tables in another account and give you permission to use the view. Enter the authorization ID for the account that owns the views for the system tables. Tablespace to Create Tables in: The name of the tablespace in which new tables are stored. For example, DSNDB04. DB2 Site Allows User to Log On More Than Once: A check box that specifies that your site lets a user log on to DB2 more than once. Selecting this check box may improve performance. NETAPI.DLL Library Available: A check box that specifies that the driver can use NETAPI.DLL to get the name of your workstation. Most major PC networks support this feature. If your network does not support this capability, deselect this option. If you supply a workstation ID, this setting is ignored. Use Catalog Stored Procedures: A check box that specifies whether the driver uses Catalog Stored Procedures or direct SQL to obtain system catalog information. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. The translators that are listed in this dialog box are determined by the values listed in the ODBC Translators section of your ODBCINST.INI file. 5Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 7.3. Connecting to DB2 Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. In the logon dialog box, do the following: 1. Type the name of the OS/2 server running the Database Gateway you wish to access or select the name from the Gateway Server box, which displays the server names specified in the Setup dialog box. 2. If required, type your Authorization ID (case-sensitive). 3. If required, type your password for the system (case-sensitive). 4. Click OK to complete the logon and to update the values in ODBC.INI. ═══ 7.4. Connecting to DB2 Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI . These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value [;attribute=value]...] An example of a connection string for the MicroDecisionware Database Gateway is DSN=DB2 VIA MDI;SRVR=IVSRVR;UID=JOHN;PWD=XYZZY;APP=Q4 The following list gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. DataSourceName (DSN) A string that identifies a MicroDecisionware data source configuration in ODBC.INI. Examples include "Accounting" or "MicroDecisionware Serv1." ServerName (SRVR) The name of the OS/2 server running the MicroDecisionware Database Gateway. LogonID (UID) The default logon ID (authorization ID) used to connect to your DB2 database. This ID is case-sensitive. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. Password (PWD) A case-sensitive password. Groups (GRP) A list of the group to which you belong, used to determine which tables you can access. Set Groups to the names of any groups to which you belong; separate each name with a comma. Groups=ALL lets your application see all table names even if you cannot access the table. ApplicationName (APP) The name used to identify your application. WorkstationID (WKID) The workstation ID used by the MicroDecisionware Database Gateway. Cancel (CAN) Cancel={0 | 1 | 2}. A value that specifies how a previously executed statement should be canceled. {bmc bullet.bmp}Cancel=0 fetches all of the remaining records if the statement was a Select. {bmc bullet.bmp}Cancel=1 cancels the statement by calling dbcancel. Set Cancel=1 if dbcancel is supported in your client/server configuration. This is the initial default. {bmc bullet.bmp}Cancel=2 closes the connection to the server for the statement. Set Cancel=2 only if dbcancel is not supported for your configuration and the performance of fetching all remaining records is unacceptable. Sysibm (SI) The authorization ID for the system tables. The initial default is SYSIBM. CreateDB (CDB) The name of the tablespace in which new tables are stored. For example, CreateDB=DSNDB04. MultipleLogon (MULT) MultipleLogon={0 | 1}. A value that specifies whether the DB2 site lets users log on more than once. Set this attribute to 1 if this is the case; 0 otherwise. Netapi (NAPI) Netapi={0 | 1}. A value that specifies whether NETAPI.DLL is available. Most major PC networks support this feature. Netapi=0 indicates it is not available; Netapi=1 indicates it is available. If you supply a value for the attribute WorkstationID, this attribute is ignored. YieldProc (YLD) This option is not available in OS/2. ModifySQL (MS) ModifySQL={0 | 1}. This attribute is provided for backward compatibility. Specify ModifySQL=0 to have the driver understand SQL dialects found in earlier drivers. Specify ModifySQL=1 (the default) to have the driver modify the SQL statement to conform to ODBC specifications. UseCatalogStored Procedures (UCSP) UseCatalogStored Procedures={0 | 1}. This attribute determines whether the driver uses Catalog Stored Procedures or direct SQL to obtain system catalog information. If set to 0, the driver uses direct SQL. The initial default is 0. ProfileName (PN) For InfoHub only. This attribute is the InfoHub profile name. If you specify a name, the driver issues a "Use profilename" statement at data source connect time. ═══ 7.5. Data Types ═══ The DB2 data types are mapped to the standard ODBC data types as follows: DB2 ODBC Char SQL_CHAR Char() For Bit Data SQL_BINARY Date SQL_DATE Decimal SQL_DECIMAL Float SQL_DOUBLE Integer SQL_INTEGER Long Varchar SQL_LONGVARCHAR Long Varchar For Bit Data SQL_LONGVARBINARY Smallint SQL_SMALLINT Time SQL_TIME Timestamp SQL_TIMESTAMP Varchar SQL_VARCHAR Varchar() For Bit Data SQL_VARBINARY Note: The Graphic, Vargraphic, and Long Vargraphic data types are not supported. If you create DB2 tables using these types, you will not be able to read the records in those tables. ═══ 7.6. Isolation and Lock Levels Supported ═══ DB2 supports isolation levels 1 (repeatable read) and 2 (read committed, the default). DB2 supports page-level locking. ═══ 7.7. ODBC Conformance Level ═══ The MicroDecisionware driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. The driver supports the minimum SQL grammar. ═══ 7.8. Number of Connections and Statements Supported ═══ If your site does not allow multiple connections, the MicroDecisionware gateway supports a single connection and one statement per connection. If your site allows multiple connections and SQL_AUTOCOMMIT is 1, the MicroDecisionware gateway supports multiple connections and multiple statements per connection. If your site allows multiple connections and SQL_AUTOCOMMIT is 0, the MicroDecisionware gateway supports multiple connections and one statement per connection. ═══ 8. Oracle Drivers ═══ The Oracle driver supports the Oracle 7 database system. Two separate Oracle drivers support the Oracle 6 and Oracle 7 database systems. The driver filename for Oracle 6 is IVORAnn.DLL, and for Oracle 7, IVOR7nn.DLL. ═══ 8.1. Oracle System Requirements ═══ The Oracle SQL*Net product is required to access remote Oracle databases. The following line must be included in your CONFIG.SYS file: SET CONFIG=C:\ORACLE\CONFIG.ORA Oracle 6 requires ORA6O32.DLL. Oracle 7 requires ORA7O32.DLL. These 32-bit DLLs are supplied by Oracle along with other required DLLs. All the DLLs must be in directories that are included in the LIBPATH specification in your CONFIG.SYS file. ═══ 8.2. Configuring Oracle Data Sources ═══ To configure an Oracle data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV Oracle or INTERSOLV Oracle7 and click OK. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 3. Specify values as follows: Data Source Name: A string that identifies this Oracle data source configuration in ODBC.INI. Examples include "Accounting" or "Oracle-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "Oracle on Server number 1." Server Name: The SQL*Net connection string designating the server and database to be accessed. The information required varies depending on the SQL*Net driver you are using. The section "Connecting to Oracle Using a Connection String" gives the format of the SQL*Net connection string. The following values are optional: Server List: The list of SQL*Net connection strings that will appear in the logon dialog box. Separate the strings with commas. If the SQL*Net connection string contains a comma, enclose it in quotation marks; for example, "Serv,1", "Serv,2", "Serv,3." Default User Name: The default user name used to connect to your Oracle database. A default user name is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the logon dialog box or connection string. Lock Time Out: A value that specifies whether Oracle should wait for a lock to be freed before raising an error when processing a Select...For Update Of statement. Values can be -1 (wait forever) or 0 (don't wait). The default is -1. Array Size: The number of bytes the driver uses for fetching multiple rows. Values can be 0 to 65536; the default is 60000. Larger values increase throughput by reducing the number of times the driver fetches data across the network. Smaller values increase response time, as there is less of a delay waiting for the server to transmit data. Catalog Comments: Check this box if you want to retrieve the contents of the COMMENTS column in your Oracle tables. Doing so may impact the performance of your queries. 4. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. 5. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 8.3. Connecting to Oracle Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. In the logon dialog box, do the following: 1. Type the SQL*Net connection string of the computer containing the Oracle database tables you want to access or select the string from the Server Name drop-down list, which displays the names you specified in the setup dialog box. 2. If required, type your Oracle user name. 3. If required, type your Oracle password. 4. Click OK to log on to the Oracle database installed on the server you specified and to update the values in ODBC.INI. ═══ 8.4. Connecting to Oracle Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for Oracle is DSN=Accounting;SRVR=X:IVSRVR;UID=JOHN;PWD=XYZZY If the server name contains a semicolon, enclose it in quotation marks: DSN=Accounting;SRVR="X:IV;SRVR";UID=JOHN;PWD=XYZZY The following table gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. DataSourceName (DSN) A string that identifies an Oracle data source configuration in ODBC.INI. Examples include "Accounting" or "Oracle-Serv1." LogonID (UID) The logon ID (user name) that the application uses to connect to your Oracle database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. Password (PWD) Your password. LockTimeOut (LTO) A value that specifies whether Oracle should wait for a lock to be freed before raising an error when processing a Select...For Update Of statement. Values can be -1 (wait forever, the initial default) or 0 (don't wait). ArraySize (AS) The number of bytes the driver uses for fetching multiple rows. Values can be 0 to 65536. The initial default is 60000. Larger values increase throughput by reducing the number of times the driver fetches data across the network. Smaller values increase response time, as there is less of a delay waiting for the server to transmit data. ServerName (SRVR) The SQL*Net connection string designating the server and database to be accessed. The information required varies depending on the SQL*Net driver you are using. The SQL*Net connection string has the following form: driver_prefix:computer_name[:sid] driver_prefix is a letter identifying the network protocol being used. The driver prefix can be as follows: P (named pipes), X (SPX), B (NetBIOS), T (TCP/IP), D (DECNet), A (Oracle Async), or AT ( AppleTalk). Check your Oracle documentation for other protocols. computer_name is the name of the Oracle Listener on your network. sid is the Oracle System Identifier and refers to the instance of Oracle running on the host. This item is required when connecting to systems that support more than one instance of an Oracle database. If the SQL*Net connection string contains semicolons, enclose it in quotation marks. See your SQL*Net documentation for more information. CatalogComments (CC) CatalogComments={0 | 1}. A value that specifies whether the driver returns the contents of the COMMENTS column for catalog functions. CatalogComments=1 returns COMMENTS. Retrieving the COMMENTS column may reduce the performance of your data catalog operations. CatalogComments=0 does not return COMMENTS (the initial default). ═══ 8.5. Oracle 6 Data Types ═══ The Oracle 6 data types are mapped to the standard ODBC data types as follows: Oracle 6 ODBC Char SQL_VARCHAR Date SQL_TIMESTAMP Long SQL_LONGVARCHAR Long Raw SQL_LONGVARBINARY Number SQL_DOUBLE Number(p,s) SQL_DECIMAL Raw SQL_VARBINARY ═══ 8.6. Oracle 7 Data Types ═══ The Oracle 7 data types are mapped to the standard ODBC data types as follows: Oracle 7 ODBC Char SQL_CHAR Date SQL_TIMESTAMP Long SQL_LONGVARCHAR Long Raw SQL_LONGVARBINARY Number SQL_DOUBLE Number(p,s) SQL_DECIMAL Raw SQL_VARBINARY Varchar2 SQL_VARCHAR ═══ 8.7. Isolation and Lock Levels Supported ═══ Oracle 6 and 7 both support isolation level 2 (repeatable read) only. Both versions support record-level locking. ═══ 8.8. ODBC Conformance Level ═══ Both Oracle drivers supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. The Oracle 6 driver also supports the Level 2 function SQLBrowseConnect. The Oracle 7 driver also supports the following Level 2 functions: ■ SQLBrowseConnect ■ SQLProcedures ■ SQLPrimaryKeys Both drivers support the core SQL grammar. ═══ 8.9. Number of Connections and Statements Supported ═══ Oracle 6 and Oracle 7 support multiple connections and multiple statements per connection. ═══ 9. SQL Server Driver ═══ The SQL Server driver supports the SQL Server database system available from Microsoft and Sybase, Inc. It also supports the Sybase Net gateway to DB2. The driver filename is IVSSnn.DLL. ═══ 9.1. SQL Server System Requirements ═══ You must install the Sybase Open Client-Library and the appropriate Sybase Net-Library to gain access to Microsoft SQL Server or Sybase SQL Server databases. SYBPING is a tool that is provided to test connectivity from your client workstation to the database server (servers that are added through SQLEdit). Use this tool to test your connection. SQLEdit is a tool that allows you to define servers and adds them to SQL.INI. Set the environment variable SYBASE to the directory where you installed the System 10 client. For example, set SYBASE=C:\SQL10. You set this environment variable in the config.sys file. ═══ 9.2. Configuring SQL Server Data Sources ═══ To configure a SQL Server data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV SQLServer and click OK. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 3. Specify values as follows: Data Source Name: A string that identifies this SQL Server data source configuration in ODBC.INI. Examples include "Accounting" or "SQL Server-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "SQL Server on Server number 1." Server Name: The name of the server that contains the desired database. Database Name: The name of the database to which you want to connect by default. If you do not specify a value, the default database defined by SQL Server is used. The following values are optional: Server List: A comma-separated list of servers that will appear in the logon dialog box. Database List: The databases that will be available in the SQL Server Logon Options dialog box. Separate the names with commas. Default Logon ID: The default logon ID used to connect to your SQL Server database. This ID is case-sensitive. A logon ID is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the logon dialog box or connection string. Language: The national language to be used by the client. The default is English. Application Name: The name SQL Server uses to identify your application. Workstation ID: The workstation ID used by the client. Cursor Cache Size: The number of cursors the cursor cache can hold. The driver creates a cache of statements; each statement represents an open connection to SQL Server. The cursor cache increases performance but uses database resources. The default is 1 (one cursor). Yield Proc: (This attribute is not available in OS/2.) A numeric value that determines whether you can work in other applications when SQL Server is busy. Character Conversion: This value controls the character set conversion between SQL Server (version 4.8 or later) and a client application. If you omit this value, no character conversion takes place on your server. Common values include iso-1 for ISO-8859-1, cp850 for Code Page 850, roman8 for Roman8 character set, and SJIS for a Japanese character set. See your SQL Server documentation for a complete list of values. Cancel Behavior: A value that specifies how a previously executed statement should be canceled. Valid values are ■ 0 fetches all of the remaining records if the statement was a Select. ■ 1 cancels the statement by calling dbcancel. This is the default and should be used if dbcancel is supported in your client/server configuration. ■ 2 closes the connection to the server for the statement. Use this value only if dbcancel is not supported for your configuration and the performance of fetching all remaining records is unacceptable. Using Gateway: Select this check box if you are using Sybase Net-Gateway to access a DB2 database with this data source. NETAPI.DLL Library Available: The driver uses NETAPI.DLL to get the name of your workstation. Most major PC networks support this feature. If your network supports this capability, select this option. If you supply a workstation ID, this field is ignored. Two-Phase Commit: This check box, when selected, enables you to have two active statements within a transaction, using the SQL Server two-phase commit services. The active statements may deadlock if they reference the same SQL Server table. 4. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. 5. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 9.3. Connecting to SQL Server Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. For SQL Server, the dialog box is as follows: In this dialog box, do the following: 1. Type the name of the server containing the SQL Server database tables you want to access (case-sensitive) or select the name from the Server Name drop-down list, which displays the server names you specified in the setup dialog box. 2. If required, type your case-sensitive login ID. 3. If required, type your case-sensitive password for the system. 4. Optionally, click Options to display the SQL Server Logon Options dialog box and specify the initial SQL Server database to connect to and the name of your workstation. 5. Click OK to log on to the SQL Server database installed on the server you specified and to update the values in ODBC.INI. ═══ 9.4. Connecting to SQL Server Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for SQL Server is DSN=Accounting;DB=PAYROLL;UID=JOHN;PWD=XYZZY The following table gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. DataSourceName (DSN) A string that identifies a SQL Server data source configuration in ODBC.INI. Examples include "Accounting" or "SQL Server-Serv1." ServerName (SRVR) The name of the server containing the SQL Server tables you want to access. Database (DB) The name of the database to which you want to connect. LogonID (UID) The case-sensitive logon ID used to connect to your SQL Server database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. Password (PWD) A case-sensitive password. Language (LANG) The national language to be used by the client. The initial default is English. ApplicationName (APP) The name SQL Server uses to identify your application. WorkstationID (WKID) The workstation ID used by the client. CursorCacheSize (CCS) The number of cursors the cursor cache can hold. The driver creates a cache of statements; each statement represents an open connection to SQL Server. The cursor cache increases performance but uses database resources. The initial default is 1. YieldProc (YLD) (This attribute not available for OS/2.) A numeric value that determines if you can work in other applications when SQL Server is busy. CharConv (CC) A value that controls the character set conversion between SQL Server (version 4.8 or later) and a client application. Common values include iso-1 for ISO-8859-1, cp850 for Code Page 850, roman8 for the Roman8 character set, and SJIS for a Japanese character set. See your SQL Server documentation for a complete list of values. Cancel (CAN) Cancel={0 | 1 | 2}. A value that specifies how a previously executed statement should be canceled. Valid values are ■ Cancel=0 fetches all remaining records if the statement was a Select. ■ Cancel=1 cancels the statement by calling dbcancel. Set Cancel=1 if dbcancel is supported in your client/server configuration. This is the initial default. ■ Cancel=2 closes the connection to the server for the statement. Set Cancel=2 only if dbcancel is not supported for your configuration and the performance of fetching all remaining records is unacceptable. Gateway (GW) Gateway={0 | 1}. A value that specifies whether you are using the Sybase Net-Gateway to access a DB2 database with this data source. Set Gateway=1 if this is the case. Otherwise, set Gateway=0 (the initial default). TwoPhaseCommit (TPC) TwoPhaseCommit={0 | 1}. This attribute lets you have two or more active statements within a transaction, using the SQL Server two-phase commit services. Set TwoPhaseCommit=1 to use two-phase commit. The active statements may deadlock if they reference the same SQL Server table. Otherwise, set TwoPhaseCommit=0 (the initial default). Netapi (NAPI) Netapi={0 | 1}. A value that specifies whether NETAPI.DLL is available. Netapi=0, the initial default, indicates it is not available; Netapi =1 indicates it is available. If you supply a value for the WorkstationID attribute, this attribute is ignored. ModifySQL (MS) ModifySQL={0 | 1}. This attribute is provided for backward compatibility. It determines whether the driver modifies SQL statements to conform to ODBC specifications or passes the SQL statement directly to SQL Server. Specify ModifySQL=1 to have the driver modify the SQL statement to conform to ODBC specifications. Specify ModifySQL=0 to have the driver understand SQL dialects found in earlier drivers. The default is 1. ═══ 9.5. SQL Server Data Types ═══ The SQL Server data types are mapped to the standard ODBC data types as follows: SQL Server ODBC Data Type binary SQL_BINARY bit SQL_BIT char SQL_CHAR datetime SQL_TIMESTAMP float SQL_FLOAT image SQL_LONGVARBINARY int SQL_INTEGER money SQL_DECIMAL real SQL_REAL smalldatetime SQL_TIMESTAMP smallint SQL_SMALLINT smallmoney SQL_DECIMAL sysname SQL_VARCHAR text SQL_LONGVARCHAR timestamp SQL_VARBINARY tinyint SQL_TINYINT varbinary SQL_VARBINARY varchar SQL_VARCHAR ═══ 9.6. Isolation and Lock Levels Supported ═══ SQL Server supports isolation level 1 (read committed) and 3 (serializable). SQL Server supports page-level locking. ═══ 9.7. ODBC Conformance Level ═══ The SQL Server driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. In addition, the following Level 2 functions are supported: ■ SQLBrowseConnect ■ SQLColumnPrivileges ■ SQLForeignKeys ■ SQLPrimaryKeys ■ SQLProcedureColumns ■ SQLProcedures ■ SQLTablePrivileges The driver supports the minimum SQL grammar. ═══ 9.8. Number of Connections and Statements Supported ═══ The SQL Server database system supports multiple connections. With two-phased commit, SQL Server supports multiple statements per connection. Otherwise, SQL Server supports a single statement per connection if SQL_AUTOCOMMIT is 0 and multiple statements per connection if SQL_AUTOCOMMIT is 1. ═══ 10. SQLBase Driver ═══ The SQLBase driver supports the Gupta SQLBase database system in the Windows and OS/2 environments. It also supports Gupta's SQLHost gateway to DB2. The driver filename is IVGUPnn.DLL. ═══ 10.1. SQLBase System Requirements ═══ The SQLBase driver requires the SQLBase client DLLs SQLOS22.DLL and SQLGCI.DLL. It also requires one or more SQLBase communication files (such as SQLPIPE.DLL) to provide access to local and remote SQLBase databases. The SQLBase driver is a 32-bit DLL and requires version 5.2 or greater of SQLBase client DLLs. ═══ 10.2. Configuring SQLBase Data Sources ═══ To configure a SQLBase data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV SQLBase and click OK. 3. If you are configuring an existing data source, select the data source name and click Setup. 4. The setup dialog box appears. 5. Specify values as follows: Data Source Name: A string that identifies this SQLBase data source configuration in ODBC.INI. Examples include "Accounting" or "SQLBase-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "SQLBase on Server number 1." Database Name: The name of the database to which you want to connect by default. The following values are optional: Server Name: The name of the server that contains the desired database. Specify the word LOCAL if you are using the local server. The server name is not required to log on. The logon dialog box appears more quickly if you do not specify a server name. If you do specify a server name, the Database Name drop-down list in the logon dialog box is populated with the names of the databases available on that server. Server List: A comma-separated list of servers that will appear in the Logon dialog box. Specify LOCAL to add the local server to the list. Default User Name: The default user name used to connect to your SQLBase database. A user name is required only if security is enabled on your database. Your ODBC application may override this value or you may override this value in the logon dialog box or connection string. Cursor Cache Size: The number of cursors the cursor cache can hold. The default is 6. Yield Proc: (This attribute is not available in OS/2.) A numeric value that determines whether you can work in other applications when SQLBase is busy. Input Message Size: The number of bytes in the input message buffer. The default is determined by SQLBase. Increasing this value retrieves more records across the network in a single fetch. Lock Time Out: The number of seconds SQLBase should wait for a lock to be freed before raising an error. Values can be -1 (wait forever) to 1800; the default is 300. No Recovery: Select this check box to disable transaction recovery. Selecting this box is dangerous because your database can become inconsistent in the event of a system crash. Release Plan: A number that determines whether a lock is maintained on a table when the cursors accessing the table are freed. Freeing the lock on the table results in a request to the server, which can decrease performance. No locks are freed when this option is set to the default, 0. Locks are freed when this option is set to 1. 6. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. 7. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 10.3. Connecting to SQLBase Using a Logon Dialog Box ═══ Some ODBC applications display a logon dialog box when you are connecting to a data source. In the logon dialog box, do the following: 1. Optionally, type the name of the server containing the SQLBase database tables you want to access or select the name from the Server Name drop-down list, which displays the server names you specified in the Setup dialog box. Type the word LOCAL to access a local SQLBase database. 2. Type the name of the database you want to access. If you specified a server name, you can select the name from the Database Name drop-down list. 3. If required, type your user name. 4. If required, type your password. 5. Click OK to complete the logon and to update the values in ODBC.INI. ═══ 10.4. Connecting to SQLBase Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for SQLBase is DSN=SQLBASE TABLES;DB=PAYROLL;UID=JOHN;PWD=XYZZY The following table gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN) A string that identifies a SQLBase data source configuration in ODBC.INI. Examples include "Accounting" or "SQLBase-Serv1." Database (DB) The name of the database to which you want to connect. LogonID (UID) The default logon ID (user name) used to connect to your SQLBase database. If so, contact your system administrator to get your logon ID. A logon ID is required only if security is enabled on your database. ServerName (SRVR) The name of the server containing the SQLBase database tables you want to access. Specify ServerName=LOCAL if you are using the local server. Servers (SRVRLIST) A comma-separated list of servers with which to prompt the user in a Logon dialog box. Specify LOCAL to add the local server to the list. Password (PWD) A case-sensitive password. CursorCacheSize (CCS) The number of cursors the cursor cache can hold. The cursor cache increases performance and uses few database resources. The initial default is 6. InputMessageSize (IMS) The number of bytes of the input message buffer. Increasing this value retrieves more records across the network in a single fetch. The initial default is determined by SQLBase. LockTimeOut (LTO) The number of seconds SQLBase should wait for a lock to be freed before raising an error. Values can be -1 to 1800; -1 means wait forever. The initial default is 300. DB2IsolationLevel (DIL) DB2IsolationLevel={CS | RR}. The DB2 isolation level SQLHost is using, provided for use with SQLHost connections. Set DB2IsolationLevel=CS when DB2 is using the Cursor Stability isolation level; DB2IsolationLevel=RR (the default) when DB2 is using Repeatable Read. YieldProc (YLD) (This attribute is not available in OS/2.) This option, which determines if you can work in other applications when SQLBase is busy. NoRecovery (NR) NoRecovery={0 | 1}. A value that enables or disables transaction recovery. NoRecovery=0 (the initial default) enables recovery; NoRecovery=1 disables recovery. NoRecovery=1 improves performance but is dangerous because your database can become inconsistent in the event of a system crash. See your SQLBase documentation for information on this option. ReleasePlan (RP) ReleasePlan={0 | 1}. A number that determines whether a lock is maintained on a table when the cursors accessing the table are freed. Freeing the lock on the table results in a request to the server, which can decrease performance. When set to 0, the default, no locks on tables are freed. When set to 1, locks are freed. ═══ 10.5. SQLBase Data Types ═══ The SQLBase data types are mapped to the standard ODBC data types as follows: SQLBase ODBC Char SQL_VARCHAR Date SQL_DATE Decimal SQL_DECIMAL Double Precision SQL_DOUBLE Integer SQL_INTEGER Long Varchar SQL_LONGVARCHAR Number SQL_DOUBLE Real SQL_REAL Smallint SQL_SMALLINT Time SQL_TIME Timestamp SQL_TIMESTAMP Varchar SQL_VARCHAR Note: The SQLBase Real data type is replaced by Float(21) when using DB2 via the SQLHost gateway. The Graphic, Vargraphic, and Long Vargraphic DB2 data types are not supported. ═══ 10.6. Isolation and Lock Levels Supported by SQLBase ═══ SQLBase supports isolation levels 1 (read committed, the default) and 3 (serializable). SQLBase also supports an alternative isolation level 1 called cursor stability. Your ODBC application can use this isolation level by calling SQLSetConnectOption (1040,1). SQLBase supports page-level locking. ═══ 10.7. SQLBase ODBC Conformance Level ═══ The SQLBase driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. In addition, the following Level 2 functions are supported: ■ SQLBrowseConnect ■ SQLColumnPrivileges ■ SQLForeignKeys ■ SQLPrimaryKeys ■ SQLSetPos ■ SQLTablePrivileges The driver also supports backward and random fetching in SQLExtendedFetch. It supports the core SQL grammar. ═══ 10.8. Number of Connections and Statements Supported by SQLBase ═══ The SQLBase database system supports multiple connections and multiple statements per connection. ═══ 11. Sybase System 10 Driver ═══ The Sybase System 10 driver supports the SQL Server System 10 database system from Sybase. The driver filename is IVSYBnn.DLL. ═══ 11.1. Sybase System 10 System Requirements ═══ You must install the Sybase Open Client-Library and the appropriate Sybase Net-Library to gain access to System 10. SYBPING is a tool that is provided to test connectivity from your client workstation to the database server (servers that are added through SQLEdit). Use this tool to test your connection. SQLEdit is a tool that allows you to define servers and adds them to SQL.INI. Set the environment variable SYBASE to the directory where you installed the System 10 client. For example, set SYBASE=C:\SQL10. You set this environment variable in the config.sys file. ═══ 11.2. Configuring Sybase System 10 Data Sources ═══ To configure a System 10 data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV Sybase System 10 and click OK. If you are configuring an existing data source, select the data source name and click Setup. The setup dialog box appears. 3. Specify values as follows: Data Source Name: A string that identifies this Sybase System 10 data source configuration in ODBC.INI. Examples include "Accounting" or "Sys10-Serv1." Description: An optional long description of a data source name. For example, "My Accounting Database" or "System 10 on Server number 1." Server Name: The name of the server that contains the System 10 tables you want to access. If not supplied, the server name in the DSQUERY environment variable is used. The following values are optional: Server List: The list of servers that appear in the logon dialog box. Separate the server names with commas. Database Name: The name of the database to which you want to connect by default. If you do not specify a value, the default is the database defined by the system administrator for each user. Database List: The databases that appear in the logon dialog box. Separate the names with commas. Default Logon ID: The default logon ID used to connect to your System 10 database. This ID is case-sensitive. A logon ID is required only if security is enabled for the database you are connecting to. Your ODBC application may override this value or you may override this value in the logon dialog box or connection string. Interfaces File: The pathname of the interfaces file. The default is the normal Sybase interfaces file. Password Encryption: A number that determines whether password encryption can be performed from the Open Client Library to the server (PasswordEncryption=1). When set to 0, the default, this cannot be done. Charset: The name of a character set corresponding to a subdirectory in $SYBASE/charsets. The default is the setting on the System 10 server. Workstation ID: The workstation ID used by the client. Language: The national language corresponding to a subdirectory in $SYBASE/locales. The default is English. Application Name: The name used by System 10 to identify your application. Yield Proc: This attribute is not available under OS/2. A numeric value that determines whether you can work in other applications when Sybase System 10 is busy. Optimize Prepare: A value of 0, 1, or 2 that determines whether stored procedures are created on the server for every call to SQLPrepare. When set to 0, stored procedures are created for every call to SQLPrepare. This setting can diminish performance. When set to 1, the initial default, the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and executed directly at SQLExecute time. When set to 2, the driver never creates stored procedures. Array Size: The number of rows the driver retrieves from the server for each fetch. This is not the number of rows given to the user. The default is 10 rows. Select Method: A value of 0 or 1 that determines whether database cursors are used for Select statements. When set to 0, the default, database cursors are used. When set to 1, Select statements are executed directly, without using database cursors. A setting of 1 limits the data source to one active statement and one active connection. Packet Size: A number that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance. When set to 0, the initial default, the driver uses the default packet size as specified in the System 10 server configuration. When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and save the value in the odbc.ini file. When set to x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, Packet Size=6 means to set the packet size to 6 * 512 = 3072 bytes). For you to take advantage of this connection attribute, you must configure the System 10 server for a maximum network packet size greater than or equal to the value you specified for Packet Size. For example, sp_configure "maximum network packet size", 5120 reconfigure Restart System 10 Server Note that the ODBC specification specifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, they have been defined as mutually exclusive. If PacketSize is specified, you will receive a message "Driver Not Capable" if you attempt to call SQL_PACKET_SIZE. If you do not set Packet Size, then application calls to SQL_PACKET_SIZE are accepted by the driver. 4. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. 5. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. ═══ 11.3. Connecting to System 10 Using a Logon Dialog Box ═══ Some ODBC applications display a Logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified. In the logon dialog box, do the following: 1. Type the case-sensitive name of the server containing the System 10 database tables you want to access or select the name from the Server Name drop-down list, which displays the server names you specified in the setup dialog box. 2. If required, type your case-sensitive login ID. 3. If required, type your case-sensitive password for the system. 4. Type the name of the database you want to access (case-sensitive) or select the name from the Database drop-down list, which displays the names you specified in the setup dialog box. 5. Click OK to complete the logon and to update the values in ODBC.INI. ═══ 11.4. Connecting to System 10 Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for Sybase System 10 is DSN=SYS10 TABLES;SRVR=IVSRVR;DB=PAYROLL;UID=JOHN;PWD=XYZZY The following table gives the long and short names for each attribute, as well as a description. The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN) A string that identifies a single connection to a System 10 database. Examples include "Accounting" or "Sys10-Serv1." ServerName (SRVR) The name of the server containing the System 10 tables you want to access. If not supplied, the initial default is the server name in the DSQUERY environment variable. LogonID (UID) The default logon ID used to connect to your System 10 database. This ID is case-sensitive. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID. Password (PWD) A case-sensitive password. Database (DB) The name of the database to which you want to connect. Language (LANG) The national language corresponding to a subdirectory in $SYBASE/locales. Charset (CS) The name of a character set corresponding to a subdirectory in $SYBASE/charsets. WorkstationID (WKID) The workstation ID used by the client. ApplicationName (APP) The name used by System 10 to identify your application. InterfacesFile (IFILE) The pathname to the interfaces file. ArraySize (AS) The number of rows the driver retrieves from the server for a fetch. This is not the number of rows given to the user. This increases performance by reducing network traffic. The initial default is 10 rows. YieldProc (YLD) A numeric value that determines whether you can work in other applications when Sybase System 10 is busy. This attribute is not available in OS/2. OptimizePrepare (OP) OptimizePrepare={0 | 1 | 2}. A value that determines whether stored procedures are created on the server for every call to SQLPrepare. When set to 0, stored procedures are created for every call to SQLPrepare. This setting can result in bad performance. When set to 1, the initial default, the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and executed directly at SQLExecute time. When set to 2, the driver never creates stored procedures. SelectMethod (SM) SelectMethod={0 | 1}. A value that determines whether database cursors are used for Select statements. When set to 0, the initial default, database cursors are used. In some cases performance degradation can ocur when performing large numbers of sequential Select statements because of the amount of overhead associated with creating database cursors. When set to 1, Select statements are executed directly without using database cursors. When set to 1, the data source is limited to one active statement and one active connection. Password Encryption (PE) Password Encryption={0 | 1}. A number that determines whether password encryption can be performed from the Open Client Library to the server (PasswordEncryption=1). When set to 0, the default, this cannot be done. PacketSize (PS) PacketSize={-1 | 0 | x}. A number that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance. When set to 0, the initial default, the driver uses the default packet size as specified in the System 10 server configuration. When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and save the value in the odbc.ini file. When set to x, an integer from 1 to 10, which indicates a multiple of 512 bytes (for example, PacketSize=6 means to set the packet size to 6 * 512 = 3072 bytes). For you to take advantage of this connection attribute, you must configure the System 10 server for a maximum network packet size greater than or equal to the value you specified for PacketSize. For example, sp_configure "maximum network packet size", 5120 reconfigure Restart System 10 Server Note that the ODBC specification specifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, they have been defined as mutually exclusive. If PacketSize is specified, you will receive a message "Driver Not Capable" if you attempt to call SQL_PACKET_SIZE. If you do not set PacketSize, then application calls to SQL_PACKET_SIZE are accepted by the driver. ═══ 11.5. Sybase System 10 Data Types ═══ The System 10 data types are mapped to the standard ODBC data types as follows: System 10 ODBC binary SQL_BINARY bit SQL_BIT char SQL_CHAR datetime SQL_TIMESTAMP decimal SQL_DECIMAL float SQL_FLOAT image SQL_LONGVARBINARY int SQL_INTEGER money SQL_DECIMAL numeric SQL_NUMERIC real SQL_REAL smalldatetime SQL_TIMESTAMP smallint SQL_SMALLINT smallmoney SQL_DECIMAL sysname SQL_VARCHAR text SQL_LONGVARCHAR timestamp SQL_VARBINARY tinyint SQL_TINYINT varbinary SQL_VARBINARY varchar SQL_VARCHAR Note: The nchar, nvarchar, sensitivity, and sensitivity_boundary data types are not supported. ═══ 11.6. Isolation and Lock Levels Supported by Sybase System 10 ═══ System 10 supports isolation levels 1 (read committed, the default) and 3 (serializable). It supports page-level locking. ═══ 11.7. Sybase System 10 ODBC Conformance Level ═══ The Sybase System 10 driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. In addition, the following Level 2 functions are supported: ■ SQLBrowseConnect ■ SQLColumnPrivileges ■ SQLForeignKeys ■ SQLPrimaryKeys ■ SQLProcedureColumns ■ SQLProcedures ■ SQLTablePrivileges The driver supports the minimum SQL grammar. ═══ 11.8. Number of Connections and Statements Supported by Sybase System 10 ═══ The System 10 database system supports multiple connections and multiple statements per connection. ═══ 12. Text Driver ═══ The Text driver supports ASCII text files. These files can be printed directly or edited with text editors or word processors, since none of the data is stored in a binary format. The driver filename is IVTXTnn.DLL. The Text driver executes SQL statements directly on the text files. The driver supports Insert statements, and inserts the record at the end of the file. However, you may not execute Update or Delete statements. ═══ 12.1. Common Text File Formats ═══ Some common formats for text files are listed in the following table: Format Description Comma-separated values Commas separate column values, and each line is a separate record. Column values can vary in length. These files often have the .CSV extension. Tab-separated values Tabs separate column values, and each line is a separate record. Column values can vary in length. Character-separated values Any printable character except single or double quotation marks can separate column values, and each line is a separate record. Column values can vary in length. Fixed No character separates column values. Instead, values start at the same position and have the same length in each line. The values appear in fixed columns if you display the file. Each line is a separate record. Comma-, tab-, and character-separated files are called character-delimited files, since values are separated by a special character. ═══ 12.2. Configuring Text Data Sources ═══ To configure a Text data source, do the following: 1. Start the ODBC Administrator. A list of data sources appears. 2. If you are configuring a new data source, click Add. A list of installed drivers appears. Select INTERSOLV TextFile and click OK. If you are configuring an existing data source, select the data source name and click Setup. The Setup dialog box appears. 3. Specify values as follows: Data Source Name: A string that identifies this Text data source configuration in ODBC.INI. Examples include "Accounting" or "Text Files." Description: An optional long description of a data source name. For example, "My Accounting Files" or "My Text Files in the Accounting Directory." Database Directory: The directory in which the text files are stored. If none is specified, the current working directory is used. The following values are optional: Rows to Scan: The number of rows in a text file that the driver scans to determine the data types in the file. If the value is 0, all rows in the file are scanned. The default is 25. Default Table Type: The type of text file: comma-separated, tab-separated, character-separated, or fixed length. This value tells the driver the default type, which is used when creating a new table and opening an undefined table. Delimiter Character: The character used as a delimiter for character-separated files. It can be any printable character. The default is a comma (,). Action for Undefined Tables: Two radio buttons that indicate what action the driver should take when it encounters a file that has not been defined. Set the Prompt for Definition radio button, if you want the driver to prompt the user when it encounters a file whose format is not defined. Otherwise, set the Guess Definition radio button; in this case, the driver guesses the file's format. Return Additional Tables: Select this check box to tell the driver to return files you have defined in functions like SQLTables, SQLColumns, etc. and files with a given extension. In Extension List, specify a comma-separated list of the extensions. To have files with no extensions returned, specify NONE. For example, if some of your files have the extensions TXT and CSV and others have no extension, specify TXT,CSV,NONE. By default, when an application requests a list of tables, only files that have been defined are returned. File Open Cache: A numeric value to specify the maximum number of unused file opens to cache. For example, the value 4 specifies that when a user opens and closes four tables, the tables are not actually closed. The driver keeps them open so that if another query uses one of these tables, the driver does not have to perform another open, which is expensive. The advantage of file open caching is increased performance. The disadvantage is that a user who specifies file locking on open may get a locking conflict even though no one appears to have the file open. The default is 0, which means no file open caching. Cache Size: The number of 64K blocks the driver uses to cache database records. The higher the number, the better the performance. The maximum number of blocks you can set depends on the system memory available. If the cache size is greater than 0, when browsing backwards, you will not be able to see updates made by other users until you reexecute the Select statement. The default is 4. Column Names in First Line: Select this check box to tell the driver to look for column names in the first line of the file. International Sort: A setting to indicate the order in which records are retrieved when you issue a Select statement with an Order By clause. Select this check box to use the international sort order as defined by your operating system. International sort order is case-insensitive (a precedes B); the sorting of accented characters is also affected (see your operating system documentation). Leave this box blank to use the ASCII sort order. ASCII sort order is case-sensitive, where uppercase letter precede lowercase letters (B precedes a). Note: The Rows to Scan, Default Table Type, Delimiter Character, and Column Names in First Line settings apply only to tables not previously defined. These fields also determine the attributes of new tables created with the Create Table statement. Use Long Qualifiers: A setting to enable the driver to use long pathnames as table qualifiers. When you set this check box, pathnames can be up to 255 characters. The default length for pathnames is 128 characters. 4. Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation. 5. Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source. You can change the defaults by configuring your data source again. You can override the defaults by connecting to the data source using a connection string with alternate values. Click Define in this dialog box to define the structure of your text files. Defining Table Structure discusses how to define the column names and data types in a table. ═══ 12.3. Defining Text Table Structure ═══ Since text files do not all have the same structure, the driver provides the option of defining the structure of an existing file. Although defining the structure is not mandatory, since the driver can attempt to guess the names and types of the columns, this feature is extremely useful. Define the structure of a file as follows: 1. Display the Define Table dialog box. 2. Specify table information. 3. Specify column information. 4. Define the table. Display the Define Table Dialog Box 1. Click Define in the ODBC Text Driver Setup dialog box, which you can access through the ODBC Administrator. The Define File dialog box appears. 2. Select the correct file and click OK to define the file's structure using the Define Table dialog box. In the Define Table dialog box, Database Name and File display the name of the database directory that contains the file and the name of the file you previously selected, respectively. Specify Table Information In the Table Information section of this dialog box, specify information about the overall structure of the file: 1. In the Table box, type a table name. Values may be up to 32 characters in length and may not be the same as another defined table in the database. This name is returned by SQLTables. By default, it is the filename without its extension. 2. Select the Column Names in First Line check box if the first line of the file contains column names. Otherwise, do not select this check box. 3. Open the Table Type box and select a table type: comma, tab, fixed, or character. 4. If the table type is character-separated, type the character that separates the values in the Delimiter Character box. 5. If you are using Windows, select the OEM to ANSI Translation check box to tell the driver that the data is stored in the IBM PC character set. If your data is stored in the ANSI character set, do not select this check box. This changes the display format only; the data is not converted. Specify Column Information In the Column Information section, define the types and names of the columns in the table. The box in the upper-left corner of this section lists the defined columns. If you specified a comma-separated, tab-separated, or character-separated table type, the Guess button appears in this section. Click Guess to tell the driver to guess the fields. The driver then displays what it thinks the fields are. You can then modify the field definitions by specifying values in the Name, Type, Mask, Precision, and Scale boxes (as appropriate) and clicking Modify. If you do not want the driver to guess the fields, take the following steps to define the fields: 1. In the Name box, type the name of the field. 2. Open the Type drop-down list and select the data type of the field. If the field type is date, then you must select a date mask for the field or type one in. See the following section, "Date Masks," for more information. 3. In the Precision box, type the precision of the field. 4. In the Scale box, type the scale of the field. The precision and scale values determine how numeric data is to be returned. 5. If you specified a fixed-length table type, you may specify the length and offset in the Length and Offset boxes, or you can specify a parse string. The length is the number of bytes the data takes up in storage; the offset is the number of bytes from the start of the table to the start of the field. 6. Click Add to add this field definition to the list box. To modify the selected field in the list box, click Modify. To remove the selected field in the list box, click Remove. If you specified a fixed-length table type, the Parse button is displayed. If you have not entered values in the length and offset boxes, click Parse to define the columns of the table. The Parse Table dialog box appears. This dialog box displays the first line of the file. You must mark where each field begins and ends by enclosing it in brackets. These brackets indicate the position and length of each field value in the record. You must do this for all the fields in the table. Click OK when you are done. Define the Table Click OK to define the table. ═══ 12.4. Date Masks for Text Driver ═══ Date masks tell the driver how a date is stored in a text file. When a value is inserted into a text file, the date is formatted so that it matches the mask. When reading a text file, the driver converts the formatted date into a date data type. The following table lists the symbols to use when specifying the date mask: Symbol Description m Output the month's number (1-12). mm Output a leading zero if the month number is less than 10. mmm, Mmm, MMM Output the three-letter abbreviation for the month depending on the case of the M's (that is, jan, Jan, JAN). mmmm, Mmmm, MMMM Output the full month name depending on the case of the M's (that is, january, January, JANUARY). d Output the day number (1-31). dd Output a leading zero if the day number is less than 10. ddd, Ddd, DDD Output the three-letter day abbreviation depending on the case of the D's (that is, mon, Mon, MON). dddd, Dddd, DDDD Output the day depending on the case of the D's (that is, monday, Monday, MONDAY). yy Output the last two digits of the year. yyyy Output the full four digits of the year. J Output the Julian value for the date. The Julian value is the number of days since 4712 BC. / - . : , (space) Output special characters used to separate the parts of a date. / Output the next character. For example, if the mask is mm/dd/yyyy /A/D, the value appears as 10/01/1993 AD in the text file. "string", 'string' Output the string in the text file. The following table shows some example masks and output when applied to the date value "1993-10-01". Mask Value yyyy-mm-dd 1993-10-01 m/d/yy 10/1/93 Ddd, Mmm dd, yyyy Fri, Oct 01, 1993 ═══ 12.5. Connecting to a Text Database Using a Connection String ═══ If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which ODBC.INI section to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in ODBC.INI. These values are not written to ODBC.INI. You can specify either long or short names in the connection string. The connection string has the form: DSN=data_source_name[;attribute=value[;attribute=value]...] An example of a connection string for text files is DSN=TEXT FILES;TT=CHARACTER;DC=& The following list gives the long and short names for each attribute, as well as a description. The defaults listed in the list are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI. If you specified a value for the attribute when configuring the data source, that value is your default. Attribute Description DataSourceName (DSN) A string that identifies a Text data source configuration in ODBC.INI. Examples include "Accounting" or "Text Files." Database (DB) The directory in which the text files are stored. ScanRows (SR) The number of rows in a text file that the driver scans to determine the column types in the file. If the value is 0, all rows in the file are scanned. The initial default is 25. TableType (TT) TableType={Comma | Tab | Character | Fixed}. The Text driver supports four types: comma-separated, tab-separated, character-separated, and fixed length. Setting this value tells the driver the default type, which is used when creating a new table and opening an undefined table. Delimiter (DC) The character used as a delimiter for character-separated files. It can be any printable character except a single or double quote. The initial default is a comma (,). UndefinedTable (UT) The Text driver can perform two operations when it encounters a file that has not been defined. UndefinedTable=PROMPT tells the driver to display a dialog box that allows the user to describe the file's format. UndefinedTable=GUESS tells the driver to guess the file's format. This is the initial default. ExtraExtensions (EE) A list of additional filename extensions to be recognized as text tables. When an application requests a list of tables, only files that have been defined are returned. To have the driver also return names of undefined files, specify a comma-separated list of file extensions. To specify files with no extension, use the keyword NONE. FileOpenCache (FOC) The maximum number of unused file opens to cache. For example, when FileOpenCache=4, and a user opens and closes four files, the files are not actually closed. The driver keeps them open so that if another query uses one of these files, the driver does not have to perform another open, which is expensive. The advantage of using file open caching is increased performance. The disadvantage is that a user who tries to open the file exclusively may get a locking conflict even though no one appears to have the file open. The initial default is 0. CacheSize (CSZ) The number of 64K blocks the driver uses to cache database records. The higher the number, the better the performance. The maximum number of blocks you can set depends on the system memory available. If the cache size is greater than 0, when browsing backwards, you will not be able to see updates made by other users until you reexecute the Select statement. The default is 4. FirstLineNames (FLN) FirstLineNames={0 | 1}. A value that determines whether the driver looks for column names in the first line of the file. If FirstLineNames=1, the driver looks for column names in the first line of the file. If FirstLineNames=0 (the initial default), the first line is interpreted as the first record in the file. IntlSort (IS) IntlSort={0 | 1}. A value that determines the order that records are retrieved when you issue a Select statement with an Order By clause. If IntlSort=1, the driver uses the international sort order as defined by your operating system. The sort is case-insensitive (a precedes B); the sorting of accented characters is also affected (see your operating system documentation). If IntlSort=0 (the initial default), the driver uses the ASCII sort order, where uppercase letters precede lowercase letters (B precedes a). UseLongQualifiers (ULQ): UseLongQualifiers={0 | 1}. Enables the driver to use long pathnames as table qualifiers. The default is 0, do not use long pathnames. The default length for pathnames is 128 characters. When UseLongQualifiers=1, pathnames can be up to 255 characters. Note: The ScanRows, TableType, Delimiter, and FirstLineNames attributes apply to tables that have not been defined. These attributes also determine the characteristics of new tables created with the Create Table statement. ═══ 12.6. Text Data Types ═══ The text file data types are mapped to the standard ODBC data types as follows: Text ODBC Numeric SQL_NUMERIC Date SQL_DATE Varchar SQL_VARCHAR ═══ 12.7. Text Select Statement ═══ You use the SQL Select statement to specify the columns and records to be read. The driver supports all Select statement clauses as described in SQL for Flat-File Drivers. ═══ 12.8. Text ODBC Conformance Level ═══ The Text driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions. It also supports backward and random fetching in SQLExtendedFetch. The driver supports the minimum SQL grammar. ═══ 12.9. Number of Connections and Statements Supported by Text ═══ Text files support multiple connections and multiple statements per connection.