C++ Connection::Commit方法代码示例

void test_piecewise_insert(void)
{
    ocout << otext("\n>>>>> TEST PIECEWISE INSERTING\n\n");

    std::ifstream file(OCI_SHARED_LIB, std::ios::in | std::ios::binary | std::ios::ate);
    if (file.is_open())
    {
        size_t size = static_cast<size_t>(file.tellg());
        file.seekg(0, std::ios::beg);
        ocout << oendl << size << otext(" bytes to write") << oendl;

        Statement st(con);
        Blong lg(st);
        st.Prepare(otext("insert into test_long_raw(code, content) values (1, :data)"));
        st.SetLongMaxSize(static_cast<unsigned int>(size));
        st.Bind(otext(":data"), lg, static_cast<unsigned int>(size), BindInfo::In);
        st.ExecutePrepared();

        unsigned char *strBuffer = new unsigned char[size];
        file.read(reinterpret_cast<char *>(strBuffer), size);
        lg.Write(Raw(strBuffer, strBuffer + size));

        delete[] strBuffer;

        ocout << oendl << lg.GetLength() << otext(" bytes written") << oendl;

        file.close();
        con.Commit();
    }
}

void test_bind2(void)
{
    ocout << otext("\n>>>>> SINGLE BINDING \n\n");

    Statement st(con);
    st.Prepare(otext("insert into test_array ")
        otext("( ")
        otext("   val_int,  val_dbl, val_flt, val_str, val_date, ")
        otext("   val_lob, val_file ")
        otext(") ")
        otext("values ")
        otext("( ")
        otext("   :val_int, :val_dbl, :val_flt, :val_str, :val_date, ")
        otext("   :val_lob, :val_file ")
        otext(") "));

    /* create objects and set values */

    TestArray t;

    t.val_date = Date::SysDate();
    t.val_int  = 1;
    t.val_dbl  = 3.14;
    t.val_flt  = 3.14f;
    t.val_str  = otext("Name00");
    t.val_lob  = Clob(con);
    t.val_file = File(con);

    t.val_lob.Write(otext("lob value00"));
    t.val_file.SetInfos(otext("mydir"), otext("file00.txt"));

    /* bind scalar C types arrays */

    st.Bind(otext(":val_int"), t.val_int, BindInfo::In);
    st.Bind(otext(":val_dbl"), t.val_dbl, BindInfo::In);
    st.Bind(otext(":val_flt"), t.val_flt, BindInfo::In);
    st.Bind(otext(":val_str"), t.val_str, static_cast<unsigned int>(t.val_str.size()), BindInfo::In);

    /* bind oracle types arrays */

    st.Bind(otext(":val_date"), t.val_date, BindInfo::In);
    st.Bind(otext(":val_lob"), t.val_lob, BindInfo::In);
    st.Bind(otext(":val_file"), t.val_file, BindInfo::In);

    /* do insert */

    st.ExecutePrepared();
    con.Commit();

    ocout << oendl << st.GetAffectedRows() << otext(" row(s) inserted") << oendl;
}

void test_object_fetch(void)
{
    ocout << otext("\n>>>>> TEST OBJECT FETCHING \n\n");

    Statement st(con);
    st.Execute(otext("select val from test_object for update"));

    Resultset rs = st.GetResultset();
    while (rs++)
    {
        Object obj = rs.Get<Object>(1);

        ocout << otext(".... val_int      : ") << obj.Get<int>(otext("VAL_INT")) << oendl;
        ocout << otext(".... val_dbl      : ") << obj.Get<double>(otext("VAL_DBL")) << oendl;
        ocout << otext(".... val_flt      : ") << obj.Get<float>(otext("VAL_FLT")) << oendl;
        ocout << otext(".... val_str      : ") << obj.Get<ostring>(otext("VAL_STR")) << oendl;
        ocout << otext(".... val_date     : ") << obj.Get<Date>(otext("VAL_DATE")) << oendl;

        Clob clob = obj.Get<Clob>(otext("VAL_LOB"));
        ocout << otext(".... val_lob      : ") << clob.Read(SizeBuffer) << oendl;

        File file = obj.Get<File>(otext("VAL_FILE"));
        ocout << otext(".... val_file     : ") << file.GetDirectory() << otext("/") << file.GetName() << oendl;

        Raw raw = obj.Get<Raw>(otext("VAL_RAW"));
        raw.push_back(0);
        ocout << otext(".... val_raw      : ") << reinterpret_cast<char *>(raw.data()) << oendl;

        Object obj2 = obj.Get<Object>(otext("VAL_OBJ"));
        ocout << otext(".... val_obj.code : ") << obj2.Get<int>(otext("ID")) << oendl;
        ocout << otext(".... val_obj.name : ") << obj2.Get<ostring>(otext("NAME")) << oendl;
    }

    con.Commit();

    ocout << otext("\n>>>>> TEST OBJECT FETCHING  AS STRING \n\n");

    st.Execute(otext("select val from test_object"));

    rs = st.GetResultset();
    while (rs++)
    {
        ocout << rs.Get<Object>(1) << oendl;
    }
}

void test_object_insert(void)
{
    unsigned char *constData = reinterpret_cast<unsigned char*>(const_cast< char *>("0123456789"));
    Raw rawData(constData, constData + 10);

    ocout << otext("\n>>>>> TEST OBJECT BINDING \n\n");

    Clob clob(con);
    clob.Write(otext("Lob Value"));
    File file(con, otext("mydir"), otext("myfile"));
    Date date = Date::SysDate();

    Object obj2(TypeInfo(con, otext("type_t"), TypeInfo::Type));

    obj2.Set<int>(otext("ID"), 1);
    obj2.Set<ostring>(otext("NAME"), otext("USB KEY 2go"));

    Object obj1(TypeInfo(con, otext("test_t"), TypeInfo::Type));

    obj1.Set<int>(otext("VAL_INT"), 1);
    obj1.Set<double>(otext("VAL_DBL"), 3.14);
    obj1.Set<float>(otext("VAL_FLT"), (float) 3.14);
    obj1.Set<ostring>(otext("VAL_STR"), otext("USB KEY 2go"));
    obj1.Set<Raw>(otext("VAL_RAW"), rawData);
    obj1.Set<Date>(otext("VAL_DATE"), date);
    obj1.Set<Object>(otext("VAL_OBJ"), obj2);
    obj1.Set<Clob>(otext("VAL_LOB"), clob);
    obj1.Set<File>(otext("VAL_FILE"), file);

    Statement st(con);
    st.Prepare(otext("insert into test_object values(:obj)"));
    st.Bind(otext(":obj"), obj1, BindInfo::In);
    st.ExecutePrepared();

    ocout << otext("Rows inserted :  ") << st.GetAffectedRows() << oendl;

    con.Commit();
}

void test_lob(void)
{
    ocout << otext("\n>>>>> TEST LOB MANIPULATION\n\n");

    Statement st(con);
    st.Execute(otext("select code, content from test_lob where code=1 for update"));

    Resultset rs = st.GetResultset();
    while (rs++)
    {
        Clob clob = rs.Get<Clob>(2);

        clob.Write(otext("today, "));
        clob.Append(otext("i'm going to the cinema ! "));
        clob.Seek(SeekSet, 0);

        ocout << otext("> code : ") << rs.Get<int>(1) << otext(", content : ") << clob.Read(SizeString) << oendl;
    }

    con.Commit();

    ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}

void test_returning(void)
{
    ocout << otext("\n>>>>> TEST RETURNING CLAUSE \n\n");

    Statement st(con);
    st.Prepare(otext("update test_lob set code = code + 1 returning code, content into :i, :l"));
    st.Register<int>(otext(":i"));
    st.Register<Clob>(otext(":l"));
    st.ExecutePrepared();

    Resultset rs = st.GetResultset();
    while (rs++)
    {
        Clob clob = rs.Get<Clob>(2);
        clob.Append(otext("(modified)"));
        clob.Seek(SeekSet, 0);

        ocout << otext("> code : ") << rs.Get<int>(1) << otext(" - ") << clob.Read(static_cast<unsigned int>(clob.GetLength())) << oendl;
    }

    con.Commit();

    ocout << oendl << rs.GetCount() << otext(" row(s) fetched") << oendl;
}

void create_tables(void)
{
    ocout << otext("\n>>>>> CREATE TABLES FOR DEMO \n\n");

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

    execute_ddl(otext("create type test_t as object ")
        otext("( ")
        otext("    val_int  number, ")
        otext("    val_flt  float, ")
        otext("    val_dbl  float, ")
        otext("    val_str  varchar2(30), ")
        otext("    val_date date, ")
        otext("    val_lob  clob, ")
        otext("    val_file bfile, ")
        otext("    val_obj  type_t, ")
        otext("    val_raw  raw(10) ")
        otext(")"));

    execute_ddl(otext("create type t_tab1_emp as VARRAY(100) of varchar2(50)"));

    execute_ddl(otext("create type t_tab2_emp as table of varchar2(50)"));

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

    execute_ddl(otext("create table test_long_raw(code int, content long raw)"));

    execute_ddl(otext("create table test_long_str(code int, content long)"));

    execute_ddl(otext("create table test_lob(code int, content clob)"));

    execute_ddl(otext("create table test_object(val test_t)"));

    execute_ddl(otext("create table test_table_obj of type_t"));

    execute_ddl(otext("create table test_array ")
        otext("( ")
        otext("    val_int  number, ")
        otext("    val_dbl  float, ")
        otext("    val_flt  float, ")
        otext("    val_str  varchar2(30), ")
        otext("    val_date date, ")
        otext("    val_lob  clob, ")
        otext("    val_file bfile ")
        otext(")")
        );

    execute_ddl(otext("create table test_coll_varray ")
        otext("( ")
        otext("    departement number, ")
        otext("    employees   t_tab1_emp ")
        otext(")")
        );

    execute_ddl(otext("create table test_coll_nested ")
        otext("( ")
        otext("    departement number, ")
        otext("    employees   t_tab2_emp ")
        otext(") nested table employees store as test_table_emp")
        );

    execute_ddl(otext("create table test_directpath(val_int number(8,4), ")
        otext(" val_str varchar2(30), val_date date)"));

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

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

    execute_ddl(otext("insert into test_lob(code,content)  ")
        otext("values (1, EMPTY_CLOB())"));

    execute_ddl(otext("insert into test_long_str(code,content) ")
        otext("values (1, 'Rugby rocks !')"));

    execute_ddl(otext("insert into test_coll_varray(departement,employees) ")
        otext("values (1, t_tab1_emp('Peter', 'John', 'Paula', 'Gina'))"));

    execute_ddl(otext("insert into test_coll_varray(departement,employees) ")
        otext("values (2, t_tab1_emp('Ben', 'Alice', 'Joel', 'Maria'))"));

    execute_ddl(otext("insert into test_coll_nested(departement,employees) ")
        otext("values (1, t_tab2_emp('Vince', 'Richard', 'Rita', 'Sophia'))"));

    execute_ddl(otext("insert into test_coll_nested(departement,employees) ")
        otext("values (2, t_tab2_emp('Paul', 'Sarah', 'Robert', 'Zoe'))"));

    execute_ddl(otext("insert into test_table_obj values(type_t(1, 'shoes'))"));
    execute_ddl(otext("insert into test_table_obj values(type_t(2, 'pen'))"));

    con.Commit();
}

void test_directpath(void)
{
    /* Some OCI Direct path function fails (segfault) if the OCI version of the
    client is different than the server one.
    It happens with OCI 8i and 9i client. Apparently Oracle 10g and 11g seems to
    have fixed these problems.
    Anyway, we run this test case only if the major versions of client and server
    match
    */
    // if (Environment::GetRuntimeMajorVersion() == con.GetServerMajorVersion())
    {
        /* commit any previous pending modifications */

        con.Commit();

        ocout << otext("\n>>>>> TEST DIRECT PATH (10 loads of 100 rows) \n\n");

        int i = 0, j = 0, n = DirPathLoadSize;

        DirectPath dp(TypeInfo(con, otext("test_directpath"), TypeInfo::Table), DirPathColumnCount, n);

        /* optional attributes to set */

        dp.SetBufferSize(DirPathBufferSize);
        dp.SetNoLog(true);
        dp.SetParallel(true);

        /* describe the target table */

        dp.SetColumn(1, otext("VAL_INT"), DirPathSizeColumn1);
        dp.SetColumn(2, otext("VAL_STR"), DirPathSizeColumn2);
        dp.SetColumn(3, otext("VAL_DATE"), DirPathSizeColumn3, otext("YYYYMMDD"));

        /* prepare the load */

        dp.Prepare();

        n = dp.GetMaxRows();

        for (i = 0; i < DirPathLoadCount; i++)
        {
            dp.Reset();

            for (j = 1; j <= n; j++)
            {
                oostringstream val1, val2, val3;

                /* fill test values */

                val1 << std::setfill(otext('0')) << std::setw(4) << j + (i * DirPathLoadCount);
                val2 << otext("value ") << std::setfill(otext('0')) << std::setw(5) << j + (i * DirPathLoadCount);
                val3 << std::setfill(otext('0')) << std::setw(2) << (j % 23) + 1 + 2000 << std::setw(2) << (j % 11) + 1 << (j % 23) + 1;

                dp.SetEntry(j, 1, val1.str());
                dp.SetEntry(j, 2, val2.str());
                dp.SetEntry(j, 3, val3.str());
            }

            /* load data to the server */
            dp.Convert();
            dp.Load();
        }

        /* commits changes */

        dp.Finish();

        ocout << std::setw(4) << dp.GetRowCount() << otext(" row(s) loaded") << oendl;
    }
}