Re: [Orclib-users] collection insert assist

[vincent r. <vin...@ya...>]

Hi,

First, always use an error handler. This would tell why i does not work.
Try again with an error handler.

Also, use OCI_CollAppend() instead of OCI_CollSetAt().

Regards,

Vincent


On Wed, Sep 26, 2012 at 9:38 PM, Kourosh Ashkon <kou...@gm...>wrote:

>  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;
> }
>
>
> ------------------------------------------------------------------------------
> How fast is your code?
> 3 out of 4 devs don\\\'t know how their code performs in production.
> Find out how slow your code is with AppDynamics Lite.
> http://ad.doubleclick.net/clk;262219672;13503038;z?
> http://info.appdynamics.com/FreeJavaPerformanceDownload.html
> _______________________________________________
> Orclib-users mailing list
> Orc...@li...
> https://lists.sourceforge.net/lists/listinfo/orclib-users
>
>


-- 
Vincent Rogier