Strange behaviour with XMLTYPE

[unficyp]

Hi, using 4.7.6 - not quite sure if XMLTYPE is supported, but:

#include <iostream>
#include <unistd.h> 

#include "ocilib.hpp"

using namespace ocilib;

int main(void)
{
        Environment::Initialize();
        Environment::EnableWarnings(true);

        std::cout << "Compile-Version: " << 
                Environment::GetCompileMajorVersion() << "." <<
                Environment::GetCompileMinorVersion() << "." <<
                Environment::GetCompileRevisionVersion() << std::endl;

        std::cout << "Runtime-Version: " << 
                Environment::GetRuntimeMajorVersion() << "." <<
                Environment::GetRuntimeMinorVersion() << "." <<
                Environment::GetRuntimeRevisionVersion() << std::endl;

        std::cout << "OCILIB: " << 
                OCILIB_MAJOR_VERSION << "." << 
                OCILIB_MINOR_VERSION << "." << 
                OCILIB_REVISION_VERSION << std::endl;

        Connection con("db", "user", "pwd");
        std::cout << "Connected to: " << con.GetServerMajorVersion() << "." <<
                con.GetServerMinorVersion() << "." << con.GetServerRevisionVersion() << std::endl;

        std::cout << std::endl;

        Statement st(con);
    try
    {

        std::cout << "try 1 *********************************************" << std::endl;
        std::cout << "*execute" << std::endl;
        st.Execute("SELECT XMLELEMENT(\"element\",xmlattributes(('name') as name, ('str') as \"type\")) as xml from dual");

        std::cout << "*getresultset" << std::endl;
        Resultset rs = st.GetResultset();
        std::cout << "*getcolumn" << std::endl;
        Column col = rs.GetColumn(1);

        std::cout << "Name: " << col.GetName() << std::endl;
        std::cout << "Type: " << col.GetType() << std::endl;
        std::cout << "SubType: " << col.GetSubType() << std::endl;
        std::cout << "SQLType: " << col.GetSQLType() << std::endl;
    }
    catch (ocilib::Exception &ex)
    {
        std::cout << ex.GetMessage() << std::endl;
    }

        std::cout << std::endl;

    try
    {
        std::cout << "try 2 *********************************************" << std::endl;

        st.Execute("SELECT XMLELEMENT(\"element\",xmlattributes(('name') as name, ('str') as \"type\")) as xml from dual");
        Resultset rs = st.GetResultset();

        Column col = rs.GetColumn(1);

        std::cout << "Name: " << col.GetName() << std::endl;
        std::cout << "Type: " << col.GetType() << std::endl;
        std::cout << "SubType: " << col.GetSubType() << std::endl;
        std::cout << "SQLType: " << col.GetSQLType() << std::endl;
    }
    catch (ocilib::Exception &ex)
    {
        std::cout << ex.GetMessage() << std::endl;
    }

    Environment::Cleanup();

    return EXIT_SUCCESS;
}

gives:

Compile-Version: 19.6.0
Runtime-Version: 19.6.0
OCILIB: 4.7.6
Connected to: 19.18.0

try 1 *********************************************
*execute
*getresultset
Error occurred at OcilibTypeInfoGet: Oracle data type (sql code 58) not supported for this operation 

try 2 *********************************************
Name: XML
Type: 12
SubType: 0
SQLType: XMLTYPE

why does this fail for the first but work for the second try ?

regards gerald

[vrogier]

Hi,

thanks for reporting the issue. XMLTYPE is not supported yet by OCILIB. but your example show a real bug in OCILIB a type info creation fails, it is not removed anymore since v4.7.4 from the internal list of type info objects. That's why the second iteration succeeds but uses an incomplete type info object.

I will commit a fix for this (resulting both iteration will throw the exception) by the end of the weekend.

Then I will check for XMLTYPE but few years ago, when I checked for it, it required to implement the whole client OCI side XML API.

Regards,

Vincent

[unficyp]

thanks for the explanation, but wouldn't it be a better idea to return a simple/complete type info for now until xmltype is fully implemented?

From my tool which i'm currently building -pov: user enters query with xmltype and gets an non-oracle exception because the datatype is not supported. in my case i would rather display the type and tell the user (when he tries to open the column like a char/lob type): sorry, the type is xmltype but any further processing is not yet implemented.

regards, gerald

[vrogier]

Hi'

Yes, that's what I had in mind when you posted the issue. I will first commit the fix for the regression that does not remove the type info from the connection's list of type info in case of error while building the type info. Then I will add support for creating a type info object for xmltype but need to find out the right way to handle it when fetching row values (mapping it to string/clob or reporting an exception).

Vincent

[vrogier]

Hi,

Thus, some progress here. I have added in v4.7.7 partial support for XMLTYPE :) Now column metadata reports "XMLTYPE" for such column. XMLTYPE column can now be also fetched using OCI_Long/ocilib::Clong or even just simply by calling OCI_GetString() / resultset::Get().

Only fetching XMLTYPE is supported. As for completely support them, must use the Oracle Client XML C API.

Let me know if there are any issues with this commit :)

Regards,

Vincent

[vrogier]

see https://github.com/vrogier/ocilib/blob/develop-v4.7.7/tests/TestREportedIssuesCppApi.cpp

TEST(ReportedIssuesCppApi, Issue331)
{ 
    auto expectedString = ToUpper(ostring(OTEXT("<element name=\"name\" type=\"str\"></element>")));

    Environment::Initialize();
    Environment::EnableWarnings(true);

    Connection con(DBS, USR, PWD);
    Statement st(con);
    st.Execute(OTEXT("SELECT XMLELEMENT(\"element\",xmlattributes(('name') as name, ('str') as \"type\")) as xml from dual"));

    auto rs = st.GetResultset();
    rs.Next();

    auto col = rs.GetColumn(1);
    ASSERT_EQ(ostring(OTEXT("XMLTYPE")), col.GetSQLType());
    ASSERT_EQ(ostring(OTEXT("XMLTYPE")), col.GetFullSQLType());
    ASSERT_EQ(DataTypeValues::TypeLong, col.GetType());
    ASSERT_EQ(LongTypeValues::LongCharacter, col.GetSubType());

    ASSERT_EQ(expectedString, ToUpper(rs.Get<Clong>(1).GetContent()));
    ASSERT_EQ(expectedString, ToUpper(rs.Get<ostring>(1)));

    Environment::Cleanup();
}

[vrogier]

I will have to do further testing but its seems good so far.

[unficyp]

Just tried the new branch and it works, thanks! (Though scrollable resultsets are now not working because of the LON G but thats ok for now :) )

[vrogier]

I completely forgot the constraint about LONG with scrollable cursors.

I went with LONG + dynamic fetching as:

• Host Lobs cannot be used on client side for XMLTYPE (that's weird)
• Host string buffer can be used but have regular string size limitations.

I will also check selecting 2 XMLTYPE as LONG with scrollable cursors (as documentation does not clearly mention server side columns types of host defined placeholder buffers types for columns).

I will also check if using dynamic fetching with an intern host string buffer instead a long object works with scrollable cursor as OCI documentation is not clear about this combination.

I will let you know asap.

Regards,

Vincent

[vrogier]

I made a test selecting 2 XMLTYPES with scrollable cursors and it works :). Thus having 1 LONG columns for scrollable cursors is only on the server side column type, not the local host mapped type.

I will add these information to OCILIB documentation

Regards,

Vincent

[unficyp]

ah i see :) rewrote a bit of my code now and scolling is working. will try the fetch later but on the console this also works.

[unficyp]

Quick observation: Table with 10 Rows, Fetchsize set to 20 returns 55 for rs.GetCount() after the first rs++. Will check if my code does something wrong - but for tables with other datatype this works

[vrogier]

Hi,

After thinking about it, I will design xlmtype support differently from what I committed (to quickly).

Instead of relying on OCI_Long, I will create a new type OCI_Xml / OCI_CDT_XML and add the following method to the C API: OCI_XmlCreate() OCI_XmlFree() OCI_XmlGetContent() OCI_XmlSetContent()

For the C++ API, I will add a new class ocilib::xml that will wraps OCI_Xml.

The reasoning is to be able to extend later XML support with DOM manipulation and integrate internally oracle XML library. While with current commit, it will not be possible and I don't to add something that I will have break soon.

That also means that v4.7.7 will be become v4.8.0 as there are API additions .

I will commit this during the week.

Regards,

Vincent

[unficyp]

Sounds like a plan :)

[vrogier]

Hi,

I committed the changes (internal changes were bigger than expected, but public API changes reduced). Check the changelog in v4.7.7. branch for more details.

Vincent

[vrogier]

Quick observation: Table with 10 Rows, Fetchsize set to 20 returns 55 for rs.GetCount() after the first rs++. Will check if my code does something wrong - but for tables with other datatype this works

Any news on this subject ?

[unficyp]

Hi,

I committed the changes (internal changes were bigger than expected, but public API changes reduced). Check the changelog in v4.7.7. branch for more details.

Vincent

Just tested it a bit - seems to work, thanks :)

[unficyp]

Quick observation: Table with 10 Rows, Fetchsize set to 20 returns 55 for rs.GetCount() after the first rs++. Will check if my code does something wrong - but for tables with other datatype this works

Any news on this subject ?

sorry, forgot (with branch version 4.7.7):

#include <iostream>
#include "ocilib.hpp"

int main()
{

    std::cout << "OCIlib: " << OCILIB_MAJOR_VERSION << "." << OCILIB_MINOR_VERSION << "." << OCILIB_REVISION_VERSION << std::endl;

    try
    {
        ocilib::Environment::Initialize(ocilib::Environment::Threaded);
    }
    catch(ocilib::Exception &ex)
    {
        std::cout << ex.GetMessage() << std::endl;
        exit(-1);
    }

    ocilib::Connection con("db", "user", "pwd");
    std::cout << "Connected to: " << con.GetServerMajorVersion() << "." <<
                                 con.GetServerMinorVersion() << "." << con.GetServerRevisionVersion() << "\n";

    ocilib::Statement st(con);

    st.SetFetchMode(ocilib::Statement::FetchScrollable);
    st.SetFetchSize(20);
    st.Prepare("SELECT level lvl, XMLELEMENT(\"element\",xmlattributes(('name') as name, ('str') as \"type\")) as xml from dual connect by level<=10");
    st.ExecutePrepared();

    ocilib::Resultset rs = st.GetResultset();
    std::cout << "1: rs count: " << rs.GetCount() << std::endl;

    while (rs++) {
        std::cout << "ROW: " <<  rs.GetCurrentRow() << std::endl;

        for (unsigned int c = 1; c < rs.GetColumnCount() + 1; c++) {
            ocilib::Column col = rs.GetColumn(c);
        }
    }
    std::cout << "2: rs count: " << rs.GetCount() << std::endl;

    con.Close();
    ocilib::Environment::Cleanup();

    return 0;
}

gives: ... 2: rs count: 55

If i disable scrolling resultsets i get 10.

regards, gerald

[vrogier]

Hi,

I reproduced the issue. This is due to the combination scrollable cursors + dynamic piecewise fetching used for retrieving of xml content. OCI statement handle attribute OCI_ATTR_CURRENT_POSITION reports crazy values when using piecewise fetching. Up to now, OCILIB relied on this attribute only when using scrollable cursors. In this case, dynamic piecewise fetching was not used as it was only used for fetching service side LONG columns that are not supported with scrollable cursors. It seems that combination is not well supported and defined in OCI. I will work on a workaround asap to compute the value of OCI_GetRowCount() not using OCI_ATTR_CURRENT_POSITION .

Vincent

[vrogier]

Hi,

I created a v4.8.0 branch with updates for XMLTYPE that will fix your scrollable statement issues. I made changes to the public API (see changelog).

Regards,

Vincent

[unficyp]

Tested a bit, this:

#include <iostream>

#include "ocilib.hpp"

int main()
{

    std::cout << "OCIlib: " << OCILIB_MAJOR_VERSION << "." << OCILIB_MINOR_VERSION << "." << OCILIB_REVISION_VERSION << std::endl;

    try
    {
        ocilib::Environment::Initialize(ocilib::Environment::Threaded);
    }
    catch(ocilib::Exception &ex)
    {
        std::cout << ex.GetMessage() << std::endl;
        exit(-1);
    }

    ocilib::Connection con("db", "user", "pwd");
    std::cout << "Connected to: " << con.GetServerMajorVersion() << "." <<
                                 con.GetServerMinorVersion() << "." << con.GetServerRevisionVersion() << "\n";

    ocilib::Statement st_ddl(con);
    try {
        st_ddl.Execute("drop table xmltest");
    } catch (ocilib::Exception &e) {

    }

    st_ddl.Execute("create table xmltest (xml xmltype, id number)");
    st_ddl.Execute("Insert into xmltest (XML,ID) values (null,'1')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values (null,'2')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'3')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'4')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values (null,'5')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'6')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'7')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'8')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values (null,'9')");
    st_ddl.Execute(" Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'10')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'11')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'12')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'13')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'14')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'15')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'16')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'17')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'18')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'19')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'20')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'21')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'22')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'23')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'24')");
    st_ddl.Execute("Insert into xmltest (XML,ID) values ('<element NAME=\"name\" type=\"str\"/>'||CHR(10),'25')");
    con.Commit();

    ocilib::Statement st(con);
    st.SetFetchSize(10);
    st.Execute("select id,xml,length(xml) len from xmltest");

    ocilib::Resultset rs = st.GetResultset();
    while (rs++)
    {
        std::cout << "id:" << rs.Get<int>(1) << std::endl;
    }
    std::cout << "=> Total fetched rows : " << rs.GetCount() << std::endl;
    try {
        st_ddl.Execute("drop table xmltest");
    } catch (ocilib::Exception &e) {
    }
    ocilib::Environment::Cleanup();

    return 0;
}

gives:

OCIlib: 4.8.0
Connected to: 19.18.0
terminate called after throwing an instance of 'ocilib::Exception'
  what():  Error occurred at OcilibResultsetFetchData: ORA-01405: fetched column value is NULL

Process finished with exit code 134 (interrupted by signal 6: SIGABRT)

on my system with SetFetchSize(10) (happens at rs++) If i do st.SetFetchSize(30) it works.

[vrogier]

By the way, I pushed another commit today.

About your issue, adding test on NULL was next on my todo. I will check that asap and will let you know.

[vrogier]

Fix committed for handling NULL in dynamic fetching. Test suite also updated.

Regards,

Vincent

[unficyp]

thanks, that works now :) gerald