h_oracle.cpp

使用OCI类的API函数

//---------------------------------------------------------------------------
#include "vcl_stl.h"
#pragma hdrstop
/*#include "h_base.h"
#include "h_xml.h"
#include "h_tea.h"
#include "h_global.h"
using namespace h_base;
using namespace h_xml;
using namespace h_tea;*/

#include "h_oracle.h"
//#include "CppSQLite3.h"
//---------------------------------------------------------------------------
set<AnsiString> HOracle::BlackList;

//Add by Liaoyangbo
//增加大对象类型列表
set<AnsiString> HOracle::BlobList;
//End by Liaoyangbo

//Add by Liaoyangbo
void __fastcall HOracle::prepareBlob(void)
{
    static char *BlobMeta[] =
    {
        "BLOB",
        "CLOB",
        "NCLOB",
        "BFILE",
        NULL
    };

    for (int i = 0; BlobMeta[i] != NULL; i++)
    {
        BlobList.insert(BlobMeta[i]);
    }
}
//End by Liaoyangbo

void __fastcall HOracle::Prepare(void)
{
   static char *SpecialSchemas[] =
   {
      "ANONYMOUS",
      "CTXSYS",
      "DBSNMP",
      "DMSYS",
      "OCITEST",
      "ODM",
      "ODM_MTR",
      "OE",
      "OLAPSYS",
      "ORDPLUGINS",
      "ORDSYS",
      "OUTLN",
      "PM",
      "QS",
      "QS_ADM" ,
      "QS_CB",
      "QS_CBADM",
      "QS_CS",
      "QS_ES",
      "QS_OS",
      "QS_WS",
      "RMAN",
      "SYS",
      "SYSTEM",
      "SYSMAN",
      "WKPROXY",
      "WK_TEST",
      "WKSYS",
      "WMSYS",
      "XDB",
      NULL
   };
   for( int i = 0; SpecialSchemas[i] != NULL; i++ )
      BlackList.insert(SpecialSchemas[i]);
}

bool __fastcall HOracle::SchemaInBlackList(const AnsiString &Schema)
{
   return BlackList.find(Schema) != BlackList.end();
}

bool __fastcall HOracle::Init(void)
{
   Prepare();
  
   sword r;
   //r = OCIEnvCreate(&FEnvhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);

   r = OCIEnvCreate(&FEnvhp, OCI_THREADED, NULL, NULL, NULL, NULL, 0, NULL);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
      return false;

   FInitOk = true;
   return true;
}

__fastcall HOracle::HOracle(void)
{
   FErrhp = (OCIError *)OCI_INVALID_HANDLE;
   FSrvhp = (OCIServer *)OCI_INVALID_HANDLE;
   FSvchp = (OCISvcCtx *)OCI_INVALID_HANDLE;
   FSsnhp = (OCISession *)OCI_INVALID_HANDLE;

   FConnected = false;
   Init();
}

__fastcall HOracle::~HOracle(void)
{
   Disconnect();
   FreeHandles();
}

bool __fastcall HOracle::AllocateHandles(void)
{
   sword r;

   dvoid *tmp;
   
   //r = OCIHandleAlloc(FEnvhp, (dvoid**)&FErrhp, OCI_HTYPE_ERROR, 0, NULL);
   r = OCIHandleAlloc(FEnvhp, (dvoid**)&FErrhp, OCI_HTYPE_ERROR, 52, (dvoid **)&tmp);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
   	//ShowMessage("new gpm. Return:" + IntToStr(r));
      CreateErrorString("OCIHandleAlloc(Env) : ", false);
      //if( MessageDlg("error, return" + IntToStr(r) + ", Continue?",
      //       mtConfirmation, TMsgDlgButtons() << mbYes << mbNo, 0) != mrYes )
      goto error;

   }
   
   //r = OCIHandleAlloc(FEnvhp, (dvoid**)&FSrvhp, OCI_HTYPE_SERVER, 0, NULL);
   r = OCIHandleAlloc(FEnvhp, (dvoid**)&FSrvhp, OCI_HTYPE_SERVER, 52, (dvoid **)&tmp);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIHandleAlloc(Server) : ");
      goto error;
   }


   //r = OCIHandleAlloc(FEnvhp, (dvoid**)&FSvchp, OCI_HTYPE_SVCCTX, 0, NULL);
   r = OCIHandleAlloc(FEnvhp, (dvoid**)&FSvchp, OCI_HTYPE_SVCCTX, 52, (dvoid **)&tmp);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIHandleAlloc(Svcctx) : ");
      goto error;
   }

   //r = OCIHandleAlloc(FEnvhp, (dvoid**)&FSsnhp, OCI_HTYPE_SESSION, 0, NULL);
   r = OCIHandleAlloc(FEnvhp, (dvoid**)&FSsnhp, OCI_HTYPE_SESSION, 0, (dvoid**)0);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIHandleAlloc(Session) : ");
      goto error;
   }

   //OCI_HTYPE_TRANS
   /*
   r = OCIHandleAlloc((dvoid *)FEnvhp, (dvoid **)&FTxnhp, OCI_HTYPE_TRANS, 0, 0);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
        CreateErrorString("OCIHandleAlloc(OCITrans) : ");
        goto error;
   }
   */


   return true;

error :

   FreeHandles();
   return false;
}

void __fastcall HOracle::FreeHandles(void)
{
   if( FErrhp != (OCIError *)OCI_INVALID_HANDLE )
      OCIHandleFree(FErrhp, OCI_HTYPE_ERROR);

   if( FSrvhp != (OCIServer *)OCI_INVALID_HANDLE )
      OCIHandleFree(FSrvhp, OCI_HTYPE_SERVER);

   if( FSvchp != (OCISvcCtx *)OCI_INVALID_HANDLE )
      OCIHandleFree(FSvchp, OCI_HTYPE_SVCCTX);

   if( FSsnhp != (OCISession *)OCI_INVALID_HANDLE )
      OCIHandleFree(FSvchp, OCI_HTYPE_SESSION); 

   // free OCI_HTYPE_TRANS
   /*
   if( FTxnhp  != (OCITrans *)OCI_INVALID_HANDLE )
        OCIHandleFree(FTxnhp, OCI_HTYPE_TRANS);
   */

   if( FEnvhp != (OCIEnv *)OCI_INVALID_HANDLE )
      OCIHandleFree(FEnvhp, OCI_HTYPE_ENV);


   FErrhp = (OCIError *)OCI_INVALID_HANDLE;
   FSrvhp = (OCIServer *)OCI_INVALID_HANDLE;
   FSvchp = (OCISvcCtx *)OCI_INVALID_HANDLE;
   FSsnhp = (OCISession *)OCI_INVALID_HANDLE;

   //OCITrans
   //FTxnhp = (OCITrans *)OCI_INVALID_HANDLE;

   FEnvhp = (OCIEnv *)OCI_INVALID_HANDLE;
}

int __fastcall HOracle::CreateErrorString(const AnsiString &Text, bool Env)
{
   text errbuf[512];
   sb4 errcode;

   errbuf[0] = '\0';

   if( Env )
      OCIErrorGet(FEnvhp, 1, NULL, &errcode, errbuf,
         sizeof(errbuf), OCI_HTYPE_ENV);
   else
      OCIErrorGet(FErrhp, 1, NULL, &errcode, errbuf,
         sizeof(errbuf), OCI_HTYPE_ERROR);

   FErrorString = Text + ":" + IntToStr(errcode) + ": " + AnsiString((char*)errbuf, sizeof(errbuf)).TrimRight();
	if (Env)
   {
   	if (FEnvhp == NULL)
      {
      	FErrorString += ". And Env is NULL!!";
        // ShowMessage(FErrorString);
      }

   }
   return errcode;
}

bool __fastcall HOracle::Connect(const AnsiString &DatabaseName,
   const AnsiString &UserId, const AnsiString &Password,
   bool Sysdba)
{
   sword r;

   if( !AllocateHandles() )
      return false;



   r = OCIServerAttach(FSrvhp, FErrhp, DatabaseName.c_str(),
      DatabaseName.Length(), OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIServerAttach : ");
      FreeHandles();
      return false;
   }

   r = OCIAttrSet(FSvchp, OCI_HTYPE_SVCCTX, FSrvhp,
      0, OCI_ATTR_SERVER, FErrhp);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(SERVER) : ");
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   /* set the external name and internal name in server handle */
   r = OCIAttrSet((dvoid *)FSrvhp, OCI_HTYPE_SERVER, (dvoid *) "demo", 0,
       OCI_ATTR_EXTERNAL_NAME, FErrhp);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(SERVER) : ");
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   r = OCIAttrSet((dvoid *)FSrvhp, OCI_HTYPE_SERVER, (dvoid *) "txn demo", 0,
       OCI_ATTR_INTERNAL_NAME, FErrhp);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(SERVER) : ");
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   r = OCIAttrSet(FSsnhp, OCI_HTYPE_SESSION,
      UserId.c_str(), UserId.Length(), OCI_ATTR_USERNAME, FErrhp);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(USERNAME) : ");
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   r = OCIAttrSet(FSsnhp, OCI_HTYPE_SESSION,
      Password.c_str(), Password.Length(), OCI_ATTR_PASSWORD, FErrhp);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(USERNAME) : ");
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   r = OCISessionBegin(FSvchp, FErrhp, FSsnhp, OCI_CRED_RDBMS,
      Sysdba ? OCI_SYSDBA : OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCISessionBegin : ");
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   r = OCIAttrSet(FSvchp, OCI_HTYPE_SVCCTX,
      FSsnhp, 0, OCI_ATTR_SESSION, FErrhp);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(SESSION) : ");
      OCISessionEnd(FSvchp, FErrhp, FSsnhp, OCI_DEFAULT);
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }

   //OCI_ATTR_TRANS
   /*
   r = OCIAttrSet(FSvchp, OCI_HTYPE_SVCCTX, FTxnhp, 0, OCI_ATTR_TRANS, FErrhp);
   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIAttrSet(TRANS) : ");
      OCISessionEnd(FSvchp, FErrhp, FSsnhp, OCI_DEFAULT);
      OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
      FreeHandles();
      return false;
   }
   */


  /*
   for( int i = 0; i <Dbs.size(); i++ )
   {
      if( Dbs[i].DSN == DatabaseName  && Dbs[i].DbStatus != dbsNull)
      {
         DbIndex = i;
         break;
      }
   }
   */

   FConnected = true;
   return true;
}

void __fastcall HOracle::Disconnect(void)
{
   if( !FConnected )
      return;

   OCISessionEnd(FSvchp, FErrhp, FSsnhp, OCI_DEFAULT);
   OCIServerDetach(FSrvhp, FErrhp, OCI_DEFAULT);
   FConnected = false;
}

bool __fastcall HOracle::GetTableInfoList(vector <TableDes> &TableList, AnsiString DBName)
{       
   int i=1;
   sword r;
   OCIStmt *Stmthp1 = (OCIStmt*)OCI_INVALID_HANDLE;
   char Owner[30 + 1];
   char TableName[30 + 1];
   char TableSpace[30 + 1];
   char RowNumber[10 + 1];
   //char TableType[12 + 1];
   OCIDefine *Defnp;
   //char* TableInfoName = (AnsiString(DBName + "_TableInfo")).c_str();
   //char* ColumnInfoName = AnsiString(DBName + "_ColumnInfo").c_str();
   AnsiString SqlStr1 = AnsiString("select owner, table_name,  NVL(tablespace_name,chr(0)),")
   +  " NVL(num_rows,0)  from dba_all_tables "  +
   " where owner NOT IN ('SYS', 'SYSTEM') order by owner, table_name";

   TableList.clear();
   r = OCIHandleAlloc(FEnvhp, (dvoid**)&Stmthp1, OCI_HTYPE_STMT, 0, NULL);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIHandleAlloc(Stmt1) : ");
      goto error;
   }

   r = OCIStmtPrepare(Stmthp1, FErrhp, SqlStr1.c_str(),
      SqlStr1.Length(), OCI_NTV_SYNTAX, OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIStmtPrepare(SqlStr1) : ");
      goto error;
   }

   Defnp = NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp, FErrhp, 1, Owner,
      sizeof(Owner) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIDefineByPos : ");
      goto error;
   }

   Defnp = NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp, FErrhp, 2, TableName,
      sizeof(TableName) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIDefineByPos : ");
      goto error;
   }

   Defnp = NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp, FErrhp, 3, TableSpace,
      sizeof(TableSpace) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIDefineByPos : ");
      goto error;
   }