ocilib_demo.c

ORACLE编程的好东西,纯C写的OCI封装.很好用,支持数据池.

            col = OCI_SchemaGetColumn(tbl, i);

            OCI_ColumnGetFullSQLType(col, str, SIZE_STR);

#if defined(OCI_CHARSET_UNICODE) && !defined(_WINDOWS)
            print_mt(MT("%-20ls%-30ls\n")
#else
            print_mt(MT("%-20s%-30s\n"),
#endif
                    OCI_ColumnGetName(col),  str);
        }
    }

    OCI_SchemaFree(tbl);
}

/* ------------------------------------------------------------------------ *
 * test_returning
 * ------------------------------------------------------------------------ */

void test_returning(void)
{    
    OCI_Lob *lob;

    print_text("\n>>>>> TEST RETURNING CLAUSE \n\n");
    
    OCI_Prepare(st, MT("update test_lob set code = code + 1 returning code, content into :i, :l"));
    OCI_RegisterLob(st, MT(":l"), OCI_CLOB);
    OCI_RegisterInt(st, MT(":i"));
    OCI_Execute(st);
 
    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        lob = OCI_GetLob(rs, 2);

        OCI_LobAppend(lob, DT("(modified with OCILIB)"), 22);
        OCI_LobSeek(lob, 0, OCI_SEEK_SET);

        temp[OCI_LobRead(lob, temp, SIZE_STR)] = 0;

        print_frmt("> code : %i - ", OCI_GetInt(rs, 1));
        print_dstr(temp);
        print_text("\n");
   }

    print_frmt("\n%d row(s) fetched\n", OCI_GetRowCount(rs));
  
    OCI_Commit(cn);
}

/* ------------------------------------------------------------------------ *
 * test_returning_array
 * ------------------------------------------------------------------------ */

void test_returning_array(void)
{    
    OCI_Lob *lob;
    OCI_Date *date;
    OCI_File *file;
    int i;

    /* arrays */
    int       tab_int [SIZE_TAB];
    dtext     tab_str [SIZE_TAB][31];
    double    tab_flt [SIZE_TAB];
    OCI_Date* tab_date[SIZE_TAB];
    OCI_Lob*  tab_lob [SIZE_TAB];
    OCI_File* tab_file[SIZE_TAB];

    print_text("\n>>>>> TEST ARRAY BINDING WITH RETURING CLAUSE \n\n");

    /* prepare SQL */

    OCI_Prepare(st, MT("insert into test_array ")
                    MT("( ")
                    MT("   val_int,  val_flt, val_str, val_date, ")
                    MT( "   val_lob, val_file ")
                    MT( ") " )
                    MT( "values ")
                    MT( "( ")
                    MT( "   :val_int, :val_flt, :val_str, :val_date, ")
                    MT( "   :val_lob, :val_file ")
                    MT(") ")
                    MT("returning")
                    MT( "  val_int,  val_flt, val_str, val_date, ")
                    MT("   val_lob, val_file ")
                    MT("into  " )
                    MT( "  :out_int, :out_flt,  :out_str, :out_date, ")
                    MT("   :out_lob, :out_file "));

    /* Set Array Size */

    OCI_BindArraySetSize(st, SIZE_TAB);

    /* bind scalar C types arrays */

    OCI_BindArrayOfInts(st, MT(":val_int"),  tab_int, 0);
    OCI_BindArrayOfDoubles(st, MT(":val_flt"), tab_flt, 0);
    OCI_BindArrayOfStrings(st, MT(":val_str"), (dtext*) tab_str, 30, 0);
    
    /* bind oracle types arrays */

    OCI_BindArrayOfDates(st, MT(":val_date"), tab_date, 0);
    OCI_BindArrayOfLobs(st, MT(":val_lob"), tab_lob, OCI_CLOB, 0);
    OCI_BindArrayOfFiles(st, MT(":val_file"), tab_file, OCI_BFILE, 0);

    /* register C scalar types output */

    OCI_RegisterInt(st, MT(":out_int"));
    OCI_RegisterDouble(st, MT(":out_flt"));
    OCI_RegisterString(st, MT(":out_str"), 30);
    
    /* bind oracle types outputs */

    OCI_RegisterDate(st, MT(":out_date"));
    OCI_RegisterLob(st, MT(":out_lob"), OCI_CLOB);
    OCI_RegisterFile(st, MT(":out_file"), OCI_BFILE);

    /* create objects and set values */

    for(i = 0; i < SIZE_TAB; i++)
    {
        /* scalar types */
        tab_int[i] = i+1;
        tab_flt[i] = 3.14*(double)(i+1);
        sprint_dt(tab_str[i], 30, DT("Name%02i"), i+1);
       
        /* date */
        tab_date[i] = OCI_DateCreate(cn);
        OCI_DateSysDate(tab_date[i]);

        /* lob */
        tab_lob[i] = OCI_LobCreate(cn, OCI_CLOB);
        sprint_dt(temp, SIZE_STR, DT("lob value%02i"), i+1);
        OCI_LobWrite(tab_lob[i], temp, (unsigned int) dtslen(temp)+1);

        /* file */
        tab_file[i] = OCI_FileCreate(cn, OCI_BFILE); 
        sprint_mt(str, SIZE_STR, MT("file%02i.txt"), i+1);
        OCI_FileSetName(tab_file[i], MT("mydir"), str);
    }

    OCI_Execute(st);

    print_frmt("Row inserted : %d\n\n", OCI_GetAffectedRows(st));

    /* free objects */

    for(i=0;i<SIZE_TAB;i++)
    {
        OCI_DateFree(tab_date[i]);
        OCI_LobFree(tab_lob[i]);
        OCI_FileFree(tab_file[i]);
    }

    /* Get back data  from the returning clause
       Obviously, each entry of the array will return a resultset containing
       1 row only (DML insert)
    */

    rs = OCI_GetResultset(st);

    i = 1;

    while (rs != NULL)
    {
        while (OCI_FetchNext(rs))
        {
            print_frmt("Row # %d-------------------\n", i);

            print_frmt(".... val_int    : %i\n", OCI_GetInt2(rs, MT(":OUT_INT")));
            print_frmt(".... val_flt    : %g\n", OCI_GetDouble2(rs, MT(":OUT_FLT")));
            print_text(".... val_str    : "); print_dstr(OCI_GetString2(rs, MT(":OUT_STR")));
            print_text("\n");

            date = OCI_GetDate(rs, 4);
            OCI_DateToText(date, MT("YYYY-MM-DD HH24:MI:SS"), SIZE_STR, str);
            print_text(".... val_date   : "); print_mstr(str);
            print_text("\n");
    
            lob  = OCI_GetLob(rs, 5);
            temp[OCI_LobRead(lob, temp, 100)]=0;
            print_text(".... val_lob    : "); print_dstr(temp);
            print_text("\n");

            file = OCI_GetFile(rs, 6);
            print_text(".... val_file   : "); 
            print_mstr(OCI_FileGetDirectory(file));
            print_text("/");
            print_mstr(OCI_FileGetName(file));
            print_text("\n");

            print_text("\n");
            i++;
        }

        rs = OCI_GetNextResultset(st);
    }
}

/* ------------------------------------------------------------------------ *
 * test_object_insert
 * ------------------------------------------------------------------------ */

void test_object_insert(void)
{

#ifndef OCI_CHARSET_UNICODE

    OCI_Date *date;
    OCI_File *file;
    OCI_Object *obj;
    OCI_Object *obj2;


    print_text("\n>>>>> TEST OBJECT BINDING \n\n");

    /* create types for the demo */

    obj  = OCI_ObjectCreate(cn, OCI_SchemaGet(cn, MT("T_TEST"), OCI_SCHEMA_TYPE));

    OCI_ObjectSetInt(obj, MT("VAL_INT"), 1);
    OCI_ObjectSetDouble(obj, MT("VAL_FLT"), 3.14);
    OCI_ObjectSetString(obj, MT("VAL_STR"), DT("USB KEY 2go"));
    OCI_ObjectSetRaw(obj, MT("VAL_RAW"), DT("AAAAA"), 5 * sizeof(dtext));
 
    date = OCI_ObjectGetDate(obj, MT("VAL_DATE"));
    OCI_DateSysDate(date);
 
    obj2 = OCI_ObjectGetObject(obj, MT("VAL_OBJ"));
    OCI_ObjectSetInt(obj2, MT("CODE"), 1);
    OCI_ObjectSetString(obj2, MT("NAME"), DT("USB KEY 2go"));

    file = OCI_ObjectGetFile(obj, MT("VAL_FILE"));
    OCI_FileSetName(file, MT("mydir"), MT("myfile"));

    OCI_Prepare(st, MT("insert into test_object values(:obj)"));
    OCI_BindObject(st, MT(":obj"), obj);
    OCI_Execute(st);
    
    print_frmt("\n%d row(s) inserted\n", OCI_GetAffectedRows(st));

    OCI_Commit(cn);

    OCI_ObjectFree(obj);

#endif
}

/* ------------------------------------------------------------------------ *
 * test_object_fetch
 * ------------------------------------------------------------------------ */

void test_object_fetch(void)
{
#ifndef OCI_CHARSET_UNICODE

    OCI_Lob *lob;
    OCI_Date *date;
    OCI_File *file;
    OCI_Object *obj;
    OCI_Object *obj2;

    print_text("\n>>>>> TEST OBJECT FETCHING \n\n");

    OCI_ExecuteStmt(st, MT("select val from test_object for update"));

    rs = OCI_GetResultset(st);

    /* print resultset content */
    while (OCI_FetchNext(rs))
    {
        obj  = OCI_GetObject(rs, 1);

        print_frmt("val_int        : %i\n",  OCI_ObjectGetInt(obj, MT("VAL_INT")));
        print_frmt("val_flt        : %g\n",  OCI_ObjectGetDouble(obj, MT("VAL_FLT")));
        print_text("val_str        : "); print_dstr(OCI_ObjectGetString(obj, MT("VAL_STR")));
        print_text("\n");

        temp[OCI_ObjectGetRaw(obj, MT("VAL_RAW"), temp, 20)] = 0;
        print_text("val_raw        : "); print_dstr(temp);
        print_text("\n");
  
        date = OCI_ObjectGetDate(obj, MT("VAL_DATE"));
        OCI_DateToText(date, MT("YYYY-MM-DD HH24:MI:SS"), SIZE_STR, str);
        print_text("val_date       : "); print_mstr(str);
        print_text("\n");

        lob = OCI_ObjectGetLob(obj, MT("VAL_LOB"));
        
        OCI_LobWrite(lob, DT("Hello from Mars !"), 17);
        OCI_LobSeek(lob, 0, OCI_SEEK_SET);
        
        temp[OCI_LobRead(lob, temp, SIZE_STR)]=0;

        print_text("val_clob       : "); print_dstr(temp);
        print_text("\n");

        file = OCI_ObjectGetFile(obj, MT("VAL_FILE"));
        print_text("val_file.dir   : "); print_mstr(OCI_FileGetDirectory(file));
        print_text("\n");
        print_text("val_file.name  : "); print_mstr(OCI_FileGetName(file));
        print_text("\n");

        obj2 = OCI_ObjectGetObject(obj, MT("VAL_OBJ"));
        print_frmt("val_obj.code   : %i\n", OCI_ObjectGetInt(obj2, MT("CODE")));
        print_text("val_obj.name   : "); print_dstr(OCI_ObjectGetString(obj2, MT("NAME")));
        print_text("\n");

        printf("\n");;

    }

    OCI_Commit(cn);

#endif

}

/* ------------------------------------------------------------------------ *
 * test_scrollable_cursor
 * ------------------------------------------------------------------------ */

void test_scrollable_cursor(void)
{
    if (OCI_GetVersionConnection(cn) >= OCI_9)
    {
        print_text("\n>>>>> TEST SCROLLABLE CURSORS \n\n");

        OCI_SetFetchMode(st, OCI_SFM_SCROLLABLE);

        OCI_ExecuteStmt(st, MT("select table_name from user_tables where ")
                            MT("table_name like 'TEST_%' order by table_name"));
       
        rs = OCI_GetResultset(st);
      
        if (OCI_FetchLast(rs))
            print_frmt("... Resultset contains %i rows\n\n", OCI_GetRowCount(rs));

        print_text("... Go to row 1\n");
        if (OCI_FetchFirst(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }
        
        print_frmt("... Enumerate from row 2 to row %i\n", OCI_GetRowCount(rs));
        while (OCI_FetchNext(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_frmt("... Enumerate from row %i back to row 1\n", OCI_GetRowCount(rs)-1);
        while (OCI_FetchPrev(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_text("... Go to the 3th row\n");
        if (OCI_FetchSeek(rs, OCI_SFD_ABSOLUTE,  3))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        print_text("... Fetch the next 2 rows\n");
        while ((OCI_GetCurrentRow(rs) < 5) && OCI_FetchNext(rs))
        {
            print_dstr(OCI_GetString(rs, 1));
            print_text("\n");
        }

        OCI_SetFetchMode(st, OCI_SFM_DEFAULT);
    }
}

void test_collection(void)
{
    OCI_Schema *type;
    OCI_Coll *coll;
    OCI_Iter *iter;
    OCI_Elem *elem;
    int i, n;

    print_text("\n>>>>> TEST VARRAY BINDING \n\n");

    type = OCI_SchemaGet(cn, MT("T_TAB1_EMP"), OCI_SCHEMA_TYPE);
    coll = OCI_CollCreate(type);
    
    OCI_Prepare(st, MT("begin")
                    MT("    select employees into :tab_emp ")
                    MT("    from test_coll_varray ")
                    MT("    where departement = :id; ")
                    MT("end;"));

    OCI_BindColl(st, MT(":tab_emp"), coll);
    OCI_BindInt(st, MT(":id"), &i);

    i = 1;

    OCI_Execute(st);

    iter = OCI_IterCreate(coll);
    elem = OCI_IterGetNext(iter);

    print_frmt("Department ID #%d\n\n", i);

    while (elem != NULL)
    {
        print_text("... Employee : ");
        print_dstr(OCI_ElemGetString(elem));
        print_text("\n");

        elem = OCI_IterGetNext(iter);
    }

    OCI_IterFree(iter);
    OCI_CollFree(coll);
 
    print_text("\n>>>>> TEST VARRAY FETCHING WITH ITERATOR \n\n");
  
    OCI_ExecuteStmt(st, MT("SELECT * from test_coll_varray"));

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        coll = OCI_GetColl(rs, 2);

        iter = OCI_IterCreate(coll);
        elem = OCI_IterGetNext(iter);

        print_frmt("\nDepartment ID #%d\n\n", OCI_GetInt(rs, 1));

        while (elem != NULL)
        {
            print_text("... Employee : ");
            print_dstr(OCI_ElemGetString(elem));
            print_text("\n");

            elem = OCI_IterGetNext(iter);
        }

        OCI_IterFree(iter);
    }

    
    print_text("\n>>>>> TEST NESTED TABLE FETCHING WITH INDEX ACCESS \n\n");

    OCI_ExecuteStmt(st, MT("SELECT * from test_coll_nested"));

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        coll = OCI_GetColl(rs, 2);

        print_frmt("\nDepartment ID #%d\n\n", OCI_GetInt(rs, 1));

        n = OCI_CollGetSize(coll);

        for(i = 1; i <= n; i++)
        {
            elem = OCI_CollGetAt(coll, i);

            print_text("... Employee : ");
            print_dstr(OCI_ElemGetString(elem));
            print_text("\n");

        }
    }
}