Unable to fetch Timpstamp datatype using OCI layer

From: <coolrohitjain_at_gmail.com>
Date: Tue, 07 Aug 2007 03:16:47 -0700
Message-ID: <1186481807.635589.276130@b79g2000hse.googlegroups.com>

Hi,
I am pretty newbie to OCI layer. I was trying to make a connection to oracle, and was trying to fetch timestamp datatype by binding timpstamp datatype.

This datatype shows different behavior for different sub second precisions.

For my program(in c++) I was able to fetch data successfully for direct execution for both Timestamp(6) and Timestamp(9). I am able to see data value's.

On the other hand when I was trying to bind timestamp data, 1) if my datatype is timestamp(6), it is unable to fetches any data.I also tried to insert same data using my program.

2)in case of timestamp(9), it is able to fetches only those rows which were inserted using my program, it doesn't fetch rows which were inserted using SQL-PLUS.

Here I have given snippet of my code:

char *sql_statement="select t1 from tstamp where t1=:1";

 rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,


           (dvoid * (*)(dvoid *, size_t)) 0,

           (dvoid * (*)(dvoid *, dvoid *, size_t))0,

           (void (*)(dvoid *, dvoid *)) 0 );


OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **)
0 );
OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,


           (size_t) 0, (dvoid **) 0);

  OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0);

OCILogon

OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,

           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);

OCIStmtPrepare(p_sql, p_err, (OraText *)sql_statement,

           strlen(sql_statement), (ub4) OCI_NTV_SYNTAX, 0);

unsigned char pDataPtr[11]; //for def

        unsigned char pDataPtr1[11]; //for bind

    pDataPtr1[0]= (unsigned char)120;
    pDataPtr1[1]=(unsigned char)107;
    pDataPtr1[2]=(unsigned char)8;
    pDataPtr1[3]=(unsigned char)8;
    pDataPtr1[4]=(unsigned char)11;
    pDataPtr1[5]=(unsigned char)1;
    pDataPtr1[6]=(unsigned char)0;
    pDataPtr1[7]=(unsigned char)0;
    pDataPtr1[8]=(unsigned char)0;
    pDataPtr1[9]=(unsigned char)0;
    pDataPtr1[10]=(unsigned char)0;


OCIBindByPos(p_sql,&p_bnd,p_err,1,pDataPtr1,len,180,
        (dvoid *)pIndicator,&len,0,0,0,OCI_DEFAULT);


OCIDefineByPos ( p_sql,&p_dfn, p_err, 1, (dvoid *)pDataPtr, len, 180
                       ,(dvoid *)pIndicator,0,0,OCI_DEFAULT);


OCIStmtExecute(p_svc, p_sql, p_err, 1, 0,
          0, 0, OCI_DEFAULT);

 while (rc != OCI_NO_DATA)
     {
for(i=0;i<len;i++)
         {
            pDataPtr[i]='\0';
         }

         rc = OCIStmtFetch(p_sql, p_err, 1, OCI_DEFAULT, 0);

}

}

I also tried to see what is the data OCI is setting in memory, i found it to be the same as I am setting.
I also found when I set SQL statement to be

               "select t1 from tstamp where t1<=:1;" then it is able to fetch data. I think oracle is storing some thing else in it's memory, then it shows while fetching data.

Thanks for reading post, I will appreciate for your reply.

Thanks,
Rohit Jain Received on Tue Aug 07 2007 - 05:16:47 CDT