ocilib_demo.c

oci的源码,可以在任何平台上编译,相当方便实用

    print_text("\n>>>>> TEST PL/SQL OUTPUT BIND\n\n");

    OCI_Prepare(st, MT("begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;"));
    OCI_BindInt(st, MT(":res"), &res);
    OCI_Execute(st);

    print_text("\nPL/SQL : trunc(sysdate+1)-trunc(sysdate-1)\n");
    print_frmt("\nResult : %i\n", res);

    print_text("\n>>>>> TEST PL/SQL SERVER OUTPUT\n\n");

    /* server output */

    OCI_ServerEnableOutput(cn, 32000, 5, 255);

    OCI_ExecuteStmt(st, MT("begin ")
                        MT("   dbms_output.put_line('First  line'); ")
                        MT("   dbms_output.put_line('Second line'); ")
                        MT("   dbms_output.put_line('Third  line'); ")
                        MT("end;")
                   );

    while ((p = OCI_ServerGetOutput(cn)))
    {
        print_dstr(p);
        print_text("\n");
    }

    OCI_ServerDisableOutput(cn);
}

/* ------------------------------------------------------------------------ *
 * test_dates
 * ------------------------------------------------------------------------ */

void test_dates(void)
{
    OCI_Date *date, *date2;

    print_text("\n>>>>> TEST DATETIME MANIPULATION\n\n");

    date  = OCI_DateCreate(NULL);
    date2 = OCI_DateCreate(NULL);

    OCI_DateFromText(date, MT("1978-04-13 20:20:12"), MT("YYYY-MM-DD HH24:MI:SS"));
    OCI_DateToText(date, MT("YYYY-MM-DD HH24:MI:SS"), SIZE_STR, str);
    print_text("\nDate                     : "); print_mstr(str);

    OCI_DateSysDate(date);
    OCI_DateToText(date, MT("YYYY-MM-DD HH24:MI:SS"), SIZE_STR, str);
    print_text("\nSysdate                  : "); print_mstr(str);

    OCI_DateAddDays(date, 5);
    OCI_DateAddMonths(date, 2);
    OCI_DateToText(date, MT("YYYY-MM-DD HH24:MI:SS"), SIZE_STR, str);
    print_text("\nDate + 5 days + 2 months : "); print_mstr(str);

    OCI_DateAssign(date2, date);
    OCI_DateLastDay(date);
    OCI_DateToText(date, MT("YYYY-MM-DD"), SIZE_STR, str);
    print_text("\nLast day of the month    : "); print_mstr(str);

    print_frmt("\nEnd of the month - date  : %i days",
               OCI_DateDaysBetween(date, date2));
    print_text("\n");

    OCI_DateFree(date);
    OCI_DateFree(date2);
}

/* ------------------------------------------------------------------------ *
 * test_timestamp
 * ------------------------------------------------------------------------ */

void test_timestamp(void)
{
#ifndef OCI_CHARSET_ANSI

    /* Oracle 9i has some troubles with formatting Intervals/timestamps in
       an UTF16 context... */

    if (OCI_GetOCIRuntimeVersion() == OCI_9)
        return;
#endif

    if (OCI_GetOCIRuntimeVersion() >= OCI_9)
    {
        OCI_Timestamp *tmsp;

        print_text("\n>>>>> TEST TIMESTAMP\n\n");

        tmsp = OCI_TimestampCreate(NULL, OCI_TIMESTAMP);

        OCI_TimestampSysTimeStamp(tmsp);

        OCI_TimestampToText(tmsp, MT("YYYY-MM-DD HH24:MI:SS:FF\n"), SIZE_STR, str, 3);


        print_text("Current timestamp            : "); print_mstr(str);

        /* intervals raw oci functions have some troubles with Oracle 9i. So let's
           use it for the demo only if we're using 10g or above */

        if (OCI_GetOCIRuntimeVersion() > OCI_9)
        {
            OCI_Interval *itv;

            print_text("\n>>>>> TEST INTERVAL \n\n");

            itv = OCI_IntervalCreate(NULL, OCI_INTERVAL_DS);

            OCI_IntervalSetDaySecond(itv, 1,1,1,1,0);

            OCI_IntervalToText(itv, 3, 3, SIZE_STR, str);

            print_text("Interval                     : "); print_mstr(str);
            print_text("\n");

            OCI_TimestampIntervalAdd(tmsp, itv);

            OCI_TimestampToText(tmsp, MT("YYYY-MM-DD HH24:MI:SS:FF\n"), SIZE_STR, str, 3);

            print_text("Current timestamp + Interval : "); print_mstr(str);

            OCI_IntervalFree(itv);
        }

        OCI_TimestampFree(tmsp);
    }
}

/* ------------------------------------------------------------------------ *
 * test_describe
 * ------------------------------------------------------------------------ */

void test_describe(void)
{
    OCI_Column   *col;
    OCI_TypeInfo *tbl;

    int i, n;

    print_text("\n>>>>> TEST DESCRIBING TABLE \n\n");

    tbl = OCI_TypeInfoGet(cn, MT("test_fetch"), OCI_TIF_TABLE);

    if (tbl)
    {
        print_text("Column Name         NULL ?  Type                        \n");
        print_text("--------------------------------------------------------\n");

        n = OCI_TypeInfoGetColumnCount(tbl);

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

            OCI_ColumnGetFullSQLType(col, str, SIZE_STR);

#if defined(OCI_CHARSET_UNICODE)
  #if !defined(_WINDOWS)
            printf("%-20ls%-8ls%-30ls\n",
  #else
            wprintf(MT("%-20s%-8s%-30s\n"),
  #endif
#else
            printf("%-20s%-8s%-30s\n",
#endif
                    OCI_ColumnGetName(col),
                    OCI_ColumnGetNullable(col) == TRUE ? MT("Y") : MT("N"),
                    str);
        }
    }

    OCI_TypeInfoFree(tbl);

    /* TEST DESCRIBING TYPE ------------------------------------------------- */

    print_text("\n>>>>> TEST DESCRIBING TYPE \n\n");

    tbl = OCI_TypeInfoGet(cn, MT("test_t"), OCI_TIF_TYPE);

    if (tbl)
    {
        print_text("Column Name         Type                           \n");
        print_text("---------------------------------------------------\n");

        n = OCI_TypeInfoGetColumnCount(tbl);

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

            OCI_ColumnGetFullSQLType(col, str, SIZE_STR);

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

    OCI_TypeInfoFree(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)"), 10);
        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)
{
#ifdef OCI_CHARSET_ANSI

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

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

    /* create types for the demo */