how to recognize null values on objects

[Herbert Metzdorf]

I am selecting sdo_geometries. Some of the geometries are NULL. How can i
recognize the NULL value?
OCI_IsNull did not work on OCI_CDT_OBJECT - it always returns FALSE.

[Vincent Rogier]

Hi,

do you means that the all SDO_GEOMETRY object instance is null or some of its
members are null ?

Can you indicate your OCILIB version, the OS and Oracle version, and if it's
an 32 or 64 bits build of OCILIB ?

thanks

vincent

[Herbert Metzdorf]

yes i mean the whole object instance. checking object members is no problem so
far.
I tried with OCILIB version 3.8.1 and 3.9.
OS is Windows 7 32 Bit.
Database is "Release 10.2.0.4.0 - 64bit"
Client is "Release 11.2.0.1.0"

[Vincent Rogier]

can you post or send me by mail your code, the table definition and if
possible some data ?

vincent

[Nobody/Anonymous]

Hi Vince,
here is a short piece of code to demonstrate my problem. Table "test_insert2"
+ data is created in the program. One row contains a NULL-Geometry, the second
row contains a valid SDO object. As a result of accessing the null-object
there is 1 unfreed object handle at cleanup. Hope you can compile the code and
give me a hint for the missing or wrong conding.
thanks

#include "ocilib.h"

void error(OCI_Error *err){
    printf("*OCILIB*: %s\n", OCI_ErrorGetString(err));
}

int main(int argc, char **argv){
    if (OCI_Initialize(error, NULL, OCI_ENV_DEFAULT) == 0)
        return EXIT_FAILURE;

    OCI_Connection *cn = OCI_ConnectionCreate("schuldb", "hm", "hm", OCI_SESSION_DEFAULT);
    if (cn == (OCI_Connection*)0){
        fprintf(stderr, "ConnectionCreate failed\n");
        return EXIT_FAILURE;
    }

    OCI_Statement *st = OCI_StatementCreate(cn);
    OCI_ExecuteStmt(st, "create table test_insert2(geo mdsys.sdo_geometry)");
    OCI_ExecuteStmt(st, "truncate table test_insert2");
    OCI_ExecuteStmt(st, "insert into test_insert2 values (null)");
    OCI_ExecuteStmt(st, "insert into test_insert2 values (sdo_geometry(3001,null,sdo_point_type(1,2,3),null,null))");

    OCI_ExecuteStmt(st, "select geo from test_insert2");
    OCI_Resultset *rs = OCI_GetResultset(st);
    for (size_t n=0; OCI_FetchNext(rs); n++) {
        char value[256];
        if(OCI_IsNull(rs, 1))
            strcpy(value,"NULL");
        else {
            OCI_Object *o = OCI_GetObject(rs, 1);
            if(o == NULL)
                strcpy(value, "GetObject() returned NULL");
            else 
                sprintf(value, "sdo_gtype=%d", OCI_ObjectGetInt(o, "SDO_GTYPE"));
        }
        printf("row[%d]: %s\n", (int)n, value);
    }
    OCI_StatementFree(st);
    OCI_Cleanup();
    return 0;
}

[Vincent Rogier]

hi,

I'll test your code this evening. Can you post the program output ?

thank you

[Herbert Metzdorf]

Hi,
running the programm more than once will cause ORA-00955 because the table
already exists - simply ignore this.
Here is my output:

*OCILIB*: ORA-00955: Es gibt bereits ein Objekt mit diesem Namen

row[0]: sdo_gtype=0
row[1]: sdo_gtype=3001
*OCILIB*: Found 1 unfreed OCI Object handles

[Vincent Rogier]

hello,

bug reproduced. I'm working ont it.

[Vincent Rogier]

bug fixed !

stupid me .....

As i had another bug fixed waiting, i'm going to release a 3.9.1 package :)

[Vincent Rogier]

hi,

version 3.9.1. available for download :)

[Herbert Metzdorf]

thank you for your response. I checked the 3.9.1 release and think the bug is
not completely fixed. There seems to be a problem with array-fetches. Playing
with different FetchSizes will give different results. Try the following code
(the first column describes the expected result):

#include "ocilib.h"

void error(OCI_Error *err){
    printf("*OCILIB*: %s\n", OCI_ErrorGetString(err));
}
int main(int argc, char **argv){
    if (OCI_Initialize(error, NULL, OCI_ENV_DEFAULT) == 0)
        return EXIT_FAILURE;
    OCI_Connection *cn = OCI_ConnectionCreate("schuldb", "hm", "hm", OCI_SESSION_DEFAULT);
    if (cn == (OCI_Connection*)0){
        fprintf(stderr, "ConnectionCreate failed\n");
        return EXIT_FAILURE;
    }
    OCI_Statement *st = OCI_StatementCreate(cn);
    OCI_ExecuteStmt(st, "create table test_insert3(descr varchar2(32),geo mdsys.sdo_geometry)");
    OCI_ExecuteStmt(st, "truncate table test_insert3");
    for(int i=0; i<10; i++) {
        OCI_ExecuteStmt(st, "insert into test_insert3 values ('NULL ',null)");
        OCI_ExecuteStmt(st, "insert into test_insert3 values ('POINT',sdo_geometry(3001,null,sdo_point_type(1,2,3),null,null))");
    }
    OCI_SetFetchSize(st,3);
    OCI_ExecuteStmt(st, "select descr,geo from test_insert3");
    OCI_Resultset *rs = OCI_GetResultset(st);
    for (size_t n=0; OCI_FetchNext(rs); n++) {
        char value[256];
        if(OCI_IsNull(rs, 2))
            strcpy(value,"NULL");
        else {
            OCI_Object *o = OCI_GetObject(rs, 2);
            if(o == NULL)
                strcpy(value, "GetObject() returned NULL");
            else 
                sprintf(value, "sdo_gtype=%d", OCI_ObjectGetInt(o, "SDO_GTYPE"));
        }
        printf("row[%2d]: %s, %s\n", (int)n, OCI_GetString(rs,1), value);
    }
    OCI_StatementFree(st);
    OCI_Cleanup();
    return 0;
}

and here is my output:

*OCILIB*: ORA-00955: Es gibt bereits ein Objekt mit diesem Namen
row[ 0]: NULL , NULL
row[ 1]: POINT, sdo_gtype=3001
row[ 2]: NULL , sdo_gtype=0
row[ 3]: POINT, sdo_gtype=3001
row[ 4]: NULL , sdo_gtype=3001
row[ 5]: POINT, NULL
row[ 6]: NULL , NULL
row[ 7]: POINT, sdo_gtype=3001
row[ 8]: NULL , sdo_gtype=3001
row[ 9]: POINT, sdo_gtype=3001
row[10]: NULL , sdo_gtype=3001
row[11]: POINT, NULL
row[12]: NULL , NULL
row[13]: POINT, sdo_gtype=3001
row[14]: NULL , sdo_gtype=3001
row[15]: POINT, sdo_gtype=3001
row[16]: NULL , sdo_gtype=3001
row[17]: POINT, NULL
row[18]: NULL , NULL
row[19]: POINT, sdo_gtype=3001
*OCILIB*: Found 1 unfreed OCI Object handles

[Vincent Rogier]

ok, i'll check your sample code tonight. I've removed the v3.9.0 from download
area.

[Vincent Rogier]

i know where the problems comes from .... I'll do the fix this evening.

Can you send me a mail ? I'll send you the windows package for validation.

thanks.

vincent

[Vincent Rogier]

problem solved, i've sent your a compiled dll for validation.

[Herbert Metzdorf]

validation complete, problem solved. thanks