About the Easysoft ODBC-Oracle Driver

ODBC API and scalar functions

API functions

The Easysoft ODBC-Oracle Driver supports all ODBC 3.x functions apart from:

The Easysoft ODBC-Oracle Driver partially supports SQLSetPos. An application can use the Easysoft ODBC-Oracle Driver to specify a cursor position by calling SQLSetPos with the SQL_POSITION argument.

Scalar functions

The Easysoft ODBC-Oracle Driver supports all scalar functions apart from:

  • BIT_LENGTH

  • CEILING

  • CHAR

  • CHARACTER_LENGTH

  • CHAR_LENGTH

  • LCASE

  • OCTET_LENGTH

  • UCASE

Use the ODBC syntax with scalar functions. For example:

SELECT
  Invoice_Id,
  Customer_Name,
  {fn EXTRACT(YEAR FROM Due_Date)} as "Year"
FROM
  Invoice

Cursor support

The Easysoft ODBC-Oracle Driver supports FORWARD_ONLY and STATIC cursors.

Advanced Security

The Oracle Advanced Security option is an Oracle client or server add-on that combines network encryption, database encryption, and strong authentication to protect sensitive data stored in Oracle databases. The Advanced Security option:

  • Guarantees data integrity by detecting whether it has been modified during transmission.

  • Encrypts data using encryption standards such as RSA or DES to ensure data privacy.

  • Supports third party authentication services such as Kerberos and RADIUS.

Oracle Advanced Security requires Net8 or Oracle Net to transmit data securely. The Easysoft ODBC-Oracle Driver uses the Oracle client, which uses these protocols to communicate with the Oracle database server over the network. Applications that access Oracle by using the Easysoft ODBC-Oracle Driver can therefore take advantage of the Oracle Advanced Security option.

For information about configuring the Oracle client and server for use with the Oracle Advanced Security option, refer to the Oracle Advanced Security Administrator’s Guide. No additional Easysoft ODBC-Oracle Driver configuration is necessary.

Oracle Real Application Clusters (RAC)

Real Application Clusters (RAC) is an Oracle High Availability feature that enables an Oracle Database Server Grid by providing a single database that spans multiple low-cost servers yet appears to the application as a single, unified database system. RAC combines the processing power of these multiple interconnected computers to provide system redundancy, scalability, and high availability. Application scale in an RAC environment to meet increasing data processing demands without changing the application code.

To increase the performance of a RAC database, you can add cluster nodes. Each additional node can help speed up application processing.

If a clustered server fails, the Oracle database will continue running on the surviving servers. If more processing power is needed, another server can be added without interrupting user’s access to data.

The Easysoft ODBC-Oracle Driver can access Oracle RAC environments. The Oracle client must be configured correctly to take full advantage of the RAC features. Please consult you Oracle documentation for more details.

Transparent Application Failover (TAF)

Transparent Application Failover (TAF) is a mechanism that automatically reconnects client applications to a node of a RAC database cluster following a connection failure. If a failure occurs, the Oracle client intercepts the resultant error message and starts the transparent failover process. The Oracle client requests another connection from the Oracle listener, which then connects the client to a surviving node of the RAC database cluster.

There may be a delay associated with failing over to another node. To keep users informed, it is possible for an OCI application to register a callback function that is invoked in the event of a connection loss and during the course of the failover. The callback function enables the OCI application to advise users that a failover is in progress and to wait while the failover completes. This ensures users do not attempt to restart their applications, because they perceive this delay as a application failure.

The Easysoft ODBC-Oracle Driver enables ODBC applications to register a failover callback function. To do this, the ODBC application must:

  1. Define a callback function that takes the form:

    int TAF_callback_fn( SQLHANDLE connection,
  int type,
  int event );

    where:

    • connection is the ODBC connection handle.

    • type is the Oracle failover type, which tells the callback function what type of failover the client has requested. The failover types are OCI_FO_NONE, OCI_FO_SESSION, OCI_FO_SELECT, and OCI_FO_TXNAL.

    • event is the type of Oracle failover event that took place, which tells the callback function why it was called. The failover events are OCI_FO_END, OCI_FO_ABORT, OCI_FO_REAUTH, OCI_FO_BEGIN, and OCI_FO_ERROR.

      The function can trigger a new failover attempt by returning OCI_FO_RETRY.

      All the OCI_* constants listed here are defined in the OCI header file oci.h. Consult the Oracle Call Interface Programmer’s Guide for the meanings of these constants.

  2. Register and establish a context for the callback function by calling SQLSetConnectAttr with the attributes SQL_ATTR_REGISTER_TAF_HANDLE and SQL_ATTR_REGISTER_TAF_CALLBACK.

    The definitions for these attributes are:

    #define SQL_ATTR_REGISTER_TAF_CALLBACK 1280
#define SQL_ATTR_REGISTER_TAF_HANDLE 1281

    The value SQL_ATTR_REGISTER_TAF_CALLBACK is a pointer to the callback function. The value for SQL_ATTR_REGISTER_TAF_HANDLE is a pointer to the connection handle used to establish a context for the callback function. For example:

    SQLSetConnectAttr(dbc,
1280 /* SQL_ATTR_REGISTER_TAF_CALLBACK */,
&TAF_callback_fn,
SQL_IS_POINTER);

SQLSetConnectAttr(dbc,
1281 /*SQL_ATTR_REGISTER_TAF_HANDLE*/,
&dbc,
SQL_IS_POINTER);

    Version 1.39 of the Perl DBD::ODBC module (which combined with Perl DBI provides an interface to ODBC databases for Perl) has been used to test the Easysoft ODBC-Oracle Driver’s TAF support. An example Perl script is provided that shows how to use the failover types and events to:

    • Keep the user informed throughout the duration of the failover.

    • Abort the failover.

An example Perl script is available at:

https://search.cpan.org/~mjevans/DBD-ODBC-1.39/ODBC.pm#odbc_taf_callback

Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP) is a scalability feature introduced in Oracle 11g Release 1, which uses a combination of dedicated server and connection broker to handle short, transient sessions coming from Web applications.

DRCP is especially relevant for architectures with multi-process, single-threaded application servers, such as PHP and Apache, that cannot do middle tier connection pooling.

The OCI client libraries enable applications to configure the behaviour of DRCP. Applications can:

  • Request a brand new session if they cannot reuse a session from the pool.

  • Specify a connection class that indicates that the application is willing to reuse a pooled server, which was used by other applications using the same connection class.

For example, applications in an HR suite may be willing to share pooled servers among themselves but not among other applications.

Because the Easysoft ODBC-Oracle Driver uses Oracle client software, it makes it possible for ODBC applications and interfaces to configure behaviour normally controlled from within the OCI layer. For example, the Easysoft ODBC-Oracle Driver enables PHP applications using the Unified ODBC interface to configure the DRCP pool purity or specify a connection class.

To configure the DRCP pool purity or specify a connection class from an ODBC application, set the Pool_Purity or Pool_Connection_Class Easysoft ODBC-Oracle Driver attributes. (Note that to set these attributes, you need to be using version 11.1+ of the Oracle client software.)

Background

A connection pool is a cache of database connection objects. The objects represent physical database connections that can be used by an application to connect to a database.

Connection pools promote the reuse of connection objects and reduce the number of times that connection objects are created. Connection pools significantly improve performance for database intensive applications because creating connection objects is costly both in terms of time and resources.

The connection pool is normally configured with a shared pool of physical connections, translating to a back-end server pool containing an identical number of dedicated server processes.

Applications that can use connection pooling include middle tier applications for Web application servers and e-mail servers. (Web applications introduced the three-tier model in which the browser is the client tier, the database is the backend tier, and the web server and its extensions are the middle tier.)

Connection pooling is beneficial only if the middle tier itself is multithreaded, because it takes advantage of the ability of multiple threads in one application process to share resources. (Threads are lightweight processes that exist within a larger process.)

DRCP is an alternative connection pooling mechanism that enables multi-process applications to share connections to the database. (In multi-process applications, unlike in multi-threaded applications, processes are insulated from each other.)

Network Protocols

The Easysoft ODBC-Oracle Driver supports both IPC and TCP/IP network protocols.

Materialized Views

The Easysoft ODBC-Oracle Driver supports materialized views. A materialized view is a database object that contains the results of a query. Materialized views stored in the same database as their base tables can improve query performance through query rewrites.

The query rewrite mechanism reduces response time for returning results from the query. It does this by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.

For more information about materialized views, refer to your Oracle documentation.

XA support

The Easysoft ODBC-Oracle Driver can be configured to take part in a distributed XA transaction. To do this, add a data source that contains entries that correspond with the xa_open string used by the XA Transaction Manager to connect to the Oracle database server.

For example, the following sample xa_open string opens an XA connection to an Oracle database named payroll. It also specifies the Net8 link and the username and password used to log onto the Oracle server.

Oracle_XA+sqlnet=ninetwo.oracle+SesTm=35+Acc=P/system/manager+Threads=true+DB=payroll

To make this XA connection available for use by the Easysoft ODBC-Oracle Driver, the data source used to access Oracle needs to contain the following corresponding entries:

[ORACLE-XA]
Driver			          = ORACLE
Database		          = ninetwo.oracle
User			          = system
Password		          = manager
XA_Enlist		          = 1
XA_Connection_String	  = payroll

In addition, the XA_Enlist attribute must be set to 1. When this setting is present, the Easysoft ODBC-Oracle Driver accesses Oracle by using the Oracle XA library. Note that the data source can then only be used to access an Oracle database as an XA resource under the control of a Transaction Manager. If you need to access the same database with a non-XA connection, configure a separate data source without the XA_Enlist attribute.

The XA_Connection_String attribute is only required if the DB field is present in the xa_open string. The XA_Connection_String attribute value must be the same as that of the DB field.

A tutorial that provides more information about using the Easysoft ODBC-Oracle Driver in the context of an XA transaction is available on the Easysoft web site. The tutorial includes a code sample that shows the point at which an ODBC connection needs to be created and closed to participate in a distributed transaction.

Supported data types

The Easysoft ODBC-Oracle Driver supports these Oracle data types:

  • BLOB

  • BFILE

  • RAW

  • CLOB

  • CHAR

  • NUMBER

    To control how NUMBER data types are mapped onto ODBC data types, use the Data_Type_Map parameter. For more information, refer to the connection attributes section.

  • BINARY_FLOAT

  • BINARY_DOUBLE

  • DECIMAL

  • INTEGER

  • FLOAT

  • DOUBLE PRECISION

  • VARCHAR2

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

  • DATE

Finding out more about data types on Windows

If you need more information about a data types, for example, the precision and scale, use Microsoft’s ODBC Test to do this.

  1. Download the version of ODBC Test that matches your application’s architecture from:

    https://www.easysoft.com/ftp/pub/utils/windows/odbc-test/

  2. Copy both files to a folder on the machine where Easysoft ODBC-Oracle Driver is installed.

  3. Double-click odbcte32.exe.

  4. Select Con > Full Connect.

  5. Choose your Easysoft ODBC-Oracle Driver data source from the list.

  6. Choose Catalog > SQLGetTypeInfo.

  7. Either choose SQL_ALL_TYPES=0 (1.0) or a specific data type from the DataType list.

  8. Choose Results > Get Data All.

Example SQL statements

Example queries

  • To fetch all records from a table, use the asterisk symbol (*) in your queries. For example:

    SELECT * FROM Customers

  • To only fetch records whose values are different, use DISTINCT. For example:

    -- Which different sales regions are there?
SELECT DISTINCT Region AS Different_Regions FROM SalesOrders
-- How many different sales regions are there?
SELECT COUNT(DISTINCT Region) AS Different_Regions FROM SalesOrders

  • To filter records, use WHERE. For example:

    SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  Region = 'Eastern'

SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  Region = 'Eastern'
  OR Region = 'Western'

SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  Region = 'Eastern'
  AND EXTRACT(YEAR FROM OrderDate) = 2025

    You can also supply a WHERE clause value as a parameter. For example, to do this in Python:

    cursor.execute("SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  Region = ?", ['Eastern'])

  • To fetch records that don’t match the WHERE clause pattern use NOT. For example:

    SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  NOT Region = 'Eastern'

  • To sort the result set in either ascending or descending order, use ORDER BY. For example:

    SELECT
  *
FROM
  SalesOrders
ORDER BY
  OrderDate ASC

SELECT
  *
FROM
  Contacts
ORDER BY
  (
    CASE
      WHEN Surname IS NULL THEN Title
      ELSE Surname
    END
  );

  • To group a result set into summary rows, use GROUP BY. For example:

    SELECT
    COUNT(Id) As "Number",
    ProductID
FROM
    SalesOrderItems
GROUP BY
    ProductID

SELECT
    COUNT(Id) As "Number",
    ProductID
FROM
    SalesOrderItems
GROUP BY
    ProductID
HAVING
  COUNT(Id) > 100;

  • To do calculations based on result set vales, use the SQL aggregate functions MIN(), MAX(), COUNT(), SUM(), and AVG(). For example:

    SELECT Max(Quantity) FROM SalesOrderItems
SELECT Sum(Quantity) FROM SalesOrderItems

  • To convert between compatible data types, use CAST. For example:

    SELECT CAST(Quantity AS Char(100))FROM  SalesOrderItems

  • To fetch records that contain column values between a given range, use BETWEEN For example:

    SELECT ProductID FROM SalesOrderItems WHERE Quantity BETWEEN 10 AND 20

  • To combine the result set of two or more SELECT statements, use UNION. For example:

    SELECT City FROM Contacts
UNION
SELECT City FROM Customers

  • To combine rows from two or more tables, use JOIN. For example:

    SELECT SalesOrders.ID, Customers.Surname, SalesOrders.OrderDate
FROM SalesOrders
INNER JOIN Customers ON SalesOrders.CustomerID=Customers.ID;

  • To fetch records that contain column values matching a search pattern, use LIKE. For example:

    SELECT Surname, GivenName FROM Customers WHERE CompanyName LIKE 'R%'
SELECT Surname, GivenName FROM Customers WHERE CompanyName LIKE '_he'

  • To search for columns without a value (NULL) or with a value (non NULL), use either IS NULL or IS NOT NULL. For example:

    SELECT * FROM Customers WHERE CompanyName IS NULL

  • To specify multiple values in a WHERE clause, you can use IN as an alternative to OR. For example:

    SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  Region = 'Eastern'
  OR Region = 'Western'
  OR Region = 'Central'

    can be replaced with:

    SELECT
  OrderDate,
  SalesRepresentative
FROM
  SalesOrders
WHERE
  Region IN  ('Eastern', 'Western', 'Central')

  • To set the maximum number of records to return, use LIMIT. For example:

    SELECT * FROM Customers LIMIT 10

  • To test for the existence of records in a subquery, use EXISTS. For example:

    SELECT
  Name
FROM
  Products
WHERE
  EXISTS (
    SELECT
     *
    FROM
      SalesOrderItems
    WHERE
     Products.ID = SalesOrderItems.ProductID
      AND Quantity < 20
  )

Example inserts, updates, and deletes

  • To insert a Oracle record, use INSERT INTO. For example:

    INSERT INTO
    Customers (
        Surname,
        GivenName,
        City,
        Phone,
        CompanyName
    )
VALUES
    (
        'Devlin',
        'Michaels',
        'Kingston',
        '2015558966',
        'PowerGroup'
    )

  • Here’s a SQL Server linked server example:

    EXEC ('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName)
VALUES (''Devlin'' , ''Michaels'' , ''Kingston'' , ''2015558966'' , ''PowerGroup'')')

  • Here’s an Oracle linked table example:

    DECLARE
  num_rows integer;
BEGIN
num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@Link
('INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (''Devlin'', ''Michaels'', ''Kingston'', ''2015558966'', ''PowerGroup'')');
END;
/

  • The Easysoft ODBC-Oracle Driver also supports parameterized inserts. Here’s an example of doing this in Perl:

    my $sth = $dbh->prepare(q/INSERT INTO Customers (Surname, GivenName, City, Phone, CompanyName) VALUES (?, ?, ?, ?, ?)/)
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute('Devlin', 'Michaels', 'Kingston', '2015558966', 'PowerGroup');

  • To update a Oracle record, use UPDATE. For example:

    UPDATE Customers
SET
  Surname = 'Jones'
WHERE
  Account_Id = 'PowerGroup'

    The Easysoft ODBC-Oracle Driver also supports parameterized updates. Here’s an example of doing this in Perl:

    my $sth = $dbh->prepare('UPDATE Customers SET Surname = \'Jones\' WHERE CompanyName = ?')
    or die "Can't prepare statement: $DBI::errstr";

$sth->execute('PowerGroup');

  • To delete a Oracle record, use DELETE. For example:

    -- Delete (mark inactive) a bank account
DELETE FROM Customers WHERE CompanyName = 'PowerGroup'

    The Easysoft ODBC-Oracle Driver also supports parameterized deletes. Here’s an example of doing this in Python:

    sql = "DELETE FROM Customers WHERE CompanyName = ?"
cursor.execute(sql, 'PowerGroup')