Forum Stats

  • 3,837,857 Users
  • 2,262,302 Discussions
  • 7,900,410 Comments

Discussions

OCI:why can't retrieve the value ?

740526
740526 Member Posts: 8
edited Dec 23, 2009 8:28PM in Oracle Call Interface (OCI)
Hi

the program as follow:
#include "oci.h"

#include<stdio.h>
#include<iostream.h>
#include<string.h>


#define LANG "en_US"

int main(int argc,char ** argv)
{

  
  OCIEnv      *m_env;
  OCIError    *m_error;
  OCISvcCtx   *m_serverctx;
  OCIStmt     *m_stmt;

  
  OCIBind     *m_bind;
  
  OCIServer   *m_server;
  OCIDefine   *m_define;
  OCIParam    *m_param;
  
  char        usr[20]="sss";
  char        pass[20]="sss";
  char        dbname[20]="\0";
  
  char        create_date[20]="\0";
  char        v_cardnum[20]="\0";

  sb2          ind=0;
  sword       status=0;
  char        errbuf[500]="\0";
  unsigned short        row_len=0;
  OCIDate    date;

  char       v_date[30];
  ub4        date_len=20;
  
  char        slist[150]="select  cardnum,create_date from cardmast where cif='00000001'";
  
  ub4 mode=OCI_DEFAULT;
 
 OCIEnvCreate(&m_env,mode,(dvoid*)0,NULL,NULL,NULL,0,(dvoid **) 0);  
  
  OCIHandleAlloc((const dvoid*)m_env,(dvoid **)&m_error,OCI_HTYPE_ERROR,0,(dvoid **) 0);
   
  
  status=OCILogon(m_env,m_error,&m_serverctx,(const OraText *) usr,strlen(usr),(const OraText *) pass,strlen(pass),(const OraText *)dbname,strlen(dbname));
  
  if(status==OCI_SUCCESS)
  	cout<<"connect to database "<<dbname<<endl;
  else
  	cout<<"fail to connect to database "<<dbname<<endl;
  	
  
  status=OCIStmtPrepare2(m_serverctx,&m_stmt,m_error,(const OraText *)slist,strlen(slist),(const OraText *)0,0,OCI_NTV_SYNTAX,0);
  cout<<"prepare status:"<<status<<endl;
  
   status=OCIStmtExecute(m_serverctx,m_stmt,m_error,0,0,0,0,OCI_STMT_SCROLLABLE_READONLY);
   
   status=OCIDefineByPos(m_stmt,&m_define,m_error,(ub4)2,(dvoid *) &date,sizeof(OCIDate),SQLT_ODT,0,0,0,OCI_DEFAULT);
   
   status=OCIStmtFetch2(m_stmt,m_error,1,OCI_DEFAULT,1,OCI_DEFAULT|OCI_STMT_SCROLLABLE_READONLY);
   cout<<"fetch status:"<<status<<endl;
  
   status=OCIDateToText(m_error,&date,(const OraText*)"DD-MM-YYYY",(ub1)10,(const OraText*)LANG,(ub4)5,(ub4*)&date_len,(OraText*) v_date);
   cout<<"the create_date :"<<v_date<<endl;  



  OCILogoff(m_serverctx,m_error);
  cout<<"it is successful !!!"<<endl; 
  return 0;

} 
the running result :
connect to database 
prepare status:0
fetch status:0
the create_date:00-00-0000
it is successful 
the create_date is null

if i adding code for define column 'cardnum'

status=OCIDefineByPos(m_stmt,&m_define,m_error,(ub4)1,(dvoid *) v_cardnum,(sb4)20,SQLT_STR,&ind,0,0,OCI_DEFAULT);
then cout the cardnum

the running result :
connect to database 
prepare status:0
fetch status:0
the create_date:20-03-2008
9435353500018651
it is successful 
is there anything wrong ?

Edited by: user8756764 on 2009-12-23 上午1:55
Tagged:

Answers

  • Vincent Rogier
    Vincent Rogier Member Posts: 185
    edited Dec 23, 2009 5:29AM
    Hi,

    you're wondering why it does not work ?

    Just because all selected items of your query must be defined !

    It does not make sense to ask to Oracle to retrieve a column that you're not fetching. If you don't want it, don't select it !

    Regards,

    Vincent
  • 740526
    740526 Member Posts: 8
    but the document say in page:160
    For example, if your program processes the following statement you would normally 
    need to define two output variables, one to receive the value returned from the name 
    column, and one to receive the value returned from the ssn column:
    SELECT name, ssn FROM employees
        WHERE empno = :empnum
    If you were only interested in retrieving values from the name column, you would not
    need to define an output variable for ssn.
  • 532463
    532463 Member Posts: 59
    I was able to get the expected output with your program with just these changes:
     char        slist[150]="select  cardnum,create_date from cardmast where cardnum= 10 ";
    I created the cardmast table as follows:
    create table cardmast(cardnum integer, create_date date);
    insert into cardmast values(10, '23-DEC-09');
    commit;
    Can you please check in sqlplus if the query you are using is indeed returning something.

    Thanks,
    Sumit
  • 740526
    740526 Member Posts: 8
    edited Dec 23, 2009 8:28PM
    it is indeed returning the value in plsql!

    the only different is that the type of column 'cardnum' is varchar2 ,

    should i call OCIAttrSet to set the charset when define because of varchar2?

    Edited by: llslls_007 on 2009-12-23 下午5:27
This discussion has been closed.