fetching objects using OCI

From: siddharth <sidaggarwal_at_rediffmail.com>
Date: 24 Dec 2001 20:07:39 -0800
Message-ID: <df731e84.0112242007.42952e0c@posting.google.com>

Hi,

I am trying to retrieve objects (i.e. user defined types from an oracle8i database using OCI). I have written some code but the call OCIStmtFetch fails with the error ORA 00932 - inconsistent data types.

I have attached the piece of code that I wrote. I would really appreciate if someone could guide me in this regard or send me some working code sample.

Thanks in advance.
Sid.

-----------------------------Attached
Code--------------------------------------

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;
			}

Received on Mon Dec 24 2001 - 22:07:39 CST