Strange behaviour with XMLTYPE #331

unficyp · 2023-02-18T12:56:50Z

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

The text was updated successfully, but these errors were encountered:

vrogier · 2023-02-18T18:58:41Z

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 · 2023-02-19T08:52:16Z

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 · 2023-02-19T09:16:03Z

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

Added support for XMLTYPE (fetch only) exposed as OCI_Long / ocilib::Clong

vrogier · 2023-02-19T20:07:07Z

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 · 2023-02-20T08:59:05Z

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 · 2023-02-20T09:00:09Z

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

unficyp · 2023-02-21T05:39:32Z

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 · 2023-02-21T06:45:29Z

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 · 2023-02-21T08:17:10Z

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 · 2023-02-21T13:16:36Z

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 · 2023-02-21T14:20:06Z

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 · 2023-02-21T19:43:42Z

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 · 2023-02-23T05:10:51Z

Sounds like a plan :)

vrogier · 2023-02-26T21:51:56Z

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 · 2023-02-26T21:52:20Z

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 · 2023-02-27T06:30:15Z

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 · 2023-02-27T06:32:47Z

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 · 2023-02-27T08:22:52Z

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 · 2023-02-28T21:25:38Z

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 · 2023-03-02T15:30:59Z

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 · 2023-03-02T17:50:30Z

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 · 2023-03-02T19:09:05Z

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

Regards,

Vincent

unficyp · 2023-03-03T06:22:14Z

thanks, that works now :)
gerald

vrogier self-assigned this Feb 18, 2023

vrogier added ocilib bug C API labels Feb 18, 2023

vrogier pushed a commit that referenced this issue Feb 19, 2023

Fixed issue #331

bad3a1b

Added support for XMLTYPE (fetch only) exposed as OCI_Long / ocilib::Clong

vrogier added enhancement request done - waiting for merge labels Feb 20, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Strange behaviour with XMLTYPE #331

Strange behaviour with XMLTYPE #331

unficyp commented Feb 18, 2023

vrogier commented Feb 18, 2023 •

edited

Loading

unficyp commented Feb 19, 2023

vrogier commented Feb 19, 2023

vrogier commented Feb 19, 2023

vrogier commented Feb 20, 2023 •

edited

Loading

vrogier commented Feb 20, 2023 •

edited

Loading

unficyp commented Feb 21, 2023

vrogier commented Feb 21, 2023

vrogier commented Feb 21, 2023 •

edited

Loading

unficyp commented Feb 21, 2023

unficyp commented Feb 21, 2023 •

edited

Loading

vrogier commented Feb 21, 2023

unficyp commented Feb 23, 2023

vrogier commented Feb 26, 2023

vrogier commented Feb 26, 2023

unficyp commented Feb 27, 2023

unficyp commented Feb 27, 2023 •

edited

Loading

vrogier commented Feb 27, 2023

vrogier commented Feb 28, 2023

unficyp commented Mar 2, 2023

vrogier commented Mar 2, 2023

vrogier commented Mar 2, 2023

unficyp commented Mar 3, 2023

Strange behaviour with XMLTYPE #331

Strange behaviour with XMLTYPE #331

Comments

unficyp commented Feb 18, 2023

vrogier commented Feb 18, 2023 • edited Loading

unficyp commented Feb 19, 2023

vrogier commented Feb 19, 2023

vrogier commented Feb 19, 2023

vrogier commented Feb 20, 2023 • edited Loading

vrogier commented Feb 20, 2023 • edited Loading

unficyp commented Feb 21, 2023

vrogier commented Feb 21, 2023

vrogier commented Feb 21, 2023 • edited Loading

unficyp commented Feb 21, 2023

unficyp commented Feb 21, 2023 • edited Loading

vrogier commented Feb 21, 2023

unficyp commented Feb 23, 2023

vrogier commented Feb 26, 2023

vrogier commented Feb 26, 2023

unficyp commented Feb 27, 2023

unficyp commented Feb 27, 2023 • edited Loading

vrogier commented Feb 27, 2023

vrogier commented Feb 28, 2023

unficyp commented Mar 2, 2023

vrogier commented Mar 2, 2023

vrogier commented Mar 2, 2023

unficyp commented Mar 3, 2023

vrogier commented Feb 18, 2023 •

edited

Loading

vrogier commented Feb 20, 2023 •

edited

Loading

vrogier commented Feb 20, 2023 •

edited

Loading

vrogier commented Feb 21, 2023 •

edited

Loading

unficyp commented Feb 21, 2023 •

edited

Loading

unficyp commented Feb 27, 2023 •

edited

Loading