TnbTinySqlAccessor.h

[詳解]

#pragma once
#include <TnbStrOperator.h>
#include <TnbHandleHandle.h>
#include <TnbPointerVector.h>
 
 
 
#ifndef _WIN32_WCE
 #include <sql.h>
 #include <sqltypes.h>
 #include <sqlucode.h>
 #pragma comment(lib, "odbc32.lib")
#else
 #include "minodbc.h" // 要 Mimer SDK。
 #pragma comment(lib, "mimodbcp.lib")
 #pragma comment(lib, "minodbcw.lib")
 #pragma comment(lib, "minsql32.lib")
#endif
 
 
 
namespace TNB
{
 
 
 
#ifndef _TnbDOXYGEN //Document作成用シンボル
 
struct TPhFreeStmtHandle
{
    void operator()(SQLHSTMT h) 
    {
        __try 
        {
            ::SQLFreeHandle(SQL_HANDLE_STMT, h); 
        }
        __except( EXCEPTION_EXECUTE_HANDLER ) 
        {
            ASSERT0( false, "", "先に CTinySqlAccessor が Disconnect しているか Stop しているか破棄されています。" );
        }
    }
};
 
#endif //_TnbDOXYGEN
 
 
 
class CTinySqlAccessor : CCopyImpossible
{
public:
 
    enum EType
    {
        Type_Null           = SQL_TYPE_NULL,        
        Type_Binary         = SQL_BINARY,           
        Type_AsciiString    = SQL_CHAR,             
        Type_UnicodeString  = SQL_WCHAR,            
        Type_DoubleFloat    = SQL_DOUBLE,           
        Type_Float          = SQL_FLOAT,            
        Type_Integer        = SQL_INTEGER,          
        Type_Short          = SQL_SMALLINT,         
        Type_TimeStamp      = SQL_TYPE_TIMESTAMP,   
        Type_Bit            = SQL_BIT               
    };
 
    class CReturnCode
    {
    public:
 
        struct TCodes
        {
            CStr        sqlState;       
            SQLINTEGER  nativeError;    
            CStr        errorMessage;   
            SQLINTEGER  plm_Rownumber;  
            TCodes(void) : nativeError(0), plm_Rownumber(0)
            {
            }
        };
 
        typedef CVectorT<TCodes> CCodeVector;
 
        CReturnCode(void) : m_type(0), m_handle(0), m_returnCode(SQL_SUCCESS)
        {
        }
 
        CReturnCode(SQLSMALLINT type, SQLHANDLE handle, SQLRETURN rc, bool isSilent = false) : m_type(type), m_handle(handle), m_returnCode(rc)
        {
            if ( ! isSilent )
            {
                Dump();
            }
        }
 
        SQLRETURN GetReturnCode(void) const
        {
            return m_returnCode;
        }
 
        operator bool(void) const
        {
            return ! HasError();
        }
 
        bool HasError(void) const
        {
            return (m_returnCode != SQL_SUCCESS && m_returnCode != SQL_SUCCESS_WITH_INFO);
        }
 
        CStr GetErrorString(void) const
        {
            CStr s;
            switch ( m_returnCode )
            {
            case SQL_SUCCESS:
            case SQL_SUCCESS_WITH_INFO:
                break;
            case SQL_NO_DATA:
                s = _T("No Data");
                break;
            case SQL_ERROR:
                s = _T("Error");
                break;
            case SQL_INVALID_HANDLE:
                s = _T("Invalid Handle");
                break;
            case SQL_NEED_DATA:
                s = _T("Need Data");
                break;
            default:
                s = _T("Unknown Error");
                break;
            }
            return s;
        }
 
        CCodeVector GetDetialCode(bool withDiagnostics = false) const
        {
            CCodeVector codes;
            SQLRETURN rc = SQL_SUCCESS;
            SQLSMALLINT record = 1;
            while ( rc == SQL_SUCCESS )
            {
                TCodes c;
                SQLSMALLINT emLen = 0;
                rc = ::SQLGetDiagRec(m_type, m_handle, record, GetSqlChar(c.sqlState, MAX_PATH), &c.nativeError
                                                        , GetSqlChar(c.errorMessage, MAX_PATH), MAX_PATH, &emLen);
                c.sqlState.ReleaseBuffer();
                c.errorMessage.ReleaseBuffer();
                if ( rc == SQL_SUCCESS )
                {
                    if ( withDiagnostics )
                    {
                        m_GetDiagField(record, SQL_DIAG_ROW_NUMBER, &c.plm_Rownumber, SQL_IS_INTEGER);
                    }
                    codes.Add(c);
                }
                record++;
            }
            return codes;
        }
 
        static bool IsReturnCodeSuccess(SQLRETURN r)
        {
            switch ( r )
            {
            case SQL_SUCCESS:
            case SQL_SUCCESS_WITH_INFO:
                return true;
            default:
                break;
            }
            return false;
        }
 
        void Dump(void)
        {
            #ifdef _TnbDEBUG_ON
            if ( m_returnCode != SQL_SUCCESS )
            {
                TTRACE2("*SqlReturn %d[%s]\n", m_returnCode, GetErrorString());
                CVectorT<TCodes> v = GetDetialCode(true);
                loop ( i, v )
                {
                    const TCodes& t = v[i];
                    TTRACE2("No%d; state[%s]", i + 1, t.sqlState);
                    TTRACE3(", e=%d<%s>, %d\n", t.nativeError, t.errorMessage, t.plm_Rownumber);
                }
            }
            #endif
        }
 
    private:
        SQLRETURN m_GetDiagField(SQLSMALLINT iRecord, SQLSMALLINT fDiagField, SQLPOINTER rgbDiagInfo, SQLSMALLINT cbDiagInfoMax, SQLSMALLINT* pcbDiagInfo = NULL) const
        {
            return ::SQLGetDiagField(m_type, m_handle, iRecord, fDiagField, rgbDiagInfo, cbDiagInfoMax, pcbDiagInfo);
        }
        SQLSMALLINT     m_type;         
        SQLHANDLE       m_handle;       
        SQLRETURN       m_returnCode;   
    };
 
 
    //-----------------------------
 
 
    typedef TNB::CPointerHandleBaseT<SQLHSTMT, TPhFreeStmtHandle> CStmtHandle;
 
 
    //-----------------------------
 
 
    class CColumnIndex
    {
    public:
 
        CColumnIndex(INDEX number) : m_number(number)
        {
        }
 
        CColumnIndex(LPCTSTR lpszLabel) : m_label(lpszLabel), m_number(0)
        {
        }
 
        bool HasLabel(void) const
        {
            return ! m_label.IsEmpty();
        }
 
        LPCTSTR GetLabel(void) const
        {
            return m_label;
        }
 
        INDEX GetNumber(void) const
        {
            return m_number;
        }
 
    private:
        INDEX   m_number;   
        CStr    m_label;    
    };
 
 
    //-----------------------------
 
 
    class CResultSet
    {
    public:
 
        struct TColumnInformation
        {
            CStr        name;               
            SQLSMALLINT dataType;           
            SQLULEN     size;               
            SQLSMALLINT decimalDigits;      
            bool        isNullable;         
        };
 
        CResultSet(CStmtHandle hStmt) : m_hStmt(hStmt), m_isBinding(false), m_isLastNull(false)
        {
        }
 
        CResultSet(const CResultSet& other) : m_hStmt(other.m_hStmt), m_isBinding(false), m_isLastNull(false)
        {
        }
 
        CResultSet& operator=(const CResultSet& other)
        {
            m_hStmt = other.m_hStmt;
            m_isBinding = false;
            m_isLastNull = false;
            return *this;
        }
 
        bool IsValid(void) const
        {
            return ! m_hStmt.IsNull();
        }
 
        void Destroy(void)
        {
            m_hStmt = NULL;
        }
 
        bool Next(void)
        {
            if ( ! Binding() )
            {
                return false;
            }
            if ( ! m_Rst(::SQLFetch(m_hStmt)) )
            {
                return false;
            }
            loop ( i, m_bindWorks.GetSize() )
            {
                TBindWork &b = m_bindWorks[i];
                SQLLEN ll;
                if ( b.dataType == SQL_VARBINARY )
                {
                    ll = b.size;
                    b.work.Resize(ll);
                    if ( ! m_Rst(::SQLGetData(m_hStmt, down_cast<SQLUSMALLINT>(i + 1), Type_Binary, b.work.Ref(), ll, &b.length)) )
                    {
                        return false;
                    }
                    b.dataType = Type_Binary;
                }
                else
                {
                    if ( b.size == 0x7FFFFFFF )
                    {
                        BYTE tmp;
                        RETCODE rc = ::SQLGetData(m_hStmt, down_cast<SQLUSMALLINT>(i + 1), SQL_C_CHAR, &tmp, 1, &ll);
                        if ( rc < 0 )
                        {
                            return m_Rst(rc);
                        }
                    }
                    else
                    {
                        ll = b.size;
                    }
                    ll = ll * 2 + 2;
                    b.work.Resize(ll);
                    SQLSMALLINT cc = (sizeof(TCHAR) == 1) ? SQL_C_CHAR : SQL_WCHAR;
                    if ( ! m_Rst(::SQLGetData(m_hStmt, down_cast<SQLUSMALLINT>(i + 1), b.dataType, b.work.Ref(), ll, &b.length), true) )
                    {
                        if ( ! m_Rst(::SQLGetData(m_hStmt, down_cast<SQLUSMALLINT>(i + 1), cc, b.work.Ref(), ll, &b.length)) )
                        {
                            return false;
                        }
                        b.dataType = cc;
                    }
                }
            }
            return true;
        }
 
        bool WasNull(void) const
        {
            return m_isLastNull;
        }
 
        CStr GetString(CColumnIndex index)
        {
            CStr r;
            m_Check(index).Get(r);
            return r;
        }
 
        CUnicode GetUnicodeString(CColumnIndex index)
        {
            CUnicode r;
            m_Check(index).Get(r);
            return r;
        }
 
        CAscii GetAsciiString(CColumnIndex index)
        {
            CAscii r;
            m_Check(index).Get(r);
            return r;
        }
 
        double GetDouble(CColumnIndex index)
        {
            double r = 0.0;
            m_Check(index).Get(r);
            return r;
        }
 
        float GetFloat(CColumnIndex index)
        {
            float r = 0.0;
            m_Check(index).Get(r);
            return r;
        }
 
        int GetInt(CColumnIndex index)
        {
            int r = 0;
            m_Check(index).Get(r);
            return r;
        }
 
        void GetTime(SYSTEMTIME& _v, CColumnIndex index)
        {
            Zero(_v);
            m_Check(index).Get(_v);
        }
 
        void GetBinary(CWorkMem& _v, CColumnIndex index)
        {
            _v.Resize(0);
            TBindWork& B = m_Check(index);
            _v.Reset(B.length, B.work.Ref());
        }
 
        const TColumnInformation& GetColumnInformation(CColumnIndex index)
        {
            Binding();
            return m_bindWorks[m_Find(index)];
        }
 
        INDEX FindColumn(LPCTSTR lpszLabel)
        {
            Binding();
            loop ( i, m_bindWorks.GetSize() )
            {
                if ( m_bindWorks[i].name.CompareNoCase(lpszLabel) == 0 )
                {
                    return i + 1;
                }
            }
            return 0;
        }
 
        size_t GetColumnCount(void) const
        {
            return m_bindWorks.GetSize();
        }
 
        CReturnCode GetLastReturnCode(void) const
        {
            return m_lastReturnCode;
        }
 
    protected:
        bool Binding(void)
        {
            if ( m_isBinding )
            {
                return m_Rst();
            }
            //== カラム数取得
            SQLSMALLINT columnCount;
            SQLRETURN r = ::SQLNumResultCols(m_hStmt, &columnCount);
            if ( ! m_Rst(r, true) )
            {
                return false;
            }
            //== カラム情報取得
            m_bindWorks.Resize(columnCount);
            loop ( i, columnCount )
            {
                SQLSMALLINT nameLen;
                TBindWork& b = m_bindWorks[i];
                SQLSMALLINT nullable;
                r = ::SQLDescribeCol(m_hStmt,                   // [in] ステートメント
                                down_cast<SQLUSMALLINT>(i + 1), // [in] カラム番号(１〜)
                                GetSqlChar(b.name, 100),        // [out] カラム名
                                100,                            // [in] カラム名領域
                                &nameLen,                       // [out] カラム名長
                                &b.dataType,                    // [out] 型
                                &b.size,                        // [out] 型サイズ
                                &b.decimalDigits,               // [out] 小数点情報
                                &nullable);                     // [out] NULLが有効か
                b.isNullable = (nullable != 0);
                b.name.ReleaseBuffer();
                if ( ! m_Rst(r) )
                {
                    return false;
                }
                TTRACE3("Col %d-[%s], type=%d, ", i + 1, b.name, b.dataType);
                TTRACE3("size=%d, dec=%d, nullable=%d\n", b.size, b.decimalDigits, b.isNullable);
            }
            m_isBinding = true;
            return true;
        }
 
    private:
        struct TBindWork : TColumnInformation
        {
            CWorkMem            work;       
            SQLLEN              length;     
            bool Get(CUnicode& _value) const
            {
                if ( length >= 0 )
                {
                    CStr s;
                    if ( m_Get(s) )
                    {
                        _value = s;
                        return true;
                    }
                    throw CNotSupportException();
                }
                return false;
            }
            bool Get(CAscii& _value) const
            {
                if ( length >= 0 )
                {
                    CStr s;
                    if ( m_Get(s) )
                    {
                        _value = s;
                        return true;
                    }
                    throw CNotSupportException();
                }
                return false;
            }
            bool Get(double& _value) const
            {
                if ( length >= 0 )
                {
                    if ( dataType == Type_DoubleFloat )
                    {
                        if ( length < sizeof(double) )
                        {
                            throw CNotSupportException();
                        }
                        _value = *reinterpret_cast<const double*>(work.Ref());
                        return true;
                    }
                    else if ( dataType == Type_Float )
                    {
                        if ( length < sizeof(float) )
                        {
                            throw CNotSupportException();
                        }
                        _value = *reinterpret_cast<const float*>(work.Ref());
                        return true;
                    }
                    else
                    {
                        CStr s;
                        if ( m_Get(s) )
                        {
                            _value = s.ToDouble();
                            return true;
                        }
                    }
                    throw CNotSupportException();
                }
                return false;;
            }
            bool Get(float& _value) const
            {
                double d;
                if ( Get(d) )
                {
                    _value = static_cast<float>(d);
                    return true;
                }
                return false;;
            }
            bool Get(int& _value) const
            {
                if ( length >= 0 )
                {
                    if ( dataType == Type_Integer )
                    {
                        if ( length < sizeof(int) )
                        {
                            throw CNotSupportException();
                        }
                        _value = *reinterpret_cast<const int*>(work.Ref());
                        return true;
                    }
                    else if ( dataType == Type_Short )
                    {
                        if ( length < sizeof(short) )
                        {
                            throw CNotSupportException();
                        }
                        _value = *reinterpret_cast<const short*>(work.Ref());
                        return true;
                    }
                    else if ( dataType == Type_Bit )
                    {
                        if ( length < sizeof(BYTE) )
                        {
                            throw CNotSupportException();
                        }
                        _value = work[0];
                        return true;
                    }
                    else
                    {
                        CStr s;
                        if ( m_Get(s) )
                        {
                            _value = s.ToInt();
                            return true;
                        }
                    }
                    throw CNotSupportException();
                }
                return false;;
            }
            bool Get(SYSTEMTIME& _time) const
            {
                if ( length >= 0 )
                {
                    if ( dataType == Type_TimeStamp )
                    {
                        if ( length < sizeof(TIMESTAMP_STRUCT) )
                        {
                            throw CNotSupportException();
                        }
                        const TIMESTAMP_STRUCT* T = reinterpret_cast<const TIMESTAMP_STRUCT*>(work.Ref());
                        Zero(_time);
                        _time.wYear = T->year;
                        _time.wMonth = T->month;
                        _time.wDay = T->day;
                        _time.wHour = T->hour;
                        _time.wMinute = T->minute;
                        _time.wSecond = T->second;
                        return true;
                    }
                    throw CNotSupportException();
                }
                return false;;
            }
            CStr ToString(void) const
            {
                CStr s;
                try
                {
                    if ( ! Get(s) )
                    {
                        s = _T("NULL");
                    }
                }
                catch ( CNotSupportException& e )
                {
                    e.OnCatch();
                    s = _T("Unknown");
                }
                return s;
            }
        private:
            bool m_Get(CStr& _value) const
            {
                if ( length >= 0 )
                {
                    if ( dataType == Type_TimeStamp )
                    {
                        CSystemTime st;
                        Get(st);
                        _value = SystemTimeToString(st);
                        return true;
                    }
                    else if ( dataType == Type_Integer || dataType == Type_Short )
                    {
                        int v;
                        Get(v);
                        _value.Format(_T("%d"), v);
                        return true;
                    }
                    else if ( dataType == Type_DoubleFloat || dataType == Type_Float )
                    {
                        double v;
                        Get(v);
                        _value.Format(_T("%.2f"), v);
                    }
                    else if ( dataType == Type_Binary )
                    {
                        _value = CStrOperator::BinaryToHexString(length, work.Ref());
                        return true;
                    }
                    else if ( dataType == Type_Bit )
                    {
                        _value.Format(_T("%u"), work[0]);
                        return true;
                    }
                    else if ( dataType == Type_AsciiString )
                    {
                        CAscii a;
                        a.SetFromLeft(reinterpret_cast<LPCSTR>(work.Ref()), length);
                        _value = a;
                    }
                    else if ( dataType == Type_UnicodeString )
                    {
                        CUnicode u;
                        u.SetFromLeft(reinterpret_cast<LPCWSTR>(work.Ref()), length);
                        _value = u;
                    }
                    else
                    {
                        _value.SetFromLeft(reinterpret_cast<LPCTSTR>(work.Ref()), length);
                    }
                    return true;
                }
                return false;
            }
        };
 
        INDEX m_Find(const CColumnIndex& index) const
        {
            if ( index.HasLabel() )
            {
                //== ラベル指定
                loop ( i, m_bindWorks.GetSize() )
                {
                    if ( m_bindWorks[i].name.CompareNoCase(index.GetLabel()) == 0 )
                    {
                        return i;
                    }
                }
                throw CNotFoundException();
            }
            //== 番号指定
            INDEX i = index.GetNumber();
            if ( m_bindWorks.GetSize() > i - 1 )
            {
                return i - 1;
            }
            throw CIndexOutOfBoundsException();
        }
        TBindWork& m_Check(const CColumnIndex& index)
        {
            INDEX i = m_Find(index);
            TBindWork& B = m_bindWorks[i];
            m_isLastNull = (B.length < 0); //マイナスは NULL ってこと
            return B;
        }
        // 戻り値処理
        bool m_Rst(SQLRETURN rc = 0, bool isSilent = false)
        {
            m_lastReturnCode = CReturnCode(SQL_HANDLE_STMT, m_hStmt, rc, isSilent);
            return CReturnCode::IsReturnCodeSuccess(rc);
        }
        bool                            m_isBinding;        
        CStmtHandle                     m_hStmt;            
        CWorkMemT<TBindWork>            m_bindWorks;        
        bool                            m_isLastNull;       
        CReturnCode                     m_lastReturnCode;   
        friend class CResultSetTest;
    };
 
 
    //-------------------------------
 
 
    class CStatement
    {
    public:
 
        CStatement(SQLHDBC hDbc, DWORD tm = 0) : m_hDbc(hDbc), m_timeout(tm)
        {
        }
 
        CStatement(const CStatement& other) 
            : m_hDbc(other.m_hDbc), m_timeout(other.m_timeout), m_hStmt(other.m_hStmt), m_lastReturnCode(other.m_lastReturnCode)
        {
        }
 
        CStatement& operator=(const CStatement& other)
        {
            m_hDbc = other.m_hDbc;
            m_timeout = other.m_timeout;
            m_hStmt = other.m_hStmt;
            m_lastReturnCode = other.m_lastReturnCode;
            return *this;
        }
 
        bool IsValid(void)
        {
            m_Init();
            return ! m_hStmt.IsNull();
        }
 
        void Destroy(void)
        {
            m_hStmt = NULL;
        }
 
        bool Execute(LPCTSTR lpszSql)
        {
            m_Init();
            //== SQL文実行
            CStr sql = lpszSql;
            SQLRETURN r = ::SQLExecDirect(m_hStmt, GetSqlChar(sql), SQL_NTS);
            return m_Rst(r);
        }
 
        CResultSet GetResultSet(void) const
        {
            return CResultSet(m_hStmt);
        }
 
        CReturnCode GetLastReturnCode(void) const
        {
            return m_lastReturnCode;
        }
 
        DWORD GetQueryTimeout(void)
        {
            SQLULEN tm;
            SQLRETURN r = ::SQLGetStmtAttr(m_hStmt, SQL_ATTR_QUERY_TIMEOUT, static_cast<SQLPOINTER>(&tm), SQL_IS_UINTEGER, NULL);
            if ( m_Rst(r) )
            {
                return static_cast<DWORD>(tm);
            }
            return DWORD_MAX;
        }
 
        virtual bool CloseCursor(void) 
        {
            return m_Rst(::SQLCloseCursor(m_hStmt));
        }
 
    protected:
 
        bool m_Init(void)
        {
            if ( m_hStmt.IsNull() )
            {
                SQLHSTMT hStmt = NULL;
                SQLRETURN r = ::SQLAllocHandle(SQL_HANDLE_STMT, m_hDbc, &hStmt);
                m_lastReturnCode = CReturnCode(SQL_HANDLE_DBC, m_hDbc, r);
                if ( ! m_lastReturnCode )
                {
                    return false;
                }
                m_hStmt = hStmt;
                SQLULEN tm = m_timeout;
                ::SQLSetStmtAttr(m_hStmt, SQL_ATTR_QUERY_TIMEOUT, reinterpret_cast<SQLPOINTER>(tm), 0);
                ASSERT( GetQueryTimeout() == m_timeout );
            }
            return true;
        }
        bool m_Rst(SQLRETURN rc = 0)
        {
            m_lastReturnCode = CReturnCode(SQL_HANDLE_STMT, m_hStmt, rc);
            return CReturnCode::IsReturnCodeSuccess(rc);
        }
        SQLHDBC         m_hDbc;             
        CStmtHandle     m_hStmt;            
        DWORD           m_timeout;          
        CReturnCode     m_lastReturnCode;   
    };
 
 
    //---------------------------
 
 
    class CPreparedStatement : public CStatement
    {
        DEFSUPER(CStatement);
    public:
 
        CPreparedStatement(SQLHDBC hDbc, LPCTSTR lpszSql, DWORD tm = 0) : _super(hDbc, tm), m_sql(lpszSql), m_isPrepared(false)
        {
        }
 
        CPreparedStatement(const CPreparedStatement& other) : _super(other), m_sql(other.m_sql), m_isPrepared(false)
        {
        }
 
        CPreparedStatement& operator=(const CPreparedStatement& other)
        {
            _super::operator=(other);
            m_sql = other.m_sql;            // SQL 文
            m_isPrepared = false;
            m_bindParams.RemoveAll();   // バインドワーク
            return *this;
        }
 
        bool ExecuteDirect(LPCTSTR lpszSql)
        {
            return _super::Execute(lpszSql);
        }
 
        bool Execute(void)
        {
            if ( ! m_Prepare() )
            {
                return false;
            }
            SQLRETURN r = ::SQLExecute(m_hStmt);
            return m_Rst(r);
        }
 
        bool SetNull(INDEX parameterIndex)
        {
            return m_BindParam(parameterIndex, SQL_C_NUMERIC, SQL_NUMERIC, SQL_NULL_DATA, NULL);
        }
 
        bool SetInt(INDEX parameterIndex, int value)
        {
            return m_BindParam(parameterIndex, SQL_C_SLONG, SQL_INTEGER, sizeof(value), &value);
        }
 
        bool SetDouble(INDEX parameterIndex, double value)
        {
            return m_BindParam(parameterIndex, SQL_C_DOUBLE, SQL_DOUBLE, sizeof(value), &value);
        }
 
        bool SetBinary(INDEX parameterIndex, size_t size, LPCVOID P)
        {
            SQLLEN l = down_cast<SQLLEN>(size);
            if ( l == 0 )
            {
                return SetNull(parameterIndex);
            }
            return m_BindParam(parameterIndex, SQL_C_BINARY, SQL_BINARY, l, P);
        }
 
        bool SetString(INDEX parameterIndex, LPCSTR lpsz)
        {
            SQLLEN l = down_cast<SQLLEN>(STRLIB::GetLen(lpsz));
            if ( l == 0 )
            {
                return SetNull(parameterIndex);
            }
            return m_BindParam(parameterIndex, SQL_C_CHAR, SQL_CHAR, l, lpsz);
        }
 
        bool SetString(INDEX parameterIndex, LPCWSTR lpsz)
        {
            SQLLEN l = down_cast<SQLLEN>(STRLIB::GetLen(lpsz) * 2);
            if( l == 0 )
            {
                return SetNull(parameterIndex);
            }
            return m_BindParam(parameterIndex, SQL_C_WCHAR, SQL_WCHAR, l, lpsz);
        }
 
        bool SetTime(INDEX parameterIndex, const SYSTEMTIME& time)
        {
            TIMESTAMP_STRUCT ts;
            Zero(ts);
            ts.year = time.wYear;
            ts.month = time.wMonth;
            ts.day = time.wDay;
            ts.hour = time.wHour;
            ts.minute = time.wMinute;
            ts.second = time.wSecond;
            return m_BindParam(parameterIndex, SQL_C_TIMESTAMP, SQL_C_TIMESTAMP, sizeof(ts), &ts);
        }
 
//      bool SetDefault(INDEX parameterIndex)
//      {
//          return m_BindParam(parameterIndex, SQL_C_DEFAULT, SQL_BINARY, SQL_DEFAULT_PARAM, NULL);
//      }
 
        virtual bool CloseCursor(void) 
        {
            m_isPrepared = false;
            return _super::CloseCursor();
        }
    private:
        // 使用禁止宣言
        bool Execute(LPCTSTR lpszSql);
        // バインドワーク型
        struct TBindWork
        {
            CWorkMem            work;       
            SQLLEN              length;     
        };
        // プレペア処理
        bool m_Prepare(void)
        {
            m_Init();
            if ( ! m_isPrepared )
            {
                SQLRETURN r = ::SQLPrepare(m_hStmt, GetSqlChar(m_sql), SQL_NTS);
                m_sql.ReleaseBuffer();
                if ( ! m_Rst(r) )
                {
                    return false;
                }
                m_isPrepared = true;
            }
            return true;
        }
        // バインド処理
        bool m_BindParam(INDEX parameterIndex, SQLSMALLINT fCType, SQLSMALLINT fSqlType, SQLLEN len, LPCVOID P)
        {
            if ( ! m_Prepare() )
            {
                return false;
            }
            if ( m_bindParams.GetSize() <= parameterIndex )
            {
                m_bindParams.SetSize(parameterIndex + 1);
            }
            TBindWork* W = m_bindParams.Get(parameterIndex);
            if ( W == NULL )
            {
                W = new TBindWork;
                m_bindParams.Set(parameterIndex, W);
            }
            else if ( W->length == len )
            {
                if ( len > 0 && ToInt(W->work.GetSize()) == len )
                {
                    memcpy(W->work.Ref(), P, len);
                }
                return true;
            }
            W->length = len;
            SQLRETURN r;
            if ( fCType == SQL_C_TIMESTAMP )
            {
                W->work.Reset(len, static_cast<const BYTE*>(P));
                r = ::SQLBindParameter(m_hStmt, down_cast<SQLUSMALLINT>(parameterIndex)
                        , SQL_PARAM_INPUT, fCType, fSqlType, 23/*TIMESTAMP_PRECISION*/, 0, W->work.Ref(), 0, &W->length);
            }
            else if ( len >= 0 )
            {
                W->work.Reset(len, static_cast<const BYTE*>(P));
                r = ::SQLBindParameter(m_hStmt, down_cast<SQLUSMALLINT>(parameterIndex)
                        , SQL_PARAM_INPUT, fCType, fSqlType, len, 0, W->work.Ref(), len, &W->length);
            }
            else
            {
                W->work.Resize(1);
                r = ::SQLBindParameter(m_hStmt, down_cast<SQLUSMALLINT>(parameterIndex)
                        , SQL_PARAM_INPUT, fCType, fSqlType, 13, 6, NULL, 0, &W->length);
            }
            return m_Rst(r);
        }
        CStr                        m_sql;          
        bool                        m_isPrepared;   
        CPointerVectorT<TBindWork>  m_bindParams;   
    };
 
 
    //-------------------------------
 
 
    ~CTinySqlAccessor(void)
    {
        Stop();
    }
 
    CTinySqlAccessor(void) : m_hEnv(NULL), m_hDbc(NULL), m_isConnecting(false), m_timeout(0)
    {
    }
 
    bool IsStarted(void) const
    {
        return m_hEnv != NULL;
    }
 
    bool Stop(void)
    {
        if ( IsStarted() )
        {
            Disconnect();
            // DB接続用ハンドル開放
            SQLRETURN r = ::SQLFreeHandle(SQL_HANDLE_DBC, m_hDbc);
            if ( ! m_Rst(SQL_HANDLE_DBC, m_hDbc, r) )
            {
                return false;
            }
            m_hDbc = NULL;
            // 環境用のハンドル開放
            r = ::SQLFreeHandle(SQL_HANDLE_ENV, m_hEnv);
            if ( ! m_Rst(SQL_HANDLE_ENV, m_hEnv, r) )
            {
                return false;
            }
            m_hEnv = NULL;
        }
        return m_Rst();
    }
 
    bool Start(void)
    {
        Stop();
        //== ODBC環境ハンドル取得
        SQLRETURN r = ::SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_hEnv);
        if ( ! m_CheckReturnCode(r) )
        {
            m_hEnv = NULL;
        }
        else
        {
            //== ODBCバージョン設定
            r = ::SQLSetEnvAttr(m_hEnv, SQL_ATTR_ODBC_VERSION, reinterpret_cast<SQLPOINTER>(SQL_OV_ODBC3), 0);
            if ( m_CheckReturnCode(r) )
            {
                //== ODBCデータベースハンドル取得
                r = ::SQLAllocHandle(SQL_HANDLE_DBC, m_hEnv, &m_hDbc);
            }
            if ( ! m_CheckReturnCode(r) )
            {
                ::SQLFreeHandle(SQL_HANDLE_ENV, m_hEnv);
                m_hEnv = NULL;
            }
        }
        return m_Rst(SQL_HANDLE_ENV, m_hEnv, r);
    }
 
    bool IsConnecting(void) const
    {
        return m_isConnecting;
    }
 
    bool Disconnect(void)
    {
        if ( ! IsConnecting() )
        {
            return m_Rst();
        }
        // コミット
        SQLRETURN r = ::SQLEndTran(SQL_HANDLE_DBC, m_hDbc, SQL_COMMIT);
        // DB ディスコネクト
        r = ::SQLDisconnect(m_hDbc);
        if ( m_Rst(SQL_HANDLE_DBC, m_hDbc, r) )
        {
            m_isConnecting = false;
            return true;
        }
        return false;
    }
 
    bool Connect(LPCTSTR lpszDataBaseName, LPCTSTR lpszUserName, LPCTSTR lpszPassword)
    {
        if ( m_PreConnect() )
        {
            //== データベース接続
            CStr dn = lpszDataBaseName;
            CStr un = lpszUserName;
            CStr pw = lpszPassword;
            SQLRETURN r = ::SQLConnect(m_hDbc, GetSqlChar(dn), SQL_NTS, GetSqlChar(un), SQL_NTS, GetSqlChar(pw), SQL_NTS);
            if ( m_Rst(SQL_HANDLE_DBC, m_hDbc, r) )
            {
                m_isConnecting = true;
                return true;
            }
        }
        return false;
    }
 
    bool Connect(LPCTSTR lpszConnect, SQLUSMALLINT options = SQL_DRIVER_NOPROMPT)
    {
        if ( m_PreConnect() )
        {
            CStr cs = lpszConnect;
            //== データベース接続
            SQLHWND hWnd = ::GetDesktopWindow();
            CStr n;
            SQLSMALLINT length = 0;
            SQLRETURN r = ::SQLDriverConnect(m_hDbc, hWnd, GetSqlChar(cs), SQL_NTS, GetSqlChar(n, 256), 256, &length, options);
            if ( m_Rst(SQL_HANDLE_DBC, m_hDbc, r) )
            {
                m_isConnecting = true;
                return true;
            }
        }
        return false;
    }
 
    bool Connect(LPCTSTR lpszDriverName, LPCTSTR lpszDataBaseName, LPCTSTR lpszUserName, LPCTSTR lpszPassword, SQLUSMALLINT options = SQL_DRIVER_NOPROMPT)
    {
        //== 名前作成
        CStr cs;
        cs.Format(_T("DRIVER={%s};Database=%s;UID=%s;PWD=%s;"), lpszDriverName, lpszDataBaseName, lpszUserName, lpszPassword);
        return Connect(cs, options);
    }
 
    void SetQueryTimeout(DWORD tm)
    {
        m_timeout = tm;
    }
 
    CStatement CreateStatement(void)
    {
        return CStatement(m_hDbc, m_timeout);
    }
 
    CPreparedStatement PreparedStatement(LPCTSTR lpszSql)
    {
        return CPreparedStatement(m_hDbc, lpszSql, m_timeout);
    }
 
    bool IsAutoCommit(void)
    {
        DWORD l = DWORD_MAX;
        SQLINTEGER len = 0;
        SQLRETURN r = ::SQLGetConnectAttr(m_hDbc, SQL_AUTOCOMMIT, &l, sizeof(l), &len);
        if ( m_Rst(SQL_HANDLE_DBC, m_hDbc, r) )
        {
            return (l == SQL_AUTOCOMMIT_ON);
        }
        return false;
    }
 
    bool SetAutoCommit(bool isEnable)
    {
        DWORD l = isEnable ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF;
        SQLRETURN r = ::SQLSetConnectAttr(m_hDbc, SQL_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(static_cast<SQLLEN>(l)), 0);
        return m_Rst(SQL_HANDLE_DBC, m_hDbc, r);
    }
    
    bool Commit(void)
    {
        SQLRETURN r = ::SQLEndTran(SQL_HANDLE_DBC, m_hDbc, SQL_COMMIT);
        return m_Rst(SQL_HANDLE_DBC, m_hDbc, r);
    }
 
    bool Rollback(void)
    {
        SQLRETURN r = ::SQLEndTran(SQL_HANDLE_DBC, m_hDbc, SQL_ROLLBACK);
        return m_Rst(SQL_HANDLE_DBC, m_hDbc, r);
    }
 
    CReturnCode GetLastReturnCode(void) const
    {
        return m_lastReturnCode;
    }
 
    static CStr SystemTimeToString(const SYSTEMTIME& time)
    {
        return CStr::Fmt(_T("%4d-%02d-%02d %02d:%02d:%02d"), time.wYear, time.wMonth, time.wDay, time.wHour, time.wMinute, time.wSecond);
    }
 
 
    //----------------
 
 
    static SQLTCHAR* GetSqlChar(CStr& _s, size_t len = 0)
    {
        #ifndef _UNICODE
            return reinterpret_cast<SQLCHAR*>(_s.GetBuffer(len));
        #else
            return _s.GetBuffer(len);
        #endif
    }
 
private:
    bool m_PreConnect(void)
    {
        if ( ! IsStarted() && ! Start() )
        {
            return false;
        }
        return Disconnect();
    }
    // 戻り値チェック
    bool m_CheckReturnCode(SQLRETURN r) const
    {
        return CReturnCode::IsReturnCodeSuccess(r);
    }
    // 戻り値処理
    bool m_Rst(SQLSMALLINT type = 0, SQLHANDLE handle = NULL, SQLRETURN rc = 0)
    {
        m_lastReturnCode = CReturnCode(type, handle, rc);
        return m_CheckReturnCode(rc);
    }
 
    SQLHENV     m_hEnv;             
    SQLHDBC     m_hDbc;             
    DWORD       m_timeout;          
    bool        m_isConnecting;     
    CReturnCode m_lastReturnCode;   
};
 
 
 
}; // TNB