retrieving objects usign OCI

From: siddharth aggarwal <siddharthaggarwal_at_yahoo.com>
Date: Mon, 24 Dec 2001 20:36:43 -0800
Message-ID: <F001.003E27B8.20011224201517@fatcity.com>

Hi,

I am trying to retrieve objects from an ORACLE 8i database using OCI. I have attached some code that I wrote for the same but it fails at the OCIStmtFetch call with the error ORA-00932 inconsistent datatypes.

Has anyone worked on such an issue before? Thanks in advance.
Sid.

---

char resultArray[10][512]; // array to store results of row fetch

text * stmt = "select * from table1";

// one of the columns of the table1 is a user-defined object

// allocate statement handle
OCIHandleAlloc((dvoid *)myenvhp, (dvoid **) &m_stmtHandle,

                                              OCI_HTYPE_STMT, (size_t) 0,

(dvoid **)0);

// prepare and execute statement
OCIStmtPrepare(m_stmtHandle, myerrhp, (text *)stmt,

(ub4) strlen((const char
*)stmt), OCI_NTV_SYNTAX, OCI_DEFAULT);
OCIStmtExecute(mysvchp, m_stmtHandle, myerrhp, (ub4) 0,

                                 (ub4) 0, (OCISnapshot
*) NULL, (OCISnapshot *)NULL, 
                                  OCI_DEFAULT);

// get the number of columns
OCIAttrGet ((dvoid *)m_stmtHandle,
(ub4)OCI_HTYPE_STMT, (dvoid *)

                            &noOfCols, (ub4 *) 0,
(ub4)OCI_ATTR_PARAM_COUNT, 
                                                        myerrhp);

// allocate define handles
m_defineHandle = new OCIDefine* [noOfCols];

for (int i=0;i<noOfCols;i++)

        {
                OCIParam *mypard;
                char *col_name;
                ub4  col_name_len;
                ub2  dsize;
                ub2 dType;
                char *attrtype,*attrschema;
                ret = OCIParamGet(m_stmtHandle, OCI_HTYPE_STMT,
myerrhp,
                                     (dvoid **) &mypard, (ub4) i+1); // get
param at 1st pos.
                ret = OCIAttrGet((dvoid*) mypard, (ub4)
OCI_DTYPE_PARAM, 
                                (dvoid**) &col_name,(ub4 *)
&col_name_len, (ub4) OCI_ATTR_NAME, 
                                (OCIError *) myerrhp); // get
column name
                ret = OCIAttrGet((dvoid*) mypard, (ub4)
OCI_DTYPE_PARAM, 
                        (dvoid*) &dsize,(ub4 *) 0,
(ub4) OCI_ATTR_DATA_SIZE, 
                                (OCIError *) myerrhp); // no
of bytes for column
                ret = OCIAttrGet((dvoid*) mypard, (ub4)
OCI_DTYPE_PARAM, 
                                (dvoid*) &dType,(ub4 *) 0,
(ub4) OCI_ATTR_DATA_TYPE, 
                                (OCIError *) myerrhp); // get
internal datatype for param
                          
                 if (dType == 108) // user defined type
                 {
                        OCIRef *type_ref = (OCIRef *) 0;
                        OCIType *addr_tdo = (OCIType *) 0;
                        OCIParam *parmp;
                        attrschema = new char[30];
                        strcpy(attrschema,"EMAIL");  // just hardcoded the
object name for convenience
                                                     // EMAIL is a valid user defined 
type in
the database                            
                        OCITypeByName(myenvhp, myerrhp, mysvchp, (const
text *) 0,


                                   (ub4) 0, (const text *)

attrschema,


                                   (ub4) strlen((const char *)

attrschema),
                                  (CONST text *) 0, (ub4) 0,
                                   OCI_DURATION_SESSION, 
OCI_TYPEGET_HEADER,
                                   &addr_tdo);  
                        void * addr;
                        OCIDefineByPos(m_stmtHandle, (OCIDefine **)
&m_defineHandle[i],
                                       myerrhp, (ub4) (i), (dvoid *)
addr,
                                       (sb4)
((dsize+1)*sizeof(char)), SQLT_NTY,


                                   (dvoid *) &m_indicator[i], (ub2 *) &m_rlenp[i],

                                   (ub2 *) &m_retcode[i], OCI_DEFAULT);

                        
                        OCIObjectNew(myenvhp,myerrhp,mysvchp,
                             OCI_TYPECODE_OBJECT,addr_tdo, 
                             (dvoid
*)NULL,OCI_DURATION_TRANS,TRUE,&addr);
                
                        OCIDefineObject(m_defineHandle[i], myerrhp,
addr_tdo,
                                        (dvoid **) &addr, (ub4 *) 0, (dvoid **) 0, (ub4
*) 0);
                                
                 }   // end of if

                 else {  // not a user defined type
                        OCIDefineByPos(m_stmtHandle, (OCIDefine **)
&m_defineHandle[i],
                                       myerrhp, (ub4) (i+1), (dvoid
*) resultArray[i],
                                       (sb4) ((513)*sizeof(char)),
SQLT_STR,


                                   (dvoid *) &m_indicator[i], (ub2 *) &m_rlenp[i],

                                   (ub2 *) &m_retcode[i], OCI_DEFAULT);

                         } // end of else
                } // end of for


                while (1)
                        {
                         ret = OCIStmtFetch(m_stmtHandle, myerrhp,(ub4)

1,OCI_FETCH_NEXT, OCI_DEFAULT);

                                // this is where i get error 00932

                         if (ret == OCI_NO_DATA)
                                break;
                        }

__________________________________________________

Do You Yahoo!?
Send your FREE holiday greetings online! http://greetings.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: siddharth aggarwal
  INET: siddharthaggarwal_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Dec 24 2001 - 22:36:43 CST