论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2011-07-20 12:09 |只看该作者 |倒序浏览

本人最近在使用oci接口对oracle数据库进行数据交互的一些操作，遇到一些问题，希望高手给出指点！
#include "oracle.h"

int main()
{
  OCI_ENV *simple_env;
  char *p;
  char *ip = "127.0.0.1";
  int port = 1521;
  char *user = "system";
  char *pass = "trytrylook";
  char *sid = "orcl";
  char cmd[90];
  char cmd1[30];
  char query[50];
  int rc;
  /* ..... */
  simple_env = (OCI_ENV *)malloc(sizeof(OCI_ENV));
  memset(simple_env, 0, sizeof(OCI_ENV));
  sprintf(cmd,"select *  from software where type='browser'");
  //strcat(cmd,cmd1);
  if(oci_init_env(simple_env, ip, port, user,pass, sid)==0)
   printf("init_env success!\n");

sprintf(query,"insert into ddd(a,b) values('0.2','gg')");
  //  sprintf(query,"delete from ddd where a=0.2");
  /* .. */
  if(oci_insert(simple_env, query))
  {
printf("insert error!\n");
perror("insert");
  }   /* ..
if(OCIStmtPrepare(simple_env->stmthp, simple_env->errhp, (CONST text *)query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
return 1;
  rc = OCIStmtExecute(simple_env->svchp, simple_env->stmthp, simple_env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS);
printf("rc = %d\n", rc);
if(0==rc)
printf("insert success!\n");
*/

// if(oci_query(simple_env, "delete  from ddd")) ;
// perror("query");


  OCI_DATA *data = (OCI_DATA *)malloc(sizeof(OCI_DATA));
  memset(data, 0, sizeof(OCI_DATA));

  printf("============cmd %s==================\n" ,cmd);

  if(oci_fetch_data(simple_env, cmd, data) == 1)
  {
printf("------------num_column = %d \n",data->num_column);
printf("%d\n",data->num_row);

for(int i=0; i<data->num_column; i++)
{
printf("%s\t",data->buf);
}
printf("\n");

  }
  else
  {
perror("fetch_data");
      return 0;
  }
  /*....*/
  oci_free_result(data);
  /* .. */
  oci_end(simple_env);
  return 0;
}

//**************************oracle.c**************************
#include "oracle.h"

int oci_init_env(OCI_ENV *env, char *ip, int port, char *user, char *pass, char *sid)
{ printf("start init_env\n");
  OCIEnv *envhp;
  OCIError *errhp;
  OCISvcCtx *svchp;
  OCIStmt *stmthp;
  int rc;
  char connstr[] = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))";

  if(env==NULL || env->user==NULL || env->pass==NULL || env->sid==NULL || env->ip==NULL)
return 1;
  snprintf(env->ip, 32, "%s", ip);
  snprintf(env->user, 32, "%s", user);
  snprintf(env->pass, 32, "%s", pass);
  snprintf(env->sid, 32, "%s", sid);
  env->port = port;

printf("start init!\n");

  rc=(OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0));
   printf("rc=%d\n", rc);
if(rc!=0)
return 1;
  if(OCIEnvInit((OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0, (dvoid **) 0))
return 1;

  if(OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,(size_t) 0,(dvoid **) 0))
return 1;
  if(OCIHandleAlloc((dvoid *) envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX,(size_t)NULL, (dvoid **)NULL))
  {
OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
return 1;
  }
printf("test1 !!!  env->user=%s, env->pass=%s, env->sid=%s \n", env->user, env->pass, env->sid);

//  if(OCILogon(envhp, errhp, &svchp, (CONST text *)env->user, strlen(env->user), (CONST text *)env->pass, strlen(env->pass),(CONST text *)env->sid, strlen(env->sid)))
  if(OCILogon(envhp, errhp, &svchp, (CONST text *)env->user, strlen(env->user), (CONST text *)env->pass, strlen(env->pass),(CONST text *)connstr, strlen(connstr)))
  {
OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
return 1;
  }
  if(OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT,(size_t)NULL, (dvoid **)NULL))
  {
OCILogoff(svchp, errhp);
OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
return 1;
  }

  env->envhp = envhp;
  env->errhp = errhp;
  env->svchp = svchp;
  env->stmthp = stmthp;
  env->defhp = NULL;
  env->bindhp = NULL;
printf("conn successful !\n");
  return 0;
}

/*1, if error
0, if success
*/

int oci_insert(OCI_ENV *env, char *query)
{
  int rc;
  printf("start insert!\n");
//  if(OCIStmtPrepare(env->stmthp, env->errhp, (CONST text *)query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
// return 1;
//Error infomation!
/*
  rc = OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS);
printf("rc = %d\n", rc);
if(0==rc)
printf("insert success!\n");
else
return 1;
*/
  if(OCIStmtPrepare(env->stmthp, env->errhp, (CONST text *)query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
return 1;
  if(OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS))
return 1;
  return 0;

}

/* 1, if error
0, if success
*/

int oci_query(OCI_ENV *env, char *query)
{
int rc;
  if(OCIStmtPrepare(env->stmthp, env->errhp, (CONST text *)query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
return 1;
//  if(OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS))
rc = OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS);
printf("rc = %d\n", rc);
//return 1;
  return 0;
}

/*1, if has more data
0, if has no data any more
*/

int oci_fetch_data(OCI_ENV *env, char *query, OCI_DATA *data)
{
  int num_col;
  char**buf_col;
  int*len_col;
  int i;
  OCIParam* colhp;
  OCIDefine** defhp;
  char tmpbuf[7][50];
  memset(tmpbuf, 0, 350);
  if(env == NULL || query == NULL || data == NULL)
return 0;
  if(data->tag == NOT_BIND)
  {
num_col = 0;
if(OCIStmtPrepare(env->stmthp, env->errhp, (CONST text *)query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT) > 0)
return 0;
if(OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 0, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DESCRIBE_ONLY) > 0)
return 0;

if(OCIAttrGet(env->stmthp, OCI_HTYPE_STMT, &num_col, 0, OCI_ATTR_PARAM_COUNT, env->errhp))
return 0;
if(num_col == 0)
return 0;
len_col = (int *)malloc(sizeof(int) * num_col);
if(len_col == NULL)
return 0;
buf_col = (char **)malloc(sizeof(char *) * num_col);
if(buf_col == NULL)
return 0;
defhp = (OCIDefine **)malloc(sizeof(OCIDefine *) * num_col);
if(defhp == NULL)
return 0;
data->num_column = num_col;
data->num_row = 0;
data->buf = buf_col;
data->buf_len = len_col;

memset(len_col, 0, sizeof(int) * num_col);
memset(buf_col, 0, sizeof(char *) * num_col);
for(i=1; i<=num_col; i++)
{
OCIParamGet(env->stmthp, OCI_HTYPE_STMT, env->errhp, (void **)&colhp, i);
OCIAttrGet(colhp, OCI_DTYPE_PARAM, (len_col+i-1), 0, OCI_ATTR_DATA_SIZE, env->errhp);
*(len_col+i-1) = *(len_col+i-1)+1;
*(buf_col+i-1) = (char *)malloc((int)(*(len_col+i-1)));
if(*(buf_col+i-1) == NULL)
return 0;
memset(*(buf_col+i-1), 0, (int)(*(len_col+i-1)));
if(OCIDefineByPos(env->stmthp, (defhp+i-1), env->errhp, i, (ub1 *)(*(buf_col+i-1)), *(len_col+i-1), SQLT_STR, NULL, (ub2 *)0, (ub2 *)0, OCI_DEFAULT))
return 0;
}

if (OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 0, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT) > 0)
return 0;
data->tag = ALREADY_BIND;
  }
  for(i=0; i<data->num_column; i++)
memset(data->buf, 0, data->buf_len);

int rc = 0;
/*
  while(rc!=OCI_NO_DATA)
  {
rc = OCIStmtFetch(env->stmthp, env->errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
data->num_row++;
printf("data->num_row = %d\n", data->num_row);
printf("rc=%d\n",rc);
  }*/
  while (OCI_NO_DATA != OCIStmtFetch(env->stmthp, env->errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT))
  {
data->num_row++;
// return 1;
  }
//  else
  {
// data->tag = NOT_BIND;
// return 0;
  }
  printf("data->num_row = %d\n", data->num_row);
}

/*1, if error
0, if success
*/

int oci_free_result(OCI_DATA *data)
{
  int i;
  if(data == NULL)
return 1;
  if(data->buf_len)
free(data->buf_len);
  if(data->buf)
  {
for(i=0; i<data->num_column; i++)
{
if(*(data->buf+i))
free(*(data->buf+i));
}
free(data->buf);
  }
  return 0;
}

下面我贴出运行结果：
linux-s1gq:/home/run/oci/oci # ./myoci
start init_env
start init!
rc=0
test1 !!!  env->user=system, env->pass=trytrylook, env->sid=orcl
conn successful !
init_env success!
start insert!
============cmd select *  from software where type='browser'==================
data->num_row = 1
------------num_column = 6
1
2891 2011-07-20 09:19:18    00:0c:29:4d:97:ba    Firefox browser 3
linux-s1gq:/home/run/oci/oci #

现在的问题就是现在的代码可以正常获取software表中的下面结果中显示的数据，但是在主函数中，红颜色的部分是调用insert函数来对ddd表插入数据，
奇怪的是如果我注释掉这个对ddd表的插入操作，结果就取不到software表的数据。
start init_env
start init!
rc=0
test1 !!!  env->user=system, env->pass=trytrylook, env->sid=orcl
conn successful !
init_env success!
rc = 0
insert success!
============cmd select *  from software where type='browser'==================
data->num_row = 1
fetch_data: No such file or directory
linux-s1gq:/home/run/oci/oci # ./myoci
start init_env
start init!
rc=0
test1 !!!  env->user=system, env->pass=trytrylook, env->sid=orcl
conn successful !
init_env success!
rc = 0
insert success!
============cmd select *  from software where type='browser'==================
data->num_row = 1
fetch_data: No such file or directory

结果就表成这样，找了好多原因，因为本人刚接触oci编程，希望各位高手指点下。