ocilib_demo.c

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

}

/* ------------------------------------------------------------------------ *
 * test_format
 * ------------------------------------------------------------------------ */

void test_format(void)
{
    int code = 1;

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

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

    rs = OCI_GetResultset(st);

    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_immediate
 * ------------------------------------------------------------------------ */

void test_immediate(void)
{
    int code = 1;

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

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

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

/* ------------------------------------------------------------------------ *
 * test_immediate_format
 * ------------------------------------------------------------------------ */

void test_immediate_format(void)
{
    int code = 1;

    print_text("\n>>>>> TEST IMMEDIATE FORMATTED\n\n");

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

    print_text("> article : "); print_dstr(temp);  print_text("\n");
}

/* ------------------------------------------------------------------------ *
 * test_fetch
 * ------------------------------------------------------------------------ */

void test_fetch(void)
{
    int i, n;

    print_text("\n>>>>> SIMPLE TEST FETCH WITH META DATA\n\n");

    /* execute query in one go */
    OCI_ExecuteStmt(st, MT("select * from test_fetch"));

    rs = OCI_GetResultset(st);
    n  = OCI_GetColumnCount(rs);

    /* print resultset columns info */
    for(i = 1; i <= n; i++)
    {
        OCI_Column *col = OCI_GetColumn(rs, i);

        print_frmt("> Field : #%i ", i);
        print_text("- Name  : "); print_mstr(OCI_ColumnGetName(col));
        print_text("\n");
    }

    print_text("\n");

    /* 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));
}

/* ------------------------------------------------------------------------ *
 * test_bind1
 * ------------------------------------------------------------------------ */

void test_bind1(void)
{
    int code = 1;

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

    /* execute query in three steps */
    OCI_Prepare(st, MT("select * from test_fetch where code = :code"));
    OCI_BindInt(st, MT(":code"), &code);
    OCI_Execute(st);

    rs = OCI_GetResultset(st);

    /* 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));
}

/* ------------------------------------------------------------------------ *
 * 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 ! "), 26);
        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;