[Orclib-users] collection insert assist

[Kourosh A. <kou...@gm...>]

Hi,

I have a simple table with the last column as a collection.  I 
created an object and assigned a value to it.  Then I created an 
element and assigned the object to it.  Then I created a collection 
and assigned the element to it.  When I insert in to the table it 
enters null for the collection.  Can you help?

The following are the type definitions and the C code.

Thank you in advance!
====================================================================================================

CREATE OR REPLACE TYPE field6_coltyp AS OBJECT
(
    field6_name       varchar2(50),
)
/

CREATE OR REPLACE TYPE field6_coltab_typ AS table of field6_coltyp ;

CREATE OR REPLACE TYPE test_table_typ AS OBJECT
    (
     field1  varchar2(25),
     field2  varchar2(25),
     field3  varchar2(20),
     field4  timestamp,
     field5  number,
     field6  field6_coltab_typ)
/

CREATE table test_table
    (
     field1  varchar2(25),
     field2  varchar2(25),
     field3  varchar2(20),
     field4  timestamp,
     field5  number,
     field6  field6_coltab_typ)
     NESTED TABLE field6 STORE AS field6_tab
/



-----------------------------------------------
#include <stdio.h>
#include <ctype.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

#include "ocilib.h"
int main(void)
{
    OCI_Connection *cn;
    OCI_Statement *st;

    char  field1 [25];
    char  field2 [25];
    char  field3 [25];
    char  field4 [30];
    char  field5 [25];
    OCI_Coll*    field6_col_coll;
    OCI_Elem*    field6_col_elem;
    OCI_Object*  field6_col_obj;

    OCI_TypeInfo *type;

    OCI_Iter *iter;

    int i;

    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
       return EXIT_FAILURE;

    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    if (cn != NULL)
    {
       st = OCI_StatementCreate(cn);

       field6_col_obj = OCI_ObjectCreate (cn, OCI_TypeInfoGet(cn, 
"field6_col_typ", OCI_TIF_TYPE));
       field6_col_elem = OCI_ElemCreate (OCI_TypeInfoGet(cn, 
"field6_col_typ", OCI_TIF_TYPE));
       field6_col_coll = OCI_CollCreate (OCI_TypeInfoGet(cn, 
"field6_col_tab_typ", OCI_TIF_TYPE));

       OCI_ObjectSetString (field6_col_obj, "field6_name", "test123");
       OCI_ElemSetObject (field6_col_elem, field6_col_obj);
       OCI_CollSetAt (field6_col_coll, 1, field6_col_elem);

       OCI_Prepare(st, "INSERT INTO test_table VALUES (:c, :s, :x, 
:d, :g, :h)");

       memset (field1, 0, sizeof (field1));
       memset (field2, 0, sizeof (field2));
       memset (field3, 0, sizeof (field3));
       memset (field4, 0, sizeof (field4));
       memset (field5, 0, sizeof (field5));

       strcpy (field1, "field1_val");
       strcpy (field2, "field2_val");
       strcpy (field3, "field3_val");
       strcpy (field4, "field4_val");
       strcpy (field5, "field5_val");

       OCI_BindString(st, ":c", field1, 25);
       OCI_BindString(st, ":s", field2, 25);
       OCI_BindString(st, ":x", field3, 25);
       OCI_BindString(st, ":d", field4, 30);
       OCI_BindString(st, ":g", field5, 25);
       OCI_BindColl(st, ":h", field6_col_coll);

       OCI_Execute(st);
       OCI_Commit(cn);

       OCI_ElemFree (field6_col_elem);
       OCI_CollFree (field6_col_coll);
       OCI_StatementFree(st);
       OCI_ConnectionFree(cn);
    }
    else
       printf("Failure\n");

    OCI_Cleanup();
    return EXIT_SUCCESS;
}