migrate data tables

Discussion in 'Perl Misc' started by John Smith, Dec 3, 2005.

  1. John Smith

    John Smith Guest

    I have data in a Foxpro DBF table. Is there a quick, simple way in Perl
    to copy this table into a SQLite table? I know how to do it manually
    using DBI, but I'm looking for a package that does it all automatically.
    I do not mind if the data types are not mapped exactly right. Could
    SNOPS do it?

    The reason I need to do this is because I need to generate some reports
    from the Foxpro database. However, I'm unfamiliar with Foxpro SQL, the
    Foxpro ODBC driver has very limited functionality, and the data is
    read-only. So it would be easier if I imported the data into a more
    advanced database and worked with it from there. I'm using Perl on
    Windows XP.
     
    John Smith, Dec 3, 2005
    #1
    1. Advertising

  2. John Smith

    robic0 Guest

    On Sat, 03 Dec 2005 14:26:16 -0500, John Smith <>
    wrote:

    >I have data in a Foxpro DBF table. Is there a quick, simple way in Perl
    >to copy this table into a SQLite table? I know how to do it manually
    >using DBI, but I'm looking for a package that does it all automatically.
    > I do not mind if the data types are not mapped exactly right. Could
    >SNOPS do it?
    >


    In my opinion, you have no business programming sql or database
    if you can't figure this out. If you are new to Perl but have done
    databse/sql queries and reports in only one dbm package, you may
    be in for a rude suprise just how tedius this is on the programming
    level. I have not used the DBI package before but have years
    on several levels programming of dbm and several win32, odbc
    managers, and of course ADO.

    Reading your dilema, I thought I would look back at the
    utility ado wrappers I developed for all of the com servers I've
    written to possibly write a Perl interface for it. After a few
    minutes of checking, I see someone has already done that.

    After a quick yahoo search took me to a quick list of cpan
    modules:
    <http://www.cpan.org/modules/00modlist.long.html#7)DatabaseInterfac>
    I found DBI and scanned it for what it does.
    DBI took a Microsoft approach and abstracted out the db engines.
    However, dbi, trying to be all things to all people on all os's
    abstracted out ADO on level of the db providers. Ado is specific
    to windows and is itself an abstracted interface to all of the
    db providers that run in windows. So in DBI, Ado
    (an abstraction) is a provider just like the other DBD's,
    written mostly for os's other than windows.

    So DBI deffers to DBD::xxx interfaces. ADO, a windows
    specific "dbi" interface, requires that you have
    MDAC and OleDB running on the machine because ado is
    a component of mdac. Should you run DBI using DBD::Ado
    it has to be run on a win32 machine with MDAC.

    Given all that, a solution I know of to your "database/DBI"
    compatibility problem with not wanting to use FoxPro itself
    to generate a report is this:

    -Get on a machine that has MS Access, import the Foxpro db
    into it, save it.
    -Go into the ODBC Manager in windows and set up a "udl"
    (Universal Data Locator) record that points to the name
    of your database. Let it create the "your name.udl" file.
    -Instantiate a DBI object, set DBD::Ado.
    -Pass the "your name.udl" to the dbd::ado method.
    -Start using ado on the database.
    <http://search.cpan.org/~sgoeldner/DBD-ADO-2.94/lib/DBD/ADO.pm>

    >The reason I need to do this is because I need to generate some reports
    >from the Foxpro database. However, I'm unfamiliar with Foxpro SQL, the
    >Foxpro ODBC driver has very limited functionality, and the data is
    >read-only. So it would be easier if I imported the data into a more
    >advanced database and worked with it from there. I'm using Perl on
    >Windows XP.


    This is really puzzling, Foxpro is very limited functionality?
    I never used foxpro, but most dbm engines support standard SQL
    syntax. Are you new to sql query's? Have you ever heard of
    "stored procedures"? Most need several practice query's to have
    it sink in. Mostly simple. However in Access, the level of
    auto-generated indirection (relationships) can produce query
    strings several K in size that can only be analyzed graphically.
    Access does however support simple sql standards.

    Any questions, please let me know.

    ===================================================
    A side note on Perl/Ado -

    On a developer level however, I do want to make a
    statement on the DBD::Ado and how in my opinion, it
    reflects as a decline of skills as we approach a pure
    macro programming community. I say nothing against
    the two authors who have written seperate ado modules.

    They are however dummed down interfaces. Not robust
    at all. They do NOT provide "all that you can be"
    to the Perl user of them. Remember ADO is of itself
    a very large DBI equivalent (in win32). The interface
    is basic and does not provide all thats possible.
    In my opinion its just typical of whats on CPAN.

    I may write my first Perl module for cpan giving
    Perl a real ADO interface, dunno yet.

    Ado interface itself is primitive. Any writer's of
    com servers using ado have for the most part written
    complex wrappers to it for internal server usage.
    Without the need to write content specific code that
    use these wrappers, the wrapper itself could be
    brought out (in name only) to Perl level methods.

    Here is some direct ADO wrapper and utility,
    com server C++ code. In it (if you read it) you will
    find all of the limited DBD::Ado methods and a lot more
    that should have been brought out to the Perl interface.

    These include an ADO wrapper class and a Recordset
    helper class. If probably, the com server is created
    independent to the Perl interface dll, the actual Ado
    com server is instantiated in this dll. This dll is
    where these classes go. In normal com (same address
    space) a simple interface pointer is passed to the caller
    for items such as recordsets. This is mitigated between
    the Perl,Ado package/instantiation dll/com dll interface.
    A truss is developed to navigate records to the Perl caller.

    Be that as it may. Its trivial, and no excuse not to have
    a robust Ado interface with Perl.

    I wrote these a few years ago (just skip it if your a script
    jokey, and or 1-liner premadonna)

    ==============================================================
    Samps:: Ado wrapper (should be promoted method for method
    to Perl caller) and a Recordset helper class (for internal).
    ==============================================================
    Ado wrapper class, uses the recordset helper class (underneith):


    // ADOTierX1.h: interface for the CADOTier class.
    //
    //////////////////////////////////////////////////////////////////////

    #if
    !defined(AFX_ADOTIER_H__F4DF1E23_D860_11D5_B52D_00010227EF90__INCLUDED_)
    #define AFX_ADOTIER_H__F4DF1E23_D860_11D5_B52D_00010227EF90__INCLUDED_

    #if _MSC_VER > 1000
    #pragma once
    #endif // _MSC_VER > 1000

    enum DataTypeEnum
    { adEmpty = 0,
    adTinyInt = 16,
    adSmallInt = 2,
    adInteger = 3,
    adBigInt = 20,
    adUnsignedTinyInt = 17,
    adUnsignedSmallInt = 18,
    adUnsignedInt = 19,
    adUnsignedBigInt = 21,
    adSingle = 4,
    adDouble = 5,
    adCurrency = 6,
    adDecimal = 14,
    adNumeric = 131,
    adBoolean = 11,
    adError = 10,
    adUserDefined = 132,
    adVariant = 12,
    adIDispatch = 9,
    adIUnknown = 13,
    adGUID = 72,
    adDate = 7,
    adDBDate = 133,
    adDBTime = 134,
    adDBTimeStamp = 135,
    adBSTR = 8,
    adChar = 129,
    adVarChar = 200,
    adLongVarChar = 201,
    adWChar = 130,
    adVarWChar = 202,
    adLongVarWChar = 203,
    adBinary = 128,
    adVarBinary = 204,
    adLongVarBinary = 205
    };

    enum ParameterDirectionEnum
    { adParamUnknown = 0,
    adParamInput = 1,
    adParamOutput = 2,
    adParamInputOutput = 3,
    adParamReturnValue = 4
    };

    typedef struct tagFldInfo
    {
    tagFldInfo(){}
    tagFldInfo(CString fldName2, DataTypeEnum fldType2, long
    fldSize2, FieldAttributeEnum fldAttr2)
    {fldName = fldName2; fldType = fldType2; fldSize = fldSize2;
    fldAttr = fldAttr2;}
    void operator=(const struct tagFldInfo &arec)
    {fldName = arec.fldName; fldType = arec.fldType; fldSize =
    arec.fldSize; fldAttr = arec.fldAttr;}
    CString fldName;
    DataTypeEnum fldType;
    long fldSize;
    FieldAttributeEnum fldAttr;
    } FLDINFO;

    enum FieldCodeEnum
    {
    fcNone = 0,
    fcBoreID = 1,
    fcMountingID = 2,
    fcProductID = 3,
    fcPushPullID = 4,
    fcRodCaseID = 5,
    fcRodID = 6,
    fcRodLoadBConditionID = 7,
    fcRodLoadConditionID = 8,
    fcRodPivotCaseID = 9,
    fcSeriesID = 10,
    fcSingleDoubleRodID = 11
    };

    enum FieldTypeEnum
    {
    ftNone = 0,
    ftDouble = 1, // cATTRIBUTE_VALUE.CALC_VALUE
    for double
    ftStringBool = 2, //
    cATTRIBUTE_VALUE.MODEL_VALUE for string or boolean
    ftInteger = 3, //
    cATTRIBUTE_VALUE.LIST_INDEX1 for integer
    ftIntegerIN = 4 //
    cATTRIBUTE_VALUE.LIST_INDEX1 for integer, for IN(x,x,x)
    };

    typedef struct tagQryFilter_Rec
    {
    tagQryFilter_Rec()
    {sFieldName = _T(""); eFieldCode = fcNone; sOperator = _T("");
    eFieldType = ftNone;
    VData.dDVal = 0.; VData.sDValUnit = _T(""); VData.nIVal = 0;
    VData.sBVal = _T("'False'"); }
    tagQryFilter_Rec(CString sfName, FieldCodeEnum efCode, CString
    sOper, FieldTypeEnum efType, double dVal, CString sValUnit)
    {sFieldName = sfName; eFieldCode = efCode; eFieldType =
    efType; sOperator = sOper;
    VData.dDVal = dVal; VData.sDValUnit = sValUnit; VData.nIVal =
    0; VData.sBVal = _T("'False'"); }
    tagQryFilter_Rec(CString sfName, FieldCodeEnum efCode, CString
    sOper, FieldTypeEnum efType, int nVal)
    {sFieldName = sfName; eFieldCode = efCode; eFieldType =
    efType; sOperator = sOper;
    VData.dDVal = 0.; VData.sDValUnit = _T(""); VData.nIVal =
    nVal; VData.sBVal = _T("'False'"); }
    tagQryFilter_Rec(CString sfName, FieldCodeEnum efCode, CString
    sOper, FieldTypeEnum efType, CString sbVal)
    {sFieldName = sfName; eFieldCode = efCode; eFieldType =
    efType; sOperator = sOper;
    VData.dDVal = 0.; VData.sDValUnit = _T(""); VData.nIVal = 0;
    VData.sBVal = sbVal; }

    struct tagQryFilter_Rec& operator= (const struct
    tagQryFilter_Rec &qf)
    {sFieldName = qf.sFieldName; eFieldCode =
    qf.eFieldCode; eFieldType = qf.eFieldType;
    sOperator = qf.sOperator; VData.dDVal = qf.VData.dDVal;
    VData.sDValUnit = qf.VData.sDValUnit;
    VData.nIVal = qf.VData.nIVal; VData.sBVal = qf.VData.sBVal;
    return *this;}

    CString sFieldName; //
    CONTROL_LABEL (the field name, code is actually used)
    FieldCodeEnum eFieldCode; // DISPLAY_WINDOW
    (field code used in place of field name)
    FieldTypeEnum eFieldType; // Type of one of
    VData
    CString sOperator; // =, >, <,
    >=, NOT EQUAL TO, etc ...

    struct tagVdata{
    double dDVal; // CALC_VALUE - double
    CString sDValUnit; // CALC_UOM_CD - double unit
    int nIVal; // LIST_INDEX1 -
    integer
    CString sBVal; // MODEL_VALUE - "'True'" or
    "'False'" or "'String'"
    // also used
    for LIST_INDEX1 - integer for IN(x,x,x)
    } VData;
    } PIVFILTER;


    typedef struct tagDB2element
    {
    tagDB2element() {nRow = 0; sFld = _T(""); dDatDbl = 0.;
    sDatdblUnit = _T("");
    sDatStr = _T(""); nDatInt =
    0; nFldType = 0;}
    struct tagDB2element& operator= (const struct tagDB2element
    &db2Rec)
    {nRow = db2Rec.nRow; sFld = db2Rec.sFld; dDatDbl =
    db2Rec.dDatDbl;
    sDatdblUnit = db2Rec.sDatdblUnit; sDatStr = db2Rec.sDatStr;
    nDatInt = db2Rec.nDatInt; nFldType = db2Rec.nFldType; return *this;}
    long nRow;
    CString sFld;
    double dDatDbl;
    CString sDatdblUnit;
    CString sDatStr;
    long nDatInt;
    long nFldType;
    } DB2ELEMENT_REC;


    #include <vector>
    #include <algorithm>
    using namespace std;


    /////////////////////////////////
    // ADO wrapper class
    class CADOTierX1
    {
    public:
    CADOTierX1();
    virtual ~CADOTierX1();

    public:
    BOOL DisconnectRecordset(_RecordsetPtr &rsReturn);
    CString GetSrcPath();
    void ChangeRS(_RecordsetPtr &rsChange);
    bool RS_GetBool(LPCTSTR sFldName);
    bool RS_GetBool(int nFieldIndex);
    bool ConvertVarToBool(VARIANT &var);
    long RS_GetLong(LPCTSTR sFldName);
    long RS_GetLong(int nFieldIndex);
    long ConvertVarToLong(VARIANT &var);
    int RS_GetInt(LPCTSTR sFldName);
    int RS_GetInt(int nFieldIndex);
    int ConvertVarToInt(VARIANT &var);
    float RS_GetFloat(LPCTSTR sFldName);
    float RS_GetFloat(int nFieldIndex);
    float ConvertVarToFloat(VARIANT &var);
    double RS_GetDouble(LPCTSTR sFldName);
    double RS_GetDouble(int nFieldIndex);
    double ConvertVarToDouble(VARIANT &var);
    CString RS_GetString(LPCTSTR sFldName);
    CString RS_GetString(int nFieldIndex);
    CString ConvertVarToStr(VARIANT &var);
    void LogItem (const CString &s);
    CString GetVariantString (int nId);
    BOOL Empty();
    long GetRecordCount();
    void ADORelease();
    BOOL Requery(long options);
    BOOL ChangeParameter(long idx, BSTR name, enum DataTypeEnum
    type, VARIANT value, enum ParameterDirectionEnum where, long size);
    BOOL PutStoredProc(BSTR newVal);
    BOOL CallStoredProc(long idx1, BSTR idx2, BSTR idx3);
    BOOL ParamQuery(BSTR query, long idx1, BSTR idx2, BSTR idx3);
    BOOL IsBOF();
    BOOL IsEOF();
    BOOL Prev();
    BOOL Last();
    BOOL Next();
    BOOL First();
    BOOL Close();
    BOOL GetFieldCount(long *newVal);
    BOOL GetField(VARIANT idx, VARIANT *newVal);
    BOOL PutField(VARIANT idx, VARIANT newVal);
    BOOL Delete();
    BOOL Update();
    BOOL AppendParameter(BSTR name, enum DataTypeEnum type,
    VARIANT value, enum ParameterDirectionEnum where, long size);
    BOOL GetCommandText(BSTR *newVal);
    BOOL PutCommandText(BSTR newVal);
    BOOL PutCommandType(enum CommandTypeEnum newVal);
    BOOL ExecuteCommand(VARIANT_BOOL bStoredProcedure,
    VARIANT_BOOL bChangeRec = VARIANT_FALSE);
    BOOL ExecuteConnection(BSTR query, VARIANT_BOOL bChangeRec =
    VARIANT_FALSE);
    BOOL CloseRecordset();
    BOOL OpenRecordset(VARIANT query);
    BOOL Open(BSTR source, BSTR user, BSTR pwd, long options);

    private:
    _CommandPtr m_command;
    _RecordsetPtr m_recordset;
    _ConnectionPtr m_connection;
    CString m_Srcpath;
    long m_FldCount;
    };

    /////////////////
    // UTILITIES //
    /////////////////
    // Generic Create Disconnected Recordset
    template<class T>
    BOOL CreateRecordsetGeneric(_RecordsetPtr &rsReturnRecordSet, FLDINFO
    *Fi, int numflds, vector <T> &aRecords)
    {
    // Create the Recordset ...
    if (rsReturnRecordSet != NULL || numflds <= 0)
    return FALSE;
    HRESULT hr =
    rsReturnRecordSet.CreateInstance(__uuidof(Recordset));
    if (SUCCEEDED(hr))
    hr =
    rsReturnRecordSet->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr =
    rsReturnRecordSet->put_ActiveConnection(_variant_t((IDispatch
    *)NULL,false));
    // Append new Fields from FIELDINFO ...
    Fields *fields = 0;
    if (SUCCEEDED(hr))
    hr = rsReturnRecordSet->get_Fields(&fields);
    //int numflds = sizeof(Fi)/sizeof(FLDINFO);
    for (int i = 0; i < numflds; i++)
    {
    if (SUCCEEDED(hr))
    hr = fields->Append(_bstr_t(Fi.fldName),
    Fi.fldType, Fi.fldSize, Fi.fldAttr);
    }
    // Open the Recordset ...
    if (SUCCEEDED(hr))
    hr =
    rsReturnRecordSet->Open(_variant_t(""),_variant_t((IDispatch
    *)NULL,false),adOpenUnspecified,adLockUnspecified,adConnectUnspecified);
    // Get Field pointers ...
    Field **field = new (Field(*[numflds]));
    for (i = 0; i < numflds; i++)
    {
    field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(_variant_t((long)i),
    &field);
    }
    // Add new records ...
    int numrecs = aRecords.size();
    for (i = 0; i < numrecs; i++)
    {
    if (SUCCEEDED(hr))
    {
    hr = rsReturnRecordSet->AddNew();
    aRecords.PutRsFieldValues(field, (long)i);
    }
    }
    // Release references ...
    for (i = 0; i < numflds; i++)
    {
    if (field != 0)
    field->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;
    //
    if (SUCCEEDED(hr))
    return TRUE;
    rsReturnRecordSet->Close();
    rsReturnRecordSet.Release();
    return FALSE;
    }

    template<class T>
    BOOL CreateRecordsetGeneric(_RecordsetPtr &rsReturnRecordSet, vector
    <FLDINFO> &vaFi, vector <T> &aRecords)
    {
    int numflds = vaFi.size();
    // Create the Recordset ...
    if (rsReturnRecordSet != NULL || numflds <= 0)
    return FALSE;
    HRESULT hr =
    rsReturnRecordSet.CreateInstance(__uuidof(Recordset));
    if (SUCCEEDED(hr))
    hr =
    rsReturnRecordSet->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr =
    rsReturnRecordSet->put_ActiveConnection(_variant_t((IDispatch
    *)NULL,false));
    // Append new Fields from FIELDINFO ...
    Fields *fields = 0;
    if (SUCCEEDED(hr))
    hr = rsReturnRecordSet->get_Fields(&fields);
    for (int i = 0; i < numflds; i++)
    {
    if (SUCCEEDED(hr))
    hr = fields->Append(_bstr_t(vaFi.fldName),
    vaFi.fldType, vaFi.fldSize, vaFi.fldAttr);
    }
    // Open the Recordset ...
    if (SUCCEEDED(hr))
    hr =
    rsReturnRecordSet->Open(_variant_t(""),_variant_t((IDispatch
    *)NULL,false),adOpenUnspecified,adLockUnspecified,adConnectUnspecified);
    // Get Field pointers ...
    Field **field = new (Field(*[numflds]));
    for (i = 0; i < numflds; i++)
    {
    field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(_variant_t((long)i),
    &field);
    }
    // Add new records ...
    int numrecs = aRecords.size();
    for (i = 0; i < numrecs; i++)
    {
    if (SUCCEEDED(hr))
    {
    hr = rsReturnRecordSet->AddNew();
    aRecords.PutRsFieldValues(field, (long)i);
    }
    }
    // Release references ...
    for (i = 0; i < numflds; i++)
    {
    if (field != 0)
    field->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;
    //
    if (SUCCEEDED(hr))
    return TRUE;
    rsReturnRecordSet->Close();
    rsReturnRecordSet.Release();
    return FALSE;
    }

    #endif //
    !defined(AFX_ADOTIER_H__F4DF1E23_D860_11D5_B52D_00010227EF90__INCLUDED_)

    // ADOTierX1.cpp: implementation of the CADOTierX1 class.
    //
    //////////////////////////////////////////////////////////////////////

    #include "stdafx.h"
    #include "resource.h"
    #include "ADOTierX1.h"

    #ifdef _DEBUG
    #undef THIS_FILE
    static char THIS_FILE[]=__FILE__;
    #define new DEBUG_NEW
    #endif



    //#define _VTDEBUG


    //////////////////////////////////////////////////////////////////////
    // Construction/Destruction
    //////////////////////////////////////////////////////////////////////

    CADOTierX1::CADOTierX1()
    {
    m_FldCount = 0;
    m_Srcpath = _T("");
    }

    CADOTierX1::~CADOTierX1()
    {

    }

    /////////////////////////////////////////////////////////////////////////////
    //

    // Need two distinct "empty" VARIANTs for Command::Execute
    static VARIANT* pvtEmpty = static_cast<VARIANT*> (&vtMissing);
    static _variant_t vtMissing2(DISP_E_PARAMNOTFOUND, VT_ERROR);
    static VARIANT* pvtEmpty2 = static_cast<VARIANT*> (&vtMissing2);


    BOOL CADOTierX1::Open(BSTR source, BSTR user, BSTR pwd, long options)
    {
    USES_CONVERSION;
    HRESULT hr = m_connection.CreateInstance("ADODB.Connection");
    // HRESULT hr =
    m_connection.CreateInstance(__uuidof(Connection));
    if (SUCCEEDED(hr))
    {
    hr = m_connection->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr = m_connection->Open(source, user, pwd,
    options);
    }
    if (SUCCEEDED(hr))
    hr = m_command.CreateInstance(__uuidof(Command));
    if (SUCCEEDED(hr))
    hr = m_command->putref_ActiveConnection(m_connection);
    if (SUCCEEDED(hr))
    hr = m_recordset.CreateInstance(__uuidof(Recordset));

    // Save the source string ...
    m_Srcpath = OLE2CT(source);

    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::OpenRecordset(VARIANT query)
    {
    VARIANT v;
    V_VT(&v) = VT_DISPATCH;
    V_DISPATCH(&v) = (IDispatch*) m_connection;

    // Need the AddRef() as VariantClear() calls Release(), unless
    fAddRef
    // false indicates we're taking ownership
    //
    V_DISPATCH(&v)->AddRef();

    HRESULT hr = m_recordset->Open(query, v, adOpenDynamic,
    adLockOptimistic, adCmdText);
    m_FldCount = 0;
    GetFieldCount(&m_FldCount);

    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::CloseRecordset()
    {
    HRESULT hr = m_recordset->Close();
    m_FldCount = 0;
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::ExecuteConnection(BSTR query, VARIANT_BOOL
    bChangeRec)
    {
    _Recordset* prec = 0;
    HRESULT hr = m_connection->Execute(query, NULL, adCmdText,
    &prec);
    // HRESULT hr = m_connection->Execute(query, pvtEmpty, adCmdText,
    &prec);
    if (SUCCEEDED(hr))
    {
    if (bChangeRec)
    m_recordset = prec;
    else
    prec->Release();
    }
    m_FldCount = 0;
    GetFieldCount(&m_FldCount);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::ExecuteCommand(VARIANT_BOOL bStoredProcedure,
    VARIANT_BOOL bChangeRec)
    {
    _Recordset* prec = 0;
    HRESULT hr = 0;
    if (bStoredProcedure)
    {
    if (SUCCEEDED(hr))
    hr =
    m_command->put_NamedParameters(VARIANT_TRUE);
    if (SUCCEEDED(hr))
    {
    long St;
    m_connection->get_State(&St);
    if (adStateOpen == St)
    //1 hr = m_command->Execute(NULL, NULL,
    -1, &prec);
    hr = m_command->Execute(NULL, NULL,
    adCmdStoredProc, &prec);
    //3 hr = m_command->Execute(pvtEmpty,
    pvtEmpty2,adCmdStoredProc, &prec);
    }
    }
    else
    {
    hr = m_command->Execute(NULL, NULL, adCmdText, &prec);
    // hr = m_command->Execute(pvtEmpty, pvtEmpty2,
    adCmdText, &prec);
    }
    if (SUCCEEDED(hr))
    {
    if (bChangeRec)
    m_recordset = prec;
    else
    prec->Release();
    }
    m_FldCount = 0;
    GetFieldCount(&m_FldCount);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::GetCommandText(BSTR * newVal)
    {
    HRESULT hr = m_command->get_CommandText(newVal);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::putCommandType(enum CommandTypeEnum newVal)
    {
    HRESULT hr = m_command->put_CommandType(newVal);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::putCommandText(BSTR newVal)
    {
    HRESULT hr = m_command->put_CommandText(newVal);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }


    BOOL CADOTierX1::AppendParameter(BSTR name, enum DataTypeEnum type,
    VARIANT value, enum ParameterDirectionEnum where, long size)
    {
    _ParameterPtr param;
    HRESULT hr = param.CreateInstance(__uuidof(Parameter));

    if (SUCCEEDED(hr))
    hr = param->put_Name(name);
    if (SUCCEEDED(hr))
    hr = param->put_Type(type);
    if (SUCCEEDED(hr))
    hr = param->put_Value(value);
    if (SUCCEEDED(hr))
    hr = param->put_Direction(where);
    if (SUCCEEDED(hr))
    hr = param->put_Size(size);

    Parameters* params = 0;
    if (SUCCEEDED(hr))
    hr = m_command->get_Parameters(&params);
    if (SUCCEEDED(hr))
    hr = params->Append(param);

    if (SUCCEEDED(hr))
    {
    params->Release();
    param->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::Update()
    {
    HRESULT hr = m_recordset->Update();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::Delete()
    {
    HRESULT hr = m_recordset->Delete(adAffectCurrent);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::GetField(VARIANT idx, VARIANT *newVal)
    {
    Fields* fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);

    Field* field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(idx, &field);
    if (SUCCEEDED(hr))
    hr = field->get_Value(newVal);

    if (SUCCEEDED(hr))
    {
    fields->Release();
    field->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::putField(VARIANT idx, VARIANT newVal)
    {
    Fields* fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    Field* field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(idx, &field);
    if (SUCCEEDED(hr))
    hr = field->put_Value(newVal);

    if (SUCCEEDED(hr))
    {
    fields->Release();
    field->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::GetFieldCount(long * newVal)
    {
    Fields* fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    if (SUCCEEDED(hr))
    hr = fields->get_Count(newVal);
    if (SUCCEEDED(hr))
    fields->Release();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::Close()
    {
    HRESULT hr = m_connection->Close();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::First()
    {
    HRESULT hr = m_recordset->MoveFirst();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::Next()
    {
    HRESULT hr = m_recordset->MoveNext();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::Last()
    {
    HRESULT hr = m_recordset->MoveLast();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::prev()
    {
    HRESULT hr = m_recordset->MovePrevious();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::IsEOF()
    {
    VARIANT_BOOL newVal;
    HRESULT hr = m_recordset->get_EOF(&newVal);
    if (SUCCEEDED(hr) && newVal)
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::IsBOF()
    {
    VARIANT_BOOL newVal;
    HRESULT hr = m_recordset->get_BOF(&newVal);
    if (SUCCEEDED(hr) && newVal)
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::paramQuery(BSTR query, long idx1, BSTR idx2, BSTR
    idx3)
    {
    HRESULT hr = ChangeParameter(0, _bstr_t(""), adInteger,
    (_variant_t) idx1, adParamInput, -1);
    if (SUCCEEDED(hr))
    hr = ChangeParameter(1, _bstr_t(""), adVarChar,
    (_variant_t) idx2, adParamInput, 25);
    if (SUCCEEDED(hr))
    hr = ChangeParameter(2, _bstr_t(""), adVarChar,
    (_variant_t) idx3, adParamInput, 80);
    if (SUCCEEDED(hr))
    hr = m_command->put_CommandText(query);
    _Recordset* prec = 0;
    if (SUCCEEDED(hr))
    hr = m_command->Execute(NULL, NULL, adCmdText, &prec);
    // hr = m_command->Execute(pvtEmpty, pvtEmpty2,
    adCmdText, &prec);
    if (SUCCEEDED(hr))
    prec->Release();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::CallStoredProc(long idx1, BSTR idx2, BSTR idx3)
    {
    HRESULT hr = ChangeParameter(0, _bstr_t(""), adInteger,
    (_variant_t) idx1, adParamInput, 4);
    if (SUCCEEDED(hr))
    hr = ChangeParameter(1, _bstr_t(""), adVarChar,
    (_variant_t) idx2, adParamInput, 25);
    if (SUCCEEDED(hr))
    hr = ChangeParameter(2, _bstr_t(""), adVarChar,
    (_variant_t) idx3, adParamInput, 80);
    if (SUCCEEDED(hr))
    hr = m_command->put_CommandText(L"{call MyProc (?, ?,
    ?)}");
    _Recordset* prec = 0;
    if (SUCCEEDED(hr))
    hr = m_command->Execute(NULL, NULL, adCmdText, &prec);
    // hr = m_command->Execute(pvtEmpty, pvtEmpty2,
    adCmdText, &prec);
    if (SUCCEEDED(hr))
    prec->Release();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::putStoredProc(BSTR newVal)
    {
    if (newVal == NULL)
    newVal = ::SysAllocString(L"create procedure MyProc @i
    integer, @g varchar(25), @g varchar(80) into Guns (ID, Gun, [Gun
    Description]) values (@i, @g, @d) return");
    HRESULT hr = m_command->put_CommandText(newVal);
    _Recordset* prec = 0;
    if (SUCCEEDED(hr))
    hr = m_command->Execute(NULL, NULL, adCmdText, &prec);
    // hr = m_command->Execute(pvtEmpty, pvtEmpty2,
    adCmdText, &prec);
    if (SUCCEEDED(hr))
    prec->Release();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::ChangeParameter(long idx, BSTR name, enum
    DataTypeEnum type, VARIANT value, enum ParameterDirectionEnum where,
    long size)
    {
    Parameters* params = 0;
    HRESULT hr = m_command->get_Parameters(&params);

    _Parameter* param = 0;
    VARIANT v;
    V_VT(&v) = VT_I4;
    V_I4(&v) = idx;
    if (SUCCEEDED(hr))
    hr = params->get_Item(v, &param);

    if (SUCCEEDED(hr))
    hr = param->put_Name(name);
    if (SUCCEEDED(hr))
    hr = param->put_Type(type);
    if (SUCCEEDED(hr))
    hr = param->put_Value(value);
    if (SUCCEEDED(hr))
    hr = param->put_Direction(where);
    if (SUCCEEDED(hr))
    hr = param->put_Size(size);
    if (SUCCEEDED(hr))
    {
    params->Release();
    param->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADOTierX1::Requery(long options)
    {
    HRESULT hr = m_recordset->Requery(options);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    void CADOTierX1::ADORelease()
    {
    m_command = 0;
    m_recordset = 0;
    m_connection = 0;
    }

    long CADOTierX1::GetRecordCount()
    {
    long pl;
    pl = 0;
    HRESULT hr = m_recordset->get_RecordCount(&pl);
    if (SUCCEEDED(hr))
    return pl;
    return 0L;
    }

    BOOL CADOTierX1::Empty()
    {
    VARIANT_BOOL bEmpty;
    HRESULT hr = m_recordset->get_EOF(&bEmpty);
    if (SUCCEEDED(hr) && &bEmpty)
    hr = m_recordset->get_BOF(&bEmpty);
    if (SUCCEEDED(hr) && bEmpty)
    return TRUE;
    return FALSE;
    }



    /////////////// Utilities: Get DAO Field ///////////////////
    /*
    enum VARENUM
    { VT_EMPTY = 0,
    VT_NULL = 1,
    VT_I2 = 2,
    VT_I4 = 3,
    VT_R4 = 4,
    VT_R8 = 5,
    VT_CY = 6,
    VT_DATE = 7,
    VT_BSTR = 8,
    VT_DISPATCH = 9,
    VT_ERROR = 10,
    VT_BOOL = 11,
    VT_VARIANT = 12,
    VT_UNKNOWN = 13,
    VT_DECIMAL = 14,
    VT_I1 = 16,
    VT_UI1 = 17,
    VT_UI2 = 18,
    VT_UI4 = 19,
    VT_I8 = 20,
    VT_UI8 = 21,
    VT_INT = 22,
    VT_UINT = 23,
    VT_VOID = 24,
    VT_HRESULT = 25,
    VT_PTR = 26,
    VT_SAFEARRAY = 27,
    VT_CARRAY = 28,
    VT_USERDEFINED = 29,
    VT_LPSTR = 30,
    VT_LPWSTR = 31,
    VT_RECORD = 36,
    VT_FILETIME = 64,
    VT_BLOB = 65,
    VT_STREAM = 66,
    VT_STORAGE = 67,
    VT_STREAMED_OBJECT = 68,
    VT_STORED_OBJECT = 69,
    VT_BLOB_OBJECT = 70,
    VT_CF = 71,
    VT_CLSID = 72,
    VT_BSTR_BLOB = 0xfff,
    VT_VECTOR = 0x1000,
    VT_ARRAY = 0x2000,
    VT_BYREF = 0x4000,
    VT_RESERVED = 0x8000,
    VT_ILLEGAL = 0xffff,
    VT_ILLEGALMASKED = 0xfff,
    VT_TYPEMASK = 0xfff
    };
    */

    CString CADOTierX1::RS_GetString(LPCTSTR sFldName)
    {
    _variant_t newVal;
    CString stmp = _T("");

    if (GetField(_variant_t(sFldName), &newVal))
    stmp = ConvertVarToStr(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetString : GetField(FldName) returned
    error.");
    #endif
    return stmp;
    }


    CString CADOTierX1::RS_GetString(int nFieldIndex)
    {
    CString str;
    _variant_t newVal;
    CString stmp = _T("");

    if (nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    stmp = ConvertVarToStr(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetString : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetString : Field Index out of range");
    #endif
    return stmp;
    }


    CString CADOTierX1::ConvertVarToStr(VARIANT &var)
    {
    USES_CONVERSION;
    // Check that newVal is of type BSTR
    // MessageBox(0,(const char*)_bstr_t(tp),"Message",0);
    CString str = _T("");

    switch (var.vt)
    {
    case VT_BSTR:
    str = OLE2CT(var.bstrVal);
    ::SysFreeString(var.bstrVal);
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetString : " + GetVariantString
    (var.vt) + " Not String Type");
    #endif
    break;
    }
    return str;
    }


    double CADOTierX1::RS_GetDouble(LPCTSTR sFldName)
    {
    _variant_t newVal;
    double dVal = 0.;

    if (GetField(_variant_t(sFldName), &newVal))
    dVal = ConvertVarToDouble(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetDouble : GetField(FldName) returned
    error.");
    #endif
    return dVal;
    }


    double CADOTierX1::RS_GetDouble(int nFieldIndex)
    {
    _variant_t newVal;
    double dVal = 0.;

    if (nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    dVal = ConvertVarToDouble(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetDouble : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetDouble : Field Index out of range");
    #endif
    return dVal;
    }


    double CADOTierX1::ConvertVarToDouble(VARIANT &var)
    {
    double dVal = 0.;
    switch (var.vt)
    {
    case VT_I2:
    dVal = var.iVal;
    break;
    case VT_I4:
    dVal = var.lVal;
    break;
    case VT_R4:
    dVal = (double) var.fltVal;
    break;
    case VT_R8:
    dVal = var.dblVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetDouble : " + GetVariantString
    (var.vt) + " Not Numeric Type");
    #endif
    break;
    }
    return dVal;
    }


    int CADOTierX1::RS_GetInt(LPCTSTR sFldName)
    {
    _variant_t newVal;
    int nVal = 0;

    if (GetField(_variant_t(sFldName), &newVal))
    nVal = ConvertVarToInt(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetInt : GetField(FldName) returned
    error.");
    #endif
    return nVal;
    }


    int CADOTierX1::RS_GetInt(int nFieldIndex)
    {
    CString str;

    _variant_t newVal;
    int nVal = 0;

    if (nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    nVal = ConvertVarToInt(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetInt : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetInt : Field Index out of range");
    #endif
    return nVal;
    }


    int CADOTierX1::ConvertVarToInt(VARIANT &var)
    {
    int nVal = 0;
    switch (var.vt)
    {
    case VT_I2:
    nVal = (int) var.iVal;
    break;
    case VT_I4:
    nVal = var.lVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetInt : " + GetVariantString
    (var.vt) + " Not Integer Type");
    #endif
    break;
    }
    return nVal;
    }


    float CADOTierX1::RS_GetFloat(LPCTSTR sFldName)
    {
    _variant_t newVal;
    float dVal = 0.;

    if (GetField(_variant_t(sFldName), &newVal))
    dVal = ConvertVarToFloat(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetFloat : GetField(FldName) returned
    error.");
    #endif
    return dVal;
    }


    float CADOTierX1::RS_GetFloat(int nFieldIndex)
    {
    _variant_t newVal;
    float dVal = 0.;

    if (nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    dVal = ConvertVarToFloat(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetFloat : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetFloat : Field Index out of range");
    #endif
    return dVal;
    }


    float CADOTierX1::ConvertVarToFloat(VARIANT &var)
    {
    float dVal = 0;
    switch (var.vt)
    {
    case VT_I2:
    dVal = (float) var.iVal;
    break;
    case VT_I4:
    dVal = (float) var.lVal;
    break;
    case VT_R4:
    dVal = var.fltVal;
    break;
    case VT_R8:
    dVal = (float) var.dblVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetFloat : " + GetVariantString
    (var.vt) + " Not Numeric Type");
    #endif
    break;
    }
    return dVal;
    }


    long CADOTierX1::RS_GetLong(LPCTSTR sFldName)
    {
    _variant_t newVal;
    long nVal = 0;

    if (GetField(_variant_t(sFldName), &newVal))
    nVal = ConvertVarToLong(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetLong : GetField(FldName) returned
    error.");
    #endif
    return nVal;
    }


    long CADOTierX1::RS_GetLong(int nFieldIndex)
    {
    CString str;
    _variant_t newVal;
    long nVal = 0;

    if (nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    nVal = ConvertVarToLong(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetLong : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetLong : Field Index out of range");
    #endif
    return nVal;
    }


    long CADOTierX1::ConvertVarToLong(VARIANT &var)
    {
    long nVal = 0;
    switch (var.vt)
    {
    case VT_I2:
    nVal = (int) var.iVal;
    break;
    case VT_I4:
    nVal = var.lVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetLong : " + GetVariantString (var.vt) +
    " Not Integer Type");
    #endif
    break;
    }
    return nVal;
    }


    bool CADOTierX1::RS_GetBool(LPCTSTR sFldName)
    {
    _variant_t newVal;
    bool bVal = false;

    if (GetField(_variant_t(sFldName), &newVal))
    bVal = ConvertVarToBool(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetBool : GetField(FldName) returned
    error.");
    #endif
    return bVal;
    }


    bool CADOTierX1::RS_GetBool(int nFieldIndex)
    {
    CString str;
    _variant_t newVal;
    bool bVal = 0;

    if (nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    bVal = ConvertVarToBool(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetBool : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetBool : Field Index out of range");
    #endif
    return bVal;
    }


    bool CADOTierX1::ConvertVarToBool(VARIANT &var)
    {
    bool bVal = false;
    switch (var.vt)
    {
    case VT_BOOL:
    bVal = (var.boolVal == 0) ? false : true;
    break;
    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetBool : " + GetVariantString
    (var.vt) + " Not Logical Type");
    #endif
    break;
    }
    return bVal;
    }


    void CADOTierX1::LogItem (const CString &s)
    {
    TRACE(s + "\r\n");
    }


    static CString sVT[] = {

    _T("VT_EMPTY") ,// 0, [V] nothing
    _T("VT_NULL") ,// 1, [V] SQL style Null
    _T("VT_I2") ,// 2, [V] 2 byte signed int
    _T("VT_I4") ,// 3, [V] 4 byte signed int
    _T("VT_R4") ,// 4, [V] 4 byte real
    _T("VT_R8") ,// 5, [V] 8 byte real
    _T("VT_CY") ,// 6, [V] currency
    _T("VT_DATE") ,// 7, [V] date
    _T("VT_BSTR") ,// 8, [V] OLE Automation string
    _T("VT_DISPATCH") ,// 9, [V] IDispatch *
    _T("VT_ERROR") ,// 10, [V] SCODE
    _T("VT_BOOL") ,// 11, [V] True=-1, False=0
    _T("VT_VARIANT") ,// 12, [V] VARIANT *
    _T("VT_UNKNOWN") ,// 13, [V] IUnknown *
    _T("VT_DECIMAL") ,// 14, [V] 16 byte fixed point
    _T("VT_I1") ,// 16, [V] signed char
    _T("VT_UI1") ,// 17, [V] unsigned char
    _T("VT_UI2") ,// 18, [V] unsigned short
    _T("VT_UI4") ,// 19, [V] unsigned short
    _T("VT_I8") ,// 20, [T] signed 64-bit int
    _T("VT_UI8") ,// 21, [T] unsigned 64-bit int
    _T("VT_INT") ,// 22, [V] signed machine int
    _T("VT_UINT") ,// 23, [V] unsigned machine int
    _T("VT_ARRAY") ,// 0x2000, [V] SAFEARRAY*
    _T("VT_BYREF") ,// 0x4000, [V] void* for local
    use
    _T("none") };


    CString CADOTierX1::GetVariantString (int nId)
    {
    if (nId == 0x2000)
    nId = 24;
    else
    if (nId == 0x4000)
    nId = 25;
    else
    if (!(nId >= 0 && nId <= 23))
    nId = 26;
    return sVT[nId];
    }


    void CADOTierX1::ChangeRS(_RecordsetPtr &rsChange)
    {
    m_recordset = rsChange;
    }

    CString CADOTierX1::GetSrcPath()
    {
    return m_Srcpath;
    }

    BOOL CADOTierX1::DisconnectRecordset(_RecordsetPtr &rsReturn)
    {
    if (m_recordset == NULL || rsReturn != NULL)
    return FALSE;
    HRESULT hr = rsReturn.CreateInstance(__uuidof(Recordset));
    if (SUCCEEDED(hr))
    hr = rsReturn->put_CursorLocation(adUseClient);
    /////////////
    // if (SUCCEEDED(hr))
    // hr = m_recordset->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr =
    m_recordset->put_ActiveConnection(_variant_t((IDispatch
    *)NULL,false));
    /////////////
    if (SUCCEEDED(hr))
    rsReturn = m_recordset;
    m_recordset = 0;
    if (SUCCEEDED(hr))
    hr =
    rsReturn->put_ActiveConnection(_variant_t((IDispatch *)NULL,false));
    // if (SUCCEEDED(hr))
    // hr = rsReturn->put_CursorLocation(adUseClient);
    // ok ...
    if (SUCCEEDED(hr))
    hr = m_recordset.CreateInstance(__uuidof(Recordset));
    if (SUCCEEDED(hr))
    return TRUE;
    // bad ...
    if (rsReturn)
    rsReturn.Release();
    if (m_recordset == NULL)
    m_recordset.CreateInstance(__uuidof(Recordset));

    return FALSE;
    }

    Ado RecordSet helper class (used by com server internally)
    ============================================================
    // ADORsX1.h: interface for the CADORsX1 class.
    //
    //////////////////////////////////////////////////////////////////////

    #if
    !defined(AFX_ADORSX1_H__5D210159_CADE_4352_8BC7_A904BA94DE31__INCLUDED_)
    #define AFX_ADORSX1_H__5D210159_CADE_4352_8BC7_A904BA94DE31__INCLUDED_

    #if _MSC_VER > 1000
    #pragma once
    #endif // _MSC_VER > 1000

    typedef struct tagDATAMAP_REC
    {
    tagDATAMAP_REC() { nCurIndex = -1; nNextIndex = -1;
    sMapData_FldName = _T(""); nMapData_Rec = -1; }

    // Define field information ...
    BOOL PutRsFieldValues(Field **field, long recnum)
    {
    // field[0]->put_Value( _variant_t((long)recnum) );
    field[0]->put_Value( _variant_t(sMapData_FldName) );
    field[1]->put_Value( _variant_t((long)nCurIndex) );
    field[2]->put_Value( _variant_t((long)nMapData_Rec) );
    field[3]->put_Value( _variant_t((long)nNextIndex) );
    return TRUE;
    }
    CString sMapData_FldName;
    int nCurIndex;
    int nMapData_Rec;
    int nNextIndex;

    } DATAMAP_REC;

    typedef struct tagMAPDATA_REC
    {
    tagMAPDATA_REC () {nMapNdx = -1; nDRec = -1;}
    tagMAPDATA_REC (int nMdx, int nDr, _variant_t &vtData)
    {nMapNdx = nMdx; nDRec = nDr; vtStoreData = vtData;}
    struct tagMAPDATA_REC& operator= (const struct tagMAPDATA_REC
    &sdRec)
    {nMapNdx = sdRec.nMapNdx; nDRec = sdRec.nDRec; vtStoreData =
    sdRec.vtStoreData; return *this;}
    int nMapNdx;
    int nDRec;
    _variant_t vtStoreData;
    } MAPDATA_REC;


    #include <vector>
    #include <algorithm>
    using namespace std;


    /////////////////////////////////
    // ADO Recordset helper class
    class CADORsX1
    {
    public:
    CADORsX1(_RecordsetPtr &rs);
    CADORsX1();
    virtual ~CADORsX1();

    public:
    BOOL DIST_ChangeMapUnfiltered(_RecordsetPtr &rsStore1, CString
    sFldStore1, _RecordsetPtr &rsStore2, CString sFldStore2);
    BOOL DIST_ChangeMapFiltered(_RecordsetPtr &rsStore1, CString
    sFldStore1, _RecordsetPtr &rsStore2, CString sFldStore2, CString
    sFiltFldStore2);
    BOOL DIST_CreateMap(vector <DATAMAP_REC> &vaDataMap, int
    *pmaxsize);

    // BOOL DIST_FilterRecords(CString Sortflds, CString Flagfld);
    BOOL DIST_FilterRecords(CString sSortflds, CString sFlagfld, CString
    sWhere = _T(""));

    BOOL CreateTable(CString sTable, CString sDatabase, CString
    sUser, CString sPassword, long nOptions);
    BOOL PutSort(LPCTSTR sSort);
    BOOL PutFilter(LPCTSTR sFltr);
    BOOL PutFilter(VARIANT sCriteria);
    BOOL AddNew();
    BOOL GetBookmark(VARIANT *vbookMark);
    BOOL GetBookmark(_RecordsetPtr &rset, VARIANT *vbookMark);
    BOOL PutBookmark(VARIANT vbookMark);
    BOOL PutBookmark(_RecordsetPtr &rset, VARIANT vbookMark);

    BOOL RS_PutLong(LPCTSTR sFldName, long lval);
    BOOL RS_PutLong(long idx, long lval);
    BOOL RS_PutBool(LPCTSTR sFldName, bool bval);
    BOOL RS_PutBool(long idx, bool bval);
    BOOL RS_PutDouble(LPCTSTR sFldName, double dval);
    BOOL RS_PutDouble(long idx, double dval);
    BOOL RS_PutFloat(LPCTSTR sFldName, double dval);
    BOOL RS_PutFloat(long idx, double dval);
    BOOL RS_PutInt(LPCTSTR sFldName, int ival);
    BOOL RS_PutInt(long idx, int ival);
    BOOL RS_PutString(LPCTSTR sFldName, LPCTSTR sval);
    BOOL RS_PutString(long idx, LPCTSTR sval);
    BOOL RemoveUnfilteredRecords();
    BOOL RemoveUnfilteredRecords(_RecordsetPtr &rsnew);
    BOOL AppendField(CString sFldName, DataTypeEnum FldType, long
    FldSize, FieldAttributeEnum FldAttr);
    void SetSrc(_RecordsetPtr &rs);
    long m_FldCount;
    BOOL Update();
    BOOL Delete();
    BOOL GetField(_RecordsetPtr &rset, VARIANT idx, VARIANT
    *newVal);
    BOOL GetField(VARIANT idx, VARIANT *newVal);
    BOOL PutField(VARIANT idx, VARIANT newVal);
    BOOL GetFieldCount(long * newVal);
    BOOL First();
    BOOL Next();
    BOOL Last();
    BOOL Prev();
    BOOL IsEOF();
    BOOL IsEOF(_RecordsetPtr &rset);
    BOOL IsBOF();
    long GetRecordCount();
    BOOL Empty();
    CString RS_GetString(LPCTSTR sFldName);
    CString RS_GetString(int nFieldIndex);
    CString ConvertVarToStr(VARIANT &var);
    double RS_GetDouble(LPCTSTR sFldName);
    double RS_GetDouble(int nFieldIndex);
    double ConvertVarToDouble(VARIANT &var);
    int RS_GetInt(LPCTSTR sFldName);
    int RS_GetInt(int nFieldIndex);
    int ConvertVarToInt(VARIANT &var);
    float RS_GetFloat(LPCTSTR sFldName);
    float RS_GetFloat(int nFieldIndex);
    float ConvertVarToFloat(VARIANT &var);
    long RS_GetLong(LPCTSTR sFldName);
    long RS_GetLong(int nFieldIndex);
    long ConvertVarToLong(VARIANT &var);
    bool RS_GetBool(LPCTSTR sFldName);
    bool RS_GetBool(int nFieldIndex);
    bool ConvertVarToBool(VARIANT &var);
    void LogItem (const CString &s);
    CString GetVariantString (int nId);
    int ParseStringCSV(vector <CString> &vaUNS, CString sUnits,
    BOOL bMakeUpper);

    private:
    _RecordsetPtr m_RSet;
    _RecordsetPtr &m_recordset;

    protected:
    };

    #endif //
    !defined(AFX_ADORSX1_H__5D210159_CADE_4352_8BC7_A904BA94DE31__INCLUDED_)

    // ADORsX1.cpp: implementation of the CADORsX1 class.
    //
    //////////////////////////////////////////////////////////////////////

    #include "stdafx.h"
    #include "ADORsX1.h"
    #include "ADOTierX1.h"

    #ifdef _DEBUG
    #undef THIS_FILE
    static char THIS_FILE[]=__FILE__;
    #define new DEBUG_NEW
    #endif

    // These are used to Map the current recordset
    // sort order to the Bookmarks used for filter.
    //
    typedef struct tagCURREC_BKMARK
    {
    int nCurrec;
    _variant_t vbmrk;
    } CURREC_BKMARK;

    typedef struct tagCURREC_BKMARK_DBL
    {
    int nCurrec;
    int nbmrk;
    } CURREC_BKMARK_DBL;

    int SortBookmarkByFilterOrder (const CURREC_BKMARK &arec, const
    CURREC_BKMARK &brec)
    {
    if (arec.nCurrec < brec.nCurrec)
    return true;
    return false;
    }

    int SortByBookmark (const CURREC_BKMARK_DBL &arec, const
    CURREC_BKMARK_DBL &brec)
    {
    if (arec.nbmrk < brec.nbmrk)
    return true;
    return false;
    }



    //////////////////////////////////////////////////////////////////////
    // Construction/Destruction
    //////////////////////////////////////////////////////////////////////

    void CADORsX1::SetSrc(_RecordsetPtr &rs)
    {
    if (rs)
    {
    m_recordset = rs;
    GetFieldCount(&m_FldCount);
    }
    }

    CADORsX1::CADORsX1(_RecordsetPtr &rs)
    : m_recordset(rs)
    {
    m_FldCount = 0;
    m_RSet = 0;
    if (m_recordset)
    GetFieldCount(&m_FldCount);
    }

    CADORsX1::CADORsX1()
    : m_recordset(m_RSet)
    {
    m_FldCount = 0;
    m_RSet = 0;
    }

    CADORsX1::~CADORsX1()
    {
    }

    BOOL CADORsX1::Update()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->Update();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::Delete()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->Delete(adAffectCurrent);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::GetField(VARIANT idx, VARIANT *newVal)
    {
    if (m_recordset == NULL)
    return FALSE;
    Fields* fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);

    Field* field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(idx, &field);
    if (SUCCEEDED(hr))
    hr = field->get_Value(newVal);

    if (SUCCEEDED(hr))
    {
    fields->Release();
    field->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::GetField(_RecordsetPtr &rset, VARIANT idx, VARIANT
    *newVal)
    {
    if (rset == NULL)
    return FALSE;
    Fields* fields = 0;
    HRESULT hr = rset->get_Fields(&fields);

    Field* field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(idx, &field);
    if (SUCCEEDED(hr))
    hr = field->get_Value(newVal);

    if (SUCCEEDED(hr))
    {
    fields->Release();
    field->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::putField(VARIANT idx, VARIANT newVal)
    {
    if (m_recordset == NULL)
    return FALSE;
    Fields* fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    Field* field = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(idx, &field);
    if (SUCCEEDED(hr))
    hr = field->put_Value(newVal);

    if (SUCCEEDED(hr))
    {
    fields->Release();
    field->Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::GetFieldCount(long * newVal)
    {
    if (m_recordset == NULL)
    return FALSE;
    Fields* fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    if (SUCCEEDED(hr))
    hr = fields->get_Count(newVal);
    if (SUCCEEDED(hr))
    fields->Release();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::First()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->MoveFirst();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::Next()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->MoveNext();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::Last()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->MoveLast();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::prev()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->MovePrevious();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::IsEOF(_RecordsetPtr &rset)
    {
    if (rset == NULL)
    return FALSE;
    VARIANT_BOOL newVal;
    HRESULT hr = rset->get_EOF(&newVal);
    if (SUCCEEDED(hr) && newVal)
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::IsEOF()
    {
    if (m_recordset == NULL)
    return FALSE;
    VARIANT_BOOL newVal;
    HRESULT hr = m_recordset->get_EOF(&newVal);
    if (SUCCEEDED(hr) && newVal)
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::IsBOF()
    {
    if (m_recordset == NULL)
    return FALSE;
    VARIANT_BOOL newVal;
    HRESULT hr = m_recordset->get_BOF(&newVal);
    if (SUCCEEDED(hr) && newVal)
    return TRUE;
    return FALSE;
    }

    long CADORsX1::GetRecordCount()
    {
    if (m_recordset == NULL)
    return FALSE;
    long pl;
    pl = 0;
    HRESULT hr = m_recordset->get_RecordCount(&pl);
    if (SUCCEEDED(hr))
    return pl;
    return 0L;
    }

    BOOL CADORsX1::Empty()
    {
    if (m_recordset == NULL)
    return FALSE;
    VARIANT_BOOL bEmpty;
    HRESULT hr = m_recordset->get_EOF(&bEmpty);
    if (SUCCEEDED(hr) && &bEmpty)
    hr = m_recordset->get_BOF(&bEmpty);
    if (SUCCEEDED(hr) && bEmpty)
    return TRUE;
    return FALSE;
    }


    /////////////// Utilities: Get DAO Field ///////////////////
    /*
    enum VARENUM
    { VT_EMPTY = 0,
    VT_NULL = 1,
    VT_I2 = 2,
    VT_I4 = 3,
    VT_R4 = 4,
    VT_R8 = 5,
    VT_CY = 6,
    VT_DATE = 7,
    VT_BSTR = 8,
    VT_DISPATCH = 9,
    VT_ERROR = 10,
    VT_BOOL = 11,
    VT_VARIANT = 12,
    VT_UNKNOWN = 13,
    VT_DECIMAL = 14,
    VT_I1 = 16,
    VT_UI1 = 17,
    VT_UI2 = 18,
    VT_UI4 = 19,
    VT_I8 = 20,
    VT_UI8 = 21,
    VT_INT = 22,
    VT_UINT = 23,
    VT_VOID = 24,
    VT_HRESULT = 25,
    VT_PTR = 26,
    VT_SAFEARRAY = 27,
    VT_CARRAY = 28,
    VT_USERDEFINED = 29,
    VT_LPSTR = 30,
    VT_LPWSTR = 31,
    VT_RECORD = 36,
    VT_FILETIME = 64,
    VT_BLOB = 65,
    VT_STREAM = 66,
    VT_STORAGE = 67,
    VT_STREAMED_OBJECT = 68,
    VT_STORED_OBJECT = 69,
    VT_BLOB_OBJECT = 70,
    VT_CF = 71,
    VT_CLSID = 72,
    VT_BSTR_BLOB = 0xfff,
    VT_VECTOR = 0x1000,
    VT_ARRAY = 0x2000,
    VT_BYREF = 0x4000,
    VT_RESERVED = 0x8000,
    VT_ILLEGAL = 0xffff,
    VT_ILLEGALMASKED = 0xfff,
    VT_TYPEMASK = 0xfff
    };
    */

    CString CADORsX1::RS_GetString(LPCTSTR sFldName)
    {
    if (m_recordset == NULL)
    return _T("");
    _variant_t newVal;
    CString stmp = _T("");

    if (GetField(_variant_t(sFldName), &newVal))
    stmp = ConvertVarToStr(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetString : GetField(FldName) returned
    error.");
    #endif
    return stmp;
    }


    CString CADORsX1::RS_GetString(int nFieldIndex)
    {
    if (m_recordset == NULL)
    return _T("");
    CString str;
    _variant_t newVal;
    CString stmp = _T("");

    if (1)//nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    stmp = ConvertVarToStr(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetString : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetString : Field Index out of range");
    #endif
    return stmp;
    }


    CString CADORsX1::ConvertVarToStr(VARIANT &var)
    {
    USES_CONVERSION;
    if (m_recordset == NULL)
    return _T("");
    // Check that newVal is of type BSTR
    // MessageBox(0,(const char*)_bstr_t(tp),"Message",0);
    CString str = _T("");

    switch (var.vt)
    {
    case VT_BSTR:
    str = OLE2CT(var.bstrVal);
    ::SysFreeString(var.bstrVal);
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetString : " + GetVariantString
    (var.vt) + " Not String Type");
    #endif
    break;
    }
    return str;
    }


    double CADORsX1::RS_GetDouble(LPCTSTR sFldName)
    {
    if (m_recordset == NULL)
    return 0.;
    _variant_t newVal;
    double dVal = 0.;

    if (GetField(_variant_t(sFldName), &newVal))
    dVal = ConvertVarToDouble(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetDouble : GetField(FldName) returned
    error.");
    #endif
    return dVal;
    }


    double CADORsX1::RS_GetDouble(int nFieldIndex)
    {
    if (m_recordset == NULL)
    return 0.;
    _variant_t newVal;
    double dVal = 0.;

    if (1)//nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    dVal = ConvertVarToDouble(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetDouble : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetDouble : Field Index out of range");
    #endif
    return dVal;
    }


    double CADORsX1::ConvertVarToDouble(VARIANT &var)
    {
    if (m_recordset == NULL)
    return FALSE;
    double dVal = 0.;
    switch (var.vt)
    {
    case VT_I2:
    dVal = var.iVal;
    break;
    case VT_I4:
    dVal = var.lVal;
    break;
    case VT_R4:
    dVal = (double) var.fltVal;
    break;
    case VT_R8:
    dVal = var.dblVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetDouble : " + GetVariantString
    (var.vt) + " Not Numeric Type");
    #endif
    break;
    }
    return dVal;
    }


    int CADORsX1::RS_GetInt(LPCTSTR sFldName)
    {
    if (m_recordset == NULL)
    return FALSE;
    _variant_t newVal;
    int nVal = 0;

    if (GetField(_variant_t(sFldName), &newVal))
    nVal = ConvertVarToInt(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetInt : GetField(FldName) returned
    error.");
    #endif
    return nVal;
    }


    int CADORsX1::RS_GetInt(int nFieldIndex)
    {
    if (m_recordset == NULL)
    return FALSE;
    CString str;

    _variant_t newVal;
    int nVal = 0;

    if (1)//nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    nVal = ConvertVarToInt(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetInt : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetInt : Field Index out of range");
    #endif
    return nVal;
    }


    int CADORsX1::ConvertVarToInt(VARIANT &var)
    {
    if (m_recordset == NULL)
    return FALSE;
    int nVal = 0;
    switch (var.vt)
    {
    case VT_I2:
    nVal = (int) var.iVal;
    break;
    case VT_I4:
    nVal = var.lVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetInt : " + GetVariantString
    (var.vt) + " Not Integer Type");
    #endif
    break;
    }
    return nVal;
    }


    float CADORsX1::RS_GetFloat(LPCTSTR sFldName)
    {
    if (m_recordset == NULL)
    return FALSE;
    _variant_t newVal;
    float dVal = 0.;

    if (GetField(_variant_t(sFldName), &newVal))
    dVal = ConvertVarToFloat(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetFloat : GetField(FldName) returned
    error.");
    #endif
    return dVal;
    }


    float CADORsX1::RS_GetFloat(int nFieldIndex)
    {
    if (m_recordset == NULL)
    return FALSE;
    _variant_t newVal;
    float dVal = 0.;

    if (1)//nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    dVal = ConvertVarToFloat(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetFloat : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetFloat : Field Index out of range");
    #endif
    return dVal;
    }


    float CADORsX1::ConvertVarToFloat(VARIANT &var)
    {
    if (m_recordset == NULL)
    return FALSE;
    float dVal = 0;
    switch (var.vt)
    {
    case VT_I2:
    dVal = (float) var.iVal;
    break;
    case VT_I4:
    dVal = (float) var.lVal;
    break;
    case VT_R4:
    dVal = var.fltVal;
    break;
    case VT_R8:
    dVal = (float) var.dblVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetFloat : " + GetVariantString
    (var.vt) + " Not Numeric Type");
    #endif
    break;
    }
    return dVal;
    }


    long CADORsX1::RS_GetLong(LPCTSTR sFldName)
    {
    if (m_recordset == NULL)
    return FALSE;
    _variant_t newVal;
    long nVal = 0;

    if (GetField(_variant_t(sFldName), &newVal))
    nVal = ConvertVarToLong(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetLong : GetField(FldName) returned
    error.");
    #endif
    return nVal;
    }


    long CADORsX1::RS_GetLong(int nFieldIndex)
    {
    if (m_recordset == NULL)
    return FALSE;
    CString str;
    _variant_t newVal;
    long nVal = 0;

    if (1)//nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    nVal = ConvertVarToLong(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetLong : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetLong : Field Index out of range");
    #endif
    return nVal;
    }


    long CADORsX1::ConvertVarToLong(VARIANT &var)
    {
    if (m_recordset == NULL)
    return FALSE;
    long nVal = 0;
    switch (var.vt)
    {
    case VT_I2:
    nVal = (int) var.iVal;
    break;
    case VT_I4:
    nVal = var.lVal;
    break;

    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetLong : " + GetVariantString (var.vt) +
    " Not Integer Type");
    #endif
    break;
    }
    return nVal;
    }


    bool CADORsX1::RS_GetBool(LPCTSTR sFldName)
    {
    if (m_recordset == NULL)
    return FALSE;
    _variant_t newVal;
    bool bVal = false;

    if (GetField(_variant_t(sFldName), &newVal))
    bVal = ConvertVarToBool(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetBool : GetField(FldName) returned
    error.");
    #endif
    return bVal;
    }


    bool CADORsX1::RS_GetBool(int nFieldIndex)
    {
    if (m_recordset == NULL)
    return FALSE;
    CString str;
    _variant_t newVal;
    bool bVal = 0;

    if (1)//nFieldIndex >= 0 && nFieldIndex <= m_FldCount)
    {
    if (GetField(_variant_t((long)nFieldIndex), &newVal))
    bVal = ConvertVarToBool(newVal);
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetBool : GetField(FldIndex)
    returned error.");
    #endif
    }
    #ifdef _VTDEBUG
    else
    LogItem("RS_GetBool : Field Index out of range");
    #endif
    return bVal;
    }


    bool CADORsX1::ConvertVarToBool(VARIANT &var)
    {
    if (m_recordset == NULL)
    return FALSE;
    bool bVal = false;
    switch (var.vt)
    {
    case VT_BOOL:
    bVal = (var.boolVal == 0) ? false : true;
    break;
    default:
    #ifdef _VTDEBUG
    LogItem("RS_GetBool : " + GetVariantString
    (var.vt) + " Not Logical Type");
    #endif
    break;
    }
    return bVal;
    }


    void CADORsX1::LogItem (const CString &s)
    {
    TRACE(s + "\r\n");
    }


    // Strings used for error log ...
    static CString sVT[] = {
    _T("VT_EMPTY") ,// 0, [V] nothing
    _T("VT_NULL") ,// 1, [V] SQL style Null
    _T("VT_I2") ,// 2, [V] 2 byte signed int
    _T("VT_I4") ,// 3, [V] 4 byte signed int
    _T("VT_R4") ,// 4, [V] 4 byte real
    _T("VT_R8") ,// 5, [V] 8 byte real
    _T("VT_CY") ,// 6, [V] currency
    _T("VT_DATE") ,// 7, [V] date
    _T("VT_BSTR") ,// 8, [V] OLE Automation string
    _T("VT_DISPATCH") ,// 9, [V] IDispatch *
    _T("VT_ERROR") ,// 10, [V] SCODE
    _T("VT_BOOL") ,// 11, [V] True=-1, False=0
    _T("VT_VARIANT") ,// 12, [V] VARIANT *
    _T("VT_UNKNOWN") ,// 13, [V] IUnknown *
    _T("VT_DECIMAL") ,// 14, [V] 16 byte fixed point
    _T("VT_I1") ,// 16, [V] signed char
    _T("VT_UI1") ,// 17, [V] unsigned char
    _T("VT_UI2") ,// 18, [V] unsigned short
    _T("VT_UI4") ,// 19, [V] unsigned short
    _T("VT_I8") ,// 20, [T] signed 64-bit int
    _T("VT_UI8") ,// 21, [T] unsigned 64-bit int
    _T("VT_INT") ,// 22, [V] signed machine int
    _T("VT_UINT") ,// 23, [V] unsigned machine int
    _T("VT_ARRAY") ,// 0x2000, [V] SAFEARRAY*
    _T("VT_BYREF") ,// 0x4000, [V] void* for local
    use
    _T("none") };


    CString CADORsX1::GetVariantString (int nId)
    {
    if (m_recordset == NULL)
    return _T("");
    if (nId == 0x2000)
    nId = 24;
    else
    if (nId == 0x4000)
    nId = 25;
    else
    if (!(nId >= 0 && nId <= 23))
    nId = 26;
    return sVT[nId];
    }


    BOOL CADORsX1::AppendField(CString FldName, DataTypeEnum FldType, long
    FldSize, FieldAttributeEnum FldAttr)
    {
    if (m_recordset == NULL)
    return FALSE;
    // Append new field ...
    Fields *fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    if (SUCCEEDED(hr))
    hr = fields->Append(_bstr_t(FldName), FldType,
    FldSize, FldAttr);
    if (fields != NULL)
    fields->Release();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }


    BOOL CADORsX1::RemoveUnfilteredRecords()
    {
    USES_CONVERSION;
    if (m_recordset == NULL)
    return FALSE;
    _RecordsetPtr rsnew;
    if (RemoveUnfilteredRecords(rsnew))
    {
    m_recordset->Close();
    m_recordset.Release();
    m_recordset = rsnew;
    return TRUE;
    }
    return FALSE;
    }


    // RemoveUnfilteredRecords(_RecordsetPtr &rsnew)
    // Persist Bookmarked Filters and String Filters
    BOOL CADORsX1::RemoveUnfilteredRecords(_RecordsetPtr &rsnew)
    {
    USES_CONVERSION;
    if (m_recordset == NULL || rsnew != NULL)
    return FALSE;
    HRESULT hr = 0;

    int ntot_recs = GetRecordCount();
    if (ntot_recs <= 0)
    return FALSE;

    // Find out if there is a Bookmark filter on the recordset...
    BOOL bookm_filter = FALSE;
    _variant_t vntFltOld;
    m_recordset->get_Filter(&vntFltOld);
    if (!(vntFltOld.vt & VT_EMPTY) && !(vntFltOld.vt & VT_BSTR))
    bookm_filter = TRUE;

    // The BookMark Array, used instead of a filter Criteria (Fltr
    field) ...
    vector <CURREC_BKMARK_DBL> vbArray;
    BSTR SaveSort;

    if (bookm_filter)
    {
    Last();
    if (!IsBOF())
    {
    int cnt = 1;
    First();
    while (!IsEOF())
    {
    _variant_t vbook;
    GetBookmark(&vbook);
    CURREC_BKMARK_DBL bmrec;
    bmrec.nCurrec = cnt;
    bmrec.nbmrk =
    (int)ConvertVarToDouble(vbook);
    vbArray.push_back(bmrec);
    Next();
    cnt++;
    }
    First();
    m_recordset->get_Sort(&SaveSort);
    PutSort(_T(""));
    PutFilter(_T(""));
    }
    }

    // Stream out to disk ...
    hr = rsnew.CreateInstance(__uuidof(Recordset));
    if (SUCCEEDED(hr))
    hr = rsnew->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr = rsnew->put_ActiveConnection(_variant_t((IDispatch
    *)NULL, false));
    _StreamPtr tmpStream = 0;
    if (SUCCEEDED(hr))
    hr = tmpStream.CreateInstance(__uuidof(Stream));
    if (SUCCEEDED(hr))
    hr = tmpStream->put_Type(adTypeBinary);
    if (SUCCEEDED(hr))
    hr =
    m_recordset->Save(_variant_t(tmpStream.GetInterfacePtr()),
    adPersistADTG);
    if (SUCCEEDED(hr))
    tmpStream->put_Position(0);
    if (SUCCEEDED(hr))
    hr =
    rsnew->Open(_variant_t(tmpStream.GetInterfacePtr()),_variant_t((IDispatch
    *)NULL,false),adOpenUnspecified,adLockUnspecified,adCmdFile);//adConnectUnspecified);
    if (tmpStream)
    {
    tmpStream->Close();
    tmpStream.Release();
    }

    // Handle Bookmark Filter type ...
    if (bookm_filter && vbArray.size())
    {
    // At this point the Filter is gone.
    // Sort the saved Bookmark MAP by bookmark
    sort (vbArray.begin(), vbArray.end(), SortByBookmark);

    CADORsX1 *Xrs_rsnew = new CADORsX1( rsnew );
    int ary_pos = 0;
    int arycnt = vbArray.size();
    Xrs_rsnew->Last();

    if (!Xrs_rsnew->IsBOF())
    {
    Xrs_rsnew->First();
    while (!Xrs_rsnew->IsEOF())
    {
    _variant_t vBookmark;
    Xrs_rsnew->GetBookmark(&vBookmark);
    int cur_arraybook =
    vbArray[ary_pos].nbmrk;
    int cur_rsbook =
    (int)Xrs_rsnew->ConvertVarToDouble(vBookmark);

    if (cur_arraybook == cur_rsbook)
    ary_pos++;
    else
    Xrs_rsnew->Delete();
    Xrs_rsnew->Next();
    }
    }
    Xrs_rsnew->First();

    // Put the original sort on original and rsnew ...
    PutSort(OLE2CT(SaveSort));
    Xrs_rsnew->PutSort(OLE2CT(SaveSort));

    if (Xrs_rsnew)
    delete Xrs_rsnew;
    if (vbArray.size() > 0)
    vbArray.erase(vbArray.begin(), vbArray.end());
    }
    if (SUCCEEDED(hr))
    return TRUE;
    if (rsnew)
    {
    rsnew->Release();
    rsnew = 0;
    }
    return FALSE;
    }


    /* old (original)
    BOOL CADORsX1::RemoveUnfilteredRecords(_RecordsetPtr &rsnew)
    {
    if (m_recordset == NULL || rsnew != NULL)
    return FALSE;
    HRESULT hr = rsnew.CreateInstance(__uuidof(Recordset));
    if (SUCCEEDED(hr))
    hr = rsnew->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr = rsnew->put_ActiveConnection(_variant_t((IDispatch
    *)NULL, false));
    _StreamPtr tmpStream = 0;
    if (SUCCEEDED(hr))
    hr = tmpStream.CreateInstance(__uuidof(Stream));
    if (SUCCEEDED(hr))
    hr = tmpStream->put_Type(adTypeBinary);
    if (SUCCEEDED(hr))
    hr =
    m_recordset->Save(_variant_t(tmpStream.GetInterfacePtr()),
    adPersistADTG);
    if (SUCCEEDED(hr))
    tmpStream->put_Position(0);
    if (SUCCEEDED(hr))
    hr =
    rsnew->Open(_variant_t(tmpStream.GetInterfacePtr()),_variant_t((IDispatch
    *)NULL,false),adOpenUnspecified,adLockUnspecified,adCmdFile);//adConnectUnspecified);
    if (tmpStream)
    {
    tmpStream->Close();
    tmpStream.Release();
    }
    if (SUCCEEDED(hr))
    return TRUE;
    if (rsnew)
    {
    rsnew->Release();
    rsnew = 0;
    }
    return FALSE;
    }
    */

    BOOL CADORsX1::RS_PutLong(LPCTSTR sFldName, long lval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t(sFldName), _variant_t((long)lval));
    }

    BOOL CADORsX1::RS_PutLong(long idx, long lval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t((long)idx),
    _variant_t((long)lval));
    }

    BOOL CADORsX1::RS_PutBool(LPCTSTR sFldName, bool bval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t(sFldName), _variant_t(bval));
    }

    BOOL CADORsX1::RS_PutBool(long idx, bool bval)
    {
    if (m_recordset == NULL)
    return FALSE;
    CString stf;
    stf = bval ? _T("True") : _T("False");
    return PutField(_variant_t((long) idx), _variant_t(bval));
    }

    BOOL CADORsX1::RS_PutDouble(LPCTSTR sFldName, double dval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t(sFldName), _variant_t(dval));
    }

    BOOL CADORsX1::RS_PutDouble(long idx, double dval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t((long) idx), _variant_t(dval));
    }

    BOOL CADORsX1::RS_PutFloat(LPCTSTR sFldName, double dval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t(sFldName), _variant_t(dval));
    }

    BOOL CADORsX1::RS_PutFloat(long idx, double dval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t((long) idx), _variant_t(dval));
    }

    BOOL CADORsX1::RS_PutInt(LPCTSTR sFldName, int ival)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t(sFldName), _variant_t((long)ival));
    }

    BOOL CADORsX1::RS_PutInt(long idx, int ival)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t((long) idx),
    _variant_t((long)ival));
    }

    BOOL CADORsX1::RS_PutString(LPCTSTR sFldName, LPCTSTR sval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t(sFldName), _variant_t(sval));
    }

    BOOL CADORsX1::RS_PutString(long idx, LPCTSTR sval)
    {
    if (m_recordset == NULL)
    return FALSE;
    return PutField(_variant_t((long) idx), _variant_t(sval));
    }


    BOOL CADORsX1::AddNew()
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->AddNew();
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::putFilter(LPCTSTR sFltr)
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->put_Filter(_variant_t(sFltr));
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::putFilter(VARIANT sCriteria)
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->put_Filter(sCriteria);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::putSort(LPCTSTR sSort)
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->put_Sort(_bstr_t(sSort));
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::GetBookmark(_RecordsetPtr &rset, VARIANT *vbookMark)
    {
    if (rset == NULL)
    return FALSE;
    HRESULT hr = rset->get_Bookmark(vbookMark);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::GetBookmark(VARIANT *vbookMark)
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->get_Bookmark(vbookMark);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::putBookmark(VARIANT vbookMark)
    {
    if (m_recordset == NULL)
    return FALSE;
    HRESULT hr = m_recordset->put_Bookmark(vbookMark);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    BOOL CADORsX1::putBookmark(_RecordsetPtr &rset, VARIANT vbookMark)
    {
    if (rset == NULL)
    return FALSE;
    HRESULT hr = rset->put_Bookmark(vbookMark);
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }

    // Create a Table from this objects recordset
    BOOL CADORsX1::CreateTable(CString sTable, CString sSource, CString
    sUser, CString sPassword, long nOptions)
    {
    USES_CONVERSION;

    if (m_recordset == NULL)
    return FALSE;
    _CommandPtr tmpCmd = NULL;
    _RecordsetPtr tmpRS = NULL;
    _ConnectionPtr tmpCon = NULL;

    HRESULT hr = tmpCon.CreateInstance("ADODB.Connection");
    if (SUCCEEDED(hr))
    {
    hr = tmpCon->put_CursorLocation(adUseClient);
    if (SUCCEEDED(hr))
    hr = tmpCon->Open(_bstr_t(sSource),
    _bstr_t(sUser), bstr_t(sPassword), nOptions);
    }
    if (SUCCEEDED(hr))
    hr = tmpCmd.CreateInstance(__uuidof(Command));
    if (SUCCEEDED(hr))
    hr = tmpCmd->putref_ActiveConnection(tmpCon);
    if (SUCCEEDED(hr))
    hr = tmpRS.CreateInstance(__uuidof(Recordset));

    if (FAILED(hr))
    {
    if (tmpCon)
    tmpCon->Close();
    tmpCmd = 0;
    tmpRS = 0;
    tmpCon = 0;
    return FALSE;
    }

    // Gather field information ...

    //////////// get recordset state, if closed, open it /////////
    First();
    long numflds = 0;
    Fields *fields = 0;
    hr = m_recordset->get_Fields(&fields);
    if (SUCCEEDED(hr))
    fields->get_Count(&numflds);
    if (numflds > 0)
    {
    // Get Field pointers ...
    Field **field = new (Field(*[numflds]));
    for (int i = 0; i < numflds; i++)
    {
    field = 0;
    if (SUCCEEDED(hr))
    hr =
    fields->get_Item(_variant_t((long)i), &field);
    }
    //FLDINFO fld_rec;
    if (SUCCEEDED(hr))
    {
    CString sCreateStr = _T("DROP TABLE ") +
    sTable + _T(";");
    _Recordset* prec = 0;
    hr = tmpCon->Execute(_bstr_t(sCreateStr),
    NULL, adCmdText, &prec);
    if (SUCCEEDED(hr))
    prec->Release();
    sCreateStr = _T("CREATE TABLE ") + sTable +
    _T(";");
    prec = 0;
    hr = tmpCon->Execute(_bstr_t(sCreateStr),
    NULL, adCmdText, &prec);
    if (SUCCEEDED(hr))
    prec->Release();

    if (SUCCEEDED(hr))
    {
    for (i = 0; i < numflds; i++)
    {
    //// Add one column at a time
    ////
    BSTR bsfldName;

    field[i]->get_Name(&bsfldName);
    DataTypeEnum efldType;
    field[i]->get_Type(&efldType);
    long nfldSize = 0;

    field[i]->get_DefinedSize(&nfldSize);

    CString sFldStr = _T("[") +
    CString(OLE2CT(bsfldName)) + _T("] ");
    CString sadd = _T("");
    switch (efldType)
    {
    case adDouble:
    sadd =
    _T("DOUBLE");
    break;
    case adInteger:
    sadd =
    _T("INTEGER");
    break;
    case adBoolean:
    sadd =
    _T("LOGICAL");
    break;
    case adVarChar:
    default:

    sadd.Format(_T("VARCHAR(%d)"), nfldSize);
    break;
    }
    sFldStr += sadd;
    sCreateStr = _T("ALTER TABLE
    ") + sTable + _T(" ADD ") + sFldStr + _T(";");
    prec = 0;
    hr =
    tmpCon->Execute(_bstr_t(sCreateStr), NULL, adCmdText +
    adExecuteNoRecords, &prec);
    // if (SUCCEEDED(hr))
    // prec->Release();
    }
    }
    }
    // Insert rows into table ...
    if (SUCCEEDED(hr))
    {
    Last();
    if (!IsBOF())
    {
    First();
    while (!IsEOF())
    {
    CString sRowData = _T("");
    for (i = 0; i < numflds; i++)
    {
    CString sadd = _T("");
    DataTypeEnum efldType;

    field[i]->get_Type(&efldType);

    switch (efldType)
    {
    case
    adDouble:

    sadd.Format(_T("%f"), RS_GetDouble(i));
    break;
    case
    adInteger:

    sadd.Format(_T("%d"), RS_GetInt(i));
    break;
    case
    adBoolean:
    sadd =
    (RS_GetBool(i) ? _T("-1") : _T("0"));//_T("'True'") : _T("'False'"));
    break;
    case
    adVarChar:
    default:

    sadd.Format(_T("'%s'"), RS_GetString(i));
    break;
    }
    sRowData += sadd;
    if (i < (numflds-1))
    sRowData +=
    _T(", ");
    }
    Next();
    _Recordset* prec = 0;
    CString sInsertStr =
    _T("INSERT INTO ") + sTable + _T(" VALUES (");
    sInsertStr += sRowData +
    _T(")");
    hr =
    tmpCon->Execute(_bstr_t(sInsertStr), NULL, adCmdText +
    adExecuteNoRecords, &prec);
    // if (SUCCEEDED(hr))
    // prec->Release();
    }
    }
    }

    // Release references ...
    for (i = 0; i < numflds; i++)
    {
    if (field[i] != 0)
    field[i]->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;
    }
    // finished ?
    if (tmpCon)
    tmpCon->Close();
    tmpCmd = 0;
    tmpRS = 0;
    tmpCon = 0;
    if (SUCCEEDED(hr))
    return TRUE;
    return FALSE;
    }


    ////////////////////////////////////////////////////
    // DISTINCT, no qualifiers, no filters
    // Options: (input can be "")
    // 1. Sorts by Sortfld with/without Flagfld
    // 2. Sorts by All Fields with/without Flagfld
    //
    BOOL CADORsX1::DIST_FilterRecords(CString sSortflds, CString sFlagfld,
    CString sWhere)
    {
    USES_CONVERSION;
    if (m_recordset == NULL)
    return FALSE;

    // Check for fields ...
    long total_flds;
    GetFieldCount(&total_flds);
    if (total_flds <= 0)
    return FALSE;

    // Parse the Sort Field string (don't upper case it) ....
    BOOL binput_flag = sFlagfld.GetLength() > 0 ? TRUE : FALSE;
    BOOL binput_sort = sSortflds.GetLength() > 0 ? TRUE : FALSE;
    vector <CString> vaSortFldName;
    if (binput_sort && !ParseStringCSV(vaSortFldName, sSortflds,
    FALSE))
    return FALSE;

    // Get the Fields ...
    Fields *fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    // Get Field pointers ...
    Field **field = new (Field(*[total_flds]));
    for (int i = 0; i < total_flds; i++)
    {
    field[i] = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(_variant_t((long)i),
    &field[i]);
    }

    // Match up the Field names with their indexes ...
    vector <int> vaSortFldNdx;
    int fld_name_size = binput_sort ? vaSortFldName.size() :
    total_flds;
    CString sSortString = _T("");
    int FlagNdx = -1;

    // For input sort string ...
    if (binput_sort)
    {
    for (i = 0; i < fld_name_size; i++)
    {
    for (int k = 0; k < total_flds; k++)
    {
    BSTR bname;
    field[k]->get_Name(&bname);
    CString fldname = OLE2CT(bname);
    if (binput_flag && fldname ==
    sFlagfld)
    FlagNdx = k;
    else
    if (fldname == vaSortFldName[i])
    {
    if (sSortString.GetLength() >
    0)
    sSortString += _T(",
    ");
    sSortString +=
    vaSortFldName[i];
    vaSortFldNdx.push_back(k);
    if (FlagNdx >= 0)
    break;
    }
    }
    }
    }
    else
    // No input sort string (get all field names) ...
    {
    for (int k = 0; k < total_flds; k++)
    {
    BSTR bname;
    field[k]->get_Name(&bname);
    CString fldname = OLE2CT(bname);
    if (binput_flag && fldname == sFlagfld)
    {
    FlagNdx = k;
    fld_name_size--;
    }
    else
    {
    if (sSortString.GetLength() > 0)
    sSortString += _T(", ");
    sSortString += fldname;
    vaSortFldNdx.push_back(k);
    }
    }
    }

    // Release references ...
    for (i = 0; i < total_flds; i++)
    {
    if (field[i] != 0)
    field[i]->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;

    // Here we have sort string, sort order and flag field indexes
    ....
    int SortNdx_size = vaSortFldNdx.size();
    if (SortNdx_size <= 0 || (binput_flag && FlagNdx < 0) ||
    fld_name_size != SortNdx_size)
    return FALSE;

    // Take off Filter and put on Sort ...
    if (sWhere.GetLength() <= 0)
    {
    PutFilter(_T(""));
    PutSort(sSortString);
    }
    else
    {
    PutSort(sSortString);
    ////////////////////////////// test
    ////////////////////////////
    PutFilter(sWhere);

    ////////////////////////////////////////////////////////////////
    }
    long total_records = GetRecordCount();

    // The BookMark Array, used instead of a Fltr field ...
    vector <CURREC_BKMARK> vbArray;

    int *Distinct = new int[total_records]();
    for (i = 0; i < total_records; i++)
    Distinct[i] = 0;

    for (int ndx = 0; ndx < fld_name_size; ndx++)
    {
    First();
    int currec = 0;
    BOOL bfirst = TRUE;
    _variant_t lastVal;
    BOOL bIsLastValid = TRUE;

    if (bfirst)
    {
    GetField(_variant_t((long)vaSortFldNdx[ndx]),
    &lastVal);
    if (!Distinct[currec])
    {
    Distinct[currec] = 1;
    if (binput_flag)
    RS_PutLong(FlagNdx, 1L);
    else
    {
    _variant_t vtmpbook;
    GetBookmark(&vtmpbook);
    CURREC_BKMARK bkrec;
    bkrec.nCurrec = currec;
    bkrec.vbmrk = vtmpbook;
    vbArray.push_back(bkrec);
    }
    }
    Next();
    bfirst = FALSE;
    bIsLastValid = TRUE;
    currec++;
    }

    while (!IsEOF())
    {
    if (!Distinct[currec])
    {
    // see if lastVal is valid
    if (bIsLastValid == FALSE)
    {
    Prev();

    GetField(_variant_t((long)vaSortFldNdx[ndx]), &lastVal);
    Next();
    bIsLastValid = TRUE;
    }
    _variant_t newVal;

    GetField(_variant_t((long)vaSortFldNdx[ndx]), &newVal);
    int condition = (newVal != lastVal);
    if (condition > 0)
    {
    if (binput_flag)
    RS_PutLong(FlagNdx,
    1L);
    else
    {
    _variant_t vtmpbook;

    GetBookmark(&vtmpbook);
    CURREC_BKMARK bkrec;
    bkrec.nCurrec =
    currec;
    bkrec.vbmrk =
    vtmpbook;

    vbArray.push_back(bkrec);
    }

    // debug ....
    // see what current field and
    record;
    /*
    int cf =
    vaSortFldNdx[ndx];
    int cr = currec;
    int ds =
    Distinct[currec];
    int stophere = 0;
    */
    // end debug ....
    }
    else
    // On first field only set Flag to 0
    if not distinct...
    if (ndx == 0 && binput_flag)
    RS_PutLong(FlagNdx, 0L);

    Distinct[currec] += condition;
    lastVal = newVal;
    }
    else
    {
    // mark lastVal as invalid...
    bIsLastValid = FALSE;
    }
    Next();
    currec++;
    }
    }

    // See what we found ...
    /*
    int dupsfound = 0;
    for (i = 0; i < total_records; i++)
    {
    if (Distinct[i] == 0)
    dupsfound++;
    }
    */

    if (binput_flag == FALSE)
    {
    // Map the Bookmarks to the record order of the
    current sort applied ...
    sort (vbArray.begin(), vbArray.end(),
    SortBookmarkByFilterOrder);

    // Filter via Bookmarked recs
    int vbcnt = vbArray.size();
    _variant_t vBookmark;
    vBookmark.vt = VT_VARIANT|VT_ARRAY;

    SAFEARRAYBOUND rgsabound[1];
    rgsabound[0].lLbound = 0;
    rgsabound[0].cElements = vbcnt; // needed ...

    // Create safearrays to store array of variant
    SAFEARRAY FAR *psa = SafeArrayCreate(VT_VARIANT, 1,
    rgsabound);
    for (i = 0; i < vbcnt; i++)
    SafeArrayPutElement(psa, (long *)&i,
    &vbArray[i].vbmrk);
    vBookmark.parray = psa;
    // Filter the Record with the array of bookmarks ..
    PutFilter(vBookmark);
    }
    else
    {
    // Filter via 'FILT' field
    PutFilter(sFlagfld + _T(" = 1"));
    }

    int nrecs = GetRecordCount();

    // Clean up ...
    vbArray.erase(vbArray.begin(), vbArray.end());
    if (Distinct)
    delete [] Distinct;

    // done
    return TRUE;
    }


    BOOL CADORsX1::DIST_CreateMap(vector <DATAMAP_REC> &vaDataMap, int
    *pmaxsize)
    {
    USES_CONVERSION;
    if (m_recordset == NULL)
    return FALSE;

    // *********************************************
    *pmaxsize = 0;
    long total_flds;
    GetFieldCount(&total_flds);

    // Parse the Sort Field string (don't upper case it) ....
    BSTR criteria;
    m_recordset->get_Sort(&criteria);
    vector <CString> vaSortFldName;
    CString Sortflds = OLE2CT(criteria);
    if (total_flds <= 0 || !ParseStringCSV(vaSortFldName,
    Sortflds, FALSE))
    return FALSE;

    // Get the Fields ...
    Fields *fields = 0;
    HRESULT hr = m_recordset->get_Fields(&fields);
    // Get Field pointers ...
    Field **field = new (Field(*[total_flds]));
    for (int i = 0; i < total_flds; i++)
    {
    field[i] = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(_variant_t((long)i),
    &field[i]);
    }
    // Match up the Field names with their indexes ...
    vector <int> vaSortFldNdx;

    int fld_name_size = vaSortFldName.size();
    for (i = 0; i < fld_name_size; i++)
    {
    for (int k = 0; k < total_flds; k++)
    {
    BSTR bname;
    field[k]->get_Name(&bname);
    CString fldname = OLE2CT(bname);
    if (fldname == vaSortFldName[i])
    {
    int flen = fldname.GetLength();
    if (flen > (*pmaxsize))
    *pmaxsize = flen;
    vaSortFldNdx.push_back(k);
    break;
    }
    }
    }
    // Release references ...
    for (i = 0; i < total_flds; i++)
    {
    if (field[i] != 0)
    field[i]->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;

    // Here we have sort string, sort order and flag field indexes
    ....
    int SortNdx_size = vaSortFldNdx.size();
    if (SortNdx_size <= 0 || fld_name_size != SortNdx_size)
    return FALSE;

    int nrecs = GetRecordCount();

    if (vaDataMap.size() > 0)
    vaDataMap.erase(vaDataMap.begin(), vaDataMap.end());

    // Note - all DATAMAP_REC members initialize to -1
    int nSecondNDX = 0;
    int nCtrlNdx = 0;
    BOOL blastfld = FALSE;

    for (int ndx = 0; ndx < fld_name_size; ndx++)
    {
    if (ndx == (fld_name_size-1))
    blastfld = TRUE;
    First();
    int currec = 0;
    BOOL bfirst = TRUE;
    _variant_t lastVal;
    _variant_t newVal;

    while (!IsEOF())
    {
    if (bfirst)
    {
    DATAMAP_REC dm_rec;

    GetField(_variant_t((long)vaSortFldNdx[ndx]), &lastVal);

    _variant_t vbookmark;
    GetBookmark(&vbookmark);
    dm_rec.nMapData_Rec =
    ((int)ConvertVarToDouble(vbookmark)) - 1;

    dm_rec.nCurIndex = nCtrlNdx;
    dm_rec.sMapData_FldName =
    vaSortFldName[ndx];
    vaDataMap.push_back(dm_rec);
    nCtrlNdx++;
    bfirst = FALSE;

    // Save the 'vector' index into the
    second field (for below)...
    if (ndx == 1)
    nSecondNDX = vaDataMap.size()
    - 1;
    }
    else
    {
    // Look for difference on all but the
    last field. For last, get them all !!

    GetField(_variant_t((long)vaSortFldNdx[ndx]), &newVal);
    if (newVal != lastVal || blastfld)
    {
    DATAMAP_REC dm_rec;

    _variant_t vbookmark;
    GetBookmark(&vbookmark);
    dm_rec.nMapData_Rec =
    ((int)ConvertVarToDouble(vbookmark)) - 1;

    dm_rec.sMapData_FldName =
    vaSortFldName[ndx];
    dm_rec.nCurIndex = nCtrlNdx;
    vaDataMap.push_back(dm_rec);
    nCtrlNdx++;
    lastVal = newVal;
    }
    }
    Next();
    }
    // Put the ender on it ...
    if (vaDataMap.size() > 0)
    {
    DATAMAP_REC dm_rec;
    dm_rec.sMapData_FldName = vaSortFldName[ndx];
    dm_rec.nMapData_Rec = -1;
    dm_rec.nCurIndex = nCtrlNdx;
    vaDataMap.push_back(dm_rec);
    nCtrlNdx++;
    }
    }
    // Traverse the Map vector, fill in the the NextFld numbers
    ....
    if (nSecondNDX > 0)
    {
    int curndx = 0;
    int DataRec = vaDataMap[curndx].nMapData_Rec;
    int nsize = vaDataMap.size();

    while (nSecondNDX < nsize)
    {
    if (DataRec ==
    vaDataMap[nSecondNDX].nMapData_Rec)
    {
    vaDataMap[curndx].nNextIndex =
    vaDataMap[nSecondNDX].nCurIndex;
    curndx++;
    DataRec =
    vaDataMap[curndx].nMapData_Rec;
    }
    nSecondNDX++;
    }
    }
    // done
    return TRUE;
    }


    int SortByMapNdx (const MAPDATA_REC &arec, const MAPDATA_REC &brec)
    {
    if (arec.nMapNdx < brec.nMapNdx)
    return true;
    return false;
    }
    int SortByDRec (const MAPDATA_REC &arec, const MAPDATA_REC &brec)
    {
    if (arec.nDRec < brec.nDRec)
    return true;
    return false;
    }
    int SortByStoreData (const MAPDATA_REC &arec, const MAPDATA_REC &brec)
    {
    USES_CONVERSION;
    switch (arec.vtStoreData.vt)
    {
    case VT_I2:
    if (arec.vtStoreData.iVal <
    brec.vtStoreData.iVal)
    return true;
    break;
    case VT_I4:
    if (arec.vtStoreData.lVal <
    brec.vtStoreData.lVal)
    return true;
    break;
    case VT_R4:
    if (arec.vtStoreData.fltVal <
    brec.vtStoreData.fltVal)
    return true;
    break;
    case VT_R8:
    if (arec.vtStoreData.dblVal <
    brec.vtStoreData.dblVal)
    return true;
    break;
    case VT_BSTR:
    {
    CString stra =
    OLE2CT(arec.vtStoreData.bstrVal);
    CString strb =
    OLE2CT(brec.vtStoreData.bstrVal);
    if (stra < strb)
    return true;
    break;
    }
    default:
    break;
    }
    return false;
    }


    BOOL CADORsX1::DIST_ChangeMapUnfiltered(_RecordsetPtr &rsStore1,
    CString sFldStore1, _RecordsetPtr &rsStore2, CString sFldStore2)
    {
    USES_CONVERSION;
    // Check sizes ...
    long pl1, pl2;
    pl1 = 0;
    pl2 = 0;
    HRESULT hr = rsStore1->get_RecordCount(&pl1);
    hr = rsStore2->get_RecordCount(&pl2);
    if (!(pl1 > 0 && pl2 > 0 && sFldStore1.GetLength() > 0 &&
    sFldStore2.GetLength() > 0))
    return FALSE;

    // Find the Store1 field name index ...
    // (get the Fields)
    int nFldStore1_Ndx = -1;
    Fields *fields = 0;
    hr = rsStore1->get_Fields(&fields);
    long total_flds = 0;
    fields->get_Count(&total_flds);
    if (total_flds <= 0)
    {
    if (fields != 0)
    fields->Release();
    return FALSE;
    }
    // Get Field pointers ...
    Field **field = new (Field(*[total_flds]));
    for (int i = 0; i < total_flds; i++)
    {
    field[i] = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(_variant_t((long)i),
    &field[i]);
    }
    for (i = 0; i < total_flds; i++)
    {
    BSTR bname;
    field[i]->get_Name(&bname);
    CString fldname = OLE2CT(bname);
    if (fldname == sFldStore1)
    {
    nFldStore1_Ndx = i;
    break;
    }
    }
    // Release references ...
    for (i = 0; i < total_flds; i++)
    {
    if (field[i] != 0)
    field[i]->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;

    // Find the Store2 field name index ...
    // (get the Fields)
    int nFldStore2_Ndx = -1;
    fields = 0;
    hr = rsStore2->get_Fields(&fields);
    total_flds = 0;
    fields->get_Count(&total_flds);
    if (total_flds <= 0)
    {
    if (fields != 0)
    fields->Release();
    return FALSE;
    }
    // Get Field pointers ...
    field = new (Field(*[total_flds]));
    for (i = 0; i < total_flds; i++)
    {
    field[i] = 0;
    if (SUCCEEDED(hr))
    hr = fields->get_Item(_variant_t((long)i),
    &field[i]);
    }
    for (i = 0; i < total_flds; i++)
    {
    BSTR bname;
    field[i]->get_Name(&bname);
    CString fldname = OLE2CT(bname);
    if (fldname == sFldStore2)
    {
    nFldStore2_Ndx = i;
    break;
    }
    }
    // Release references ...
    for (i = 0; i < total_flds; i++)
    {
    if (field[i] != 0)
    field[i]->Release();
    }
    if (fields != 0)
    fields->Release();
    delete [] field;

    if (nFldStore1_Ndx < 0 || nFldStore2_Ndx < 0)
    return FALSE;

    // Advance to sFldStore1 Ctrl in this Map ...
    First();
    int currec = 0;
    BOOL bfirst = TRUE;
    _variant_t mapFldVal;
    BOOL bfound = FALSE;
    while (!IsEOF())
    {
    GetField(_variant_t((long)0L), &mapFldVal);
    if (mapFldVal == _variant_t(sFldStore1))
    {
    bfound = TRUE;
    break;
    }
    Next();
    }
    if (!bfound)
    return FALSE;

    // At this point we have the Store's field indexs where the
    data is
    // and we are at that postion in the Map
    //
    ------------------------------------------------------------------
    // Create an array of Map Data (Ctrl, Ndx, Store1[DRec])
    //
    vector <MAPDATA_REC> vaMap;
    CString sCurCtrl = RS_GetString(0);
    CString sLastCtrl = sCurCtrl;
    i = 0;
    while (sCurCtrl == sLastCtrl)
    {
    MAPDATA_REC map_rec;
    map_rec.nMapNdx = i;//RS_GetLong(1); // Ndx fld
    i++;
    map_rec.nDRec = RS_GetLong(2); // DRec fld
    if (map_rec.nDRec == -1)
    break;
    vaMap.push_back(map_rec);
    Next();
    if (IsEOF())
    break;
    sCurCtrl = RS_GetString(0);
    }

    // Sort the Map array by the DRec field ...
    // note - should already be sorted!!
    // sort(vaMap.begin(), vaMap.end(), SortByDRec);

    // Get info from STORE1 into Map array (vtStoreData) ...
    int nsize = vaMap.size();
    int movecnt = 0;
    for (i = 0; i < nsize; i++)
    {
    int curdrec = vaMap[i].nDRec;



    _variant_t vbook;
    vbook.vt = VT_R8;
    vbook.dblVal = curdrec+1;
    PutBookmark(rsStore1, vbook);

    // if (i == 0)
    // rsStore1->MoveFirst();
    // while (movecnt < curdrec)
    // {
    // rsStore1->MoveNext();
    // movecnt++;
    // }
    // if (IsEOF())
    // break;

    _variant_t vtstore;
    GetField(rsStore1, _variant_t((long)nFldStore1_Ndx),
    &vtstore);
    vaMap[i].vtStoreData = vtstore;
    }

    // Sort the Map array by the Variant field (same as Field of
    Store2 now) ...
    sort(vaMap.begin(), vaMap.end(), SortByStoreData);

    // Save Store1's Sort string, Put Sort Store1 by sFldStore1
    ....
    BSTR crit_Store1;
    rsStore1->get_Sort(&crit_Store1);
    rsStore1->put_Sort(_bstr_t(sFldStore1));

    // Save Store2's Sort string, Put Sort Store2 by sFldStore2
    ....
    BSTR crit_Store2;
    rsStore2->get_Sort(&crit_Store2);
    rsStore2->put_Sort(_bstr_t(sFldStore2));

    // Get indexes from STORE2 into Map array (overwrite DRec
    info) ...
    int move2cnt = 0;
    i = 0;

    rsStore2->MoveFirst();
    _variant_t vtStore2a;
    GetField(rsStore2, _variant_t((long)nFldStore2_Ndx),
    &vtStore2a);
    _variant_t vtMapStore = vaMap[i].vtStoreData;

    while (!IsEOF(rsStore2) && i < nsize)
    {
    if (vtStore2a == vtMapStore)
    {
    // Do substitution ...
    _variant_t vbook;
    GetBookmark(rsStore2, &vbook);
    int vbookStore2 =
    ((int)ConvertVarToDouble(vbook)) - 1;
    // Go past dups in Map array ...
    while (i < nsize && vaMap[i].vtStoreData ==
    vtStore2a)
    {
    // Do substitution ...
    vaMap[i].nDRec = vbookStore2;
    // vaMap[i].nDRec = move2cnt;
    i++;
    }
    if (i < nsize)
    vtMapStore = vaMap[i].vtStoreData;
    }
    if (i < nsize)
    {
    // Go past dups in Store2 ...
    if (!IsEOF(rsStore2))
    {
    _variant_t vtStore2b = vtStore2a;
    while (!IsEOF(rsStore2) && vtStore2b
    == vtStore2a)
    {
    rsStore2->MoveNext();
    move2cnt++;
    if (!IsEOF(rsStore2))
    GetField(rsStore2,
    _variant_t((long)nFldStore2_Ndx), &vtStore2a);
    }
    }
    }
    }

    // Here the substituted indexes into Store2 are in the Map
    array,
    // get the array info back into the Map Recordset (this) ...
    // -------------------------------------------------

    // Sort the Map array by the ndx field ...
    sort(vaMap.begin(), vaMap.end(), SortByMapNdx);

    // Advance to sFldStore1 Ctrl in this Map ...
    First();
    currec = 0;
    bfirst = TRUE;
    while (!IsEOF())
    {
    GetField(_variant_t((long)0L), &mapFldVal);
    if (mapFldVal == _variant_t(sFldStore1))
    {
    bfound = TRUE;
    break;
    }
    Next();
    }

    // Do substitution ...
    for (i = 0; i < nsize; i++)
    {
    RS_PutLong((long)2, (long)vaMap[i].nDRec); //
    DRec fld
    Next();
    if (IsEOF())
    break;
    }

    // Put rsStore1, rsStore2 sorts back on ..
    rsStore1->put_Sort(crit_Store1);
    rsStore2->put_Sort(crit_Store2);

    // done!
    return TRUE;
    }


    BOOL CADORsX1::DIST_ChangeMapFiltered(_RecordsetPtr &rsStore1, CString
    sFldStore1, _RecordsetPtr &rsStore2, CString sFldStore2, CString
    sFiltFldStore2)
    {
    USES_CONVERSION;
    return FALSE;
    }


    int CADORsX1::parseStringCSV(vector <CString> &vaPRS, CString sString,
    BOOL bMakeUpper)
    {
    /* example: " FieldA, FieldB, FieldC " */
    USES_CONVERSION;
    int ncurpos = 0;
    int nlastpos = 0;
    int npos = 0;
    if (vaPRS.size() > 0)
    vaPRS.erase(vaPRS.begin(), vaPRS.end());

    do {
    ncurpos = sString.Find(',',nlastpos);

    if (ncurpos >= 0)
    npos = ncurpos;
    else
    if (sString.GetLength() > 0)
    npos = sString.GetLength();

    CString stmp = sString.Mid(nlastpos, (npos -
    nlastpos));
    if (stmp.GetLength() > 0)
    {
    // ok found one, make upper and remove all
    peripheral spaces ..
    if (bMakeUpper)
    stmp.MakeUpper();
    stmp.TrimRight();
    stmp.TrimLeft();
    vaPRS.push_back(stmp);
    }
    nlastpos = ncurpos+1;
    } while (ncurpos >= 0);

    return vaPRS.size();
    }[/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i][/i]
     
    robic0, Dec 4, 2005
    #2
    1. Advertising

  3. John Smith

    John Smith Guest

    robic0 wrote:
    > On Sat, 03 Dec 2005 14:26:16 -0500, John Smith <>
    > wrote:
    >
    >> I have data in a Foxpro DBF table. Is there a quick, simple way in Perl
    >> to copy this table into a SQLite table? I know how to do it manually
    >> using DBI, but I'm looking for a package that does it all automatically.
    >> I do not mind if the data types are not mapped exactly right. Could
    >> SNOPS do it?
    >>
    >> The reason I need to do this is because I need to generate some

    reports
    >>from the Foxpro database. However, I'm unfamiliar with Foxpro SQL, the
    >> Foxpro ODBC driver has very limited functionality, and the data is
    >> read-only. So it would be easier if I imported the data into a more
    >> advanced database and worked with it from there. I'm using Perl on
    >> Windows XP.

    >
    > This is really puzzling, Foxpro is very limited functionality?
    > I never used foxpro, but most dbm engines support standard SQL
    > syntax. Are you new to sql query's? Have you ever heard of
    > "stored procedures"? Most need several practice query's to have
    > it sink in. Mostly simple. However in Access, the level of
    > auto-generated indirection (relationships) can produce query
    > strings several K in size that can only be analyzed graphically.
    > Access does however support simple sql standards.
    >


    Foxpro is not bad, but the Foxpro ODBC driver is very limited. See:
    http://tinyurl.com/bhdby

    I tried DBIx::Migrate, but it didn't create the tables in the target. I
    looked at SPOPS, but it was too complicated. I didn't want to use
    inefficient Table2Hash/Hast2Table functions. Taking advantage of
    SQLite's data type independence, I wrote this utility function:

    # source DBI connection - odbc
    # target DBI connection - sqlite
    # odbc query
    # new table name
    sub CreateTemporarySQLiteTable($$$$)
    {
    my ($dbh, $sth, @row, $i);
    my $source = shift;
    my $target = shift;
    my $query = shift;
    my $newtablename = shift;
    my ($fieldnames);

    $sth = $source->prepare($query)
    or die "Error-".$source->errstr()."-preparing: $query";
    $sth->execute() or die "Error-".$source->errstr()."-executing: $query";
    $fieldnames = $sth->{NAME_uc};
    $query = "CREATE TABLE ".$newtablename." ('".join("','",@$fieldnames)."')";
    $target->do($query)
    or die "Error creating table: ".$source->errstr();
    while(@row = $sth->fetchrow_array())
    {
    $query = "INSERT INTO ".$newtablename." VALUES (";
    for($i = 0; $i < scalar(@row); $i++)
    {
    $query .= $target->quote($row[$i]);
    if($i == scalar(@row)-1) {
    $query .= " ) ";
    } else {
    $query .= " , ";
    }
    }
    $target->do($query);
    }
    $target->commit();
    }

    It reads my Foxpro data into a SQLite table. Actually, it takes the
    results of any SELECT query from any DBI data source, and dumps it into
    a new SQLite table. I can take snapshots of all the data I need from
    Foxpro, and then do additional processing from there. The main thing I
    wanted from SQLite was the ability to do nested subqueries. The Foxpro
    ODBC driver only supports a couple in the WHERE clause. I couldn't
    import or touch the Foxpro database, because it is part of an in-use,
    legacy application. Thanks for your reply.
     
    John Smith, Dec 5, 2005
    #3
  4. John Smith

    robic0 Guest

    On Mon, 05 Dec 2005 02:29:55 -0500, John Smith <>
    wrote:

    >robic0 wrote:
    >> On Sat, 03 Dec 2005 14:26:16 -0500, John Smith <>
    >> wrote:
    >>
    >>> I have data in a Foxpro DBF table. Is there a quick, simple way in Perl
    >>> to copy this table into a SQLite table? I know how to do it manually
    >>> using DBI, but I'm looking for a package that does it all automatically.
    >>> I do not mind if the data types are not mapped exactly right. Could
    >>> SNOPS do it?
    >>>
    > >> The reason I need to do this is because I need to generate some

    >reports
    >>>from the Foxpro database. However, I'm unfamiliar with Foxpro SQL, the
    >>> Foxpro ODBC driver has very limited functionality, and the data is
    >>> read-only. So it would be easier if I imported the data into a more
    >>> advanced database and worked with it from there. I'm using Perl on
    >>> Windows XP.

    >>
    >> This is really puzzling, Foxpro is very limited functionality?
    >> I never used foxpro, but most dbm engines support standard SQL
    >> syntax. Are you new to sql query's? Have you ever heard of
    >> "stored procedures"? Most need several practice query's to have
    >> it sink in. Mostly simple. However in Access, the level of
    >> auto-generated indirection (relationships) can produce query
    >> strings several K in size that can only be analyzed graphically.
    >> Access does however support simple sql standards.
    >>

    >
    >Foxpro is not bad, but the Foxpro ODBC driver is very limited. See:
    >http://tinyurl.com/bhdby
    >
    >I tried DBIx::Migrate, but it didn't create the tables in the target. I
    >looked at SPOPS, but it was too complicated. I didn't want to use
    >inefficient Table2Hash/Hast2Table functions. Taking advantage of
    >SQLite's data type independence, I wrote this utility function:
    >
    ># source DBI connection - odbc
    ># target DBI connection - sqlite
    ># odbc query
    ># new table name
    >sub CreateTemporarySQLiteTable($$$$)
    >{
    > my ($dbh, $sth, @row, $i);
    > my $source = shift;
    > my $target = shift;
    > my $query = shift;
    > my $newtablename = shift;
    > my ($fieldnames);
    >
    > $sth = $source->prepare($query)
    > or die "Error-".$source->errstr()."-preparing: $query";
    > $sth->execute() or die "Error-".$source->errstr()."-executing: $query";
    > $fieldnames = $sth->{NAME_uc};
    > $query = "CREATE TABLE ".$newtablename." ('".join("','",@$fieldnames)."')";
    > $target->do($query)
    > or die "Error creating table: ".$source->errstr();
    > while(@row = $sth->fetchrow_array())
    > {
    > $query = "INSERT INTO ".$newtablename." VALUES (";
    > for($i = 0; $i < scalar(@row); $i++)
    > {
    > $query .= $target->quote($row[$i]);
    > if($i == scalar(@row)-1) {
    > $query .= " ) ";
    > } else {
    > $query .= " , ";
    > }
    > }
    > $target->do($query);
    > }
    > $target->commit();
    >}
    >
    >It reads my Foxpro data into a SQLite table. Actually, it takes the
    >results of any SELECT query from any DBI data source, and dumps it into
    >a new SQLite table. I can take snapshots of all the data I need from
    >Foxpro, and then do additional processing from there. The main thing I
    >wanted from SQLite was the ability to do nested subqueries. The Foxpro
    >ODBC driver only supports a couple in the WHERE clause. I couldn't
    >import or touch the Foxpro database, because it is part of an in-use,
    >legacy application. Thanks for your reply.
    >

    Honestly, I don't know why you go to so much trouble trying to
    just generate a report. The create and insert constructs are
    standard. You seem to have sql down so it shouldn't matter what
    db engine you use. When you programmatically do a create table
    (with fields), then populate it with insert into's (records),
    and can't do a query on the existing db/table? That doesen't
    make sense.

    If the where clause isin't so robust, then convert
    the db/table into another form using acess. Then construct a query
    string to be passed to a more robust engine that can do the sql
    you need to describe the output you want.

    To that end, today I casually noticed standard Perl has an ADO
    interface inplememted directly through their Ole module (using OleDB)
    that auto-initializes. Using that, if you know ADO paradigm, you can
    do anything you want, such as what your code shows above and all your
    query's directly. The code I posted just wrapped the ADO paradigm
    that can be done in this module, however it just marshals information
    to the connected db engine. So beyond table creation, it still boils
    down to creating a sql string that you connected db engine can
    understand. This is true of ALL engines, thus ADO.

    I think you are confusing the issue when you rely on hacked out
    modules to provide you a turnkey solution. You don't know these
    modules. You would be better off understanding the ADO paradigm
    which is your gateway to ALL dbm's. That paradigm is the one
    used in all and anything database in Perl. This is the joke
    here --> You don't think that when M$ says this is the way it will
    be, the major (and minor) corporate db don't comply do you?

    ADO is the common interface to ALL ODBC dbm's running on windows.
    And now you know you don't need DBI for it. If you want
    the ability to convert Foxpro db to one with more robust sql
    and/or you work with alot and want to do complex reports that
    Foxpro cant do but that Foxpro can be converted to another
    engine and that would be a part of the procedure everytime.
    If Foxpro ODBC can be relied upon to provide the minimum
    get fields/records via ADO, sure you could convert it in Perl
    if you can connect to it. You would have a parallel connection
    to another dbm (like you did above), then write out the new db/table.
    Then do your sql/reports using the new engine.

    Its all so tedius. I've done this thousands of times. But you know
    its job security where you work since nobody wants to touch this
    stuff. A word of advice, when you get a method down, don't tell
    your boss that you can do this anytime easily now. Make him think
    every request requires custom code and a long time to do it.
     
    robic0, Dec 7, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Xavier MT
    Replies:
    2
    Views:
    549
    Guest
    Aug 13, 2003
  2. Peter Bassett
    Replies:
    3
    Views:
    931
    Augustus
    Aug 15, 2003
  3. Otuatail

    Tables within tables

    Otuatail, Jul 31, 2004, in forum: HTML
    Replies:
    7
    Views:
    498
  4. Chris Brat
    Replies:
    5
    Views:
    698
    =?iso-8859-1?q?Luis_M._Gonz=E1lez?=
    Aug 22, 2006
  5. Kamarulnizam Rahim

    Migrate data from .csv to .yml

    Kamarulnizam Rahim, Jan 12, 2011, in forum: Ruby
    Replies:
    4
    Views:
    295
    Kamarulnizam Rahim
    Jan 12, 2011
Loading...

Share This Page