oracle.c

站长写的使用OCI开发Oracle程序的通用函数库for unix

  pquery->RecCount =pquery->FieldCount =0;
  if(pquery->m_hpSelect)
  {
    OCIHandleFree( pquery->m_hpSelect, OCI_HTYPE_STMT );
    pquery->m_hpSelect =NULL;
  }
  CheckErr(pquery->pdb, (hr =OCIHandleAlloc( pquery->pdb->m_hpEnv,
	(void**)&pquery->m_hpSelect, // ref to statement handle pointer
	OCI_HTYPE_STMT, // type of handle being allocated
	0,	NULL )));
  if(FAILED(hr))
    return -1;
  // Associate statement string with handle
  CheckErr(pquery->pdb, (hr =OCIStmtPrepare( pquery->m_hpSelect,
	pquery->pdb->m_hpErr, // error handle pointer
	(text *)stmt, // statement string
	strlen(stmt),
	OCI_NTV_SYNTAX, // specify native syntax
	OCI_DEFAULT )));
  if(FAILED(hr))
  {
    db_query_close(pquery);
    return -1;
  }
  // Execute but don't fetch yet
  CheckErr(pquery->pdb, (hr =(OCIStmtExecute( pquery->pdb->m_hpContext,
	pquery->m_hpSelect, // prepared by previous function calls
	pquery->pdb->m_hpErr,
	0, // 'iters' i.e. max rows to fetch during this call
	0, NULL, NULL, OCI_DEFAULT ))));
  // If it returns OCI_NO_DATA then no need to define recordset
  if ( hr == OCI_NO_DATA)
  {
    db_query_close(pquery);
    return 0;
  }
  if(FAILED(hr) )
  {
    db_query_close(pquery);
    return -1;
  }

  // Load the types into recordset
  pquery->FieldCount = 0;
  while ( hr == OCI_SUCCESS )
  {
    // Get pointer to column
    void* pFieldAttr = NULL;
    HRESULT hrGetNext;
    hrGetNext = OCIParamGet( pquery->m_hpSelect, OCI_HTYPE_STMT,
	pquery->pdb->m_hpErr,
	&pFieldAttr,
	pquery->FieldCount+1 // position
    );
    if ( hrGetNext != OCI_SUCCESS )
      break;

    // Increment column count and allocate an OdbField structure
    ++pquery->FieldCount;
    BindField* pField = (BindField *)malloc(sizeof(BindField));
    pField->pBuffer =NULL;

    if(pquery->m_BindFields ==NULL)
      pquery->m_BindFields =(BindField **)malloc(pquery->FieldCount*sizeof(BindField *));
    else
      pquery->m_BindFields =(BindField **)realloc(pquery->m_BindFields, pquery->FieldCount*sizeof(BindField *));
    pquery->m_BindFields[pquery->FieldCount-1] =pField;

    if(pquery->FieldValues ==NULL)
      pquery->FieldValues =(char **)malloc(pquery->FieldCount*sizeof(char *));
    else
      pquery->FieldValues =(char **)realloc(pquery->FieldValues, pquery->FieldCount*sizeof(char *));
    pquery->FieldValues[pquery->FieldCount-1] =NULL;

    // Get data type
    CheckErr(pquery->pdb, (hr =OCIAttrGet( pFieldAttr, OCI_DTYPE_PARAM,
	&pField->wType,
	0,
	OCI_ATTR_DATA_TYPE,
	pquery->pdb->m_hpErr )));
    if(FAILED(hr))
    {
      db_query_close(pquery);
      return -1;
    }
    // Get data size
    CheckErr(pquery->pdb, (hr =OCIAttrGet( pFieldAttr, OCI_DTYPE_PARAM,
	&pField->wSize,
	0,
	OCI_ATTR_DATA_SIZE,
	pquery->pdb->m_hpErr )));
    if(FAILED(hr))
    {
      db_query_close(pquery);
      return -1;
    }
    // Type conversions
    pField->m_type =SQL_TYPE_STR;
    if ( pField->wType == SQLT_LNG )
    {
      // LONG size
      pField->wSize = 32760;
    }
    else if ( pField->wType == SQLT_DAT )
    {
      // String is bound to DATE
      pField->wType = SQLT_STR;
      pField->wSize = 50;
      pField->m_type =SQL_TYPE_DATE;
    }
    else if ( pField->wType == SQLT_NUM )
    {
      // String is bound to NUMBER
      pField->wType = SQLT_STR;
      pField->wSize = 30; // allow for null-terminator
    }
    // Get column name
    ub4 nNameLen =0;
text *p =NULL;
    CheckErr(pquery->pdb, (hr =OCIAttrGet( pFieldAttr, OCI_DTYPE_PARAM,
	(void**)&p,
	&nNameLen,
	OCI_ATTR_NAME,
	pquery->pdb->m_hpErr )));

strncpy(pField->name, p, nNameLen);
pField->name[nNameLen] =0;
//printf("name=%s, size=%d\n", pField->name,pField->wSize); 

//printf("debug:oracle.c:db_query_select:field name %s, \tnNameLen=%d\n",pField->name, nNameLen);

    upper_case(pField->name);

    // Set size and name
    pField->pBuffer =(dvoid *)malloc( pField->wSize+1 );
    memset(pField->pBuffer, 0, pField->wSize+1);
    //pField->csName.Format( "%.*s", nNameLen, pName );
    //FieldValues[FieldCount-1] =(char *)malloc(pField->wSize+1);
    //memset(FieldValues[FieldCount-1], 0, pField->wSize+1);
    pquery->FieldValues[pquery->FieldCount-1] =(char *)pField->pBuffer; 
  }

  // Bind storage for receiving input variables
  OCIDefine *pDefn; // to hold pointer to field definition
  int iField;
  for ( iField=0; iField<pquery->FieldCount; ++iField )
  {
    // Get pointer to field structure
    BindField* pField = pquery->m_BindFields[iField];

    // Bind
    pDefn = NULL;
    CheckErr(pquery->pdb, (hr =OCIDefineByPos( pquery->m_hpSelect,
	&pDefn, // function allocs and gives back a pointer to field definition
	pquery->pdb->m_hpErr,
	iField+1, // position in select starting at 1
	pField->pBuffer, // storage area for field result
	pField->wSize, // sizeof storage area
	pField->wType, // field type
	&pField->nInd, // indp, null indicator
	&pField->wLen, // rlenp
	NULL,
	OCI_DEFAULT )));
    if(FAILED(hr))
    {
      db_query_close(pquery);
      return -1;
    }
  }

  // Fetch
  //FetchNext();

  return pquery->FieldCount;
}

int db_query_fetch(DB_QUERY_INFO *pquery)
{
  HRESULT hr;

  CheckErr(pquery->pdb, (hr =OCIStmtFetch( pquery->m_hpSelect,
		pquery->pdb->m_hpErr,
		1, // 'nrows' i.e. max rows
		OCI_FETCH_NEXT, 
		OCI_DEFAULT )));

  // Set result to 0 if no data returned
  if ( SUCCEEDED(hr) && hr != OCI_NO_DATA )
  {
    pquery->RecCount ++;
    for(int i=0; i<pquery->FieldCount; i++)
    {
      pquery->FieldValues[i][pquery->m_BindFields[i]->wLen] =0;
      del_lr_spaces(pquery->FieldValues[i]);
    }
  }
  else if(hr ==OCI_NO_DATA)
    return 0;
  else return -1;

  return 1;
}

int db_query_is_eos(DB_QUERY_INFO *pquery)
{
	return FALSE;
}

char *get_field_value(DB_QUERY_INFO *pquery, int field_num)
{
  if(field_num >=pquery->FieldCount) return NULL;
  return pquery->FieldValues[field_num];
}

char *get_field_value2(DB_QUERY_INFO *pquery, int field_num, char *buf)
{
  *buf =0;
  if(field_num >=pquery->FieldCount) return NULL;
  strcpy(buf, pquery->FieldValues[field_num]);
  return buf;
}

char *get_field_value_by_name(DB_QUERY_INFO *pquery, char *field_name)
{
  if(field_name ==NULL) return NULL;

  upper_case(field_name);
  for(int i =0; i<pquery->FieldCount; i++)
  {
    if(!strcmp(field_name, pquery->m_BindFields[i]->name))
    {
      return pquery->FieldValues[i];
    }
  }
  return NULL;
}

char *get_field_value_by_name2(DB_QUERY_INFO *pquery, char *field_name, char *buf)
{
  *buf =0;
  if(field_name ==NULL) return NULL;

  upper_case(field_name);
  for(int i =0; i<pquery->FieldCount; i++)
  {
    if(!strcmp(field_name, pquery->m_BindFields[i]->name))
    {
      strcpy(buf, pquery->FieldValues[i]);
      return buf;
    }
  }
  return NULL;
}

int db_exec_bind_long(DB_INFO *pdb, char *sql_stmt, int count, char **long_values, int *long_sizes)
{
  HRESULT hr;
  OCIStmt *hpstmt;

  CheckErr(pdb, (hr =OCIHandleAlloc( pdb->m_hpEnv,
	(void**)&hpstmt, // ref to statement handle pointer
	OCI_HTYPE_STMT, // type of handle being allocated
	0, NULL )));
  if(FAILED(hr))
  {
    printf("OCIHandleAlloc failed!\n");
    return -1;
  }
  // Associate statement string with handle
  CheckErr(pdb, (hr =OCIStmtPrepare( hpstmt,
	pdb->m_hpErr, // error handle pointer
	(text *)sql_stmt, // statement string
	strlen(sql_stmt),
	OCI_NTV_SYNTAX, // specify native syntax
	OCI_DEFAULT )));
  if(FAILED(hr))
  {
    OCIHandleFree( hpstmt, OCI_HTYPE_STMT );
    return -1;
  }
  
  int i;
  OCIBind *pbind[20];

  memset(pbind, 0, sizeof(pbind));
  for(i=0; i<count && i<20; i++)
  {
    if(OCIBindByPos(hpstmt,&pbind[i], pdb->m_hpErr, i+1,
        (dvoid *)long_values[i], (ub4)long_sizes[i],
        SQLT_LNG/*LBI long binary type */, NULL, NULL, NULL, 0,
        NULL, OCI_DEFAULT) !=0)
    {
      OCIHandleFree(hpstmt, OCI_HTYPE_STMT);
      return -1;
    }
  }
  // Execute but don't fetch yet
  CheckErr(pdb, (hr =(OCIStmtExecute( pdb->m_hpContext,
	hpstmt, // prepared by previous function calls
	pdb->m_hpErr,
	1, // 'iters' i.e. max rows to fetch during this call
	0, NULL, NULL, OCI_DEFAULT ))));


  if (FAILED(hr))
  {
  OCIHandleFree(hpstmt, OCI_HTYPE_STMT);
    return -1;
  }

  //if(pdb->m_auto_commit) db_commit(pdb);

  OCIHandleFree(hpstmt, OCI_HTYPE_STMT);
  return 0;
}