oracle.c

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

/*
 copyright: Paladin of http://www.programsalon.com
*/

#include <stdio.h>
#include <stdlib.h>

#include "oracle.h"
#include "../util/util.h"

extern char *del_lr_spaces(char *pbuf);

int db_init(DB_INFO *pdb)
{
  HRESULT hr;

  pdb->m_hpEnv = NULL;
  pdb->m_hpErr = NULL;
  pdb->m_hpServer = NULL;
  pdb->m_hpContext = NULL;
  pdb->m_szError[0] =0;
  pdb->m_hpSession =NULL;
  pdb->m_pOracleTypes =NULL;
  pdb->m_auto_commit =0;

  CheckErr(pdb, (hr=OCIInitialize( OCI_OBJECT, NULL, NULL, NULL, NULL ) ));
  if(FAILED(hr))
    return -1;

  CheckErr(pdb, (hr =OCIEnvInit( &pdb->m_hpEnv, OCI_DEFAULT, 0, NULL )) );
  if ( FAILED(hr) )
  {
    printf("OCIEnvInit failed!\n");
    return -2;
  }

  // Allocate error, server, and service context handles
  OCIHandleAlloc( pdb->m_hpEnv, (void**)&pdb->m_hpErr, OCI_HTYPE_ERROR, 0, NULL );
  OCIHandleAlloc( pdb->m_hpEnv, (void**)&pdb->m_hpServer, OCI_HTYPE_SERVER, 0, NULL );
  OCIHandleAlloc( pdb->m_hpEnv, (void**)&pdb->m_hpContext, OCI_HTYPE_SVCCTX, 0, NULL );
  OCIHandleAlloc( pdb->m_hpEnv, (void**)&pdb->m_hpSession, OCI_HTYPE_SESSION, 0, NULL);

}

int db_exit(DB_INFO *pdb)
{
  HRESULT hr;

  db_close(pdb);

  if(pdb->m_hpSession)
    OCIHandleFree(pdb->m_hpSession, OCI_HTYPE_SESSION);
  // Free server handle
  if (pdb->m_hpServer)
    CheckErr(pdb, (hr =OCIHandleFree(pdb->m_hpServer, OCI_HTYPE_SERVER)));

  // Free service context
  if (pdb->m_hpContext)
    OCIHandleFree( pdb->m_hpContext, OCI_HTYPE_SVCCTX);

  // Free error handle
  if (pdb->m_hpErr)
    OCIHandleFree( pdb->m_hpErr, OCI_HTYPE_ERROR );

  int i =0;
  if(pdb->m_pOracleTypes)
  {
    while(pdb->m_pOracleTypes[i] !=NULL)
      free(pdb->m_pOracleTypes[i++]);
    free(pdb->m_pOracleTypes);
    pdb->m_pOracleTypes =NULL;
  }
}

int db_set_auto_commit(DB_INFO *pdb, int if_auto)
{
  pdb->m_auto_commit =if_auto; 
  return 0;
}

void CheckErr(DB_INFO *pdb, HRESULT status )
{
  static struct CErrorPair
  {
    sword w;
    const char* szText;
  }epaErrors[] =
  {
    OCI_SUCCESS, "SUCCESS",
    OCI_SUCCESS_WITH_INFO, "SUCCESS_WITH_INFO",
    OCI_NEED_DATA, "NEED_DATA",
    OCI_NO_DATA, "NO_DATA",
    OCI_INVALID_HANDLE, "INVALID_HANDLE",
    OCI_STILL_EXECUTING, "STILL_EXECUTE",
    OCI_CONTINUE, "CONTINUE",
  };

  if ( status == OCI_ERROR )
  {
    sb4 errcode = 0;
    OCIErrorGet(pdb->m_hpErr, 1, NULL, &errcode, (unsigned char*)pdb->m_szError, sizeof(pdb->m_szError), OCI_HTYPE_ERROR);
  }
  else
  {
    // Look for description in static array
    DWORD nTotalResultCodes = sizeof(epaErrors) / sizeof(struct CErrorPair);
    for ( int iPos = 0; iPos < sizeof(epaErrors); ++ iPos )
    {
      if ( epaErrors[iPos].w == status )
      {
        strcpy( pdb->m_szError, epaErrors[iPos].szText );
        break;
      }
    }
  }
}

char *db_get_error_string(DB_INFO *pdb)
{
  return pdb->m_szError;
}


int db_connect(DB_INFO *pdb, char *server, char *user, char *pass)
{
  HRESULT hr;

  // Associate TNS with server handle
  CheckErr(pdb, (hr =OCIServerAttach( pdb->m_hpServer,	pdb->m_hpErr,
	(unsigned char*)(char*)server, strlen(server),0 )));
  if ( FAILED(hr) )
  {
    printf("OCIServerAttach failed!\n");
    return -3;
  }

  // Get server version string
  char version[1024];
  CheckErr(pdb, (hr =OCIServerVersion( pdb->m_hpServer, pdb->m_hpErr,
    (text*)version, sizeof(version), OCI_HTYPE_SERVER )));
  if ( FAILED(hr) )
    return -4;
  //m_csLog += csVersion + "\n";

  // Specify server handle to service context
  CheckErr(pdb, (hr =OCIAttrSet( pdb->m_hpContext,	OCI_HTYPE_SVCCTX,
	pdb->m_hpServer,	0, OCI_ATTR_SERVER,	pdb->m_hpErr )));
  if ( FAILED(hr) )
     return -5;

  // Allocate a session handle

  // Associate username with session handle
  OCIAttrSet( pdb->m_hpSession, OCI_HTYPE_SESSION, (void*)(const char*)user,
  strlen(user),	OCI_ATTR_USERNAME, pdb->m_hpErr);

  // Associate password with session handle
  OCIAttrSet( pdb->m_hpSession, OCI_HTYPE_SESSION, (void*)(const char*)pass,
    strlen(pass), OCI_ATTR_PASSWORD, pdb->m_hpErr);

  // Open session using service context and session handle
  CheckErr(pdb, (hr =OCISessionBegin( pdb->m_hpContext, pdb->m_hpErr,
	pdb->m_hpSession, OCI_CRED_RDBMS, OCI_DEFAULT )));
  if ( FAILED(hr) )
    return -6;

  // Specify session handle to service context
  OCIAttrSet( pdb->m_hpContext, OCI_HTYPE_SVCCTX, pdb->m_hpSession,
	0, OCI_ATTR_SESSION, pdb->m_hpErr);

  // Change date format
  if ( SUCCEEDED(hr) )
  {
    char *csSQL = "alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'";
    if(db_exec(pdb, csSQL ) <0)
      return -7;
  }
  else return -8;

  return TRUE;
}

int db_close(DB_INFO *pdb)
{
  HRESULT hr;

  if(pdb->m_auto_commit) db_commit(pdb);
  // Free select statement handle

  // Detach server from server handle
  if(pdb->m_hpSession)
  {
    OCISessionEnd(pdb->m_hpContext, pdb->m_hpErr, pdb->m_hpSession, OCI_DEFAULT);
  }

  if (pdb->m_hpServer)
    OCIServerDetach( pdb->m_hpServer, pdb->m_hpErr, OCI_DEFAULT );


  if(FAILED(hr)) return FALSE;

  return TRUE;
}

int db_exec(DB_INFO *pdb, char *stmt)
{
  HRESULT hr;

  OCIStmt *hpStatement = NULL;
  CheckErr(pdb, (hr =OCIHandleAlloc( pdb->m_hpEnv,(void**)&hpStatement, OCI_HTYPE_STMT, 0, NULL )));
  if(FAILED(hr))
    return -1;
  // Associate statement string with handle
  CheckErr(pdb, (hr =OCIStmtPrepare( hpStatement,	pdb->m_hpErr,
     (text *)stmt, strlen(stmt),	OCI_NTV_SYNTAX,	OCI_DEFAULT )));

  if(FAILED(hr))
  {
    if(hpStatement)
      OCIHandleFree( hpStatement, OCI_HTYPE_STMT );
    return -2;
  }
  // Execute statement
  CheckErr(pdb, (hr =OCIStmtExecute( pdb->m_hpContext,	hpStatement,
	pdb->m_hpErr,1,	0, // row offset
	NULL, NULL, // snapshot in/out
	OCI_DEFAULT )));
  // Free statement handle

  if(FAILED(hr))
  {
    OCIHandleFree( hpStatement, OCI_HTYPE_STMT );
    return -3;
  }

  if(pdb->m_auto_commit) db_commit(pdb);
  int row_count =0;

  CheckErr(pdb, (hr=OCIAttrGet(hpStatement, OCI_HTYPE_STMT, (ub2 *)&row_count, (ub4 *)0, OCI_ATTR_ROW_COUNT, pdb->m_hpErr)));

  OCIHandleFree( hpStatement, OCI_HTYPE_STMT );

  if(FAILED(hr))
    return 0;
  else return row_count;
}

#if defined(__STDC__) || defined(__cplusplus)
int db_begin_trans(DB_INFO *pdb)
#else
int db_begin_trans(pdb)
DB_INFO *pdb;
#endif
{
  if(FAILED(OCITransStart(pdb->m_hpContext, pdb->m_hpErr, 10, OCI_TRANS_NEW)))
    return -1;
  return 0;
}

#if defined(__STDC__) || defined(__cplusplus)
int db_roll_back(DB_INFO *pdb)
#else
int db_roll_back(pdb)
DB_INFO *pdb;
#endif
{
  if(FAILED(OCITransRollback(pdb->m_hpContext, pdb->m_hpErr, 0)))
    return -1;
  return 0;
}

#if defined(__STDC__) || defined(__cplusplus)
int db_commit(DB_INFO *pdb)
#else
int db_commit(DB_INFO *pdb)
DB_INFO *pdb;
#endif 
{
  if(FAILED(OCITransCommit(pdb->m_hpContext, pdb->m_hpErr, 0)))
    return -1;
  return 0;
}

int db_query_init(DB_QUERY_INFO *pquery, DB_INFO *pdb)
{
  pquery->pdb=pdb;
  pquery->m_hpSelect =NULL;
  pquery->m_BindFields =NULL;
  pquery->FieldValues =NULL;
}

void db_query_close(DB_QUERY_INFO *pquery)
{
  if(pquery->m_hpSelect)
    OCIHandleFree( pquery->m_hpSelect, OCI_HTYPE_STMT );
  pquery->m_hpSelect =NULL;
  if(pquery->m_BindFields)
  {
    for(int i =0; i<pquery->FieldCount; i++)
    {
      if(pquery->m_BindFields[i])
      {
        if(pquery->m_BindFields[i]->pBuffer)
        free(pquery->m_BindFields[i]->pBuffer);
        free(pquery->m_BindFields[i]);
      }
    }
    free(pquery->m_BindFields);
    pquery->m_BindFields =NULL;
  }
  if(pquery->FieldValues) free(pquery->FieldValues);
  pquery->FieldValues =NULL;
}

int db_query_select(DB_QUERY_INFO *pquery, char *stmt)
{
  HRESULT hr;