This discussion is archived
2 Replies Latest reply: Jan 3, 2013 12:06 PM by jchristian RSS

problem in using OCIBindByName  for date type column

910885 Newbie
Currently Being Moderated
Hi,

Can you please guide me about my following problem?

I am trying to fetch the data from system table "FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$" in Oracle Express edition 10.2.0.3 database, using the OCI library on Windows with C++

This table has a TIMESTAMP column of date type.

I've the following query as below:

select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') order by TIME_STAMP asc;

For this, first I am preparing the query as below:

select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > :PKVAL order by TIME_STAMP asc;

Before calling OCIExecute(), I am setting the PKVAL buffer to following value:
"to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') "

& calling the OCIBindByName function with data type as SQLT_DATE.

But when the OCIExecute is called, my program crashes with access violation

Can you please guide me what is the correct way to pass the date value in query to OCI? How do we bind the date values?

Thanks in advance for your time and help.

Best Regards.
  • 1. Re: problem in using OCIBindByName  for date type column
    jchristian Newbie
    Currently Being Moderated
    I had a similar problem. TO_DATE doesn't appear to function w/ BindByName or BindByPos.

    You are going to have to bite the bullet and use an actual date.

    Here's an example (I am using two dates in my query):

    ...
    int main (int argc, char **argv, char **envp)
    {
    ...
    static text invalidQuery = (text )"SELECT f1.my_val, COUNT(f1.my_val) invalid_count, (SELECT COUNT(*) FROM my_db_table f2 WHERE f2.my_val = f1.my_val AND f2.foo_dt = :1 ) total_count FROM my_db_table f1 WHERE f1.foo_dt = :1 AND ( (f1.vld_bar_sw IS NULL OR f1.vld_bar_sw = 'N') OR (f1.vld_foo_sw IS NULL OR f1.vld_foo_sw = 'N') ) group by my_val order by my_val asc";
    ...
    OCIDate boundDateVal;
    sb2 inputYear;
    ub1 inputMonth;
    ub1 inputDay;
    char inputYearStr [] = "0000";
    char inputMonthStr [] = "00";
    char inputDayStr [] = "00";
    ...
    /* I get and validate the date as input, but you can set it however you'd like */
    ...
    inputYear = (sb2) (atoi (inputYearStr));
    inputMonth = (ub1) (atoi(inputMonthStr));
    inputDay = (ub1) (atoi(inputDayStr));
    ...
    /* Allocating Date*/
    OCIDateSetDate ( &boundDateVal, inputYear, inputMonth, inputDay );
    OCIDateSetTime( &boundDateVal, 0, 0, 0);
    ...
    returnVal = OCIBindByPos(invalidStmt, &firstInputBindHPtr, myErrorHandle, (ub4) 1,
    (dvoid *) &boundDateVal, (sword) sizeof(boundDateVal),
    SQLT_ODT, /*dty OCIDate*/
    (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);


    if ( !(returnVal == OCI_SUCCESS || returnVal == OCI_SUCCESS_WITH_INFO) )
    {
    displayErrors (returnVal, myErrorHandle, "OCIBindByPos-contactDate 1 ");
    cleanup();
    exit(1);
    }//if

    returnVal = OCIBindByPos(invalidStmt, &secInputBindHPtr, myErrorHandle, (ub4) 2,
    //(dvoid *) &InputArguments.contactDate, (sword) sizeof(InputArguments.contactDate),
    (dvoid *) &boundDateVal, (sword) sizeof(boundDateVal),
    SQLT_ODT, /*dty OCIDate*/
    (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);


    if ( !(returnVal == OCI_SUCCESS || returnVal == OCI_SUCCESS_WITH_INFO) )
    {
    displayErrors (returnVal, myErrorHandle, "OCIBindByPos-contactDate 2 ");
    cleanup();
    exit(1);
    }//if
    ...

    }//main

    ...



    ** I have had issues w/ OTN not correctly translating certain characters, so hopefully the above example is readable or you can figure out which characters may be missing.
  • 2. Re: problem in using OCIBindByName  for date type column
    jchristian Newbie
    Currently Being Moderated
    I found out I shoudl use a special tag for the oracle forums to post code. Let's see if it corrects any formatting issues... Identical to prevously posted code.
    ...
    int main (int argc, char **argv, char **envp)
    {
    ...
        static text *invalidQuery = (text *)"SELECT f1.my_val, COUNT(f1.my_val) invalid_count, (SELECT COUNT(*) FROM my_db_table f2 WHERE f2.my_val = f1.my_val AND f2.foo_dt = :1 ) total_count FROM my_db_table f1 WHERE f1.foo_dt = :1 AND ( (f1.vld_bar_sw IS NULL OR f1.vld_bar_sw = 'N') OR (f1.vld_foo_sw IS NULL OR f1.vld_foo_sw = 'N') ) group by my_val order by my_val asc";
    ...
        OCIDate boundDateVal;
        sb2 inputYear;
        ub1 inputMonth;
        ub1 inputDay;
        char inputYearStr [] = "0000";
        char inputMonthStr [] = "00";
        char inputDayStr [] = "00";
    ...  
    /* I get and validate the date as input, but you can set it however you'd like */
    ...
        inputYear =  (sb2) (atoi (inputYearStr));
        inputMonth = (ub1) (atoi(inputMonthStr));
        inputDay =  (ub1) (atoi(inputDayStr));
    ...
        /* Allocating Date*/
        OCIDateSetDate ( &boundDateVal, inputYear, inputMonth, inputDay );
        OCIDateSetTime(  &boundDateVal,    0,  0,  0);
    ...
        returnVal = OCIBindByPos(invalidStmt, &firstInputBindHPtr, myErrorHandle, (ub4) 1,
               (dvoid *) &boundDateVal, (sword) sizeof(boundDateVal),
                SQLT_ODT, /*dty OCIDate*/
               (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
    
    
        if ( !(returnVal == OCI_SUCCESS || returnVal == OCI_SUCCESS_WITH_INFO) )
        {
            displayErrors (returnVal, myErrorHandle, "OCIBindByPos-contactDate 1 ");
            cleanup();
            exit(1);
        }//if
    
        returnVal = OCIBindByPos(invalidStmt, &secInputBindHPtr, myErrorHandle, (ub4) 2,
               //(dvoid *) &InputArguments.contactDate, (sword) sizeof(InputArguments.contactDate),
               (dvoid *) &boundDateVal, (sword) sizeof(boundDateVal),
                SQLT_ODT, /*dty OCIDate*/
               (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
    
    
        if ( !(returnVal == OCI_SUCCESS || returnVal == OCI_SUCCESS_WITH_INFO) )
        {
            displayErrors (returnVal, myErrorHandle, "OCIBindByPos-contactDate 2 ");
            cleanup();
            exit(1);
        }//if
    ...
    
    }//main
    
    ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points