ocilib_demo.c

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

/*
   +----------------------------------------------------------------------+
   |                                                                      |
   |                     OCILIB - C Driver for Oracle                     |
   |                                                                      |
   |                      (C Wrapper for Oracle OCI)                      |
   |                                                                      |
   |                         DEMO SOURCE FILE                             |
   |                                                                      |
   +----------------------------------------------------------------------+
   |              Website : http://orclib.sourceforge.net                 |
   +----------------------------------------------------------------------+
   |    Copyright (c) 2007-2009 Vincent ROGIER <[e-mail]>   |
   +----------------------------------------------------------------------+
   | This library is free software; you can redistribute it and/or        |
   | modify it under the terms of the GNU Library General Public          |
   | License    as published by the Free Software Foundation; either      |
   | version 2 of the License, or (at your option) any later version.     |
   |                                                                      |
   | This library is distributed in the hope that it will be useful,      |
   | but WITHOUT ANY WARRANTY; without even the implied warranty of       |
   | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU    |
   | Library General Public License for more details.                     |
   |                                                                      |
   | You should have received a copy of the GNU Library General Public    |
   | License along with this library; if not, write to the Free           |
   | Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.   |
   +----------------------------------------------------------------------+
   |          Author: Vincent ROGIER <[e-mail]>             |
   +----------------------------------------------------------------------+

*/

#include "ocilib_demo.h"

/* ------------------------------------------------------------------------ *
 * prototypes
 * ------------------------------------------------------------------------ */

void err_handler(OCI_Error *err);
void print_version(void);

void cleanup(void);
void disconnect(void);

void create_tables(void);
void drop_tables(void);

void test_format(void);
void test_immediate(void);
void test_immediate_format(void);
void test_fetch(void);
void test_bind1(void);
void test_bind2(void);
void test_piecewise_insert(void);
void test_piecewise_fetch(void);
void test_lob(void);
void test_nested_table(void);
void test_ref_cursor(void);
void test_plsql(void);
void test_dates(void);
void test_timestamp(void);
void test_describe(void);
void test_returning(void);
void test_returning_array(void);
void test_object_insert(void);
void test_object_fetch(void);
void test_scrollable_cursor(void);
void test_collection(void);
void test_ref(void);
void test_directpath(void);

/* ocilib test functions array */

test_t tab_test[] =
{
        {test_format,            TRUE},
        {test_immediate,         TRUE},
        {test_immediate_format,  TRUE},
        {test_fetch,             TRUE},
        {test_bind1,             TRUE},
        {test_bind2,             TRUE},
        {test_piecewise_insert,  TRUE},
        {test_piecewise_fetch,   TRUE},
        {test_lob,               TRUE},
        {test_nested_table,      TRUE},
        {test_ref_cursor,        TRUE},
        {test_plsql,             TRUE},
        {test_dates,             TRUE},
        {test_timestamp,         TRUE},
        {test_describe,          TRUE},
        {test_returning,         TRUE},
        {test_returning_array,   TRUE},
        {test_object_insert,     TRUE},
        {test_object_fetch,      TRUE},
        {test_scrollable_cursor, TRUE},
        {test_collection,        TRUE},
        {test_ref,               TRUE},
        {test_directpath,        TRUE}
};

/* ------------------------------------------------------------------------ *
 * variables
 * ------------------------------------------------------------------------ */

static OCI_Connection *cn = NULL;
static OCI_Statement  *st = NULL;
static OCI_Resultset  *rs = NULL;

static mtext str[SIZE_STR+1];
static dtext temp[SIZE_STR+1];

static int nb_err = 0;

/* ------------------------------------------------------------------------ *
 * err_handler
 * ------------------------------------------------------------------------ */

void err_handler(OCI_Error *err)
{
    print_text("\n");

    if (OCI_ErrorGetType(err) == OCI_ERR_ORACLE)
    {
        const mtext *sql = OCI_GetSql(OCI_ErrorGetStatement(err));

        if (sql != NULL)
        {
            print_text("> ERROR - SQL : "); print_mstr(sql);
            print_text("\n");
        }
    }

    print_text("> ERROR - MSG : ");
    print_mstr(OCI_ErrorGetString(err));
    print_text("\n");

    nb_err++;
}


/* ------------------------------------------------------------------------ *
 * main
 * ------------------------------------------------------------------------ */

int mtmain(int argc, mtarg* argv[])
{
    mtext home[SIZE_STR] = MT("");
    mtext dbs [SIZE_STR] = MT("");
    mtext usr [SIZE_STR] = MT("");
    mtext pwd [SIZE_STR] = MT("");

    size_t i;

    /* CHECK COMMAND LINE --------------------------------------------------- */

    if (argc < (ARG_COUNT-1))
    {
        return EXIT_FAILURE;
    }

    /* GET ARGUMENTS ---------------------------------------------------------*/

    GET_ARG(dbs, ARG_DB);
    GET_ARG(usr, ARG_USER);
    GET_ARG(pwd, ARG_PWD);

    if(argc == ARG_COUNT)
        GET_ARG(home, ARG_HOME);

    /* INITIALIZE OCI ------------------------------------------------------- */

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

    /* CONNECTION TO SERVER ------------------------------------------------- */

    print_text("Connecting to ");
    print_args(usr);
    print_text("/");
    print_args(pwd);
    print_text("@");
    print_args(dbs);
    print_text("\n\n");

    cn = OCI_ConnectionCreate(dbs, usr, pwd, OCI_SESSION_DEFAULT);

    if (cn)
    {
        st = OCI_StatementCreate(cn);

        print_version();
        create_tables();

        /* execute tests */

        for (i = 0; i < ARRAY_COUNT(tab_test); i++)
        {
            tab_test[i].proc();
        }

        drop_tables();

        disconnect();
    }
    else
    {
        print_mstr(OCI_ErrorGetString(OCI_GetLastError()));
    }

    cleanup();

    print_text("\npress any key to exit...");

    getchar();

    return EXIT_SUCCESS;
}

/* ------------------------------------------------------------------------ *
 * cleanup
 * ------------------------------------------------------------------------ */

void cleanup(void)
{
    OCI_Cleanup();

    print_frmt("\n%i errors encountered\n\n", nb_err);
}

/* ------------------------------------------------------------------------ *
 * disconnect
 * ------------------------------------------------------------------------ */

void disconnect(void)
{
    OCI_ConnectionFree(cn);
}

/* ------------------------------------------------------------------------ *
 * print_version
 * ------------------------------------------------------------------------ */

void print_version(void)
{
    print_text("\n>>>>> OCILIB BUILD INFORMATION \n\n");

    if (OCI_GetImportMode() == OCI_IMPORT_MODE_LINKAGE)
        print_text("OCI import mode         : LINKAGE\n");
    else
        print_text("OCI import mode         : RUNTIME\n");

    if (OCI_GetCharsetMetaData() == OCI_CHAR_ANSI)
        print_text("MetaData  char type     : ANSI\n");
    else
        print_text("MetaData  char type     : UNICODE\n");

    if (OCI_GetCharsetUserData() == OCI_CHAR_ANSI)
        print_text("UserData  char type     : ANSI\n");
    else
        print_text("UserData  char type     : UNICODE\n");

    print_text("\n>>>>> VERSIONS INFORMATION \n\n");

    print_frmt("OCILIB major    version : %i\n",   OCILIB_MAJOR_VERSION);
    print_frmt("OCILIB minor    version : %i\n",   OCILIB_MINOR_VERSION);
    print_frmt("OCILIB revision version : %i\n\n", OCILIB_REVISION_VERSION);

    /* print all versions */
    print_frmt("OCI compile     version : %i\n",   OCI_GetOCICompileVersion());
    print_frmt("OCI runtime     version : %i\n\n", OCI_GetOCIRuntimeVersion());

    print_frmt("Server major    version : %i\n",   OCI_GetServerMajorVersion(cn));
    print_frmt("Server minor    version : %i\n",   OCI_GetServerMinorVersion(cn));
    print_frmt("Server revision version : %i\n\n", OCI_GetServerRevisionVersion(cn));

    print_frmt("Connection      version : %i\n\n", OCI_GetVersionConnection(cn));

    print_text("\n>>>>> SERVER VERSION BANNER \n\n");

    /* print server string version */

    print_mstr(OCI_GetVersionServer(cn));

    print_text("\n\n");

}

/* ------------------------------------------------------------------------ *
 * create_tables
 * ------------------------------------------------------------------------ */

void create_tables(void)
{
    print_text("\n>>>>> CREATE TABLES FOR DEMO \n\n");

    /* create types for the demo */
    OCI_ExecuteStmt(st, MT("create type type_t as OBJECT (id int, name varchar2(50))"));

    OCI_ExecuteStmt(st, MT("create type test_t as object ")
                    MT("( ")
                    MT("    val_int  number, ")
                    MT("    val_flt  float, ")
                    MT("    val_str  varchar2(30), ")
                    MT("    val_date date, ")
                    MT("    val_lob  clob, ")
                    MT("    val_file bfile, ")
                    MT("    val_obj  type_t, ")
                    MT("    val_raw  raw(20) ")
                    MT(")"));

    OCI_ExecuteStmt(st, MT("create type t_tab1_emp as VARRAY(100) of varchar2(50)"));

    OCI_ExecuteStmt(st, MT("create type t_tab2_emp as table of varchar2(50)"));

    /* create table for the demo */
    OCI_ExecuteStmt(st, MT("create table test_fetch(code int, article ")
                        MT("varchar2(30), price float, creation date)"));

    OCI_ExecuteStmt(st, MT("create table test_long_raw(code int, content long raw)"));

    OCI_ExecuteStmt(st, MT("create table test_long_str(code int, content long)"));

    OCI_ExecuteStmt(st, MT("create table test_lob(code int, content CLOB)"));

    OCI_ExecuteStmt(st, MT("create table test_object(val test_t)"));

    OCI_ExecuteStmt(st, MT("create table test_table_obj of type_t"));

    OCI_ExecuteStmt(st, MT("create table test_array ")
                        MT("( ")
                        MT("    val_int  number, ")
                        MT("    val_flt  float, ")
                        MT("    val_str  varchar2(30), ")
                        MT("    val_date date, ")
                        MT("    val_lob  clob, ")
                        MT("    val_file bfile ")
                        MT(")")
                    );

    OCI_ExecuteStmt(st, MT("create table test_coll_varray ")
                        MT("( ")
                        MT("    departement number, ")
                        MT("    employees   t_tab1_emp ")
                        MT(")")
                    );

    OCI_ExecuteStmt(st, MT("create table test_coll_nested ")
                        MT("( ")
                        MT("    departement number, ")
                        MT("    employees   t_tab2_emp ")
                        MT(") nested table employees store as test_table_emp")
                    );

    OCI_ExecuteStmt(st, MT("create table test_directpath(val_int number(8,4), ")
                        MT(" val_str varchar2(30), val_date date)"));

    /* insert data into the demo tables */
    OCI_ExecuteStmt(st, MT("insert into test_fetch ")
                        MT("(code, article, price, creation) ")
                        MT("values (1, 'shoes', 3.14, to_date('1978-12-23', 'YYYY-MM-DD'))"));

    OCI_ExecuteStmt(st, MT("insert into test_fetch ")
                        MT("(code, article, price, creation) ")
                        MT("values (2, 'shirt', 5.99, to_date('1999-09-12', 'YYYY-MM-DD'))"));

    OCI_ExecuteStmt(st, MT("insert into test_lob(code,content)  ")
                        MT("values (1, EMPTY_CLOB())"));

    OCI_ExecuteStmt(st, MT("insert into test_long_str(code,content) ")
                        MT("values (1, 'Rugby rocks !')"));

    OCI_ExecuteStmt(st, MT("insert into test_coll_varray(departement,employees) ")
                        MT("values (1, t_tab1_emp('Peter', 'John', 'Paula', 'Gina'))"));

    OCI_ExecuteStmt(st, MT("insert into test_coll_varray(departement,employees) ")
                        MT("values (2, t_tab1_emp('Ben', 'Alice', 'Joel', 'Maria'))"));

    OCI_ExecuteStmt(st, MT("insert into test_coll_nested(departement,employees) ")
                        MT("values (1, t_tab2_emp('Vince', 'Richard', 'Rita', 'Sophia'))"));

    OCI_ExecuteStmt(st, MT("insert into test_coll_nested(departement,employees) ")
                        MT("values (2, t_tab2_emp('Paul', 'Sarah', 'Robert', 'Zoe'))"));

    OCI_ExecuteStmt(st, MT("insert into test_table_obj values(type_t(1, 'shoes'))"));
    OCI_ExecuteStmt(st, MT("insert into test_table_obj values(type_t(2, 'pen'))"));

    OCI_Commit(cn);
}

/* ------------------------------------------------------------------------ *
 * drop_tables
 * ------------------------------------------------------------------------ */

void drop_tables(void)
{
    print_text("\n>>>>> DROPPING TABLES AND TYPES \n\n");

    OCI_ExecuteStmt(st, MT("drop table test_fetch"));
    OCI_ExecuteStmt(st, MT("drop table test_long_str"));
    OCI_ExecuteStmt(st, MT("drop table test_long_raw"));
    OCI_ExecuteStmt(st, MT("drop table test_lob"));
    OCI_ExecuteStmt(st, MT("drop table test_array"));
    OCI_ExecuteStmt(st, MT("drop table test_object"));
    OCI_ExecuteStmt(st, MT("drop table test_coll_varray"));
    OCI_ExecuteStmt(st, MT("drop table test_coll_nested"));
    OCI_ExecuteStmt(st, MT("drop table test_table_obj"));
    OCI_ExecuteStmt(st, MT("drop table test_directpath"));

    OCI_ExecuteStmt(st, MT("drop type  test_t"));
    OCI_ExecuteStmt(st, MT("drop type  type_t"));
    OCI_ExecuteStmt(st, MT("drop type  t_tab1_emp"));
    OCI_ExecuteStmt(st, MT("drop type  t_tab2_emp"));