X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

ODPI-C: A Light Weight Driver for Oracle Database

Christopher Jones
Senior Principal Product Manager

This is a guest post by Oracle’s Vaddeti Karthik Raju, a contributor to the ODPI-C project.

What is ODPI-C ?

Oracle Database Programming Interface for C (ODPI-C) is an open source library of C code that simplifies the use of common Oracle Call Interface (OCI) features for Oracle Database drivers and user applications which are written in C or C++. It is a wrapper over Oracle Call Interface (OCI) that makes applications and language interfaces easier to develop.

For complete details of ODPI-C, visit the homepage. Here you can find a list of all its powerful features, links to the source code, the documentation, and some samples.

Why ODPI-C ?

Since ODPI-C is a wrapper on top of OCI and exposes common OCI functionality, why use ODPI-C? Though OCI is highly efficient and flexible, it requires a lot of code and technical skill to use well. ODPI-C reduces the amount of code and the skill level required, which is particularly useful for language driver creators. Thus it reduces the amount of time required for a developer to implement new Oracle features in a particular language or application.

User applications and language drivers have been written in ODPI-C. Drivers currently using ODPI-C include:

Oracle Drivers

  • cx_Oracle Python interface

  • node-oracledb Node.js module

Third-party Drivers

  • go-goracle Go Driver

  • mirmir Rust Bindings

  • odpic-raw Haskell Raw Bindings

  • ruby-ODPI Ruby Interface

  • rust-oracle Driver for Rust

Common setup

Let us go through few example programs which demonstrates how to perform operations in ODPI-C.

For installation of ODPI-C, visit the installation instructions. In summary, the supplied Makefile or Makefile.win32 can be used to build a shared library, which can be linked with samples and tests. Once the library has been built, locate the directory it is in and add the directory name to your system library search path, e.g. PATH on Windows or LD_LIBRARY_PATH on Linux. You'll also need some Oracle client libraries, such as from the Oracle Instant Client "Basic" package.

Before going further, create the database objects using the below code. Here I am using database credentials scott/tiger@localhost/orclpdb:

$ sqlplus scott/tiger@localhost/orclpdb
create table TestTable (
    IntCol              number(9) not null,
    StringCol           varchar2(100)
);

Include the below headers, defines and printError( ) function for all the programs shown in this blog post: this code is common for all programs. Make sure that you connect to the same schema where you created the table.

#include <dpi.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>

#define USER           "scott"
#define PASSWORD       "tiger"
#define CONNECT_STRING "localhost/orclpdb"

static dpiContext *gContext = NULL;
static dpiErrorInfo gErrorInfo;

//-----------------------------------------------------------------------------
// printError()
//   Prints the error message. The error is first fetched
// from the global DPI context.
//-----------------------------------------------------------------------------
int printError(void)
{
    if (gContext)
        dpiContext_getError(gContext, &gErrorInfo);
    fprintf(stderr, " [FAILED]\n");
    fprintf(stderr, "    FN: %s\n", gErrorInfo.fnName);
    fprintf(stderr, "    ACTION: %s\n", gErrorInfo.action);
    fprintf(stderr, "    MSG: %.*s\n", gErrorInfo.messageLength,
            gErrorInfo.message);
    fflush(stderr);
    return DPI_FAILURE;
}

Inserting

The program insert.c shows how to perform simple insert operation that adds couple of records to the table.

// insert.c

int main()
{
    const char *insertSql = "insert into TestTable values (:1, :2)";
    dpiData intColValue, stringColValue;
    dpiConn *conn;
    dpiStmt *stmt;

    // create context
    if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext,
            &gErrorInfo) < 0)
        return printError();

    // create connection
    if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD),
            CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0)
        return printError();

    // prepare insert statement for execution
    if (dpiConn_prepareStmt(conn, 0, insertSql, strlen(insertSql), NULL, 0,
            &stmt) < 0)
        return printError();

    // create first row
    dpiData_setInt64(&intColValue, 1);
    if (dpiStmt_bindValueByPos(stmt, 1, DPI_NATIVE_TYPE_INT64,
            &intColValue) < 0)
        return printError();
    dpiData_setBytes(&stringColValue, "Test data 1", strlen("Test data 1"));
    if (dpiStmt_bindValueByPos(stmt, 2, DPI_NATIVE_TYPE_BYTES,
            &stringColValue) < 0)
        return printError();
    if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0)
        return printError();

    // create second row
    dpiData_setInt64(&intColValue, 2);
    if (dpiStmt_bindValueByPos(stmt, 1, DPI_NATIVE_TYPE_INT64,
            &intColValue) < 0)
        return printError();
    dpiData_setBytes(&stringColValue, "Test data 2", strlen("Test data 2"));
    if (dpiStmt_bindValueByPos(stmt, 2, DPI_NATIVE_TYPE_BYTES,
            &stringColValue) < 0)
        return printError();
    if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0)
        return printError();

    // commit changes
    if (dpiConn_commit(conn) < 0)
        return printError();

    // cleanup
    dpiStmt_release(stmt);
    dpiConn_release(conn);

    return DPI_SUCCESS;
}

Remember to add the headers, defines and printError() to insert.c before compiling it. I recommend using the Makefile or Makefile.win32 from the samples directory to help you compile.

Let us walk through each function of the code:

  1. Create the ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create a standalone connection to the database (dpiConn_create()).

  3. Prepare a statement for execution (dpiConn_prepareStmt()).

  4. Populate data in structures and bind it to the statement (dpiData_setInt64(), dpiData_setBytes(), dpiStmt_bindValueByPos()).

  5. Execute the statement (dpiStmt_execute()).

  6. Repeat steps 4 and 5 for the second row.

  7. Commit the data (dpiConn_commit()).

  8. Clean up (dpiStmt_release(), dpiConn_release()).

Inserting multiple records

In insert.c we saw how to insert single record at a time. If you want to insert multiple records into a table you can use the dpiStmt_executeMany() function. In insertmany.c we will add multiple records to the table more efficiently by using an array bind operation.

// insertmany.c

int main()
{
    const char *insertSql = "insert into TestTable values (:1, :2)";
    dpiData *intData, *strData;
    uint32_t numRows = 5, i;
    dpiVar *intVar, *strVar;
    char buffer[100];
    dpiConn *conn;
    dpiStmt *stmt;

    // create context
    if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext,
            &gErrorInfo) < 0)
        return printError();

    // create connection
    if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD),
            CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0)
        return printError();

    // prepare and bind insert statement
    if (dpiConn_prepareStmt(conn, 0, insertSql, strlen(insertSql), NULL, 0,
            &stmt) < 0)
        return printError();
    if (dpiConn_newVar(conn, DPI_ORACLE_TYPE_NUMBER, DPI_NATIVE_TYPE_INT64,
            numRows, 0, 0, 0, NULL, &intVar, &intData) < 0)
        return printError();
    if (dpiStmt_bindByPos(stmt, 1, intVar) < 0)
        return printError();
    if (dpiConn_newVar(conn, DPI_ORACLE_TYPE_VARCHAR, DPI_NATIVE_TYPE_BYTES,
            numRows, 100, 1, 0, NULL, &strVar, &strData) < 0)
        return printError();
    if (dpiStmt_bindByPos(stmt, 2, strVar) < 0)
        return printError();

    // populate some dummy data
    for (i = 0; i < numRows; i++) {
        dpiData_setInt64(&intData[i], i + 1);
        sprintf(buffer, "Test data %d", i + 1);
        if (dpiVar_setFromBytes(strVar, i, buffer, strlen(buffer)) < 0)
             return printError();
    }

    // perform execute many
    if (dpiStmt_executeMany(stmt, DPI_MODE_EXEC_DEFAULT, numRows) < 0)
        return printError();

    // commit changes
    if (dpiConn_commit(conn) < 0)
        return printError();

    // cleanup
    if (dpiVar_release(intVar) < 0)
        return printError();
    if (dpiVar_release(strVar) < 0)
        return printError();
    dpiStmt_release(stmt);
    dpiConn_release(conn);

    return DPI_SUCCESS;
}

Let us walk through each function of the code:

  1. Create ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create standalone connection to the database (dpiConn_create()).

  3. Prepare statement for execution (dpiConn_prepareStmt()).

  4. Create variables and bind them to the statement by position. (dpiConn_newVar(), dpiStmt_bindByPos()).

  5. Populate data into bound variables (dpiData_setInt64(), dpiVar_setFromBytes()).

  6. Execute the statement by specifying the number of times (dpiStmt_executeMany()).

  7. Commit the data (dpiConn_commit()).

  8. Clean up (dpiVar_release(), dpiStmt_release(), dpiConn_release()).

Fetching

So far we have seen how to insert data into a table. In the following program we will see how to fetch records from the table. In order to make sure some records exist in the table run insertmany before running fetch.

// fetch.c

int main()
{
    const char *selectSql = "select IntCol, StringCol from TestTable";
    dpiData *intColValue, *stringColValue;
    dpiNativeTypeNum nativeTypeNum;
    uint32_t bufferRowIndex;
    dpiConn *conn;
    dpiStmt *stmt;
    int found;

    // create context
    if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext,
            &gErrorInfo) < 0)
        return printError();

    // create connection
    if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD),
            CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0)
        return printError();

    // prepare select statement
    if (dpiConn_prepareStmt(conn, 0, selectSql, strlen(selectSql), NULL, 0,
            &stmt) < 0)
        return printError();
    if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, NULL) < 0)
        return printError();

    // fetch rows
    while (1) {
        if (dpiStmt_fetch(stmt, &found, &bufferRowIndex) < 0)
            return printError();
        if (!found)
            break;
        if (dpiStmt_getQueryValue(stmt, 1, &nativeTypeNum, &intColValue) < 0)
            return printError();
        if (dpiStmt_getQueryValue(stmt, 2, &nativeTypeNum, &stringColValue) < 0)
            return printError();
        printf("Int = %ld  String = '%.*s'\n", intColValue->value.asInt64,
                stringColValue->value.asBytes.length,
                stringColValue->value.asBytes.ptr);
    }

    // cleanup
    dpiStmt_release(stmt);
    dpiConn_release(conn);

    return DPI_SUCCESS;
}

When I run the program, the output is:

Int = 1  String = 'Test data 1'
Int = 2  String = 'Test data 2'
Int = 3  String = 'Test data 3'
Int = 4  String = 'Test data 4'
Int = 5  String = 'Test data 5'

Let us walk through each function of the code:

  1. Create ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create standalone connection to the database (dpiConn_create()).

  3. Prepare statement for execution (dpiConn_prepareStmt()).

  4. Execute the statement (dpiStmt_execute()).

  5. Fetch the row and get column values (dpiStmt_fetch(), dpiStmt_getQueryValue()).

  6. Clean up (dpiStmt_release(), dpiConn_release()).

Scrolling

In the above example rows are retrieved in order from the statement until the rows are exhausted.

Now let us see an example on how to scroll between rows using the dpiStmt_scroll() function. To make sure some records exist in the table, run insertmany before running this program.

// scroll.c

int main()
{
    const char *sql = "select IntCol from TestTable order by IntCol";
    dpiNativeTypeNum nativeTypeNum;
    uint32_t numQueryColumns;
    uint32_t bufferRowIndex;
    dpiData *data;
    dpiStmt *stmt;
    dpiConn *conn;
    int found;

    // create context
    if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &gContext,
            &gErrorInfo) < 0)
        return printError();

    // create connection
    if (dpiConn_create(gContext, USER, strlen(USER), PASSWORD, strlen(PASSWORD),
            CONNECT_STRING, strlen(CONNECT_STRING), NULL, NULL, &conn) < 0)
        return printError();

    // prepare select statement
    if (dpiConn_prepareStmt(conn, 1, sql, strlen(sql), NULL, 0, &stmt) < 0)
        return printError();

    // number of rows fetched from the database
    if (dpiStmt_setFetchArraySize(stmt, 5) < 0)
        return printError();
    if (dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, &numQueryColumns) < 0)
        return printError();

    // set the relative position to scroll
    if (dpiStmt_scroll(stmt, DPI_MODE_FETCH_RELATIVE, 4, 0) < 0)
        return printError();

    // fetch the row
    if (dpiStmt_fetch(stmt, &found, &bufferRowIndex) < 0)
        return printError();
    if (dpiStmt_getQueryValue(stmt, 1, &nativeTypeNum, &data) < 0)
        return printError();
    printf("\nIntCol = %ld\n", data->value.asInt64);

    // cleanup
    dpiStmt_release(stmt);
    dpiConn_release(conn);

    return DPI_SUCCESS;
}

When I run scroll the output is:

   IntCol = 5

Let us walk through each function of the code:

  1. Create ODPI-C context which is the first function that must be called in all ODPI-C applications (dpiContext_create()).

  2. Create standalone connection to the database (dpiConn_create()).

  3. Prepare statement for execution (dpiConn_prepareStmt()).

  4. Set the fetch array size (dpiStmt_setFetchArraySize()).

  5. Execute the statement (dpiStmt_execute()).

  6. Scroll the statement to the specified position (dpiStmt_scroll()).

  7. Fetch the row and get column value (dpiStmt_fetch(), dpiStmt_getQueryValue()).

  8. Clean up (dpiStmt_release(), dpiConn_release()).

Wrap up

ODPI-C has a number of advanced features letting you create powerful applications.

For more ODPI-C tests and samples visit the tests and samples directories.

For complete documentation details visit the ODPI-C documentation page.

If you have questions, feel free to contact us on GitHub.

Vaddeti Karthik Raju is a Senior Member Technical Staff for Oracle Bengaluru. He contributes to a number of projects, including ODPI-C.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.