ocilib_demo.c

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

    while (OCI_FetchNext(rs))
    {
        print_frmt("> code : %i", OCI_GetInt(rs, 1));
        print_text(", action : "); print_dstr(OCI_GetString(rs, 2));
        print_frmt(", price : %g", OCI_GetDouble(rs,3));
        print_text(", date : "); print_dstr(OCI_GetString(rs, 4));
        print_text("\n");
    }

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

/* ------------------------------------------------------------------------ *
 * test_bind2
 * ------------------------------------------------------------------------ */

void test_bind2(void)
{    
    OCI_Lob *lob;
    OCI_Date *date;
    OCI_File *file;
    
    int i;
    double flt;

    print_text("\n>>>>> SINGLE BINDING \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(") "));

    /* create objects and set values */
   
    /* date */
    date = OCI_DateCreate(cn);
    OCI_DateSysDate(date);

    /* lob */
    lob = OCI_LobCreate(cn, OCI_CLOB);
    sprint_dt(temp, SIZE_STR, DT("lob value00"));
    OCI_LobWrite(lob, temp, (unsigned int) dtslen(temp)+1);

    /* file */
    file = OCI_FileCreate(cn, OCI_BFILE); 
    OCI_FileSetName(file, MT("mydir"), MT("file00.txt"));

    /* scalar types */
    i   = 1;
    flt = 3.14;
    sprint_dt(temp, 30, DT("Name00"));

    /* bind scalar C types arrays */

    OCI_BindInt(st, MT(":val_int"),  &i);
    OCI_BindDouble(st, MT(":val_flt"), &flt);
    OCI_BindString(st, MT(":val_str"), (dtext*) temp, 30);
    
    /* bind oracle types arrays */

    OCI_BindDate(st, MT(":val_date"), date);
    OCI_BindLob(st, MT(":val_lob"), lob);
    OCI_BindFile(st, MT(":val_file"), file);
 
    /* do insert */
    
    OCI_Execute(st);

    print_frmt("Row inserted : %d\n\n", OCI_GetAffectedRows(st));
    
    /* free objets */

    OCI_DateFree(date);
    OCI_LobFree(lob);
    OCI_FileFree(file);

    /* commit; */

    OCI_Commit(cn);
}

/* ------------------------------------------------------------------------ *
 * test_piecewise_insert
 * ------------------------------------------------------------------------ */

void test_piecewise_insert(void)
{    
    FILE *f;

    print_text("\n>>>>> TEST PIECEWISE INSERTING\n\n");

    /* this code could have been used with a text file in a LONG column
       as well...  */

    /* open the app file in for reading*/

    f = fopen(EXE_NAME, "rb");

    if (f)
    {
        int n;
        OCI_Long *lg;
        unsigned char buffer[SIZE_BUF];

        fseek (f , 0 , SEEK_END); 
        n = ftell(f); 
        rewind (f);  
  
        print_frmt("\n%d bytes to write\n", n);

        lg = OCI_LongCreate(st, OCI_BLONG);

        /* execute query in three steps */       
        OCI_Prepare(st,  MT("insert into test_long_raw(code, content) ")
                         MT("values (1, :data)"));
        
        OCI_BindLong(st, MT(":data"), lg, n);
        OCI_Execute(st);

        /* write data into table by chunks of 2048 bytes */       
        while ((n = (int) fread(buffer, 1, sizeof(buffer), f)))
        {
            OCI_LongWrite(lg, buffer, n);
        }

        print_frmt("\n%d bytes written\n", OCI_LongGetSize(lg));
        fclose(f);

        OCI_LongFree(lg);
        OCI_Commit(cn);
    }
}

/* ------------------------------------------------------------------------ *
 * test_piecewise_fetch
 * ------------------------------------------------------------------------ */

void test_piecewise_fetch(void)
{    
    OCI_Long *lg;
    unsigned char buffer[SIZE_BUF];
    int n;

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

    /* execute query in one go */       
    OCI_ExecuteStmt(st, MT("select content from test_long_raw ")
                              MT("where code = 1"));

    rs = OCI_GetResultset(st);

    /* read data by chunks of 2048 bytes*/       
    while (OCI_FetchNext(rs))
    {
        lg = OCI_GetLong(rs, 1);

        while ((n = OCI_LongRead(lg, buffer, sizeof(buffer)))) {}

        print_frmt("\n%d bytes read\n", OCI_LongGetSize(lg));
    }    

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

    print_text("\n>>>>> TEST LONG MAPPED TO STRING\n\n");

    OCI_ExecuteStmt(st, MT("select content from test_long_str ")
                        MT("where code = 1"));
    OCI_SetLongMode(st, OCI_LONG_IMPLICIT);
    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        const dtext *long_str = OCI_GetString(rs, 1);

        print_dstr(long_str);
        print_frmt("\n%d bytes read\n", dtslen(long_str));
    }

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

/* ------------------------------------------------------------------------ *
 * test_lob
 * ------------------------------------------------------------------------ */

void test_lob(void)
{
    OCI_Lob *lob;
    
    print_text("\n>>>>> TEST LOB MANIPULATION\n\n");

    OCI_ExecuteStmt(st, MT("select code, content from test_lob ")
                        MT("where code=1 for update"));
    
    rs = OCI_GetResultset(st);
    
    while (OCI_FetchNext(rs))
    {
        lob  = OCI_GetLob(rs, 2);

        OCI_LobWrite(lob, DT("today, "), 7);
        OCI_LobAppend(lob, DT("i'm going to the cinema !"), 25);
        OCI_LobAppend(lob, DT(" See you later..."), 17);
        OCI_LobSeek(lob, 0, OCI_SEEK_SET);
        
        temp[OCI_LobRead(lob, temp, SIZE_STR)] = 0;

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

    OCI_Commit(cn);

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

/* ------------------------------------------------------------------------ *
 * test_nested_table
 * ------------------------------------------------------------------------ */

void test_nested_table(void)
{    
    print_text("\n>>>>> TEST NESTED TABLE \n\n");

    OCI_ExecuteStmt(st, MT("select article, cursor(select sysdate ")
                        MT("from dual) from test_fetch"));

    rs = OCI_GetResultset(st);

    while (OCI_FetchNext(rs))
    {
        OCI_Statement *st2 = OCI_GetStatement(rs, 2);
        OCI_Resultset *rs2 = OCI_GetResultset(st2);

        while (OCI_FetchNext(rs2))
        {
            print_text("> article : "); print_dstr(OCI_GetString(rs, 1));
            print_text(", date : ");    print_dstr(OCI_GetString(rs2, 1));
            print_text("\n");
        }
    }    
}

/* ------------------------------------------------------------------------ *
 * test_ref_cursor
 * ------------------------------------------------------------------------ */

void test_ref_cursor(void)
{    
    OCI_Statement* st2 = OCI_StatementCreate(cn);

    print_text("\n>>>>> TEST REF CURSOR \n\n");
     
    OCI_Prepare(st, MT("begin open :c for select * from test_fetch; end;"));
    OCI_BindStatement(st, MT(":c"), st2);
    OCI_Execute(st);
     
    rs = OCI_GetResultset(st2);
    
    /* print resultset content */
    while (OCI_FetchNext(rs))
    {
        print_frmt("> code : %i", OCI_GetInt(rs, 1));
        print_text(", action : "); print_dstr(OCI_GetString(rs, 2));
        print_frmt(", price : %g", OCI_GetDouble(rs,3));
        print_text(", date : "); print_dstr(OCI_GetString(rs, 4));
        print_text("\n");
    }

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

    OCI_StatementFree(st2);
}

/* ------------------------------------------------------------------------ *
 * test_plsql
 * ------------------------------------------------------------------------ */

void test_plsql(void)
{
    int res = 0;
    const dtext *p=NULL;

    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_Schema *tbl;

    int i, n;

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

    tbl = OCI_SchemaGet(cn, MT("test_fetch"), OCI_SCHEMA_TABLE);

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

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

            OCI_ColumnGetFullSQLType(col, str, SIZE_STR);

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

    OCI_SchemaFree(tbl);

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

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

    tbl = OCI_SchemaGet(cn, MT("T_TEST"), OCI_SCHEMA_TYPE);

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

        n = OCI_SchemaGetColumnCount(tbl);
   
        for(i = 1; i <= n; i++)
        {