Re: [Orclib-users] collection insert assist

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

Thank you!

Regards,

At 01:37 PM 9/26/2012, vincent rogier wrote:
>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 
><<mailto:kou...@gm...>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://ad.doubleclick.net/clk;262219672;13503038;z?
>http://info.appdynamics.com/FreeJavaPerformanceDownload.html
>_______________________________________________
>Orclib-users mailing list
><mailto:Orc...@li...>Orc...@li...
>https://lists.sourceforge.net/lists/listinfo/orclib-users
>
>
>
>
>--
>Vincent Rogier