Oracle7 ODBC Driver for Windows 3.1

Version 2.5.3.1.0 Production

Release Information

The information on Oracle7 ODBC Driver is divided into the following sections:

New in this Release

Required Components

Installing and Using the Product

Information on Bug Fixes

Known Issues and Additional Information

New in this Release

SQLBrowseConnect API has been implemented in this release.

Required Components

This version of the Oracle7 ODBC Driver requires the following Oracle components:

Component Version

Required Support Files 7.3

SQL*Net 2.3

Oracle Installer 3.1.3.1.0 or later

Installing and Using the Product

This product should be installed and deinstalled using only the Oracle Installer.

This version of Oracle7 ODBC driver will not coexist with any other Oracle7 ODBC driver version. Deinstall all previous installations of Oracle7 ODBC driver before installing this version of the driver.

Installation of this product copies the following components :

Oracle7 ODBC driver

Oracle7 ODBC driver Release notes

Oracle7 ODBC driver Help file

ODBC 16bit administrator

Oracle7 ODBCTEST application

Icons are created in "Oracle7 ODBC" folder for Windows 3.1 operating system.

To configure data sources, run the ODBC Administrator. To access remote Oracle7 databases, SQL*Net is required.

Information on Bug Fixes

Bug Number Description

487935 WHERE CLAUSE OVER DATE COLUMN PRODUCES WRONG RESULT FOR LEFT

JOIN

508739 ORA-1455 ON A COLUMN OF NUMBER(10)

495670 ENH: SQLEXTENDEDFETCH WITH SQL_SCROLL_FORWARD_ONLY ONLY

SUPPORTS SQL_FETCH_NEXT

507660 CANNOT CALL A STORED FUNCTION

501543 LAST CHARACTER OF A STRING ARRAY IS TRUNCATED SOMTIMES AT SECOND

SQLEXECUTE

509076 NEED TO CONSIDER IMPLEMENTING SQLBROWSECONNECT

508623 ORA-2005 HAPPENS WHEN DELETE A DATA OF LONG RAW FIELD

515556 MEMORY ALLOCATION ERROR WHEN DELETE A COLUMN (VARCHAR2

DATATYPES > 255)

519175 UNABLE TO PASS PARAMATERS >254 CHACTERS IN LENGTH IN RDO STORED

FUNCTION CALL

295624 ABILITY TO DISABLE ORACLE LOGON LOOP IN ODBC DRIVER

515121 ODBC DRIVER WILL NOT ACCEPT USERNAME/PASSWORD AS ONE STRING

523391 SQLCONNECT ALWAYS CONNECTS TO THE LOCAL SERVER

516611 SQLSTATISTICS APPEARS TO CAUSE SUBSEQUENT FETCHS TO BE VERY SLOW

Known Issues and Additional Information

Asynchronous Default Setting

By default, Asynchronous execution is off. You can specify the setting for a particular Data Source while setting it up. If you want Asynchronous execution for a particular connection, you can use the ASY keyword of SQLDriverConnect command.

Positioned Updates

Oracle7 ODBC driver supports positioned updates. You should use SELECT statement with the FOR UPDATE clause to use this feature. You can use one statement handle to select the records and another statement handle to update or delete. While updating or deleting, you need to use the cursor name and the CURRENT OF clause.

Oracle7 ODBC driver does not append the NO WAIT clause to the SELECT statement. If the tables in query are locked, the application waits till the lock is removed on the respective tables. The application should explicitly use the NO WAIT clause with the SELECT statement to avoid a long wait.

After positioning the cursor, you can delete or update the record with the cursor name. If the driver has selected the record before the cursor was named, you can get the name of the cursor with SQLGetCursorName.

For more information, refer to Microsoft ODBC SDK Programmer's Reference.

Limitations on Concurrent Connections

The driver currently supports up to a maximum of 15 concurrent connections.

Asynchronous Execution

From version 1.14.1.0.0 onwards, Oracle7 ODBC driver has non-blocking SQL support. With this feature, applications running long queries do not block execution of other programs on client machines. This feature is transparent to client applications. To switch to another application press CTRL-ESC or ALT-TAB and select the application you wish to run.

You might not be able to do other work in the client application while running the query. This feature provides a mechanism to work in other programs while a database command is running. Depending on the client application, switching to other applications while a query is executing might take from 1 to 10 seconds.

By default Asynchronous execution is off. You can specify the setting for a particular Data Source while setting it up. If you want Asynchronous execution for a particular connection, you can use the ASY keyword of SQLDriverConnect command.

Windows 95/NT Compatibility

This Oracle7 ODBC Driver is not supported under Microsoft Windows 95/NT.

Level 2 Compliance

The Level 2 functions SQLSetPos() and SQLDescribeParam() are not implemented in this release. These are planned for future releases.

ODBC Specifications Compliance

This driver conforms to ODBC 2.1 specifications and Level 2 API conformance.

The functionality has been tested against ODBC SDK version 2.1.

Data Types

The following data types are supported:

SQL Data Types

SQL_CHAR

SQL_TIMESTAMP

SQL_FLOAT

SQL_BIT

SQL_TINYINT

SQL_BIGINT

SQL_DECIMAL

SQL_SMALLINT

SQL_INTEGER

SQL_REAL

SQL_DOUBLE

SQL_BINARY

SQL_DATE

SQL_TIME

SQL_LONGVARCHAR

SQL_LONGVARBINARY

SQL_NUMERIC

SQL_VARBINARY

SQL_VARCHAR

SQL C Data Types

SQL_C_SLONG

SQL_C_SSHORT

SQL_C_STINYINT

SQL_C_ULONG

SQL_C_USHORT

SQL_C_UTINYINT

Packaged Procedures

The syntax for package.procedure (arguments) is not directly functional and there is no synonym workaround. To invoke a packaged procedure, you would create a stored procedure that invokes the packaged procedure. For example, to invoke procedure "bar" in package "foo" with arguments X, Y, Z, you would create a stub procedure, say, "stub_foo", as follows:

CREATE OR REPLACE PROCEDURE

stub_foo (x in number, y out number, z in out number) AS

BEGIN

foo.bar (X, Y, Z);

END

Now you invoke foo.bar( ) using stub_foo( ).

Date and Time Default

If you use a DATE data type column and you do not specify a date component, the default date is the current date. If you use a DATE data type field and you do not specify a time component, the default time is 12:00:00am.

Time Format

Oracle7 only stores hours, minutes and seconds. It does not store decimals for seconds. The valid time format is {t '10:20:43'}. If you show a BOUND column for data type DATE, the decimal time portion is returned (10:20:43.0), but not stored, on the Oracle7 server.

Cannot Retrieve Dates in the BC Era

The driver allows you to insert a date in the BC era with "insert into table values (TO_DATE('0001-10-18 BC','YYYY-MM-DD BC'))", but returns an error message, "Numeric value out of range", when you retrieve the value. This is because the ODBC specification treats negative and zero year values as invalid.

Timestamp: Decimal Value on Seconds

Inserting a decimal value on seconds for a Timestamp data type while using the Timestamp parameter truncates the decimal portion of the seconds without reporting a truncation error. Example: inserting into table values ({ts '1992-12-31 23:59:59.123'}) results in storing the value '1992-12-31 23:59:59'.

Misleading Error Message for Full Year Less Than or Equal to Zero

As part of the ODBC specifications, values for a full year less than or equal to zero are invalid. If a value less than or equal to zero is inserted as a year, the following error message is displayed: "Full year must be between -4713 and +4713". This message is misleading. The Oracle7 database allows a full year to be less than or equal to zero, but, to comply with the ODBC specifications, the Oracle7 ODBC Driver does not.

Numeric Truncation and Rounding Off

Inserted numeric values are truncated and/or rounded without any notification to the user.

Example 1: inserting 123456.1256 into a data type of number(8,2) results in storing the value 123456.13 (the last 2 decimals are truncated and the second decimal is rounded off).

Example 2: inserting 123.45 into a data type of number(8,0) results in storing the value 123 (the two decimals are truncated).

LONG RAW and RAW are Not Searchable

The data types LONG RAW and LONG are not searchable. For limitations, refer to the Oracle7 Server SQL Language Reference Manual.

Valid Value Range Limited by Oracle7 BCD Data Format

Since the Oracle7 database stores real numbers in BCD format, with a limit of 22 characters, the valid numeric range is approximately 1e-129 to 1e+126. This means that a number may have a maximum of 126 digits when inserted.

Precision Corrupted For Some Float Values

When a string of digits is inserted into a float field, they are first converted by the server to BCD format for storage. When they are retrieved, they are again converted from BCD format to float format. This conversion process is not always exact, so a small conversion error is occasionally introduced (for example, if the value 41.256 is inserted, the value retrieved is 41.256000000000001).

PacketSize Options

In order to send a specific packet size to the server, add the line "PacketSize=" to your ODBC.INI file. Only 1024, 2048, 4096 and 8192 are valid sizes (in bytes). When you add an ODBC Data Source, the line "PacketSize=2048" will be placed in your ODBC.INI file, but only for TCP/IP servers ("t:" prefix). If there is no entry in your ODBC.INI file, then no PacketSize will be sent in the connection string.

Transactions are for Data Manipulation Language (DML) Statements

Transactions (COMMIT, ROLLBACK) are for DML (that is UPDATE, DELETE, etc.)

If the transaction encounters a DDL statement, the transaction will be committed if the DDL statement has a valid syntax.

Execution of Data Definition Language (DDL) Statements

In the versions of the Oracle7 ODBC driver prior to version 2.0.3.1.1, preparing any DDL statement resulted in execution of the statement.

From version 2.0.3.1.1 driver onwards, the parsing of any DDL SQL Statement is deferred until the execute phase. Hence, any DDL statement gets executed only on calling SQLExecute and not upon SQLPrepare.

Autocommit

The driver sets the autocommit default to on. To turn off the autocommit, call SQLSetConnectOption with SQL_AUTOCOMMIT_OFF as the third parameter. Once the autocommit is turned off, you must call SQLTransact to commit/rollback any transaction. When autocommit is set to off, users might experience locking conflicts on DML statements. This is the normal situation.

Procedure/Function

Oracle Procedure does not have direct return value. If you are calling a procedure by this driver, please do not specify a place holder and equal sign before the call statement. If, however, you are calling a function, you have to have a place holder and equal sign. Otherwise, the driver will treat it as a procedure call. When using a constant string input parameter, the string must be enclosed in single quotes. If the string is not enclosed in single quotes, the driver treats it as an internal variable. The driver treats the string not enclosed in single quotes as it is. For example,

{call proc_name(1234-,?)}

would become

begin

proc-name (1234,:C1);

end.

Other examples

To call a procedure:

SQLExecDirect(hstmt, "{call proc_name('My_input_string', ?)}", len);

To call a function:

SQLExecDirect(hstmt, "{?=call proc_name('My_input_string', ?)}", len);

Note: Please refer to the ODBC API online help for more details on how to use SQLBindParam()

to setup each place holder.

Scalar Functions

There is no syntax checking for all Scalar functions. Internally, the driver converts all scalar functions to Oracle function calls and passes them to the server. If there is a syntax error, such as an invalid parameter, the calling program will not get the error return code until the program calls the ODBC fetch data routine.

Mapping Between C Data Type Float/Double and SQL Data Type Number

If a column data type is FLOAT, and you are retrieving the data in your program, or if you don't know the value range, or you know it is not between -3.5E38 and +3.5E38, use DOUBLE instead of FLOAT. For C data types, FLOAT is 4 bytes and DOUBLE is 8 bytes. For Oracle7, FLOAT can be either 4 bytes or 8 bytes. For Oracle database server 7.0.x, if you fetch a number which is stored in a char string column and its value is not between -3.5E38 and +3.5E38, and if your C data type is SQL_C_FLOAT, then you might get General Protection Fault (GPF). This bug has been fixed in Oracle 7.1.x.

Data Dictionary View is Not Accessible in Some Oracle 7.0.x Versions.

In some earlier versions of Oracle7 database servers, even if you have SELECT_ANY_TABLE privileges, you still cannot access Data Dictionary (DBA) views. This problem has been corrected in Oracle 7.1.x.

Executing a Stored Procedure or a Block of PL/SQL Statements

To execute a stored procedure or a block of PL/SQL statements, the ODBC driver treats return code ORA-1403(NO DATA FOUND) as SQL_SUCCESS. According to the ODBC specification, the return error code should be based on the SQL statement, whether or not it has been successfully executed. It is not based on the return data. You still can use the exception handler to create a user error return code.

Using Two Programs Results in a GPF in the Required Support Files (RSFs)

If there are two programs running, the first one calls the second one, which connects to a remote database using the ODBC driver. After executing a couple of ODBC API calls, it then disconnects and exits to return control to the first program. If you repeat spawning a second program which makes the ODBC calls approximately 100 times, a General Protection Fault occurs in the Windows Required Support Files. However, if you call a subroutine within the first program to connect via ODBC to a remote Oracle Server, no general protection fault will occur.

Float Data Type

According to the Microsoft ODBC specification, the float data type should have precision set to 15. The Oracle7 ODBC driver has precision set to 18.

Thunking Support

This driver does not support thunking facility of any kind.

Outer Joins

When SQLGetInfo() is called for fInfoType=SQL_OUTER_JOIN, the driver returns "Y". The driver supports the ODBC outer join syntax for left outer joins only. The driver does not support nested outer joins. However, columns on the left side of the comparison operator in the ON clause must come from the left-hand table in the outer join, and columns on the right side of the comparison operator must come from the right-hand table.

Knowing the Return Data Type of Stored Procedures

SQLProcedureColumns() returns an additional row giving the information about the data type of value returned by each function.

SQL_C_ULONG Data Type Limitation

The maximum value allowed for SQL_C_ULONG is the same as the maximum value allowed for SQL_C_LONG. This will be rectified in the future versions.

SQLExtendedFetch Options

SQL_FETCH_FIRST and SQL_FETCH_NEXT options are available.

SQL_FETCH_FIRST can be specified only in the first SQLExtendedFetch call (i.e., when no rows have been retrieved yet).

SQL_FETCH_NEXT can be specified at any time for SQLExtendedFetch call.

SQLGetData Features

When the following sequence is performed :

SQLGetData(icol=n), SQLGetData(icol=m), SQLGetData(icol=n), the second call to SQLGetData(icol=n) retrieves data from the start of the n column. Any offset in the data in the column due to earlier calls to SQLGetData for the column is no longer valid. This is according to the 2.1 updates to ODBC specifications.

But this behavior is not applicable if column n is of "LONG" data type in database. In that case, the second call to SQLGetData(icol=n) after the call to SQLGetData(icol=m) returns "SQL_NO_DATA_FOUND".

Errors in SQL Statements Returned Only When SQLExecute is Called

From version 2.0.3.1.1 driver onwards, the parsing of any SQL statement is deferred until the execute phase. Hence, any error in the SQL statement is captured and returned only on calling SQLExecute and not upon SQLPrepare.

License Agreement

IMPORTANT--READ THE FOLLOWING LICENSE AGREEMENT CAREFULLY BEFORE

INSTALLING AND/OR USING THE PROGRAM.

By installing and/or using the Program, you indicate your acceptance of the following Oracle

Program License Agreement ("Agreement"). If you do not agree to the terms of this Agreement, promptly delete the Program from your computer and cease use of the Program.

Oracle Corporation ("Oracle") grants you ("Customer") a license to use the Oracle7 ODBC Driver software ("Program") as indicated below.

Customer may make and use an unlimited number of copies of the Program solely for Customer's internal data processing operations; provided that: (a) the Program is not modified in any way; and (b) Customer maintains the copyright notice on all copies of the Program. Customer may not use the Program for commercial timesharing, rental, or service bureau use. The Program may not be transferred, sold, assigned, or otherwise conveyed to another party without Oracle's prior written consent. Customer agrees not to cause or permit the reverse engineering, disassembly, or decompilation of the Program. Customer shall not disclose the results of any benchmark tests of the Programs to any third party without Oracle's prior written approval.

Customer acquires only the right to use the Program and does not acquire any rights of ownership in the Program. Oracle, or its licenser, shall at all times retain all rights, title and interest in the Program.

Oracle may terminate this Agreement if Customer breaches any of its terms. Upon termination, Customer shall cease using the Program and shall destroy or return to Oracle all copies of the Program including all modifications and merged portions in any form.

This Agreement is governed by the laws of the State of California.

ORACLE DOES NOT WARRANT THE PROGRAM AND ORACLE DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NONINFRINGEMENT; THIS PROGRAM IS DISTRIBUTED "AS-IS". ORACLE SHALL NOT BE LIABLE FOR ANY INDIRECT, INCIDENTAL, SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE, DATA OR DATA USE, INCURRED BY CUSTOMER OR ANY THIRD PARTY, WHETHER IN AN ACTION IN CONTRACT OR TORT, EVEN IF CUSTOMER OR ANY OTHER PERSON HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. ORACLE'S LIABILITY FOR DAMAGES HEREUNDER SHALL IN NO EVENT EXCEED THE FEES PAID BY CUSTOMER UNDER THIS AGREEMENT.

Customer agrees to comply fully with all relevant export laws and regulations of the U.S. to assure that neither the Programs, nor any direct product thereof, are exported, directly or indirectly, in violation of U.S. law. The Program may not be transferred outside the country where delivery is taken or transferred, sold, assigned, or otherwise conveyed to another party without Oracle's prior written consent.

If the Program is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable.

Restricted Rights Legend Use, duplication or disclosure by the Government is subject to restrictions as currently set forth in subparagraph (c)(1)(ii) of DFARS 252-227-7013, Rights in Technical Data and Computer Software (October 1988). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA, 94065.

If the Program is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights" as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

Unless both parties have signed a license agreement covering the Program, this Agreement constitutes the complete agreement between the parties and supersedes all prior or contemporaneous agreements or representations, written or oral, concerning the subject matter of this Agreement. This Agreement may not be modified or amended except in a writing signed by a duly authorized representative of each party; no other act, document, usage or custom shall be deemed to amend or modify this Agreement. The waiver by either party of any breach of this Agreement shall not constitute a waiver of any other or subsequent breach.

Oracle is a registered trademark of Oracle Corporation. Microsoft, Windows, and Access are trademarks of Microsoft Corporation. All other product or company names mentioned are used for identification purposes only, and may be trademarks of their respective owners.