OCI executeStmt, immediate - with format

Detailed Description

OCILIB offers some smart routines that takes a variable number of arguments in order to minimize OCILIB function calls and reduce the amount of code lines

On Windows platforms, the target programming language must support the __cdecl calling convention

Note:

OCI_Immediate() and OCI_ImmediateFmt() support all OCILIB supported types for output result, except :

• OCI_Long
• OCI_Statement If a query output result contains one of these unsupported types, the function returns FALSE

In the parameter list, every output placeholder MUST be preceded by an integer parameter that indicates the type of the placeholder in order to handle correctly the given pointer.

Possible values for indicating placeholders type :

• OCI_ARG_SHORT ------> short *
• OCI_ARG_USHORT -----> unsigned short *
• OCI_ARG_INT --------> int *
• OCI_ARG_UINT -------> unsigned int*
• OCI_ARG_BIGINT -----> big_int *
• OCI_ARG_BIGUINT ----> unsigned big_int *
• OCI_ARG_DOUBLE ----> double *
• OCI_ARG_TEXT -------> dtext *
• OCI_ARG_RAW --------> void *
• OCI_ARG_DATETIME ---> OCI_Date *
• OCI_ARG_LOB --------> OCI_Lob *
• OCI_ARG_FILE -------> OCI_File *
• OCI_ARG_TIMESTAMP --> OCI_Timstamp *
• OCI_ARG_INTERVAL ---> OCI_Interval *
• OCI_ARG_OBJECT -----> OCI_Object *
• OCI_ARG_COLLECTION -> OCI_Coll *
• OCI_ARG_REF --------> OCI_Ref *

Note:

For output strings and Raws, returned data is copied to the given buffer instead of returning a pointer the real data. So these buffers must be big enough to hold the column content. No size check is performed.

• For strings, only the real string is copied.
• For Raws, the number of bytes copied is the column size

Warning:

Input parameters for formatted function only support a restricted set of datatypes !

Supported input identifiers :

• 's' : (dtext *) ----------> input string (quotes are added)
• 'm' : (dtext *) ----------> metadata string (no quotes added)
• 't' : (OCI_Date *) -------> Date
• 'p' : (OCI_Timestamp *) --> timestamp
• 'v' : (OCI_Interval *) ---> Interval
• 'i' : (int) --------------> signed 32 bits integer
• 'u' : (unsigned int) -----> unsigned 32 bits integer
• 'li' : (big_int) ----------> signed 64 bits integer
• 'lu' : (big_uint) ---------> unsigned 64 bits integer
• 'hi' : (short) ------------> signed 16 bits integer
• 'hu' : (unsigned short) ---> unsigned 16 bits integer
• 'g' : (double ) ----------> Numerics
• 'r' : (OCI_Ref *) --------> Reference
• 'o' : (OCI_Object *) -----> Object (not implemented yet)
• 'c' : (OCI_Coll *) -------> collection (not implemented yet)

Example

#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
    OCI_Resultset  *rs;
 
    int code = 1;
    char name[50];
  
    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);
 
    /* sql format with params ----------------------------------------------- */

    OCI_ExecuteStmtFmt(st, "select article from test_fetch where code = %i", code);

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
        printf("article : %s\n", OCI_GetString(rs, 1));

    /* sql immediate (parse, exec, one fetch) ------------------------------- */

    OCI_Immediate(cn, "select code, article from test_fetch where code = 1", 
                  OCI_ARG_INT, &code, OCI_ARG_TEXT, name);

    printf("article : %s - code %i\n", name, code);

    /* sql immediate (parse, exec, one fetch) with params ------------------- */

    
    OCI_ImmediateFmt(cn, "select article from test_fetch where code = %i", 
                          code, OCI_ARG_TEXT, name);

    printf("article : %s\n", name);

    OCI_Cleanup();
 
    return EXIT_SUCCESS;
}

OCI Fetch file

#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement *st;
    OCI_Resultset *rs;
    OCI_File *file;
    char buffer[256];
    int n;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);

    file = OCI_FileCreate(cn, OCI_CFILE);

    OCI_FileSetName(file, "MYDIR", "MyfileName");

    /* check if faile exists */

    if (OCI_FileExists(file))
    {
         printf("file size : %d\n", OCI_FileGetSize(file));
         printf("file dir  : %s\n", OCI_FileGetDirectory(file));
         printf("file name : %s\n", OCI_FileGetName(file));
    }

    /* bind for inserting into table */

    OCI_Prepare(st, "insert into my_bfile_table(code, value) values (1, :bfile)");
    OCI_BindFile(st, ":bfile", file);
    OCI_Execute(st);
    OCI_Commit(cn);

    /* free local file object */

    OCI_FileFree(file),

    /* fetch bfile data from table */

    OCI_ExecuteStmt(st, "select code, value from my_bfile_table");

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        file = OCI_GetFile(rs, 2);

        OCI_FileOpen(file);

        printf("file size  %d\n", OCI_FileGetSize(file));
        printf("file dir   %s\n", OCI_FileGetDirectory(file));
        printf("file name  %s\n", OCI_FileGetName(file));

        while (n = OCI_FileRead(file, buffer, sizeof(buffer)-1))
        {
            buffer[n] = 0;
            printf(buffer);
        }

        OCI_FileClose(file);
    }

    OCI_Cleanup();

    return EXIT_SUCCESS;
}

OCI fetch struct

#include "ocilib.h"

typedef struct product_t
{
    int        code;
    char      *name;
    double     price;
    OCI_Date  *creation;
} product_t;

typedef struct product_ind_t
{
    boolean    code;
    boolean    name;
    boolean    price;
    boolean    creation;
} product_ind_t;


int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
    OCI_Resultset  *rs;

    product_t     prd;
    product_ind_t ind;

    char buf[100];

    int i = 0;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn  = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st  = OCI_StatementCreate(cn);

    OCI_ExecuteStmt(st, "select * from products");

    rs = OCI_GetResultset(st);

    OCI_SetStructNumericType(rs, 1,  OCI_NUM_INT);
    OCI_SetStructNumericType(rs, 3,  OCI_NUM_DOUBLE);
 
    while (OCI_FetchNext(rs))
    {
        i++;

        OCI_GetStruct(rs, &prd, &ind);

        OCI_DateToText(prd.creation, "DD-MM-YYYY", 100, buf);
       
        printf("row #%d              \n"
               "...prd.code     : %d \n"
               "...prd.name     : %s \n"
               "...prd.price    : %g \n"
               "...prd.creation : %s \n"
               "                     \n",
               i, prd.code, prd.name, prd.price, buf
              );
    }

    printf("\n\n%d row(s) fetched\n", OCI_GetRowCount(rs));

    OCI_Cleanup();

    return EXIT_SUCCESS;
}

OCI Prepare and execute - prepare_excute 1step

First, call OCI_StatementCreate() to allocate a statement handle. Then :

• Prepare the SQL with OCI_Prepare()
• Parse and execute it with OCI_Execute()

These two steps can be done together by calling OCI_ExecuteStmt() that prepares and executes in one go.

Example

#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_Statement  *st;
  
    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    st = OCI_StatementCreate(cn);
 
    /* prepare and execute in 2 steps */

    OCI_Prepare(st, "delete from test_fetch where code > 10");
    OCI_Execute(st);

    /* prepare/execute in 1 step */

    OCI_ExecuteStmt(st, "delete from test_fetch where code > 1");

    printf("%d row deleted", OCI_GetAffectedRows(st));

    OCI_Commit(cn);

    OCI_Cleanup();
 
    return EXIT_SUCCESS;
}

OCI Error handler - enable warnings

Example of warning handling

#include "ocilib.h"

void err_handler(OCI_Error *err)
{
    int   err_type = OCI_ErrorGetType(err);
    char *err_msg  = OCI_ErrorGetString(err);

    printf("%s - %s\n", err_type == OCI_ERR_WARNING ? "warning" : "error", err_msg);
}

int main(void)
{
    OCI_Connection *cn;

    if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    OCI_EnableWarnings(TRUE);

    cn = OCI_ConnectionCreate("db", "usr", "expired_pwd_in_grace_period", 
                              OCI_SESSION_DEFAULT);

    /* ... application code here ... */

    OCI_Cleanup();

 
    return EXIT_SUCCESS;
}

OCI Error handler

Example with callbacks

#include "ocilib.h"

void err_handler(OCI_Error *err)
{
    printf(
                "code  : ORA-%05i\n"
                "msg   : %s\n"
                "sql   : %s\n",
                OCI_ErrorGetOCICode(err), 
                OCI_ErrorGetString(err),
                OCI_GetSql(OCI_ErrorGetStatement(err))
           );
}

int main(void)
{
    OCI_Connection *cn;

    if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("wrong_db", "wrong_usr", "wrong_pwd", 
                              OCI_SESSION_DEFAULT);

    /* ... application code here ... */

    OCI_Cleanup();

 
    return EXIT_SUCCESS;
}

Example with thread context

#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT | OCI_ENV_CONTEXT))
        return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("wrong_db", "wrong_usr", "wrong_pwd", 
                              OCI_SESSION_DEFAULT);

    if (cn == NULL)
    {
        OCI_Error *err = OCI_GetLastError();

        printf("errcode %d, errmsg %s", OCI_ErrorGetOCICode(err), 
                                        OCI_ErrorGetString(err));
    }

    OCI_Cleanup();

 
    return EXIT_SUCCESS;
}

OCI Description table

출처 : https://github.com/wxd237/ocilib

#include "ocilib.h"

int main(void)
{
    OCI_Connection *cn;
    OCI_TypeInfo *tbl;
    int i,n;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;

    cn  = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    tbl = OCI_TypeInfoGet(cn, "products", OCI_TIF_TABLE);

    if (tbl != NULL)
    {
        printf ("Column Name         Type      Length  Prec.   Scale   Null ?\n");
        printf ("----------------------------  ------------------------------\n");

        n = OCI_TypeInfoGetColumnCount(tbl);
 
        for(i = 1; i <= n; i++)
        {
            OCI_Column *col = OCI_TypeInfoGetColumn(tbl, i);

            printf("%-20s%-10s%-8i%-8i%-8i%-s\n",
                    OCI_GetColumnName(col),
                    OCI_GetColumnSQLType(col),
                    OCI_GetColumnSize(col),
                    OCI_GetColumnPrecision(col),
                    OCI_GetColumnScale(col),
                    OCI_GetColumnNullable(col) == TRUE ? "Y" : "N");
        }
    }

    OCI_Cleanup();

    return EXIT_SUCCESS;
}