Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Perl Misc (http://www.velocityreviews.com/forums/f67-perl-misc.html)
-   -   migrate data tables (http://www.velocityreviews.com/forums/t895465-migrate-data-tables.html)

John Smith 12-03-2005 07:26 PM

migrate data tables
 
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.

robic0 12-04-2005 11:52 PM

Re: migrate data tables
 
On Sat, 03 Dec 2005 14:26:16 -0500, John Smith <noone@nowhere.net>
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_00 010227EF90__INCLUDED_)
#define AFX_ADOTIER_H__F4DF1E23_D860_11D5_B52D_00010227EF9 0__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(Recordse t));
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[i].fldName),
Fi[i].fldType, Fi[i].fldSize, Fi[i].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[i] = 0;
if (SUCCEEDED(hr))
hr = fields->get_Item(_variant_t((long)i),
&field[i]);
}
// Add new records ...
int numrecs = aRecords.size();
for (i = 0; i < numrecs; i++)
{
if (SUCCEEDED(hr))
{
hr = rsReturnRecordSet->AddNew();
aRecords[i].PutRsFieldValues(field, (long)i);
}
}
// Release references ...
for (i = 0; i < numflds; i++)
{
if (field[i] != 0)
field[i]->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(Recordse t));
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[i].fldName),
vaFi[i].fldType, vaFi[i].fldSize, vaFi[i].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[i] = 0;
if (SUCCEEDED(hr))
hr = fields->get_Item(_variant_t((long)i),
&field[i]);
}
// Add new records ...
int numrecs = aRecords.size();
for (i = 0; i < numrecs; i++)
{
if (SUCCEEDED(hr))
{
hr = rsReturnRecordSet->AddNew();
aRecords[i].PutRsFieldValues(field, (long)i);
}
}
// Release references ...
for (i = 0; i < numflds; i++)
{
if (field[i] != 0)
field[i]->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_00 010227EF90__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_A9 04BA94DE31__INCLUDED_)
#define AFX_ADORSX1_H__5D210159_CADE_4352_8BC7_A904BA94DE3 1__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_A9 04BA94DE31__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()),_var iant_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()),_var iant_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[i] = 0;
if (SUCCEEDED(hr))
hr =
fields->get_Item(_variant_t((long)i), &field[i]);
}
//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();
}




John Smith 12-05-2005 07:29 AM

Re: migrate data tables
 
robic0 wrote:
> On Sat, 03 Dec 2005 14:26:16 -0500, John Smith <noone@nowhere.net>
> 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.



robic0 12-07-2005 06:33 AM

Re: migrate data tables
 
On Mon, 05 Dec 2005 02:29:55 -0500, John Smith <noone@nowhere.net>
wrote:

>robic0 wrote:
>> On Sat, 03 Dec 2005 14:26:16 -0500, John Smith <noone@nowhere.net>
>> 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.



All times are GMT. The time now is 10:17 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.