tinyoci.cpp

一个Oracle OCI编程接的C++封装

        // 有需要用到Indicator吗? 也许可以提供一些高级功能，如
        // 指定某行某列为“空值”。
        pTinyItem->IndicatorArray = new sb2[m_nIter];
        memset( pTinyItem->IndicatorArray, 0, m_nIter*sizeof(sb2) );
        
        m_Status = OCIBindByName( m_phStmtHandle, 
                                  (OCIBind**)&phBind,
                                  m_phErrorHandle, 
                                  (ub1*)pTinyItem->szName, 
                                  strlen( pTinyItem->szName ), 
                                  pTinyItem->pValueArray, 
                                  pTinyItem->lSize,
                                  pTinyItem->nType,
                                  pTinyItem->IndicatorArray, 
                                  (ub2 *) 0, 
                                  (ub2 *) 0, 
                                  (ub4)   0,
                                  (ub4*)  0,
                                  OCI_DEFAULT );
        if( OCI_SUCCESS != m_Status )
            return false;

        // 只有非SELECT语句才能支持数组输入绑定。
        if( ( m_nIter > 1 ) && !IsSelect() )
        {
            m_Status = OCIBindArrayOfStruct( (OCIBind*)phBind,
                                             (OCIError*)m_phErrorHandle,
                                             (ub4)pTinyItem->lSize,
                                             (ub4)sizeof(sb2),
                                             (ub4)0,
                                             (ub4)0
                                             );
            if( OCI_SUCCESS != m_Status )
                return false;
        }
    }

    return true;
}

/*M***************************************************************************
Method Name  : TinyOci::Define
            
Return       :                
Process      :
Note         : IndicatorArray is allocated here !
Author       : 
Date         :
Revision     :
***************************************************************************M*/
bool TinyOci::Define( void )
{
    OCIDefine   *phDefine;

    TinyItem *pTinyItem;
    int i;

    for(i=0; i<DefineNameList.GetCount(); i++ )
    {
        pTinyItem = (TinyItem *)DefineNameList.GetAt(i);
        
        pTinyItem->IndicatorArray = new sb2[m_lMaxFetch];
        memset( pTinyItem->IndicatorArray, 0, m_lMaxFetch * sizeof( sb2 ) );        

        m_Status = OCIDefineByPos( m_phStmtHandle, 
                                   (OCIDefine**)&phDefine,
                                   m_phErrorHandle, 
                                   i+1,
                                   pTinyItem->pValueArray, 
                                   pTinyItem->lSize, 
                                   pTinyItem->nType,
                                   pTinyItem->IndicatorArray, 
                                   (ub2 *) 0, 
                                   (ub2 *) 0, 
                                   OCI_DEFAULT );
        if( OCI_SUCCESS != m_Status )
            return false;

        if( m_lMaxFetch > 1 )
        {
            m_Status = OCIDefineArrayOfStruct(  (OCIDefine*)phDefine,
                                                (OCIError*)m_phErrorHandle,
                                                (ub4)pTinyItem->lSize,
                                                (ub4)sizeof(sb2),
                                                (ub4)0,
                                                (ub4)0
                                                );
            if( OCI_SUCCESS != m_Status )
                return false;
        }
    }

    return true;
}



/*M***************************************************************************
Method Name  : TinyOci::TranslateSizeType
            
Return       :                
Process      : Oracle Type System Type(Attribute geting) 
               to SQLT Type (bind,define).

Note         : Any OCI Type Code will be maped to the followings only:
               SQLT_FLT - double
               SQLT_INT - long
               SQLT_STR - sz char

               注：可以把所有基本数据类型都按 SQLT_STR 进行处理，但有以下的问题：
               1. 必须假定OCI_TYPECODE_NUMBER，OCI_TYPECODE_REAL，OCI_TYPECODE_DOUBLE，
                  OCI_TYPECODE_FLOAT，OCI_TYPECODE_DECIMAL，OCI_TYPECODE_OCTET，
                  OCI_TYPECODE_INTEGER，OCI_TYPECODE_SMALLINT类型的最大值，如假定
                  最多只能有十进制30位；
               2. 这样做可以很容易确定某一行的某一字段是否为“空值”:即是否为空字符串。

Author       : 
Date         :
Revision     :
***************************************************************************M*/
bool TinyOci::TranslateSizeType( sb4 &lSize, ub2 &nType, ub2 nScale, ub2 nPrecision )
{
    bool bRc;

    bRc = true;
    switch( nType )
    {
    case OCI_TYPECODE_REF:    
        bRc = false;
        break;

    case OCI_TYPECODE_DATE:
        nType = SQLT_STR;
        lSize = 21;
        break;

    case OCI_TYPECODE_NUMBER:        
        if( 0 != nScale )
        {
            nType = SQLT_FLT;
            lSize = sizeof(double);
        }
        else if( (0 == nScale) && (0 != nPrecision) )
        {
            nType = SQLT_INT;
            lSize = sizeof(long);
        }
        else
        {
            nType = SQLT_FLT;
            lSize = sizeof(double);
        }

        // For Debug
        nType = SQLT_STR;
        lSize = 30;
        break;

    case OCI_TYPECODE_REAL: 
    case OCI_TYPECODE_DOUBLE: 
    case OCI_TYPECODE_FLOAT:
        nType = SQLT_FLT;
        lSize = sizeof(double);

        // For Debug
        nType = SQLT_STR;
        lSize = 30;
        break;
    
    case OCI_TYPECODE_DECIMAL: 
    case OCI_TYPECODE_OCTET: 
    case OCI_TYPECODE_INTEGER: 
    case OCI_TYPECODE_SMALLINT: 
        nType = SQLT_INT;
        lSize = sizeof(long);

        // For Debug
        nType = SQLT_STR;
        lSize = 30;
        break;

    case OCI_TYPECODE_RAW: 
        bRc = false;
        break;

    case OCI_TYPECODE_VARCHAR2:   
    case OCI_TYPECODE_VARCHAR: 
    case OCI_TYPECODE_CHAR:     
        nType = SQLT_STR;
        lSize = lSize + 1;
        break;

    case OCI_TYPECODE_VARRAY:
    case OCI_TYPECODE_TABLE: 
    case OCI_TYPECODE_CLOB: 
    case OCI_TYPECODE_BLOB: 
    case OCI_TYPECODE_BFILE: 
    case OCI_TYPECODE_OBJECT: 
    case OCI_TYPECODE_NAMEDCOLLECTION:
    default:
        bRc = false;
        break;
    }

    return bRc;
}

/*M***************************************************************************
Method Name  : TinyOci::DescribeDefine
            
Return       :                
Process      :
Note         : IndicatorArray is allocated here !
Author       : 
Date         :
Revision     :
***************************************************************************M*/
bool TinyOci::DescribeDefine( void )
{
    ub4       i = 0;
    ub4       nNameLen = 0;
    char     *pszName= 0;
    sb4       lSize  = 0;
    ub2       nType  = 0;
    ub2       nScale = 0;
    ub2       nPrecision = 0;
    ub4       nPara  = 0;
    OCIParam  *pOCIParam = 0; 
    TinyItem  *pTinyItem = 0;
    OCIDefine *phDefine  = 0;    

    if( !IsSelect() )
        return false;

    // get the number of columns in the select list 
    m_Status = OCIAttrGet ( (dvoid *)m_phStmtHandle, 
                            (ub4)OCI_HTYPE_STMT, 
                            (dvoid *)&nPara, 
                            (ub4 *) 0, 
                            (ub4)OCI_ATTR_PARAM_COUNT, 
                            m_phErrorHandle ); 
    if( OCI_SUCCESS != m_Status )
        return false;
    
    // Clean pre-define list
    CleanDescDefineList();

    // get describe information for each column 
    for(i = 0; i<nPara; i++) 
    {
        // 必须初始化这些变量，
        // 否则OCIAttrGet可能给出不正确结果 !
        nNameLen   = 0;
        pszName    = 0;
        lSize      = 0;
        nType      = 0;
        nScale     = 0;
        nPrecision = 0;

        OCIParamGet( (dvoid *)m_phStmtHandle, OCI_HTYPE_STMT, m_phErrorHandle, (void**)&pOCIParam, i+1 ); 
        m_Status = OCIAttrGet( (dvoid*)pOCIParam, 
                               (ub4) OCI_DTYPE_PARAM, 
                               (dvoid*) &nType,
                               (ub4 *) 0, 
                               (ub4) OCI_ATTR_DATA_TYPE, 
                               (OCIError *)m_phErrorHandle );
        if( OCI_SUCCESS != m_Status )
            return false;

        // Should I FREE the returned pszName ?!!!
        // NO, IT IS OCI TO DO !
        m_Status = OCIAttrGet( (dvoid*)pOCIParam, 
                               (ub4) OCI_DTYPE_PARAM, 
                               (dvoid**)&pszName,
                               (ub4 *)&nNameLen, 
                               (ub4) OCI_ATTR_NAME, 
                               (OCIError *)m_phErrorHandle );
        if( OCI_SUCCESS != m_Status )
            return false;

        m_Status = OCIAttrGet( (dvoid*)pOCIParam, 
                               (ub4) OCI_DTYPE_PARAM, 
                               (dvoid*)&lSize,
                               (ub4 *)0, 
                               (ub4) OCI_ATTR_DATA_SIZE, 
                               (OCIError *)m_phErrorHandle );
        if( OCI_SUCCESS != m_Status )
            return false;

        m_Status = OCIAttrGet( (dvoid*)pOCIParam, 
                               (ub4) OCI_DTYPE_PARAM, 
                               (dvoid*)&nPrecision,
                               (ub4 *)0, 
                               (ub4) OCI_ATTR_PRECISION, 
                               (OCIError *)m_phErrorHandle );
        if( OCI_SUCCESS != m_Status )
            return false;

        m_Status = OCIAttrGet( (dvoid*)pOCIParam, 
                               (ub4) OCI_DTYPE_PARAM, 
                               (dvoid*)&nScale,
                               (ub4 *)0, 
                               (ub4) OCI_ATTR_SCALE, 
                               (OCIError *)m_phErrorHandle );
        if( OCI_SUCCESS != m_Status )
            return false;
        
        // OCI TypeCode to SQLT
        if( !TranslateSizeType( lSize, nType, nScale, nPrecision ) )
            return false;

        // Setup Define Item                        
        pTinyItem = new TinyItem;
        pTinyItem->IndicatorArray = new sb2[m_lMaxFetch];                
        pTinyItem->lSize          = lSize;
        pTinyItem->nType          = nType;
        pTinyItem->pValueArray    = (void*)new char[lSize*m_lMaxFetch];

        memset( pTinyItem->pValueArray, 0, m_lMaxFetch * pTinyItem->lSize );
        memset( pTinyItem->IndicatorArray, 0, m_lMaxFetch * sizeof( sb2 ) );
        
        if( nNameLen >= TINYOCI_MAX_NAME_LEN )
            nNameLen = TINYOCI_MAX_NAME_LEN-1;
        strncpy( pTinyItem->szName, pszName, nNameLen );
        pTinyItem->szName[nNameLen] = 0;

        // Push into our define list
        DescDefineNameList.Append( pszName, pTinyItem );        

        // Start defining
        m_Status = OCIDefineByPos( m_phStmtHandle, 
                                   (OCIDefine**)&phDefine,
                                   m_phErrorHandle, 
                                   i+1,
                                   pTinyItem->pValueArray, 
                                   pTinyItem->lSize, 
                                   pTinyItem->nType,
                                   pTinyItem->IndicatorArray, 
                                   (ub2 *) 0, 
                                   (ub2 *) 0, 
                                   OCI_DEFAULT );//OCI_DYNAMIC_FETCH
        if( OCI_SUCCESS != m_Status )
            return false;

        if( m_lMaxFetch > 1 )
        {
            m_Status = OCIDefineArrayOfStruct(  phDefine,
                                                m_phErrorHandle,
                                                (ub4)pTinyItem->lSize,
                                                (ub4)sizeof(sb2),
                                                (ub4)0,
                                                (ub4)0
                                                );
            if( OCI_SUCCESS != m_Status )
                return false;
        }

    }// End for loop

    return true;
}

/*M***************************************************************************
Method Name  : TinyOci::Prepare
            
Return       :                
Process      :
Note         : 
Author       : 
Date         :
Revision     :
***************************************************************************M*/
bool TinyOci::Prepare( const char *pszSql )
{
    ub4	  sizep;

    m_Status = OCIStmtPrepare( m_phStmtHandle,
                               m_phErrorHandle,
                               (OraText*)pszSql,
                               (ub4)strlen((char*)pszSql),
                               (ub4)OCI_NTV_SYNTAX,
                               (ub4)OCI_DEFAULT );
    if( OCI_SUCCESS != m_Status )
        return false;

    // Get statement type
    m_Status = OCIAttrGet( (dvoid *)m_phStmtHandle, 
                           (ub4) OCI_HTYPE_STMT, 
                           (dvoid *) &m_StmtType, 
                           (ub4*) &sizep, 
                           (ub4) OCI_ATTR_STMT_TYPE , 
                           m_phErrorHandle );
    if( OCI_SUCCESS != m_Status )
        return false;

    if( ( m_lPrefetch > 0 ) && IsSelect() )
    {
        m_Status = OCIAttrSet( m_phStmtHandle,
                               OCI_HTYPE_STMT,
                               &m_lPrefetch,
                               0,
                               OCI_ATTR_PREFETCH_ROWS,
                               m_phErrorHandle );
        if( OCI_SUCCESS != m_Status )
            return false;
    }

    return true;
}

/*M***************************************************************************
Method Name  : TinyOci::Execute
            
Return       :                
Process      :
Note         : 1. Only BLOCK mode is supported.
Author       : 
Date         :
Revision     :
***************************************************************************M*/
bool TinyOci::Execute( const char *pszSql, int nIter )
{
    // Keep it
    m_nIter = nIter;

    // 调用OCIStmtPrepare准备该SQL语句
    if( !Prepare( pszSql ) )    
        return false;    
    
    // 检查BindNameList是否为空，如果非空，则根据名称绑定所有输入变量
    if( !Bind() )    
        return false;    

    // 当执行一条SELECT语句时，nIter参数被忽略，且重复执行的次数设置为0。
    if( IsSelect() )    
        nIter = 0;    

    // Reset counters
    m_lRowCountSoFar= 0;
    m_lRowFetched   = 0;     

    // 调用OCIStmtExecute执行该SQL语句。
    m_Status = OCIStmtExecute( m_phOCISvcCtx,
                               m_phStmtHandle,
                               m_phErrorHandle,
                               nIter,
                               (ub4)0,
                               (OCISnapshot*)0,
                               (OCISnapshot*)0,
                               (ub4)OCI_DEFAULT );
    if( OCI_SUCCESS != m_Status )
        return false;   

    // 如果是OCI_STMT_SELECT，则：
    if( IsSelect() )
    {
        // 检查DefineNameList是否为空，如果非空，则根据名称绑定所有输出变量。
        if( DefineNameList.GetCount() > 0 )
        {
            if( !Define() )
                return false;
        }
        // 如果DefineNameList为空，则:Desribe Select-List and Define
        else
        {
            if( !DescribeDefine( ) )
                return false;
        }
    }

    return true;
}

/*M***************************************************************************
Method Name  : TinyOci::CleanDefineBuffer
            
Return       :                               
Process      :
Note         : Called by short TinyOci::Fetch( long &lRows ) ONLY !
   
Author       :