Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Unanswered: OCI - help regarding inserting date into table

    Hi All,

    Greetings,
    I am newbie in oracle. n i have been given an assignment in OCI where i have to insert certain values like customerid, customername, dateofpurchase, amount ,itemid into a table. all the things r working fine except for i have been trying to figure out how to insert a datevalue of type date into a table, but couldn't succeed.
    could someone illustrate with an code snippet for inserting a date value.

    Thanks in Advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What does it, actually, mean that you couldn't succeed? Any error? If so, which one? Could you post code you've used?

  3. #3
    Join Date
    Nov 2008
    Posts
    3
    i have taken dateofpurchase as dop of type ub1
    ub1 dop[7];

    n the following is the code snippet i have used for inserting into my table cust_info

    //code
    const text * inst=(text *)"insert into cust_info(custid,name,addr,dateofpurchase,amount,i temid) \
    values(:custid,:custname,:custaddr,:dateofpur,:amo unt,:itemid)";

    r=OCIStmtPrepare(stmt,err,inst,strlen((char *)inst),OCI_NTV_SYNTAX,OCI_DEFAULT);
    if(r==OCI_SUCCESS)
    {
    checkerr(err,OCIBindByName(stmt,&bindp1,err,(text *)":custid",-1,(dvoid *)&custid,sizeof(sword),SQLT_INT,(dvoid *)0,(ub2 *)0,(ub2 *)0,(ub4)0,(ub4 *)0,OCI_DEFAULT));
    checkerr(err,OCIBindByName(stmt,&bindp2,err,(text *)":custname",-1,(dvoid *)custname,sizeof(custname),SQLT_STR,(dvoid *)0,(ub2 *)0,(ub2 *)0,(ub4)0,(ub4 *)0,OCI_DEFAULT));
    checkerr(err,OCIBindByName(stmt,&bindp3,err,(text *)":custaddr",-1,(dvoid *)custaddr,sizeof(custaddr),SQLT_STR,(dvoid *)0,(ub2 *)0,(ub2 *)0,(ub4)0,(ub4 *)0,OCI_DEFAULT));
    checkerr(err,OCIBindByName(stmt,&bindp4,err,(text *)":dateofpur",-1,(dvoid *)dop,sizeof(dop),SQLT_DAT,(dvoid *)0,(ub2 *)0,(ub2 *)0,(ub4)0,(ub4 *)0,OCI_DEFAULT));
    checkerr(err,OCIBindByName(stmt,&bindp5,err,(text *)":amount",-1,(dvoid *)&amount,sizeof(sword),SQLT_INT,(dvoid *)0,(ub2 *)0,(ub2 *)0,(ub4)0,(ub4 *)0,OCI_DEFAULT));
    checkerr(err,OCIBindByName(stmt,&bindp6,err,(text *)":itemid",-1,(dvoid *)itemid,sizeof(itemid),SQLT_STR,(dvoid *)0,(ub2 *)0,(ub2 *)0,(ub4)0,(ub4 *)0,OCI_DEFAULT));
    r=OCIStmtExecute(svc,stmt,err,1,0,NULL,NULL,OCI_DE FAULT);
    }

    //code ends

    this code executes fine, but the data inserted for dateofpurchase is displayed as 53-, -43 when i have given input as 090508

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Having in mind that I'm not familiar with OCI, perhaps you should pay attention to inserting a DATE into the column, not a string.

    In other words, although '090508' looks like a date to you, Oracle might not know what it means. I, for example, do not. Which part of it is year, which is month and which is a day?

    SQL statement might look like this:
    Code:
    INSERT INTO this_table (ID, date_column)
    VALUES
    (1, TO_DATE('090508', 'ddmmyy'));
    See if you can adjust it to your code. Pay attention to the TO_DATE function!

  5. #5
    Join Date
    Nov 2008
    Posts
    3
    thanks for tat piece of advise littlefoot.
    i have tried that also, bt it din help much.

    can somebody help me in this regard

  6. #6
    Join Date
    Nov 2008
    Posts
    5
    Hi,

    Can you post the code you used to declare the variable 'dop' and its value assignement ?

    btw, 'dop' must a structure (not a string) that needs value computation to fill its members

    You could use as well the type SQL_ODT in order to use the OCI public OCIDate structure..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •