h_oracle.cpp

使用OCI类的API函数

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

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

   r = OCIStmtExecute(FSvchp, Stmthp1, FErrhp, 0, 0,
      NULL, NULL, OCI_DEFAULT);

   if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
   {
      CreateErrorString("OCIStmtExecute(Stmthp1) : ");
      goto error;
   }
   
   for(;;)
   {
      r = OCIStmtFetch2(Stmthp1, FErrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
      if( r == OCI_NO_DATA )
         break;
      if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
      {
         CreateErrorString("OCIStmtFetch2 : ");
         goto error;
      }
      Owner[30] = '\0';
      TableName[30] = '\0';
      TableSpace[30]= '\0';
      //TableType[12] = '\0';
      RowNumber[10] = '\0';

      TableDes  aTable;
      aTable.TableOwner  = AnsiString(Owner).TrimRight();
      aTable.TableName  = AnsiString(TableName).TrimRight();
      aTable.TableSpace  = AnsiString(TableSpace).TrimRight();
      aTable.Rows   = StrToInt(AnsiString(RowNumber).TrimRight());
      aTable.GetColOk = "no";
      aTable.GetIndexOk = false;
      aTable.GetKeyOk = false;
      //aTable.Type = AnsiString(TableType).TrimRight();

      //add 2007-05-24
      //GetTableCol(aTable,aTable.Fields,i,DBName);
      i++;
      TableList.push_back(aTable);
   }

   OCIHandleFree(Stmthp1, OCI_HTYPE_STMT);
   return true;

error :

   if( Stmthp1 != (OCIStmt*)OCI_INVALID_HANDLE )
      OCIHandleFree(Stmthp1, OCI_HTYPE_STMT);

   return false;
}

bool  __fastcall HOracle::GetTableCol(AnsiString tableName, AnsiString tableOwner,
     vector <ColumnDes> &TableCols)
{
   int i;
   sword r;
   OCIStmt *Stmthp1 = (OCIStmt*)OCI_INVALID_HANDLE;
   /*
   char ColumnName[30 + 1];
   memset(ColumnName, 0, sizeof(ColumnName));
   char DataType[30+1];
   char DataLength[10+1];
   char DataPrecision[10+1];
   char DataScale[10+1];
   char Nullable[1+1];
   char colID[10 + 1];
   */

   typedef struct
   {
      char ColumnName[30 + 1];
      //memset(ColumnName, 0, sizeof(ColumnName));
      char DataType[30+1];
      char DataLength[10+1];
      char DataPrecision[10+1];
      char DataScale[10+1];
      char Nullable[1+1];
      char colID[10 + 1];
   } t_std;
   t_std tstd[100] = {0};

   //Add by Liaoyangbo
   bool isLob = false;
   //增加LONG类型字段的判断
   bool isLong = false;
   //End by Liaoyangbo


   OCIDefine *Defnp;

   AnsiString SqlStr1 =
      AnsiString("select column_name, substr(data_type,0,30), data_length, NVL(data_precision,0),")
       + " NVL(data_scale,0), nullable, column_id from dba_tab_columns where table_name='"
       + tableName + "' and owner ='" + tableOwner + "'";


   TableCols.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;
   }


   OCIDefine *Defnp1 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp1, FErrhp, 1, &tstd[0].ColumnName,
      sizeof(tstd[0].ColumnName) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   OCIDefine *Defnp2 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp2, FErrhp, 2, &tstd[0].DataType,
      sizeof(tstd[0].DataType) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   OCIDefine *Defnp3 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp3, FErrhp, 3, &tstd[0].DataLength,
      sizeof(tstd[0].DataLength) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   OCIDefine *Defnp4 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp4, FErrhp, 4, &tstd[0].DataPrecision,
      sizeof(tstd[0].DataPrecision) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   OCIDefine *Defnp5 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp5, FErrhp, 5, &tstd[0].DataScale,
      sizeof(tstd[0].DataScale) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   OCIDefine *Defnp6 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp6, FErrhp, 6, &tstd[0].Nullable,
      sizeof(tstd[0].Nullable) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   OCIDefine *Defnp7 = (OCIDefine *) NULL;
   r = OCIDefineByPos(Stmthp1, &Defnp7, FErrhp, 7, &tstd[0].colID,
      sizeof(tstd[0].colID) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);

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

   r = OCIDefineArrayOfStruct(Defnp1, FErrhp, sizeof(t_std), 0, 0, 0);
   r = OCIDefineArrayOfStruct(Defnp2, FErrhp, sizeof(t_std), 0, 0, 0);
   r = OCIDefineArrayOfStruct(Defnp3, FErrhp, sizeof(t_std), 0, 0, 0);
   r = OCIDefineArrayOfStruct(Defnp4, FErrhp, sizeof(t_std), 0, 0, 0);
   r = OCIDefineArrayOfStruct(Defnp5, FErrhp, sizeof(t_std), 0, 0, 0);
   r = OCIDefineArrayOfStruct(Defnp6, FErrhp, sizeof(t_std), 0, 0, 0);
   r = OCIDefineArrayOfStruct(Defnp7, FErrhp, sizeof(t_std), 0, 0, 0);

   r = OCIStmtExecute(FSvchp, Stmthp1, FErrhp, 0, 0,
      NULL, NULL, OCI_DEFAULT);

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


   for(;;)
   {
      r = OCIStmtFetch2(Stmthp1, FErrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
      if( r == OCI_NO_DATA )
         break;
      if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
      {
         CreateErrorString("OCIStmtFetch2 : ");
         goto error;
      }
      /*
      tstd[0].ColumnName[30] = '\0';
      DataType[30] = '\0';
      DataLength[10] = '\0';
      DataPrecision[10] = '\0';
      DataScale[10]='\0';
      Nullable[1]='\0';
      colID[10] = '\0';
      */  

      for (i = 0; i < 100; i++)
      {
         ColumnDes aColumn;
         aColumn.ColumnName = AnsiString(tstd[i].ColumnName).TrimRight();
         if (aColumn.ColumnName.IsEmpty())
            break;
         aColumn.DataType = AnsiString(tstd[i].DataType).TrimRight();
         sscanf(tstd[i].DataLength, "%d", &aColumn.DataLength);
         sscanf(tstd[i].DataPrecision, "%d", &aColumn.DataPrecision);
         sscanf(tstd[i].DataScale, "%d", &aColumn.DataScale);
         if (tstd[i].Nullable[0] == 'y' || tstd[i].Nullable[0] == 'Y')
            aColumn.IsNull = true;
         else
            aColumn.IsNull = false;

         //Add by Liaoyangbo
         sscanf(tstd[i].colID, "%d", &aColumn.ColId);
      
         //增加对大对象的判断
         /*if (BlobList.find(aColumn.DataType) != BlobList.end())
         {
            //该字段为大对象
            aColumn.IsBlob = true;
            //存在大对象（表）
            isLob = true;
         }
         else
         {
            aColumn.IsBlob = false;
         }*/
         //判断该字段是否为LONG类型字段
         if (aColumn.DataType.UpperCase() == "LONG")
         {
            //该字段为LONG类型
            aColumn.IsLong = true;
            isLong = true;
         }
         else
         {
            aColumn.IsLong = false;
         }
         //End by Liaoyangbo

         TableCols.push_back(aColumn);
      }
   }

   //Add by Liaoyangbo
   //表中存在大对象
   if (isLob)
   {
       //Table.IsBlob = true;
   }
   else
   {
       //Table.IsBlob = false;
   }
   //表中是否存在LONG字段类型
   if (isLong)
   {
       //Table.hasLong = true;
   }
   else
   {
       //Table.hasLong = false;
   }
   //End by Liaoyangbo

   OCIHandleFree(Stmthp1, OCI_HTYPE_STMT);
   return true;

error :

   if( Stmthp1 != (OCIStmt*)OCI_INVALID_HANDLE )
      OCIHandleFree(Stmthp1, OCI_HTYPE_STMT);

   return false; 
}

bool __fastcall HOracle::GetTableDataInfo(AnsiString tableName, AnsiString tableOwner, vector <TableData> &TableDatas)
{
    sword r;
    OCIDefine *Defnp[4];

    typedef struct
    {
       char col1[10+1];
       char col2[10+1];
       char col3[10+1];
       char col4[10+1];
    }stTableData;

    stTableData tableData[100]= {0}; // 每次批量读取100行
    OCIStmt *Stmthp1 = (OCIStmt*)OCI_INVALID_HANDLE;
    string SqlStr1 = string("select * from ");
    SqlStr1 += tableOwner.c_str();
    SqlStr1 += string(".");
    SqlStr1 += tableName.c_str();
    //SqlStr1 += "";

    Defnp[0] = (OCIDefine *) 0;
    Defnp[1] = (OCIDefine *) 0;
    Defnp[2] = (OCIDefine *) 0;
    Defnp[3] = (OCIDefine *) 0;

    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[0] = NULL;
    r = OCIDefineByPos(Stmthp1, &Defnp[0], FErrhp, 1, &tableData[0].col1,
      sizeof(tableData[0].col1) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);
    if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
    {
        CreateErrorString("OCIDefineByPos : ");
        goto error;
    }

    Defnp[1] = NULL;
    r = OCIDefineByPos(Stmthp1, &Defnp[1], FErrhp, 2, &tableData[0].col2,
      sizeof(tableData[0].col2) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);
    if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
    {
        CreateErrorString("OCIDefineByPos : ");
        goto error;
    }

    Defnp[2] = NULL;
    r = OCIDefineByPos(Stmthp1, &Defnp[2], FErrhp, 3, &tableData[0].col3,
      sizeof(tableData[0].col3) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);
    if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
    {
        CreateErrorString("OCIDefineByPos : ");
        goto error;
    }

    Defnp[3] = NULL;
    r = OCIDefineByPos(Stmthp1, &Defnp[3], FErrhp, 4, &tableData[0].col4,
      sizeof(tableData[0].col4) - 1, SQLT_CHR, NULL, NULL,
      NULL, OCI_DEFAULT);
    if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
    {
        CreateErrorString("OCIDefineByPos : ");
        goto error;
    }
    r = OCIDefineArrayOfStruct(Defnp[0], FErrhp, sizeof(stTableData), 0, 0, 0);
    r = OCIDefineArrayOfStruct(Defnp[1], FErrhp, sizeof(stTableData), 0, 0, 0);
    r = OCIDefineArrayOfStruct(Defnp[2], FErrhp, sizeof(stTableData), 0, 0, 0);
    r = OCIDefineArrayOfStruct(Defnp[3], FErrhp, sizeof(stTableData), 0, 0, 0);

    r = OCIStmtExecute(FSvchp, Stmthp1, FErrhp, 0, 0, NULL, NULL, OCI_DEFAULT);
    if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
    {
        CreateErrorString("OCIStmtExecute(Stmthp1) : ");
        goto error;
    }

    for(;;)
    {
        r = OCIStmtFetch2(Stmthp1, FErrhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT); // OCI_FETCH_NEXT
        if( r == OCI_NO_DATA )
			break;
		if( r != OCI_SUCCESS && r != OCI_SUCCESS_WITH_INFO )
		{
			CreateErrorString("OCIStmtFetch2 : ");
			goto error;
		}
        for (int i = 0; i < 100; i++)
        {
            TableData aData;
            aData.col1 = AnsiString(tableData[i].col1).TrimRight();
            if (aData.col1.IsEmpty())
                break;

            aData.col2 = AnsiString(tableData[i].col2).TrimRight();
            aData.col3 = AnsiString(tableData[i].col3).TrimRight();
            aData.col4 = AnsiString(tableData[i].col4).TrimRight();
            TableDatas.push_back(aData);
        }
    }
    return true;

error:
   if( Stmthp1 != (OCIStmt*)OCI_INVALID_HANDLE )
      OCIHandleFree(Stmthp1, OCI_HTYPE_STMT);
   return false;
}