//  Beta release only.
//
//  Copyright(c) Ivyware Pty Ltd 2007-11  (all rights reserved)
//               MELBOURNE, VICTORIA, AUSTRALIA, 3130
//
//  Provided no fee is charged (either by the distributer or user)
//  permission to use, copy, modify, and distribute this software and
//  its documentation for any purpose and without fee is hereby granted,
//  provided that the above copyright notice appear in all copies and
//  that both that copyright notice and this permission notice appear
//  in supporting documentation.
//
//  No waranty or suitablity for purpose is implied.
//
//
//  P2PeerHub SQL-ODBC integration object
//  NOTES: Integrates SQL database, using ODBC API into a P2Peer virtual
//         network
//       : Supports ODBC connection and disconnection, execution of SQL
//         statements and stored procedures.
//               
//

#include "StdAfx.h"
#include "ODBC_Ext.h"
#include "P2PTL.h"
#include "P2PeerHubODBC.h"
#include "P2Pwin32.h"
#include "P2Pevent.h"

//#include <Odbcinst.h>
//#include <Sql.h>
//#include <Sqltypes.h>
//#include <Sqlucode.h>
//#include <Msdasql.h>
//#include <Msdadc.h>

P2PeerHubODBC::P2PeerHubODBC ( P2PaddrSTR strP2PaddrThis )
             : P2PeerHub ( strP2PaddrThis )
{
    // Firstly
    RenderThisSafe();
}

P2PeerHubODBC::~P2PeerHubODBC ( void )
{
    if ( m_nSQLPumpID > 0 )
      ClosePump ( m_nSQLPumpID );
}

void
P2PeerHubODBC::RenderThisSafe (  )
{
    // Attributes
    m_nSQLPumpID = 0;
    m_hSQL       = SQL_NULL_HANDLE;
    m_hDBC       = SQL_NULL_HDBC;
    m_hEnv       = SQL_NULL_HENV;
}

///////////////////////////////////////////////////////////////////////
// P2PeerTarget virtual overrides


///////////////////////////////////////////////////////////////////////
//  P2PeerCon event handlers
//  NOTES: If there is a desire to optimise the connection map
//         subjectively place frequent messages at the top and less
//         frequent messages at the bottom of the map
//       : 
BEGIN_P2PeerCon_MAP(P2PeerHubODBC, P2PeerHub)
END_P2PeerCon_MAP()

///////////////////////////////////////////////////////////////////////
//  P2PeerMsg Handlers
//  NOTES: Default handler set available in P2PeerHub derived objects
//       : If there is a desire to optimise the message map
//         subjectively place frequent messages at the top and less
//         frequent messages at the bottom of the map
//
BEGIN_P2PeerMsg_MAP(P2PeerHubODBC, P2PeerHub)
    ON_P2PeerMsg("SQLconnect2server",On_SQLconnect2server)
    ON_P2PeerMsg("SQLDriverConnect",On_SQLDriverConnect)
    ON_P2PeerMsg("SQLselect",On_SQLselect)
    ON_P2PeerMsg("ExecuteSP",On_ExecuteSP)
    ON_P2PeerMsg("SQLdisconnectServer",On_SQLdisconnectServer)
END_P2PeerMsg_MAP()

//
//  MSG_SQLConnect
//  Performs SQL-ODBC connection
//
//
//  Parameters:  P2PeerMsg *pMsg
//               SQL connection message
//
//  Returns:     msgRESULT
//               Completion summary
//
msgRESULT
P2PeerHubODBC::On_SQLconnect2server ( P2PeerMsg *pMsg )
{
    // Locals
    SQLRETURN  sqlResult;

    // Because this is all problematic, we
    try
    {
      // To be sure, to be sure
      // NOTES: Create SQL pump for subsequent processing
      if ( m_nSQLPumpID                  ||
           m_hSQL       != SQL_NULL_HDBC    )
        EVSQL->MODULE
             ->Message_T("Already connected to server")
             ->Throw();

      // Confirm thread context
      // NOTES: SQL requests can generate 1000's of responses that overwhelm
      //        message queues.  As such the SQL query and consequence P2PeerMsg
      //        generation is performed by the dedicated SQL pump
      //      : This leaves the primary Hub thread free to pump the P2PeerMsg's
      //        generated by the query
      //      : However, this all negated if SQL processing pump does not exist
      if (  m_nSQLPumpID                &&
           !P2PeerContext(m_nSQLPumpID)    )
        return P2PeerContextSwap(m_nSQLPumpID);

      // Allocate environment handle
      SQLHANDLE hSQL;
      sqlResult = SQLAllocHandle ( SQL_HANDLE_ENV
                                 , SQL_NULL_HANDLE
                                 , &hSQL ); 
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_ENV, SQL_NULL_HANDLE )
             ->MODULE
             ->Message_T("SQLAllocHandle(ENV) failed")
             ->Throw();
      SafeSQLHANDLE shSQL(SQL_HANDLE_ENV,hSQL);

      // Set environment attributes
      sqlResult = SQLSetEnvAttr ( hSQL
                                , SQL_ATTR_ODBC_VERSION
                                , (void*)SQL_OV_ODBC3, 0 ); 
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_ENV, shSQL )
             ->MODULE
             ->Message_T("SQLSetEnvAttr() failed")
             ->Throw();

      // Allocate connection handle
      SQLHANDLE hDBC;
      sqlResult = SQLAllocHandle ( SQL_HANDLE_DBC
                                 ,  hSQL
                                 , &hDBC ); 
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_ENV, shSQL )
             ->MODULE
             ->Message_T("SQLAllocHandle(DBC) failed")
             ->Throw();
      SafeSQLHANDLE shDBC(SQL_HANDLE_DBC,hDBC);
          
      // Set login timeout
      sqlResult = SQLSetConnectAttr ( hDBC
                                    , SQL_LOGIN_TIMEOUT
                                    , (SQLPOINTER)(NULL), 0);
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_DBC, hDBC )
             ->MODULE
             ->Message_T("SQLSetConnectAttr(DBC) failed, connection timeout")
             ->Throw();

      // Connect to data source
      P3PmsgNode& oNodeData = pMsg -> r_datn();
      LPCTSTR   szDSN = "DSN=IVYWARE01\\SQLEXPRESS;";

      sqlResult = SQLConnect( hDBC
                            , (SQLCHAR*)szDSN //(SQLCHAR*)oNodeData["ServerName"].c_str()
                            , SQL_NTS
                            , (SQLCHAR*)oNodeData["Username"].c_str()
                            , SQL_NTS
                            , (SQLCHAR*)oNodeData["Password"].c_str()
                            , SQL_NTS );
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_DBC, hDBC )
             ->MODULE
             ->Message(_T("SQLConnect(DBC) failed1, server[%s]"), oNodeData["ServerName"].c_str() )
             ->Throw();

      // Create P2Pump to run subsequent queries
      SpawnPump ( 0, 0, _T("SQL"), &m_nSQLPumpID );
      m_hSQL = shSQL.Deref();
      m_hDBC = shDBC.Deref();

      // Perform acknowledgement
      return ReflectP2PeerMsg ( pMsg );
    }

    // Exceptions
    catch( P2Pevent *pEVT )
    {
      pEVT -> Advice_T ( "Connect2server request abandoned" );
      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel(true);
    }

    // Tidy up and
    return msgHANDLED;
}

//
//  MSG_SQLDriverConnect
//  Performs SQL-ODBC Driver connection
//
//
//  Parameters:  P2PeerMsg *pMsg
//               SQL connection request
//
//  Returns:     msgRESULT
//               Completion summary
//
msgRESULT
P2PeerHubODBC::On_SQLDriverConnect ( P2PeerMsg *pMsg )
{
    // Locals
    SQLRETURN  sqlResult;

    // Because this is all problematic, we
    try
    {
      // To be sure, to be sure
      // NOTES: Create SQL pump for subsequent processing
      if ( m_nSQLPumpID                  ||
           m_hSQL       != SQL_NULL_HDBC    )
        EVSQL->MODULE
             ->Message_T("Already connected to server")
             ->Throw();

      // Confirm thread context
      // NOTES: SQL requests can generate 1000's of responses that overwhelm
      //        message queues.  As such the SQL query and consequence P2PeerMsg
      //        generation is performed by the dedicated SQL pump
      //      : This leaves the primary Hub thread free to pump the P2PeerMsg's
      //        generated by the query
      //      : However, this all negated if SQL processing pump does not exist
      if (  m_nSQLPumpID                &&
           !P2PeerContext(m_nSQLPumpID)    )
        return P2PeerContextSwap(m_nSQLPumpID);

      // Allocate environment handle
      SQLHANDLE hSQL;
      sqlResult = SQLAllocHandle ( SQL_HANDLE_ENV
                                 , SQL_NULL_HANDLE
                                 , &hSQL ); 
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_ENV, SQL_NULL_HANDLE )
             ->MODULE
             ->Message_T("SQLAllocHandle(ENV) failed")
             ->Throw();
      SafeSQLHANDLE shSQL(SQL_HANDLE_ENV,hSQL);

      // Set environment attributes
      sqlResult = SQLSetEnvAttr ( hSQL
                                , SQL_ATTR_ODBC_VERSION
                                , (void*)SQL_OV_ODBC3, 0 ); 
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_ENV, shSQL )
             ->MODULE
             ->Message_T("SQLSetEnvAttr() failed")
             ->Throw();

      // Allocate connection handle
      SQLHANDLE hDBC;
      sqlResult = SQLAllocHandle ( SQL_HANDLE_DBC
                                 ,  hSQL
                                 , &hDBC ); 
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_ENV, shSQL )
             ->MODULE
             ->Message_T("SQLAllocHandle(DBC) failed")
             ->Throw();
      SafeSQLHANDLE shDBC(SQL_HANDLE_DBC,hDBC);
          
      // Set login timeout
      sqlResult = SQLSetConnectAttr ( hDBC
                                    , SQL_LOGIN_TIMEOUT
                                    , (SQLPOINTER)(NULL), 0);
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_DBC, hDBC )
             ->MODULE
             ->Message_T("SQLSetConnectAttr(DBC) failed, connection timeout")
             ->Throw();

      // Connect to data source
      TCHAR       szDSNout[1024];
      P3PmsgNode& oNodeData = pMsg -> r_datn();
      SQLSMALLINT    nOutSize = 0;
      sqlResult = SQLDriverConnect ( hDBC
                                   , NULL  // GetDesktopWindow()
                                   , (SQLCHAR *)oNodeData["ConnectionString"].c_str() //szDSN
                                   , SQL_NTS
                                   , (SQLCHAR *)szDSNout
                                   , sizeof(szDSNout)/2
                                   , &nOutSize
                                   , SQL_DRIVER_NOPROMPT ); // SQL_DRIVER_PROMPT );
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_DBC, hDBC )
             ->MODULE
             ->Message(_T("SQLConnect(DBC) failed1, server[%s]"), oNodeData["ServerName"].c_str() )
             ->Throw();

      // Create P2Pump to run subsequent queries
      SpawnPump ( 0, 0, _T("SQL"), &m_nSQLPumpID );
      m_hSQL = shSQL.Deref();
      m_hDBC = shDBC.Deref();

      // Perform acknowledgement
      return ReflectP2PeerMsg ( pMsg );
    }

    // Exceptions
    catch( P2Pevent *pEVT )
    {
      pEVT -> Advice_T ( "Connect2server request abandoned" );
      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel(true);
    }

    // Tidy up and
    return msgHANDLED;
}

//
//  MSG_DisconnectSQL
//  Performs SQL-ODBC disconnection
//
//
//  Parameters:  P2PeerMsg *pP2PeerMsg
//               Disconnect message
//
//  Returns:     msgRESULT
//               Completion summary
//
msgRESULT
P2PeerHubODBC::On_SQLdisconnectServer  ( P2PeerMsg *pMsg )
{
    // Beacause this is all problematic, we
    try
    {
      // SQL disconnection
      if ( m_hDBC != SQL_NULL_HDBC            &&
           SQLDisconnect(m_hDBC) == SQL_ERROR    )
        EVSQL->MessageSQLGetDiagRec(SQL_HANDLE_DBC,m_hDBC)
             ->MODULE
             ->Message_T("SQLFreeHandle(HDBC) failed")
             ->Cancel   ( );

      // ODBC disconnection
      if ( m_hDBC != SQL_NULL_HDBC                           &&
           SQLFreeHandle(SQL_HANDLE_DBC,m_hDBC) == SQL_ERROR    )
        EVSQL->MessageSQLGetDiagRec(SQL_HANDLE_DBC,m_hDBC)
             ->MODULE
             ->Message_T("SQLFreeHandle(HDBC) failed")
             ->Cancel ( );
      else
        m_hDBC = SQL_NULL_HDBC;

      // SQL environment disconnection
      if ( m_hSQL != SQL_NULL_HENV                           &&
           SQLFreeHandle(SQL_HANDLE_ENV,m_hSQL) == SQL_ERROR    )
        EVSQL->MessageSQLGetDiagRec(SQL_HANDLE_ENV,m_hSQL)
             ->MODULE
             ->Message_T("SQLFreeHandle(HENV) failed")
             ->Cancel   ( );
      else
        m_hSQL = SQL_NULL_HENV;
    }

    // Exceptions
    // NOTES: Post back to source
    catch ( P2Pevent *pEVT )
    {
      pEVT -> Advice_T ( "DisconnectSQL request abandoned" );

      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel ( );
    }

    catch ( ... )
    {
      P2Pevent *pEVT =
      EVERR->MODULE
           ->Message_T("Last resort exception" );

      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel( );
    }

    // Tidy up and
    return msgHANDLED;
}

//
//  MSG_SQLselect
//  NOTES: Executes passed SQL-ODBC statement and posts SQL response back
//         in a series of record set messages
//
//
//  Parameters:  P2PeerMsg *pP2PeerMsg
//               Message containing SQL statement to be executed
//               NOTES: Example "select * from Tablename"
//
//  Returns:     msgRESULT
//               Completion summary
//
msgRESULT
P2PeerHubODBC::On_SQLselect ( P2PeerMsg *pMsg )
{
    // Introduce locals
    USES_CONVERSION;
    SQLRETURN    sqlResult;
    P2PeerMsgSP   spMsgSql;
    int            nRSet = 0;

    // Confirm thread context
    // NOTES: SQL requests can generate 1000's of responses that overwhelm
    //        message queues.  As such the SQL query and consequence P2PeerMsg
    //        generation is performed by the dedicated SQL pump
    //      : This leaves the primary Hub thread free to pump the P2PeerMsg's
    //        generated by the query
    //      : However, this all negated if SQL processing pump does not exist
    if (  m_nSQLPumpID                &&
         !P2PeerContext(m_nSQLPumpID)    )
      return P2PeerContextSwap(m_nSQLPumpID);

    // Because this is all problematic we
    try
    {
      // To be sure, to be sure
      // NOTES: Pre-fetch a number of fields to ensure availablity
      if ( m_hDBC == SQL_NULL_HDBC )
        EVERR->MODULE
             ->Message_T("Not connected to data base" )
             ->Throw    ( );

      LPCTSTR lpszMsgReply = pMsg -> r_datn()["MsgReply"].c_str();

      // Allocate statement handle
      // NOTES: Resource managed via local safe handle
      HSTMT hStmt = SQL_NULL_HSTMT;
      sqlResult = SQLAllocHandle ( SQL_HANDLE_STMT, m_hDBC, &hStmt );
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec(SQL_HANDLE_DBC,m_hDBC)
             ->MODULE
             ->Message_T("SQLAllocStmt failed")
             ->Throw ( );
      SafeSQLHANDLE shSTMT ( SQL_HANDLE_STMT, hStmt );

      // Prepare the SQL statement by assigning it to the statement handle
      LPCTSTR lpszSqlStmt  = pMsg -> r_datn()["SQLstmt"].c_str();
      sqlResult = SQLPrepare ( hStmt
                             , (SQLCHAR *)lpszSqlStmt, (SQLINTEGER)strlen(lpszSqlStmt) );
      if ( sqlResult != SQL_SUCCESS )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT,hStmt )
             ->MODULE
             ->Message_T("SQLPrepare failed")
             ->Throw ( );

      // Execute the SQL statement handle
      sqlResult = SQLExecute( hStmt );  
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
             ->MODULE
             ->Message("SQLExecute (%s) failed" 
                      , lpszSqlStmt )
             ->Throw();

      // Fetch result set summary
      SQLSMALLINT nCols = 0;
      sqlResult = SQLNumResultCols ( hStmt, &nCols );
      if ( sqlResult != SQL_SUCCESS )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
             ->MODULE
             ->Message("SQLNumResultCols (%s) failed" 
                      , lpszSqlStmt )
             ->Throw();

      // Loop for all result sets
      // NOTES: Result set may contain zero, 1, 2, ... result
      //        sets.  Each of which is packages into its own P2PeerMsg
      //        and routed back to the source
      while ( (sqlResult=SQLFetch(hStmt)) == SQL_SUCCESS )
      {
        SQLCHAR      szColumnName[128];
        SQLSMALLINT  iNameLength;
        SQLSMALLINT  iDataType;
        SQLUINTEGER  uiColumnSize;
        SQLSMALLINT  iDecimalDigits;
        SQLSMALLINT  iNullablePtr;
        SQLCHAR      szValue[10000];
        SQLWCHAR     wzValue[10000];
        SQLINTEGER   cbValue;
        P3PmsgData   oData;

        // Generate record set response
        spMsgSql = pMsg -> ResponseFactory ( lpszMsgReply, 0, 0 );
        P3PmsgNode oNodeData = spMsgSql->r_datn().r_Object();
        oNodeData.AddField ( "RSet", P3PmsgData(nRSet++) );
        oNodeData.AddField ( "Cols", P3PmsgData(nCols) );
        oNodeData.AddField ( "sqlResult", P3PmsgData(SQL_SUCCESS) );
        if ( pMsg->r_datn().Exists("MsgReplyTag") )
          oNodeData += pMsg->r_datn().SelectItem("MsgReplyTag");
        P3PmsgNode oNodeSQL = oNodeData.PushBack ( P3PmsgNode("SQL",P3PmsgData()) );
//if(nRSet>100)
//break;
        for ( SQLUSMALLINT c = 1; c <= nCols; c++ )
        {
          // Fetch column description
          sqlResult =  SQLDescribeCol ( hStmt
                                      , c
                                      , szColumnName, sizeof(szColumnName)
                                      ,&iNameLength
                                      ,&iDataType
                                      ,&uiColumnSize
                                      ,&iDecimalDigits
                                      ,&iNullablePtr );
          if ( sqlResult != SQL_SUCCESS )
            EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
                 ->MODULE
                 ->Message("SQLDescribeCol (%s) failed" 
                          , lpszSqlStmt )
                 ->Throw();
          if ( iNullablePtr )
            oData.SetAttr ( 0, 0 );
          else
            oData.SetAttr ( 0, 0 );

          // Fetch column data
          cbValue = uiColumnSize;
          if ( iDataType == SQL_VARCHAR )
            sqlResult = SQLGetData ( hStmt, c, SQL_C_CHAR
                                   , szValue, sizeof(szValue), &cbValue );
          else if ( iDataType >= 0 )
            sqlResult = SQLGetData ( hStmt, c, iDataType
                                   , szValue, sizeof(szValue), &cbValue );
          else if ( iDataType == SQL_WVARCHAR )
            sqlResult = SQLGetData ( hStmt, c, SQL_C_WCHAR
                                   , wzValue, sizeof(wzValue), &cbValue );
          else if ( iDataType == SQL_BIGINT )
            sqlResult = SQLGetData ( hStmt, c, SQL_C_TCHAR
                                   , szValue, sizeof(szValue), &cbValue );
          else if ( iDataType == SQL_VARBINARY     ||
                    iDataType == SQL_LONGVARBINARY    )
            sqlResult = SQLGetData ( hStmt, c, SQL_C_BINARY 
                                   , szValue, sizeof(szValue), &cbValue );
          else if ( iDataType == -150 )
            sqlResult = SQLGetData ( hStmt, c, SQL_C_WCHAR
                                   , wzValue, sizeof(wzValue), &cbValue );
          else
            sqlResult = SQLGetData ( hStmt, c, iDataType
                                   , szValue, sizeof(szValue), &cbValue );
          if ( sqlResult != SQL_SUCCESS )
            EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
                 ->MODULE
                 ->Message("SQLGetData(column=%i,datatype=%i) failed" 
                          , c, iDataType )
                 ->Throw();
          if ( cbValue == SQL_NULL_DATA  )
          {
            SQLGetData_2_P3PmsgData ( oData, iDataType, 0, cbValue );
            oNodeSQL.AddField ( (LPCTSTR)szColumnName, oData );
            continue;
          }

          //else
          //  ODBCdata_2_P3PmsgData ( oData, iDataType, szValue, cbValue );

          // Make repository
          // NOTES: Map from SQL to P2Peer data types
          if ( iDataType == SQL_CHAR )
          {
            while ( cbValue            >   0  &&
                    szValue[cbValue-1] == ' '    )
              cbValue--;
            if ( cbValue <= 0xFF )
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BSTR08) );
            else
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BSTR16) );
          }
          else if ( iDataType == SQL_VARCHAR )
          {
            if ( cbValue <= 0xFF )
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BSTR08) );
            else
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BSTR16) );
          }
          else if ( iDataType == SQL_WVARCHAR ||
                    iDataType == -150            )
          {
            LPCTSTR lpszValue = W2A(wzValue);
            if ( cbValue <= 0xFF )
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(lpszValue,cbValue,VBLockData_BSTR08) );
            else
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(lpszValue,cbValue,VBLockData_BSTR16) );
          }
          else if ( iDataType == SQL_WCHAR )
          {
            while ( cbValue            >   0  &&
                    szValue[cbValue-1] == ' '    )
              cbValue--;
            if ( cbValue <= 0xFF )
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BSTR08) );
            else
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BSTR16) );
          }
          else if ( iDataType == SQL_INTEGER )
          {
            P3PmsgData oData(*(int*)szValue);
            if ( (iNullablePtr&SQL_NO_NULLS) == SQL_NO_NULLS )
              oData.SetAttr ( 0, VBLockAttr_NULLABLE );
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(int*)szValue) );
          }
          else if ( iDataType == SQL_BIGINT )
          {
            P3PmsgData oData(*(INT64*)szValue);
            if ( (iNullablePtr&SQL_NO_NULLS) == SQL_NO_NULLS )
              oData.SetAttr ( 0, VBLockAttr_NULLABLE );
            oNodeSQL.AddField ( (LPCTSTR)szColumnName, oData );
          }
          else if ( iDataType == SQL_SMALLINT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(short*)szValue) );
          else if ( iDataType == SQL_FLOAT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(float*)szValue) );
          else if ( iDataType == SQL_FLOAT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(double*)szValue) );
          else if ( iDataType == SQL_DECIMAL )
          {
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(double*)szValue) );
          }
          else if ( iDataType == SQL_TINYINT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( (short)*(unsigned char*)szValue) );
          else if ( iDataType == SQL_GUID )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(GUID*)szValue) );
          else if ( iDataType == SQL_TIME )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(int*)szValue) );
          else if ( iDataType == SQL_BIT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(bool*)szValue) );
          else if ( iDataType == SQL_TIMESTAMP )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(int*)szValue) );
          else if ( iDataType == SQL_TYPE_TIMESTAMP )
          {
            SQL_TIMESTAMP_STRUCT *pTIMESTAMP = (SQL_TIMESTAMP_STRUCT *)szValue;
            COleDateTime o;
            SQLSMALLINT nYear = pTIMESTAMP->year;
            if ( nYear < 1970 )
              nYear = 1971;
            CTime oTime (            nYear, pTIMESTAMP->month,  pTIMESTAMP->day
                        , pTIMESTAMP->hour, pTIMESTAMP->minute, pTIMESTAMP->second );
            P3PmsgTime oDataTime ( oTime.GetTime() );
            oNodeSQL.AddField ( (LPCTSTR)szColumnName, oDataTime );
          }
          else if ( iDataType == SQL_VARBINARY     ||
                    iDataType == SQL_LONGVARBINARY    )
          {
            if ( cbValue <= 0xFF )
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BLOB08) );
            else if ( cbValue <= 0xFFFF )
              oNodeSQL.AddField ( (LPCTSTR)szColumnName
                                , P3PmsgData(szValue,cbValue,VBLockData_BLOB16) );
            else
              ASSERT(0);
          }
          else
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData("n/a",3,VBLockData_BSTR08) );
        }

        // Post 
        PostP2PeerMsg ( spMsgSql.Dereference() );
      }

      // Perform end of record set notification
      spMsgSql = pMsg -> ResponseFactory ( lpszMsgReply, 0, 0 );
      spMsgSql -> r_datn().AddField ( "RSet", P3PmsgData(nRSet) );
      spMsgSql -> r_datn().AddField ( "Cols", P3PmsgData(nCols) );
      spMsgSql -> r_datn().AddField ( "sqlResult", P3PmsgData(SQL_NO_DATA) );
      if ( pMsg->r_datn().Exists("MsgReplyTag") )
        spMsgSql -> r_datn() += pMsg->r_datn().SelectItem("MsgReplyTag");
      PostP2PeerMsg ( spMsgSql.Dereference() );
    }

    // Exceptions
    // NOTES: Post back to source
    catch ( P2Pevent *pEVT )
    {
      pEVT -> Advice_T( "SQL SELECT request abandoned" );

      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel ( );
    }

    catch ( ... )
    {
      P2Pevent *pEVT =
      EVERR->MODULE
           ->Message_T("Last resort exception" );

      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel( );
    }

    // Tidy up and
    return msgHANDLED;
}

//
//  MSG_ExecuteSP
//  NOTES: Executes passed Stored Procedure and posts SQL response back
//         in a series of record set messages
//
//
//  Parameters:  P2PeerMsg *pP2PeerMsg
//               Stored procedure to be executed
//
//  Returns:     msgRESULT
//               Completion summary
//
msgRESULT
P2PeerHubODBC::On_ExecuteSP ( P2PeerMsg *pMsg )
{
    // Introduce locals
    SQLRETURN    sqlResult;
    P2PeerMsgSP   spMsgSql;
    int            nRSet = 0;

    // Because this is all problematic we
    try
    {
      // To be sure, to be sure
      // NOTES: Pre-fetch a number of fields to ensure availablity
      if ( m_hDBC == SQL_NULL_HDBC )
        EVERR->MODULE
             ->Message_T("Not connected to data base" )
             ->Throw  ( );

      LPCTSTR lpszMsgReply = pMsg -> r_datn()["MsgReply"].c_str();
      LPCTSTR lpszSPname   = pMsg -> r_datn()["SPname"].c_str();

      // Allocate statement handle
      // NOTES: Resource managed via local safe handle
      HSTMT hStmt = SQL_NULL_HSTMT;
      sqlResult = SQLAllocHandle ( SQL_HANDLE_STMT, m_hDBC, &hStmt );
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec(SQL_HANDLE_DBC,m_hDBC)
             ->Module ( __FUNCTION__"(%s)"
                      , lpszSPname )
             ->Message("SQLAllocStmt failed")
             ->Throw ( );
      SafeSQLHANDLE shSTMT ( SQL_HANDLE_STMT, hStmt );

      // Build Stored Procedure call
      char szSPstmt[2048];
      int   iSize = 0;
      iSize += sprintf_s (&szSPstmt[iSize], (sizeof(szSPstmt)-iSize), "{call %s"
                         , pMsg -> r_datn()["SPname"].c_str() );

      iSize += sprintf_s (&szSPstmt[iSize], (sizeof(szSPstmt)-iSize), "}" );

      // Prepare the SQL statement by assigning it to the statement handle
      sqlResult = SQLPrepare ( hStmt
                             , (SQLCHAR *)szSPstmt, (SQLINTEGER)strlen(szSPstmt) );
      if ( sqlResult != SQL_SUCCESS )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT,hStmt )
             ->Module ( __FUNCTION__"(%s)"
                      , lpszSPname )
             ->Message("SQLPrepare failed")
             ->Throw ( );

      // Execute the SQL statement handle
      sqlResult = SQLExecute( hStmt );  
      if ( sqlResult != SQL_SUCCESS           &&
           sqlResult != SQL_SUCCESS_WITH_INFO    )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
             ->Module ( __FUNCTION__"(%s)"
                      , lpszSPname )
             ->Message("SQLExecute (%s) failed" 
                      , szSPstmt )
             ->Throw();

      // Fetch result set summary
      SQLSMALLINT nCols = 0;
      sqlResult = SQLNumResultCols ( hStmt, &nCols );
      if ( sqlResult != SQL_SUCCESS )
        EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
             ->MODULE
             ->Message("SQLNumResultCols (%s) failed" 
                      , szSPstmt )
             ->Throw();

      // Loop for all result sets
      // NOTES: Result set may contain zero, 1, 2, ... result
      //        sets.  Each of which is packages into its own P2PeerMsg
      //        and routed back to the source
      while ( (sqlResult=SQLFetch(hStmt)) == SQL_SUCCESS )
      {
        SQLCHAR      szColumnName[128];
        SQLSMALLINT  iNameLength;
        SQLSMALLINT  iDataType;
        SQLUINTEGER  uiColumnSize;
        SQLSMALLINT  iDecimalDigits;
        SQLSMALLINT  iNullablePtr;
        SQLCHAR      szValue[10000];
        SQLINTEGER   cbValue;

        // Generate record set response
        spMsgSql = pMsg -> ResponseFactory ( lpszMsgReply, 0, 0 );
        spMsgSql -> r_datn().AddField ( "RSet", P3PmsgData(nRSet++) );
        spMsgSql -> r_datn().AddField ( "Cols", P3PmsgData(nCols) );
        spMsgSql -> r_datn().AddField ( "sqlResult", P3PmsgData(SQL_SUCCESS) );
        //TODOif ( pMsg->HasName("UserData") )
        //  pMsgSql -> MakeField ( pMsg->Field("UserData") )
        P3PmsgNode& oNodeSQL = spMsgSql -> r_datn().AddNode ( "SQL", P3PmsgData() );

        for ( SQLUSMALLINT c = 1; c <= nCols; c++ )
        {
          // Fetch column description
          sqlResult =  SQLDescribeCol ( hStmt
                                      , c
                                      , szColumnName, sizeof(szColumnName)
                                      ,&iNameLength
                                      ,&iDataType
                                      ,&uiColumnSize
                                      ,&iDecimalDigits
                                      ,&iNullablePtr );
          if ( sqlResult != SQL_SUCCESS )
            EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
                 ->Module(__FUNCTION__)
                 ->Message("SQLDescribeCol (%s) failed" 
                          , szSPstmt )
                 ->Throw();

          // Fetch column data
          sqlResult = SQLGetData ( hStmt, c, iDataType
                                 , szValue, sizeof(szValue), &cbValue );
          if ( sqlResult != SQL_SUCCESS )
            EVSQL->MessageSQLGetDiagRec ( SQL_HANDLE_STMT, hStmt )
                 ->MODULE
                 ->Message("SQLGetData (%s) failed" 
                          , szSPstmt )
                 ->Throw();

          // Make repository
          // NOTES: Map from SQL to P2Peer data types
          if ( iDataType == SQL_CHAR )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(char*)szValue) );
          else if ( iDataType == SQL_VARCHAR )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData(szValue,(P2Psize_t)cbValue,VBLockData_BSTR08) );
          else if ( iDataType == SQL_INTEGER )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(int*)szValue) );
          else if ( iDataType == SQL_SMALLINT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(short*)szValue) );
          else if ( iDataType == SQL_FLOAT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(float*)szValue) );
          else if ( iDataType == SQL_DOUBLE )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(double*)szValue) );
          else if ( iDataType == SQL_TINYINT )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( (short)*(unsigned char*)szValue) );
          else if ( iDataType == SQL_TIME )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(int*)szValue) );
          else if ( iDataType == SQL_TIMESTAMP )
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData( *(int*)szValue) );
          else
            oNodeSQL.AddField ( (LPCTSTR)szColumnName
                              , P3PmsgData("n/a",3,VBLockData_BSTR08) );
        }

        // Post 
        PostP2PeerMsg ( spMsgSql.Dereference() );
      }

      // Perform end of record set notification
      spMsgSql = pMsg -> ResponseFactory ( lpszMsgReply, 0, 0 );
      spMsgSql -> r_datn().AddField ( "RSet", P3PmsgData(nRSet) );
      spMsgSql -> r_datn().AddField ( "Cols", P3PmsgData(nCols) );
      spMsgSql -> r_datn().AddField ( "sqlResult", P3PmsgData(SQL_NO_DATA) );
      PostP2PeerMsg ( spMsgSql.Dereference() );
    }

    // Exceptions
    // NOTES: Post back to source
    catch ( P2Pevent *pEVT )
    {
      pEVT -> Advice ( "ExecuteSP request abandoned" );

      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel ( );
    }

    catch ( ... )
    {
      P2Pevent *pEVT =
      EVERR->MODULE
           ->Message("Last resort exception" );

      PostP2PeerMsg ( pMsg->ExceptionFactory(pEVT) );
      pEVT -> Cancel( );
    }

    // Tidy up and
    return msgHANDLED;
}

//
//  P2PeventSQL events
//  NOTES: Implementation to simplify SQL error collation and
//         reporting
//
P2PeventODBC*
P2PeventODBC::MessageSQLGetDiagRec ( SQLSMALLINT hType, SQLHANDLE hSQL )
{
    // Locals
    SQLRETURN     sqlResult;
    SQLCHAR       SqlState[6]="";
    SQLCHAR       Msg[SQL_MAX_MESSAGE_LENGTH] ="";
    SQLINTEGER    NativeError=0;
    SQLSMALLINT   MsgLen;

    Msg[0] = 0;

    sqlResult = SQLGetDiagRec( hType, hSQL
                             , 1
                             , SqlState
                             , &NativeError
                             , Msg
                             , sizeof(Msg)-1
                             , &MsgLen );
    if ( sqlResult != SQL_NO_DATA_FOUND )
      this->Message("SQLGetDiagRec() -> %s"
                   , Msg );
    if ( NativeError )
      this->HResult(NativeError);

    // Tidy up, and
    return this;
};
